1. 恢复数据库实践

1.1 数据库恢复基本事项

mysql命令以及source命令恢复数据库的原理就是在数据库里重新执行文件的SQL语句的过程。数据恢复和字符集的关联很大，如果字符集不正确则会导致恢复的数据乱码。

1.2 利用source命令恢复数据库

先备份一下：

[root@oldboy ~]# mysqldump -B --master-data=2 --single-transaction oldboy | gzip > /opt/oldboy.sql.gz
[root@oldboy ~]# ll /opt/oldboy.sql.gz 
-rw-r--r--. 1 root root 869 Feb  8 03:30 /opt/oldboy.sql.gz

恢复前解压为SQL文件：

[root@oldboy ~]# gzip -d /opt/oldboy.sql.gz 
gzip: /opt/oldboy.sql already exists; do you wish to overwrite (y or n)? y
---这里是因为之前有过同名的备份，如果不重要就覆盖，重要的话就另外找地方解压
[root@oldboy ~]# ll /opt/
total 212
-rw-r--r--. 1 root root   2204 Feb  8 03:30 oldboy.sql

登录数据库删除oldboy数据库，然后准备用source恢复：

mysql> drop database oldboy;
Query OK, 1 row affected (0.08 sec)
mysql> select * from oldboy.test;    ---查询数据是否还有
ERROR 1146 (42S02): Table 'oldboy.test' doesn't exist
mysql> source /opt/oldboy.sql
---恢复内容省略
mysql> select * from oldboy.test;    ---查询数据是否还有
+----+---------+
| id | name    |
+----+---------+
|  1 | oldboy  |
|  2 | oldgirl |
|  3 | inca    |
|  4 | zuma    |
|  5 | kaka    |
+----+---------+
5 rows in set (0.00 sec)

如果是utf8数据库，人工编辑的SQL文件，则建议使用“UTF8没有签名”格式

1.3 利用mysql命令恢复（标准）

1. 使用mysql命令恢复基本实践

mysql命令是MySQL数据库自带的重要命令之一，除了日常登录数据库之外，还可以通过mysqldump备份的文件或者人工编辑得SQL语句文件对数据库进行数据恢复：

mysql> drop database oldboy;
Query OK, 1 row affected (0.03 sec)
[root@oldboy ~]# mysql < /opt/oldboy.sql 
[root@oldboy ~]# mysql -e "select * from oldboy.test;"    ---使用-e参数，可在命令行执行MySQL命令
+----+---------+
| id | name    |
+----+---------+
|  1 | oldboy  |
|  2 | oldgirl |
|  3 | inca    |
|  4 | zuma    |
|  5 | kaka    |
+----+---------+

2. 使用开发人员提交的SQL语句恢复文件

假定开发人员让运维人员或DBA插入数据到数据库（可能是通过邮件发送的，内容可能是字符串或者SQL文件），此时的SQL文件里可能没有use db这样的字样，此时如果使用mysql命令导入就要指定数据库名了：

[root@oldboy ~]# mysql oldboy < /opt/oldboy.sql

指定库名oldboy的作用就相当于在数据库里执行use oldboy，因此如果使用mysqldump备份时不使用-B参数，那么在恢复时不但可能会提示没有数据库，还可能会提示没有选择数据库，并且在SQL语句文件里尽可能地加入字符集设置，以防止乱码。

[root@oldboy ~]# cat /opt/oldboy5.sql 
set names utf8;
insert into test(name) values('小陶');

mysql不仅可以恢复mysqldump的备份文件，只要是sql语句，都可以通过mysql命令执行到数据库中。

3. 针对压缩的备份数据进行恢复

方法1：使用gzip解压（会删除压缩文件）

gzip -d /opt/oldboy.sql.gz
mysql < /opt/oldboy.sql

方法2：使用gzip解压（不会删除压缩文件）

[root@oldboy ~]# mysqldump -B --master-data=2 --single-transaction oldboy | gzip > /opt/oldboy2.sql.gz
[root@oldboy ~]# gzip -cd /opt/oldboy2.sql.gz > /opt/oldboy2.sql    ---特殊解压方法
[root@oldboy ~]# mysql < /opt/oldboy2.sql

方法3：使用gunzip解压（不会删除压缩文件）

[root@oldboy ~]# gunzip -cd /opt/oldboy2.sql.gz > /opt/oldboy2.sql
[root@oldboy ~]# mysql < /opt/oldboy2.sql

或者：

[root@oldboy ~]# gunzip < /opt/oldboy2.sql.gz | mysql

方法4：使用zcat读取压缩包数据

[root@oldboy ~]# zcat /opt/oldboy2.sql.gz > /opt/oldboy3.sql
[root@oldboy ~]# mysql < /opt/oldboy3.sql

1.4 利用mysql -e参数查看mysql数据

1. 查看数据库oldboy库test表数据

[root@oldboy ~]# mysql -e "use oldboy;select * from test;"
+----+---------+
| id | name    |
+----+---------+
|  1 | oldboy  |
|  2 | oldgirl |
|  3 | inca    |
|  4 | zuma    |
|  5 | kaka    |
+----+---------+

2. 利用mysql -e参数查看SQL线程执行状态

[root@oldboy ~]# mysql -e "show processlist;"
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 19 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

企业故障案例：
故障原因是mysql系统的sleep线程过多，有大量的慢查询语句，导致数据库无法接受正常的请求。

mysql> show full processlist;

结果分别如下面两张图所示：

数据库里正在执行的SQL语句的列属性

数据库里正在执行的SQL语句的信息

mysql> kill 212555221;

在“mysql> kill 212555221;”语句中，212555221是ID，若使用kill（insert,update）命令则可能会丢失数据。
解决办法：
先调整MySQL的如下两个超时参数配置：

mysql> show variables like '%_timeout';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+
12 rows in set (0.00 sec)
set global wait_timeout = 60;
set global interactive_timeout = 60;
---把空闲连接时间缩短

然后在配置文件里修改：

[mysqld]
interactive_timeout = 120    ---此参数设置后wait_timeout自动生效
---服务器关闭交互式连接前等待活动的秒数
wait_timeout = 120
---服务器关闭非交互连接前等待活动的秒数

其他补充方法：
1）在PHP程序中，不使用持久链接，即使用mysql_connect而不是pconnect
2）PHP程序执行完毕，应该显式调用mysql_close
3）Java程序调整连接池（C3P0）或者调整JAVA服务（Tomcat有关连接池参数）
4）逐步分析MySQL的SQL查询及慢查询日志，找到查询过慢的SQL，优化之

3. 利用mysql -e参数查看mysql变量及性能状态

[root@oldboy ~]# mysql -e "show variables;"    ---查看mysql的所有参数配置

可通过以下命令查看my.cnf配置文件的配置有没有在数据库中生效：

[root@oldboy ~]# mysql -e "show variables like 'log_bin';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

查看mysql数据库运行状态的命令：

[root@oldboy ~]# mysql -e "show global status;" | head -5
Variable_name   Value
Aborted_clients 0
Aborted_connects    0
Binlog_cache_disk_use   0
Binlog_cache_use    9

4. 利用mysql -e参数不重启数据库修改数据库参数

[root@oldboy ~]# mysql -e "show variables;" | grep key_buffer
key_buffer_size 8388608
[root@oldboy ~]# mysql -e "set global key_buffer_size = 1024*1024*16;"
---set global为设置全局变量，命令设置是即时生效，但重启就恢复
[root@oldboy ~]# mysql -e "show variables;" | grep key_buffer
key_buffer_size 16777216

5. 利用mysql -e参数引出的重要命令

show processlist;    ---查看数据库里正在执行的SQL语句，可能无法看全完整的SQL语句
show full processlist;    ---查看正在执行的完整SQL语句，完整显示
set global key_buffer_size = 1024*1024*16    ---不重启数据库调整数据库参数，直接生效，重启后失效
show variables;    ---查看数据库的配置参数信息，例如，my.cnf里参数的生效情况
show variables like '%log_bin%';
kill ID;    ---杀掉SQL线程的命令，ID为线程号
show session status;    ---查看当前会话的数据库运行的状态信息，很重要，要分析并要做好监控
show engine innodb status;    ---显示innodb引擎的性能状态

6. mysqladmin命令常用参数

mysqladmin password oldboy123    ---设置密码
mysqladmin -uroot -poldboy123 password oldboy    ---修改密码
mysqladmin -uroot -poldboy123 status    ---查看状态，相当于show status
mysqladmin -uroot -poldboy123 -i 1 status    ---每秒查看一次状态
mysqladmin -uroot -poldboy123 extended-status    ---等同于“show global status;”
mysqladmin -uroot -poldboy123 flush-logs    ---切割日志
mysqladmin -uroot -poldboy123 processlist    ---查看执行的SQL语句信息
mysqladmin -uroot -poldboy123 processlist -i 1    ---每秒查看一次执行的SQL语句
mysqladmin -uroot -poldboy123 shutdown    ---关闭mysql服务
mysqladmin -uroot -poldboy123 variables    ---相当于show variables

7. mysql命令常用参数

-u：指定数据库用户
-p：指定数据库密码
-S：指定数据库socket文件
-h：指定数据库主机，默认localhost
-P：指定数据库端口，默认3306
-e：不登录数据库指定数据库命令
--default-cgaracter-set=name：指定字符集登录数据库或备份

2. mysqlbinlog增量恢复工具

mysqlbinlog工具的作用是解析mysql的二进制binlog的日志内容，把二进制日志解析成可以在MySQL数据库里执行的SQL语句。

2.1 mysqlbinlog工具解析binlog日志实践

默认情况下，binlog日志是二进制格式的，不能使用查看文本工具得命令查看：

[root@oldboy ~]# cd /application/mysql/data/
[root@oldboy data]# ll oldboy-bin.*
-rw-rw----. 1 mysql mysql  168 Feb  7 21:46 oldboy-bin.000001
-rw-rw----. 1 mysql mysql  168 Feb  7 21:47 oldboy-bin.000002
-rw-rw----. 1 mysql mysql 8343 Feb  8 11:19 oldboy-bin.000003    ---找一个内容大的文件做测试
-rw-rw----. 1 mysql mysql   60 Feb  7 21:47 oldboy-bin.index
[root@oldboy data]# file oldboy-bin.000003
oldboy-bin.000003: MySQL replication log
[root@oldboy data]# tail -2 oldboy-bin.000003    ---查看内容，发现很混乱，确定是非纯文本文件
#
std!!!
      oldboyoldboy/*!40000 ALTER TABLE `test` DISABLE KEYS */p󝯴𛚂S# 
                                                                 std!!!
                                                                       oldboyoldboyBEGINyϴ𛚂£ #
      std!!!
            oldboyoldboyINSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka')𞪤4𛚐 ° 
                           󖵵\\x #
                                std!!!
                                      oldboyoldboy/*!40000 ALTER TABLE `test` ENABLE KEYS */P

1. 解析指定库的binlog日志

[root@oldboy data]# mysqlbinlog -d oldboy oldboy-bin.000003 -r bin.sql    --- -d指定库，-r指定生成的文件
[root@oldboy data]# grep -i insert bin.sql     ---过滤内容
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka')
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka')
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka')
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka')
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka')
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka')
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka')

结论：mysqlbinlog可以指定-d实现分库导出binlog，如果使用-d参数，那么在更新数据时，必须要有use库名，才能分出指定库的binlog，例如，写入数据库的语句必须采用如下写法：

use oldboy;    ---必须要有
insert into test values(1,'oldboy');

2. 按照位置截取binlog内容

按照位置截取binlog内容的优点是精确，但是要花费时间寻找位置，例如，要截取oldboy-bin.000009文件从位置365到位置456的日志：

mysqlbinlog oldboy-bin.000009 --start-position=365 --stop-position=456 -r pos.sql

提示：开始位置点必须存在于binlog里，结尾位置点可以不存在。
若指定了开始位置，不指定结束位置，则会截取开始处到结尾的binlog日志。若指定了结束位置，不指定开始位置，则截取最开始到最后面的全部binlog日志。
所谓的位置点，就是mysqlbinlog解析文件里的不同行行首的“# at 数字”标识的数据。

3. 按照时间戳取binlog内容

按照时间戳取binlog内容的缺点是模糊、不准确，截取的内容会丢失部分数据，精确到秒，1秒也可能会有多条语句。
下面语句标识截取得是oldboy-bin.000009文件从'2019-4-20 17:14:15'时间到'2019-4-23 17:15:15'时间的数据：

mysqlbinlog oldboy-bin.000009 --start-datetime='2019-4-20 17:14:15' --stop-datetime='2019-4-23 17:15:15' -r time.sql

2.2 mysqlbinlog命令常用参数

-d ,--database=name：根据指定库拆分binlog（拆分单表binlog可通过SQL关键字过滤）
-r ,--result-file=name：指定解析binlog输出SQL语句的文件
-R，--read-from-remote-server：从mysql服务器读取binlog日志，是下面参数的别名read-from-remote-master=BINLOG-DUMP-NON-GTIDS
-j,--start-position=#：读取binlog的起始位置点，#号是具体的位置点
--stop-position=#：读取binlog的停止位置点，#号是具体的位置点
--start-datetime=name：读取binlog的起始位置点，name是具体的时间，格式为：2019-04-20 11:25:26
--stop-datetime=name：读取binlog的停止位置点，name是具体的时间，格式为：2019-04-23 11:25:26
--base64-output=decode-rows：解析row级别binlog日志的方法，例如：mysqlbinlog --base64-output=decode-rows -v mysqlbin.000016

2019-04-23 MySQL数据库备份与恢复基础实践（2）