mysql数据库备份与恢复

数据库常用备份方案

数据库备份方案及特点

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝数据恢复快备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推没有重复的备份数据备份时间短恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

mysql备份工具mysqldump

语法

mysqldump [OPTIONS] database_name [tables_name ...]            #备份数据库中的一个或多个表
mysqldump [OPTIONS] --all-databases [OPTIONS]                  #备份所有的数据库
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]     #备份其中一个或多个数据库,使用空格分隔

常用的 OPTIONS

-uUSERNAME      #指定数据库用户名
-hHOST          #指定服务器主机，请使用ip地址
-pPASSWORD      #指定数据库用户的密码
-P#             #指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
--all-databases,-A  #备份所有数据库
--databases,-B  #要备份的数据库，可以同时备份多个，使用空格分隔

备份整个数据库（全备）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| it                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

[root@localhost ~]# 
[root@localhost ~]# mysqldump -uroot -p -hlocalhost --all-databases > all_databases_backup.sql
Enter password: 
[root@localhost ~]# ls
all_databases_backup.sql  anaconda-ks.cfg

备份 it 库中的 user 表和 student 表

mysql> show tables from it;
+--------------+
| Tables_in_it |
+--------------+
| student      |
| user         |
+--------------+
2 rows in set (0.00 sec)

[root@localhost ~]# mysqldump -uroot -p -hlocalhost it user student > tables_backup.sql
Enter password: 
[root@localhost ~]# ls
all_databases_backup.sql  anaconda-ks.cfg  tables_backup.sql

备份 it 库

[root@localhost ~]# mysqldump -uroot -p -hlocalhost --databases it > it_database_backup.sql
Enter password: 
[root@localhost ~]# ls
all_databases_backup.sql  anaconda-ks.cfg  it_database_backup.sql  tables_backup.sql

备份 mysql 库和 sys 库

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

[root@localhost ~]# mysqldump -uroot -p -hlocalhost --databases sys mysql > databases_backup.sql
Enter password: 
[root@localhost ~]# ls
all_databases_backup.sql  anaconda-ks.cfg  databases_backup.sql  it_database_backup.sql  tables_backup.sql

mysql数据恢复

恢复 it 库

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

[root@localhost ~]# mysql -uroot -p -hlocalhost < it_database_backup.sql
Enter password: 
[root@localhost ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| it                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

恢复 user 表和 student 表

mysql> show tables from it;
Empty set (0.00 sec)

[root@localhost ~]# mysql -uroot -p -hlocalhost it < tables_backup.sql 
Enter password: 
[root@localhost ~]# mysql -uroot -p -e 'show tables from it;'
Enter password: 
+--------------+
| Tables_in_it |
+--------------+
| student      |
| user         |
+--------------+

恢复 sys 库和 mysql 库

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

[root@localhost ~]# mysql -uroot -p -hlocalhost < databases_backup.sql 
Enter password: 
[root@localhost ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| it                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

恢复误删的数据库

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

[root@localhost ~]# mysql -uroot -p -hlocalhost < all_databases_backup.sql 
Enter password: 
[root@localhost ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| it                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

差异备份与恢复

mysql 差异备份

开启MySQL服务器的二进制日志功能

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/opt/data
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
port=3306
pid-file=/opt/data/mysql.pid
character-set-server=utf8
log-error=/var/log/mysqld.log

server-id=1             #设置服务器标识符；只能是数字
log-bin=mysql_bin       #开启二进制日志功能

重启数据库服务

[root@localhost ~]# service mysqld restart
Shutting down MySQL... SUCCESS! 
Starting MySQL.......... SUCCESS!

开始差异备份

首先进行全备

[root@localhost ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| it                 |
| mysql              |
| sys                |
+--------------------+
[root@localhost ~]# mysqldump -uroot -p --all-databases > all-databases.sql
Enter password: 
[root@localhost ~]# ls
all-databases.sql  anaconda-ks.cfg

增加新内容

mysql> select * from user;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan |   100 |
|  2 | lisi     |   110 |
|  3 | wangwu   |   111 |
|  4 | xiaoliu  |   120 |
|  5 | liba     |   125 |
+----+----------+-------+
5 rows in set (0.00 sec)

mysql> insert user values(6,'aaa',130),(7,'bbb',135);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan |   100 |
|  2 | lisi     |   110 |
|  3 | wangwu   |   111 |
|  4 | xiaoliu  |   120 |
|  5 | liba     |   125 |
|  6 | aaa      |   130 |
|  7 | bbb      |   135 |
+----+----------+-------+
7 rows in set (0.00 sec)

mysql差异备份恢复

模拟误删数据

[root@localhost ~]# mysql -uroot -p -e 'drop database it;'
Enter password: 
[root@localhost ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sys                |
+--------------------+

刷新创建新的二进制日志

[root@localhost ~]# mysqladmin -uroot -p flush-logs
Enter password: 
[root@localhost ~]# ll /opt/data/
total 188492
-rw-r-----. 1 mysql mysql       56 Jan  8 16:03 auto.cnf
-rw-------. 1 mysql mysql     1676 Jan  8 16:03 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jan  8 16:03 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jan  8 16:03 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jan  8 16:03 client-key.pem
-rw-r-----. 1 mysql mysql     2912 Jan  9 16:34 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 Jan  9 17:10 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jan  9 17:10 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jan  8 16:03 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jan  9 16:34 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Jan  8 20:35 mysql
-rw-r-----. 1 mysql mysql      766 Jan  9 17:01 mysql_bin.000001
-rw-r-----. 1 mysql mysql      352 Jan  9 17:09 mysql_bin.000002     
-rw-r-----. 1 mysql mysql      154 Jan  9 17:09 mysql_bin.000003   
-rw-r-----. 1 mysql mysql       57 Jan  9 17:09 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 Jan  9 16:34 mysql.pid
-rw-------. 1 mysql mysql     1676 Jan  8 16:03 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jan  8 16:03 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jan  8 16:03 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jan  8 16:03 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jan  8 20:28 sys

恢复完全备份

[root@localhost ~]# mysql -uroot -p < all-databases.sql 
Enter password: 
[root@localhost ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| it                 |
| mysql              |
| sys                |
+--------------------+

恢复差异备份
- 检查误删数据库的位置在什么地方

mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 219 | Query          |         1 |         305 | drop database it                      |
| mysql_bin.000002 | 305 | Rotate         |         1 |         352 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
5 rows in set (0.01 sec)
#从上述列表可以看出，数据删除是在219开始，结束是在305，也就是说在219节点数据库未删除，到305数据库已经删除

使用 mysqlbinlog 恢复差异备份

[root@localhost ~]# mysqlbinlog --stop-position=219 /opt/data/mysql_bin.000002 | mysql -uroot -p
Enter password: 
[root@localhost ~]# mysql -uroot -p -e 'show tables from it;'
Enter password: 
+--------------+
| Tables_in_it |
+--------------+
| student      |
| user         |
+--------------+