Linux——MySQL备份还原

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

一、binlog

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

Binlog的用途

• 主从同步
• 恢复数据库

1.开启二进制日志功能

#修改服务的配置文件
[root@mysql ~]# cat /etc/my.cnf 
[mysqld]
......
server-id=1
#开启bin-log功能，需指定server_id，否则可能报错
log-bin=/usr/local/mysql/data/bin-log
#指定二进制日志存放路径及二进制日志前缀
[root@mysql ~]# systemctl  restart  mysqld
[root@mysql data]# ll bin-log.*
-rw-r----- 1 mysql mysql 154 1月  25 15:56 bin-log.000001
#该文件为二进制日志文件，每次重启mysql或执行flush logs命令，就会产生一个新的二进制日志文件
-rw-r----- 1 mysql mysql  37 1月  25 15:56 bin-log.index
#二进制日志文件的索引

2.查看是否开启

mysql>  show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
#ON表示已经开启，OFF表示未开启

3.对数据库进行增删改操作

#清空所有的二进制文件，从000001开始
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> create database qq;
Query OK, 1 row affected (0.00 sec)

mysql> use qq;
Database changed
mysql> create table tb1(
    -> id int primary key auto_increment ,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql> insert into tb1(name) values('lisi'),('zhansan');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

#重新开始一个新的日志文件再执行操作。注意，此时上面所有的操作写入的是第一个二进制日志文件，以下的命令操作写入第二个二进制文件中！
mysql> flush logs;
Query OK, 0 rows affected (0.11 sec)

#删除id为2的数据
mysql> delete from tb1 where id =2;
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb1(name) values('tom');
Query OK, 1 row affected (0.09 sec)

mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | lisi |
|  3 | tom  |
+----+------+
2 rows in set (0.00 sec)

4.查看MySQL的二进制日志

1.查看二进制日志

mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| bin-log.000001 |       830 |
| bin-log.000002 |       672 |
+----------------+-----------+
2 rows in set (0.00 sec)

2.查看二进制文件内容

语法：

mysql> help show binlog events
Name: 'SHOW BINLOG EVENTS'
Description:
Syntax:
SHOW BINLOG EVENTS
   [IN 'log_name']
   [FROM pos]
   [LIMIT [offset,] row_count]

Shows the events in the binary log. If you do not specify 'log_name',
the first binary log is displayed. SHOW BINLOG EVENTS requires the
REPLICATION SLAVE privilege.

URL: https://dev.mysql.com/doc/refman/5.7/en/show-binlog-events.html

• **in：**指定要查看的二进制文件

• **from：**指定从哪个“pos”位置开始查看

• **limit：**限制返回的行数，offset是指跳过多少行再显示

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

mysql> show binlog events in 'bin-log.000001';
+----------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------+
| Log_name       | Pos | Event_type     | Server_id | End_log_pos | Info                                                                               |
+----------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------+
| bin-log.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.32-log, Binlog ver: 4                                              |
| bin-log.000001 | 123 | Previous_gtids |         1 |         154 |                                                                                    |
| bin-log.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                               |
| bin-log.000001 | 219 | Query          |         1 |         307 | create database qq                                                                 |
| bin-log.000001 | 307 | Anonymous_Gtid |         1 |         372 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                               |
| bin-log.000001 | 372 | Query          |         1 |         514 | use `qq`; create table tb1(
id int primary key auto_increment ,
name varchar(20)
) |
| bin-log.000001 | 514 | Anonymous_Gtid |         1 |         579 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                               |
| bin-log.000001 | 579 | Query          |         1 |         649 | BEGIN                                                                              |
| bin-log.000001 | 649 | Table_map      |         1 |         696 | table_id: 109 (qq.tb1)                                                             |
| bin-log.000001 | 696 | Write_rows     |         1 |         754 | table_id: 109 flags: STMT_END_F                                                    |
| bin-log.000001 | 754 | Xid            |         1 |         785 | COMMIT /* xid=14 */                                                                |
| bin-log.000001 | 785 | Rotate         |         1 |         830 | bin-log.000002;pos=4                                                               |
+----------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)

#从219开启后的隔一行开启查询4行
mysql> show binlog events in 'bin-log.000001' from 219 limit 1,4;
+----------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------+
| Log_name       | Pos | Event_type     | Server_id | End_log_pos | Info                                                                               |
+----------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------+
| bin-log.000001 | 307 | Anonymous_Gtid |         1 |         372 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                               |
| bin-log.000001 | 372 | Query          |         1 |         514 | use `qq`; create table tb1(
id int primary key auto_increment ,
name varchar(20)
) |
| bin-log.000001 | 514 | Anonymous_Gtid |         1 |         579 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                               |
| bin-log.000001 | 579 | Query          |         1 |         649 | BEGIN                                                                              |
+----------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

• SHOW BINARY LOGS 等价于 SHOW MASTER LOGS
• PURGE BINARY LOGS用于删除二进制日志

mysql>purge binary logs to 'mysql-bin.000010'; 
#把这个文件之前的其他文件都删除掉
mysql>purge binary logs before '2021-01-24 22:46:26';
#把指定时间之前的二进制文件删除了

5.通过二进制日志恢复数据

第一步

找到删除zhangsan记录的sql语句在二进制日志中的位置，每条sql语句都是一个事务，所以需要从其begin到commit，才算是完整的sql语句

[root@mysql ~]# cd /usr/local/mysql/data/
[root@mysql data]# mysqlbinlog -v bin-log.000002 
......
BEGIN
/*!*/;
# at 289
#210125 16:07:29 server id 1  end_log_pos 336 CRC32 0xc4549c28 	Table_map: `qq`.`tb1` mapped to number 109
# at 336
#210125 16:07:29 server id 1  end_log_pos 384 CRC32 0x7b9a353a 	Delete_rows: table id 109 flags: STMT_END_F

BINLOG '
QXwOYBMBAAAALwAAAFABAAAAAG0AAAAAAAEAAnFxAAN0YjEAAgMPAjwAAiicVMQ=
QXwOYCABAAAAMAAAAIABAAAAAG0AAAAAAAEAAgAC//wCAAAAB3poYW5zYW46NZp7
'/*!*/;
### DELETE FROM `qq`.`tb1`
### WHERE
###   @1=2
###   @2='zhansan'
# at 384
#210125 16:07:29 server id 1  end_log_pos 415 CRC32 0x0b91bb9b 	Xid = 16
COMMIT/*!*/;
#从查看结果中可以看出，delete事件发生的位置在289，结束在415

第二步

**恢复流程：**直接用bin-log日志将数据库恢复到删除位置287前,然后跳过故障点,再进行恢复下面所有的操作

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

第三步

模拟删除数据库

mysql> drop database qq;
Query OK, 1 row affected (0.01 sec)

mysql>

第四步

利用binlog恢复数据

[root@mysql ~]# mysql -uroot -p < /opt/bin-log.000001.sql 
Enter password: 
[root@mysql ~]# mysql -uroot -p < /opt/289.sql 
Enter password: 
[root@mysql ~]# mysql -uroot -p < /opt/415.sql 
Enter password: 

第五步

恢复完成后查看数据

mysql>  select * from qq.tb1;
+----+---------+
| id | name    |
+----+---------+
|  1 | lisi    |
|  2 | zhansan |
|  3 | tom     |
+----+---------+
3 rows in set (0.00 sec)

6.mysqlbinlog常见的选项

• **–start-datetime ：**从二进制日志中读取指定时间戳或者本地计算机时间之后的日志事件。
• –stop-datetime： 从二进制日志中读取指定时间戳或者本地计算机时间之前的日志事件。
• **–start-position：**从二进制日志中读取指定position 事件位置作为开始。
• **–stop-position ：**从二进制日志中读取指定position 事件位置作为事件截至

二、mysqldump

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

单表

备份单表（test.tb1）到/opt/下

mysql> select schema();
+----------+
| schema() |
+----------+
| test     |
+----------+
1 row in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb1            |
+----------------+
1 row in set (0.00 sec)
#备份
[root@mysql ~]# mysqldump -u root -p  test tb1 > /opt/test.sql
Enter password: 

#删除
mysql> drop table test.tb1;
Query OK, 0 rows affected (0.01 sec)


#恢复
[root@mysql ~]# mysql  -u root -p test < /opt/test.sql 
Enter password: 

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb1            |
+----------------+
1 row in set (0.00 sec)

多个表

备份多表（test.{tb1,tb2}）到/opt/下

#备份
[root@mysql ~]# mysqldump -u root -p  test {tb1,tb2} > /opt/test1.sql
Enter password: 

#恢复
[root@mysql ~]# mysql  -u root -p test < /opt/test1.sql 
Enter password: 

单个库

备份单表（test1）到/opt/下

#备份
[root@mysql ~]# mysqldump -u root -p --databases test1 > /opt/test2.sql
Enter password: 

#恢复
[root@mysql ~]# mysql  -u root -p < /opt/test2.sql 
Enter password: 

多个库

备份单表（test,test1）到/opt/下

#备份
[root@mysql ~]# mysqldump -u root -p --databases {test,test1} > /opt/test3.sql
Enter password: 

#恢复
[root@mysql ~]# mysql  -u root -p < /opt/test3.sql 
Enter password: 

所有库

备份数据库中所有的库到/opt/下

–all-databases：也可以省略写-A

#备份
[root@mysql ~]# mysqldump -u root -p --all-databases > /opt/test4.sql
Enter password: 

#恢复
[root@mysql ~]# mysql  -u root -p < /opt/test4.sql 
Enter password: