MySQL——备份还原

KFC质检员

已于 2023-11-03 15:49:29 修改

阅读量930

点赞数

分类专栏：数据库文章标签： mysql

于 2021-03-18 20:41:37 首次发布

本文链接：https://blog.csdn.net/weixin_45373345/article/details/113036786

版权

数据库专栏收录该内容

26 篇文章 0 订阅

订阅专栏

查看数据库实例所有数据库磁盘占用情况

select concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size from information_schema.tables;

一、 mysqldump备份结合binlog日志恢复

MySQL备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在MySQL故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。

1、binlog介绍

mysql的二进制日志记录着该数据库的所有增删改的操作日志(前提是要在服务器上开启binlog)，还包括了这些操作的执行时间。为了显示这些二进制内容，可以使用mysqlbinlog命令来查看。

Binlog的用途

主从同步
恢复数据库

开启binary log功能

通过编辑my.cnf中的log-bin选项可以开启二进制日志：log-bin [=DIR/[filename]]

[root@mysql ~]# vim /etc/my.cnf
……
server_id = 1			#从MySQL 5.7.3以后版本必须配置server-id，否则无法启用MySQL二进制日志
log-bin = /usr/local/mysql/data/log-bin			#其中mysql-bin代表的是basename就是生成二进制日志文件的前缀部分,默认的位置在datadir目录下,也可以设置为其他的路径

chown -R mysql:mysql /usr/local/mysql/
[root@mysql ~]# systemctl restart mysqld			#重启mysql

（配置文件中只写log_bin不写后面的文件名和路径时，默认存放在/usr/local/mysql/data目录下，文件名为主机名-bin.000001…命名）其中，DIR参数指定二进制文件的存储路径； filename参数指定二进制文件的文件名，其形式为filename.number，number的形式为000001、000002等。每次重启mysql服务或运行 mysql> flush logs; 都会生成一个新的二进制日志文件，这些日志文件的number会不断地递增。除了生成上述的文件外还会生成一个名为filename.index的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引

查看bin-log是否开启，如下：mysql> show variables like ‘%log_bin%’;

mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| log_bin                         | ON                                  |
| log_bin_basename                | /usr/local/mysql/data/log-bin       |
| log_bin_index                   | /usr/local/mysql/data/log-bin.index |
| log_bin_trust_function_creators | OFF                                 |
| log_bin_use_v1_row_events       | OFF                                 |
| sql_log_bin                     | ON                                  |
+---------------------------------+-------------------------------------+

查看产生的binary log

注：查看binlog内容是为了恢复数据 bin-log因为是二进制文件，不能通过文件内容查看命令直接打开查看，mysql提供两种方式查看方式，先对数据库进行一下增删改的操作，否则log里边数据有点空。

[root@mysql ~]# mysql -uroot -p123 -e"reset master"
[root@mysql ~]# mysql -uroot -p123 -e"create database test123"
[root@mysql ~]# mysql -uroot -p123 test123 -e"create table tb1(id int primary key auto_increment,name varchar(20))"
[root@mysql ~]# mysql -uroot -p123 -e"insert into test123.tb1(name) values('lisi')"
[root@mysql ~]# mysql -uroot -p123 -e"insert into test123.tb1(name) values('zhangsan')"

重新开始一个新的日志文件

[root@mysql ~]# mysql -uroot -p123 -e"flush logs"
[root@mysql ~]# mysql -uroot -p123 -e"delete from test123.tb1 where id=2"
[root@mysql ~]# mysql -uroot -p123 -e"insert into test123.tb1(name) values('tom')"
[root@mysql ~]# mysql -uroot -p123 -e"select * from test123.tb1"
+----+------+
| id | name |
+----+------+
|  1 | lisi |
|  3 | tom  |
+----+------+

查看MySQL Server上的二进制日志

mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| log-bin.000001 |      1106 |
| log-bin.000002 |       679 |
+----------------+-----------+

查看二进制日志信息的命令：

语法格式：SHOW BINLOG EVENTS[IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]

查看二进制日志中的事件（右有解）

默认显示可找到的第一个二进制日志文件中的事件，包含了日志文件名、事件的开始位置、事件类型、结束位置、信息等内容

mysql> show binlog events;
+----------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| Log_name       | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                |
+----------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| log-bin.000001 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.32-log, Binlog ver: 4                                               |		此事件为格式描述事件
| log-bin.000001 |  123 | Previous_gtids |         1 |         154 |                                                                                     |
| log-bin.000001 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                |
| log-bin.000001 |  219 | Query          |         1 |         322 | create database test123                                                             |		为查询事件
| log-bin.000001 |  322 | Anonymous_Gtid |         1 |         387 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                |
| log-bin.000001 |  387 | Query          |         1 |         535 | use `test123`; create table tb1(id int primary key auto_increment,name varchar(20)) |
| log-bin.000001 |  535 | Anonymous_Gtid |         1 |         600 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                |
| log-bin.000001 |  600 | Query          |         1 |         668 | BEGIN                                                                               |		为查询事件，事务开始
| log-bin.000001 |  668 | Table_map      |         1 |         720 | table_id: 108 (test123.tb1)                                                         |		为表映射事件
| log-bin.000001 |  720 | Write_rows     |         1 |         765 | table_id: 108 flags: STMT_END_F                                                     |		执行的insert事件
| log-bin.000001 |  765 | Xid            |         1 |         796 | COMMIT /* xid=16 */                                                                 |		Xid时间是自动提交事务的动作
| log-bin.000001 |  796 | Anonymous_Gtid |         1 |         861 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                |
| log-bin.000001 |  861 | Query          |         1 |         929 | BEGIN                                                                               |
| log-bin.000001 |  929 | Table_map      |         1 |         981 | table_id: 108 (test123.tb1)                                                         |
| log-bin.000001 |  981 | Write_rows     |         1 |        1030 | table_id: 108 flags: STMT_END_F                                                     |
| log-bin.000001 | 1030 | Xid            |         1 |        1061 | COMMIT /* xid=19 */                                                                 |
| log-bin.000001 | 1061 | Rotate         |         1 |        1106 | log-bin.000002;pos=4                                                                |		为日志轮换事件，是执行flush logs开启新日志文件引起的
+----------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------+

查看指定的二进制日志中的事件

mysql> show binlog events in 'log-bin.000002';
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name       | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| log-bin.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| log-bin.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| log-bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| log-bin.000002 | 219 | Query          |         1 |         287 | BEGIN                                 |
| log-bin.000002 | 287 | Table_map      |         1 |         339 | table_id: 108 (test123.tb1)           |
| log-bin.000002 | 339 | Delete_rows    |         1 |         388 | table_id: 108 flags: STMT_END_F       |
| log-bin.000002 | 388 | Xid            |         1 |         419 | COMMIT /* xid=26 */                   |
| log-bin.000002 | 419 | Anonymous_Gtid |         1 |         484 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| log-bin.000002 | 484 | Query          |         1 |         552 | BEGIN                                 |
| log-bin.000002 | 552 | Table_map      |         1 |         604 | table_id: 108 (test123.tb1)           |
| log-bin.000002 | 604 | Write_rows     |         1 |         648 | table_id: 108 flags: STMT_END_F       |
| log-bin.000002 | 648 | Xid            |         1 |         679 | COMMIT /* xid=29 */                   |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+

该命令还包含其他选项以便灵活查看

mysql> show binlog events in 'log-bin.000002' from 219 limit 1,3;		//limit 1,1为偏移量
+----------------+-----+-------------+-----------+-------------+---------------------------------+
| Log_name       | Pos | Event_type  | Server_id | End_log_pos | Info                            |
+----------------+-----+-------------+-----------+-------------+---------------------------------+
| log-bin.000002 | 287 | Table_map   |         1 |         339 | table_id: 108 (test123.tb1)     |
| log-bin.000002 | 339 | Delete_rows |         1 |         388 | table_id: 108 flags: STMT_END_F |
| log-bin.000002 | 388 | Xid         |         1 |         419 | COMMIT /* xid=26 */             |
+----------------+-----+-------------+-----------+-------------+---------------------------------+

SHOW BINARY LOGS 等价于 SHOW MASTER LOGS PURGE BINARY LOGS用于删除二进制日志，如：
PURGE BINARY LOGS TO ‘mysql-bin.00010’; //把这个文件之前的其他文件都删除掉
PURGE BINARY LOGS BEFORE ‘2016-08-28 22:46:26’; //把指定时间之前的二进制文件删除了
RESET MASTER 与 RESET SLAVE 前者清空index文件中列出的所有二进制日志，重置index文件为空，并创建一个新的二进制日志文件，一般用于MASTER首次启动时。后者使SLAVE忘记其在MASTER二进制日志文件中的复制位置，它会删除master.info、relay-log.info 和所有中继日志文件并开始一个新的中继日志文件，以便于开始一个干净的复制。
在使用RESET SLAVE前需先关闭 SLAVE 复制线程。上述方式可以查看到服务器上存在的二进制日志文件及文件中的事件，但是想查看到文件中具体的内容并应于恢复场景还得借助mysqlbinlog这个工具。

mysqlbinlog 工具

语法格式： mysqlbinlog [options] log_file …
mysqlbinlog 常见的选项有以下几个：
- -start-datetime 从二进制日志中读取指定时间戳或者本地计算机时间之后的日志事件。
- -stop-datetime 从二进制日志中读取指定时间戳或者本地计算机时间之前的日志事件。
- -start-position 从二进制日志中读取指定position 事件位置作为开始。
- -stop-position 从二进制日志中读取指定position 事件位置作为事件截至。

输出内容会因日志文件的格式以及mysqlbinlog工具使用的选项不同而略不同。 mysqlbinlog 的可用选项可参考man手册。二进制日志文件的格式包含行模式、语句模式和混合模式（也即有服务器决定在什么情况下记录什么类型的日志），基于语句的日志中事件信息包含执行的语句等，基于行的日志中事件信息包含的是行的变化信息等。混合模式的日志中两种类型的事件信息都会记录。为了便于查看记录了行变化信息的事件在当时具体执行了什么样的SQL语句可以使用mysqlbinlog工具的-v（- -verbose）选项，该选项会将行事件重构成被注释掉的伪SQL语句，如果想看到更详细的信息可以将该选项给两次如-vv，这样可以包含一些数据类型和元信息的注释内容，如先切换到binlog所在的目录下

# mysqlbinlog mysql-bin.000001
# mysqlbinlog -v mysql-bin.000001
# mysqlbinlog -vv mysql-bin.000001

另外mysqlbinlog和可以通过- -read-from-remote-server选项从远程服务器读取二进制日志文件，这时需要一些额外的连接参数，如-h，-P，-p，-u等，这些参数仅在指定了–read-from-remote-server后有效。无论是本地二进制日志文件还是远程服务器上的二进制日志文件，无论是行模式、语句模式还是混合模式的二进制日志文件，被mysqlbinlog工具解析后都可直接应用与MySQL Server进行基于时间点、位置或数据库的恢复。下面就来演示如何使用binlog恢复之前删除数据（id=2那条记录）注意：在实际生产环境中，如果遇到需要恢复数据库的情况，不要让用户能访问到数据库，以避免新的数据插入进来，以及在主从的环境下，关闭主从。

查看binlog文件，从中找出delete from test.tb1 where id=2

[root@mysql ~]# cd /usr/local/mysql/data/
[root@mysql data]# mysqlbinlog -v log-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210125  1:28:16 server id 1  end_log_pos 123 CRC32 0xe79ad54d 	Start: binlog v 4, server v 5.7.32-log created 210125  1:28:16
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
MK4NYA8BAAAAdwAAAHsAAAABAAQANS43LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AU3Vmuc=
'/*!*/;
# at 123
#210125  1:28:16 server id 1  end_log_pos 154 CRC32 0x8f30c37b 	Previous-GTIDs
# [empty]
# at 154
#210125  1:29:19 server id 1  end_log_pos 219 CRC32 0x1e5b0eb2 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210125  1:29:19 server id 1  end_log_pos 287 CRC32 0x2296eb58 	Query	thread_id=10	exec_time=0	error_code=0
SET TIMESTAMP=1611509359/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 287
#210125  1:29:19 server id 1  end_log_pos 339 CRC32 0x4ae564c4 	Table_map: `test123`.`tb1` mapped to number 108
# at 339
#210125  1:29:19 server id 1  end_log_pos 388 CRC32 0x236ca6dd 	Delete_rows: table id 108 flags: STMT_END_F

BINLOG '
b64NYBMBAAAANAAAAFMBAAAAAGwAAAAAAAEAB3Rlc3QxMjMAA3RiMQACAw8CPAACxGTlSg==
b64NYCABAAAAMQAAAIQBAAAAAGwAAAAAAAEAAgAC//wCAAAACHpoYW5nc2Fu3aZsIw==
'/*!*/;
### DELETE FROM `test123`.`tb1`
### WHERE
###   @1=2
###   @2='zhangsan'
# at 388
#210125  1:29:19 server id 1  end_log_pos 419 CRC32 0xc5aaa64d 	Xid = 26
COMMIT/*!*/;
# at 419
#210125  1:29:39 server id 1  end_log_pos 484 CRC32 0x8bb697cc 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 484
#210125  1:29:39 server id 1  end_log_pos 552 CRC32 0xdb06796a 	Query	thread_id=11	exec_time=0	error_code=0
SET TIMESTAMP=1611509379/*!*/;
BEGIN
/*!*/;
# at 552
#210125  1:29:39 server id 1  end_log_pos 604 CRC32 0x40290fae 	Table_map: `test123`.`tb1` mapped to number 108
# at 604
#210125  1:29:39 server id 1  end_log_pos 648 CRC32 0x749da4a9 	Write_rows: table id 108 flags: STMT_END_F

BINLOG '
g64NYBMBAAAANAAAAFwCAAAAAGwAAAAAAAEAB3Rlc3QxMjMAA3RiMQACAw8CPAACrg8pQA==
g64NYB4BAAAALAAAAIgCAAAAAGwAAAAAAAEAAgAC//wDAAAAA3RvbamknXQ=
'/*!*/;
### INSERT INTO `test123`.`tb1`
### SET
###   @1=3
###   @2='tom'
# at 648
#210125  1:29:39 server id 1  end_log_pos 679 CRC32 0x48b65c9a 	Xid = 29
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

从中可以看出delete事件发生position是287，事件结束position是419 恢复流程：直接用bin-log日志将数据库恢复到删除位置287前，然后跳过故障点，再进行恢复下面所有的操作

由于之前没有做过全库备份，所以要使用所有binlog日志恢复，所以生产环境中需要很长时间恢复，导出相关binlog文件

[root@mysql data]# mysqlbinlog /usr/local/mysql/data/log-bin.000001 > /opt/log-bin.000001.sql
[root@mysql data]# mysqlbinlog --stop-position=287 /usr/local/mysql/data/log-bin.000002 > /opt/287.sql
[root@mysql data]# mysqlbinlog --start-position=419 /usr/local/mysql/data/log-bin.000002 > /opt/419.sql

删除test数据库

mysql> drop database test123;

利用binlog恢复数据

[root@mysql data]# mysql -uroot -p123 < /opt/log-bin.000001.sql 
[root@mysql data]# mysql -uroot -p123 < /opt/287.sql 
[root@mysql data]# mysql -uroot -p123 < /opt/419.sql

恢复完成后，检查下表的数据是否完整

mysql> select * from test123.tb1;
+----+----------+
| id | name     |
+----+----------+
|  1 | lisi     |
|  2 | zhangsan |
|  3 | tom      |
+----+----------+

练习：两insert一delete;两insert一delete;两insert一delete;

[root@mysql data]# mysql -uroot -p123 -e"flush logs"
[root@mysql data]# mysql -uroot -p123 -e"show binary logs"
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| log-bin.000001 |      1120 |
| log-bin.000002 |      2071 |
| log-bin.000003 |       154 |
+----------------+-----------+

[root@mysql data]# mysql -uroot -p123 -e"create database ooo"
[root@mysql data]# mysql -uroot -p123 ooo -e"create table tb1(id int primary key auto_increment,name varchar(20))"
[root@mysql data]# mysql -uroot -p123 -e"insert into ooo.tb1(name) values('tom1')"
[root@mysql data]# mysql -uroot -p123 -e"insert into ooo.tb1(name) values('tom2')"
[root@mysql data]# mysql -uroot -p123 -e"delete from ooo.tb1 where name='tom1'"
[root@mysql data]# mysql -uroot -p123 -e"insert into ooo.tb1(name) values('tom3')"
[root@mysql data]# mysql -uroot -p123 -e"insert into ooo.tb1(name) values('tom4')"
[root@mysql data]# mysql -uroot -p123 -e"delete from ooo.tb1 where name='tom3'"
[root@mysql data]# mysql -uroot -p123 -e"insert into ooo.tb1(name) values('tom5')"
[root@mysql data]# mysql -uroot -p123 -e"insert into ooo.tb1(name) values('tom6')"
[root@mysql data]# mysql -uroot -p123 -e"delete from ooo.tb1 where name='tom5'"
[root@mysql data]# mysql -uroot -p123 -e"select * from ooo.tb1"
+----+------+
| id | name |
+----+------+
|  2 | tom2 |
|  4 | tom4 |
|  6 | tom6 |
+----+------+

[root@mysql data]# cd /usr/local/mysql/data/
[root@mysql data]# mysqlbinlog -v log-bin.000003
……

[root@mysql data]# mysqlbinlog --stop-position=1162 log-bin.000003 > /opt/1.sql
[root@mysql data]# mysqlbinlog --start-position=1286 --stop-position=1933 log-bin.000003 > /opt/22.sql
[root@mysql data]# mysqlbinlog --start-position=2057 --stop-position=2704 log-bin.000003 > /opt/333.sql

mysql> drop database ooo;

[root@mysql data]# mysql -uroot -p123 < /opt/1.sql 
[root@mysql data]# mysql -uroot -p123 < /opt/22.sql 
[root@mysql data]# mysql -uroot -p123 < /opt/333.sql

mysql> select * from ooo.tb1;
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
|  3 | tom3 |
|  4 | tom4 |
|  5 | tom5 |
|  6 | tom6 |
+----+------+

2、mysqldump介绍

mysqldump 是 mysql 用于备份和数据转移的一个工具。它主要产生一系列的SQL语句，可以封装到文件，该文件包含有所有重建你的数据库所需要的 SQL命令如 CREATE DATABASE，CREATE TABLE，INSERT 等等。可以用来实现轻量级的快速迁移或恢复数据库。 mysqldump 是将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。 mysqldump一般在数据量很小的时候（几个G）可以用于备份。当数据量比较大的情况下，就不建议用mysqldump工具进行备份了。

数据库的导出

导出对象说明：mysqldump可以针对单个表、多个表、单个数据库、多个数据库、所有数据库进行导出的操作

# mysqldump [options] db_name [tbl_name …] //导出指定数据库或单个表
# mysqldump [options] --databases db_name … //导出多个数据库
# mysqldump [options] --all-databases //导出所有数据库
导出数据库test # mysqldump -uroot -p --flush-logs test > /opt/test.sql //–flush-logs这个选项就会完整备份的时候重新开启一个新binlog

mysqldump 针对单个表进行导出导入

[root@mysql data]# mysqldump -uroot -p123 test123 tb1 > /opt/test123_tb1.sql		单个表导出

mysql> drop table tb1;		删除表

[root@mysql data]# mysql -uroot -p123 test123 < /opt/test123_tb1.sql 		表导入

mysql> select * from tb1;		查验
+----+----------+
| id | name     |
+----+----------+
|  1 | lisi     |
|  2 | zhangsan |
|  3 | tom      |
+----+----------+

mysqldump 针对多个表进行导出导入 tb1 和 tb3

mysql> create table tb2(id int);
mysql> insert into tb2 values(1),(2),(3);
mysql> create table tb3(name varchar(20));
mysql> insert into tb3 values('a'),('b'),('c');
mysql> show tables;
+-------------------+
| Tables_in_test123 |
+-------------------+
| tb1               |
| tb2               |
| tb3               |
+-------------------+

[root@mysql data]# mysqldump -uroot -p123 test123 {tb1,tb3} > /opt/test123_tb1_tb3_1.sql
或
[root@mysql data]# mysqldump -uroot -p123 test123 tb1 tb3 > /opt/test123_tb1_tb3_2.sql

mysql> drop table tb1;
mysql> drop table tb3;
[root@mysql data]# mysql -uroot -p123 test123 < /opt/test123_tb1_tb3_1.sql
mysql> show tables;
+-------------------+
| Tables_in_test123 |
+-------------------+
| tb1               |
| tb2               |
| tb3               |
+-------------------+
mysql> select * from tb1;
+----+----------+
| id | name     |
+----+----------+
|  1 | lisi     |
|  2 | zhangsan |
|  3 | tom      |
+----+----------+
mysql> select * from tb3;
+------+
| name |
+------+
| a    |
| b    |
| c    |
+------+

mysql> drop table tb1;
mysql> drop table tb3;
[root@mysql data]# mysql -uroot -p123 test123 < /opt/test123_tb1_tb3_2.sql
mysql> show tables;
+-------------------+
| Tables_in_test123 |
+-------------------+
| tb1               |
| tb2               |
| tb3               |
+-------------------+
mysql> select * from tb1;
+----+----------+
| id | name     |
+----+----------+
|  1 | lisi     |
|  2 | zhangsan |
|  3 | tom      |
+----+----------+
mysql> select * from tb3;
+------+
| name |
+------+
| a    |
| b    |
| c    |
+------+

mysqldump 针对单个数据库进行导出导入

[root@mysql data]# mysqldump -uroot -p123 --databases test123 > /opt/test123.sql

mysql> drop database test123;

[root@mysql data]# mysql -uroot -p123 < /opt/test123.sql 

mysql> use test123;
mysql> show tables;
+-------------------+
| Tables_in_test123 |
+-------------------+
| tb1               |
| tb2               |
| tb3               |
+-------------------+

mysqldump 针对多个数据库进行导出导入

[root@mysql data]# mysqldump -uroot -p123 --databases test123 mytest > /opt/test123-mytest.sql

mysql> drop database test123;
mysql> drop database mytest;

[root@mysql data]# mysql -uroot -p123 < /opt/test123-mytest.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
……
| mytest             |
| test123            |
+--------------------+

mysql> use test123
mysql> show tables;
+-------------------+
| Tables_in_test123 |
+-------------------+
| tb1               |
| tb2               |
| tb3               |
+-------------------+

mysqldump 针对所有数据库进行导出导入

[root@mysql data]# mysqldump -uroot -p123 -A > /opt/all.sql

mysql> drop database mytest;
mysql> drop database test123;

[root@mysql data]# mysql -uroot -p123 < /opt/all.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
……
| mytest             |
| test123            |
+--------------------+

数据库的导入

# mysql -uroot -p test < /opt/test.sql

mysqldump全库备份+binlog的数据恢复

1、mysqldump备份方案：每周一凌晨1点全库备份
2、备份步骤

环境准备与备份还原：检查开启binlog 先创建一些原始数据

mysql> reset master;
mysql> create database test_db;
mysql> use test_db;
mysql> create table tb1(id int primary key auto_increment,name varchar(20));
mysql> insert into tb1(name) values('tom1');
mysql> insert into tb1(name) values('tom2');
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
+----+------+

1）创建备份目录

[root@mysql ~]# mkdir /opt/mysqlbackup/
[root@mysql ~]# mkdir /opt/mysqlbackup/daily

2）全库备份这里模拟周一的完整备份数据库任务

[root@mysql ~]# mysqldump -uroot -p123 --flush-logs test_db > /opt/mysqlbackup/test_db_`date +%Y%m%d_%H%M%S`.sql

[root@mysql ~]# ll /opt/mysqlbackup/
total 4
drwxr-xr-x 2 root root    6 Jan 27 17:21 daily
-rw-r--r-- 1 root root 1871 Jan 27 17:22 test_db_20200127_172252

备份mysqldump全库备份之前的binlog日志文（注：生产环境中可能不只一个binlog文件）

[root@mysql ~]# cp /usr/local/mysql/data/log-bin.000001 /opt/mysqlbackup/daily/
[root@mysql ~]# mysql -uroot -p123 -e"purge binary logs to 'log-bin.000002'"

模拟下操作失误,将数据修改错误了。

mysql> use test_db;
mysql> delete from tb1 where id=1;
mysql> insert into tb1(name) values('tom3');
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
|  2 | tom2 |
|  3 | tom3 |
+----+------+

备份自mysqldump之后的binlog日志文件

[root@mysql ~]# cp /usr/local/mysql/data/log-bin.000002 /opt/mysqlbackup/daily/

上面的模拟的误操作是删除了id=1的记录

3）现在使用mysqldump的全库备份和binlog来恢复数据。使用mysqldump的备份进行全库恢复

[root@mysql ~]# mysql -uroot -p123 test_db < /opt/mysqlbackup/test_db_2020_01_27.sql

查询一下数据

[root@mysql ~]# mysql -uroot -p123 -e"select * from test_db.tb1"
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
+----+------+

从显示结果可以看到使用mysqldump备份将数据还原到了备份时的状态，刚才删除的数据（id=2）恢复回来了，但备份后产生的数据却丢失了所以还得利用binlog进一步还原因为删除是在全库备份后发生的，而mysqldump全库备份时使用–flush-logs选项，所以只需要分析全库备份后的binlog即mysql-bin.000002。

mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| log-bin.000002 |      1853 |
+----------------+-----------+

查看mysql-bin.000002中的事件，可以看到有删除事件

mysql> show binlog events in 'log-bin.000002';
+----------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name       | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                      |
+----------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
……                                                                                                                                               |
| log-bin.000002 |  219 | Query          |         1 |         294 | BEGIN                                                                                                                                                                                     |
| log-bin.000002 |  294 | Table_map      |         1 |         346 | table_id: 113 (test_db.tb1)                                                                                                                                                               |
| log-bin.000002 |  346 | Delete_rows    |         1 |         391 | table_id: 113 flags: STMT_END_F                                                                                                                                                           |
| log-bin.000002 |  391 | Xid            |         1 |         422 | COMMIT /* xid=341 */                                                                                                                                                                      |
……
+----------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

使用mysqlbinlog 命令可以查看备份的binlog文件的详细事件。恢复流程：直接用bin-log日志将数据库恢复到删除位置前,然后跳过故障点,再进行恢复删除后的所有操作。

先用mysqlbinlog命令找到delete那条语句的位置

[root@mysql ~]# mysqlbinlog -v /opt/mysqlbackup/daily/log-bin.000002 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210127 17:28:18 server id 1  end_log_pos 123 CRC32 0x87a1a789 	Start: binlog v 4, server v 5.7.32-log created 210127 17:28:18
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
MjIRYA8BAAAAdwAAAHsAAAABAAQANS43LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AYmnoYc=
'/*!*/;
# at 123
#210127 17:28:18 server id 1  end_log_pos 154 CRC32 0x5aa323c6 	Previous-GTIDs
# [empty]
# at 154
#210127 17:29:44 server id 1  end_log_pos 219 CRC32 0x33176329 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210127 17:29:44 server id 1  end_log_pos 294 CRC32 0x963d2caa 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1611739784/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 294
#210127 17:29:44 server id 1  end_log_pos 346 CRC32 0x139231e5 	Table_map: `test_db`.`tb1` mapped to number 108
# at 346
#210127 17:29:44 server id 1  end_log_pos 391 CRC32 0xa45ca49e 	Delete_rows: table id 108 flags: STMT_END_F

BINLOG '
iDIRYBMBAAAANAAAAFoBAAAAAGwAAAAAAAEAB3Rlc3RfZGIAA3RiMQACAw8CPAAC5TGSEw==
iDIRYCABAAAALQAAAIcBAAAAAGwAAAAAAAEAAgAC//wBAAAABHRvbTGepFyk
'/*!*/;
### DELETE FROM `test_db`.`tb1`
### WHERE
###   @1=1
###   @2='tom1'
# at 391
#210127 17:29:44 server id 1  end_log_pos 422 CRC32 0x524ca6ae 	Xid = 43
COMMIT/*!*/;
# at 422
#210127 17:29:56 server id 1  end_log_pos 487 CRC32 0xc969f31a 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 487
#210127 17:29:56 server id 1  end_log_pos 562 CRC32 0x4f70c190 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1611739796/*!*/;
BEGIN
/*!*/;
# at 562
#210127 17:29:56 server id 1  end_log_pos 614 CRC32 0x4100ee56 	Table_map: `test_db`.`tb1` mapped to number 108
# at 614
#210127 17:29:56 server id 1  end_log_pos 659 CRC32 0x65ad3938 	Write_rows: table id 108 flags: STMT_END_F

BINLOG '
lDIRYBMBAAAANAAAAGYCAAAAAGwAAAAAAAEAB3Rlc3RfZGIAA3RiMQACAw8CPAACVu4AQQ==
lDIRYB4BAAAALQAAAJMCAAAAAGwAAAAAAAEAAgAC//wDAAAABHRvbTM4Oa1l
'/*!*/;
### INSERT INTO `test_db`.`tb1`
### SET
###   @1=3
###   @2='tom3'
# at 659
#210127 17:29:56 server id 1  end_log_pos 690 CRC32 0x67ea6694 	Xid = 44
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

通过 mysqlbinlog 命令所显示的结果可以看到误操作 delete 的开始 postion为294，结束 position是422。从二进制日志中读取指定 position=294 事件位置作为截至，即把数据恢复到delete删除前

[root@mysql ~]# mysqlbinlog --stop-position=294 /opt/mysqlbackup/daily/log-bin.000002 | mysql -uroot -p123

从二进制日志中读取指定 position=422 事件位置作为开始，即跳过删除事件，恢复删除事件之后对数据的正常操作

[root@mysql ~]# mysqlbinlog --start-position=422 /opt/mysqlbackup/daily/log-bin.000002 | mysql -uroot -p123

查看恢复结果：

[root@mysql ~]# mysql -uroot -p123 -e"select * from test_db.tb1"
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
|  3 | tom3 |
+----+------+

从上面显示可以看出数据恢复到正常状态

通过crond计划任务周期性执行备份脚本 mysqldump备份方案：

周日凌晨1点全库备份周一到周六凌晨每隔4个小时增量备份一次设置crontab任务，每天执行备份脚本

[root@mysql ~]# crontab -e
#每个星期日凌晨1:00执行完全备份脚本
0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1
#周一到周六每隔4个小时增量备份一次
0 */4 * * 1-6 /root/mysqldailybackup.sh >/dev/null 2>&1

mysqlfullbackup.sh脚本内容：

[root@localhost ~]# cat mysqlfullbackup.sh
#!/bin/sh
# Name:mysqlFullBackup.sh
# 定义数据库目录
mysqlDir=/usr/local/mysql

# 定义用于备份数据库的用户名和密码
user=root
userpwd=123
dbname=test_db

# 定义备份目录
databackupdir=/opt/mysqlbackup
[ ! -d $databackupdir ] && mkdir $databackupdir

# 定义邮件正文文件
emailfile=$databackupdir/email.txt

# 定义邮件地址
email=root@localhost.localdomain

# 定义备份日志文件
logfile=$databackupdir/mysqlbackup.log
DATE=`date -I`
echo "" > $emailfile
echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
cd $databackupdir

# 定义备份文件名
dumpfile=mysql_$DATE.sql
gzdumpfile=mysql_$DATE.sql.tar.gz

# 使用mysqldump备份数据库，请根据具体情况设置参数
$mysqlDir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile
//-x--lock-all-tables

# 压缩备份文件
if [ $? -eq 0 ]; then
	tar czf $gzdumpfile $dumpfile >> $emailfile 2>&1
	echo "BackupFileName:$gzdumpfile" >> $emailfile
	echo "DataBase Backup Success!" >> $emailfile
	rm -f $dumpfile
else
	echo "DataBase Backup Fail!" >> $emailfile
fi

# 写日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile

# 发送邮件通知
cat $emailfile | mail -s "MySQL Backup" $email

mysqldailybackup.sh脚本内容：

[root@localhost ~]# cat mysqldailybackup.sh
#!/bin/sh
# Name:mysqlDailyBackup.sh
# 定义数据库目录和数据目录
mysqldir=/usr/local/mysql
datadir=$mysqldir/data

# 定义用于备份数据库的用户名和密码
user=root
userpwd=123

# 定义备份目录，每日备份文件备份到$dataBackupDir/daily
databackupdir=/opt/mysqlbackup
dailybackupdir=$databackupdir/daily
[ ! -d $dailybackupdir ] && mkdir -p $databackupdir/daily

# 定义邮件正文文件
emailfile=$databackupdir/email.txt

# 定义邮件地址
email=root@localhost.localdomain

# 定义日志文件
logfile=$databackupdir/mysqlbackup.log
echo "" > $emailfile
echo $(date +"%Y-%m-%d %H:%M:%S") >> $emailfile
#

# 刷新日志，使数据库使用新的二进制日志文件
$mysqldir/bin/mysqladmin -u$user -p$userpwd flush-logs
cd $datadir

# 得到二进制日志列表
filelist=`cat mysql-bin.index`
icounter=0
for file in $filelist
do
	icounter=`expr $icounter + 1` // = let icounter++
done
nextnum=0
ifile=0
for file in $filelist
do
	binlogname=`basename $file`
	nextnum=`expr $nextnum + 1`
	# 跳过最后一个二进制日志（数据库当前使用的二进制日志文件）
	if [ $nextnum -eq $icounter ]; then
		echo "Skip lastest!" > /dev/null
	else
		dest=$dailybackupdir/$binlogname
		# 跳过已经备份的二进制日志文件
		if [ -e $dest ]; then
			echo "Skip exist $binlogname!" > /dev/null
		else
			# 备份日志文件到备份目录
			cp $binlogname $dailybackupdir
			if [ $? -eq 0 ]; then
				ifile=`expr $ifile + 1`
				echo "$binlogname backup success!" >> $emailfile
			fi
		fi
	fi
done
if [ $ifile -eq 0 ];then
	echo "No Binlog Backup!" >> $emailfile
else
	echo "Backup $ifile File(s)." >> $emailfile
	echo "Backup MySQL Binlog OK!" >> $emailfile
fi

# 发送邮件通知
cat $emailfile | mail -s "MySQL Backup" $email

# 写日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile

二、使用 xtrabackup 进行MySQL数据库备份

mysqldump备份方式是采用逻辑备份，其最大的缺陷就是备份和恢复速度都慢，对于一个小于50G的数据库而言，这个速度还是能接受的，但如果数据库非常大，那再使用mysqldump备份就不太适合了。这时就需要一种好用又高效的工具，xtrabackup就是其中一款，号称免费版的InnoDB HotBackup。 Xtrabackup实现是物理备份，而且是物理热备

目前主流的有两个工具可以实现物理热备：ibbackup和xtrabackup； ibbackup是商业软件，需要授权，非常昂贵。 xtrabackup功能比ibbackup还要强大，但却是开源的。

Xtrabackup提供了两种命令行工具：

xtrabackup：专用于备份InnoDB和XtraDB引擎的数据；
innobackupex：这是一个perl脚本，在执行过程中会调用xtrabackup命令，这样用该命令即可以实现备份InnoDB，也可以备份MyISAM引擎的对象。

Xtrabackup 是由 percona 提供的 mysql 数据库备份工具

特点：
（1）备份过程快速、可靠；
（2）备份过程不会打断正在执行的事务；
（3）能够基于压缩等功能节约磁盘空间和流量；
（4）自动实现备份检验；
（5）还原速度快。

官方链接地址：https://www.percona.com/software/percona-xtrabackup

安装xtrabackup

1）下载xtrabackup

[root@mysql ~]# rpm -qa libgcrypt		# 需要和 libgcrypt 版本相对应
libgcrypt-1.5.3-14.el7.x86_64

[root@mysql ~]# cd /usr/src/
[root@mysql src]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.15/binary/tarball/percona-xtrabackup-2.4.15-Linux-x86_64.libgcrypt153.tar.gz

2）解压

[root@mysql src]# tar zxf percona-xtrabackup-2.4.15-Linux-x86_64.libgcrypt153.tar.gz

3）复制bin下的所有程序到/usr/bin

[root@mysql src]# cp /usr/src/percona-xtrabackup-2.4.15-Linux-x86_64/bin/* /usr/bin/

Xtrabackup中主要包含两个工具：
xtrabackup：是用于热备份 innodb, xtradb 表中数据的工具，支持在线热备份，可以在不加锁的情况下备份 Innodb 数据表，不过此工具不能操作Myisam引擎表；
innobackupex：是将 xtrabackup 进行封装的 perl 脚本，能同时处理 Innodb 和 Myisam，但在处理 Myisam时需要加一个读锁。由于操作Myisam时需要加读锁，这会堵塞线上服务的写操作，而Innodb没有这样的限制，所以数据库中Innodb表类型所占的比例越大，则越有利。

4）安装相关插件

[root@mysql src]# yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5 -y

5）下载percona-toolkit并安装

[root@mysql src]# wget https://downloads.percona.com/downloads/percona-toolkit/3.3.0/binary/redhat/7/x86_64/percona-toolkit-3.3.0-1.el7.x86_64.rpm
[root@mysql src]# rpm -ivh percona-toolkit-3.3.0-1.el7.x86_64.rpm

开启binary log功能

[root@mysql ~]# vim /etc/my.cnf
……
server_id = 1			#从MySQL 5.7.3以后版本必须配置server-id，否则无法启用MySQL二进制日志
log-bin = /usr/local/mysql/data/log-bin			#其中mysql-bin代表的是basename就是生成二进制日志文件的前缀部分,默认的位置在datadir目录下,也可以设置为其他的路径


[root@mysql ~]# systemctl restart mysqld			#重启mysql

方案一：xtrabackup完全备份+binlog增量备份

测试环境准备

创建一个测试数据库，并创建一张表输入几行数据

mysql> use mytest
mysql> create table tb1(id int primary key auto_increment,name varchar(20));
mysql> insert into tb1(name) values('tom1');
mysql> insert into tb1(name) values('tom2');
mysql> select * from mytest.tb1;
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
+----+------+

1、备份创建备份目录

[root@mysql ~]# mkdir -p /opt/mysqlbackup/{full,inc}		# full：全备存放的目录； inc：增量备份存放的目录

完全备份：

基本语法：# innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

进行完全备份

[root@mysql src]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 --socket=/usr/local/mysql/mysql.sock /opt/mysqlbackup/full/
…… #出现如下提示，表示成功
200129 19:31:39 Executing UNLOCK TABLES
200129 19:31:39 All tables unlocked
200129 19:31:39 [00] Copying ib_buffer_pool to /opt/mysqlbackup/full/2021-01-29_19-31-34/ib_buffer_pool
200129 19:31:39 [00]        ...done
200129 19:31:39 Backup created in directory '/opt/mysqlbackup/full/2021-01-29_19-31-34/'
MySQL binlog position: filename 'log-bin.000001', position '154'
200129 19:31:39 [00] Writing /opt/mysqlbackup/full/2021-01-29_19-31-34/backup-my.cnf
200129 19:31:39 [00]        ...done
200129 19:31:39 [00] Writing /opt/mysqlbackup/full/2021-01-29_19-31-34/xtrabackup_info
200129 19:31:39 [00]        ...done
xtrabackup: Transaction log of lsn (2754444) to (2754453) was copied.
200129 19:31:40 completed OK!

相关选项说明：
- - user 指定连接数据库的用户名，
- - password 指定连接数据库的密码，
- - defaults-file=/etc/my.cnf 指定mysql的配置文件my.cfg，如果指定则必须是第一个参数。
/path/to/BACKUP-DIR/ 指定备份所存放的目标目录，备份过程会创建一个以当时备份时间命名的目录存放备份文件。
innobackupex 要从其中获取datadir等信息；
- - database 指定要备份的数据库，这里指定的数据库只对MyISAM表有效，对于InnoDB 数据来说都是全备（所有数据库中的InnoDB数据都进行了备份，不是只备份指定的数据库，恢复时也一样）；
/opt/mysqlbackup/full 是备份文件的存放位置。

备份后的文件：在备份的同时，备份数据会在备份目录下创建一个以当前日期时间为名字的目录存放备份文件：

[root@mysql ~]# cd /opt/mysqlbackup/full/
[root@mysql full]# ll
total 0
drwxr-x--- 6 root root 237 Jan 29 19:46 2020-01-29_19-45-56
[root@mysql full]# ll 2020-01-29_19-45-56/
total 12340
-rw-r----- 1 root root      487 Jan 29 19:46 backup-my.cnf
-rw-r----- 1 root root      280 Jan 29 19:46 ib_buffer_pool
-rw-r----- 1 root root 12582912 Jan 29 19:46 ibdata1
drwxr-x--- 2 root root     4096 Jan 29 19:46 mysql
drwxr-x--- 2 root root       52 Jan 29 19:46 mytest
drwxr-x--- 2 root root     8192 Jan 29 19:46 performance_schema
drwxr-x--- 2 root root     8192 Jan 29 19:46 sys
-rw-r----- 1 root root       19 Jan 29 19:46 xtrabackup_binlog_info
-rw-r----- 1 root root      135 Jan 29 19:46 xtrabackup_checkpoints
-rw-r----- 1 root root      515 Jan 29 19:46 xtrabackup_info
-rw-r----- 1 root root     2560 Jan 29 19:46 xtrabackup_logfile

各文件说明：
（1）xtrabackup_checkpoints ——备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
（2）xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
（3）xtrabackup_binlog_pos_innodb ——二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前 position。
（4）xtrabackup_binary ——备份中用到的xtrabackup的可执行文件；
（5）backup-my.cnf ——备份命令用到的配置选项信息；
在使用innobackupex进行备份时，还可以使用 - - no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据

最小权限用户进行备份

备份数据库的用户需要具有相应权限，如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：

mysql> create user 'bkpuser'@'localhost' identified by '123';
mysql> revoke all privileges,grant option from 'bkpuser'@'localhost';
mysql> grant reload,lock tables,replication client,process on *.* to 'bkpuser'@'localhost';
mysql> flush privileges;

至此全备完全成功，然后向mysql某个库插入几条数据，然后进行增量备份
对完全备份的后数据库更改进行二进制日志增量备份：查看完全备份时binlog日志位置(position)：

[root@mysql ~]# cat /opt/mysqlbackup/full/2020-01-29_20-23-17/xtrabackup_binlog_info 
log-bin.000001	897

模拟数据库修改：
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
+----+------+

mysql> insert into tb1 values(3,'tom3');
mysql> insert into tb1 values(4,'tom4');

增量备份二进制文件：
[root@mysql ~]# mysqlbinlog --start-position=897 /usr/local/mysql/data/log-bin.000001 > /opt/mysqlbackup/inc/`date +%F`.sql

2、还原数据库（模拟数据库损坏：这里直接使用删除数据目录文件来模拟损坏）

[root@mysql ~]# rm -rf /usr/local/mysql/data/*
[root@mysql ~]# ll /usr/local/mysql/data/
total 0

还原完全备份：
（1）准备(prepare)一个完全备份
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处于不一致状态。“准备” 的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。 innobakupex命令的 - -apply-log选项可用于实现上述功能。

- - apply-log指明是将日志应用到数据文件上，完成之后将备份文件中的数据恢复到数据库中：

[root@mysql ~]# innobackupex --apply-log /opt/mysqlbackup/full/2020-01-29_20-23-17/
……
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 897, file name log-bin.000001
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.19 started; log sequence number 2762773
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2762792
200129 20:41:08 completed OK!

注：/opt/mysqlbackup/full/2020-01-29_20-23-17/备份文件所在目录名称

在实现 “准备” 的过程中，innobackupex通常还可以使用 - - use-memory选项来指定其可以使用的内存的大小，默认通常为100M。 如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。innobackupex命令的 - - copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器 DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。

（2）还原数据库：

[root@mysql ~]# innobackupex --copy-back /opt/mysqlbackup/full/2020-01-29_20-23-17/
……
200129 20:43:56 [01] Copying ./performance_schema/global_status.frm to /usr/local/mysql/data/performance_schema/global_status.frm
200129 20:43:56 [01]        ...done
200129 20:43:56 [01] Copying ./performance_schema/session_status.frm to /usr/local/mysql/data/performance_schema/session_status.frm
200129 20:43:56 [01]        ...done
200129 20:43:56 [01] Copying ./ib_buffer_pool to /usr/local/mysql/data/ib_buffer_pool
200129 20:43:56 [01]        ...done
200129 20:43:56 [01] Copying ./xtrabackup_info to /usr/local/mysql/data/xtrabackup_info
200129 20:43:56 [01]        ...done
200129 20:43:56 [01] Copying ./xtrabackup_binlog_pos_innodb to /usr/local/mysql/data/xtrabackup_binlog_pos_innodb
200129 20:43:56 [01]        ...done
200129 20:43:56 [01] Copying ./xtrabackup_master_key_id to /usr/local/mysql/data/xtrabackup_master_key_id
200129 20:43:56 [01]        ...done
200129 20:43:56 [01] Copying ./ibtmp1 to /usr/local/mysql/data/ibtmp1
200129 20:43:56 [01]        ...done
200129 20:43:57 completed OK!

- - copy-back指明是进行数据恢复

修改还原后的数据目录权限:

[root@mysql ~]# ll /usr/local/mysql/data/
total 122924
-rw-r----- 1 root root      280 Jan 29 20:43 ib_buffer_pool
-rw-r----- 1 root root 12582912 Jan 29 20:43 ibdata1
-rw-r----- 1 root root 50331648 Jan 29 20:43 ib_logfile0
-rw-r----- 1 root root 50331648 Jan 29 20:43 ib_logfile1
-rw-r----- 1 root root 12582912 Jan 29 20:43 ibtmp1
drwxr-x--- 2 root root     4096 Jan 29 20:43 mysql
drwxr-x--- 2 root root       82 Jan 29 20:43 mytest
drwxr-x--- 2 root root     8192 Jan 29 20:43 performance_schema
drwxr-x--- 2 root root     8192 Jan 29 20:43 sys
-rw-r----- 1 root root       19 Jan 29 20:43 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root      543 Jan 29 20:43 xtrabackup_info
-rw-r----- 1 root root        1 Jan 29 20:43 xtrabackup_master_key_id

当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如 mysql

[root@mysql ~]# chown -R mysql:mysql /usr/local/mysql/data/

必须重启MySQL：

[root@mysql ~]# systemctl restart mysqld

验证还原后的数据：

mysql> select * from mytest.tb1;
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
+----+------+

（3）还原增量备份：
为了防止还原时产生大量的二进制日志，在还原时可临时关闭二进制日志后再还原：

mysql> set sql_log_bin=0;
mysql> source /opt/mysqlbackup/inc/2020-01-29.sql

↑ ↓ 或者在命令行：
[root@mysql ~]# mysql -uroot -p123 < /opt/mysqlbackup/inc/2020-01-29.sql
↑ ↓ 或者在命令行：
[root@mysql ~]# mysqlbinlog /opt/mysqlbackup/inc/2020-01-29.sql | mysql -uroot -p123

重新启动二进制日志并验证还原数据：

mysql> set sql_log_bin=1;

验证数据是否恢复回来

mysql> select * from mytest.tb1;
+----+------+
| id | name |
+----+------+
|  1 | tom1 |
|  2 | tom2 |
|  3 | tom3 |
|  4 | tom4 |
+----+------+

方案二：xtrabackup完全备份+xtrabacup增量备份

前面进行增量备份，使用的还是老方法：备份二进制日志。其实xtrabackup还支持进行增量备份。

xtrabackup的备份原理
在InnoDB内部会维护一个redo日志文件，也可以叫做事务日志文件（transaction log，事务日志）。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。 xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。这就是xtrabackup的备份过程所以每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础。 xtraBackup基于InnoDB的crash-recovery功能。它会复制innodb的data file，由于不锁表，复制出来的数据是不一致的，在恢复的时候使用crash-recovery，使得数据恢复一致。当InnoDB启动的时候，它会先去检查data file和transaction log，并且会做两步操作： 1.It applies committed transaction logentries to the data files 2.it performs an undo operation on anytransactions that modified data but did not commit. 所以在prepare过程中，XtraBackup使用复制到的transactions log对备份出来的innodb data file进行crash recovery。

测试环境准备

创建一个测试数据库，并创建一张表输入几行数据

mysql> create database test;
mysql> use test
mysql> create table xx(id int,name varchar(20));
mysql> insert into xx values(1,'tom1');
mysql> insert into xx values(2,'tom2');

mysql> select * from test.xx;
+------+------+
| id   | name |
+------+------+
|    1 | tom1 |
|    2 | tom2 |
+------+------+


[root@mysql ~]# rm -rf /opt/mysqlbackup/full/*
[root@mysql ~]# rm -rf /opt/mysqlbackup/inc/*

1、xtrabacup进行备份

[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --socket=/usr/local/mysql/mysql.sock --user=bkpuser --password=123 --port=3306 --backup --target-dir=/opt/mysqlbackup/full/full_incre_$(date +%Y-%m-%d_%H:%M:%S)

部分显示信息如下所示：
- - defaults-file 指定数据库的配置文件，如果使用该参数必须做为第一个参数；
- - user 指定连接数据库的用户名；
- - password 指定连接数据库的密码；
- - port 指定连接数据库的端口号；
- - backup 实施备份到target-dir;
- - target-dir=name 备份文件的存放目录路径。innobackupex要从其中获取datadir等信息；
- - database 指定要备份的数据库，这里指定的数据库只对MyISAM表和InnoDB表的表结构有效，对于InnoDB 数据来说都是全备（所有数据库中的InnoDB数据都进行了备份，不是只备份指定的数据库，恢复时也一样）；
/opt/mysqlbackup/full/ 是备份文件的存放位置。

查看完全备份文件

[root@mysql ~]# ll /opt/mysqlbackup/full/
drwxr-x--- 7 root root 249 Jan 30 00:14 full_incre_2020-01-30_00:14:47

xtrabackup进行增量备份，先录入些数据，再进行增量备份1

mysql> select * from test.xx;
+------+------+
| id   | name |
+------+------+
|    1 | tom1 |
|    1 | tom2 |
+------+------+

mysql> insert into xx values(3,'tom3');

[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --socket=/usr/local/mysql/mysql.sock --user=bkpuser --password=123 --port=3306 --backup --target-dir=/opt/mysqlbackup/inc/incre_$(date +%Y-%m-%d_%H:%M:%S) --incremental-basedir=/opt/mysqlbackup/full/full_incre_2020-01-30_00\:14\:47/

部分显示信息如下所示：
- - incremental-basedir 指定上次完整备份或者增量备份文件的位置(即如果是第一次增量备份则指向完全备份所在目录，在执行过增量备份之后再一次进行增量备份时，其–incremental-basedir应该指向上一次的增量备份所在的目录)。

查看增量备份文件：

[root@mysql ~]# ll /opt/mysqlbackup/inc/
drwxr-x--- 7 root root 275 Jan 30 00:22 incre_2020-01-30_00:22:52
注：这里的增量备份其实只针对的是InnoDB，对于MyISAM来说，还是完整备份。

向表中再插入几行数据，进行第二次增量备份2

mysql> select * from xx;
+------+------+
| id   | name |
+------+------+
|    1 | tom1 |
|    1 | tom2 |
|    3 | tom3 |
+------+------+

mysql> insert into xx values(4,'tom4');

[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --socket=/usr/local/mysql/mysql.sock --user=bkpuser --password=123 --port=3306 --backup --target-dir=/opt/mysqlbackup/inc/incre_$(date +%Y-%m-%d_%H:%M:%S) --incremental-basedir=/opt/mysqlbackup/inc/incre_2020-01-30_00\:22\:52/

注：第二次增量备份–incremental-basedir指向上一次增量备份文件的位置

查看增量备份文件

[root@mysql ~]# ll /opt/mysqlbackup/inc/
drwxr-x--- 7 root root 275 Jan 30 00:22 incre_2020-01-30_00:22:52
drwxr-x--- 7 root root 275 Jan 30 00:28 incre_2020-01-30_00:28:35

2、 xtrabacup进行恢复

删除表里面的数据

mysql> select * from xx;
+------+------+
| id   | name |
+------+------+
|    1 | tom1 |
|    1 | tom2 |
|    3 | tom3 |
|    4 | tom4 |
+------+------+

mysql> delete from xx;

完整备份恢复：在进行恢复前，如果完整备份在远程主机上，首先将完整备份复制到本地主机上，如果是tar包，则需要先解包，解包命令为：tar –izxf xxx.tar，这里必须使用-i参数（忽略存档中的 0 字节块（通常意味着文件结束））。

开始全备份恢复

[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=bkpuser --password=123 --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_2020-01-30_00\:14\:47/

恢复到第一次增量的时刻
增量备份恢复的步骤和完整备份恢复的步骤基本一致，只是应用日志的过程稍有不同。增量备份恢复时，是先将所有的增量备份挨个应用到完整备份的数据文件中，然后再将完整备份中的数据恢复到数据库中。

[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=bkpuser --password=123 --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_2020-01-30_00\:14\:47/ --incremental-dir=/opt/mysqlbackup/inc/incre_2020-01-30_00\:22\:52/

恢复到第二次增量的时刻

[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=bkpuser --password=123 --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_2020-01-30_00\:14\:47/ --incremental-dir=/opt/mysqlbackup/inc/incre_2020-01-30_00\:28\:35/

恢复整个库

[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=bkpuser --password=123 --target-dir=/opt/mysqlbackup/full/full_incre_2020-01-30_00\:14\:47/

然后停止mysql数据库：

[root@mysql ~]# systemctl stop mysqld

开始rsync数据文件：

[root@mysql ~]# cd /opt/mysqlbackup/full/full_incre_2020-01-30_00\:14\:47/
[root@mysql full_incre_2020-01-30_00:14:47]# rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' /opt/mysqlbackup/full/full_incre_2020-01-30_00\:14\:47/ /usr/local/mysql/data/

授予mysql访问权限
当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户 mysql

[root@mysql ~]# chown -R mysql:mysql /usr/local/mysql/data/

启动mysql服务

[root@mysql ~]# systemctl start mysqld

验证
登录mysql，看到以前在备份之后删除的数据已经通过2次增量备份恢复过来了，如下所示：

mysql> select * from test.xx;
+------+------+
| id   | name |
+------+------+
|    1 | tom1 |
|    1 | tom2 |
|    3 | tom3 |
|    4 | tom4 |
+------+------+

方案三：innobackupex全库备份+innobackupex增量备份

测试环境准备

创建一个测试数据库，并创建一张表输入几行数据

mysql> create database test2;
mysql> use test2
mysql> create table yy(id int,name varchar(20));
mysql> insert into yy values(1,'kim1');
mysql> insert into yy values(2,'kim2');


[root@mysql ~]# rm -rf /opt/mysqlbackup/full/*
[root@mysql ~]# rm -rf /opt/mysqlbackup/inc/*

1、innobackupex先做完全备份

[root@mysql ~]# innobackupex --defaults-file=/etc/my.cnf -S /usr/local/mysql/mysql.sock --user=bkpuser --password=123 --no-timestamp /opt/mysqlbackup/full/full_incre_$(date +%Y-%m%d_%H:%M:%S)

查看完全备份文件
[root@mysql ~]# ll /opt/mysqlbackup/full/
drwxr-x--- 8 root root 262 Jan 30 02:29 full_incre_2020-0130_02:29:19

innobackupex做增量备份
做第一次增量备份先录入增量数据，再进行增量备份

mysql> select * from test2.yy;
+------+------+
| id   | name |
+------+------+
|    1 | kim1 |
|    2 | kim2 |
+------+------+

mysql> insert into yy values(3,'kim3');

进行增量备份
[root@mysql ~]# innobackupex --user=bkpuser --password=123 -S /usr/local/mysql/mysql.sock --no-timestamp --incremental /opt/mysqlbackup/inc/incre_$(date +%Y-%m-%d_%H:%M:%S) --incremental-basedir=/opt/mysqlbackup/full/full_incre_2020-0130_02\:29\:19/

查看增量备份文件
[root@mysql ~]# ll /opt/mysqlbackup/inc/
drwxr-x--- 8 root root 288 Jan 30 02:36 incre_2020-01-30_02:36:36

基于全备和第一个增量备份来做第二次增量备份，先录入增量数据录入，再进行第二次增量备份

mysql> select * from yy;
+------+------+
| id   | name |
+------+------+
|    1 | kim1 |
|    2 | kim2 |
|    3 | kim3 |
+------+------+

mysql> insert into yy values(4,'kim4');

进行第二次增量备份
[root@mysql ~]# innobackupex --user=bkpuser --password=123 -S /usr/local/mysql/mysql.sock --no-timestamp --incremental /opt/mysqlbackup/inc/incre_$(date +%Y-%m-%d_%H:%M:%S) --incremental-basedir=/opt/mysqlbackup/inc/incre_2020-01-30_02\:36\:36/

查看增量备份文件
[root@mysql ~]# ll /opt/mysqlbackup/inc/
drwxr-x--- 8 root root 288 Jan 30 02:36 incre_2020-01-30_02:36:36
drwxr-x--- 8 root root 288 Jan 30 02:40 incre_2020-01-30_02:40:08

2、innobackupex做增量恢复

删除表里面的数据

mysql> delete from test2.yy;

开始做恢复，恢复全备份

[root@mysql ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/full_incre_2020-0130_02\:29\:19/

- - redo-only 用于准备增量备份内容把数据合并到全备份目录，配合incremental-dir 增量备份目录使用

基于全备份进行第一次增量备份的恢复

[root@mysql ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/full_incre_2020-0130_02\:29\:19/ --incremental-dir=/opt/mysqlbackup/inc/incre_2020-01-30_02\:36\:36/

基于全备份和第一次增量备份，恢复第二次增量备份

[root@mysql ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/full_incre_2020-0130_02\:29\:19/ --incremental-dir=/opt/mysqlbackup/inc/incre_2020-01-30_02\:40\:08/

恢复整个数据库停止数据库

[root@mysql ~]# systemctl stop mysqld

清空数据目录下所有文件

[root@mysql ~]# mkdir -p /tmp/mysqldatabak
[root@mysql ~]# mv /usr/local/mysql/data/* /tmp/mysqldatabak/

将恢复好的数据按照配置文件的需求拷贝到相应目录

[root@mysql ~]# innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=123 --copy-back /opt/mysqlbackup/full/full_incre_2020-0130_02\:29\:19/

赋予mysql账号权限

[root@mysql ~]# chown -R mysql:mysql /usr/local/mysql/data/

启动mysql服务

[root@mysql ~]# systemctl start mysqld

验证：登录mysql界面，查看数据是否已经恢复

mysql> select * from test2.yy;
+------+------+
| id   | name |
+------+------+
|    1 | kim1 |
|    2 | kim2 |
|    3 | kim3 |
|    4 | kim4 |
+------+------+

附：Xtrabackup的 “流” 及 “备份压缩” 功能

Xtrabackup对备份的数据文件支持 “流” 功能，即可以将备份的数据通过 STDOUT 传输给 tar 程序进行归档，而不是默认的直接保存至某备份目录中。要使用此功能，仅需要使用 - - stream 选项即可。如：

[root@mysql ~]# innobackupex --user=bkpuser --password=123 --socket=/usr/local/mysql/mysql.sock --stream=tar /opt/mysqlbackup/full/ | gzip > /opt/mysqlbackup/full/full_`date +%Y-%m-%d_%H:%M:%S`.tar.gz

三、mysqldump、xtrabackup分别导入导出约5G数据所耗费的时间

1、建测试表：生成5G的数据

mysql> use mytest
mysql> CREATE TABLE t (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment '自增主键', 
dept tinyint not null comment '部门id',
name varchar(30) comment '用户名称',
create_time datetime not null comment '注册时间', 
last_login_time datetime comment '最后登录时间'
) comment '测试表';

# 手工插入第一条测试数据，后面数据会根据这条数据作为基础生成
mysql> insert into t values(1,1,'user_1', '2018-01-01 00:00:00', '2018-03-01 12:00:00');

#初始化序列变量
mysql> set @i=1;

mysql> insert into t(dept,name,create_time,last_login_time) select dept,name,create_time,last_login_time from t;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t(dept,name,create_time,last_login_time) select dept,name,create_time,last_login_time from t;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t(dept,name,create_time,last_login_time) select dept,name,create_time,last_login_time from t;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

…………执行好多次之后…………

mysql> select count(id) from t;
+-----------+
| count(id) |
+-----------+
|  67108864 |	约6千7百万条数据
+-----------+

[root@mysql ~]# ll -h /usr/local/mysql/data/mytest/ 
……
-rw-r----- 1 mysql mysql 8.6K Jan 30 22:02 t.frm
-rw-r----- 1 mysql mysql 4.5G Jan 30 22:47 t.ibd

2、mysqldump 针对单个数据库进行导出导入

[root@mysql ~]# mysqldump -uroot -p123 -B mytest > /opt/mytest.sql
mysqldump: Got errno 28 on write		#磁盘空间不足
清理后再次执行↓
[root@mysql ~]# mysqldump -uroot -p123 -B mytest > /opt/mytest.sql	
导出耗时1分41秒

[root@mysql ~]# ll -h /opt/
-rw-r--r-- 1 root root 4.2G Jan 31 01:31 mytest.sql

mysql> drop database mytest;		删除库

[root@mysql ~]# mysql -uroot -p123 < /opt/mytest.sql	
导入耗时12分10秒

mysql> select count(id) from mytest.t;
+-----------+
| count(id) |
+-----------+
|  67108864 |
+-----------+

mysql> select * from mytest.t where id=2;
+----+------+--------+---------------------+---------------------+
| id | dept | name   | create_time         | last_login_time     |
+----+------+--------+---------------------+---------------------+
|  2 |    1 | user_1 | 2018-01-01 00:00:00 | 2018-03-01 12:00:00 |
+----+------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

[root@mysql ~]# ll -h /usr/local/mysql/data/mytest/
……
-rw-r----- 1 mysql mysql 8.6K Jan 31 02:51 t.frm
-rw-r----- 1 mysql mysql 2.9G Jan 31 03:03 t.ibd

3、xtrabackup完全备份单库

[root@mysql ~]# mkdir -p /opt/mysqlbackup/full		#备份目录

[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --socket=/usr/local/mysql/mysql.sock --user=root --password=123 --port=3306 --database=mytest --backup --target-dir=/opt/mysqlbackup/full/full_incre_$(date +%Y-%m-%d_%H:%M:%S)
备份耗时28秒

#查看全备份文件
[root@mysql ~]# ll /opt/mysqlbackup/full/full_incre_2021-01-31_03\:18\:45/ -h		
total 589M
-rw-r----- 1 root root  487 Jan 31 03:19 backup-my.cnf
-rw-r----- 1 root root  292 Jan 31 03:19 ib_buffer_pool
-rw-r----- 1 root root 588M Jan 31 03:18 ibdata1
drwxr-x--- 2 root root 4.0K Jan 31 03:19 mysql
drwxr-x--- 2 root root  108 Jan 31 03:19 mytest
drwxr-x--- 2 root root 8.0K Jan 31 03:19 performance_schema
drwxr-x--- 2 root root 8.0K Jan 31 03:19 sys
drwxr-x--- 2 root root   48 Jan 31 03:19 test
drwxr-x--- 2 root root   48 Jan 31 03:19 test2
-rw-r----- 1 root root   25 Jan 31 03:19 xtrabackup_binlog_info
-rw-r----- 1 root root  147 Jan 31 03:19 xtrabackup_checkpoints
-rw-r----- 1 root root  633 Jan 31 03:19 xtrabackup_info
-rw-r----- 1 root root 2.5K Jan 31 03:19 xtrabackup_logfile

mysql> drop database mytest;		删除库

# 开始全备份恢复
[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password=123 --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_2021-01-31_03\:18\:45/

# 恢复整个库
[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password=123 --target-dir=/opt/mysqlbackup/full/full_incre_2021-01-31_03\:18\:45/

# 停止mysql数据库
[root@mysql ~]# systemctl stop mysqld

# rsync数据文件
[root@mysql ~]# cd /opt/mysqlbackup/full/full_incre_2021-01-31_03\:18\:45/
[root@mysql full_incre_2021-01-31_03:18:45]# rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' /opt/mysqlbackup/full/full_incre_2021-01-31_03\:18\:45/ /usr/local/mysql/data/
恢复耗时46秒

# 授予mysql访问权限
[root@mysql ~]# chown -R mysql:mysql /usr/local/mysql/data/

# 启动mysql服务
[root@mysql ~]# systemctl start mysqld

验证：
mysql> select count(id) from mytest.t;
+-----------+
| count(id) |
+-----------+
|  67108864 |
+-----------+

mysql> select * from mytest.t where id=2;
+----+------+--------+---------------------+---------------------+
| id | dept | name   | create_time         | last_login_time     |
+----+------+--------+---------------------+---------------------+
|  2 |    1 | user_1 | 2018-01-01 00:00:00 | 2018-03-01 12:00:00 |
+----+------+--------+---------------------+---------------------+

[root@mysql ~]# ll -h /usr/local/mysql/data/mytest/
……
-rw-r----- 1 mysql mysql 8.6K Jan 31 03:19 t.frm
-rw-r----- 1 mysql mysql 2.9G Jan 31 03:19 t.ibd

4、比较

	mysqldump	xtrabackup
备份	1分41秒	28秒
恢复	12分10秒	46秒

KFC质检员

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
MySQL——备份还原

一、 mysqldump备份结合binlog日志恢复MySQL备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在MySQL故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。1、binlog介绍mysql的二进制日志记录着该数据库的所有增删改的操作日志(前提是要在服务器上开启binlog)，还包括了这些操作的执行时间。为了显示这些二进制内容，可以使用mysqlbinlog命令来查看。Binlog的用途主从同步恢复
复制链接

扫一扫