备份与恢复使用的命令
mysqldump
常用选项
- -A, --all-databases：导出全部数据库
- -B, --databases：导出几个数据库。参数后面所有名字参量都被看作数据库名。
- --dump-slave[=#]：该选项将导致主的binlog位置和文件名追加到导出数据的文件中。设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，在命令前增加说明信息。该选项将会打开--lock-all-tables，除非--single-transaction被指定。该选项会自动关闭--lock-tables选项。默认值为0。
- -f, --force：在导出过程中忽略出现的SQL错误。
- -h, --host=name：需要导出的主机信息
- --ignore-table=name：不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
- --include-master-host-port：在--dump-slave产生的'CHANGE MASTER TO..'语句中增加'MASTER_HOST=
，MASTER_PORT= ' 。 - -x, --lock-all-tables：提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
- -l, --lock-tables：开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。请注意当导出多个数据库时，-–lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
- --log-error=name：附加警告和错误信息到给定文件。
- --master-data[=#]：该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables选项。
- -d, --no-data：不导出任何数据，只导出数据库表结构。
- -p, --password[=name]：连接数据库密码
- -P, --port=#：连接数据库端口号
- -R, --routines：导出存储过程以及自定义函数。
- --single-transaction：该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。
- -S, --socket=name：指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock。
- --tables：覆盖--databases (-B)参数，指定需要导出的表名。
- -u, --user=name：指定连接的用户名。
- --set-gtid-purged=[=name]：将“SET @@ GLOBAL.GTID_PURGED”添加到输出。 可能此选项的值为ON，OFF和AUTO。如果使用ON，并且服务器上未启用GTID，则会生成错误。 如果使用OFF，则此选项不起作用。如果使用AUTO并且在服务器上启用了GTID，则将“SET @@ GLOBAL.GTID_PURGED”添加到输出。 如果禁用GTID，AUTO不起作用。 如果没有提供值，则将考虑默认（AUTO）值。
上面的一些参数中处理必须的连接登录信息，其中--single-transaction对InnoDB存储引擎的表进行热备是必须添加的，如果是MyISAM存储引擎会有可能出现表锁，所以建议如果有MyISAM存储引擎的表的话备份还是在slave 主机。实际上如果备份还是在slave上进行。剩下有几个参数需要做举例说明：
--dump-slave与--master-data选项
master上执行
备份前日志信息如下：
(root@localhost) [(none)]> show master status;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000006 | 1772 | | | 7e221f74-a1b6-11e7-b6b1-000c299c7a30:1-203 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)
执行如下备份命令：
# mysqldump -A --set-gtid-purged=OFF --master-data=1 >master1.sql
# mysqldump -A --set-gtid-purged=OFF --master-data=2 >master2.sql
# mysqldump -A --set-gtid-purged=OFF --dump-slave=2 >slave2.sql
mysqldump: Couldn't execute 'START SLAVE': The server is not configured as slave; fix in config file or with CHANGE MASTER TO (1200)
# mysqldump -A --set-gtid-purged=OFF --dump-slave=1 >slave1.sql
mysqldump: Couldn't execute 'START SLAVE': The server is not configured as slave; fix in config file or with CHANGE MASTER TO (1200)
由于是master所以没有slave上的信息故在执行时报错。
查看备份文件对比：
# diff master1.sql master2.sql
22c22
< CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=2018;
---
> -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=2018;
945c945
< -- Dump completed on 2017-09-26 16:56:56
---
> -- Dump completed on 2017-09-26 16:56:58
# diff slave1.sql slave2.sql
备份后的日志状态：
(root@localhost) [(none)]> show master status;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000006 | 2018 | | | 7e221f74-a1b6-11e7-b6b1-000c299c7a30:1-205 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)
slave上执行
备份前查看日志信息：
(root@localhost) [(none)]> show master statusG
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 2769
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 7e221f74-a1b6-11e7-b6b1-000c299c7a30:1-205,
9d3f94b2-a1b7-11e7-b6b8-000c29415751:1-56
1 row in set (0.00 sec)
(root@localhost) [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.197.61
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 2018
Relay_Log_File: mysql-relay-bin.000010
Relay_Log_Pos: 2228
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2018
Relay_Log_Space: 2682
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 61
Master_UUID: 7e221f74-a1b6-11e7-b6b1-000c299c7a30
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7e221f74-a1b6-11e7-b6b1-000c299c7a30:6-205
Executed_Gtid_Set: 7e221f74-a1b6-11e7-b6b1-000c299c7a30:1-205,
9d3f94b2-a1b7-11e7-b6b8-000c29415751:1-56
Auto_Position: 1
1 row in set (0.00 sec)
备份命令和前面在master上执行的一样，下面看下在slave上备份的差别：
# diff master1.sql master2.sql
22c22
< CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=3015;
---
> -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=3015;
988c988
< -- Dump completed on 2017-09-26 17:00:21
---
> -- Dump completed on 2017-09-26 17:00:22
# diff slave1.sql slave2.sql
22c22
< CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=2018;
---
> -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=2018;
# diff master1.sql slave1.sql
19c19
< -- Position to start replication or point-in-time recovery from
---
> -- Position to start replication or point-in-time recovery from (the master of this slave)
22c22
< CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=3015;
---
> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=2018;
# diff master2.sql slave2.sql
19c19
< -- Position to start replication or point-in-time recovery from
---
> -- Position to start replication or point-in-time recovery from (the master of this slave)
22c22
< -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=3015;
---
> -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=2018;
988c988
< -- Dump completed on 2017-09-26 17:00:22
---
> -- Dump completed on 2017-09-26 17:00:21
执行备份之后的日志信息：
(root@localhost) [(none)]> show master statusG
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 3015
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 7e221f74-a1b6-11e7-b6b1-000c299c7a30:1-205,
9d3f94b2-a1b7-11e7-b6b8-000c29415751:1-58
1 row in set (0.00 sec)
(root@localhost) [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.197.61
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 2018
Relay_Log_File: mysql-relay-bin.000010
Relay_Log_Pos: 2228
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2018
Relay_Log_Space: 2682
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 61
Master_UUID: 7e221f74-a1b6-11e7-b6b1-000c299c7a30
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7e221f74-a1b6-11e7-b6b1-000c299c7a30:6-205
Executed_Gtid_Set: 7e221f74-a1b6-11e7-b6b1-000c299c7a30:1-205,
9d3f94b2-a1b7-11e7-b6b8-000c29415751:1-58
Auto_Position: 1
1 row in set (0.00 sec)
总结
1.--dump-slave与--master-data等于1是将备份时binlog文件以及pos位置信息直接显示出来，而等于2时就是将该语句给注释掉了。
2.--master-data所指定的binlog是备份所在数据库的binlog，而--dump-slave所指定的binlog是备份所在数据库master上的binlog位置。
3.使用--dump-slave与--master-data参数备份和恢复是配套的，例如如果使用--dump-slave参数进行备份则恢复是需要master上的binlog进行恢复。
mysqlbinlog
常用选项
- --base64-output=name：确定输出语句何时应该是base64-encoded的BINLOG语句：'never'禁用它，只适用于没有基于行的事件的binlog;
如果还提供了--verbose选项，'decode-rows'将行事件解码为已注释的伪SQL语句 'auto'仅在必要时打印base64。 如果没有--base64-output [= name]选项，则默认为'auto'。 - -d, --database=name：只列出该数据库的条目(只适用本地日志)。
- -f, --force-read：使用该选项，如果mysqlbinlog读它不能识别的二进制日志事件，它会打印警告，忽略该事件并继续。没有该选项，如果mysqlbinlog读到此类事件则停止。
- -h, --host=name：获取给定主机上的MySQL服务器的二进制日志。
- -l, --local-load=name：为指定目录中的LOAD DATA INFILE预处理本地临时文件。
- -o, --offset=# ：跳过前N个条目。
- -p, --password[=name]：当连接服务器时使用的密码。如果使用短选项形式(-p)，选项和密码之间不能有空格。 如果在命令行中–password或-p选项后面没有密码值，则提示输入一个密码。
- -P, --port=# ：用于连接远程服务器的TCP/IP端口号。
- --start-datetime=name：在第一个事件处开始读取二进制日志，其中datetime等于或晚于参数; 参数必须是本地时区的日期和时间，以DATETIME和TIMESTAMP类型的MySQL服务器接受的任何格式，例如：2004-12-25 11:25:56（您应该可以使用引号将您的shell 正确设置）。
- --stop-datetime=name：在第一个事件处停止读取二进制日志，其中datetime等于或晚于参数; 参数必须是本地时区的日期和时间，以DATETIME和TIMESTAMP类型的MySQL服务器接受的任何格式，例如：2004-12-25 11:25:56（您应该可以使用引号将您的shell 正确设置）。
- -j, --start-position=# ：开始读取位置N的binlog。适用于在命令行上传递的第一个binlog。
- --stop-position=#：停止读取位置N的binlog。适用于在命令行上传递的最后一个binlog。
- -u, --user=name：连接远程服务器时使用的MySQL用户名。
- -v, --verbose：用于输出基于row模式的binlog日志，-vv为列数据类型添加注释。
xtrabackup备份
备份
关于xtrabackup这边不做详细介绍，只是将常用的备份选项：
innobackupex --compress --compress-threads=8 --stream=xbstream --databases-exclude=test --user=XXXXX --password=XXXXX --socket=/var/lib/mysql/mysql.sock --parallel=8 ${targetdir} > target.xbstream
恢复
1.下载qpress rpm包安装
centos 6：
http://repo.percona.com/centos/6Server/os/x86_64/qpress-11-1.el6.x86_64.rpm
centos 7：
http://repo.percona.com/centos/7Server/os/x86_64/qpress-11-1.el7.x86_64.rpm
- 解压恢复数据
# mkdir restore
# mv target.xbstream restore/
# cd restore/
# /opt/percona-xtrabackup-2.4.7-Linux-x86_64/bin/xbstream -x < 2017-06-27.xbstream
# for f in `find ./ -iname "*.qp"`;do qpress -dT4 $f $(dirname $f) && rm -f $f;done
# /opt/percona-xtrabackup-2.4.7-Linux-x86_64/bin/innobackupex --apply-log ./
......
最后输出： completed OK!
这个只能恢复到备份的时间点数据，剩下的就需要基于binlog进行恢复了。
关于备份时binlog时记录的信息可以查看xtrabackup_binlog_info 和xtrabackup_binlog_pos_innodb 信息。