江苏龙网

  1. 主页 > 生活百科 > >

Mysql数据备份与恢复( 四 )

数据备份mysql

mysql> show master status;+---------------+----------+--------------+------------------+-------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------+| binlog.000020 |156 ||||+---------------+----------+--------------+------------------+-------------------+

  • 设置当前会话不记录二进制日志，并恢复全备数据
(echo "SET SQL_LOG_BIN=0;";cat /Users/zhangpanqin/Desktop/backup.sql) | mysql -u root -h 10.211.55.8 -pMysql@12345678 -f
  • 查看 backup.sql 记录的是什么时候备份的数据
/*CHANGE MASTER TO MASTER_LOG_FILE='binlog.000019', MASTER_LOG_POS=477;*/
  • 使用 mysqlbinlog 导出 binlog 从位置 477 开始的 sql
-- 笨的方法就是，查看删除的 sql 语句SHOW BINLOG EVENTS IN 'binlog.000019' FROM 477 LIMIT 0,10;mysql> SHOW BINLOG EVENTS IN 'binlog.000019' FROM 477 LIMIT 0,10;+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+| Log_name| Pos| Event_type| Server_id | End_log_pos | Info|+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+| binlog.000019 |477 | Anonymous_Gtid |1 |556 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'|| binlog.000019 |556 | Query|1 |642 | BEGIN|| binlog.000019 |642 | Query|1 |744 | use `ceshi2`; DELETE FROM `account`|| binlog.000019 |744 | Xid|1 |775 | COMMIT /* xid=300922 */|| binlog.000019 |775 | Anonymous_Gtid |1 |854 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'|| binlog.000019 |854 | Query|1 |940 | BEGIN|| binlog.000019 |940 | Query|1 |1126 | use `ceshi2`; INSERT INTO `ceshi2`.`account`(`id`, `username`, `age`) VALUES (11111111, '删除全库之后插入', 11)|| binlog.000019 | 1126 | Xid|1 |1157 | COMMIT /* xid=301033 */|| binlog.000019 | 1157 | Anonymous_Gtid |1 |1236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'|| binlog.000019 | 1236 | Query|1 |1322 | BEGIN|+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+ -- 导出 477-556 之间的 sqlmysqlbinlog binlog.000019 --start-position 477 --stop-position 556 > 477-556.sql-- 导出 从 775 开始的 sqlmysqlbinlog binlog.000019 --start-position 775> 775.sql这里比较好的做法就是直接使用工具直接解析 sql
binlog2sql
https://github.com/danfengcao/binlog2sql
  • 执行剩下的 sql
(echo "SET SQL_LOG_BIN=0;";cat /Users/zhangpanqin/Desktop/477-556.sql) | mysql -u root -h 10.211.55.8 -pMysql@12345678 -f(echo "SET SQL_LOG_BIN=0;";cat /Users/zhangpanqin/Desktop/775.sql) | mysql -u root -h 10.211.55.8 -pMysql@12345678 -f
  • 查看 binlog 日志，没有添加二进制日志到数据库中，不影响从库
mysql> SHOW MASTER STATUS;+---------------+----------+--------------+------------------+-------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------+| binlog.000020 |156 ||||+---------------+----------+--------------+------------------+-------------------+XtraBackup 使用XtraBackup 只能备份InnoDB和XtraDB 两种数据表 。
安装系统环境：Centos 7 x86_64
数据库：Mysql 8.0.21
由以上环境决定了 xtrabackup 需要安装 8.0.14 版本 。
https://www.percona.com/doc/percona-xtrabackup/8.0/installation/yum_repo.htmlwget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.14/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpmyum localinstall percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm# 验证版本xtrabackup --version命令讲解
  • --backup 备份操作，备份数据到 --target-dir 指定的目录 。
  • --prepare 恢复数据执行的阶段 。
  • --use-memory 指定备份时占用的内存，--use-memory=4G 。
  • --copy-back 将准备好的数据文件复制到 mysql datadir 目录 。
  • ``--Apply-log-only` 阻止回滚未完成的事务
全量备份创建备份使用的用户CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'Mysql@12345678';GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrabackup'@'localhost';GRANT SELECT ON performance_schema.log_status TO 'xtrabackup'@'localhost'; FLUSH PRIVILEGES;


推荐阅读


上一篇：狗狗吃猕猴桃有什么好处 兔子耳朵给狗吃对狗好吗

下一篇：盒马鲜生是会员才能购物吗 盒马鲜生会员店一定要是会员吗