MySQL备份与恢复（同适用于数据库迁移服务器）

数据库迁移：

使用mysqldump进行全表备份，在新数据库中，新建库，再使用mysqldump恢复表数据。

数据库备份：

备份整个库：

mysqldump -uroot -p --max-allowed-packet=512M --master-data=2 --single-transaction db >/path/file.sql

(root:账号  db:数据库  path:备份文件路径  file:备份文件名)

备份数据库下某张表：

mysqldump -uroot -p --max-allowed-packet=512M --master-data=2 --single-transaction db table.. >/path/file.sql

(root:账号  db:数据库 table:表名(表名之间空格符链接)  path:备份文件路径  file:备份文件名)

--max-allowed-packet在mysqldump进行数据库备份时，--max-allowed-packet选项指允许设置这个数据包的最大大小。如果数据库包含大量的数据，或者正在导出很大的表，可能需要增加这个值，以避免出现数据包大小超过默认限制的问题。

--master-data=2用于在备份文件中包含主服务器信息，例如主服务器的二进制日志文件名和位置。当使用该选项时，mysqldump将在备份文件中记录这些信息，以便在以后使用它来恢复主从复制环境。

--single-transaction选项用于在备份期间创建一个单独的事务。当使用该选项时，mysqldump将在备份期间启动一个新的事务，以确保备份的文件与数据库中的一致状态匹配。这可以确保备份的文件在事务提交之前不会被其他事务更改，从而提供一致性的备份。然而--single-transaction只能在InnoDB存储引擎下工作，不适用于其他存储引擎（如MyISAM）。此外，如果数据库中有长事务或锁定的表，使用--single-transaction选项可能会导致备份失败或产生不完整的数据。

备份数据库中忽略某张表：

mysqldump -u root -p --max-allowed-packet=512M --single-transaction --ignore-table=my_test.t_core_log my_test > /dev/shm/lims.sql
my_test：数据库名，t_core_log：忽略表名

数据库恢复：

（根据恢复场景，使用不同的恢复方式，例如：生产数据库数据意外丢失全部，即使用mysqldump备份文件恢复至某时刻后，再使用binlog进行增量备份。建议先整体恢复到测试数据库，后再备份整表，迁移至生产数据库）

mysqldump备份文件恢复：

mysql -uroot -p db </path/file.sql

(root:账号  db:数据库 path:备份文件路径  file:备份文件名)

使用从数据库备份文件导出数据恢复参考：Mysql之mysqldump整库备份单表恢复还原_mysqldump还原表_恒悦sunsite的博客-CSDN博客

恢复单表数据，即使用awk等截取备份文件中的数据，转为sql文件后恢复即可

binlog恢复：

1. 为了防止恢复数据后影响最新业务，先停掉lims服务，执行flush logs，产生一个新的binlog文件，此时旧的binlog文件不会再有写入；
2. 通过mysqlbinlog将binlog转为sql，以方便查询具体位置。

mysqlbinlog --set-charset=utf-8 /var/lib/mysql/mysql-bin.000001>backuptmp.sql

1. 查看生成的backuptmp.sql，最终确定需要恢复的起始位置和结束位置。
2. 通过mysqlbinlog获取所有需进行增量备份的binlog文件，可以使用start-position、stop-position设置具体的截取位置，转为sql文件。

mysqlbinlog --start-position=1 --stop-position=100 /usr/local/mysql/data/binlog.001 > /back.sql

1. 使用上述数据库恢复或使用navicat导入sql文件方式即可。

Xtrabackup备份恢复：

第1步：在测试环境安装相同版本的mysql和XBK

第2步：把要恢复的XBK备份/home/lcbio/lims/mysqldb/20230215_1400拷贝到测试环境/home/bk

第3步:xtrabackup --prepare --target-dir=/home/bk

第4步:清空测试环境mysql的datadir,然后恢复数据到datadir

xtrabackup --copy-back --target-dir=/home/bk

第5步:chown -R mysql.mysql datadir 然后启动mysql,此时数据已恢复到20230215_1400

第6步:检查binlog恢复的起点

根据我们要恢复到的大概时间2点40确定需要恢复的binlog文件为

binlog.000426 -000428

第7步: 使用mysqlbinlog文件依次对binlog文件进行转换

mysqlbinlog binlog.000426 > 426.sql

mysqlbinlog binlog.000427 > 427.sql

mysqlbinlog binlog.000426 > 428.sql

第7步:426.sql去掉位置155前面的内容:

因为发现426.sql中有误删表语句,所以只需要恢复到426.sql中误操作之前

426.sql去掉第一个错误的drop后的内容：

第8步:测试库中导入修改后的426.sql完成增量恢复

mysql > use lims;

mysql > source /home/426.sql

第9步：将恢复后的lims库导出然后导入生产库