备份介绍与分类
数据库备份方案一般分为冷备份和热备份
冷备份：也被称为物理备份，拷贝数据库目录下的文件进行备份
热备份：数据库服务正常运行情况，直接对数据库的数据进行备份。备份方案有全量备份、增量备份和差异备份
全量备份
一般称为：全备，每次都将所有数据备份一遍，优点是安全，即使数据丢失也不怕，将数据导回去即可，缺点是耗资源、备份时间长
增量备份
增备，第一次备份时将所有数据备份一遍（也就是进行一次全备），第二次开始每次备份都只备份基于上一次备份的基础上改变的部分。 但是在恢复数据的时候，需要按照备份的顺序，先恢复全量备份时的数据，再恢复第一次增量的数据，再恢复第二次增量的数据。但是如果中途某一次的备份数据丢了，想要恢复很难，一般不使用
差异备份
差备，第一次也是全备，第二次只备份了的数据，没有修改的不动，也就是二次备份对于第三次备份来说是一次全备。差异备份是利用二进制日志来记录执行的操作，通过操作来恢复，所以进行差异备份之前需要开启MySQL的二进制日志功能
备份操作
备份之前先创建一些数据
mysql> select * from test.test1;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 21 |
+----+-------+------+
2 rows in set (0.00 sec)
mysql>全量备份
热备份
有密备份
使用mysqldump进行备份。-u指定用户，-p指定密码，-P指定端口，-h指定主机IP
#备份单张表
[root@test1 ~]# mysqldump -uroot -psakura test test1 > test1.sql //将test库中的test1表导出，保存为test1.sql
#备份多张表
[root@test1 ~]# mysqldump -uroot -psakura test test1 test2 > test1.sql //备份test库中的test1和test2表
#备份单个数据库
[root@test1 ~]# mysqldump -uroot -psakura --databases test > test.sql
#备份多个数据库
[root@test1 ~]# mysqldump -uroot -psakura --databases test abc > test.sql //备份test和abc两个库
#备份整个MySQL数据库
[root@test1 ~]# mysqldump -uroot -psakura --all-databases > mysqldump.sql
#备份命名时一般会以日期时间来命名，方便区分
例如：
[root@test1 ~]# mysqldump -uroot -psakura --all-databases > mysqlall-$(date '+%Y%m%d%H%M%S')
//备份MySQL整个数据库，命名时会自动带上当前年月日和时分秒免密备份
#与免密登录一样需要先写存放用户名密码的文件
[root@test1 ~]# cd
[root@test1 ~]# vim .my.cnf
[root@test1 ~]# cat .my.cnf
[mysqldump] //做备份，所以要改为mysqldump
user=root
password=sakura
[root@test1 ~]# mysqldump --all-databases > mysqlall-$(date '+%Y%m%d%H%M%S') //再备份就不需要输入密码冷备份
#进入数据存放目录
[root@test1 ~]# cd /opt/data/
[root@test1 data]# ls
auto.cnf client-key.pem ib_logfile1 public_key.pem test
ca-key.pem ib_buffer_pool mysql server-cert.pem test1.err
ca.pem ibdata1 performance_schema server-key.pem
client-cert.pem ib_logfile0 private_key.pem sys
[root@test1 data]# mkdir /tmp/mysql.bak
[root@test1 data]# mv * /tmp/mysql.bak/
[root@test1 data]# ls
#数据目录中的内容全部移走了，此时数据库服务仍然开启，但是进入数据库里面不会有仁和数据
[root@test1 data]# ss -anlt | grep 3306
LISTEN 0 80 *:3306 *:*
[root@test1 data]# mysql -uroot -psakura
........
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql>
#将文件移回data目录，数据库中也可以正常看到之前创建的库和表了
[root@test1 data]# mv /tmp/mysql.bak/* /opt/data/
[root@test1 data]# mysql -uroot -psakura
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.34 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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 |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
......
mysql> select * from test.test1;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 21 |
+----+-------+------+
2 rows in set (0.01 sec)
mysql>全备恢复
恢复表：
#在命令行界面恢复，需要指定恢复到test数据库
[root@test1 ~]# mysql test < test.bak-20230223
#在MySQL数据库界面恢复，如果恢复的时候不在存放.sql文件的目录下，需要带上绝对路径
[root@test1 ~]# cd
[root@test1 ~]# ls
password test1.sql
[root@test1 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.34 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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 test;
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> source test1.sql;
Query OK, 0 rows affected (0.00 sec)
...........
mysql> select * from test.test1;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 2 | jerry | 21 |
+----+-------+------+
1 row in set (0.00 sec)
mysql>恢复数据库
恢复单个数据库和恢复全备数据库方法一样
#在命令行恢复数据库
[root@test1 ~]# mysql < test.sql
#在MySQL命令行恢复，同样如果不在存放备份文件的目录下，在恢复时需要跟上恢复文件的绝对路径
mysql> source test.sql;
.......
mysql>差异备份
开启二进制日志
#在MySQL配置文件my.cnf中添加：
# server-id=10 //主机标识，数字可以自定义
# log-bin=mysql_bin //指定日志的名字，可以自定义，不一定要叫‘mysql_bin’
[root@test1 ~]# cat >> /etc/my.cnf <<EOF
> server-id=10
> bin-log=mysql_bin
> EOF
[root@test1 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id=10
log-bin=mysql_bin
[root@test1 ~]# systemctl restart mysqld.service先进行一次全备
#这里的全备与之前的有区别，需要添加额外的参数
#--single-transaction ：开启事务日志
#--flush-logs ：刷新日志
#--master-data=2 ：指定master数据标识符为2
#--all-databases ： 全备
#--delete-master-logs ： 删除master的日志
[root@test1 ~]# mysqldump -uroot -psakura --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-chayi.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@test1 ~]#
#改变数据库，此处新建了一个class数据库
mysql> create database class;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| class |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql>
#查看没有刷新之前的日志
[root@test1 ~]# ll /opt/data/ | grep mysql_bin.000
-rw-r----- 1 mysql mysql 316 Feb 26 15:45 mysql_bin.000003 //现在是000003
#刷新二进制日志
[root@test1 ~]# mysqladmin -uroot -psakura flush-logs
[root@test1 ~]# ll /opt/data/ | grep mysql_bin.000
-rw-r----- 1 mysql mysql 363 Feb 26 15:57 mysql_bin.000003
-rw-r----- 1 mysql mysql 154 Feb 26 15:57 mysql_bin.000004 //刷新之后多了一个000004，现在如果再对数据库进行操作，这些操作的记录会被记录到000004文件中
#进入数据库，查看000003日志在数据库中的哪个地方
mysql> show binlog events in 'mysql_bin.000003'; //查看二进制日志事件，pod表示事件开始时的操作id号 ，end_log_pod表示结束时的操作id号，pod到end_pod之间，就是执行这次事件的范围。server_id对应的就是之前在配置文件中设置的主机标识
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 | 4 | Format_desc | 10 | 123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids | 10 | 154 | |
| mysql_bin.000003 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 219 | Query | 10 | 316 | create database class |
| mysql_bin.000003 | 316 | Rotate | 10 | 363 | mysql_bin.000004;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
5 rows in set (0.00 sec)
mysql>
#将二进制日志转换为文本文件方便查看理解
#--no-defaults ：不要读取任何选项文件
#--base64-output=decode-rows ： 利用64位字符，以行的方式导出核心部分
[root@test1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000003 > /opt/mysql000003.txt具体的mysqlbinlog命令解析可以查看此文档->mysqlbinlog命令详解
差备恢复
注意：差异恢复之前，需要先全量恢复一次
[root@test1 ~]# mysql -uroot -psakura < all-chayi.sql方法一：通过操作ID来恢复
#--start-position=219 ：恢复 操作ID 219之后的所有操作
#--stop-position=219 ：恢复 操作ID 219之前的所有操作
[root@test1 ~]# mysqlbinlog --stop-position=219 /opt/data/mysql_bin.000003 | mysql -uroot -psakura
[root@test1 ~]#方法二：通过操作时间恢复
#需要先将binlog日志转换为文本类型方便查看时间
[root@test1 ~]# cd /opt/data/
[root@test1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000008 > /opt/mysql000008.txt
[root@test1 data]# cat /opt/mysql000008.txt
.....
#230227 9:46:22 server id 10 end_log_pos 154 CRC32 0x9e357117 Previous-GTIDs
.....
[root@test1 ~]# mysqlbinlog --stop-datetime='23-02-27 9:47:33' /opt/data/mysql_bin.000008 | mysql -uroot -psakura //年月日之间要隔开本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
