mysqldump实现备份还原的实验

mysqldump实现备份还原的实验

mysql的备份还原是十分重要的事情，不仅仅要定期备份，还要考虑到备份的方式，备份的时间，备份时的影响，备份后的数据是否正常，数据库和二进制日志是否分开存放等等。数据的损失带来的是更多利益的损失，所以以下针对此做一个实验，实验步骤如下：

0.指定二进制文件日志路径
1.备份数据库并移动到其他机器或者路径
2.生成新的数据
3.删除整个数据库data
4.停止二进制日志功能
5.重新初始化数据库及指定数据路径
6.设置目录权限
7.还原数据库
8.使用二进制日志还原数据库到最新状态。

mysqldump命令

用法：
mysqldump [option] > bac_upfile
使用重定向到新的文件夹，还原时可以直接使用此文件进行还原，经常配置gzip等压缩工具联合备份带压缩一并执行

常见选项：

-A, --all-databases #备份所有数据库，含create database 
-B, --databases db_name… #指定备份的数据库，包括create database语句 
-E, --events：#备份相关的所有event scheduler 
-R, --routines：#备份所有存储过程和自定义函数 
--triggers：#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器 
--default-character-set=utf8 #指定字符集 
--master-data[=#]： #此选项须启用二进制日志 
	#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
	#2：记录为注释的CHANGE MASTER TO语句 
	#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启-- single-transaction） 
-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文 件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和-- single-transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志 
--compact #去掉注释，适合调试，生产不使用 
-d, --no-data #只备份表结构 
-t, --no-create-info #只备份数据,不备份create table 
-n,--no-create-db #不备份create database，可被-A或-B覆盖 --flush-privileges #备份mysql或相关时需要使用 
-f, --force #忽略SQL错误，继续执行
--hex-blob #使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数 据类型的列时使用，避免乱码 
-q, --quick #不缓存查询，直接输出，加快备份速度

示例：
1.备份所有数据库
mysqldump -A > /data/db_backup.sql

[root@masterdns/data]$mysqldump -A > /data/db_backup.sql
[root@masterdns/data]$ll
-rw-r--r-- 1 root  root  23738440 Nov 26 16:05 db_backup.sql

2.备份指定数据库:mysql和hellodb为我本地的数据库。
$mysqldump -B mysql hellodb > /data/db_mysql_hello.sql

[root@masterdns/data]$mysqldump -B mysql hellodb > /data/db_mysql_hello.sql
[root@masterdns/data]$ll
-rw-r--r-- 1 root  root  23737672 Nov 26 16:09 db_mysql_hello.sql

1.备份数据库

修改路径’log-bin=/data/mybin/mysql-bin’
mkdir /mysql_bak;mysqldump -A --master-data=2 -F|gzip > /mysql_bak/db_bak_date +%F.gz
--master-data表示备份时会增加一行标记备份时的结束position,2表示这行为#注释，1则不注释
-F表示备份时生成新的二进制日志文件，方便还原，如果不加则需要通过grep等方式找到备份结束的位置

[root@masterdns/data]$mkdir /mysql_bak;mysqldump -A --master-data=2 -F|gzip > /mysql_bak/db_bak_`date +%F`.gz
[root@masterdns/data]$ll /mysql_bak/db_bak_2019-11-26.gz 
-rw-r--r-- 1 root root 5598360 Nov 26 16:27 /mysql_bak/db_bak_2019-11-26.gz`

2.新增数据，模拟环境

增加授权

MariaDB [hellodb]> grant all on hellodb.* to 'any'@'192.168.7.%' identified by 'sa123';

增加数据

MariaDB [hellodb]> insert students (name,age,gender) values('kk',22,'M');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> insert students (name,age,gender) values('kk10',23,'M');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> insert students (name,age,gender) values('kk11',24,'M');
Query OK, 1 row affected (0.00 sec)

3.删库~

rm -rf /data/mysql

4.建库

找到mysql_install_db脚本指定basedir，datadir和user=mysql

[root@masterdns/app/mysql/scripts]$./mysql_install_db --user=mysql --datadir=/data/mysql --basedir=/app/mysql
Installing MariaDB/MySQL system tables in '/data/mysql' ...
2019-11-26 16:50:08 140467876345984 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2019-11-26 16:50:09 140467331356416 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
OK
To start mysqld at boot time you have to copy
				·
				·
				·（省略）

5.指定权限

[root@masterdns/data]$chown mysql.mysql mysql

6.关闭二进制日志

防止还原二进制日志时产生不必要的冗余日志。

MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

7.解压并还原数据库

解压

[root@masterdns/data]$gzip -d db_bak.gz 

还原，在mysql命令行中使用source命令

MariaDB [school]> source /data/db_bak

查看一下数据库，OK

MariaDB [school]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
6 rows in set (0.00 sec)

8.查找二进制日志并还原

通过查找备份的二进制日志即可定位到最后一次保存的位置，如图为385，因为使用了-F选项重新生成了新的日志，所以新的日志就是
mysql-bin.000008

[root@masterdns/data]$grep "^CHANGE MASTER TO MASTER_LOG_FILE" /data/db_bak
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=385;

生成二进制日志导入文件

[root@masterdns/data]$mysqlbinlog /data/mybin/mysql-bin.000009 > /data/binlog.sql

还原二进制日志

MariaDB [hellodb]> source /data/binlog.sql

确认是否还原成功

总结

1.–master-data生成的标记在备份的数据库中而非二进制日志中
2.删库后可能有进程未关闭，生成新的数据库后会导致服务无法启动，需ps -ef|grep "mysql"查到相关进程并强制关闭。
3.还原数据库时需关闭二进制日志功能，否则会产生大量冗余日志。