mysql数据库备份与恢复

1. 数据库常用备份方案

数据库备份方案：

• 全量备份
• 增量备份
• 差异备份

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

2. mysql备份工具mysqldump

//语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
//常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307


//创建一个jxy的数据库名，并创建一个student表
[root@mysql ~]# mysql -uroot -p123456        //这是的密码是你自己设置的密码
mysql> create database jxy;
Query OK, 1 row affected (0.00 sec)

mysql> use jxy;
Database changed
mysql> create table student(id int(11) not null primary key auto_incrementt,name varchar(100) not null,age tinyint(4) null);
Query OK, 0 rows affected (0.00 sec)

mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> insert into student(name,age) values ('harry',20),('tom',21),('natasha',24),('jerry',25);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | harry   |   20 |
|  2 | tom     |   21 |
|  3 | natasha |   24 |
|  4 | jerry   |   25 |
+----+---------+------+
4 rows in set (0.00 sec)


//查看数据库和表
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jxy                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use jxy;
Database changed
mysql> show tables;
+---------------+
| Tables_in_jxy |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

//备份整个数据库(全量备份)
[root@mysql ~]# ls
anaconda-ks.cfg
[root@mysql ~]# mysqldump -uroot -p123456 --all-databases > all-20190818.sql
[root@mysql ~]# ls
all-20190818.sql  anaconda-ks.cfg

//备份jxy库的student表
[root@mysql ~]# mysqldump -uroot -p123456 jxy student > tab-201908182301.
sql
[root@mysql ~]# ls
all-20190818.sql  anaconda-ks.cfg  tab-201908182301.sql

//备份jxy库
[root@mysql ~]# mysqldump -uroot -p123456 --databases jxy > lib-201908182303.sql
[root@mysql ~]# ls
all-20190818.sql  lib-201908182303.sql
anaconda-ks.cfg   tab-201908182301.sql

//误删jxy数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jxy                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

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

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

3. mysql数据恢复

//恢复jxy数据库
[root@mysql ~]# ls
all-20190818.sql  lib-201908182303.sql
anaconda-ks.cfg   tab-201908182301.sql
[root@mysql ~]# mysql -uroot -p123456 < all-20190818.sql 

mysql> use jxy;
Database changed

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

//创建名为runtime的表,往里面插入东西
mysql> create table runtime(id int(11) not null primary key auto_incrementt,name varchar(100) not null,age tinyint(4) null);
Query OK, 0 rows affected (0.00 sec)

mysql> desc runtime;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)  | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into runtime(name,age) values ('heihei',4),('haha',6);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from runtime;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | heihei |    4 |
|  2 | haha   |    6 |
+----+--------+------+
2 rows in set (0.00 sec)

//备份jxy库的runtime表
[root@mysql ~]# mysqldump -uroot -p123456 jxy runtime > tab-201908182301.sql 
[root@mysql ~]# ls
all-20190818.sql  lib-201908182303.sql
anaconda-ks.cfg   tab-201908182301.sql

mysql> show tables;
+---------------+
| Tables_in_jxy |
+---------------+
| runtime       |
| student       |
+---------------+
2 rows in set (0.00 sec)


//恢复jxy数据库的runtime表
mysql> use jxy;
Database changed

mysql> select * from jxy.runtime;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | heihei |    4 |
|  2 | haha   |    6 |
+----+--------+------+
2 rows in set (0.00 sec)

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

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

mysql> source tab-201908182301.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
......
......
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_jxy |
+---------------+
| runtime       |
| student       |
+---------------+
2 rows in set (0.00 sec)

mysql> select * from runtime;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | heihei |    4 |
|  2 | haha   |    6 |
+----+--------+------+
2 rows in set (0.00 sec)

//误删除整个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jxy                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database jxy;
Query OK, 2 rows affected (0.03 sec)

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

//恢复jxy整个数据库
[root@mysql ~]# mysql -uroot -p123456 < all-20190818.sql 
[root@mysql ~]# ls
all-20190818.sql  lib-201908182303.sql
anaconda-ks.cfg   tab-201908182301.sql

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

4. 差异备份与恢复

4.1. mysql差异备份

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

[root@mysql ~]# vim /etc/my.cnf
[root@mysql ~]# 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

server-id = 1          //设置服务器标识符
log-bin = mysql_bin    //开启二进制日志功能

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

//对数据库进行完全备份
mysql> use jxy;
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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jxy                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from jxy;
+---------------+
| Tables_in_jxy |
+---------------+
| runtime       |
| student       |
+---------------+
2 rows in set (0.00 sec)

mysql> select * from jxy.runtime;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | heihei |    4 |
|  2 | haha   |    6 |
+----+--------+------+
2 rows in set (0.00 sec)

mysql> select * from jxy.student;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | harry   |   20 |
|  2 | tom     |   21 |
|  3 | natasha |   24 |
|  4 | jerry   |   25 |
+----+---------+------+
4 rows in set (0.00 sec)

//完全备份
[root@mysql ~]# mysqldump -root -p123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20190818.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# ll
总用量 800
-rw-r--r--. 1 root root      0 8月  18 23:55 all-20190818.sql
-rw-------. 1 root root   1482 3月  25 03:01 anaconda-ks.cfg
-rw-r--r--. 1 root root   2115 8月  18 23:03 lib-201808182303.sql
-rw-r--r--. 1 root root 803285 8月  18 23:55 oot
-rw-r--r--. 1 root root   1948 8月  18 23:18 tab-201908182301.sql


//修改runtime表
[root@mysql ~]# mysql -uroot -p123456
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 5
Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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 jxy;
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> 
mysql> select * from runtime;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | heihei |    4 |
|  2 | haha   |    6 |
+----+--------+------+
2 rows in set (0.00 sec)

mysql> update runtime set age = 10 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from runtime;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | heihei |    4 |
|  2 | haha   |   10 |
+----+--------+------+
2 rows in set (0.00 sec)

4.2. mysql差异备份恢复

//误删数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jxy                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database jxy;
Query OK, 2 rows affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
//由此可以看到jxy这个数据库已经被删除了

刷新创建新的二进制日志
[root@mysql ~]# ll /opt/data/
总用量 122956
-rw-r-----. 1 mysql mysql       56 8月  18 22:34 auto.cnf
-rw-r-----. 1 mysql mysql      542 8月  18 23:42 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月  19 00:04 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月  19 00:04 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月  18 22:33 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月  18 23:55 ibtmp1
drwxr-x---. 2 mysql mysql     4096 8月  18 23:38 mysql
-rw-r-----. 1 mysql mysql      586 8月  19 00:04 mysql_bin.000006
-rw-r-----. 1 mysql mysql       19 8月  18 23:55 mysql_bin.index
-rw-r-----. 1 mysql mysql    12496 8月  18 23:42 mysql.err
-rw-r-----. 1 mysql mysql        6 8月  18 23:42 mysql.pid
drwxr-x---. 2 mysql mysql     8192 8月  18 22:34 performance_schema
-rw-r-----. 1 mysql mysql     8485 8月  18 23:42 server1.err
drwxr-x---. 2 mysql mysql     8192 8月  18 22:34 sys

//刷新创建新的二进制日志
[root@mysql ~]# mysqladmin -uroot -p123456 flush-logs
[root@mysql ~]# ll /opt/data/
总用量 122960
-rw-r-----. 1 mysql mysql       56 8月  18 22:34 auto.cnf
-rw-r-----. 1 mysql mysql      542 8月  18 23:42 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月  19 00:04 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月  19 00:04 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月  18 22:33 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月  18 23:55 ibtmp1
drwxr-x---. 2 mysql mysql     4096 8月  18 23:38 mysql
-rw-r-----. 1 mysql mysql      633 8月  19 00:09 mysql_bin.000006
-rw-r-----. 1 mysql mysql      154 8月  19 00:09 mysql_bin.000007
-rw-r-----. 1 mysql mysql       38 8月  19 00:09 mysql_bin.index
-rw-r-----. 1 mysql mysql    12496 8月  18 23:42 mysql.err
-rw-r-----. 1 mysql mysql        6 8月  18 23:42 mysql.pid
drwxr-x---. 2 mysql mysql     8192 8月  18 22:34 performance_schema
-rw-r-----. 1 mysql mysql     8485 8月  18 23:42 server1.err
drwxr-x---. 2 mysql mysql     8192 8月  18 22:34 sys

恢复完全备份
[root@mysql ~]# mysql -uroot -p123456 < all-20190818.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jxy                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@mysql ~]# mysql -uroot -p123456 -e 'show tables from jxy;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_jxy |
+---------------+
| runtime       |
| student       |
+---------------+
[root@mysql ~]# mysql -uroot -p123456 -e 'select * from jxy.runtime;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | heihei |    4 |
|  2 | haha   |    6 |
+----+--------+------+
[root@mysql ~]# mysql -uroot -p123456 -e 'select * from jxy.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | harry   |   20 |
|  2 | tom     |   21 |
|  3 | natasha |   24 |
|  4 | jerry   |   25 |
+----+---------+------+

恢复差异备份
[root@mysql ~]# ll /opt/data/
总用量 123724
-rw-r-----. 1 mysql mysql       56 8月  18 22:34 auto.cnf
-rw-r-----. 1 mysql mysql      542 8月  18 23:42 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月  19 00:25 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月  19 00:25 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月  18 22:33 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月  19 00:17 ibtmp1
drwxr-x---. 2 mysql mysql       96 8月  19 00:25 jxy
drwxr-x---. 2 mysql mysql     4096 8月  19 00:21 mysql
-rw-r-----. 1 mysql mysql      201 8月  19 00:21 mysql_bin.000006
-rw-r-----. 1 mysql mysql   785447 8月  19 00:25 mysql_bin.000007
-rw-r-----. 1 mysql mysql       38 8月  19 00:21 mysql_bin.index
-rw-r-----. 1 mysql mysql    12598 8月  19 00:16 mysql.err
-rw-r-----. 1 mysql mysql        6 8月  18 23:42 mysql.pid
drwxr-x---. 2 mysql mysql     8192 8月  18 22:34 performance_schema
-rw-r-----. 1 mysql mysql     8485 8月  18 23:42 server1.err
drwxr-x---. 2 mysql mysql     8192 8月  18 22:34 sys

//检查误删数据库的位置在什么地方
[root@mysql ~]# mysql -e 'show binlog events in "mysql_bin.000006";'
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000006 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.22-log, Binlog ver: 4 |
| mysql_bin.000006 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000006 | 154 | Rotate         |         1 |         201 | mysql_bin.000007;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

//使用mysqlbinlog恢复差异备份
[root@mysql ~]# mysqlbinlog --stop-position=154 /opt/data/mysql_bin.000006 | 
mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# mysql -uroot -p123456 -e 'select * from jxy.runtime;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | heihei |    4 |
|  2 | haha   |    6 |
+----+--------+------+
[root@mysql ~]# mysql -uroot -p123456 -e 'select * from jxy.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | harry   |   20 |
|  2 | tom     |   21 |
|  3 | natasha |   24 |
|  4 | jerry   |   25 |
+----+---------+------+