Linux MySQL专栏-backup/restore

mysqldump完备+binlog日志（完备时间节点以后）

准备数据

mysql> create database ceshi;
Query OK, 1 row affected (0.01 sec)

mysql> use ceshi;
Database changed
mysql> create table ceshi(id int,name char(10));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into ceshi values(1,'t'),(2,'y'),(3,'r');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from ceshi;
+------+------+
| id   | name |
+------+------+
|    1 | t    |
|    2 | y    |
|    3 | r    |
+------+------+
3 rows in set (0.00 sec)

完备所有数据库

建议周完备

mkdir -p   /data_backup/  && chown -R mysql:mysql /data_backup/

mysqldump  -uroot -proot  --single-transaction  --flush-logs --master-data --delete-master-logs  -A  > /data_backup/backup_full-$(date +'%F_%T').sql

(WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.)
(WARNING: --delete-master-logs is deprecated and will be removed in a future version. Use --delete-source-logs instead.)

ll  /data_backup/

mysql崩溃，服务起不来，需要重置mysql服务，重置前一定要先把binlog日志cp出来备份，否则无法实现增量恢复，切记！！

ls   -lt     /var/lib/mysql/data/binlog.*

cp  -a     /var/lib/mysql/data/binlog.*  /data_backup/

恢复完备

mysql -uroot -proot < /data_backup/backup_full-2024-05-03_23:01:11.sql

恢复增量(从恢复完备往后直至崩溃前的最后一个binary日志，按时间基线一个一个恢复)

mysqlbinlog  -uroot -proot    /data_backup/binlog.* | mysql -uroot -proot

恢复查看

mysql -uroot -proot  -e  "show databases;"
mysql -uroot -proot  -D   ceshi  -e  "select * from ceshi;"

验证没问题，再次完备!

放心删除上次完备和上次cp出来的binlog日志!