Mysql Backup and Recovery
一、Mysqldump备份
开启二进制:
[mysqld]
log-bin=mysql-bin
server_id=1
binlog_format=row
1.全备
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full.sql
mysqldump -uroot -p -A -R -E --triggers --flush-logs --master-data=2 --single-transaction > /tmp/full.sql
mysqldump -uroot -p -A -R -E --triggers --flush-logs --set-gtid-purged=off --master-data=2 --single-transaction >/tmp/full.sql
可使用mysql_config_editor绕过密码输入:
[root@db01 ~]# mysql_config_editor set --login-path=root --user=root --password --host=localhost
[root@db01 ~]# mysql_config_editor print --all
[client]
[root]
user = root
password = *****
host = localhost
[root@db01 ~]# mysqldump --login-path=root -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full.sql2.单库备份
mysqldump --login-path=root -B -R -E --triggers --master-data=2 --single-transaction mydb > /tmp/mydb.sql3.单表备份
mysqldump --login-path=root --tables -R -E --triggers --master-data=2 --single-transaction mydb t t1> /tmp/mydb.sql
或
mysqldump --login-path=root -R -E --triggers --master-data=2 --single-transaction mydb t t1> /tmp/mydb.sql4.备份表结构
mysqldump --login-path=root -R -E --triggers --no-data --master-data=2 --single-transaction mydb t t1> /tmp/mydb.sql5.只备数据不备表结构
mysqldump --login-path=root -R -E --triggers -t --master-data=2 --single-transaction mydb t t1> /tmp/mydb.sql6.导数前切换日志
mysqldump --login-path=root -R -E --triggers --flush-logs --master-data=2 --single-transaction mydb t t1> /tmp/mydb.sql二、Mysqldump参数
参数说明:
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected
-B, --databases Dump several databases. Note the difference in usage; in
this case no tables are given. All name arguments are
regarded as database names. 'USE db_name;' will be
included in the output.
-E, --events Dump events.
--log-error=name Append warnings and errors to given file.
-R, --routines Dump stored routines (functions and procedures).
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
--tables Overrides option --databases (-B).
--triggers Dump triggers for each dumped table.
(Defaults to on; use --skip-triggers to disable.)
-l, --lock-tables Lock all tables for read.
(Defaults to on; use --skip-lock-tables to disable.)
--max-allowed-packet=#
The maximum packet length to send to or receive from
server.
-i, --comments Write additional information.
(Defaults to on; use --skip-comments to disable.)
--add-drop-database Add a DROP DATABASE before each create.
--add-drop-table Add a DROP TABLE before each create.
(Defaults to on; use --skip-add-drop-table to disable.)
--add-drop-trigger Add a DROP TRIGGER before each create.
-t, --no-create-info
Don't write table creation info.
-d, --no-data No row information.
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables
off.
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped. The exception is when
using --lock-all-tables or --master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use --lock-all-tables or --master-data with
--flush-logs.
三、恢复
1.全库恢复
全库备份:
[root@db01 ~]# mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full.sql
全库恢复:
[root@db01 ~]# mysql -uroot -p < /tmp/full.sql
或
mysql> source /tmp/full.sql如果只想恢复某一个库，需添加--one-database参数恢复单库。
2.单库恢复
方法一:使用--one-database恢复单库
[root@db01 ~]# mysql --login-path=root --one-database mydb < /tmp/full.sql
或
[root@db01 ~]# mysql --login-path=root -D mydb -o < /tmp/full.sql
如果mydb已经被drop,恢复会提示数据库不存在:
[root@db01 ~]# mysql --login-path=root --one-database mydb < /tmp/full.sql
ERROR 1049 (42000): Unknown database 'mydb'
解决办法:
从备份中找出创建mydb数据库语句，然后手工创建，然后重新导入:
[root@db01 ~]# grep -i "^create database" /tmp/full.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
[root@db01 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 66
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
Query OK, 1 row affected (0.00 sec)
[root@db01 ~]# mysql --login-path=root --one-database mydb < /tmp/full.sql
mysql> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| t |
| t1 |
| test |
+----------------+
3 rows in set (0.00 sec)
方法二:从备份文件中提取单库数据恢复
(1) 生成mydb脚本
[root@db01 ~]# sed -n '/^-- Current Database: `mydb`/,/^-- Current Database: `/p' /tmp/full.sql > /tmp/mydb.sql
(2) 添加时区
[root@db01 ~]# vim /tmp/mydb.sql
-- Current Database: `mydb`
--
set session TIME_ZONE='+00:00';
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `mydb`;
......
(3) 恢复mydb数据库
mysql> source /tmp/mydb.sql3.单表恢复
(1) 生成建表语句
[root@db01 ~]# cat /tmp/full.sql | sed -n -e '/^CREATE DATABASE.*`mydb`/,/^CREATE DATABASE/ p' | sed -e '$d' | sed -n '/-- Table structure for table `t`/,/UNLOCK TABLES;/p' > /tmp/create_table_t.sql
或
[root@db01 ~]# cat /tmp/full.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `test_tb`/!d;q' > /tmp/create_table_t.sql
[root@db01 ~]# cat /tmp/full.sql | grep --ignore-case 'insert into `test_tb`' > /tmp/table_t_data.sql
(2) 添加时区
-- Table structure for table `t`
--
set session TIME_ZONE='+00:00';
DROP TABLE IF EXISTS `t`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
......
(3) 恢复t表
mysql> use mydb
mysql> source /tmp/create_table_t.sql
4.Mysqlbinlog Position恢复
恢复步骤: 先使用全库备份进行恢复，然后再使用二进制日志恢复全备之后产生的数据
(1) 全备
[root@db01 ~]# mysqldump -uroot -p -A -R -E --triggers --flush-logs --master-data=2 --single-transaction > /tmp/full.sql
(2) 模拟数据丢失
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+------+
7 rows in set (0.00 sec)
mysql> insert into t values (8);
Query OK, 1 row affected (0.00 sec)
mysql> delete from t;
Query OK, 8 rows affected (0.00 sec)
mysql> flush logs;
mysql> insert into t values (9);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+------+
| id |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
mysql> drop table t;
(3) 将表恢复drop之前状态
查找drop之前的lsn开始及结束位置:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000021 | 585 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in '/data/mysql/data/mysql-bin.000021';
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------+
| mysql-bin.000021 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000021 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000021 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000021 | 219 | Query | 6 | 291 | BEGIN |
| mysql-bin.000021 | 291 | Table_map | 6 | 335 | table_id: 922 (mydb.t) |
| mysql-bin.000021 | 335 | Write_rows | 6 | 375 | table_id: 922 flags: STMT_END_F |
| mysql-bin.000021 | 375 | Xid | 6 | 406 | COMMIT /* xid=10383 */ |
| mysql-bin.000021 | 406 | Anonymous_Gtid | 6 | 471 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000021 | 471 | Query | 6 | 585 | use `mydb`; DROP TABLE `t` /* generated by server */ |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------+
9 rows in set (0.00 sec)
mysql> show binlog events in '/data/mysql/data/mysql-bin.000020';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000020 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000020 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000020 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000020 | 219 | Query | 6 | 291 | BEGIN |
| mysql-bin.000020 | 291 | Table_map | 6 | 335 | table_id: 922 (mydb.t) |
| mysql-bin.000020 | 335 | Write_rows | 6 | 375 | table_id: 922 flags: STMT_END_F |
| mysql-bin.000020 | 375 | Xid | 6 | 406 | COMMIT /* xid=10380 */ |
| mysql-bin.000020 | 406 | Anonymous_Gtid | 6 | 471 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000020 | 471 | Query | 6 | 543 | BEGIN |
| mysql-bin.000020 | 543 | Table_map | 6 | 587 | table_id: 922 (mydb.t) |
| mysql-bin.000020 | 587 | Delete_rows | 6 | 662 | table_id: 922 flags: STMT_END_F |
| mysql-bin.000020 | 662 | Xid | 6 | 693 | COMMIT /* xid=10381 */ |
| mysql-bin.000020 | 693 | Rotate | 6 | 740 | mysql-bin.000021;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
13 rows in set (0.00 sec)
导出数据:
[root@db01 ~]# mysqlbinlog -uroot -p --start-position=219 --stop-position=693 /data/mysql/data/mysql-bin.000020 > /tmp/000020.sql
[root@db01 ~]# mysqlbinlog -uroot -p --start-position=219 --stop-position=471 /data/mysql/data/mysql-bin.000021 > /tmp/000021.sql
先从全备中恢复t表:
[root@db01 ~]# mysql -uroot -p --one-database mydb < /tmp/full.sql
此步不理想，对mydb的其它表都做了恢复操作，最好使用full.sql脚本中提取t表数据，然后再导入
再从二进制日志中恢复全备后产生的数据:
mysql> use mydb
mysql> source /tmp/000020.sql
mysql> source /tmp/000021.sql
mysql> select * from t;
+------+
| id |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
5.Mysqlbinlog GTID恢复
GTID开启:
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1
(1) 全备
[root@db01 ~]# mysqldump --user=root -p -A -R -E --triggers --flush-logs --master-data=2 --single-transaction > /tmp/full.sql
(2) 模拟数据丢失
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+------+
7 rows in set (0.00 sec)
mysql> insert into t values (8);
Query OK, 1 row affected (0.00 sec)
mysql> delete from t;
Query OK, 8 rows affected (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values (9);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+------+
| id |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
mysql> drop table t;
Query OK, 0 rows affected (0.01 sec)
(3) 将表恢复至drop前状态，同时恢复误delete数据
查看drop前gtid:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000015 | 625 | | | d5d29a37-ace6-11eb-9949-000c29bdd3e5:1-38 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> show binlog events in '/data/mysql/data/mysql-bin.000015';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000015 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000015 | 123 | Previous_gtids | 6 | 194 | d5d29a37-ace6-11eb-9949-000c29bdd3e5:1-36 |
| mysql-bin.000015 | 194 | Gtid | 6 | 259 | SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:37' |
| mysql-bin.000015 | 259 | Query | 6 | 331 | BEGIN |
| mysql-bin.000015 | 331 | Table_map | 6 | 375 | table_id: 172 (mydb.t) |
| mysql-bin.000015 | 375 | Write_rows | 6 | 415 | table_id: 172 flags: STMT_END_F |
| mysql-bin.000015 | 415 | Xid | 6 | 446 | COMMIT /* xid=1321 */ |
| mysql-bin.000015 | 446 | Gtid | 6 | 511 | SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:38' |
| mysql-bin.000015 | 511 | Query | 6 | 625 | use `mydb`; DROP TABLE `t` /* generated by server */ |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
9 rows in set (0.00 sec)
mysql> show binlog events in '/data/mysql/data/mysql-bin.000014';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000014 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000014 | 123 | Previous_gtids | 6 | 194 | d5d29a37-ace6-11eb-9949-000c29bdd3e5:1-34 |
| mysql-bin.000014 | 194 | Gtid | 6 | 259 | SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:35' |
| mysql-bin.000014 | 259 | Query | 6 | 331 | BEGIN |
| mysql-bin.000014 | 331 | Table_map | 6 | 375 | table_id: 172 (mydb.t) |
| mysql-bin.000014 | 375 | Write_rows | 6 | 415 | table_id: 172 flags: STMT_END_F |
| mysql-bin.000014 | 415 | Xid | 6 | 446 | COMMIT /* xid=1318 */ |
| mysql-bin.000014 | 446 | Gtid | 6 | 511 | SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:36' |
| mysql-bin.000014 | 511 | Query | 6 | 583 | BEGIN |
| mysql-bin.000014 | 583 | Table_map | 6 | 627 | table_id: 172 (mydb.t) |
| mysql-bin.000014 | 627 | Delete_rows | 6 | 702 | table_id: 172 flags: STMT_END_F |
| mysql-bin.000014 | 702 | Xid | 6 | 733 | COMMIT /* xid=1319 */ |
| mysql-bin.000014 | 733 | Rotate | 6 | 780 | mysql-bin.000015;pos=4 |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
13 rows in set (0.00 sec)
从全备文件/tmp/full.sql中 SET @@GLOBAL.GTID_PURGED='d5d29a37-ace6-11eb-9949-000c29bdd3e5:1-34'，可知1-34事务已全备
从binlog分析可知，GTID:35和GTID:37需要恢复的数据，而GTID:36是delete数据，需要跳过此步:
SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:35'
SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:37'
SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:36'
导出数据:
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids=d5d29a37-ace6-11eb-9949-000c29bdd3e5:35-37 --exclude-gtids=d5d29a37-ace6-11eb-9949-000c29bdd3e5:36 /data/mysql/data/mysql-bin.000014 /data/mysql/data/mysql-bin.000015 > /tmp/gtid.sql
先从全备中恢复t表:
[root@db01 ~]# mysql -uroot -p --one-database mydb < /tmp/full.sql
Enter password:
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
解决办法:
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@db01 ~]# mysql -uroot -p --one-database mydb < /tmp/full.sql
再从二进制日志中恢复全备后产生的数据:
mysql> use mydb
mysql> source /tmp/gtid.sql
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+------+
9 rows in set (0.00 sec)