Mysql的备份

最新推荐文章于 2024-03-26 14:45:28 发布

礁之

最新推荐文章于 2024-03-26 14:45:28 发布

阅读量812

点赞数 6

分类专栏：数据库系列文章标签：数据库 mysql 备份数据 binlog 二进制日志

本文链接：https://blog.csdn.net/rzy1248873545/article/details/118512785

版权

数据库系列专栏收录该内容

19 篇文章 3 订阅

订阅专栏

一、Mysql备份

（1）binlog二进制日志概述

binlog二进制日志是mysql常用的备份数据库的方式
mysql数据库中的增、删、改的操作都会记录到binlog日志中，相当于二进制日志记录着一条条SQL语句，开启了binlog二进制日志后，备份数据库其实就是备份二进制日志，
通过二进制日志去恢复数据，日志中的每条操作都有相应的时间和位置号，可以指定事件或者位置号去恢复数据库数据

（2）开启binlog二进制日志

[root@rzy ~]# vim /etc/my.cnf #修改mysql主配置文件  
  1 [mysqld]
  2 basedir = /usr/local/mysql
  3 datadir = /usr/local/mysql/data  #这个路径就是二进制日志存放的路径
  4 port = 3306
  5 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  6 character_set_server=utf8
  7 init_connect='SET NAMES utf8'
  8 log-error=/usr/local/mysql/logs/mysqld.log
  9 pid-file=/usr/local/mysql/logs/mysqld.pid
 10 skip-name-resolve
 11 server-id = 1  #添加，开启二进制时，指定的id为1
 12 log-bin=mysql-bin #添加，开启二进制日志
#保存退出
[root@rzy ~]# systemctl restart mysqld #重启mysql服务
[root@rzy ~]# cd /usr/local/mysql/data/ #进入存放二进制日志的目录
[root@rzy data]# ls 
aaa       ib_buffer_pool  ib_logfile0  ibtmp1  mysql-bin.000001  mysqld_safe.pid     sys
auto.cnf  ibdata1         ib_logfile1  mysql   mysql-bin.index   performance_schema
————————————————————————————————————————————————————————————
#mysql-bin.000001这个就是二进制日志
#mysql-bin.index  这个可以查看mysql所有记录的二进制文件
————————————————————————————————————————————————————————————
[root@rzy data]# cat mysql-bin.index  #查看当前mysql操作记录的二进制文件是那个
./mysql-bin.000001   #可以看到当前mysql记录的二进制日志有000001

[root@rzy data]# mysqlbinlog mysql-bin.000001  #查看二进制日志需要使用mysqlbinlog命令查看，不然是乱码
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210519 23:13:55 server id 1  end_log_pos 123 CRC32 0x203bbf20 	Start: binlog v 4, server v 5.7.12-log created 210519 23:13:55 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
MyulYA8BAAAAdwAAAHsAAAABAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAzK6VgEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ASC/OyA=
'/*!*/;
# at 123
#210519 23:13:55 server id 1  end_log_pos 154 CRC32 0x92e7fa2e 	Previous-GTIDs
# [empty]
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*/;

binlog日志内容作用：

记录了mysql数据的增、删、改的操作
MySQL的主从复制就是基于二进制日志来进行同步的，slave服务器通过复制master服务器的二进制日志完成主从复制，在执行之前保存于中继日志（relay log）中
slave从服务器通常可以关闭二进制日志从而提高服务器性能，因为从服务器主要是读主服务器发过来的二进制日志，它本身是不需要记录二进制日志的，所有可以关闭二进制日志功能从而提高性能

有两种情况，二进制日志会重新生成：

重启mysql服务
在MySQL服务器中使用flush logs;重置日志

注意！！！！：

在删除二进制日志时，只删除二进制日志是不行的，而且会导致mysql管理混乱，还会使mysql无法重新启动，故而在删除二进制日志时，要记得把mysql-bin.index中的内容也删除

（3）在mysql中查看二进制日志

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)

mysql> flush logs;  #重置日志
Query OK, 0 rows affected (0.01 sec)

mysql> show master status; #再次查看发现变成了02号日志
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@rzy ~]# cat /usr/local/mysql/data/mysql-bin.index  #查看记录的日志，发现多了一个
./mysql-bin.000001
./mysql-bin.000002

（4）mysql二进制文件读取工具mysqlbinlog

命令格式：mysqlbinlog 选项二进制日志路径

选项：

选项	作用
–start-datetime	用来指定二进制日志的起始日期日期需要写成yyyy-mm-dd 00:00:00格式的
–stop-datetime	用来指定二进制日志的结束日期
–start-position	用来指定二进制日志的起始位置
–stop-position	用来指定二进制日志的结束位置

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qLXCSXnX-1625533842204)(F:\个人文档\markdown图片\image-20210519163939454.png)]

利用这些选项可以完成mysql的增量备份，一般都是配合and加上起始和结束日期来备份，以天来备份的

[root@rzy ~]# mysql -u root -p123123 #进入mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.12-log Source distribution

Copyright (c) 2000, 2016, 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> use aaa;
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_aaa |
+---------------+
| aaa           |
| bbb           |
+---------------+
2 rows in set (0.01 sec)

mysql> drop table aaa; #删除两个表
Query OK, 0 rows affected (0.00 sec)

mysql> drop table bbb;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;  #再次查看确认删除
Empty set (0.00 sec)

mysql> exit
Bye

[root@rzy ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000002  #使用mysqlbinlog命令查看
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210519 23:48:49 server id 1  end_log_pos 123 CRC32 0x8d27bd79 	Start: binlog v 4, server v 5.7.12-log created 210519 23:48:49
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
YTOlYA8BAAAAdwAAAHsAAAABAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AXm9J40=
'/*!*/;
# at 123
#210519 23:48:49 server id 1  end_log_pos 154 CRC32 0x4c53ba28 	Previous-GTIDs
# [empty]
# at 154
#210520  0:19:23 server id 1  end_log_pos 219 CRC32 0x24558e28 	Anonymous_GTID	last_committed=0	sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210520  0:19:23 server id 1  end_log_pos 333 CRC32 0x3ce0596c 	Query	thread_id=3	exec_time=0	error_code=0
use `aaa`/*!*/;          
SET TIMESTAMP=1621441163/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
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/*!*/;
DROP TABLE `aaa` /* generated by server */  #这个就是删除aaa表的命令
/*!*/;
# at 333
#210520  0:19:25 server id 1  end_log_pos 398 CRC32 0x701d265b 	Anonymous_GTID	last_committed=1	sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 398
#210520  0:19:25 server id 1  end_log_pos 512 CRC32 0xd93b7e48 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1621441165/*!*/;
DROP TABLE `bbb` /* generated by server */  #这个就是刚才删除bbb表的命令
/*!*/;
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*/;
[root@rzy ~]# mysqlbinlog --start-position 333 --stop-position 398 /usr/local/mysql/data/mysql-bin.000002 > /root/mysql-bin.sql  #从位置333到398的内容输出到新的二进制日志中
[root@rzy ~]# mysqlbinlog --start-datetime 2021-05-20 00:19:23 --stop-datetime 2021-05-20 00:19:25 /usr/local/mysql/data/mysql-bin.000002 > /root/mysql-date-bin.sql #利用时间来输出二进制日志内容
[root@rzy ~]# mysqlbinlog mysql-bin.sql  #查看新建的二进制日志，确认输出内容
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
ERROR: File is not a binary log file.
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*/;

[root@rzy ~]# mysql -u root -p123123 < mysql-bin.sql  #恢复数据
mysql: [Warning] Using a password on the command line interface can be insecure. #这个是提示密码不安全

（5）二进制日志内容解释

[root@rzy ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000002
# at 577
#210520  0:47:31 server id 1  end_log_pos 687 CRC32 0x1b302d07 	QueryQuery：记录类型
#thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1621442851/*!*/;
create table aaa(id int,name char(10))
_________________________________________________________
#210520 0:47:31:时间点
#server id 1：服务器id，就是配置文件里的那个id
#Query：记录类型
#thread_id=6：线程号
#exec_time=0：语句的时间戳和写入二进制日志文件的时间差
#at 577：事件位置
#error_code=0：错误代码
#create table aaa(id int,name char(10))：事件内容一般z

（6）二进制日志格式

二进制有三种格式分别是statement、row、mixed，是由bin_log_format定义的，可以在mysql中临时定义，也可以在配置文件中永久定义，
mysql5.7版本之前默认格式为statement，mysql5.7版本及之后默认格式为row，

statement： 此格式是基于恢复语句的，使用这个格式二进制日志会记录在mysql中执行过的SQL语句。需要注意的是，如果执行的SQL语句使用了某种即时函数，例如current_date()这种表示当前时间的函数的话，在恢复数据时，会导致恢复数据的不一致
row： 此格式是基于恢复行数据的，顾名思义就是恢复数据库的行数据，但是缺点就是如果数据库数据量大的话，恢复数据的量会很大
mixed： 此格式是混合模式，使用这个模式会由mysql去自行定义恢复SQL语句还是行数据 （使用statement格式还是row格式）

mysql> show variables like "binlog_format";   #查看mysql当前二进制记录的格式
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.04 sec)

mysql> set session binlog_format=statement;   #临时修改二进制记录的格式为statement
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@rzy ~]# vim /etc/my.cnf 
  1 [mysqld]
  2 basedir = /usr/local/mysql
  3 datadir = /usr/local/mysql/data
  4 port = 3306
  5 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  6 character_set_server=utf8
  7 init_connect='SET NAMES utf8'
  8 log-error=/usr/local/mysql/logs/mysqld.log
  9 pid-file=/usr/local/mysql/logs/mysqld.pid
 10 skip-name-resolve
 11 server-id = 1
 12 log-bin=mysql-bin
 13 binlog_format=ROW  #添加binlog_format=ROW永久指定mysql二进制日志格式为row
#保存退出
[root@rzy ~]# systemctl restart mysqld

建议：不要把二进制日志和数据文件放在同一个设备，可以将binlog二进制日志备份到其他设备上

（7）备份类型

在备份mysql数据时，根据多种情况可以分类成多种备份类型

根据备份时，mysql服务是否可以正常进行读写

冷备份（cold backup）：备份时需要停止mysql服务，mysql的读写操作都不能进行
温备份：备份时需要对全局（mysql的所有数据）施加共享锁，mysql只能进行读操作不能进行写操作
热备份（hot backup）：备份时不需要停止mysql服务，mysql的读写操作可以正常进行

根据备份时备份的数据是整个数据还是变化数据（数据集）

完全备份（full backup）：备份整个mysql的数据，恢复时只需一次性恢复，备份和恢复简单，但是恢复时间长，备份时间也长，并且每一次进行完全备份会产生大量的重复数据，占用大量空间
增量备份（partial backup）：只备份完全备份后增加的数据，例如：第一天把数据库进行了完全备份，第二天增加了数据A，增量备份只会备份数据A而不会把全部的数据在备份一遍，恢复时需要诶个恢复，如果中间的某一次备份数据损坏，将导致数据丢失
差异备份（differential backup）：只备份完全备份之后修改过的所有文件，备份的时间起点是自完全备份开始，备份数据量会越来越大，恢复数据时，只需要恢复上一次的完全备份和最近一次的差异备份即可，例如：第一天把数据库进行了完全备份，第二天没有新数据，第三天增加了数据A，差异备份会只备份数据A，恢复时也是需要恢复完全备份和最后一次差异备份的数据A

根据备份时的备份内容

物理备份（physical backup）：物理备份会直接复制数据文件（即mysql的数据存储路径），并且打包归档

特点：物理备份不需要额外工具，直接使用归档命令既可以，但是跨平台能力比较差，如果数据量超过几十个G的话，则适用于物理备份

逻辑备份（logical backup）：逻辑备份其实就是备份SQL语句，mysqldump就是逻辑备份

特点：可以使用文档编辑器进行编辑，导入数据库方便，直接读取SQL语句即可。逻辑备份恢复数据时间较慢，占用空间大，并且无法保证浮点数的精度，恢复完数据后还需重新建立索引

（8）备份的策略

在进行mysql的备份时，有以下因素需要考虑

备份方式
备份实践
备份成本
锁时间
使用时间
性能开销
恢复成本
恢复时间
可以容忍数据的最大丢失数量

（9）备份内容

在备份mysql时要备份的内容一般有：

数据库中的数据
mysql的配置文件
mysql中的代码：存储过程、存储函数、触发器
OS相关的配置文件，crontab计划任务中的备份策略脚本
如果在主从复制的场景中。一般是备份跟复制相关的信息
二进制日志需要定期备份，一旦发现二进制日志出现问题，需要马上对数据进行完全备份

（10）Mysql最常用的三种备份工具

mysqldump

通常为小数据情况下的备份，mysqldump是单线程备份，所以在恢复和备份时较慢，故通常只备份小数据

innoDB引擎支持：热备、温备

MyISAM、Aria引擎支持：温备

Xtrabackup

Xtrabackup通常使用innobackupex工具，一般备份mysql大数据时使用，xtrabackup属于物理备份，备份和恢复速度快

innoDB引擎支持：热备、增量备份、完全备份

MyISAM引擎支持：温备、完全备份

LVM-snapshot

这个备份工具在实际工作中几乎不使用，这个一个接近于热备的工具，利用LVM可以创建快照的特点备份数据，备份之前要先请求全局锁并且在创建完快照后释放全局锁，这个快照只能恢复一次，快照恢复后需要再次创建快照，使用cp、tar等工具进行物理备份，备份和恢复的速度较快

使用这个工具是很难实现增量备份的，并且请求全局锁需要等待一段时间，在实际工作的服务器上尤为如此，所以几乎不使用这个工具

因为LVM-snapshot几乎不使用，所以这里只做mysqldump和Xtrabackup的比较：

工具名称	mysqldump	Xtrabackup
备份方式	逻辑备份	物理备份
数据保存方式	SQL脚本	二进制文件
是否支持热备份	支持	支持
是否支持增量备份	支持	支持
备份过程是否锁表	锁表	不锁表
是否影响服务器的正常业务	影响较大	影响较小
备份和恢复的时间	因为是单线程所以耗时较长	耗时较短
占用空间	占用空间小	物理备份占用空间较大

推荐使用Xtrabackup进行完全备份，使用Mysqldump进行增量备份

二、Mysqldump工具

mysqldump是基于mysql的二进制日志备份的，是单线程备份，所以在备份、恢复大数据时，比较耗费时间，一般都使用mysqldump备份小数据
使用mysqldump工具之前要记得开启
使用Mysqldump工具：

-备份所有数据库

mysql> show databases;  #先查看有几个数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.07 sec)

mysql> use aaa;
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; #查看aaa库中的表
+---------------+
| Tables_in_aaa |
+---------------+
| aaa           |
| bbb           |
+---------------+
2 rows in set (0.00 sec)

mysql> exit
bye
[root@rzy ~]# mkdir /beifen #创建备份目录
[root@rzy ~]# mysqldump -u root -p123123 --opt --all-databases > /beifen/all.sql #备份所有数据库
mysqldump: [Warning] Using a password on the command line interface can be insecure.

-恢复数据

[root@rzy ~]# mysql -u root -p123123; #进入数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.12-log Source distribution

Copyright (c) 2000, 2016, 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> show databases; #先查看所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database aaa; #删除aaa库
Query OK, 2 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> exit
Bye
[root@rzy ~]# mysql -u root -p123123 < /beifen/all.sql  #使用刚才的完全备份恢复mysql数据
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysql -u root -p123123 -e "show databases;" #-e命令可以直接调用SQl语句，查看所以数据库发现aaa库又回来了
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

-备份单个库

[root@rzy ~]# mysqldump -u root -p123123 --databases aaa > /beifen/aaa-data.sql #备份aaa库
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysql -u root -p123123 -e "drop database aaa;" #删除aaa库
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysql -u root -p123123 -e "show databases;;" #查看是否删除
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@rzy ~]# mysql -u root -p123123 < /beifen/aaa-data.sql  #恢复aaa库
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysql -u root -p123123 -e "show databases;;" #再次查看所有库，发现aaa库回来了
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

-备份单个、多个表

[root@rzy ~]# mysqldump -u root -p123123 aaa bbb > /beifen/aaa-bbb.sql #备份aaa库的bbb表
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysql -u root -p123123 -e "use aaa; drop table bbb;" #删除aaa库的bbb表
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysql -u root -p123123 -e "use aaa; show tables;" #查看是否删除
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_aaa |
+---------------+
| aaa           |
+---------------+
[root@rzy ~]# mysql -u root -p123123 aaa < /beifen/aaa-bbb.sql  #恢复bbb表，要注意指定库
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysql -u root -p123123 -e "use aaa; show tables;" #查看是否恢复成功
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_aaa |
+---------------+
| aaa           |
| bbb           |
+---------------+
[root@rzy ~]# mysqldump -u root -p123123 aaa aaa bbb > /beifen/aaa-table.sql   #备份aaa库的aaa和bbb表
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysql -u root -p123123 -e "use aaa; drop table aaa ;"         #删除aaa表
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysql -u root -p123123 -e "use aaa; drop table bbb ;"         #删除bbb表
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysql -u root -p123123 -e "use aaa; show tables;"       #查看aaa库的所有表确认删除
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysql -u root -p123123 aaa < /beifen/aaa-table.sql      #恢复两个表
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysql -u root -p123123 -e "use aaa; show tables;"       #查看aaa库的所有表，发现成功恢复
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_aaa |
+---------------+
| aaa           |
| bbb           |
+---------------+

-导出表的结构

[root@rzy ~]# mysqldump  -u root -p123123 -d aaa > /beifen/aaa-jiegou.sql #导出aaa库下所有表的结构
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysqldump  -u root -p123123 -d aaa aaa > /beifen/aaa-jiegou.sql #只导出aaa库下aaa表的结构
mysqldump: [Warning] Using a password on the command line interface can be insecure.

-导出表的数据

[root@rzy ~]# mysqldump -u root -p123123 -t aaa > /beifen/aaa-shuju.sql #导出aaa库下所有表的数据
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@rzy ~]# mysqldump -u root -p123123 -t aaa aaa > /beifen/aaa-shuju.sql #只导出aaa库下aaa表的数据
mysqldump: [Warning] Using a password on the command line interface can be insecure.

三、Xtrabackup工具

（1）Xtrabackup概述

Xtrabackup是由percona公司开发、用于Mysql数据库物理备份的备份工具，支持Mysql、oracle、percona Server、mariaDB数据库，是一个开源的数据库备份工具
使用Xtrabackup需要安装percona-toolkit-2.2.19-1.noarch.rpm和percona-xtrabackup-2.4.5-Linux-x86_64.tar.gz
在Xtrabackup软件包安装完成后，在bin目录下，一共有六个文件：

[root@rzy ~]# cd /usr/src/percona-xtrabackup-2.4.5-Linux-x86_64/
[root@rzy percona-xtrabackup-2.4.5-Linux-x86_64]# ll
总用量 4
drwxr-xr-x  2 root root  111 11月 25 2016 bin
drwxr-xr-x  3 root root   18 11月 25 2016 man
drwxr-xr-x 14 root root 4096 11月 25 2016 percona-xtrabackup-2.4-test
[root@rzy percona-xtrabackup-2.4.5-Linux-x86_64]# cd bin/
[root@rzy bin]# ll
总用量 225988
lrwxrwxrwx 1 root root        10 11月 25 2016 innobackupex -> xtrabackup
-rwxr-xr-x 1 root root   5179300 11月 25 2016 xbcloud
-rwxr-xr-x 1 root root      3020 11月 25 2016 xbcloud_osenv
-rwxr-xr-x 1 root root   5001985 11月 25 2016 xbcrypt  #加解密使用
-rwxr-xr-x 1 root root   5071619 11月 25 2016 xbstream #类似于tar
-rwxr-xr-x 1 root root 216142648 11月 25 2016 xtrabackup

其中最主要的是innobackupex和Xtrabackup，innobackupex是一个perl脚本，Xtrabackup是C\C++编译的二进制

Xtrabackup是用来备份innoDB引擎的表的，不能备份非innoDB引擎的表，和mysqld server没有交互

可以看到innobackupex与Xtrabackup做了软连接，innobackupex脚本用来备份非innoDB引擎的表，但是可以调用Xtrabackup命令来备份innoDB引擎的表，还会发送命令与mysqld server进行交互，比如加读锁、获取位点等，简单的来说，innobackupex在Xtrabackup上做了一层封装，使之既能备份非innoDB表也可以调用Xtrabackup备份innoDB表

mysql的系统表使用的是MyISAM存储引擎，当我们备份系统表时一般都是通过innobackupex命令进行备份

（2）备份原理

Xtrabackup和innobackupex两个工具之间的交互和协调是通过文件的创建、删除来实现的，主要的文件有：

Xtrabackup_suspended_1
Xtrabackup_suspended_2
Xtrabackup_log_copied

交互的过程：

innobackupex在启动Xtrabackup进程后，会一直等待Xtrabackup备份完innoDB文件。

（而innobackupex如何知道Xtrabackup有没有备份完呢，其实在Xtrabackup备份完后会创建名叫Xtrabackup_suspended_2的文件，当innobackupex检测到这个文件被创建出来之后，就知道Xtrabackup已经备份完innoDB文件了）

在Xtrabackup备份完innoDB文件后，创建除了Xtrabackup_suspended_2文件，它会等待innobackupex把这个文件删除，才会继续往下执行操作

innobackupex在检测到Xtrabackup_suspended_2被创建出来后就会继续往下执行操作

innobackupex在备份完非innoDB表后，会删除Xtrabackup_suspended_2文件，接着等待Xtrabackup_log_copied创建

Xtrabackup检测到Xtrabackup_suspended_2文件被删除后，会继续往下执行操作

（3）备份过程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iJjABezQ-1625533842206)(F:\个人文档\markdown图片\image-20210522110000697.png)]

（4）安装Xtrabackup

-实验环境

系统	主机名	mysql版本	使用的软件包
centos7.4	rzy	Mysql-5.7.12	percona-toolkit-2.2.19-1.noarch.rpm percona-xtrabackup-2.4.5-Linux-x86_64.tar.gz

-实验步骤

[root@rzy ~]# ll #上传两个软件包
总用量 225988
-rw-------. 1 root root     1264 1?. 12 18:27 anaconda-ks.cfg
-rw-r--r--  1 root root 70389425 5?. 11 14:35 boost_1_59_0.tar.bz2
-rw-r--r--  1 root root 50571897 5?. 11 14:30 mysql-5.7.12.tar.gz
-rw-r--r--  1 root root  1736921 5?. 22 17:21 percona-toolkit-2.2.19-1.noarch.rpm
-rw-r--r--  1 root root 82404945 5?. 22 17:21 percona-xtrabackup-2.4.5-Linux-x86_64.tar.gz
[root@rzy ~]# tar xf percona-xtrabackup-2.4.5-Linux-x86_64.tar.gz -C /usr/src/
[root@rzy ~]# cd /usr/src/percona-xtrabackup-2.4.5-Linux-x86_64/
[root@rzy percona-xtrabackup-2.4.5-Linux-x86_64]# cp bin/* /usr/bin/ #优化命令执行路径，和修改环境变量是一样的
[root@rzy percona-xtrabackup-2.4.5-Linux-x86_64]# cd 
[root@rzy ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护，将以只读方式挂载
mount: /dev/sr0 已经挂载或 /mnt 忙
       /dev/sr0 已经挂载到 /mnt 上
[root@rzy ~]# yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5 #安装依赖包
。。。。。。
完毕！
[root@rzy ~]# rpm -ivh percona-toolkit-2.2.19-1.noarch.rpm  #安装rpm包

-使用Xtrabackup工具

常用选项：

选项	作用
–host	指定主机
–user	指定用户名
–password	指定密码
–port	指定端口
–databases	指定数据库
–incremental	创建增量备份
–incremental-basedir	指定包含完全备份的目录
–incremental-dir	指定包含增量备份的目录
–apply-log	对备份进行预处理操作
–redo-only	不回滚未提交事务
–copy-back	恢复备份目录

一般情况下，在备份完成后，数据是不能立即用于恢复操作的，因为备份的数据可能会包含尚未提交的事务或已经提交但是没有同步至数据文件的事务，这导致了数据文件的数据不一致，而apply-log选项的作用正是通过回滚未提交的事务以及同步已经提交的事务至数据文件的方法，使得数据文件的数据重新保证了一致性

-完全备份

[root@rzy bin]# innobackupex --user=root --password=123123 /beifen/ #直接完全备份至/beifen目录
。。。。。。
210522 17:51:48 completed OK! #ok表示备份成功
[root@rzy bin]# ll /beifen/
总用量 0
drwxr-x--- 5 root root 193 5月  22 17:51 2021-05-22_17-51-45
[root@rzy bin]# ll /beifen/2021-05-22_17-51-45/ #从备份的目录可以看出，是物理备份
总用量 12336 
-rw-r----- 1 root root      425 5月  22 17:51 backup-my.cnf #备份使用到的配置选项信息文件
-rw-r----- 1 root root      352 5月  22 17:51 ib_buffer_pool
-rw-r----- 1 root root 12582912 5月  22 17:51 ibdata1
drwxr-x--- 2 root root     4096 5月  22 17:51 mysql
drwxr-x--- 2 root root     8192 5月  22 17:51 performance_schema
drwxr-x--- 2 root root     8192 5月  22 17:51 sys
-rw-r----- 1 root root      113 5月  22 17:51 xtrabackup_checkpoints #备份的类型、状态和LSN状态信息文件
-rw-r----- 1 root root      417 5月  22 17:51 xtrabackup_info #mysql服务器当前正在使用的二进制日志文件和此时二进制日志时间的位置信息文件
-rw-r----- 1 root root     2560 5月  22 17:51 xtrabackup_logfile #备份的日志文件

-恢复

[root@rzy ~]# innobackupex --apply-log /beifen/2021-05-22_17-51-45/ #先对备份进行预处理，使数据一致
。。。。。。
210522 18:19:01 completed OK! 
[root@rzy ~]# rm -rf /usr/local/mysql/data/* #删除mysql文件
[root@rzy ~]# innobackupex --copy-back /beifen/2021-05-22_17-51-45/ #进行恢复
。。。。。。
210522 18:19:53 completed OK!
[root@rzy ~]# chown -R mysql:mysql /usr/local/mysql/data/ #增加属主和属组
[root@rzy ~]# ll /usr/local/mysql/data/ #查看是否已经恢复
总用量 122916
-rw-r----- 1 mysql mysql      352 5月  22 18:19 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 5月  22 18:19 ibdata1
-rw-r----- 1 mysql mysql 50331648 5月  22 18:19 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 5月  22 18:19 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 5月  22 18:19 ibtmp1
drwxr-x--- 2 mysql mysql     4096 5月  22 18:19 mysql
drwxr-x--- 2 mysql mysql     8192 5月  22 18:19 performance_schema
drwxr-x--- 2 mysql mysql     8192 5月  22 18:19 sys
-rw-r----- 1 mysql mysql      417 5月  22 18:19 xtrabackup_info
[root@rzy ~]# mysql -u root -p123123 -e "show databases;" #进入mysql查看是否可以正常操作
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

——————————————————————————————————————————————————————————
恢复三部曲
1.先把备份进行预操作，使数据一致
2.恢复备份至mysql
3.增加属主和属组
——————————————————————————————————————————————————————————

-增量备份

[root@rzy ~]# mysql -u root -p123123 #进入mysql创建表
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.12 Source distribution

Copyright (c) 2000, 2016, 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 aaa;
Query OK, 1 row affected (0.00 sec)

mysql> use aaa;
Database changed
mysql> create table aaa(id int);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into aaa values(1);
Query OK, 1 row affected (0.07 sec)

mysql> insert into aaa values(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into aaa values(3);
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
[root@rzy ~]# innobackupex --user=root --password=123123 --incremental /beifen/ --incremental-basedir=/beifen/2021-05-22_17-51-45/ #进行增量备份要记得使用--incremental-basedir=指定完全备份
。。。。。。
210522 18:31:39 completed OK!
[root@rzy ~]# ll /beifen/
总用量 0
drwxr-x--- 5 root root 245 5月  22 18:19 2021-05-22_17-51-45 #完全备份
drwxr-x--- 6 root root 230 5月  22 18:31 2021-05-22_18-31-36 #增量备份
[root@rzy 2021-05-22_18-31-36]# cat xtrabackup_checkpoints  #查看增量备份的xtrabackup_checkpoints文件
backup_type = incremental #备份类型为增量备份
from_lsn = 2522130 #lsn从2522130开始
to_lsn = 2522673   #lsn到2522673结束
last_lsn = 2522673
compact = 0
recover_binlog_info = 0
[root@rzy 2021-05-22_18-31-36]# cat /beifen/2021-05-22_17-51-45/xtrabackup_checkpoints  #查看完全备份的
backup_type = full-prepared #备份类型为完全备份
from_lsn = 0 #lsn从0开始
to_lsn = 2522130  #lsn从255130结束
last_lsn = 2522139
compact = 0
recover_binlog_info = 0

-恢复增量备份

[root@rzy 2021-05-22_18-31-36]# innobackupex --apply-log --redo-only /beifen/2021-05-22_17-51-45/ #先把完全备份的数据保持一致性
。。。。。。
210522 18:40:17 completed OK!
[root@rzy 2021-05-22_18-31-36]# innobackupex --apply-log --redo-only /beifen/2021-05-22_17-51-45/ --incremental-dir=/beifen/2021-05-22_18-31-36/ #把增量备份和全局备份的数据目录合并--incremental-dir=指定增量备份目录
。。。。。。
210522 18:41:24 completed OK!
[root@rzy 2021-05-22_18-31-36]# cat /beifen/2021-05-22_17-51-45/xtrabackup_checkpoints  #查看合并后的完全备份的数据目录的xtrabackup_checkpoints文件的lsn是否增加到了增量备份的数字
backup_type = log-applied
from_lsn = 0
to_lsn = 2522673 #发现lsn结束的数字和上面的增量备份的数字一致了
last_lsn = 2522673
compact = 0
recover_binlog_info = 0
[root@rzy 2021-05-22_18-31-36]# innobackupex --copy-back /beifen/2021-05-22_17-51-45/ #恢复数据
。。。。。。
210522 18:42:43 completed OK!
[root@rzy 2021-05-22_18-31-36]# chown -R mysql:mysql /usr/local/mysql/data/ #修改属主和属组
[root@rzy 2021-05-22_18-31-36]# mysql -u root -p123123 -e "show databases;" #确认mysql可以正常使用
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

-使用Xtrabackup的总结

增量备份需要使用参数–incremental指定需要备份到那个目录，在恢复增量备份和完全备份时，要使用–incremental-dir参数指定增量备份目录和完全备份目录合并
进行数据备份恢复时，要先使用参数–apply-log和redo-only使数据目录的数据保持一致
如果有多个增量备份，要把增量备份逐一和完全备份进行合并，可以查看完全备份的lsn值是否达到最近一次的增量备份的lsn值

礁之

关注

6
点赞
踩
4

收藏

觉得还不错? 一键收藏
5
评论
Mysql的备份

文章目录一、Mysql备份（1）binlog二进制日志概述（2）开启binlog二进制日志（3）在mysql中查看二进制日志（4）mysql二进制文件读取工具mysqlbinlog（5）二进制日志内容解释（6）二进制日志格式（7）备份类型（8）备份的策略（9）备份内容（10）Mysql最常用的三种备份工具二、Mysqldump工具-备份所有数据库-恢复数据-备份单个库-备份单个、多个表-导出表的结构-导出表的数据三、Xtrabackup工具（1）Xtrabackup概述（2）备份原理（3）备份过程（4）安装
复制链接

扫一扫