MYSQL备份恢复实战之二

Mysql的bin log的作业和oracle的redo log差不多，是记录数据库所有的重做信息，不在的在于Mysql的bin log是可读的，oracle则完全是屏幕加密的。

下面通过一个完整备份+增量备份（bin_log)来恢复数据库。

首先选择t1的做为恢复的参考点

mysql> use l5m
Database changed
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   524288 |
+----------+
1 row in set (0.32 sec)

目前bin log的情况

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------+
| Variable_name                   | Value                      |
+---------------------------------+----------------------------+
| log_bin                         | ON                         |
| log_bin_basename                | /u01/mysql/mysql_bin       |
| log_bin_index                   | /u01/mysql/mysql_bin.index |
| log_bin_trust_function_creators | OFF                        |
| log_bin_use_v1_row_events       | OFF                        |
| sql_log_bin                     | ON                         |
+---------------------------------+----------------------------+
6 rows in set (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql_bin.000001 |   5733915 |
| mysql_bin.000002 |       177 |
| mysql_bin.000003 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)

接下来可以做一个完整的备份

[root@qht131 backup]# mysqldump -u root -p --lock-all-tables --master-data=2 --flush-logs --all-databases > full.sql

--master-data表示在备份中注释掉chang master to的语句，因为备份和恢复在同一个数据库，默认值是1，也就是不注释，数据库做主从复制的时候必须用默认值。

--flush-logs表示备份完后立即执行一个switch log.

接着做一些数据的变更，对t1表增加10条数据，模拟增量备份

mysql> insert into t1 select * from t1 limit 1,10;
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   524298 |
+----------+
1 row in set (0.22 sec)

mysql>  show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql_bin.000001 |   5733915 |
| mysql_bin.000002 |       177 |
| mysql_bin.000003 |       201 |
| mysql_bin.000004 |       475 |
+------------------+-----------+
4 rows in set (0.00 sec)

可以看到binlog增长了一个。

下面备份bin_log，直接导出二进制log的sql

[root@qht131 backup]# mysqlbinlog /u01/mysql/mysql_bin.000004 > /u01/backup/incr1.sql
[root@qht131 backup]# ls
full.sql  incr1.sql

备份完成后，现在开始破坏，将数据库相关的文件都删除

[root@qht131 mysql]# pwd
/u01/mysql
[root@qht131 mysql]# ls
auto.cnf         ib_logfile0       mysql_bin.000003    private_key.pem
ca-key.pem       ib_logfile1       mysql_bin.000004    public_key.pem
ca.pem           ibtmp1            mysql_bin.index     server-cert.pem
client-cert.pem  l5m               mysqld.pid          server-key.pem
client-key.pem   mysql             mysql.sock          sys
ib_buffer_pool   mysql_bin.000001  mysql.sock.lock
ibdata1          mysql_bin.000002  performance_schema
[root@qht131 mysql]# rm -rf *
[root@qht131 mysql]# ls
[root@qht131 mysql]# mysqladmin -u root shutdown
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/u01/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/u01/mysql/mysql.sock' exists!

试着启动mysql,肯定是失败的

[root@qht131 mysql]# service mysql start
Starting MySQL..........The server quit without updating PID file (/u01/mysql/mysqld.pid).                                                 [FAILED]

[root@qht131 mysql]# mysqld -user=root &
[1] 25398
[root@qht131 mysql]# 2018-03-21T04:57:05.188216Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-03-21T04:57:05.189682Z 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2018-03-21T04:57:05.189739Z 0 [Note] mysqld (mysqld 5.7.21-log) starting as process 25398 ...
2018-03-21T04:57:05.191966Z 0 [ERROR] Fatal error: Can't change to run as user 'ser=root' ;  Please check that the user exists!

2018-03-21T04:57:05.192002Z 0 [ERROR] Aborting

2018-03-21T04:57:05.192020Z 0 [Note] Binlog end
2018-03-21T04:57:05.192071Z 0 [Note] mysqld: Shutdown complete


[1]+  Exit 1                  mysqld -user=root

虽然启动失败，但是目录下生成了一些文件，这些我们也不需要，也一起删除！

[root@qht131 mysql]# ls
auto.cnf        ibdata1      ib_logfile1       mysql_bin.index
ib_buffer_pool  ib_logfile0  mysql_bin.000001
[root@qht131 mysql]# rm -rf *

最好检查一下系统中是否还有mysql的进程存在，如果有的话要先kill掉。

这时我们做初始化，就和当初创建mysql一样

[root@qht131 mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/u01/mysql
[root@qht131 mysql]# bin/mysql_ssl_rsa_setup --datadir=/u01/mysql
Generating a 2048 bit RSA private key
..............+++
.......................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.....................................+++
............+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.....................+++
........................+++
writing new private key to 'client-key.pem'
-----
[root@qht131 mysql]# bin/mysqld_safe -user=mysql &
[1] 25498

[root@qht131 mysql]# mysqld_safe -user=mysql &
[1] 28121
[root@qht131 mysql]# 2018-03-21T05:53:32.691673Z mysqld_safe Logging to '/u01/log/mysql/mysql_3306.err'.
2018-03-21T05:53:32.717041Z mysqld_safe Starting mysqld daemon with databases from /u01/mysql

[root@qht131 mysql]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

在登入mysql的时候其实遇到一点小麻烦，这里的root密码在初始化的时候系统没有提供，网上参考了一个方法重新修改了密码：Mysql5.7的初始密码忘记后的更改

下面开始恢复数据：

[root@qht131 backup]# mysql -p -u root < full.sql
Enter password:

查看数据的情况，是完整备份时的数据了

mysql> use l5m
Database changed
mysql> show tables;
+---------------+
| Tables_in_l5m |
+---------------+
| t1            |
| t2            |
+---------------+
2 rows in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   524288 |
+----------+
1 row in set (0.27 sec)

继续恢复增量的部分，也就是bin_log导出的文件

[root@qht131 backup]# mysql -p -u root < incr1.sql
Enter password:

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   524298 |
+----------+
1 row in set (0.22 sec)

可以看到增量的部分也恢复成功了。

Bin_log在mysql是非常重要的，和oracle的redo log 和archive log同样可以用来恢复数据库到最新的状态。mysql建议尽量多的保存bin_log，不能随意的删除。