mysql数据库备份与恢复
数据库常用备份方案
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内,对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
mysql备份工具mysqldump
//新建一个库school，并新建一个表student，插入数据
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use school
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.04 sec)
mysql> insert student(name,age) values('haruki',20),('akira',18),('neneka',21);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | haruki | 20 |
| 2 | akira | 18 |
| 3 | neneka | 21 |
+----+--------+------+
3 rows in set (0.00 sec)
// 全量备份数据库
[root@cst ~]# mysqldump -uroot -p1234 --all-databases >all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
//删除school后恢复
[root@cst ~]# mysql -uroot -p1234 -e 'drop database school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cst ~]# mysql -uroot -p1234 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@cst ~]# mysql -uroot -p1234 < all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cst ~]# mysql -uroot -p1234 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
恢复成功
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
备份实例
MariaDB [cst]> show databases;
+--------------------+
| Database |
+--------------------+
| cst |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [cst]> show tables;
+---------------+
| Tables_in_cst |
+---------------+
| student |
| student2 |
| student3 |
+---------------+
3 rows in set (0.000 sec)
//备份整个数据库(全备)
[root@cst ~]# cd /opt/
[root@cst opt]# mkdir mydata
[root@cst opt]# ls
mydata
[root@cst opt]# cd mydata/
[root@cst mydata]# mysqldump -uroot -p6666 --all-databases > all.sql
[root@cst mydata]# ls
all.sql
//备份cst库
[root@cst mydata]# mysqldump -uroot -p6666 --databases cst > data_cst.sql
[root@cst mydata]# ls
all.sql data_cst.sql
//备份cst库中的student和student3表
[root@cst mydata]# mysqldump -uroot -p6666 cst student student3 > data_tables.sql
[root@cst mydata]# ls
all.sql data_cst.sql data_tables.sql
恢复实例
//模拟误删cst库
MariaDB [(none)]> drop database cst;
Query OK, 3 rows affected (0.007 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
//恢复cst库
[root@cst mydata]# mysql -uroot -p6666 < data_cst.sql
[root@cst mydata]# mysql -uroot -p6666 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| cst |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
//模拟误删除cst库中的student和student3表
MariaDB [cst]> drop table student;
Query OK, 0 rows affected (0.002 sec)
MariaDB [cst]> drop table student3;
Query OK, 0 rows affected (0.002 sec)
MariaDB [cst]> show tables;
+---------------+
| Tables_in_cst |
+---------------+
| student2 |
+---------------+
1 row in set (0.000 sec)
//恢复删除的表
MariaDB [cst]> source data_tables.sql;
MariaDB [cst]> show tables;
+---------------+
| Tables_in_cst |
+---------------+
| student |
| student2 |
| student3 |
+---------------+
3 rows in set (0.000 sec)
//删除所有库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | //该库无法删除
+--------------------+
1 row in set (0.000 sec)
//恢复所有库
[root@cst mydata]# mysql -uroot -p6666 < all.sql
[root@cst mydata]# mysql -uroot -p6666 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| cst |
| information_schema |
| mysql |
+--------------------+
#一个系统库无法恢复，但不影响正常使用。
差异备份与恢复
mysql差异备份
//开启MySQL服务器的二进制日志功能
[root@cst ~]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql_bin
[root@cst ~]# systemctl restart mariadb.service
//对数据库进行完全备份
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| cst |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
3 rows in set (0.000 sec)
MariaDB [(none)]> use cst;
Database changed
MariaDB [cst]> show tables;
+---------------+
| Tables_in_cst |
+---------------+
| student |
| student2 |
| student3 |
+---------------+
3 rows in set (0.000 sec)
MariaDB [cst]> select * from student3;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | mana | 20 |
| 2 | kiruya | 15 |
| 3 | akira | 24 |
+----+--------+------+
3 rows in set (0.000 sec)
//完全备份
[root@cst mydata]# mysqldump -uroot --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all.sql
[root@cst mydata]# ls
all.sql
//增加新内容
MariaDB [cst]> insert into student3(name,age) values('genji',20),('hanzo',124);
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [cst]> select * from student3;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | mana | 20 |
| 2 | kiruya | 15 |
| 3 | akira | 24 |
| 4 | genji | 20 |
| 5 | hanzo | 124 |
+----+--------+------+
5 rows in set (0.000 sec)
MariaDB [cst]> update student3 set age = 24 where id = 5;
Query OK, 1 row affected (0.033 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [cst]> select * from student3;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | mana | 20 |
| 2 | kiruya | 15 |
| 3 | akira | 24 |
| 4 | genji | 20 |
| 5 | hanzo | 24 |
+----+--------+------+
5 rows in set (0.000 sec)
5 rows in set (0.000 sec)
mysql差异备份恢复
//模拟删除cst库
MariaDB [(none)]> drop database cst;
Query OK, 3 rows affected (0.186 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
//刷新创建新的二进制日志
[root@cst mydata]# cd /var/lib/mysql/
[root@cst mysql]# ls
[root@cst mysql]# ls
aria_log.00000001 ibdata1 mysql_bin.000003
aria_log_control ibtmp1 mysql_bin.index
ib_buffer_pool multi-master.info mysql_upgrade_info
ib_logfile0 mysql performance_schema
ib_logfile1 mysql.sock
//刷新创建新的二进制日志
[root@cst mysql]# mysqladmin -uroot flush-logs
[root@cst mysql]# ls
aria_log.00000001 ibdata1 mysql_bin.000003
aria_log_control ibtmp1 mysql_bin.000004
ib_buffer_pool multi-master.info mysql_bin.index
ib_logfile0 mysql mysql_upgrade_info
ib_logfile1 mysql.sock performance_schema
//恢复完全备份
[root@cst mysql]# mysql -uroot < /opt/mydata/all.sql
[root@cst mysql]# mysql -uroot -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| cst |
| information_schema |
| mysql |
+--------------------+
[root@cst mysql]# mysql -uroot -e 'show tables from cst;'
+---------------+
| Tables_in_cst |
+---------------+
| student |
| student2 |
| student3 |
+---------------+
[root@cst mysql]# mysql -uroot -e 'select * from cst.student3;'
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | mana | 20 |
| 2 | kirue | 15 |
| 3 | akira | 24 |
+----+-------+------+
//恢复差异备份
//选择刷新出来的前一个日志文件,找到最后一次操作的位置
MariaDB [cst]> show binlog events in 'mysql_bin.000003';
*************************** 16. row ***************************
Log_name: mysql_bin.000003
Pos: 946
Event_type: Query
Server_id: 1
End_log_pos: 1029
Info: drop database cst
*************************** 17. row ***************************
Log_name: mysql_bin.000003
Pos: 1029
Event_type: Rotate
Server_id: 1
End_log_pos: 1076
Info: mysql_bin.000004;pos=4
17 rows in set (0.000 sec)
//使用mysqlbinlog恢复差异备份
[root@cst mysql]# mysqlbinlog --stop-position=946 /var/lib/mysql/mysql_bi
[root@cst mysql]# mysql -uroot -e 'select * from cst.student3'
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | mana | 20 |
| 2 | kiruya | 15 |
| 3 | akira | 24 |
| 4 | genji | 20 |
| 5 | hanzo | 24 |
+----+--------+------+