MySQL 锁表
2019-10-06 本文已影响0人
DB哥
Linux System Environment
[root@master ~]# cat /etc/redhat-release #==》系统版本
CentOS Linux release 7.5.1804 (Core)
[root@master ~]# uname –r #==》系统内核
3.10.0-862.el7.x86_64
[root@master ~]# uname -m #==》系统位数
x86_64
[root@master ~]# echo $LANG #==》系统字符集
en_US.UTF-8
[root@master ~]# mysql –version #==》MySQL版本
mysql Ver 14.14 Distrib 5.7.27, for linux-glibc2.12 (x86_64) using EditLine wrapper
MySQL锁表简介
MySQL锁表是禁止用户在数据库增加/删除/修改操作，数据库读操作是不限制，但锁表命令是受时间限制，在不同的数据库引擎的情况下，会受下面参数的控制，锁表时，如果超过设置的时间就会自动解锁。
#==》默认单位时间为秒
interactive_timeout=60
wait_timeout=60
MySQL锁表与解锁命令
#==》锁表命令
mysql> flush table with read lock;
#==》解锁命令
mysql> unlock tables;
一、MySQL设置锁表时间
标注：锁表有两种方法，第一种方法是临时生效，重启数据库参数失效；第二种方法是永久生效，但要重启数据库才生效
1、MySQL锁表临时生效（如果数据库不能重启，建议使用此方法）
#==》先执行锁表命令以下锁表时间才会生效
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
#==》MySQL默认锁表时长为 8小时
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------+----------+
13 rows in set (0.01 sec)
mysql> set global wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)
mysql> set global interactive_timeout = 10;
Query OK, 0 rows affected (0.00 sec)
#==》需要重新登录数据库即可查看生效状态
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 60 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 60 |
+-----------------------------+----------+
13 rows in set (0.01 sec)
2、MySQL锁表永久生效
#==》先执行锁表命令以下锁表时间才会生效
mysql> flush table with read lock;
#==》MySQL默认锁表时长为 8小时
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------+----------+
13 rows in set (0.01 sec)
#==》修改为10秒测试
[root@master ~]# vim /etc/my.cnf
interactive_timeout = 10
wait_timeout = 10
#==》重启数据库配置即可生效
[root@master ~]# /etc/init.d/mysqld restart
#==》查看修改情况
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 60 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 60 |
+-----------------------------+----------+
13 rows in set (0.01 sec)
二、测试
#==》锁表
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
#==》显示数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
#==》创建数据，提示锁表了
mysql> create database testmysql;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> set global wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)
mysql> set global interactive_timeout = 10;
Query OK, 0 rows affected (0.00 sec)
#==》需要重新登录数据库即可查看生效状态
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 10 |
+-----------------------------+----------+
13 rows in set (0.01 sec)
#==》创建数据成功，有警告但不影响
mysql> create database testmysql;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 9
Current database: *** NONE ***
Query OK, 1 row affected (0.01 sec)
#==》查看创建的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testmysql |
+--------------------+
5 rows in set (0.00 sec)
