MySQL数据库备份之逻辑备份

一、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;