【第八章】MySQL数据库备份—逻辑备份

一、数据库备份

1.命令简介：

# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
1）关于数据库名：
　　-a, --all-databases 　       所有库
　　school 　　　　　　        数据库名
　　school stu_info t1 school  数据库的表stu_info、t1
　　-b, --databases bbs test mysql 多个数据库
2）关于其它参数说明：
　　--single-transaction         #innodb 一致性 服务可用性
　　-x, --lock-all-tables          #myisam 一致性 服务可用性
　　-e, --events                    #备份事件调度器代码
　　--opt                               #同时启动各种高级选项
　　-r, --routines                 #备份存储过程和存储函数
　　-f, --flush-logs               #备份之前刷新日志
　　--triggers                       #备份触发器
　　--master-data=1|2        #该选项将会记录binlog的日志位置与文件名并追加到文件中

2、操作过程：

1）创建库表：

mysql> create database school;
query ok, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use school;
database changed

mysql> select * from school.t1;
empty set (0.00 sec)

mysql> create table t2 (id int);
query ok, 0 rows affected (0.02 sec)

mysql> insert into t1 values (1),(2);
query ok, 2 rows affected (0.03 sec)
records: 2  duplicates: 0  warnings: 0

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> 

2）逻辑备份：

[root@localhost ~]# mysqldump -uroot -p'yanglt123.' --all-databases \
> --single-transaction \
> --routines \
> --triggers \
> --master-data=1 \
> --flush-logs > /tmp/`date +%f`-mysql-all.sql`

mysqldump: [warning] using a password on the command line interface can be insecure.    #此提示是密码明文显示的愿意
[root@localhost tmp]# 

 注意事项：

--master-data=1    #该选项将会记录binlog的日志位置与文件名并追加到文件中
参数为1和2的时候，都是把position日志截断，如果为2的话第22行为注释状态，为1的时候没有注释，建议选择1：

 [root@localhost tmp]# vim 2018-09-19-mysql-all.sql 可以
 19 -- position to start replication or point-in-time recovery from
 20 --
 21 
 22 change master to master_log_file='mysql-bin.000003', master_log_pos=154;
 23 
 24 --
:set nu                                                        

 二、数据库恢复

1. 停止数据库 　　【systemtl stop mysqld 】
2. 清理环境 　　   【rm -rf /var/lib/mysql/*；】
3. 启动数据库 　   【初始密码 /var/log/mysqld.log】
4. 重置密码 　　   【新密码 】
5. mysql恢复数据  【新密码 】
6. 刷新授权 　　   【备份时密码 】

注：如果不是一个新的数据库环境，我们需要从第一步开始，如果已经是一个新的数据环境，我们可以直接从第5步执行。

先创建一个表，等一下验证恢复情况：
mysql> create table t2 (id int);
query ok, 0 rows affected (0.02 sec)

mysql> insert into t2 values(1),(2)
    -> ;
query ok, 2 rows affected (0.03 sec)
records: 2  duplicates: 0  warnings: 0

mysql> show tables;
+------------------+
| tables_in_school |
+------------------+
| t1               |
| t2               |
+------------------+
2 rows in set (0.00 sec)

mysql> bye
[root@localhost ~]# 

1）停止数据库
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# 
2）清理环境
此处暂时不删除bin-log日志
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -rf /var/lib/mysql/*

3）启动数据库
[root@localhost ~]# systemctl start mysqld

4）重置密码       
[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log|tail -n 1
2018-09-19t09:48:39.418109z 1 [note] a temporary password is generated for root@localhost: abm<-wrj4nsv
[root@localhost ~]# mysqladmin -uroot -p'abm<-wrj4nsv' password "yanglt123."
mysqladmin: [warning] using a password on the command line interface can be insecure.
warning: since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# 

5）恢复数据
[root@localhost ~]# mysql -uroot -p'yanglt123.' < /tmp/2018-09-19-mysql-all.sql 
mysql: [warning] using a password on the command line interface can be insecure.
[root@localhost ~]# 
可以看到它恢复到了备份点，刚才创建的表t2是在备份点之后生成的，可以看到表中没有t2：
mysql> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use school;
reading table information for completion of table and column names
you can turn off this feature to get a quicker startup with -a

database changed
mysql> show tables;
+------------------+
| tables_in_school |
+------------------+
| t1               |
+------------------+
1 row in set (0.00 sec)

mysql> 

6） 刷新授权 
改完密码后与备份点的密码可能不一致，所有我们要执行此步骤，来实现与备份点密码一致。
[root@localhost ~]# mysql -p'yanglt123.' -e 'flush privileges'
mysql: [warning] using a password on the command line interface can be insecure.
[root@localhost ~]# 

7）建议在逻辑备份恢复时，暂停binlog
mysql> set sql_log_bin=0;
query ok, 0 rows affected (0.02 sec)
mysql> source /tmp/2018-09-19-mysql-all.sql;

三、