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

原创

海风掠过 2018-02-01 00:57:24 ©著作权

©著作权归作者所有：来自51CTO博客作者海风掠过的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.mysqldump的工作原理？

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

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

[root@localhost opt]# egrep -v "#|\*|--|^$" /opt/mysql_bak_B.sql 
USE `linzhongniao`;
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.备份数据库mysqldump命令实战

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

mysqldump –u用户名 –p密码 参数 库1 库2 > 备份的文件名

2.1 备份linzhongniao库不指定参数

查看要备份数库，mysql命令的-e参数可以实现非交互式对话

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "select * from linzhongniao.student;"
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | lisi|
|  4 | woshishei   |
|  5 | xiaozhang   |
+----+-------------+
[root@linzhongniao ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock linzhongniao >/opt/linzhongniao_bak.sql

检查备份的结果

[root@linzhongniao ~]# grep -Ev "#|\*|--|^$" /opt/linzhongniao_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=6 DEFAULT CHARSET=utf8;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'linzhogniao'),(2,'wwn1314'),(3,'lisi'),(4,'woshishei'),(5,'xiaozhang');
UNLOCK TABLES;

2.2 mysqldump命令参数说明

2.2.1 `--default-character-set`参数说明

备份前查看数据库客户端及服务器端的字符集设置，最好把字符集调成utf8我这里已经在编译安装的时候指定utf8字符集。为避免备份出来的数据存在乱码的可能，可以指定字符集备份数据。

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like 'character_set%'"
+--------------------------+----------------------------------+
| Variable_name| Value|
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database   | utf8 |
| character_set_filesystem | binary   |
| character_set_results| utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir   | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
[root@linzhongniao ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock --default-character-set=utf8 linzhongniao >/opt/linzhongniao_bakutf8.sql
[root@linzhongniao ~]# egrep -v "#|\*|--|^$" /opt/linzhongniao_bakutf8.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=6 DEFAULT CHARSET=utf8;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'linzhogniao'),(2,'wwn1314'),(3,'lisi'),(4,'woshishei'),(5,'xiaozhang');
UNLOCK TABLES;

2.2.2 -B参数

mysqldump的-B参数可以增加创建数据库和连接数据库的命令，即下面两条语句：

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

(1)带-B参数导出数据

[root@linzhongniao ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B linzhongniao >/opt/linzhongniao_bakB.sql

(2)查看带B参数和不带B参数的区别

(3)恢复测试

为大家能够更加理解-B参数的作用，删除数据库然后分别用带B参数导出的数据的不带B参数导出的数据进行测试

删除linzhongniao库

[root@linzhongniao opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "drop database linzhongniao;" 
[root@linzhongniao opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linzhongniao_gbk   |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+

不用带-B参数的备份恢复数据

[root@linzhongniao opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock </opt/linzhongniao_bak.sql 
ERROR 1046 (3D000) at line 22: No database selected

用带-B参数的备份恢复数据

[root@linzhongniao opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock </opt/linzhongniao_bakB.sql 
[root@linzhongniao opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "select * from linzhongniao.student;"
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | lisi|
|  4 | woshishei   |
|  5 | xiaozhang   |
+----+-------------+

(4)总结

通过上面的测试可以指导mysqldump的-B参数可以备份创建库的语句和连接库的语句，避免误删库和表导致数据无法恢复的问题

2.2.3 `--compact`参数

--compact参数：测试时用的比较多，可以优化输出的内容，让容量更少适合调适。如果备份的数据不用做测试，想达到优化磁盘空间的效果可以对备份的数据用gzip进行压缩。

[root@linzhongniao opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock --compact linzhongniao|gzip>/opt/linzhongniao_bakcompact.sql.gz
[root@linzhongniao opt]# ls linzhongniao_bakcompact.sql.gz 
linzhongniao_bakcompact.sql.gz

2.2.4 -A参数

--all-databases, -A：——备份所有数据库，在所有数据库中转储所有表。这与使用——database选项和在命令行中命名所有数据库。

[root@linzhongniao opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A >/opt/linzhongniao_bakA.sql   -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

这个地方有一个警告：警告:跳过表mysql.event的数据。显式指定——events选项，看一下mysqldump的--events参数。events(事件)官方的意思是在输出中包含转储数据库的事件调度器事件。出现警告我们就将这个参数加上。

[root@linzhongniao opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock --events -A >/opt/linzhongniao_bakA.sql
[root@linzhongniao opt]# ls linzhongniao_bakA.sql 
linzhongniao_bakA.sql

2.2.5 -d参数

没有数据,-d不要写入任何表行信息(也就是说，不要转储表内容)。这是如果您只想转储表的CREATE TABLE语句(例如，通过加载转储文件来创建表的空副本)。只备份表结构。

[root@linzhongniao opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -d linzhongniao>/opt/linzhongniao_bakd.sql
[root@linzhongniao opt]# egrep -v "#|\*|--|^$" linzhongniao_bakd.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=6 DEFAULT CHARSET=utf8;

2.2.6 -t参数

-t参数相当于--no-create-info：不要编写重新创建每个转储表的CREATE TABLE语句。只备份数据。

[root@linzhongniao opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -t linzhongniao>/opt/linzhongniao_bakt.sql
[root@linzhongniao opt]# egrep -v "#|\*|--|^$" linzhongniao_bakt.sql LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'linzhogniao'),(2,'wwn1314'),(3,'lisi'),(4,'woshishei'),(5,'xiaozhang');
UNLOCK TABLES;

2.2.6 -F参数

-F参数刷新binlog，在备份数据的时候可能还会有用户写入为了防止binlog记录的数据丢失，需要用-F参数对binlog日志文件进行切割然后会产生一个新的binlog日志文件，有新的数据就会往新生成的binlog日志中写入。

如果想让mysql有记录日志的功能就要已开启binlog。如下所示：

[root@linzhongniao opt]# grep "log-bin" /data/3306/my.cnf 
log-bin = /data/3306/mysqlbin_linzhongniao
[root@linzhongniao opt]# ls /data/3306/mysqlbin_linzhongniao.00001
mysqlbin_linzhongniao.000010  mysqlbin_linzhongniao.000011  mysqlbin_linzhongniao.000012
mysqlbin_linzhongniao.000013  mysqlbin_linzhongniao.000014

备份数据

[root@linzhongniao opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B -F >/opt/linzhongniao_bakF.sql 
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
[root@linzhongniao opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock --events -A -B -F >/opt/linzhongniao_bakF.sql
[root@linzhongniao opt]# ls /data/3306/mysqlbin_linzhongniao.00001
mysqlbin_linzhongniao.000010  mysqlbin_linzhongniao.000012  mysqlbin_linzhongniao.000014
mysqlbin_linzhongniao.000011  mysqlbin_linzhongniao.000013  mysqlbin_linzhongniao.000015

2.2.7 `--master-data`参数

(1)--master-data等于1时

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

[root@linzhongniao opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock --master-data=1 linzhongniao>/opt/linzhongniao_bakmaster.sql
[root@linzhongniao opt]# egrep -v "#|\*|--|^$" /opt/linzhongniao_bakmaster.sql   
CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin_linzhongniao.000015', MASTER_LOG_POS=107;
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=6 DEFAULT CHARSET=utf8;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'linzhogniao'),(2,'wwn1314'),(3,'lisi'),(4,'woshishei'),(5,'xiaozhang');
UNLOCK TABLES;

(2)--master-data等于2时

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

[root@linzhongniao opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock --master-data=2 linzhongniao>/opt/linzhongniao_bakmaster2.sql
[root@linzhongniao opt]# grep  "CHANGE MASTER" /opt/linzhongniao_bakmaster2.sql  
 -- CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin_linzhongniao.000015', MASTER_LOG_POS=107;

2.2.8 -q参数

-q(quick快速)参数，什么都不做，只是导出时加了一个sql_no_chche来确保不会读取缓存里的数据

[root@linzhongniao opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -q -B linzhongniao>/opt/linzhongniao_bakq.sql
[root@linzhongniao opt]# egrep -v "#|\*|--|^$" /opt/linzhongniao_bakq.sql 
USE `linzhongniao`;
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=6 DEFAULT CHARSET=utf8;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'linzhogniao'),(2,'wwn1314'),(3,'lisi'),(4,'woshishei'),(5,'xiaozhang');
UNLOCK TABLES;

2.2.9 `--lock-table`参数

锁表，导出数据时加了一个read local lock，该锁不会阻止读，也不会阻止写入数据。

[root@linzhongniao opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock --lock-table -B linzhongniao>/opt/linzhongniao_bak_lock-table.sql
[root@linzhongniao opt]# egrep -v "#|\*|--|^$" /opt/linzhongniao_bak_lock-table.sql 
USE `linzhongniao`;
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=6 DEFAULT CHARSET=utf8;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'linzhogniao'),(2,'wwn1314'),(3,'lisi'),(4,'woshishei'),(5,'xiaozhang');
UNLOCK TABLES;

2.2.10 `--lock-all-tables`参数

这个就不一样了，它请求发起一个全局的读锁，会阻止对所有表的写入操作，以此来确保数据的一致性，备份完成后，该会话会断开，会自动解锁。

[root@linzhongniao opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock --lock-all-tables -B linzhongniao>/opt/linzhongniao_bak_lock-all-tables.sql
[root@linzhongniao opt]# egrep -v "#|\*|--|^$" /opt/linzhongniao_bak_lock-all-tables.sql 
USE `linzhongniao`;
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=6 DEFAULT CHARSET=utf8;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'linzhogniao'),(2,'wwn1314'),(3,'lisi'),(4,'woshishei'),(5,'xiaozhang');
UNLOCK TABLES;

2.2.11 `–-single-transaction`参数

Inodb表在备份时，通常启用选项--single-transaction来保证备份的一致性，实际上它的工作原理是设定本次会话的时间间隔级别为：PEPEATABLE READ，以确保本次会话dump时，不会看到其他会话已经提交了的数据。

[root@linzhongniao opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "use linzhongniao;show create table student\G"
 *************************** 1. row ***************************
   Table: student
Create Table: CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
[root@linzhongniao opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock --single-transaction -B linzhongniao >/opt/linzhongniao_bak_single.sql
[root@linzhongniao opt]# egrep -v "#|\*|--|^$" /opt/linzhongniao_bak_single.sql  USE `linzhongniao`;
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=6 DEFAULT CHARSET=utf8;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'linzhogniao'),(2,'wwn1314'),(3,'lisi'),(4,'woshishei'),(5,'xiaozhang');
UNLOCK TABLES;

2.3 mysqldump的关键参数说明

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

1.–B指定多个库，增加建库语句和use语句。

2.--compact 去掉注释适合调适输出，生产不用

3.–A所有库

4.–F刷新binlog日志

5.–master-data 增加binlog日志文件名及对应的位置点

6.–x相当于--lock-all-tables 不允许读写，备份会话结束后解锁

7.–l相当于--lock-tables 锁表允许读写

8.–d 只备份表结构

9.–t 备份数据

10.–-single-transaction 适合innodb事务数据库备份

下面是有关mysqldump常用命令的博文

http://imysql.cn/2008_10_24_deep_into_mysqldump_options

2.4 生产场景常用的备份命令

Myisam引擎备份命令：

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

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

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

3.恢复数据实践

3.1 用source命令恢复数据库

(1)删除linzhongniao库

[root@linzhongniao opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;drop database linzhongniao;"
+--------------------+
| Database   |
+--------------------+
| information_schema |
| linzhongniao_gbk   |
| mysql  |
| performance_schema |
| school |
| test   |
+--------------------+

(2)恢复数据

mysql> system ls /opt/
linzhongniao_bakA.sql   linzhongniao_bak_lock-all-tables.sql  linzhongniao_bak_single.sql
linzhongniao_bakB.sql   linzhongniao_bak_lock-table.sql   linzhongniao_bak.sql
linzhongniao_bakcompact.sql.gz  linzhongniao_bakmaster2.sql   linzhongniao_bakt.sql
linzhongniao_bakd.sql   linzhongniao_bakmaster.sqllinzhongniao_bakutf8.sql
linzhongniao_bakF.sql   linzhongniao_bakq.sql linzhongniao_bal.sql
mysql> source /opt/linzhongniao_bakB.sql
mysql> show databases like '%niao';
+------------------+
| Database (%niao) |
+------------------+
| linzhongniao |
+------------------+
1 row in set (0.00 sec)

3.2 用输入重定向方法

(1)删除linzhongniao库

[root@linzhongniao opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "drop database linzhongniao;"
[root@linzhongniao opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;"
 +--------------------+
| Database            |
 +--------------------+
| information_schema  |
| linzhongniao_gbk    |
| mysql               |
| performance_schema  |
| school              |
| test                |
 +--------------------+

(2)恢复数据

[root@linzhongniao opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock </opt/linzhongniao_bakB.sql 
[root@linzhongniao opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;"
 +--------------------+
| Database           |
 +--------------------+
| information_schema |
| linzhongniao       |
| linzhongniao_gbk   |
| mysql              |
| performance_schema |
| school             |
| test               |
 +--------------------+

4.分库备份的数据如何快速恢复?

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

(1)备份数据

[root@linzhongniao ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock --single-transaction -B linzhongniao>/bak/linzhongniao_bak.sql
[root@linzhongniao ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock --single-transaction -B school>/bak/school_bak.sql
[root@linzhongniao ~]# ls /bak/
linzhongniao_bak.sql  school_bak.sql

(2)删除linzhongniao库和school库

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "drop database linzhongniao"
[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "drop database school"  
[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases"  
+--------------------+
| Database   |
+--------------------+
| information_schema |
| linzhongniao_gbk   |
| mysql  |
| performance_schema |
| test   |
+--------------------+

(3)恢复数据

如果数据库比较多不知道库名可以筛选备份的文件得到库名。

方法一：用sed命令

[root@linzhongniao ~]# ls /bak/|sort|sed 's#_bak.sql##g'
linzhongniao
school

方法二：用awk命令

[root@linzhongniao ~]# ls /bak/|sort|awk -F "_" '{print $1}'
linzhongniao
school

在命令行用for循环批量恢复数据

[root@linzhongniao ~]# for dbname in `ls /bak/|sort|awk -F "_" '{print $1}'`;do mysql -uroot -p123456 -S /data/3306/mysql.sock </bak/${dbname}_bak.sql;done
[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;"
+--------------------+
| Database   |
+--------------------+
| information_schema |
| linzhongniao   |
| linzhongniao_gbk   |
| mysql  |
| performance_schema |
| school |
| test   |
+--------------------+

5.mysql常用基础命令

5.1 查看当前数据库有多少用户连接

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show processlist\G"
*************************** 1. row ***************************
 Id: 47
   User: root
   Host: localhost
 db: linzhongniao
Command: Sleep
   Time: 4562
  State: 
   Info: NULL
*************************** 2. row ***************************
 Id: 66
   User: root
   Host: localhost
 db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist

5.2 查看数据库的所有参数信息

例如查看bin_log是否开启

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like '%log_bin%'"
 +---------------------------------+-------+
| Variable_name   | Value |
 +---------------------------------+-------+
| log_bin | ON|
| log_bin_trust_function_creators | OFF   |
| sql_log_bin | ON|
 +---------------------------------+-------+
[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables;"|grep "log_bin" 
log_bin ON
log_bin_trust_function_creators OFF
sql_log_bin ON

5.3 查看当前会话的状态

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show status;"

5.4 动态查看整个数据库运行的状态

例如：select查询了数据库多少次

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show global status;"|grep "sel"
Com_insert_select   0
Com_replace_select  0
Com_select  525

通过看上面的结果我们已经select查询了525次，接下来再执行select查询命令

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "select * from linzhongniao.student;"
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | lisi|
|  4 | woshishei   |
|  5 | xiaozhang   |
+----+-------------+
[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show global status;"|grep "sel"Com_insert_select   0
Com_replace_select  0
Com_select  528

一共查询了528次，查询linzhongniao库是一次查询student表是一次，也可以查看insert插入，update修改等的执行状态。

5.5 修改数据库参数不重启生效实战

以key_buffer_size参数为例，key_buffer_size为myisam引擎存放索引的缓冲区

(1)查看key_buffer_size的值

[root@linzhongniao ~]# grep "key_buffer" /data/3306/my.cnf 
key_buffer_size = 16M
[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like 'key_buffer%'\G"
 *************************** 1. row ***************************
Variable_name: key_buffer_size
Value: 16777216

(2)修改key_buffer_size参数的值不重启数据库生效

上面是查看key_buffer_size参数值的两种，要想使参数不用重启生效，那么必须把上面两种方法查看的参数值都做修改。

将key_buffer_size参数的值修改为32M，先修改my.cnf配置文件

[root@linzhongniao ~]# sed -i 's#key_buffer_size = 16M#key_buffer_size = 32M#g' /data/3306/my.cnf 
[root@linzhongniao ~]# grep "key_buffer" /data/3306/my.cnf   key_buffer_size = 32M

还要修改mysql内置参数的值，可以用set global key_buffer_size=1024*1024*32 命令

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "set global key_buffer_size=1024*1024*32;"
[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like 'key_buffer%'\G"
 *************************** 1. row ***************************
Variable_name: key_buffer_size
Value: 33554432

5.6 小结：生产常用命令

（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里的参数的生效情况。

6.mysql增量备份介绍

6.1 mysqlbinlog命令介绍

mysqlbinlog是解析mysql的binlog日志的命令。要想让数据有binlog记录日志功能必须在my.cnf配置文件中开启binlog功能。

[root@linzhongniao ~]# grep "log-bin" /data/3306/my.cnf 
log-bin = /data/3306/mysqlbin_linzhongniao

6.2 mysql的日志文件是什么呢

用来记录mysql内部增删改查等对mysql数据库所有库的更新内容的记录

6.3 mysqlbinlog参数说明

6.3.1 -d参数

Mysqlbinlog的-d参数拆分binlog日志，截取指定库的binlog。binlog日志里记录了所有库更新的内容的记录，在恢复数据库时不能把所有库更新的内容全部恢复会报错，因为不是所有库都出故障。

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

[root@linzhongniao ~]# mysqlbinlog -d linzhongniao /data/3306/mysqlbin_linzhongniao.000015 >linzhongniao.sql

6.3.2 基于位置的增量恢复

指定开始位置和结束位置

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

实践：-r相当于重定向

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

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

指定开始时间和结束时间

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

实践：

[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删除了一条记录，那我们在恢复数据的时候就要把这个时间段跳过去。

7.增量恢复参考资料

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

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

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

上一篇：Mysql DBA 高级运维学习之路-mysql数据库乱码问题

下一篇：Mysql DBA 高级运维学习之路-Mysql主从复制原理及实战部署

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯