数据库备份与恢复
备份与恢复
备份单个表格与恢复
MariaDB [lzx]> show tables;
+---------------+
| Tables_in_lzx |
+---------------+
| student |
+---------------+
1 row in set (0.000 sec)
对student表进行备份
[root@node1 opt]# mysqldump -uroot -p lzx student > student-$(date '+%Y%m%d').sql
Enter password:
[root@node1 opt]# ls
playbook student-20220701.sql
// 删除表
MariaDB [lzx]> drop table student;
Query OK, 0 rows affected (0.008 sec)
MariaDB [lzx]> show tables;
Empty set (0.000 sec)
// 对表进行恢复
[root@node1 opt]# mysql -uroot -p lzx < student-20220701.sql
Enter password:
[root@node1 opt]#
MariaDB [lzx]> show tables;
+---------------+
| Tables_in_lzx |
+---------------+
| student |
+---------------+
1 row in set (0.000 sec)
对库进行备份并恢复
MariaDB [lzx]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| lzx |
+--------------------+
5 rows in set (0.000 sec)
[root@node1 opt]# mysqldump -uroot -p --all-databases > all-$(date '+%Y%m%d').sql
Enter password:
[root@node1 opt]#
// 对库进行恢复
MariaDB [lzx]> drop database lzx ;
Query OK, 1 row affected (0.005 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.000 sec)
[root@node1 opt]# mysql -uroot -p < all-20220701.sql
Enter password:
[root@node1 opt]#
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| lzx |
+--------------------+
5 rows in set (0.001 sec)
差异备份与恢复
完全备份
[root@node1 mysql]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-2022-07-01.sql
Enter password:
[root@node1 opt]#
[root@node1 mysql]# ls
aria_log.00000001 ibtmp1 mysql_upgrade_info
aria_log_control multi-master.info performance_schema
ib_buffer_pool mysql test
ibdata1 mysql-bin.000002 wzw
ib_logfile0 mysql-bin.index
ib_logfile1 mysql.sock
对库进行修改
MariaDB [lzx]> update student set age=18 where id=10;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [lzx]> delete from student where name='jerry';
Query OK, 1 row affected (0.001 sec)
MariaDB [lzx]> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 18 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
9 rows in set (0.000 sec)
模拟误删操作
删除数据库
MariaDB [lzx]> drop database lzx;
Query OK, 1 row affected (0.003 sec)
刷新库的日志文件
[root@node1 ~]# mysqladmin -uroot -p flush-logs
Enter password:
[root@node1 ~]#
[root@node1 mysql]# ls
aria_log.00000001 ibtmp1 mysql.sock
aria_log_control multi-master.info mysql_upgrade_info
ib_buffer_pool mysql performance_schema
ibdata1 mysql-bin.000002 test
ib_logfile0 mysql-bin.000003
ib_logfile1 mysql-bin.index
恢复完全备份
[root@node1 opt]# mysql -uroot -p < all-2022-07-01.sql
Enter password:
[root@node1 opt]#
MariaDB [lzx]> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
10 rows in set (0.000 sec)
恢复差异备份
先查看误删数据库所在的位置
MariaDB [lzx]> 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 | 256 | Server ver: 10.3.17-MariaDB-log, Binlog ver: 4 |
| mysql-bin.000002 | 256 | Gtid_list | 1 | 285 | [] |
| mysql-bin.000002 | 285 | Binlog_checkpoint | 1 | 328 | mysql-bin.000001 |
| mysql-bin.000002 | 328 | Binlog_checkpoint | 1 | 371 | mysql-bin.000002 |
| mysql-bin.000002 | 371 | Gtid | 1 | 413 | BEGIN GTID 0-1-1 |
| mysql-bin.000002 | 413 | Query | 1 | 517 | use `wzw`; delete from student where name='jerry' |
| mysql-bin.000002 | 517 | Xid | 1 | 548 | COMMIT /* xid=437 */ |
| mysql-bin.000002 | 548 | Gtid | 1 | 590 | BEGIN GTID 0-1-2 |
| mysql-bin.000002 | 590 | Query | 1 | 693 | use `wzw`; update student set age=18 where id=10 |
| mysql-bin.000002 | 693 | Xid | 1 | 724 | COMMIT /* xid=439 */ |
| mysql-bin.000002 | 724 | Gtid | 1 | 766 | GTID 0-1-3 |
| mysql-bin.000002 | 766 | Query | 1 | 849 | drop database wzw |
| mysql-bin.000002 | 849 | Rotate | 1 | 896 | mysql-bin.000003;pos=4 |
+------------------+-----+-------------------+-----------+-------------+---------------------------------------------------+
13 rows in set (0.001 sec)
使用mysqlbinlog命令恢复差异备份
这里的766是你开始删除库之前的修改过数据的位置。
mysqlbinlog --stop-position=766 /var/lib/mysql/mysql-bin.000002 | mysql -uroot -p
MariaDB [lzx]> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 18 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
9 rows in set (0.000 sec)
热备、冷备、温备
热备（在线备份）：在数据库运行时直接备份，对数据库操作没有任何影响。
冷备（离线备份）：在数据库停止时进行备份。
温备：在数据库运行时加全局读锁备份，保证了备份数据的一致性，但对性能有影响。
热备流程
备份开始时，记录重做日志的日志序号（LSN）。
复制共享表空间和独立表空间的文件。
复制完后，再次记录重做日志的日志序号（LSN）。
通过前面记录的日志序号来复制在备份时产生的重做日志。
冷备优点
备份简单，只需要复制相关文件即可。
恢复简单而且速度快，不需要执行任何 SQL 语句，也不需要重建索引。
复制
数据库复制的原理是异步实时的将二进制日志（binlog） 重做并应用到从数据库。
浙公网安备 33010602011771号