（十二）MySQL逻辑备份mysqldump
2024-08-27 10:13:56
（1）简介
- 语法
mysqldump -h服务器 -u用户名 -p密码 [-P端口号] [参数] 数据库名 >备份文件.sql - 关于数据库：
-A,--all-databases 所有库，会生成DDL语句（创建数据库的语句和进入数据库的语句，导入的时候不需要指定数据）
test 数据库,没有指定其它参数，导入到数据的时候需要指定导入到哪个数据库，不会生成创建数据库的DDL语句，mysql -uroot -predhat testdb <1.sql
test t1 t2 test数据库的表t1和t2 ，导入到数据的时候需要指定导入到哪个数据库，不会生成创建数据库的DDL语句
-B, --databases bbs test mysql 多个数据库 ，会生成DDL语句（创建数据库的语句和进入数据库的语句，导入的时候不需要指定数据）mysql -uroot -predhat <1.sql
- 其它参数说明
--single-transaction #InnoDB 一致性服务可用性
-x #MyISAM 一致性服务可用性
-E #备份时间调度器代码
-R #备份存储过程和存储函数
-F #备份之前刷新日志
-d #只备份表结构
-t #只备份表数据
--triggers #备份触发器
--master-data=1|2 #用于记录binlog日志位置和文件名追加到文件中，一般使用1
（2）机器损坏备份恢复
前提：需要有完全备份和增量备份（二进制日志文件）
1）备份
- 需要提前开启二进制日志
#vim /etc/my.cnf
log-bin=/data/mydata/mysql-bin/master
server-id=1
#mkdir /data/mydata/mysql-bin
#chown -R mysql.mysql /data/mydata/mysql-bin
#systemctl restart mysqld
- 准备数据
mysql> create database testdb;
mysql> create table testdb.test(id int);
mysql> insert into testdb.test values(1);
- 完整备份
mysqldump -uroot -pMysql@123 -A -R --single-transaction --master-data=1 --flush-logs >/backup/$(date +%F-%H)-mysql-all.sql \\需要提前创建好备份目录
生成目录：ll /backup/2018-04-26-00-mysql-all.sql
- 生成一些数据，用于使用二进制日志恢复
mysql> insert into testdb.test values(2);
mysql> flush logs;
mysql> insert into testdb.test values(2);
mysql> insert into testdb.test values(3);
mysql> flush logs;
mysql> insert into testdb.test values(4);
mysql> flush logs;
mysql> select * from testdb.test;
+------+
| id |
+------+
| 1 |
| 2 |
| 2 |
| 3 |
| 4 |
+------+
- 把二进制文件拷贝了其它目录，我这里就先放在/root目录了
cp -ar /data/mydata/mysql-bin/ /root - 模拟数据库故障
rm -rf /var/lib/mysql/*
systemctl stop mysqld
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld \\无法启动
rm -rf /var/lib/mysql/* \\再删除数据
systemctl start mysqld \\再次可以启动了
grep "password" /var/log/mysqld.log
2018-04-25T10:01:13.953346Z 1 [Note] A temporary password is generated for root@localhost: 9Mf3.k)AOgEd
mysqladmin -uroot -p'9Mf3.k)AOgEd' password 'Mysql@123';
2）恢复：注意恢复时关闭记录二进制日志，避免产生不必要的IO操作
- 关闭记录二进制日志，完整备份恢复
#mysqladmin -uroot -p'Mysql@123' flush-logs
mysql> set sql_log_bin=0;
mysql> system mysql -uroot -predhat </backup/2018-04-26-00-mysql-all.sql
- 获取备份记录的log_file文件和pos位置点
# grep -i change /backup/2018-04-26-00-mysql-all.sql | head -1
CHANGE MASTER TO MASTER_LOG_FILE='master.000003', MASTER_LOG_POS=154;
- 增量备份恢复
mysql> system mysqlbinlog --start-position=154 master.000003 master.000004 master.000005 master.000006 | mysql -uroot -p'Mysql@123'
- 验证数据
mysql> select * from testdb.test;
+------+
| id |
+------+
| 1 |
| 2 |
| 2 |
| 3 |
| 4 |
+------+
5 rows in set (0.01 sec)
（3）模拟删除数据恢复
1）准备数据
mysql -uroot -predhat -e "create database testdb"
mysql -uroot -predhat -e "create table testdb.test(id int)"
mysql -uroot -predhat -e "insert into testdb.test values(1)"
2）完整备份
mysqldump -uroot -predhat -A -R --single-transaction --master-data=1 --flush-logs >/backup/$(date +%F-%H)-mysql-all.sql
生成目录：ll /backup/2018-04-25-10-mysql-all.sql
3）准备数据用于增量备份恢复
mysql> insert into testdb.test values(2);
mysql> flush logs;
mysql> insert into testdb.test values(3);
mysql> flush logs;
mysql> insert into testdb.test values(4);
mysql> flush logs;
mysql> insert into testdb.test values(5);
mysql> select * from testdb.test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
4）删除数据库:注意删除数据库刷新一下二进制日志
mysql> drop database testdb;
mysql> flush logs;
5）完全备份恢复
mysql -uroot -predhat </backup/2018-04-25-10-mysql-all.sql
6）获取备份文件的位置点
# grep -i "change" /backup/2018-04-25-10-mysql-all.sql | head -1
CHANGE MASTER TO MASTER_LOG_FILE='master.000002', MASTER_LOG_POS=154;
7）获取删库的pos位置点
[root@localhost ~]# mysqlbinlog -v /data/mydata/mysql-bin/master.000005 | grep -C 3 "^\bdrop\b"
# mysqlbinlog -v /data/mydata/mysql-bin/master.000005 | grep -C 3 "^\bdrop\b"
# at 472
#180425 10:34:23 server id 1 end_log_pos 570 CRC32 0x1d4bcda1 Query thread_id=24 exec_time=0 error_code=0
SET TIMESTAMP=1524666863/*!*/;
drop database testdb
/*!*/;
# at 570
#180425 10:35:14 server id 1 end_log_pos 635 CRC32 0x836dc275 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
8）增量备份恢复
master.000005 是删除数据库的二进制文件，上面把删除库语句上面at的pos位置找到，恢复这个文件的时候使用stop-position=472
cd /data/mydata/mysql-bin
#mysqlbinlog --start-position=154 master.000002 master.000003 master.000004 | mysql -uroot -p'redhat'
# mysqlbinlog --stop-position=472 master.000005 | mysql -uroot -p'redhat'
9）验证
mysql> select * from testdb.test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
最新文章
- NoSQL和MongoDB
- Nginx配置指定媒体类型文件强制下载
- JavaScript-计算器
- quartz启动时间配置
- elipse 调试 反射 invoke 子类
- python的生成器
- NCPC 2012 Galactic Warlords
- 读书笔记一 Java程序员的基本修养（数组及其内存管理）
- webpack2使用ch2-entry和output简要说明
- Python学习笔记 - 字符串和编码
- #Java学习之路——基础阶段（第一篇）
- Oracle数值函数
- mysql中的多表查询
- activiti 快速入门--组任务（candidate users）分配（6）
- [C++]数组与指针(纯代码-复习用)
- stage.focus后 有黄色边框怎么去掉
- ABC Tech Day（2018.08.11）
- Android 4.4 Kitkat Phone工作流程浅析(八)__Phone状态分析
- Codeforces 285C - Building Permutation
- HDUOJ ---悼念512汶川大地震遇难同胞——来生一起走