门户网站改造方案,赣州律师网站建设,大型旅游网站源码 织梦 2016,大宗商品期货交易平台Top
NSD DBA DAY06
案例1#xff1a;完全备份与恢复案例2#xff1a;增量备份与恢复案例3#xff1a;差异备份与恢复案例4#xff1a;binlog日志 1 案例1#xff1a;完全备份与恢复
1.1 问题
练习物理备份与恢复练习mysqldump备份与恢复
1.2 方案
在数据库服务器192…Top
NSD DBA DAY06
案例1完全备份与恢复案例2增量备份与恢复案例3差异备份与恢复案例4binlog日志 1 案例1完全备份与恢复
1.1 问题
练习物理备份与恢复练习mysqldump备份与恢复
1.2 方案
在数据库服务器192.168.88.50 练习数据的备份与恢复
1.3 步骤
实现此案例需要按照如下步骤进行。
步骤一练习物理备份与恢复
冷备份需停止数据库服务 适合线下服务器。
备份数据
[rootmysql50 ~]# systemctl stop mysqld[rootmysql50 ~]# mkdir /bakdir 创建备份目录[rootmysql50 ~]# cp -r /var/lib/mysql /bakdir/mysql.bak 拷贝数据源文件[rootmysql50 ~]# cd /var/lib/mysql[rootmysql50 mysql]# tar -zcvf /bakdir/mysql.tar.gz ./* 打包压缩数据源文件[rootmysql50 mysql]# ls /bakdir/ 查看备份文件mysql.bak mysql.tar.gz
删除数据
[rootmysql50 ~]# rm -rf /var/lib/mysql/*
恢复数据
[rootmysql50 ~]# tar -xf /bakdir/mysql.tar.gz -C /var/lib/mysql/[rootmysql50 ~]# systemctl start mysqld[rootmysql50 ~]# mysql -uroot -pNSD2023...amysql show databases;--------------------| Database |--------------------| GAMEDB || db1 || home || information_schema || mysql || performance_schema || studb || sys || tarena || 学生库 |--------------------10 rows in set (0.00 sec)
也可使用cp拷贝的备份文件恢复数据
[rootmysql50 ~]# systemctl stop mysqld[rootmysql50 ~]# rm -rf /var/lib/mysql/*[rootmysql50 ~]# cp -r /bakdir/mysql.bak/* /var/lib/mysql/[rootmysql50 ~]# chown -R mysql:mysql /var/lib/mysql[rootmysql50 ~]# systemctl start mysqld[rootmysql50 ~]# mysql -uroot -pNSD2023...a
步骤二练习mysqldump备份与恢复
热备份备份和恢复数据库服务必须是运行的
//备份1张表[rootmysql50 ~]# mysqldump -uroot -pNSD2023...a tarena salary /bakdir/tarena_salary.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.//备份多张表[rootmysql50 ~]# mysqldump -uroot -pNSD2023...a tarena employees departments /bakdir/tarena_employees_deparments.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.//备份1个库[rootmysql50 ~]# mysqldump -uroot -pNSD2023...a -B tarena /bakdir/tarena.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.//备份多个库[rootmysql50 ~]# mysqldump -uroot -pNSD2023...a -B studb db1 /bakdir/studb_db1.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.//备份所有库[rootmysql50 ~]# mysqldump -uroot -pNSD2023...a -A /bakdir/allbak.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[rootmysql50 ~]#
恢复数据覆盖恢复数据
[rootmysql50 ~]# mysql -uroot -pNSD2023...amysql drop database tarena; //删除库Query OK, 6 rows affected (0.57 sec)mysql exitBye[rootmysql50 ~]# mysql -uroot -pNSD2023...a /bakdir/tarena.sql //恢复数据mysql: [Warning] Using a password on the command line interface can be insecure.[rootmysql50 ~]# mysql -uroot -pNSD2023...a //登陆mysql use tarena; //进库mysql show tables; //看表------------------| Tables_in_tarena |------------------| departments || employees || salary || stu4 || user || wage_grade |------------------6 rows in set (0.00 sec)mysql delete from salary; //删除表记录Query OK, 8055 rows affected (0.11 sec)mysql exitBye[rootmysql50 ~]#//使用备份文件恢复数据[rootmysql50 ~]# mysql -uroot -pNSD2023...a tarena /bakdir/tarena_salary.sqlmysql: [Warning] Using a password on the command line interface can be insecure.[rootmysql50 ~]# mysql -uroot -pNSD2023...a //登陆服务mysql select count(*) from tarena.salary; //查看行数----------| count(*) |----------| 8055 |----------1 row in set (0.00 sec)
分析
Mysqldump 备份和恢复数据时会锁表锁表期间无法对表做写访问mysqldump适合备份数据量比较小的数据或在数据库服务器访问量少的时候备份。 2 案例2增量备份与恢复
2.1 问题
练习数据增量备份练习数据增量恢复
2.2 方案
2.3 准备2台数据库服务器如表-1所示 增量备份备份上次备份后新产生的数据。
PERCONA Xtrabackup是一款强大的在线热备份工具备份过程中不锁库表适合生产环境。支持完全备份与恢复、增量备份与恢复、差异备份与恢复。
在192.168.88.50主机完成备份与恢复的练习 。
2.4 步骤
实现此案例需要按照如下步骤进行。
步骤一练习数据增量备份
安装软件在mysql50 、MySQL51 两台主机都要安装 //把软件拷贝到虚拟机里[openeulerserver1 ~]$ scp /linux-soft/s3/percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz root192.168.88.50:/root///安装依赖[roothost50 ~]# yum -y install perl-DBD-MySQL//解压源码[roothost50 ~ ]# tar -xf percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz//移动并改名[roothost50 ~ ]# mv percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal /usr/local/percona//把命令添加到系统环境变量[roothost50 ~ ]# vim /etc/bashrcexport PATH/usr/local/percona/bin:$PATH 添加在文件末尾:wq[roothost50 ~ ]# source /etc/bashrc//查看帮助信息[roothost50 ~ ]# man xtrabackup (按q 退出)
增量备份在mysql50主机 完成增量备份练习
对数据做增量备份前必须先有一次备份也就是首次备份通常是备份所有数据比如每周周一完全备份周二到周日增量备份。
周一完全备份备份所有数据 [rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/fullbak --datadir/var/lib/mysql…………230530 18:18:48 [00] ...donextrabackup: Transaction log of lsn (24822878) to (24822898) was copied.230530 18:18:50 completed OK![rootmysql50 ~]#//插入新数据 可以插入多行mysql insert into tarena.salary(date,employee_id,basic,bonus)values(20230610,18,25000,8000);
周二增量备份备份周一备份后新产生的数据 [rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/new2 --incremental-basedir/fullbak --datadir/var/lib/mysql…………230530 18:33:52 [00] ...donextrabackup: Transaction log of lsn (24827173) to (24827183) was copied.230530 18:33:53 completed OK![rootmysql50 ~]#//插入新数据 可以插入多行mysql insert into tarena.salary(date,employee_id,basic,bonus)values(20230710,18,25000,8000);
周三增量备份备份周二备份后新产生的数据 [rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/new3 --incremental-basedir/new2 --datadir/var/lib/mysql…………230530 18:46:17 [00] ...donextrabackup: Transaction log of lsn (24832526) to (24832536) was copied.230530 18:46:18 completed OK![rootmysql50 ~]#//插入新数据 可以插入多行mysql insert into tarena.salary(date,employee_id,basic,bonus)values(20230710,18,25000,8000);
周四增量备份备份周三备份后新产生的数据 [rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/new4 --incremental-basedir/new3 --datadir/var/lib/mysql…………230530 18:53:41 [00] ...donextrabackup: Transaction log of lsn (24837561) to (24837571) was copied.230530 18:53:42 completed OK![rootmysql50 ~]#//插入新数据 可以插入多行mysql insert into tarena.salary(date,employee_id,basic,bonus)values(20230710,18,25000,8000);
周五增量备份备份周四备份后新产生的数据 [rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/new5 --incremental-basedir/new4 --datadir/var/lib/mysql…………230530 18:58:50 [00] ...donextrabackup: Transaction log of lsn (24841645) to (24841655) was copied.230530 18:58:51 completed OK![rootmysql50 ~]#//插入新数据 可以插入多行mysql insert into tarena.salary(date,employee_id,basic,bonus)values(20230710,18,25000,8000);
周六增量备份备份周五备份后新产生的数据 [rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/new6 --incremental-basedir/new5 --datadir/var/lib/mysql…………230530 19:00:55 [00] ...donextrabackup: Transaction log of lsn (24848404) to (24848414) was copied.230530 19:00:56 completed OK![rootmysql50 ~]#//插入新数据 可以插入多行mysql insert into tarena.salary(date,employee_id,basic,bonus)values(20230710,18,25000,8000);
周日增量备份备份周六备份后新产生的数据 [rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/new7 --incremental-basedir/new6 --datadir/var/lib/mysql…………230530 19:00:55 [00] ...donextrabackup: Transaction log of lsn (24848404) to (24848414) was copied.230530 19:00:56 completed OK![rootmysql50 ~]#
步骤二练习数据增量恢复
增量恢复数据步骤
准备恢复数据合并数据清空数据库目录拷贝数据修改数据库目录所有者/组用户为mysql重启数据库服务
具体操作如下
MySQL51 拷贝 MySQL50 的备份文件到 本机的根目录下 [rootmysql50 ~]# scp –r root192.168.88.50:/fullbak /[rootmysql50 ~]# scp –r root192.168.88.50:/new2 /[rootmysql50 ~]# scp –r root192.168.88.50:/new3 /[rootmysql50 ~]# scp –r root192.168.88.50:/new4 /[rootmysql50 ~]# scp –r root192.168.88.50:/new5 /[rootmysql50 ~]# scp –r root192.168.88.50:/new6 /[rootmysql50 ~]# scp –r root192.168.88.50:/new7 /
在MySQL51主机使用备份文件恢复数据
1)、准备恢复数据 [rootmysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir/fullbak…………Log background threads are being closed...Shutdown completed; log sequence number 24822898Number of pools: 1230531 14:32:14 completed OK![rootmysql51 ~]#
2、合并数据 //将周二的增量数据拷贝到周一备份目录里合并后周一的目录里存放的是周一 周二 的数据[rootmysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir/fullbak --incremental-dir/new2…………230531 14:40:05 [00] Copying /new2/binlog.index to ./binlog.index230531 14:40:05 [00] ...done230531 14:40:05 completed OK![rootmysql51 ~]#//将周三的增量数据拷贝到周一备份目录里合并后周一的目录里存放的是周一 周二 周三 的数据[rootmysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir/fullbak --incremental-dir/new3…………230531 15:00:37 [00] Copying /new3/binlog.index to ./binlog.index230531 15:00:37 [00] ...done230531 15:00:37 completed OK![rootmysql51 ~]#//将周四的增量数据拷贝到周一备份目录里合并后周一的目录里存放的是周一 周二 周三 周四的数据[rootmysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir/fullbak --incremental-dir/new4…………230531 15:00:37 [00] Copying /new4/binlog.index to ./binlog.index230531 15:00:37 [00] ...done230531 15:00:37 completed OK![rootmysql51 ~]#//将周五的增量数据拷贝到周一备份目录里合并后周一的目录里存放的是周一 周二 周三 周四周五的数据[rootmysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir/fullbak --incremental-dir/new5…………230531 15:00:37 [00] Copying /new5/binlog.index to ./binlog.index230531 15:00:37 [00] ...done230531 15:00:37 completed OK![rootmysql51 ~]#//将周六的增量数据拷贝到周一备份目录里合并后周一的目录里存放的是周一 周二 周三 周四周五周六的数据[rootmysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir/fullbak --incremental-dir/new6…………230531 15:00:37 [00] Copying /new6/binlog.index to ./binlog.index230531 15:00:37 [00] ...done230531 15:00:37 completed OK![rootmysql51 ~]#//将周日的增量数据拷贝到周一备份目录里合并后周一的目录里存放的是周一 周二 周三 周四周五周六周日的数据[rootmysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir/fullbak --incremental-dir/new7…………230531 15:00:37 [00] Copying /new7/binlog.index to ./binlog.index230531 15:00:37 [00] ...done230531 15:00:37 completed OK![rootmysql51 ~]#[rootmysql51 ~]# rm -rf /var/lib/mysql/*[rootmysql51 ~]# xtrabackup --copy-back --target-dir/fullbak[rootmysql51 ~]# chown -R mysql:mysql /var/lib/mysql
6重启数据库服务 [rootmysql51 ~]# systemctl restart mysqld
7连接服务查看数据 [rootmysql51 ~]# mysql -uroot -pNSD2023...amysql select count(*) from tarena.salary where date20230710;----------| count(*) |----------| 75 |----------1 row in set (0.01 sec)mysql select count(*) from tarena.salary where not date20230710;----------| count(*) |----------| 8067 |----------1 row in set (0.00 sec) 3 案例3差异备份与恢复
3.1 问题
练习差异备份练习差异恢复
3.2 方案
差异备份备份完全备份后新产生的数据。
PERCONA Xtrabackup是一款强大的在线热备份工具备份过程中不锁库表适合生产环境。支持差异备份与恢复。
在192.168.88.50主机完成差异备份
3.3 步骤
实现此案例需要按照如下步骤进行。
步骤一练习差异备份
差异备份
对数据做差异备份前必须先有一次备份也就是首次备份通常是备份所有数据比如每周周一完全备份周二到周日差异备份。 //周一完全备份[rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/allbak --datadir/var/lib/mysql…………230531 17:10:02 [00] Writing /allbak/xtrabackup_info230531 17:10:02 [00] ...donextrabackup: Transaction log of lsn (24881353) to (24881373) was copied.230531 17:10:03 completed OK![rootmysql50 ~]#//插入新数据 可以插入多行mysql insert into tarena.salary(date,employee_id,basic,bonus)values(20230810,18,25000,8000);
周二差异备份备份周一备份后新产生的数据 [rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/dir2 --incremental-basedir/allbak --datadir/var/lib/mysql…………230531 17:23:56 [00] Writing /dir2/xtrabackup_info230531 17:23:56 [00] ...donextrabackup: Transaction log of lsn (24886741) to (24886751) was copied.230531 17:23:58 completed OK![rootmysql50 ~]#//插入新数据 可以插入多行mysql insert into tarena.salary(date,employee_id,basic,bonus)values(20230810,18,25000,8000);
周三差异备份备份周一备份后新产生的数据 [rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/dir3 --incremental-basedir/allbak --datadir/var/lib/mysql…………230531 17:27:10 [00] Writing /dir3/xtrabackup_info230531 17:27:10 [00] ...donextrabackup: Transaction log of lsn (24892043) to (24892063) was copied.230531 17:27:11 completed OK![rootmysql50 ~]#//插入新数据 可以插入多行mysql insert into tarena.salary(date,employee_id,basic,bonus)values(20230810,18,25000,8000);
周四差异备份备份周一备份后新产生的数据 [rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/dir4 --incremental-basedir/allbak --datadir/var/lib/mysql…………230531 17:31:00 [00] Writing /dir4/xtrabackup_info230531 17:31:00 [00] ...donextrabackup: Transaction log of lsn (24900560) to (24900580) was copied.230531 17:31:01 completed OK![rootmysql50 ~]#//插入新数据 可以插入多行mysql insert into tarena.salary(date,employee_id,basic,bonus)values(20230810,18,25000,8000);
周五差异备份备份周一备份后新产生的数据 [rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/dir5 --incremental-basedir/allbak --datadir/var/lib/mysql…………230531 17:32:38 [00] Writing /dir5/xtrabackup_info230531 17:32:38 [00] ...donextrabackup: Transaction log of lsn (24906902) to (24906912) was copied.230531 17:32:39 completed OK![rootmysql50 ~]#//插入新数据 可以插入多行mysql insert into tarena.salary(date,employee_id,basic,bonus)values(20230810,18,25000,8000);
周六差异备份备份周一备份后新产生的数据 [rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/dir6 --incremental-basedir/allbak --datadir/var/lib/mysql…………230531 17:41:01 [00] Writing /dir6/xtrabackup_info230531 17:41:01 [00] ...donextrabackup: Transaction log of lsn (24914729) to (24914739) was copied.230531 17:41:02 completed OK![rootmysql50 ~]#//插入新数据 可以插入多行mysql insert into tarena.salary(date,employee_id,basic,bonus)values(20230810,18,25000,8000);
周日差异备份备份周一备份后新产生的数据 [rootmysql50 ~]# xtrabackup --host127.0.0.1 --userroot --passwordNSD2023...a --backup --target-dir/dir7 --incremental-basedir/allbak --datadir/var/lib/mysql…………230531 17:43:16 [00] Writing /dir7/xtrabackup_info230531 17:43:16 [00] ...donextrabackup: Transaction log of lsn (24920772) to (24920782) was copied.230531 17:43:17 completed OK![rootmysql50 ~]#
步骤二练习差异恢复
差异恢复数据步骤
准备恢复数据合并数据清空数据库目录拷贝数据修改数据库目录所有者/组用户为mysql重启数据库服务
具体操作如下
MySQL51 拷贝 MySQL50 的备份文件到 本机的根目录下 [rootmysql51 ~]# scp –r root192.168.88.50:/allbak /[rootmysql51 ~]# scp –r root192.168.88.50:/dir7 /
在MySQL51主机使用备份文件恢复数据
1)、准备恢复数据 [rootmysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir/allbak…………Log background threads are being closed...Shutdown completed; log sequence number 24881373Number of pools: 1230531 17:59:06 completed OK![rootmysql51 ~]#
2、合并数据 //将周日的差异备份与周一的完全备份合并因为周日的差异备份包扩周二周日的所有数据[rootmysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir/allbak --incremental-dir/dir7…………230531 18:05:08 [00] Copying /dir7/binlog.000029 to ./binlog.000029230531 18:05:08 [00] ...done230531 18:05:08 [00] Copying /dir7/binlog.index to ./binlog.index230531 18:05:08 [00] ...done230531 18:05:08 completed OK![rootmysql51 ~]#[rootmysql51 ~]# rm -rf /var/lib/mysql/*[rootmysql51 ~]# xtrabackup --copy-back --target-dir/allbak[rootmysql51 ~]# chown -R mysql:mysql /var/lib/mysql
6重启数据库服务 [rootmysql51 ~]# systemctl restart mysqld
7连接服务查看数据 [rootmysql51 ~]# mysql -uroot -pNSD2023...amysql select count(*) from tarena.salary where date20230810;----------| count(*) |----------| 75 |----------1 row in set (0.01 sec)mysql select count(*) from tarena.salary where not date20230810;----------| count(*) |----------| 8067 |----------1 row in set (0.00 sec) 4 案例4binlog日志
4.1 问题
查看正在使用的binlog日志文件自定义日志目录和日志名手动创建新的日志文件练习日志相关命令的使用使用日志恢复数据
4.2 方案
binlog日志介绍
也称做 二进制日志MySQL服务日志文件的一种保存除查询之外的所有SQL命令可用于数据的备份和恢复配置mysql主从同步的必要条件准备新的数据库服务器如表-1做binlog日志的练习 4.3 步骤
实现此案例需要按照如下步骤进行。
步骤一查看正在使用的binlog日志文件
在新创建的数据库服务器做如下操作: [rootmysql52 ~]# yum -y install mysql-server mysql 安装软件[rootmysql52 ~]# systemctl start mysqld 启动服务[rootmysql52 ~]# mysql 连接服务mysql show master status; 查看日志文件-----------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-----------------------------------------------------------------------------| binlog.000001 | 156 | | | |-----------------------------------------------------------------------------1 row in set (0.00 sec)执行查询命令mysql select count(*) from mysql.user;----------| count(*) |----------| 4 |----------1 row in set (0.00 sec)mysql show master status; 执行查询命令 日志偏移量不变-----------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-----------------------------------------------------------------------------| binlog.000001 | 156 | | | |-----------------------------------------------------------------------------1 row in set (0.00 sec)执行建库、建表命令mysql create database db1;Query OK, 1 row affected (0.07 sec)mysql create table db1.user(name char(10));Query OK, 0 rows affected (0.52 sec)mysql show master status; 执行写命令 日志偏移量改变-----------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-----------------------------------------------------------------------------| binlog.000001 | 535 | | | |-----------------------------------------------------------------------------1 row in set (0.00 sec)mysql insert into db1.user values(jim); 插入记录Query OK, 1 row affected (0.10 sec)mysql show master status; 执行写命令 日志偏移量改变-----------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-----------------------------------------------------------------------------| binlog.000001 | 809 | | | |-----------------------------------------------------------------------------1 row in set (0.00 sec)mysql
步骤二自定义日志目录和日志名
日志文件默认保存在/var/lib/mysql目录下默认日志名binlog [rootmysql52 ~]# vim /etc/my.cnf.d/mysql-server.cnf[mysqld]log-bin/mylog/mysql52 //定义日志目录和日志文件名(手动添加):wq[rootmysql52 ~]# mkdir /mylog 创建目录[rootmysql52 ~]# chown mysql /mylog 修改目录所有者mysql用户[rootmysql52 ~]# setenforce 0 关闭selinux[rootmysql52 ~]# systemctl restart mysqld 重启服务[rootmysql52 ~]# ls /mylog/ 查看日志目录mysql52.000001 mysql52.index[rootmysql52 ~]# mysql 登陆服务Mysql show master status 查看日志信息-----------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-----------------------------------------------------------------------------| mysql52.000001 | 156 | | | |-----------------------------------------------------------------------------
步骤三手动创建新的日志文件
默认日志文件容量大于1G时会自动创建新的日志文件在日志文件没写满时执行的所有写命令都会保存到当前使用的日志文件里。 //刷新前查看mysql show master status;-----------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-----------------------------------------------------------------------------| mysql52.000001 | 156 | | | |-----------------------------------------------------------------------------1 row in set (0.00 sec)mysql flush logs; //刷新日志Query OK, 0 rows affected (0.22 sec)mysql flush logs; //刷新日志Query OK, 0 rows affected (0.16 sec)mysql show master status; //刷新一次创建一个新日志-----------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-----------------------------------------------------------------------------| mysql52.000003 | 156 | | | |-----------------------------------------------------------------------------1 row in set (0.00 sec)//只要服务重启就会创建新日志[rootmysql52 ~]# systemctl restart mysqld[rootmysql52 ~]# mysql 连接服务Mysql show master status; 查看日志-----------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-----------------------------------------------------------------------------| mysql52.000004 | 156 | | | |-----------------------------------------------------------------------------[rootmysql52 ~]#//完全备份后创建新的日志文件创建的日志个数和备份库的个数一致[rootmysql52 ~]# mysqldump --flush-logs mysql user user.sql[rootmysql52 ~]# mysql -e show master status-----------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-----------------------------------------------------------------------------| mysql52.000005 | 156 | | | |-----------------------------------------------------------------------------[rootmysql52 ~]# mysqldump --flush-logs -B mysql db1 db_2.sql[rootmysql52 ~]# mysql -e show master status-----------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-----------------------------------------------------------------------------| mysql52.000007 | 156 | | | |-----------------------------------------------------------------------------[rootmysql52 ~]#
步骤四练习日志相关命令的使用
MySQL服务提供了管理日志的专属命令具体练习如下: //查看已有的日志文件mysql show binary logs;日志文件名 日志大小(字节) 加密no/yes--------------------------------------| Log_name | File_size | Encrypted |--------------------------------------| mysql52.000001 | 201 | No || mysql52.000002 | 201 | No || mysql52.000003 | 179 | No || mysql52.000004 | 201 | No || mysql52.000005 | 201 | No || mysql52.000006 | 201 | No || mysql52.000007 | 156 | No |--------------------------------------7 rows in set (0.00 sec)//查看正在使用的日志mysql show master status;-----------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-----------------------------------------------------------------------------| mysql52.000007 | 156 | | | |-----------------------------------------------------------------------------1 row in set (0.00 sec)//插入记录mysql insert into db1.user values(yaya);Query OK, 1 row affected (0.04 sec)//查看日志文件内容mysql show binlog events in mysql52.000007;Log_name 日志文件名。Pos 命令在日志文件中的起始位置。Event_type 事件类型例如 Query、Table_map、Write_rows 等。Server_id 服务器 ID。End_log_pos命令在文件中的结束位置以字节为单位。Info执行命令信息。---------------------------------------------------------------------------------------------------| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |---------------------------------------------------------------------------------------------------| mysql52.000007 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.26, Binlog ver: 4 || mysql52.000007 | 125 | Previous_gtids | 1 | 156 | || mysql52.000007 | 156 | Anonymous_Gtid | 1 | 235 | SET SESSION.GTID_NEXT ANONYMOUS || mysql52.000007 | 235 | Query | 1 | 306 | BEGIN || mysql52.000007 | 306 | Table_map | 1 | 359 | table_id: 108 (db1.user) || mysql52.000007 | 359 | Write_rows | 1 | 400 | table_id: 108 flags: STMT_END_F || mysql52.000007 | 400 | Xid | 1 | 431 | COMMIT /* xid649 */ |---------------------------------------------------------------------------------------------------7 rows in set (0.00 sec)//删除日志文件名之前的所有日志文件mysql purge master logs to mysql52.000004;Query OK, 0 rows affected (0.10 sec)//查看已有的日志文件mysql show binary logs;--------------------------------------| Log_name | File_size | Encrypted |--------------------------------------| mysql52.000004 | 201 | No || mysql52.000005 | 201 | No || mysql52.000006 | 201 | No || mysql52.000007 | 431 | No |--------------------------------------4 rows in set (0.00 sec)//删除所有日志文件并重新创建日志文件mysql reset master;Query OK, 0 rows affected (0.14 sec)//查看已有的日志文件 仅有第1个文件了mysql show binary logs;--------------------------------------| Log_name | File_size | Encrypted |--------------------------------------| mysql52.000001 | 156 | No |--------------------------------------1 row in set (0.00 sec)
步骤五使用日志恢复数据
把查看到的文件内容管道给连接mysql服务的命令执行
恢复数据命令
mysqlbinlog /目录/文件名 | mysql –uroot -p密码
1在mysql52主机执行如下操 //重置日志mysql reset master;Query OK, 0 rows affected (0.09 sec)//查看日志mysql show master status;-----------------------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-----------------------------------------------------------------------------| mysql52.000001 | 156 | | | |-----------------------------------------------------------------------------1 row in set (0.00 sec)//建库、mysql create database gamedb;Query OK, 1 row affected (0.07 sec)//建表mysql create table gamedb.t1(name char(10),class char(3));Query OK, 0 rows affected (0.55 sec)//插入记录mysql insert into gamedb.t1 values (yaya,nsd);Query OK, 1 row affected (0.08 sec)mysql insert into gamedb.t1 values (yaya,nsd);Query OK, 1 row affected (0.04 sec)mysql insert into gamedb.t1 values (yaya,nsd);Query OK, 1 row affected (0.08 sec)//查看表记录mysql select * from gamedb.t1;-------------| name | class |-------------| yaya | nsd || yaya | nsd || yaya | nsd |-------------3 rows in set (0.00 sec)mysql exit//把日志文件拷贝给恢复数据的服务器,比如 mysql50[rootmysql52 ~]# scp /mylog/mysql52.000001 root192.168.88.50:/root/The authenticity of host 192.168.88.50 (192.168.88.50) cant be established.ECDSA key fingerprint is SHA256:t7J3okFd0o9zTmFCIetvDl6mxGCmc43VoD6C65zico.Are you sure you want to continue connecting (yes/no/[fingerprint])? Yes 同意Warning: Permanently added 192.168.88.50 (ECDSA) to the list of known hosts.root192.168.88.50s password: mysql50的密码mysql52.000001 100% 1410 1.6MB/s 00:00[rootmysql52 ~]#
2在MySQL50 使用日志恢复数据 //查看日志[rootmysql50 ~]# ls /root/mysql52.000001/root/mysql52.000001//执行日志恢复数据[rootmysql50 ~]# mysqlbinlog /root/mysql52.000001 | mysql -uroot -pNSD2023...amysql: [Warning] Using a password on the command line interface can be insecure.//连接服务查看数据[rootmysql50 ~]# mysql -uroot -pNSD2023...a -e select * from gamedb.t1mysql: [Warning] Using a password on the command line interface can be insecure.-------------| name | class |-------------| yaya | nsd || yaya | nsd || yaya | nsd |-------------[rootmysql50 ~]#
