mysqldump会锁表吗，如何使用mysqldump命令备份mysql数据库（全局锁、表级锁和行锁）

关于【mysqldump会锁表吗】，如何使用mysqldump命令备份mysql数据库，今天小编给您分享一下，如果对您有所帮助别忘了关注本站哦。

• 内容导航：
• 1、MySQL锁：全局锁、表级锁和行锁
• 2、如何使用mysqldump命令备份mysql数据库
• 3、mysqldump 导出数据库 到底怎么用
• 4、mysql如何备份数据

1、MySQL锁：全局锁、表级锁和行锁

事务的实现离不开MySQL数据库的锁机制，设计锁的目的也是为了处理并发访问问题，本文简单介绍MySQL 里面的全局锁、表级锁和行锁三类锁。

按对数据操作的类型可分为读锁（read lock）和写锁（write lock）。

• 读锁也叫共享锁（S锁），加了读锁后，加锁的当前会话以及其它会话只能进行读操作，并且多个读操作可以同时进行。
• 写锁也称为排他锁，X锁，exclusive的缩写，因为加了写锁之后其它会话不能进行读写操作。

从对数据操作的颗粒度来分，锁可以分为全局锁、表级锁和行锁，下面分别对他们进行介绍。

全局锁

全局锁是对整个数据库加锁，数据库被加上全局锁后所有数据表会处于只读状态，修改数据表的DDL语句，数据更新语句（增删改）等操作会被阻塞。

全局锁的命令是：FLUSH TABLES WITH READ LOCK; , 简称FTWRL，解锁命令：UNLOCK TABLES;

窗口A加全局锁：

mysql> FLUSH TABLES WITH READ LOCK;Query OK, 0 rows affected (0.00 sec)

窗口A插入数据会报错：

mysql> insert into department (name) values ('测试');ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

窗口B查询：

mysql> select * from department;+----+------+| id | name |+----+------+| 1 | 开发 || 2 | 产品 || 5 | 测试 |+----+------+3 rows in set (0.04 sec)

窗口B插入数据会被阻塞：

mysql> insert into department (name) values ('测试');

窗口A解锁：

mysql> UNLOCK TABLES;Query OK, 0 rows affected (0.00 sec)

窗口B插入语句执行成功：

mysql> insert into department (name) values ('测试');Query OK, 1 row affected (2 min 44.85 sec)mysql> select * from department;+----+------+| id | name |+----+------+| 1 | 开发 || 2 | 产品 || 5 | 测试 || 6 | 测试 |+----+------+4 rows in set (0.00 sec)

全局锁的一个使用场景是做全库逻辑备份（mysqldump），加了全局锁后，在备份过程中整个库处于只读状态。如果备份主库，备份期间更新相关业务都无法执行；如果是备份从库，备份期间从库就不能执行主库同步过来的 binlog，会导致主从延迟。

支持事务的引擎InnoDB可以不使用FTWRL，使用mysqldump命令备份时可以使用参数--single-transaction来获取一致性备份，它会在开始备份前启动一个事务（可重复读隔离级别），利用了MVCC技术（可参考文章MySQL事务：事务隔离），确保拿到一致性视图，备份过程可以正常更新数据。

而不支持事务的引擎MyISAM就无法使用--single-transaction参数了，它不支持事务隔离，所以使用MyISAM引擎的数据库备份需要使用FTWRL命令。

表级锁

表级锁是对整张表加锁，MyISAM、InnoDB、BDB和MEMOR都支持表级锁。

MySQL 表级锁包括两种：表锁和元数据锁（meta data lock，MDL)。

表锁

表锁包括读锁（read lock）和写锁（write lock）。

lock table 表名 read; -- 读锁 lock table 表名 write; -- 写锁unlock tables; -- 释放表锁，连接断开时会自动释放表锁

以MyISAM引擎为例演示一下表锁的作用效果：

创建一个以MyISAM为引擎的表，并插入一条记录：

create table table_lock_test( id int not null auto_increment primary key, name char(30) not null) ENGINE=MyISAM DEFAULT CHARSET=utf8; insert into table_lock_test (name) values ('zhangsan');

1、读锁实例

窗口A给table_lock_test表加读锁：

mysql> lock table table_lock_test read;Query OK, 0 rows affected (0.04 sec)mysql> select * from table_lock_test;+----+----------+| id | name |+----+----------+| 1 | zhangsan |+----+----------+1 row in set (0.00 sec)

查看被锁定的表：

mysql> show open tables where In_use > 0;+----------+-----------------+--------+-------------+| Database | Table | In_use | Name_locked |+----------+-----------------+--------+-------------+| testdb | table_lock_test | 1 | 0 |+----------+-----------------+--------+-------------+1 row in set (0.00 sec)

窗口A执行更新、插入操作：

mysql> update table_lock_test set name='lishi' where id=1;ERROR 1099 (HY000): Table 'table_lock_test' was locked with a READ lock and can't be updatedmysql> insert into table_lock_test (name) values ('lishi');ERROR 1099 (HY000): Table 'table_lock_test' was locked with a READ lock and can't be updated

窗口A读其它表：

mysql> select * from department;ERROR 1100 (HY000): Table 'department' was not locked with LOCK TABLES

未解锁前不能操作其它表。

窗口B查询：

mysql> select * from table_lock_test;+----+----------+| id | name |+----+----------+| 1 | zhangsan |+----+----------+1 row in set (0.00 sec)

窗口B执行更新操作：

mysql> update table_lock_test set name='lishi' where id=1;

更新操作会被阻塞，因为读锁没有被释放，只能查询不能更新。到窗口A将锁释放：

unlock tables;

解锁后，窗口B更新操作成功：

mysql> update table_lock_test set name='lishi' where id=1;Query OK, 1 row affected (3 min 22.88 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from table_lock_test;+----+-------+| id | name |+----+-------+| 1 | lishi |+----+-------+1 row in set (0.00 sec)

2、写锁实例

窗口A给table_lock_test表加写锁：

mysql> lock table table_lock_test write;Query OK, 0 rows affected (0.00 sec)mysql> select * from table_lock_test;+----+-------+| id | name |+----+-------+| 1 | lishi |+----+-------+1 row in set (0.00 sec)

窗口A执行更新、插入操作：

mysql> update table_lock_test set name='zhangsan' where id=1;Query OK, 1 row affected (0.05 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> insert into table_lock_test (name) values ('lishi');Query OK, 1 row affected (0.05 sec)mysql> select * from table_lock_test;+----+----------+| id | name |+----+----------+| 1 | zhangsan || 2 | lishi |+----+----------+2 rows in set (0.00 sec)

窗口A读其它表：

mysql> select * from department;ERROR 1100 (HY000): Table 'department' was not locked with LOCK TABLES

和读锁一样，未解锁前不能其它表进行CRUD操作。

窗口B查询：

mysql> select * from table_lock_test;

被阻塞了，因为写锁为排它锁，其它连接不能对table_lock_test表进行读写操作。

到窗口A将锁释放：

unlock tables;

解锁后，窗口B查询成功：

mysql> select * from table_lock_test;+----+----------+| id | name |+----+----------+| 1 | zhangsan || 2 | lishi |+----+----------+2 rows in set (3 min 10.99 sec)

元数据锁

在 MySQL 5.5 以后的版本中，访问一个表的时候会自动加上 MDL（metadata lock)，作用是保证读写的正确性。比如在RR隔离级别下，当前事务A在操作一个表的过程中，另外一个事务B对表执行DDL操作（表结构变更），如果没有MDL，会导致事务A 两次查询结果不一样。要实现可重复度，就需要使用到MDL。

当对一个表做DML操作（增删改查）时，加 MDL 读锁，当其它事务对表进行DDL操作时，需要先获得MDL 读锁才能对表结构进行修改；当要对表做DDL操作（表结构变更）时，会加 MDL 写锁。

• 读锁之间不互斥，多事务同时对一张表进行DML操作不会阻塞。 注意：实际进行增删改查时可能会看到锁等待，这是InnoDB的行锁导致的。
• 读写锁之间互斥，也就是对同一表进行的DML和DDL操作会相互阻塞。
• 写锁之间互斥，多事务同时对一张表进行DDL操作会阻塞，其中一个要等另一个执行完才能开始执行。

MDL锁在语句开始执行时申请，在事务提交后释放。如果事务A对表T加了一个MDL读锁，且未提交。事务B对数据表执行DDL操作，此时事务B会被锁住（Waiting for table metadata lock），因为事务A 的 MDL 读锁没有释放，而事务B需要 MDL 写锁，因此会被阻塞。而后续对表T的任何操作都会被阻塞，此时表T完全不可读写了。MDL 锁的这一机制需要特别注意。

在窗口A启动一个事务：

mysql> select * from performance_schema.metadata_locks;+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+| TABLE | performance_schema | metadata_locks | NULL | 1883582562928 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5929 | 75 | 4 |+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+1 row in set (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.04 sec)mysql> select * from table_lock_test;+----+----------+| id | name |+----+----------+| 1 | zhangsan || 2 | lishi |+----+----------+2 rows in set (0.14 sec)mysql> select * from performance_schema.metadata_locks;+-------------+--------------------+-----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |+-------------+--------------------+-----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+| TABLE | testdb | table_lock_test | NULL | 1883582562928 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5929 | 75 | 7 || TABLE | performance_schema | metadata_locks | NULL | 1883583662656 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5929 | 75 | 8 |+-------------+--------------------+-----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+2 rows in set (0.00 sec)mysql>

事务A启动后，metadata_locks表新增了一条table_lock_test表的加锁记录。

窗口B执行一个修改表结构的DDL操作：

mysql> ALTER TABLE table_lock_test ADD age TINYINT;

会被阻塞。

窗口A查询数据库线程：

mysql> show processlist;+----+-----------------+-----------------+--------+---------+---------+---------------------------------+---------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-----------------+-----------------+--------+---------+---------+---------------------------------+---------------------------------------------+| 4 | event_scheduler | localhost | NULL | Daemon | 1693591 | Waiting alt="mysqldump会锁表吗，如何使用mysqldump命令备份mysql数据库（全局锁、表级锁和行锁）" src="https://p9.toutiaoimg.com/tos-cn-i-qvj2lq49k0/576847d263fb4302bc116901109e7506~tplv-tt-large.image" />

出现死锁后，有两种策略来解决：

1. 等待锁超时。默认超时等待时间为50s，可通过innodb_lock_wait_timeout参数来设置，具体等待时间需要根据自己的实际业务来设置。
2. 死锁检测。设置参数innodb_deadlock_detect = on，开启后，系统会自动检测死锁的事务并回滚某一个事务，让其他可以事务继续执行。

总结

本文简单介绍了全局锁、表级锁和行锁，全局锁主要用于全库逻辑备份。如果数据库引擎使用的是 InnoDB，可以使用 -–single-transaction参数。

表锁是对整张表加锁，访问一个表的时候会自动加上元数据锁(MDL)，MDL锁在语句开始执行时申请，在事务提交后释放。如果表T的MDL锁没有释放，后续对表T的任何操作都会被阻塞。

行锁是对数据行加锁，MyISAM引擎不支持行锁，它一般使用的是表级锁。InnoDB 引擎支持行锁，因此它可以实现更好的并发控制。另外使用行锁要注意尽量减少死锁对数据库的影响。

另外要注意的是，加锁是要消耗资源的，对锁的各种操作会增加系统的开销，相比MyISAM引擎，InnoDB 引擎的行锁会导致占用更大的数据空间。

--THE END--

凡是对自己有一点利益就会选择相信。有时候即使毫无可能，人们也会顽冥不化。

2、如何使用mysqldump命令备份mysql数据库

通用规律只有使用 --all-databases (-A) 会 ERROR 1356，那就看看他到底备份了什么东西。于是喊上同事一起 less 看了下，上下扫了两眼。

突然发现：1. 备份 SQL 文件里 DROP 掉了 mysql.proc；2. 后CREATE了一个新的 mysql.proc；3. LOCK TABLES 和 UNLOCK TABLES 中间居然没有备份 CREATE ROUTINE 任何数据？这不就是相当于每次导入全备都给我一个没有任何 sys schema routines 的全新 mysql.proc 表？那这不就异常的尴尬？---- Table structure for table `proc`------ Dumping data for table `proc`-真相大白在官方文档【sys-schema-usage】官方文档明确的告诉我们不会备份 sys 库。

但在使用 mysqldump 在执行 --all-databases 会清空 mysql.proc 导致 sys 无法正常使用；这是一个 BUG，并且只存在于 MySQL 5.7.x ！1、mysql_upgrade install or upgrade sys schema这个方案适用于 sys 库已经因为 mysqldump 导入而损坏的情况下使用。注意：mysql_upgrade 在修理 sys 库的同时，还修理 mysql 库和用户库表（期间加锁且速度一般），有极小可能会误伤；使用 mysql_upgrade 的时候要加上 --upgrade-system-tables，不然会扫描用户库表。2、全备时同时备份 sys 库这个方案适用于需要还原的数据库，sys 库也不太正常的情况下使用；在全备后额外再备份一份 sys 库用于修复。注意：不适用于做主从时使用它。

3、使用 databases 全备这个方案适用于所有场景的全备需求，100% 安全。4、使用 mysql-sys 开源代码如果你的数据库 sys 全部中招了，又是生产库。那你只能用这个方法；mysql-sys：https://github.com/mysql/mysql-sys中记录了 sys 库的创建语句将文件下载到本地，然后根据数据库版本，执行以下命令即可。

3、mysqldump 导出数据库 到底怎么用

开始——运行——输入cmd，确定备份或导出数据库命令mysqldump怎么使用？进入Dos命令行下，输入mysqldump --help,可以先熟悉mysqldump的帮助信息注意：如果安装时配置了bin路径则可以直接输入mysqldump --help命令如果安装时没有配置了bin路径则需要切换到mysql安装目录下的bin目录下输入mysqldump --help命令备份或导出数据库命令mysqldump怎么使用？在帮助信息里可以看到命令的基本格式如下：Usage:mysqldump [OPTIONS] database [tables]OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]OR mysqldump [OPTIONS] --all-databases [OPTIONS]备份或导出数据库命令mysqldump怎么使用？备份或导出整个数据库输入mysqldump -uroot -p test > test.sql 回车后输入输入root的密码即可命令解释：mysqldump -u 用户名 -p 数据库名 > 导出的文件名备份或导出数据库命令mysqldump怎么使用？这样打开我的电脑就可以在当前目录看到导出的文件了备份或导出数据库命令mysqldump怎么使用？备份或导出数据库中一个表输入mysqldump -uroot -p phpmyadmin pma_bookmark > pma_bookmark.sql 回车后输入输入root的密码即可命令解释：mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名备份或导出数据库命令mysqldump怎么使用？7这样就可以在当前目录看到导出的文件了。就可以还原或导入到别的数据库服务器上了。

补充：关于msyql其他命令怎么使用，可以点击作者名片，在经验列表中找到。

4、mysql如何备份数据

1.1

本文关键词：mysqldump 备份数据库，mysqldump备份命令及参数描述正确，mysqldump备份整个数据库，mysqldump备份数据库表，mysqldump备份数据表命令。这就是关于《mysqldump会锁表吗，如何使用mysqldump命令备份mysql数据库（全局锁、表级锁和行锁）》的所有内容，希望对您能有所帮助！