当前位置：首页 > news >正文

新乡做网站的公司网站后台的形成

news 2025/12/20 17:03:04

新乡做网站的公司,网站后台的形成,旭辉网站建设,学校网站做网页飘窗怎么做1 案例1#xff1a;完全备份与恢复 1.1 问题练习物理备份与恢复练习mysqldump备份与恢复 1.2 方案在数据库服务器192.168.88.50 练习数据的备份与恢复 1.3 步骤实现此案例需要按照如下步骤进行。步骤一#xff1a;练习物理备份与恢复冷备份#xff0c;需停止数…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.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. //备份多张表 [rootmysql50 ~]# mysqldump -uroot -pNSD2023...a tarena employees departments /bakdir/tarena_employees_deparments.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. //备份1个库 [rootmysql50 ~]# mysqldump -uroot -pNSD2023...a -B tarena /bakdir/tarena.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. //备份多个库 [rootmysql50 ~]# mysqldump -uroot -pNSD2023...a -B studb db1 /bakdir/studb_db1.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. //备份所有库 [rootmysql50 ~]# mysqldump -uroot -pNSD2023...a -A /bakdir/allbak.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [rootmysql50 ~]# 恢复数据覆盖恢复数据 [rootmysql50 ~]# mysql -uroot -pNSD2023...a mysql drop database tarena; //删除库 Query OK, 6 rows affected (0.57 sec) mysql exit Bye [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 exit Bye [rootmysql50 ~]# //使用备份文件恢复数据 [rootmysql50 ~]# mysql -uroot -pNSD2023...a tarena /bakdir/tarena_salary.sql mysql: [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台数据库服务器主机名IP地址角色说明MySQL50192.168.88.50数据库服务器备份数据练习MySQL51192.168.88.51数据库服务器恢复数据练习增量备份备份上次备份后新产生的数据。 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/bashrc export 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] ...done xtrabackup: 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] ...done xtrabackup: 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] ...done xtrabackup: 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] ...done xtrabackup: 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] ...done xtrabackup: 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] ...done xtrabackup: 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] ...done xtrabackup: 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 24822898 Number of pools: 1 230531 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.index 230531 14:40:05 [00] ...done 230531 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.index 230531 15:00:37 [00] ...done 230531 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.index 230531 15:00:37 [00] ...done 230531 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.index 230531 15:00:37 [00] ...done 230531 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.index 230531 15:00:37 [00] ...done 230531 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.index 230531 15:00:37 [00] ...done 230531 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...a mysql 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_info 230531 17:10:02 [00] ...done xtrabackup: 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_info 230531 17:23:56 [00] ...done xtrabackup: 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_info 230531 17:27:10 [00] ...done xtrabackup: 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_info 230531 17:31:00 [00] ...done xtrabackup: 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_info 230531 17:32:38 [00] ...done xtrabackup: 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_info 230531 17:41:01 [00] ...done xtrabackup: 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_info 230531 17:43:16 [00] ...done xtrabackup: 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 24881373 Number of pools: 1 230531 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.000029 230531 18:05:08 [00] ...done 230531 18:05:08 [00] Copying /dir7/binlog.index to ./binlog.index 230531 18:05:08 [00] ...done 230531 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...a mysql 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日志的练习主机名IP地址角色说明MySQL52192.168.88.52数据库服务器关闭selinux、防火墙 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...a mysql: [Warning] Using a password on the command line interface can be insecure. //连接服务查看数据 [rootmysql50 ~]# mysql -uroot -pNSD2023...a -e select * from gamedb.t1 mysql: [Warning] Using a password on the command line interface can be insecure. ------------- | name | class | ------------- | yaya | nsd | | yaya | nsd | | yaya | nsd | ------------- [rootmysql50 ~]#

查看全文

http://www.pierceye.com/news/321973/