Mysql备份工具mysqldump
基本备份与恢复
准备工作
[root@localhost ~]# yum -y install mariadb*
[root@localhost ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 0.0.0.0:3306 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> use school;
Database changed
MariaDB [school]> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.004 sec)
MariaDB [school]> insert student(name,age) values('tom',20),('jerry',23),('zhangshan',20);
Query OK, 3 rows affected (0.001 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [school]> create table student1(id int not null primary key auto_increment,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.005 sec)
MariaDB [school]> create table student2(id int not null primary key auto_increment,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.005 sec)
MariaDB [school]> insert student1(name,age) values('hehe',20),('jerry',23),('zhangshan',20),('lisi',24);
Query OK, 4 rows affected (0.001 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
| student1 |
| student2 |
+------------------+
3 rows in set (0.000 sec)
MariaDB [school]> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | zhangshan | 20 |
+----+-----------+------+
3 rows in set (0.000 sec)
MariaDB [school]> select * from student1;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | hehe | 20 |
| 2 | jerry | 23 |
| 3 | zhangshan | 20 |
| 4 | lisi | 24 |
+----+-----------+------+
4 rows in set (0.000 sec)
MariaDB [school]> select * from student2;
Empty set (0.001 sec)
MariaDB [school]> quit
Bye
备份
//备份school库中的student表
[root@localhost ~]# mysqldump -uroot school student > table_student.sql
//备份school库
[root@localhost ~]# mysqldump -uroot school > table_school.sql
[root@localhost ~]# ls
anaconda-ks.cfg table_school.sql table_student.sql
//备份所有数据库
[root@localhost ~]# mysqldump -uroot --all-databases > all.sql
[root@localhost ~]# du -sh *
472K all.sql
4.0K anaconda-ks.cfg
4.0K table_school.sql
4.0K table_student.sql
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 12
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> create database infomation;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> use infomation;
Database changed
MariaDB [infomation]> create table info(id int not null primary key auto_increment,name varchar(50),salary float);
Query OK, 0 rows affected (0.003 sec)
MariaDB [infomation]> insert info(name,salary) values('tom',8000),('jerry',9000),('zhangshan',7000),('lisi',10000);
Query OK, 4 rows affected (0.002 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [infomation]> show databases;
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
5 rows in set (0.000 sec)
MariaDB [infomation]> quit
Bye
//备份school库中的infomation表
[root@localhost ~]# mysqldump -uroot --databases school infomation > databases.sql
恢复
#恢复方法一：
//模拟误删school数据库
MariaDB [(none)]> drop database school;
Query OK, 3 rows affected (0.007 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> create database school;
MariaDB [(none)]> use school;
Database changed
MariaDB [school]> show tables;
Empty set (0.000 sec)
//恢复school库
MariaDB [school]> source table_school.sql;
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
······
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
| student1 |
| student2 |
+------------------+
3 rows in set (0.000 sec)
#恢复方法二：
//模拟误删school数据库
MariaDB [(none)]> drop database school;
Query OK, 3 rows affected (0.007 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [school]> drop database school;
Query OK, 3 rows affected (0.008 sec)
MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> quit
Bye
[root@localhost ~]# mysql -uroot school < table_school.sql
[root@localhost ~]# mysql -uroot -e 'show tables from school;'
+------------------+
| Tables_in_school |
+------------------+
| student |
| student1 |
| student2 |
+------------------+
//备份所有数据库
[root@localhost ~]# mysqldump -uroot --all-databases > all.sql
//模拟误删school、infomation、performance_schema、mysql数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
5 rows in set (0.000 sec)
MariaDB [(none)]> drop database school;
Query OK, 3 rows affected (0.006 sec)
MariaDB [(none)]> drop database infomation;
Query OK, 1 row affected (0.003 sec)
MariaDB [(none)]> drop database performance_schema;
Query OK, 52 rows affected (0.002 sec)
MariaDB [(none)]> drop database mysql;
Query OK, 31 rows affected (0.009 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> quit
Bye
[root@localhost ~]# mysql -uroot < all.sql
[root@localhost ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
5 rows in set (0.000 sec)
差异备份与恢复
mysql差异备份
开启MySQL服务器的二进制日志功能
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id = 10
log-bin = mysql_bin
[root@localhost ~]# systemctl restart mariadb
对数据库进行完全备份
[root@localhost ~]# rm -rf *.sql
//完全备份
[root@localhost ~]# mysqldump -uroot --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20201229.sql
[root@localhost ~]# ls
all-20201229.sql anaconda-ks.cfg
//增加新内容
[root@localhost ~]# mysql
MariaDB [(none)]> use school;
Database changed
MariaDB [school]> desc student2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)
MariaDB [school]> insert student2(name,age) values('tom',20),('jerry',23),('lisi',27);
Query OK, 3 rows affected (0.002 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [school]> update student2 set age = 127 where id = 3;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [school]> select * from student2;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | lisi | 127 |
+----+-------+------+
3 rows in set (0.000 sec)
mysql差异备份恢复
模拟误删数据
MariaDB [school]> drop database school;
Query OK, 3 rows affected (0.007 sec)
刷新创建新的二进制日志
[root@localhost ~]# ll /var/lib/mysql/
total 122920
-rw-rw----. 1 mysql mysql 16384 Dec 29 23:11 aria_log.00000001
-rw-rw----. 1 mysql mysql 52 Dec 29 23:11 aria_log_control
-rw-rw----. 1 mysql mysql 1905 Dec 29 23:11 ib_buffer_pool
-rw-rw----. 1 mysql mysql 12582912 Dec 29 23:21 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Dec 29 23:21 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Dec 29 22:15 ib_logfile1
-rw-rw----. 1 mysql mysql 12582912 Dec 29 23:11 ibtmp1
-rw-rw----. 1 mysql mysql 0 Dec 29 22:15 multi-master.info
drwx------. 2 mysql mysql 4096 Dec 29 23:02 mysql
-rw-rw----. 1 mysql mysql 1298 Dec 29 23:21 mysql_bin.000002
-rw-rw----. 1 mysql mysql 19 Dec 29 23:13 mysql_bin.index
srwxrwxrwx. 1 mysql mysql 0 Dec 29 23:11 mysql.sock
-rw-rw----. 1 mysql mysql 16 Dec 29 22:15 mysql_upgrade_info
[root@localhost ~]# mysqladmin -uroot flush-logs
[root@localhost ~]# ll /var/lib/mysql/
total 122924
-rw-rw----. 1 mysql mysql 16384 Dec 29 23:11 aria_log.00000001
-rw-rw----. 1 mysql mysql 52 Dec 29 23:11 aria_log_control
-rw-rw----. 1 mysql mysql 1905 Dec 29 23:11 ib_buffer_pool
-rw-rw----. 1 mysql mysql 12582912 Dec 29 23:21 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Dec 29 23:21 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Dec 29 22:15 ib_logfile1
-rw-rw----. 1 mysql mysql 12582912 Dec 29 23:11 ibtmp1
-rw-rw----. 1 mysql mysql 0 Dec 29 22:15 multi-master.info
drwx------. 2 mysql mysql 4096 Dec 29 23:02 mysql
-rw-rw----. 1 mysql mysql 1345 Dec 29 23:25 mysql_bin.000002
-rw-rw----. 1 mysql mysql 385 Dec 29 23:25 mysql_bin.000003
-rw-rw----. 1 mysql mysql 38 Dec 29 23:25 mysql_bin.index
srwxrwxrwx. 1 mysql mysql 0 Dec 29 23:11 mysql.sock
-rw-rw----. 1 mysql mysql 16 Dec 29 22:15 mysql_upgrade_info
恢复完全备份
[root@localhost ~]# mysql -uroot < all-20201229.sql
[root@localhost ~]# mysql
MariaDB [(none)]> use school;
Database changed
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
| student1 |
| student2 |
+------------------+
MariaDB [school]> select * from student2;
Empty set (0.001 sec)
检查误删数据库的位置在什么地方
MariaDB [(none)]> show binlog events in 'mysql_bin.000002'G;
*************************** 1. row ***************************
Log_name: mysql_bin.000002
Pos: 4
Event_type: Format_desc
Server_id: 10
End_log_pos: 256
Info: Server ver: 10.3.17-MariaDB-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql_bin.000002
Pos: 256
Event_type: Gtid_list
Server_id: 10
End_log_pos: 285
Info: []
*************************** 3. row ***************************
Log_name: mysql_bin.000002
Pos: 285
Event_type: Binlog_checkpoint
Server_id: 10
End_log_pos: 328
Info: mysql_bin.000001
*************************** 4. row ***************************
Log_name: mysql_bin.000002
Pos: 328
Event_type: Binlog_checkpoint
Server_id: 10
End_log_pos: 371
Info: mysql_bin.000002
*************************** 5. row ***************************
Log_name: mysql_bin.000002
Pos: 371
Event_type: Gtid
Server_id: 10
End_log_pos: 413
Info: BEGIN GTID 0-10-1
*************************** 6. row ***************************
Log_name: mysql_bin.000002
Pos: 413
Event_type: Annotate_rows
Server_id: 10
End_log_pos: 503
Info: insert student2(name,age) values('tom',20),('jerry',23),('lisi',27）
*************************** 7. row ***************************
Log_name: mysql_bin.000002
Pos: 503
Event_type: Table_map
Server_id: 10
End_log_pos: 560
Info: table_id: 52 (school.student2)
*************************** 8. row ***************************
Log_name: mysql_bin.000002
Pos: 560
Event_type: Write_rows_v1
Server_id: 10
End_log_pos: 626
Info: table_id: 52 flags: STMT_END_F
*************************** 9. row ***************************
Log_name: mysql_bin.000002
Pos: 626
Event_type: Xid
Server_id: 10
End_log_pos: 657
Info: COMMIT /* xid=461 */
*************************** 10. row ***************************
Log_name: mysql_bin.000002
Pos: 657
Event_type: Gtid
Server_id: 10
End_log_pos: 699
Info: BEGIN GTID 0-10-2
*************************** 11. row ***************************
Log_name: mysql_bin.000002
Pos: 699
Event_type: Annotate_rows
Server_id: 10
End_log_pos: 763
Info: update student set age = 127 where id = 3
*************************** 12. row ***************************
Log_name: mysql_bin.000002
Pos: 763
Event_type: Table_map
Server_id: 10
End_log_pos: 819
Info: table_id: 50 (school.student)
*************************** 13. row ***************************
Log_name: mysql_bin.000002
Pos: 819
Event_type: Update_rows_v1
Server_id: 10
End_log_pos: 885
Info: table_id: 50 flags: STMT_END_F
*************************** 14. row ***************************
Log_name: mysql_bin.000002
Pos: 885
Event_type: Xid
Server_id: 10
End_log_pos: 916
Info: COMMIT /* xid=463 */
*************************** 15. row ***************************
Log_name: mysql_bin.000002
Pos: 916
Event_type: Gtid
Server_id: 10
End_log_pos: 958
Info: BEGIN GTID 0-10-3
*************************** 16. row ***************************
Log_name: mysql_bin.000002
Pos: 958
Event_type: Annotate_rows
Server_id: 10
End_log_pos: 1023
Info: update student2 set age = 127 where id = 3
*************************** 17. row ***************************
Log_name: mysql_bin.000002
Pos: 1023
Event_type: Table_map
Server_id: 10
End_log_pos: 1080
Info: table_id: 52 (school.student2)
*************************** 18. row ***************************
Log_name: mysql_bin.000002
Pos: 1080
Event_type: Update_rows_v1
Server_id: 10
End_log_pos: 1136
Info: table_id: 52 flags: STMT_END_F
*************************** 19. row ***************************
Log_name: mysql_bin.000002
Pos: 1136
Event_type: Xid
Server_id: 10
End_log_pos: 1167
Info: COMMIT /* xid=465 */
*************************** 20. row ***************************
Log_name: mysql_bin.000002
Pos: 1167
Event_type: Gtid
Server_id: 10
End_log_pos: 1209
Info: GTID 0-10-4
*************************** 21. row ***************************
Log_name: mysql_bin.000002
Pos: 1209
Event_type: Query
Server_id: 10
End_log_pos: 1298
Info: drop database school
*************************** 22. row ***************************
Log_name: mysql_bin.000002
Pos: 1298
Event_type: Rotate
Server_id: 10
End_log_pos: 1345
Info: mysql_bin.000003;pos=4
22 rows in set (0.000 sec)
使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=1167 /var/lib/mysql/mysql_bin.000002 |mysql -uroot //恢复到1167，也就是20.row
[root@localhost ~]# mysql
MariaDB [(none)]> use school;
Database changed
MariaDB [school]> select * from student2;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | lisi | 27 |
+----+-------+------+
3 rows in set (0.000 sec)