MySql备份工具MySqldump
mysql备份工具mysqldump
安装mariadb
[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 //3306端口号起来了
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 80 0.0.0.0:3306 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
创建school库和student表
[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)]> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'database' at line 1
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.002 sec)
MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.001 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.008 sec)
MariaDB [school]> insert student(name,age) values('tom',20),('jerry',23),('zhangshan',20);
Query OK, 3 rows affected (0.002 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.007 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.007 sec)
MariaDB [school]> insert student1(name,age) values('hehe',20),('jerry',23),('zhangshan',20),('lisi',24);
Query OK, 4 rows affected (0.002 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [school]> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | zhangshan | 20 |
+----+-----------+------+
3 rows in set (0.001 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.001 sec)
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
| student1 |
| student2 |
+------------------+
3 rows in set (0.000 sec)
MariaDB [school]>
开始备份数据库
//语法：
mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
//常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
备份school库里的student表
[root@localhost ~]# mysqldump -uroot school student > table_student.sql
[root@localhost ~]# ls
anaconda-ks.cfg 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 ~]#
备份所有的数据库（全备）
[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 ~]#
指定备份多个库怎么做
MariaDB [infomation]> show databases;
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
5 rows in set (0.001 sec)
MariaDB [infomation]>
[root@localhost ~]# mysqldump -uroot --databases school infomation > databases.sql //备份school库和infomation库
[root@localhost ~]# ls
all.sql anaconda-ks.cfg databases.sql table_school.sql table_student.sql
[root@localhost ~]#
注意： 备份的时候记得写日期，上面没加日期是为了方便演示
恢复数据库中的表
#方法一,需要创建这个库，然后进到这个库，source备份文件#
模拟误删了school库
MariaDB [(none)]> drop database school;
Query OK, 3 rows affected (0.010 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]>
开始恢复
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
5 rows in set (0.001 sec)
MariaDB [(none)]> use school;
Database changed
MariaDB [school]> source table_school.sql;
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.008 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 3 rows affected (0.002 sec)
Records: 3 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.003 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.006 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 4 rows affected (0.002 sec)
Records: 4 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.004 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.007 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.002 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.001 sec)
MariaDB [school]> show tables; //恢复成功了
+------------------+
| Tables_in_school |
+------------------+
| student |
| student1 |
| student2 |
+------------------+
3 rows in set (0.001 sec)
MariaDB [school]> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | zhangshan | 20 |
+----+-----------+------+
3 rows in set (0.002 sec)
#方法二，需要创建这个库，但是不用进到库里面，直接把这个文件用重定向的方式恢复#
模拟误删了school库
MariaDB [(none)]> drop database school;
Query OK, 3 rows affected (0.012 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]>
开始恢复
MariaDB [(none)]> create database school; //创建school库
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
5 rows in set (0.002 sec)
MariaDB [(none)]>
[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 ~]# mysql -uroot -e 'select * from school.student;' //可以看到表里面的内容
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | zhangshan | 20 |
+----+-----------+------+
[root@localhost ~]#
恢复数据库
误删了school库和infomation库
MariaDB [(none)]> drop database infomation;
Query OK, 1 row affected (0.003 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)
MariaDB [(none)]>
开始恢复
[root@localhost ~]# mysql -uroot < databases.sql
[root@localhost ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
[root@localhost ~]#
mysql差异备份与恢复
mysql差异备份
开启mysql二进制日志功能
[root@localhost ~]# vim /etc/my.cnf
#
[mysqld]
log-bin = mysql_bin //开启二进制日志功能
server-id = 10 //设置服务器标识符
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[root@localhost ~]# systemctl restart mariadb //重启服务
完全备份
[root@localhost ~]# mysqldump -uroot --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20201230.sql
[root@localhost ~]# ls
all-20201230.sql anaconda-ks.cfg
[root@localhost ~]#
添加新内容
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.17-MariaDB-log 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)]> show databases;
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
5 rows in set (0.001 sec)
MariaDB [(none)]> use school;
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
MariaDB [school]> insert student2(name,age)values('tom',20),('jerry',23),('lisi',127);
Query OK, 3 rows affected (0.003 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [school]> select * from student2;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | lisi | 127 |
+----+-------+------+
3 rows in set (0.001 sec)
MariaDB [school]> update student2 set age = 27 where id =3;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [school]> select * from student2;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | lisi | 27 |
+----+-------+------+
3 rows in set (0.000 sec)
MariaDB [school]>
mysql差异备份恢复
模拟误删了school库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
5 rows in set (0.001 sec)
MariaDB [school]> drop database school;
Query OK, 3 rows affected (0.008 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| infomation |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]>
刷新创建新的二进制日志
[root@localhost ~]# cd /var/lib/mysql/
[root@localhost mysql]# ls
aria_log.00000001 ibdata1 ibtmp1 mysql mysql.sock
aria_log_control ib_logfile0 infomation mysql_bin.000002 mysql_upgrade_info
ib_buffer_pool ib_logfile1 multi-master.info mysql_bin.index performance_schema
[root@localhost mysql]# mysqladmin -uroot flush-logs
[root@localhost mysql]# ll
total 122924
-rw-rw----. 1 mysql mysql 16384 Dec 30 23:36 aria_log.00000001
-rw-rw----. 1 mysql mysql 52 Dec 30 23:36 aria_log_control
-rw-rw----. 1 mysql mysql 1812 Dec 30 23:36 ib_buffer_pool
-rw-rw----. 1 mysql mysql 12582912 Dec 31 00:03 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Dec 31 00:03 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Dec 30 22:02 ib_logfile1
-rw-rw----. 1 mysql mysql 12582912 Dec 30 23:36 ibtmp1
drwx------. 2 mysql mysql 52 Dec 30 23:20 infomation
-rw-rw----. 1 mysql mysql 0 Dec 30 22:02 multi-master.info
drwx------. 2 mysql mysql 4096 Dec 30 22:02 mysql
-rw-rw----. 1 mysql mysql 1084 Dec 31 00:07 mysql_bin.000002
-rw-rw----. 1 mysql mysql 385 Dec 31 00:07 mysql_bin.000003 //多出来了一个003
-rw-rw----. 1 mysql mysql 38 Dec 31 00:07 mysql_bin.index
srwxrwxrwx. 1 mysql mysql 0 Dec 30 23:36 mysql.sock
-rw-rw----. 1 mysql mysql 16 Dec 30 22:02 mysql_upgrade_info
drwx------. 2 mysql mysql 20 Dec 30 22:02 performance_schema
[root@localhost mysql]#
注意： 误删之后立马刷新日志
恢复完全备份
[root@localhost ~]# mysql -uroot < all-20201230.sql
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.3.17-MariaDB-log 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)]> use school;
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
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
| student1 |
| student2 |
+------------------+
3 rows in set (0.000 sec)
检查误删数据库的位置在什么地方
MariaDB [school]> 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',127)
*************************** 7. row ***************************
Log_name: mysql_bin.000002
Pos: 503
Event_type: Table_map
Server_id: 10
End_log_pos: 560
Info: table_id: 53 (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: 53 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=480 */
*************************** 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: 762
Info: update student2 set age = 27 where id =3
*************************** 12. row ***************************
Log_name: mysql_bin.000002
Pos: 762
Event_type: Table_map
Server_id: 10
End_log_pos: 819
Info: table_id: 53 (school.student2)
*************************** 13. row ***************************
Log_name: mysql_bin.000002
Pos: 819
Event_type: Update_rows_v1
Server_id: 10
End_log_pos: 875
Info: table_id: 53 flags: STMT_END_F
*************************** 14. row ***************************
Log_name: mysql_bin.000002
Pos: 875
Event_type: Xid
Server_id: 10
End_log_pos: 906
Info: COMMIT /* xid=483 */
*************************** 15. row ***************************
Log_name: mysql_bin.000002
Pos: 906
Event_type: Gtid
Server_id: 10
End_log_pos: 948
Info: GTID 0-10-3
*************************** 16. row ***************************
Log_name: mysql_bin.000002
Pos: 948
Event_type: Query
Server_id: 10
End_log_pos: 1037
Info: drop database school
*************************** 17. row ***************************
Log_name: mysql_bin.000002
Pos: 1037
Event_type: Rotate
Server_id: 10
End_log_pos: 1084
Info: mysql_bin.000003;pos=4
17 rows in set (0.000 sec)
ERROR: No query specified
MariaDB [school]>
使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=948 /var/lib/mysql/mysql_bin.000002 |mysql -uroot //恢复到948，也就是删除school库的前一步，修改lisi年龄age的时候
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.17-MariaDB-log 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)]> use school;
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
MariaDB [school]> select * from student2;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | lisi | 27 |
+----+-------+------+
3 rows in set (0.001 sec)
MariaDB [school]>
