一，备份，恢复
为什么要备份 灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景
参考链接: https://www.toutiao.com/a6939518201961251359/
环境：
(重点1.4和二）
| IP | 角色 | MySQL版本 |
| 192.168.26.101 | 源库 | 8.0.28 |
- 1.1备份指定库的表
[root@mysql ~]# mysql -uroot -p123 < hellodb_innodb.sql 首先将文件导入本地，此命令将文件导入数据库 (在ySQL官网下载hellodb_innodb.sql 文件里面含有7个表) mysql: [Warning] Using a password on the command line interface can be insecure. [root@mysql ~]# mysql -uroot -p123 mysql> show databases; ## 查看数据库 hellodb ## 刚刚导入的 mysql> show tables; ##查看表 +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students |9 | teachers | | toc | +-------------------+ [root@mysql ~]# mysqldump -uroot hellodb -p > /data/hellodb.sql ##将hellodb库中的表重定向到本地/data下的 hellodb.sql Enter password: [root@mysql ~]# ls /data/hellodb.sql /data/hellodb.sql [root@mysql ~]# grep -i create /data/hellodb.sql ##查看文件的内容
删库模拟
[root@mysql ~]# mysql -uroot -p123 mysql> drop database hellodb; ##进入数据库把hellodb库删除 [root@mysql ~]# mysql -uroot -p123 mysql> create database hellodb2; ##手动创建数据库，导入数据测试 [root@mysql ~]# mysql -uroot -p hellodb2 < /data/hellodb.sql ##将本地/data下的 hellodb.sql重定向到hellodb2中 Enter password: [root@mysql ~]# mysql -uroot -p123 mysql> use hellodb2; mysql> show tables;
+-------------------+ | Tables_in_hellodb2 | +-------------------+ | classes | | coc | | courses | | scores | | students |9 | teachers | | toc | +-------------------+
- 1.2备份指定库
使用 mysqldump [root@mysql ~]# mysql -uroot -p < hellodb_innodb.sql ##将hellodb_innodb.sql导入数据库中 Enter password: [root@mysql ~]# mysqldump -uroot -p -B hellodb > /data/hellodb_B.sql ##将hellodb库重定向到/data/hellodb_B.sql Enter password: [root@mysql ~]# grep -i 'create' /data/hellodb_B.sql
删除数据库
[root@mysql ~]# mysql -uroot -p -e 'drop database hellodb' ##删除hellodb库 Enter password: [root@mysql ~]# mysql -uroot -p -e 'show databases' Enter password:
还原
[root@mysql ~]# mysql -uroot -p < /data/hellodb_B.sql ##把/data/hellodb_B.sql导入到数据库中恢复 Enter password: [root@mysql ~]# mysql -uroot -p -e 'show databases' Enter password:
- 1.3备份所有库
[root@mysql ~]# mysqldump -uroot -p -A > /data/all.sql ##将MySQL库所有文件重定向到/data/all.sql Enter password: [root@mysql ~]# grep -i '^create database' /data/all.sql
模拟数据库损坏，还原
[root@mysql opt]# systemctl stop mysqld [root@mysql opt]# rm -rf /usr/local/mysql/* 损坏MySQL数据库文件（ [root@mysql opt]# systemctl start mysqld [root@mysql opt]# mysql -uroot -p123 删除太彻底了，把mysql删了进不去） 只需要把那五个库删了就行了 -bash: mysql: command not found
恢复
[root@mysql opt]# scp /data/all.sql [email protected]:/data/all.sql 将备份的文件远程传输到另一台机器进行恢复 相同环境 [root@102 data]# mysql -uroot -p < /data/all.sql #在第一次还原的时候会失败，因为此刻数据库的密码也回到了刚安装完毕后的初始密码，需要首先进去，设置 下root的密码，然后再次进行还原
查看结果
mysql> show databases; +--------------------+ | Database | +--------------------+ | db1 | | db2 | | hellodb | | hellodb2 | | information_schema | | mysql | | performance_schema | | sys | | wordpress | | xkf | | zabbix | +--------------------+ 11 rows in set (0.00 sec)
- 1.4案例四：之前的案例，是只能还原部分数据，如果备份后，到当前的某个时刻，数据没有备份，那么这 部分数据就会丢失，
mysql> select @@log_bin; +-----------+ | @@log_bin | +-----------+ | 1 | ##以前版本是0，需要打开。现在版本默认打开 +-----------+ 1 row in set (0.00 sec) root@mysql]# vim /etc/my.cnf [mysqld] log-bin=/data/logbin/mysql-bin 把mysql刚生成的二进制文件存放在/data/logbin server-id=1234 这两行添加到【mysql】下面 [root@mysql ]# mkdir /data/logbin/ [root@mysql ]# chown mysql.mysql /data/logbin/ 改变所属组和用户 [root@mysql ]# systemctl restart mysqld [root@mysql ]# mysql -uroot -p Enter password: mysql> show variables like '%format%'; +---------------------------------+---------+ | Variable_name | Value | +---------------------------------+---------+ | binlog_format | ROW | | default_week_format | 0 | | information_schema_stats_expiry | 86400 | | innodb_default_row_format | dynamic | | require_row_format | OFF | +---------------------------------+---------+ ##备注，如果这里的 binlog_format格式不是row，建议到配置文件中修改下，在配置文件中mysqld下面 加入binlog_format=row
#测试，备份
[root@mysql ~]# mysqldump -uroot -p -A --master-data=2 > /data/all.sql -A 全部 --ma单机操作 Enter password: [root@mysql ~]# mysql -uroot -p hellodb mysql> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | xiaoming | 20 | M | | 6 | xiaozhang | 18 | F | +-----+---------------+-----+--------+ 6 rows in set (0.00 sec) mysql> insert teachers values(null,'xkf',22,'M'); Query OK, 1 row affected (0.00 sec) #插入新的内容 mysql> insert teachers values(null,'wss',22,'F'); Query OK, 1 row affected (0.00 sec) mysql> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | xiaoming | 20 | M | | 6 | xiaozhang | 18 | F | | 7 | xkf | 22 | M | | 8 | wss | 22 | F | +-----+---------------+-----+--------+ 8 rows in set (0.00 sec) #查看，已经生成了二进制文件 root@mysql ~]# ll /data/logbin/ total 8 -rw-r-----. 1 mysql mysql 757 Mar 15 19:02 mysql-bin.000001 -rw-r-----. 1 mysql mysql 30 Mar 15 19:00 mysql-bin.index #查看二进制目前的状态 mysql> show master logs; +------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +------------------+-----------+-----------+ | mysql-bin.000001 | 757 | No | +------------------+-----------+-----------+ 1 row in set (0.00 sec) #打开刚才备份的all.sql文件查看 [root@mysql ~]# vim /data/all.sql -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=157; #这个是当时我们做备份的时候，那个瞬间二进制日志的位置，意思就是备份到了157这个位置，往后的都是没 有备份的
#删除库，还原测试
[root@mysql ~]# systemctl stop mysqld.service ##要关闭MySQL [root@mysql ~]# rm -rf /data/3306/data/* ##损坏库文件 （删除数据库文件即可）损坏太多了 [root@mysql ~]# ll /data/ total 1220 drwxr-xr-x. 3 mysql mysql 18 Mar 9 14:28 3306 -rw-r--r--. 1 root root 1249125 Mar 15 19:01 all.sql drwxr-xr-x. 2 mysql mysql 77 Mar 15 19:39 logbin [root@mysql logbin]# mysqlbinlog --start-position=157 /data/logbin/mysql-bin.000001 > /opt/binlog.sql [root@mysql opt]# ll /opt/ 备份完之后没有备份的，通过二进制日志，将未备份的一直到现在最新位置导出来 total 8 -rw-r--r--. 1 root root 4268 Mar 15 19:55 binlog.sql 之前备份和二进制备份都在这两个文件里 [root@mysql opt]# ll /data/ total 1220 drwxr-xr-x. 3 mysql mysql 18 Mar 9 14:28 3306 -rw-r--r--. 1 root root 1249125 Mar 15 19:01 all.sql drwxr-xr-x. 2 mysql mysql 77 Mar 15 19:39 logbin [root@mysql opt]# scp /data/all.sql [email protected]:/root 将备份的文件远程传输到相同环境配置的102进行恢复 [root@mysql opt]# scp /opt/binlog.sql [email protected]:/root 进102MySQL #先暂时停止二进制日志的记录 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@sql_log_bin; +---------------+ | @@sql_log_bin | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | zabbix | +--------------------+ 5 rows in set (0.00 sec) #继续还原 mysql> source /root/all.sql 利用之前备份的文件和二进制实时备份文件进行恢复 mysql> source /root/binlog.sql #打开二进制 mysql> set sql_log_bin=1; #查看数据是否还原成功 mysql> show databases; +--------------------+ | Database | +--------------------+ | db1 | | db2 | | hellodb | | information_schema | 备份的数据库文件 | mysql | | performance_schema | | sys | | wordpress | | zabbix | +--------------------+ 9 rows in set (0.00 sec) mysql> use hellodb; mysql> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | xiaoming | 20 | M | 备份完之后没有备份的，通过二进制日志，将未备份的一直到现在最新位置导出来 | 6 | xiaozhang | 18 | F | 7 | xkf | 22 | M | | 8 | wss | 22 | F | +-----+---------------+-----+--------+ 8 rows in set (0.00 sec) 第一次做完全备份，需要知道完全备份到哪个位置，二进制日志文件（vim /data/all.sql）157），postion all.sql备份到哪里 之后操作是没有备份的，通过二进制日志，将未备份的一直到现在最新位置导出来ejz 二进制文件和数据库文件需要单独存放
二 mysqldump 备份还原实战案例
- 2.1 实战案例：恢复误删除的表
每天2：30做完全备份，早上13：47误删除了表students，13：52才发现故障，现需要将数 据库还原到13：47的状态，且恢复被删除的students表 (101恢复到快照二进制MySQL)
[root@mysql data]# vim /etc/my.cnf log-bin=/data/logbin/mysql-bin [root@mysql ~]# mkdir /data/logbin/ [root@mysql ~]# chown mysql.mysql /data/logbin/ [root@mysql ~]# systemctl restart mysqld [root@mysql logbin]# mysql -uroot -p123 mysql> select @@sql_log_bin; +---------------+ | @@sql_log_bin | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec)
#完全备份
[root@mysql ~]# mkdir /backup
[root@mysql ~]# rz -E
rz waiting to receive.
[root@mysql ~]# ls
\ anaconda-ks.cfg hellodb_innodb.sql
[root@mysql ~]# mysql -uroot -p123
mysql> source /root/hellodb_innodb.sql ##在数据库中把本地hellodb导入进来
[root@mysql ~]# mysqldump -uroot -p -A -F --single-transaction --master-data=2 >/backup/allbackup_`date +%F_%T`.sql
Enter password: 日期 时间
-A, --all-databases #备份所有数据库
single-transaction） -F, --flush-logs #备份前滚动日志，
--master-data[=#]： #此选项须启用二进制日志
[root@mysql ~]# ls /backup/
allbackup_2023-03-17_13\:44\:29.sql
#完全备份后数据更新
mysql> use hellodb;
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| students |
| teachers |
| toc |
+-------------------+
mysql> insert teachers values(null,'xiaoming',20,'M');
Query OK, 1 row affected (0.01 sec)
mysql> insert teachers values(null,'xiaohua',18,'F');
Query OK, 1 row affected (0.00 sec)
#13：47误删除了一个重要的表
mysql> drop table teachers;
Query OK, 0 rows affected (0.01 sec)
#后续其它表继续更新
mysql> insert students (name,age,gender)values('zz',30,'M');
Query OK, 1 row affected (0.00 sec)
mysql> insert students (name,age,gender)values('m',29,'F');
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
| 26 | zz | 30 | M | NULL | NULL |
| 27 | m | 29 | F | NULL | NULL |
#13：52发现表删除，进行还原 #停止数据库访问
#从完全备份中，找到二进制位置 root@mysql ~]# grep '\-\- CHANGE MASTER TO' /backup/allbackup_2023-03-17_13\:44\:29.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=157; #备份从完全备份后的二进制日志 [root@mysql ~]# mysqlbinlog --start-position=157 /data/logbin/mysql-bin.000002 > /backup/inc.sql 将完全备份后的文件导出到/backup/inc.sql里（完全备份只备份到157，157之后的二进制操作记录需要导出，和之前的完全备份文件配合进行全部还原） #找到误删除的语句，从备份中删除此语句 [root@mysql ~]# vim /backup/inc.sql #DROP TABLE `teachers` /* generated by server */ 注释掉，让它失效。 如果文件过大，可以使用sed实现 #sed -i.bak '/^DROP TABLE/d' /backup/inc.sql mysql> set sql_log_bin=0; 先关闭二进制记录 mysql> source /backup/allbackup_2023-03-17_13:44:29.sql 将之前完全备份的文件重定向到数据库进行恢复 teachers表和student表（删除teachers表需要进 /backup/inc.sql 把误删除的语句注释掉不生效，才可以通过all.....文件恢复） mysql> set sql_log_bin=1; 数据已经还原打开二进制日志记录验证 mysql> use hellodb; mysql> select * from students; | 26 | zz | 30 | M | NULL | NULL | | 27 | m | 29 | F | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 27 rows in set (0.00 sec) ysql> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | xiaoming | 20 | M | | 6 | xiaohua | 18 | F | +-----+---------------+-----+--------+ 6 rows in set (0.00 sec) 结果：数据已还原标签：hellodb,data,备份,sql,mysql,MySQL,root,数据库 From： https://www.cnblogs.com/Xkf-IE/p/17253542.html