mysqldump 逻辑备份的正确姿势

在上一篇文章 MySQL 命令行工具之 mysqldump 深入研究中，我们搞定了mysqldump的参数和基本原理。那么我们该怎么样最好的使用它的？它有哪些坑呢？

1. 利用mysqldump进行逻辑备份

1）全逻辑备份：

mysqldump -uxxx -p --flush-logs --delete-master-logs --all-databases > alldb.sql (每天晚上进行一次全备)

2）增量备份：

mysqladmin flush-logs (每小时刷一下，保存起来，进行了一次增量备份)

3）缺点：

1> --all-databases 包含了 mysql 数据库，其中包含了权限的数据，所以我们应该加上 --flush-privileges，在恢复时，权限才能生效；

注意 --all-databases 包括了mysql数据库，但是不会包含 information_schema和performance_schema两个数据库。

2> 因为 mysqldump 默认启用了 --lock-tables，所以会导致在备份期间对所有表持有读锁： lock table tb read local，所以所有的update，delete语句

会被阻塞。但是select语句和insert语句不会被阻塞。

3> --delete-master-logs 备份之后，会执行 purge logs to 语句。删除了备份之后的master上的binary log. 一般而言，我们不建议随便删除binary log.

我们应该将它们保存起来，而不是直接删除。以防万一，要留条退路。

4> 该备份方式，虽然在整个备份过程中持有了 lock table tb read local，但是还是可以执行 insert 语句的。所以得到的不是一致性的备份。虽然得到的不是

一致性的备份，但是因为flush log之后，所有的操作也会记入新的binary log，所以如果使用了所有新的binary log来进行完全恢复的话，最后恢复的数据

也是一致性的。当然不一致性的备份无法用于搭建slave。

如果要得到一致性的备份的话，需要使用 --lock-all-tables 或者使用 --single-transaction 选项。前者使用了全局读锁，不允许任何修改操作。后者使用

了事务的特性来得到一致性备份。

所以我们应该对上面的备份方式进行改良。

2. 使用mysqldump备份的最佳姿势

1）优化锁和得到一致性备份：

我们可以使用结合使用 --single-transaction 、--master-data=2 、--flush-logs 来达到将锁定时间大大减少的目的。同时有得到了一致性的备份，而且该一致性备份和 flush 的日志也是一致的；

2）去掉 --delete-master-logs 选项，改为在备份之后，将所有被刷新的 binary log 移到一个地方保存起来；

3）因为使用了 --single-transaction 选项，针对的只能是 innodb 数据，但是mysql数据是Myisam引擎的，所以我们最好将mysql数据库的备份分开来，

另外专门针对 mysql 数据库进行一次操作。当然不分开来备份，可能也没有问题。

4）还要加上 --routines 来备份存储过程和函数，触发器默认会备份。

优化之后，我们得到：

mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --databases db1 db2 db3 > alldb.sql;

mysqldump -uxxx -p --flush-privileges --databases mysql > mysql.sql;

如果将mysql也一起备份的话：

mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --flush-privileges --all-databases > alldb.sql;

3. 使用mysqldump来搭建slave环境

搭建slave环境，一般有两种方法，对于规模不大的库，可以采用mysqldump来搭建；对于规模很大的库，最好采用xtrabackup来搭建，速度要快很多。

1）首先分别在master和slave上设置不同的server_id=1/101，启用master上的log-bin=1，启用slave上的relog-log=relay-bin; 在master上设置：

binlog_format=row；二进制日志的格式。maser上最好还设置sync_binlog=1 和 innodb_flush_log_at_trx_commit=1防止发生服务器崩溃时

导致复制破坏。在slave上最好还配置：read-only=1 和 skip-slave-start=1 前者可以防止没有super权限的用户在slave上进行写，后者防止在启动

slave数据库时，自动启动复制线程。以后需要手动start slave来启动复制线程。注意slave没有必要启用 log-bin=1，除非需要搭建二级slave。

2）在master上建立一个具有复制权限的用户：

grant replication slave, replication client on *.* to repl@’192.168.%.%’ identified by ‘123456’;

3）备份master上的数据库，迁移到slave上：

[root@localhost ~]# mysqldump -uroot -p --routines --flush-logs --master-data=2 --databases db2 db1>/root/backup.sql
Enter password:
[root@localhost ~]# scp /root/backup.sql 192.168.137.9:/tmp/backup.sql
The authenticity of host '192.168.137.9 (192.168.137.9)' can't be established.
RSA key fingerprint is a4:cd:c0:13:d1:8c:c0:a5:e7:c4:43:b5:95:17:af:d3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.137.9' (RSA) to the list of known hosts.
root@192.168.137.9's password:
backup.sql

因为slave的搭建需要一致性的备份，所以需要启用 --lock-all-tables(master-data=1/2会自动启用--lock-all-tables)或者--single-transaction；

另外还需要知道该一致性备份的数据，对应的master上的binary log的文件名，以及在该文件中的position，所以必须启用 master-data选项。

因为--master-data会启用--lock-all-tables 所以数据才是一致性的；但是导致了全局锁，不能进行任何修改操作；下面我们使用--single-transaction进行优化：

mysqldump -uroot -p --routines --flush-logs --single-transaction --master-data=2 --databases db1 db2 > /root/backup.sql; (--flush-logs非必须)

这样全局锁仅仅在备份的开始短暂的持有。不会再备份的整个过程中持有全局锁。

4）在slave上执行备份的脚本，然后连上master，开启复制线程：

执行sql脚本：

mysql> source /tmp/backup.sql

找到 --master-data 输出的 binary log 的文件名和postion:

[root@localhost ~]# head -50 /tmp/backup.sql

......
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;--
-- Position to start replication or point-in-time recovery from
---- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=809;

执行 change master to, start slave:

在salve上执行命令开始复制：
mysql> change master to master_host='192.168.137.8', master_user='repl', master_password='123456',-> master_log_file='mysql-bin.000010', master_log_pos=809;
Query OK, 0 rows affected, 2 warnings (0.09 sec)mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

最后在slave上查看复制线程的状态：

mysql> show slave status\G
... ...Slave_IO_Running: YesSlave_SQL_Running: Yes
......

slave_IO_Runing 和 slave_sql_runing 状态都是yes表示搭建成功。

5）replication涉及到的三个线程：
1> master上的 binlog dump(dump线程)，即读取master上的binlog，发送到slave上的线程。
2> slave上的IO线程：读取slave上的relay log。
3> slave上的sql线程：执行IO线程读取的relay log的线程。

4. 使用mysqldump的备份进行还原

下面使用 mysqldump 进行一个备份，然后删除 datadir, 然后使用备份sql脚本和binary log进行还原的过程。

1）首先进行一个全备：

mysqldump -uroot -p --single-transaction --master-data=2 --routines --flush-logs --databases gs ngx_lua > gs_ngx_lua_backup.sql;

数据库有两个库： gs , ngx_lua.

2）将备份时刷新之后的 binary log 利用 mv 命令移动到安全的位置，也就是--master-data=2输出的日志文件，它之前的日志文件都存储到安全的位置：

[root@localhost ~]# head -n 50 gs_ngx_lua_backup.sql
-- MySQL dump 10.13  Distrib 5.6.26, for linux-glibc2.5 (i686)
--
-- Host: localhost    Database: gs
... ...--
-- Position to start replication or point-in-time recovery from
---- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000027', MASTER_LOG_POS=120;

也就是将 MASTER_LOG_FILE='mysql-bin.000027' 之前的日志都存储到其它位置。

然后执行：purge binary logs to 'mysql-bin.000027' 更新了 mysql-bin.index 中的索引信息，这里并没有删除binary log，因为它们已经被mv走了。

3）下面模拟一个增量备份：

mysql> delete from user where id=5;
Query OK, 1 row affected (0.02 sec)mysql> select * from user;
+----+----------+------+-------------+----------+
| id | name     | sex  | phone       | addr     |
+----+----------+------+-------------+----------+
|  1 | yuanfang |    1 | 14465463786 | hangzhou |
|  2 | Andy     |    0 | 14465463786 | beijing  |
|  3 | peter    |    0 | 14465463786 | Hongkong |
|  4 | lexin    |    1 | 36565634    | shenzhen |
+----+----------+------+-------------+----------+
4 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.11 sec)mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000027 |       370 |
| mysql-bin.000028 |       120 |
+------------------+-----------+
2 rows in set (0.00 sec)

这里 flush logs 进行增量备份，然后将增量备份的 bianry log 文件 mysql-bin.000027 也存储起来。

然后在进行一条 delete 语句：

mysql> select * from user;
+----+----------+------+-------------+----------+
| id | name     | sex  | phone       | addr     |
+----+----------+------+-------------+----------+
|  1 | yuanfang |    1 | 14465463786 | hangzhou |
|  2 | Andy     |    0 | 14465463786 | beijing  |
|  3 | peter    |    0 | 14465463786 | Hongkong |
+----+----------+------+-------------+----------+
3 rows in set (0.00 sec)

到这里数据库的最新状态是：user 表只有3条记录。

然后我们同样将 mysql-bin.000028 也存储起来。

4）然后我们删除 data-dir 目录中的所有文件，然后开始还原：

[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# mv ./* /backup/before_delete/

此时 data-dir 目录是空的。然后我们试图使用 mysqladmin -uroot -p shutdown 来关闭mysqld，发现早不到 mysql.sock 无法关闭，我们只好使用

killall mysqld

来关闭。

5）然后进行数据库的初始化，然后开始还原：

进入到 /usr/local/mysql/script 目录，然后执行初始化：

./mysql_install_db --user=mysql --datadir=/var/lib/mysql --basedir=/usr/local/mysql

[root@localhost scripts]# ./mysql_install_db  --user=mysql --datadir=/var/lib/mysql --basedir=/usr/local/mysql
Installing MySQL system tables...2015-10-24 16:45:19 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.26-log) starting as process 5870 .. .
2015-10-24 16:45:19 5870 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2015-10-24 16:45:19 5870 [Note] InnoDB: The InnoDB memory heap is disabled
2015-10-24 16:45:19 5870 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2015-10-24 16:45:19 5870 [Note] InnoDB: Memory barrier is not used
2015-10-24 16:45:19 5870 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-10-24 16:45:19 5870 [Note] InnoDB: Using Linux native AIO
2015-10-24 16:45:19 5870 [Note] InnoDB: Not using CPU crc32 instructions
2015-10-24 16:45:19 5870 [Note] InnoDB: Initializing buffer pool, size = 6.0M
2015-10-24 16:45:19 5870 [Note] InnoDB: Completed initialization of buffer pool
2015-10-24 16:45:20 5870 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2015-10-24 16:45:20 5870 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2015-10-24 16:45:20 5870 [Note] InnoDB: Database physically writes the file full: wait...
2015-10-24 16:45:20 5870 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2015-10-24 16:45:21 5870 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2015-10-24 16:45:23 5870 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2015-10-24 16:45:23 5870 [Warning] InnoDB: New log files created, LSN=45781
2015-10-24 16:45:23 5870 [Note] InnoDB: Doublewrite buffer not found: creating new
2015-10-24 16:45:23 5870 [Note] InnoDB: Doublewrite buffer created
2015-10-24 16:45:23 5870 [Note] InnoDB: 128 rollback segment(s) are active.
2015-10-24 16:45:23 5870 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-10-24 16:45:23 5870 [Note] InnoDB: Foreign key constraint system tables created
2015-10-24 16:45:23 5870 [Note] InnoDB: Creating tablespace and datafile system tables.
2015-10-24 16:45:23 5870 [Note] InnoDB: Tablespace and datafile system tables created.
2015-10-24 16:45:23 5870 [Note] InnoDB: Waiting for purge to start
2015-10-24 16:45:23 5870 [Note] InnoDB: 5.6.26 started; log sequence number 0
2015-10-24 16:45:28 5870 [Note] Binlog end
2015-10-24 16:45:28 5870 [Note] InnoDB: FTS optimize thread exiting.
2015-10-24 16:45:28 5870 [Note] InnoDB: Starting shutdown...
2015-10-24 16:45:30 5870 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OKFilling help tables...2015-10-24 16:45:31 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.26-log) starting as process 5893 ...
2015-10-24 16:45:31 5893 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2015-10-24 16:45:31 5893 [Note] InnoDB: The InnoDB memory heap is disabled
2015-10-24 16:45:31 5893 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2015-10-24 16:45:31 5893 [Note] InnoDB: Memory barrier is not used
2015-10-24 16:45:31 5893 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-10-24 16:45:31 5893 [Note] InnoDB: Using Linux native AIO
2015-10-24 16:45:31 5893 [Note] InnoDB: Not using CPU crc32 instructions
2015-10-24 16:45:31 5893 [Note] InnoDB: Initializing buffer pool, size = 6.0M
2015-10-24 16:45:31 5893 [Note] InnoDB: Completed initialization of buffer pool
2015-10-24 16:45:31 5893 [Note] InnoDB: Highest supported file format is Barracuda.
2015-10-24 16:45:32 5893 [Note] InnoDB: 128 rollback segment(s) are active.
2015-10-24 16:45:32 5893 [Note] InnoDB: Waiting for purge to start
2015-10-24 16:45:32 5893 [Note] InnoDB: 5.6.26 started; log sequence number 1625977
2015-10-24 16:45:34 5893 [Note] Binlog end
2015-10-24 16:45:34 5893 [Note] InnoDB: FTS optimize thread exiting.
2015-10-24 16:45:34 5893 [Note] InnoDB: Starting shutdown...
2015-10-24 16:45:35 5893 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OKTo start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:/usr/local/mysql/bin/mysqladmin -u root password 'new-password'/usr/local/mysql/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'Alternatively you can run:/usr/local/mysql/bin/mysql_secure_installationwhich will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.See the manual for more instructions.You can start the MySQL daemon with:cd . ; /usr/local/mysql/bin/mysqld_safe &You can test the MySQL daemon with mysql-test-run.plcd mysql-test ; perl mysql-test-run.plPlease report any problems at http://bugs.mysql.com/The latest information about MySQL is available on the web athttp://www.mysql.comSupport MySQL by buying support/licenses at http://shop.mysql.comWARNING: Found existing config file /usr/local/mysql/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/local/mysql/my-new.cnf,
please compare it with your file and take the changes you need.WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server[root@localhost scripts]#

./mysql_install_db

初始化成功之后，执行：

/usr/local/mysql/bin/mysql_secure_installation

来进行密码设置。这一步可能会报错：找不到 /tmp/mysql.sock 文，解决方法，在/etc/my.cnf 中的[client], [mysql], [mysqld] 都进行下面的设置：

socket=/tmp/mysql.sock

然后重新执行：/usr/local/mysql/bin/mysql_secure_installation 就行了。

初始化完成之后，我们使用备份的sql脚本来进行恢复：

[root@localhost ~]# mysql -uroot -p < gs_ngx_lua_backup.sql

执行完成之后，发现 gs 和 ngx_lua 数据库都还原出来了，但是他们的数据不是最新的数据：

mysql> use gs;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select * from user;
+----+----------+------+----------------+----------+
| id | name     | sex  | phone          | addr     |
+----+----------+------+----------------+----------+
|  1 | yuanfang |    1 | 14465463786    | hangzhou |
|  2 | Andy     |    0 | 14465463786    | beijing  |
|  3 | peter    |    0 | 14465463786    | Hongkong |
|  4 | lexin    |    1 | 36565634       | shenzhen |
|  5 | lexin2   |    1 | 43563436565634 | aomao    |
+----+----------+------+----------------+----------+
5 rows in set (0.01 sec)

这是因为，我们还没有使用 mysql-bin.000027 和 mysql-bin.000028 两个binary log。mysql-bin.000027 是我们前面模拟的增量备份，而mysql-bin.000028 是删除data-dir目录时，最新的binary log。依次应用了这两个binary log之后，数据库才能恢复到最新的状态。

6）应用 binary log:

[root@localhost backup]# pwd
/backup
[root@localhost backup]# mysqlbinlog mysql-bin.000027 > 27.sql
[root@localhost backup]# mysqlbinlog mysql-bin.000028 > 28.sql

mysqlbinlog mysql-bin.000027 > 27.sql 得到了 27.sql 和 28.sql 之后，使用 mysql 来执行：

mysql -uroot -p < 27.sql; 应用了增量备份的binary log.

然后查看数据：

mysql> mysql> select * from user;
+----+----------+------+-------------+----------+
| id | name     | sex  | phone       | addr     |
+----+----------+------+-------------+----------+
|  1 | yuanfang |    1 | 14465463786 | hangzhou |
|  2 | Andy     |    0 | 14465463786 | beijing  |
|  3 | peter    |    0 | 14465463786 | Hongkong |
|  4 | lexin    |    1 | 36565634    | shenzhen |
+----+----------+------+-------------+----------+
4 rows in set (0.00 sec)

然应用 28.sql ：

mysql -uroot -p < 28.sql;

得到最新的状态：

mysql> select * from user;
+----+----------+------+-------------+----------+
| id | name     | sex  | phone       | addr     |
+----+----------+------+-------------+----------+
|  1 | yuanfang |    1 | 14465463786 | hangzhou |
|  2 | Andy     |    0 | 14465463786 | beijing  |
|  3 | peter    |    0 | 14465463786 | Hongkong |
+----+----------+------+-------------+----------+
3 rows in set (0.00 sec)

可以看到，成功的还原到了删除 data-dir 目录之前的状态了。

5. mysqldump的坑

如果对 mysqldump 的默认启用的选项不熟悉的话，可能会被默认启用的选项 --add-drop-table 给坑了。因为默认会生成 drop table if exist 语句。可能会导致数据的丢失。 --add-drop-database 默认没有启用。如果不想他生成 drop table 语句，可以加入 --skip-add-drop-table选项，或者 --add-ropt-table=0也可以。

6. 总结：

1）逻辑备份的最佳方法：

全备：

mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --databases db1 db2 db3 > alldb.sql;

mysqldump -uxxx -p --flush-privileges --databases mysql > mysql.sql;

如果将mysql也一起备份的话：

mysqldump -uxxx -p --single-transaction --master-data=2 --routines --flush-logs --flush-privileges --all-databases > alldb.sql;

有时，还需要加入：--default-character-set=utf8/utf8mb4 ，该选项一般也可以配置在/etc/my.cnf中。

增量备份：flush logs; 然后将binary log存储起来即可。

2）搭建slave时的最佳选项：

mysqldump -uxxx -p --single-transaction --master-data=2 --routines --databases db1 db2 db3 > alldb.sql;

搭建slave，没有必要 --flush-logs。当然搭建slave的最佳方式是使用 xtrabackup，物理备份。