Mysql运维管理-Mysql增量备份及分库分表备份数据恢复实战12

摘要：

使用mysqldump命令进行备份的过程实际上是以逻辑sql语句的形式直接从mysql数据库输出数据或生成备份文件的过程，，，；解锁表；1.2备份单个数据库练习多个参数使用MySQL数据库附带一个非常有用的备份命令mysqldump。它的基本用法如下：语法：mysqldump–u用户名–p密码数据库名>备份文件名Fan Lie 1：备份名为cuidong a的数据库。备份前查看数据[root@localhost~]#mysql-uroot-p123456--default-character-set=latin1-e“select*fromcuizhong.student；”+---+----------+|id|name|+---+/----------+|1| zhangsan ||2|lisi||3|wanger||4|xiaozhang||5|xiaowang||6|？？|| |7|小红||8|不知道||9|李斯|+--------+------------+b。执行备份命令[root@localhost~]#mysqldump–uroot-p123456cuizhong>/opt/mysql_Bak.sqlc。检查备份结果[root@localhost~]#egrp-v“#|*|--|^$”/opt/mysql_bak.sqlDROPTABLEIFEXISTS`student`；删除表CREATEABLE`student`ENGINE=InnoDB AUTO_ INCREMENT=10DEFAULTCHARSET=latin1；LOCKTABLES`学生`WRITE；锁定表INSERTINTO `student`VALUES，，，（6，'）？？？

1. MySQL数据库的备份与恢复

1.1 Mysqldump的工作原理？

利用mysqldump命令备份的过程，实际上就是把数据从mysql库里以逻辑的sql语句的形式直接输出或者生成备份的文件的过程。

备份的数据过滤注释都是sql语句，结果如下：

[root@localhost opt]# egrep -v "#|*|--|^$" /opt/mysql_bak_B.sql 
USE `cuizhong`;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'zhangsan'),(2,'lisi'),(3,'wanger'),(4,'xiaozhang'),(5,'xiaowang'),(6,'???'),(7,'小红'),(8,'不认识'),(9,'李四');
UNLOCK TABLES;

1.2备份单个数据库练习多种参数使用

Mysql数据库自带了一个很好用的备份命令，就是mysqldump, 它的基本使用如下：

语法：mysqldump –u用户名 –p密码数据库名>备份的文件名

范烈1：备份名字为cuizhong的库

a.查看备份前的数据

[root@localhost~]# mysql -uroot -p123456  --default-character-set=latin1 -e "select * from cuizhong.student;"
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
|  7 | 小红|
|  8 | 不认识 |
|  9 | 李四|
+----+-----------+

b.执行备份命令

[root@localhost~]#mysqldump –uroot -p123456 cuizhong >/opt/mysql_bak.sql

c. 检查备份结果

[root@localhost ~]# egrep -v "#|*|--|^$" /opt/mysql_bak.sql 
DROP TABLE IF EXISTS `student`;  删除表
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
LOCK TABLES `student` WRITE; 锁表
INSERT INTO `student` VALUES (1,'zhangsan'),(2,'lisi'),(3,'wanger'),(4,'xiaozhang'),(5,'xiaowang'),(6,'???'),(7,'å°çº¢'),(8,'ä¸è®¤è¯†'),(9,'æŽå››');
UNLOCK TABLES;

范烈2：设置字符集参数备份解决乱码问题

a.查看备份前数据库客户端及服务器短的字符集设置

[root@localhost ~]# mysql -uroot -p123456 -e "show variables like 'character_set%';"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | latin1   |
| character_set_connection | latin1   |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results| latin1   |
| character_set_server | latin1   |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

b.指定字符集备份，这里为—default-character-set=latinl

mysqldump -uroot -p123456  --default-character-set=latin1 cuizhong>/opt/mysql_bak.sql

执行结果：

[root@localhost~]#mysqldump-uroot-p123456  --default-character-set=latin1 cuizhong>/opt/mysql_bak.sql
[root@localhost ~]# egrep -v "#|*|--|^$" /opt/mysql_bak.sql 
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'zhangsan'),(2,'lisi'),(3,'wanger'),(4,'xiaozhang'),(5,'xiaowang'),(6,'???'),(7,'小红'),(8,'不认识'),(9,'李四');
UNLOCK TABLES;

恢复测试：

[root@localhost ~]# mysql -uroot -p123456 -e "use cuizhong;drop table student;"
[root@localhost ~]# mysql -uroot -p123456 cuizhong </opt/mysql_bak.sql
[root@localhost~]# mysql -uroot -p123456 -e "select * from cuizhong.student;"
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
|  7 | 小红|
|  8 | 不认识 |
|  9 | 李四|
+----+-----------+

范例3：备份时加-B参数

和前面的备份文件对比，看看-B的作用

[root@localhost ~]# cd /opt/
[root@localhost opt]# diff mysql_bak.sql mysql_bak_B.sql 
18a19,26
> -- Current Database: `cuizhong`
> --
> 
> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cuizhong` /*!40100 DEFAULT CHARACTER SET latin1 */;   加了一个创建库的语句
> 
> USE `cuizhong`;  加了一个USE                    
> 
> --
51c59
< -- Dump completed on 2018-01-30  4:05:37
---
> -- Dump completed on 2018-01-30  4:18:06

提示：-B参数的作用是增加创建数据库和连接数据库的命令，即下面两条语句：

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cuizhong` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `cuizhong`;

测试利用-B的备份进行恢复测试：

（1）删除cuizhong库

mysql> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| cuizhong   |
| mysql  |
| performance_schema |
| zbf|
+--------------------+
6    rows in set (0.00 sec)
mysql> drop databases cuizhong;
mysql> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| mysql  |
| performance_schema |
| zbf|
+--------------------+

（2）恢复数据

先用不带-B参数的sql文件恢复

[root@localhost opt]# mysql -uroot -p123456 cuizhong </opt/mysql_bak.sql 
ERROR 1049 (42000): Unknown database 'cuizhong'

带-B参数恢复

[root@localhost opt]# mysql -uroot -p123456 </opt/mysql_bak_B.sql
[root@localhost opt]# mysql -uroot -p123456 -e "select * from cuizhong.student;"
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
|  7 | 小红|
|  8 | 不认识 |
|  9 | 李四|
+----+-----------+

范例4：用—compact备份

--compact参数说明

测试时用的比较多，可以优化输出的内容，让容量更少适合调适。

[root@localhost opt]# mysqldump -uroot -p123456 --compact -B cuizhong >/opt/mysql_bak_B_compact.sql

范例5：指定压缩命令压缩备份mysql数据

[root@localhost opt]# mysqldump -uroot -p123456 -B cuizhong|gzip >/opt/mysql_bak.sql.gz
[root@localhost opt]# ll |grep mysql_bak.s
 -rw-r--r--. 1 root root 2001 1月  30 04:05 mysql_bak.sql
 -rw-r--r--. 1 root root  858 1月  30 05:03 mysql_bak.sql.gz

压缩速率近3倍

通过上面例子我们可以得出结论

（1）导出数据用B参数。

（2）用gzip对备份的数据进行压缩。

1.3 备份多个库及多个参数

（1）备份多个库，多个库之间用你空格分开

[root@localhost opt]# mysqldump -uroot -p123456 -B zbf cuizhong|gzip >/opt/mysql.sql.gz

（2）-B参数说明

-B参数最关键是表示多个库并且增加user db和create database db 的信息

生产常用：

-B, --databases Dump several databases. Note the difference in usage; in this case no tables are given. All name arguments are regarded as database names. 'USE db_name;' will be included in the output.

参数说明：

改参数用于导出若干数据在备份结果中会加入create databases db和 use db.-B后面的参数将被作为数据名，-B参数比较常用当-B后的库列全时同-A参数。

1.4 如何做分库备份

（1）什么是分库分表备份

分库备份实质就是执行一个sql语句备份一个库，如果数据库里有多个库就执行多条相同的备份单个库的备份语句就可以备份多个库了，，注意每个库都可以用备份的库作为库名结尾加.sql,备份命令如下：

Mysql –uroot –p123456 –B cuizhong ….
Mysql –uroot –p123456 –B cuizhong_gbk
….

将上述命令放在一个脚本里，就是一个很土的备份脚本了，更详细的可以点击下方网址学习。

http://edu.51cto.com/course/808.html

如果数据数据库里面有很多库，不知道库的名称怎么办呢？我们可以用grep把所有的库筛选出来。

[root@localhost ~]# mysql -uroot -p123456 -e "show databases;"|grep -Evi "databa|info|per"|sed 's#^#mysqldump -uroot -p123456 -B #g'
mysqldump -uroot -p123456 -B cuizhong
mysqldump -uroot -p123456 -B mysql
mysqldump -uroot -p123456 -B zbf

执行一条命令分库备份实战

[root@localhost ~]# mysql -uroot -p123456 -e "show databases;"|grep -Evi "databa|info|per"|sed -r ' s#^([a-z].*$)#mysqldump -uroot -p123456 --events -B 1|gzip>/opt/bak/1.sql.gz#g'
mysqldump -uroot -p123456 --events -B cuizhong|gzip>/opt/bak/cuizhong.sql.gz
mysqldump -uroot -p123456 --events -B mysql|gzip>/opt/bak/mysql.sql.gz
mysqldump -uroot -p123456 --events -B zbf|gzip>/opt/bak/zbf.sql.gz

[root@localhost ~]# mysql -uroot -p123456 -e "show databases;"|grep -Evi "databa|info|per"|sed -r ' s#^([a-z].*$)#mysqldump -uroot -p123456 --events -B 1|gzip>/opt/bak/1.sql.gz#g'|bash

[root@localhost ~]# ll /opt/bak/

总用量 152
 -rw-r--r--. 1 root root874 1月  30 21:52 cuizhong.sql.gz
 -rw-r--r--. 1 root root 144238 1月  30 21:52 mysql.sql.gz
 -rw-r--r--. 1 root root795 1月  30 21:52 zbf.sql.gz

也可以用简单的脚本实现分库备份

[root@localhost ~]# cat beifen.sh 
#/bin/bash
for dbname in `mysql -uroot -p123456 -e "show databases;"|grep -Evi "databa|info|per"`
do
    mysqldump -uroot -p123456 --events -B $dbname|gzip>/opt/bak/${dbname}_bak.sql.gz
done
[root@localhost ~]# ll /opt/bak/
总用量 152
 -rw-r--r--. 1 root root874 1月  30 22:44 cuizhong_bak.sql.gz
 -rw-r--r--. 1 root root 144238 1月  30 22:44 mysql_bak.sql.gz
 -rw-r--r--. 1 root root795 1月  30 22:44 zbf_bak.sql.gz

分库备份缺点：文件多，很碎

（1）备一个完整全备，在做一个分库分表备份。

（2）脚本批量恢复多个SQL文件。

1.5 备份数据库表结构（不包含数据 ##）

mysqldump –d 参数只备份表结构

利用mysqldump –d 参数只备份表结构，例如备份cuizhong的所有表结构

[root@localhost ~]# mysqldump -uroot -p123456 --compact -d cuizhong student
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

也可以用mysqldump –t 参数备份表数据，-t相当于—no-create-info

[root@localhost ~]# mysqldump -uroot -p123456 --compact -t cuizhong student
INSERT INTO `student` VALUES (1,'zhangsan'),(2,'lisi'),(3,'wanger'),(4,'xiaozhang'),(5,'xiaowang'),(6,'???'),(7,'小红'),(8,'不认识'),(9,'李四');

更多用法课题执行mysqldump –-help查询。

1.6 刷新binlog参数

-F 刷新binlog参数防止以后重复恢复数据。

（1）在my.cnf中开启bin-log

[root@localhost ~]# cat /etc/my.cnf|grep log-bin
log-bin=mysql-bin

（2）重启mysql数据库

[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.^[[A. SUCCESS!

（3）重启完我们再查看一下mysql数据文件目录

我们发现出现了mysql-bin.000001，它记录了mysql修改过的数据。

[root@localhost ~]# ll /usr/local/mysql/data/
总用量 28720
drwx------. 2 mysql mysql 4096 1月  30 04:32 cuizhong
 -rw-rw----. 1 mysql mysql 18874368 1月  31 04:10 ibdata1
 -rw-rw----. 1 mysql mysql  5242880 1月  31 04:10 ib_logfile0
 -rw-rw----. 1 mysql mysql  5242880 1月  28 23:08 ib_logfile1
 -rw-r-----. 1 mysql root 17406 1月  31 04:10 localhost.localdomain.err
 -rw-rw----. 1 mysql mysql5 1月  31 04:10 localhost.localdomain.pid
drwx------. 2 mysql root  4096 1月  28 23:07 mysql
 -rw-rw----. 1 mysql mysql  107 1月  31 04:10 mysql-bin.000001
 -rw-rw----. 1 mysql mysql   19 1月  31 04:10 mysql-bin.index
drwx------. 2 mysql mysql 4096 1月  28 23:07 performance_schema
drwx------. 2 mysql mysql 4096 1月  28 23:15 zbf

（4）用-F参数刷新binlog备份数据库

[root@localhost ~]# mysqldump -uroot -p123456 -A -B -F --events|gzip >/opt/all.sql.gz

（5）在查看mysql数据文件目录

 [root@localhost ~]# ll /usr/local/mysql/data/
 总用量 28736
 drwx------. 2 mysql mysql 4096 1月  30 04:32 cuizhong
 -rw-rw----. 1 mysql mysql 18874368 1月  31 04:10 ibdata1
 -rw-rw----. 1 mysql mysql  5242880 1月  31 04:10 ib_logfile0
 -rw-rw----. 1 mysql mysql  5242880 1月  28 23:08 ib_logfile1
 -rw-r-----. 1 mysql root 17406 1月  31 04:10 localhost.localdomain.err
 -rw-rw----. 1 mysql mysql5 1月  31 04:10 localhost.localdomain.pid
 drwx------. 2 mysql root  4096 1月  28 23:07 mysql
 -rw-rw----. 1 mysql mysql  150 1月  31 04:10 mysql-bin.000001
 -rw-rw----. 1 mysql mysql  150 1月  31 04:10 mysql-bin.000002
 -rw-rw----. 1 mysql mysql   76 1月  31 04:10 mysql-bin.index
 drwx------. 2 mysql mysql 4096 1月  28 23:07performance_schema
 drwx------. 2 mysql mysql 4096 1月  28 23:15 zbf

1.7 最小化参数备份mysql数据库

--master-data 最小化备份mysql数据库直接找到binlog的位置

--master-data 用最小化参数就不用切割日志了，增量恢复数据知道这个binlog的位置就行了。

--master-data的值等于1时

--master-data等于1时可以帮助我们找到binlog的位置，在走主从同步的时候就不用show master status;查看binlog的位置了以及在从库中change master配置从不参数了。

[root@localhost ~]# mysqldump -uroot -p123456 --master-data=1 --compact cuizhong
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;

--master-data的值等于2时

--master-data等于1时会在CHANGE MASTER TO MASTER_LOG_FILE前面加注释，恢复数据的时候就不执行了

[root@localhost ~]# mysqldump -uroot -p123456 --master-data=2 --compact cuizhong
 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;

1.8 Mysqldump的关键参数说明

关键参数：更多参数可以通过mysqldump –-help查看

–B指定多个库，增加建库语句和use语句。
–compact 去掉注释适合调适输出，生产不用
–A所有库
–F刷新binlog日志
–master-data 增加binlog日志文件名及对应的位置点
–x ,--lock-all-tables 锁表
–l ,--lock-tables Lock all for read
–d 只备份表结构
–t 备份数据
–-single-transaction 适合innodb事务数据库备份

InnoDB表在备份时，通常启用—single-transaction 来保证备份的一致性，实际上它的工作原理是设置本次会话的隔离级为REPEATABLE READ 以确保本次会话(dump)时，不会看到其他会话已经提交的数据。

http://imysql.cn/2008_10_24_deep_into_mysqldump_options

1.9 生产场景常规备份

Myisam引擎备份命令：

Mysqldump –uroot –p123456 –A –B –F –-master-data=2 –x –events |gzip >/opt/all.sql.gz

Inoodb引擎备份命令：推荐使用

Mysqldump –uroot –p123456 –A –B –F –-master-data=2 –single-transaction --events |gzip >/opt/all.sql.gz

混合引擎以myisam引擎备份为主

2. 恢复数据实践

2.1 用source命令恢复数据库

进入mysql数据库控制台，mysql –u root –p登陆后，然后用source 命令后面加备份的兼备文件的路径。

实战演练：

a.进入mysql控制台删掉数据库cuizhong模拟数据恢复

mysql> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| cuizhong   |
| mysql  |
| performance_schema |
| zbf|
+--------------------+
5 rows in set (0.01 sec)

mysql> drop database cuizhong
-> ;
Query OK, 1 row affected (0.12 sec)

mysql> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| mysql  |
| performance_schema |
| zbf|
+--------------------+
4 rows in set (0.01 sec)

b.用system找一下我们之前的备份的文件，也可以退出控制台用-e参数

mysql> system ls /opt/
all.sql.gz  cuizhong.sql mysql_bak_B_compact.sql  mysql_bak.sql    mysql.sql zbf.sql
bak    cuizhong.sql.gz  mysql_bak_B.sql      mysql_bak.sql.gz    mysql.sql.gz  zbf.sql.gz

c.用source恢复数据，查看一下cuizhong库

mysql> source /opt/mysql_bak_B.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
…省略……

看数据已经恢复了

mysql> select * from cuizhong.student;
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
|  7 | 小红|
|  8 | 不认识 |
|  9 | 李四|
+----+-----------+

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

[root@localhost opt]# mysql -uroot -p123456 -e "use zbf;drop table student;show tables;"
ERROR 1051 (42S02) at line 1: Unknown table 'student'
[root@localhost opt]# mysql -uroot -p123456 zbf</opt/zbf_bak.sql
root@localhost opt]# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 24
Server version: 5.5.32-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use zbf
Database changed
mysql> show tables;
+---------------+
| Tables_in_zbf |
+---------------+
| student   |
+---------------+
1 row in set (0.00 sec)

问题：分库分表备份的数据如何快速恢复？

通过脚本指定库和表，指定mysql恢复。

（1）删除cuizhong和zbf库。

[root@localhost bak]# mysql -uroot -p123456 -e "drop database cuizhong;drop database zbf"
[root@localhost bak]# mysql -uroot -p123456 -e "show databases;"
+--------------------+
| Database   |
+--------------------+
| information_schema |
| mysql  |
| performance_schema |
+--------------------+

（2）如果数据库比较多不知道库名可以用mysql的-e参数筛选也可以筛选备份的文件得到库名

[root@localhost bak]# gzip -d *
[root@localhost bak]# ls
cuizhong_bak.sql  zbf_bak.sql
[root@localhost bak]# ls *.sql|awk -F "_" ' {print $1}'
cuizhong
zbf

或者用sed命令

[root@localhost bak]# ls *.sql|sed ' s#_bak.sql##g'
cuizhong
zbf

（3）执行脚本分库分表恢复数据

[root@localhost bak]# for dbname in `ls *.sql|sed ' s#_bak.sql##g'`;do mysql -uroot -p123456 < ${dbname}_bak.sql;done
[root@localhost bak]# mysql -uroot -p123456 -e "show databases;"
+--------------------+
| Database   |
+--------------------+
| information_schema |
| cuizhong   |
| mysql  |
| performance_schema |
| zbf|
+--------------------+

3.Mysql 带-e参数实现非交互式对话

(1) 恢复mysql数据

[root@localhost bak]# mysql -uroot -p123456 -e "truncate table cuizhong.student;"
[root@localhost bak]# mysql -uroot -p123456 -e "select * from cuizhong.student;"
[root@localhost bak]# mysql -uroot -p123456 cuizhong </opt/bak/
cuizhong_bak.sql  zbf_bak.sql   
[root@localhost bak]# mysql -uroot -p123456 cuizhong </opt/bak/cuizhong_bak.sql 
[root@localhost bak]# mysql -uroot -p123456 -e "select * from cuizhong.student;"
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
|  7 | 小红|
|  8 | 不认识 |
|  9 | 李四|
+----+-----------+

(2) 不登录数据库执行mysql命令小结

http://blog.51cto.com/oldboy/632608

4.mysql进程的查看以及在线修改参数

4.1 mysql进程的查看

Show processlist; 查看正在执行的SQL语句

这种方式查看不全。例如：

（1）查看当前数据库有多少人连接

[root@localhost bak]# mysql -uroot -p123456 -e "show processlist;"
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host  | db   | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 46 | root | localhost | NULL | Query   |0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
[root@localhost bak]# mysql -uroot -p123456 -e "show processlistG"
*************************** 1. row ***************************
 Id: 47
   User: root
   Host: localhost
 db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist

Show variables;查看数据库的参数信息

（2）查看mysql的所有参数配置

[root@localhost bak]# mysql -uroot -p123456 -e "show variables;"

比如我们查看binlog日志有没有开启

[root@localhost bak]# mysql -uroot -p123456 -e "show variables;"|grep log_bin
log_bin    ON
log_bin_trust_function_creators    OFF
sql_log_bin    ON

（3）查看mysql数据库的状态

Show status查看当前会话的状态

[root@localhost bak]# mysql -uroot -p123456 -e "show status;"

Show global status; 动态全局查看整个数据库运行的状态

列如：查看select多少次。

[root@localhost bak]# mysql -uroot -p123456 -e "show  global status;"|grep sel
Com_insert_select    0
Com_replace_select    0
Com_select    205

通过看上面的结果我们已经select查询了205次，接下来我再查询一次

[root@localhost bak]# mysql -uroot -p123456 -e "select * from cuizhong.student;"
+----+-----------+
| id | name  |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi  |
|  3 | wanger|
|  4 | xiaozhang |
|  5 | xiaowang  |
|  6 | ???   |
|  7 | 小红|
|  8 | 不认识 |
|  9 | 李四|
+----+-----------+
[root@localhost bak]# mysql -uroot -p123456 -e "show  global status;"|grep sel
Com_insert_select    0
Com_replace_select    0
Com_select    208

这里显示的次数是208次，包括自己查询。也可以查看insert ,update等的状态。

也可以登录mysql查看

mysql> show global status like "%sele%";
+------------------------+-------+
| Variable_name  | Value |
+------------------------+-------+
| Com_insert_select  | 0 |
| Com_replace_select | 0 |
| Com_select | 209   |
| Select_full_join   | 0 |
| Select_full_range_join | 0 |
| Select_range   | 0 |
| Select_range_check | 0 |
| Select_scan| 279   |
+------------------------+-------+

4.2 不重启数据库修改数据库参数，重启数据库参数仍然生效实战

(1) 查看一下key_buffer_size,配置文件my.cnf里的key_buffer_size是myisam引擎存放索引的缓冲区。

[root@localhost bak]# grep  key_buffer /etc/my.cnf 
key_buffer_size = 16K
key_buffer_size = 8M
[mysqld]
#character-set-server = utf8
port= 3306
socket  = /usr/local/mysql/tmp/mysql.sock
skip-external-locking
#myisam引擎存放索引的缓冲区
key_buffer_size = 16K
max_allowed_packet = 1M

(2) 登录mysql全局修改key_buffer_size

set global `key_buffer_size`=1024102432在线修改`key_buffer_size`参数不用重启数据库调整数据库参数，直接生效，重启后失效。

mysql> show variables like 'key_buffer%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| key_buffer_size | 16384 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> set global key_buffer_size=1024*1024*32;
Query OK, 0 rows affected (0.11 sec)
mysql> show variables like 'key_buffer%';
+-----------------+----------+
| Variable_name   | Value|
+-----------------+----------+
| key_buffer_size | 33554432 |
+-----------------+----------+
1 row in set (0.00 sec)

(3) 退出mysql重启mysql服务，再查看一下key_buffer_size的值

[root@localhost bak]# mysql -uroot -p123456 -e "show variables like 'key_buffer%';";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| key_buffer_size | 16384 |
+-----------------+-------+

重启完mysql服务之后key_buffer_size的值有变为16k了，所以全局修改mysql参数最好的方法就是全局和配置文件都要修改，这样就会永久生效。

mysql> show variables like 'key_buffer%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| key_buffer_size | 16384 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> set global key_buffer_size=1024*1024*32;
Query OK, 0 rows affected (0.01 sec)

[root@localhost bak]vim /etc/my.cnf
....省略....
#myisam引擎存放索引的缓冲区
key_buffer_size = 32M

重启mysql服务再查看key_buffer_size的值

[root@localhost bak]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@localhost bak]# mysql -uroot -p123456 -e "show variables like 'key_buffer%';";
+-----------------+----------+
| Variable_name   | Value|
+-----------------+----------+
| key_buffer_size | 33554432 |
+-----------------+----------+

这次我们看重启mysql服务key_buffer_size的值也不会变了。

小结：生产常用命令

（1）Show status;查看当前会话的数据库状态信息。

（2）Show global status; 查看整个数据库运行的状态，很重要要分析并要做好监控。

（3）Show processlist; 查看正在执行的SQL语句，看不全。

（4）Show full processlist;查看正在执行的完整的SQL语句，完整显示。

（5）set global key_buffer_size=1024*1024*32不重启数据库调整数据库参数，直接生效，重启后失效。

（6）Show variables;查看数据库的参数信息，例如：my.cnf里的参数的生效情况。

5.MySQL增量备份

5.1 Mysqlbinlog命令介绍

5.1.1 mysqlbinlog是什么？

mysqlbinlog是解析mysql的binlog日志。在my.cnf配置文件里打开binlog。

[root@localhost data]# grep log-bin /etc/my.cnf 
log-bin=mysql-bin

5.1.2那么mysql的binlog日志是什么呢

数据目录下的文件就是mysql的binlog

[root@localhost bak]# cat /usr/local/mysql/data/mysql-bin.index 
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009
./mysql-bin.000010

5.1.3 Mysql的日志的作用是什么呢？

用来记录mysql内部增删改等对mysql数据库所有库的更新内容的记录，查询除外比如select,show。

5.2 mysql增量备份恢复及mysqlbinlog参数说明

5.2.1 mysqlbinlog 参数举例说明

（1）mysqlbinlog的 -d 参数

Mysqlbinlog的-d参数拆分binlog日志，截取指定库的binlog。

Binlog日志里记录了所有库更新的内容的记录，在恢复数据库时不能把所有库更新的内容全部恢复会报错，因为不是所有库都出故障。

-d参数指定想要恢复的库

mysqlbinlog --no-defaults -d linzhongniao mysql-bin.000011 >linzhongniao.sql

更多的参数可以mysqlbinlog –help查看

（2）基于位置的增量恢复

指定开始位置和结束位置

我们先less查看一下mysql-bin.000011，我只把下图中带insert的部分备份出来，下图告诉了我们开始位置、时间和结束位置。如果只指定开始位置截取从开始位置到结尾。

Mysql DBA 高级运维学习笔记-Mysql增量备份及分库分表备份数据恢复实战

实践：-r相当于重定向

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000011 --start-position=906 --stop-position=1051 -r pos.sql

（3）基于时间点的增量恢复（基于时间就不准确了）

指定开始时间和结束时间

比如我想恢复180201 2:45:16到180201 2:47:01之间的数据。

Mysql DBA 高级运维学习笔记-Mysql增量备份及分库分表备份数据恢复实战

实践：

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000011 --start-datetime='2018-02-01 2:45:16' --stop-datetime='2018-02-01 2:47:01' -r time.sql

比如说谁谁谁在几点用drop删除了一条记录，那我们在恢复数据的时候就要把这个时间段跳过去。

5.3 mysql增量恢复企业实战

mysql增量恢复企业实战请阅读老男孩老师博文

http://blog.51cto.com/oldboy/1431161

http://blog.51cto.com/oldboy/1431172