06.19 MySQL数据库备份（进阶）

第一章 SQL语句分类

1. DDL
数据库定义语言 管理库和表 create、drop、alter等
2. DCL
数据控制语言 用户管理授权 grant、revoke、commit；rollback
3. DML
数据操作语言 针对表里的数据 insert、delete、update、select

第二章 DDL语句之数据库管理语言

1. 创建数据库

mysql> create database oldboy;   #<==字符集和编译指定相同.

2. 查询数据库

show databases;show databases like '%old%';

3. 查看建库的语句

mysql> show create database oldboy\G

4. 查看字符集

指定字符集建库CREATE DATABASE db_name CHARACTER SET  charset_name COLLATE collation_nameCREATE DATABASE oldgirl CHARACTER SET gbk COLLATE gbk_chinese_ci;show character set;    #<==找字符集.mysql> show variables like '%char%';  #查看默认字符集

5. 改库的字符集

ALTER DATABASE [db_name] CHARACTER SET  charset_name COLLATE collation_nameALTER DATABASE oldgirl CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

6. 字符集排序规则

mysql> show  collation;

7. 删除库

mysql> drop database oldboy;    

8. 切换库

mysql> use oldboyDatabase changedmysql> select database();+------------+| database() |+------------+| oldboy     |+------------+1 row in set (0.00 sec)

9. 创建表

create table student(id int(4) not null,name char(20) not null,age tinyint(2)  NOT NULL default '0',dept varchar(16)  default NULL);

10. 查看表内容

mysql> select * from test;

11. 查看表结构

desc student;

12. 查看建表的语句

mysql> show create table student\G*************************** 1. row ***************************       Table: studentCreate Table: CREATE TABLE `student` (  `id` int(4) NOT NULL,  `name` char(20) NOT NULL,  `age` tinyint(2) NOT NULL DEFAULT '0',  `dept` varchar(16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

13. 开发人员建表语句

CREATE TABLE `subject_comment_manager` (  `subject_comment_manager_id` bigint(12) NOT NULL auto_increment COMMENT '主键',  `subject_type` tinyint(2) NOT NULL COMMENT '素材类型',  `subject_primary_key` varchar(255) NOT NULL COMMENT '素材的主键',  `subject_title` varchar(255) NOT NULL COMMENT '素材的名称',  `edit_user_nick` varchar(64) default NULL COMMENT '修改人',  `edit_user_time` timestamp NULL default NULL COMMENT '修改时间',  `edit_comment` varchar(255) default NULL COMMENT '修改的理由',  `state` tinyint(1) NOT NULL default '1' COMMENT '0代表关闭，1代表正常',  PRIMARY KEY  (`subject_comment_manager_id`),  KEY `IDX_PRIMARYKEY` (`subject_primary_key`(32)),   #<==括号内的32表示对前32个字符做前缀索引。  KEY `IDX_SUBJECT_TITLE` (`subject_title`(32))  KEY `index_nick_type` (`edit_user_nick`(32),`subject_type`)#<==联合索引，此行为新加的，用于给大家讲解的。实际表语句内没有此行。) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

14. char和varchear区别

15. 更改表名

rename table student to test;alter table test rename to student;

16. 添加行默认至行尾

mysql> alter table test add sex char(4);Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc test;+-------+----------+------+-----+---------+----------------+| Field | Type     | Null | Key | Default | Extra          |+-------+----------+------+-----+---------+----------------+| id    | int(4)   | NO   | PRI | NULL    | auto_increment || name  | char(20) | NO   |     | NULL    |                || sex   | char(4)  | YES  |     | NULL    |                |+-------+----------+------+-----+---------+----------------+3 rows in set (0.00 sec)

17. 添加至name行后

#下面指定添加年龄列到name列后面的位置，示例如下：alter table test add age int(4) after name;

18. 在第一列添加字段

#现在通过下面的命令在第一列添加qq字段。alter table test add qq varchar(15) first;

19. 删除字段

alter table test drop qq;

20. 同时添加两个字段

#若要同时添加两个字段，可采用如下命令。mysql> alter table test add age tinyint(2) first,add qq varchar(15);

21. 改变字段

改变字段的命令如下：alter table ett_ambiguity change ambiguity_state  ambiguity_state tinyint  comment '状态，默认1=正常，0=失效';ALTER TABLE `ett_photo`MODIFY COLUMN `PHOTO_DESCRIPTION` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '描述' AFTER PHOTO_TITLE`;修改字段类型的命令如下：mysql> alter table test modify age char(4) after name;         Query OK, 6 rows affected (0.00 sec)Records: 6  Duplicates: 0  Warnings: 0修改字段名称的命令如下：mysql> alter table test change age oldboyage char(4) after name;       

第三章 DCL语句之数据库控制语言

1. 创建用户

CREATE USER '用户'@'主机' IDENTIFIED BY '密码';mysql> create user oldboy@localhost identified by 'oldboy123';

2. 查看用户权限

mysql> show grants for oldboy@localhost\G

3. 删除用户

mysql> select user,host from mysql.user;mysql> drop user 'root'@'::1';mysql> drop user 'root'@'mysql\_52';

4. 给用户授权

mysql> grant all on *.* to chao@localhost;

5. 创建用户同时授权

mysql> grant all on *.* to oldgirl@localhost identified by 'oldboy123';mysql> flush privileges;

6. 收回权限

mysql> revoke insert on *.* from 'oldboy'@'localhost';mysql> show grants for 'oldboy'@'localhost'\G*************************** 1. row ***************************Grants for oldboy@localhost: GRANT SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515'1 row in set (0.00 sec)###删除掉一个  然后就会显示所有

7. 博客授权

grant select,insert,update,delete,create,drop on blog.* to  'blog'@'172.16.1.%' identified by 'oldboy123';revoke create,drop on blog.* from 'blog'@'172.16.1.%';mysql> create user blog@localhost identified by 'oldboy123';mysql> select user,host from mysql.user;

8. 授权不规范导致的生产血案
运维人员授权用户all权限了，导致开发通过该用户自行改了表结构（字段），最后造成服务出问题，最后黑锅甩在了运维人员身上。
运维人员排查了半天没结果，最后对比表结构（把生产数据和备份的数据），发现了问题，最后告诉开发，把字段改回去，服务就好了。
启发：生产场景尽量不要给开发select意外的权限，对于网站的连接账号，不要授予select、insert、delete、update以外的权限。对别人的仁慈，就是对自己的岗位和公司最大的背叛。

第四章 DML语句之数据库操作语言

1. 插入数据

use oldboydrop table test;CREATE TABLE test (   id int(4) NOT NULL AUTO_INCREMENT,  name char(20) NOT NULL,  PRIMARY KEY (id)) ;desc test;insert into test(id,name) values(1,'oldboy');insert into test(name) values('oldgirl');

2. 插入两行数据

mysql> insert into test values(3,'inca');Query OK, 1 row affected (0.01 sec)

3. 插入多行数据

delete from test;INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');

4. 备份数据库

[root@mysql_52 ~]# mysqldump -B --compact oldboy>/opt/bak.sql

5. 更改表中的内容

mysql> update test set id=6 where name='kaka';mysql> update test set name='bingbing' where id='2';

6. 删除防止不加条件

mysql> mysql -U

7. 查看用户主机密码

mysql> select user,host,password from mysql.user;+---------+------------+-------------------------------------------+| user    | host       | password                                  |+---------+------------+-------------------------------------------+| root    | localhost  | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 || root    | 127.0.0.1  |                                           || oldboy  | localhost  | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 || oldgirl | 172.16.1.% | *2CADADD54086D5EB4C9F10E0430084D7F179885C |+---------+------------+-------------------------------------------+4 rows in set (0.00 sec)

8. 条件where、and、or

mysql> select user,host from mysql.user where user='root' and host='127.0.0.1';+------+-----------+| user | host      |+------+-----------+| root | 127.0.0.1 |+------+-----------+1 row in set (0.00 sec)

9. select查看表升降序

mysql> select id,name from test where id>2 order by id asc;+----+------+| id | name |+----+------+|  3 | inca ||  4 | zuma ||  5 | kaka |+----+------+3 rows in set (0.00 sec)mysql> select id,name from test where id>2 order by id desc;+----+------+| id | name |+----+------+|  5 | kaka ||  4 | zuma ||  3 | inca |+----+------+3 rows in set (0.00 sec)

10. select查看表limit

mysql> select id,name from test limit 1,3;+----+---------+| id | name    |+----+---------+|  2 | oldgirl ||  3 | inca    ||  4 | zuma    |+----+---------+3 rows in set (0.00 sec)mysql> select id,name from test limit 2,3;+----+------+| id | name |+----+------+|  3 | inca ||  4 | zuma ||  5 | kaka |+----+------+3 rows in set (0.00 sec)mysql> select id,name from test limit 2,1;+----+------+| id | name |+----+------+|  3 | inca |+----+------+1 row in set (0.00 sec)

11. 模糊搜索内容%匹配

mysql> select id,name from test where name like 'old%';+----+---------+| id | name    |+----+---------+|  1 | oldboy  ||  2 | oldgirl |+----+---------+2 rows in set (0.00 sec)mysql> select id,name from test where name like '%o%';

12. 删除表中的数据

mysql> delete from test where id=2;Query OK, 1 row affected (0.01 sec)mysql> select id,name from test;+----+--------+| id | name   |+----+--------+|  1 | oldboy ||  3 | inca   ||  4 | zuma   ||  5 | kaka   |+----+--------+4 rows in set (0.00 sec)mysql> delete from test where name='zuma';Query OK, 1 row affected (0.03 sec)mysql> select id,name from test;+----+--------+| id | name   |+----+--------+|  1 | oldboy ||  3 | inca   ||  5 | kaka   |+----+--------+3 rows in set (0.00 sec)

13. 伪删除可恢复

mysql> alter table test add  state tinyint(2) not null default 1;Query OK, 0 rows affected (0.08 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> mysql> desc test;+-------+------------+------+-----+---------+----------------+| Field | Type       | Null | Key | Default | Extra          |+-------+------------+------+-----+---------+----------------+| id    | int(4)     | NO   | PRI | NULL    | auto_increment || name  | char(20)   | NO   |     | NULL    |                || state | tinyint(2) | NO   |     | 1       |                |+-------+------------+------+-----+---------+----------------+3 rows in set (0.00 sec)mysql> select id,name from test;+----+---------+| id | name    |+----+---------+|  1 | oldboy  ||  2 | oldgirl ||  3 | inca    ||  4 | zuma    ||  5 | kaka    |+----+---------+5 rows in set (0.00 sec)mysql> update test set state=1;Query OK, 0 rows affected (0.01 sec)Rows matched: 5  Changed: 0  Warnings: 0mysql> select id,name from test;+----+---------+| id | name    |+----+---------+|  1 | oldboy  ||  2 | oldgirl ||  3 | inca    ||  4 | zuma    ||  5 | kaka    |+----+---------+5 rows in set (0.00 sec)mysql> select * from test;+----+---------+-------+| id | name    | state |+----+---------+-------+|  1 | oldboy  |     1 ||  2 | oldgirl |     1 ||  3 | inca    |     1 ||  4 | zuma    |     1 ||  5 | kaka    |     1 |+----+---------+-------+5 rows in set (0.00 sec)mysql> select * from test where id=1;+----+--------+-------+| id | name   | state |+----+--------+-------+|  1 | oldboy |     1 |+----+--------+-------+1 row in set (0.01 sec)mysql> select * from test where state=1;+----+---------+-------+| id | name    | state |+----+---------+-------+|  1 | oldboy  |     1 ||  2 | oldgirl |     1 ||  3 | inca    |     1 ||  4 | zuma    |     1 ||  5 | kaka    |     1 |+----+---------+-------+5 rows in set (0.00 sec)mysql> mysql> mysql> mysql> mysql> select * from test where state=1;+----+---------+-------+| id | name    | state |+----+---------+-------+|  1 | oldboy  |     1 ||  2 | oldgirl |     1 ||  3 | inca    |     1 ||  4 | zuma    |     1 ||  5 | kaka    |     1 |+----+---------+-------+5 rows in set (0.00 sec)mysql> mysql> mysql> mysql> mysql> select * from test where state=1;+----+---------+-------+| id | name    | state |+----+---------+-------+|  1 | oldboy  |     1 ||  2 | oldgirl |     1 ||  3 | inca    |     1 ||  4 | zuma    |     1 ||  5 | kaka    |     1 |+----+---------+-------+5 rows in set (0.00 sec)mysql> mysql> update test set state=0 where name='oldboy';Query OK, 1 row affected (0.03 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from test where state=1;+----+---------+-------+| id | name    | state |+----+---------+-------+|  2 | oldgirl |     1 ||  3 | inca    |     1 ||  4 | zuma    |     1 ||  5 | kaka    |     1 |+----+---------+-------+4 rows in set (0.00 sec)mysql> select * from test;+----+---------+-------+| id | name    | state |+----+---------+-------+|  1 | oldboy  |     0 ||  2 | oldgirl |     1 ||  3 | inca    |     1 ||  4 | zuma    |     1 ||  5 | kaka    |     1 |+----+---------+-------+5 rows in set (0.00 sec)

14. 物理删除（慎用）

mysql> truncate table test;Query OK, 0 rows affected (0.05 sec)

第五章 索引的管理

1. 各索引创建

mysql> desc test;mysql> alter table test add index index_name(name);mysql> desc test;mysql> alter table test drop index index_namemysql> desc test;mysql> create index index_name on test(name);mysql> alter table test add primary key(id);##<==创建逐渐索引：alter table student change id id##<==删除主键索引（主键列不能自增）：alter table student drop primary key;##<==创建普通索引：alter table student add index index_dept(dept);##<==根据列的前n个字符创建索引create index index_dept on student(dept(8));##<==根据多个列创建联合索引create index ind_name_dept on student(name.dept);##<==根据多个列的前n个字符创建联合索引create index ind_name_dept on student(name(8),dept(10));##<==创建唯一索引create unique index uni_ind_name on student(name);##<==删除普通索引与唯一索引alter table student drop index index_dept

2. 创建索引小结

1、 索引类似数据的目录，会加快查询数据的速度。
2、 要在表的列（字段）上创建索引。
3、 索引会加快查询速度，但是也会音响更新的数据，因为更新要维护索引数据。
4、 索引列并不是越多越好，要在频繁查询的表语句where后的条件列上创建索引。
5、 小表或重复值很多的列上可以不建索引，要在大表以及重复值少的条件列上创建索引。
6、 多个列联合索引有前缀生效特性。
7、 当字段内容前N个字符已经接近唯一时，可以对字段的前N个字符创建索引。
8、 索引从工作方式区分，有主键，唯一，普通索引。
9、 索引类型会有BTREE（默认）和hash（适合做缓存内存数据库）等。

第六章 数据库备份与恢复

1. 备份数据的意义
运维工作的核心简单概括就两件事：第一个是保护公司的数据，第二个是让网站能7*24小时提供服务。
虽然这两件事情很重要，相比较而言，丢失一部分数据和让7*24小时提供服务，哪个更重要呢？
这就要看公司和具体的业务了。
例如：对于类似百度搜索、腾讯这样的公司、丢失几万条记录（比如QQ聊天记录）。
对于绝大多数企业来讲，丢失数据就失去商机，失去产品，失去客户，甚至会造成公司倒闭，那么，在所有的数据中，哪些数据又是最核心的呢？这恐怕要属数据库中的数据了，当然，并不是其他数据不重要，只是这一部分更具代表性。既然数据库中的数据地位那么高，那数据库备份与恢复的重要性就不言而喻了。

2. mysqldump备份命令

##-A  --all-databasesmysqldump  -A >/opt/a.sql##-B增加建库（create）及（use）库的语句，可以直接接多个库名，同时备份多个库##-B 库1 库2-d仅表结构-t仅数据--compact减少无用数据输出-F, --flush-logs 刷新binlog日志逻辑备份:利用mysqldump命令备份数据的过程，实际上就是把数据（包括库表）从MySQL库里以SQL语句的形式直接输出或者生成备份文件的过程，这种备份成SQL语句的方式称为逻辑备份。

备份多个表:
mysqldump 库1 表1 表2 表3 >库1.sql
mysqldump 库2 表1 表2 表3 >库2.sql

2. mysqldump重要参数

3. 分库备份：for循环

mysqldump -uroot -p'oldboy123' -B oldboy ...mysqldump -uroot -p'oldboy123' -B oldboy_utf8 ...mysqldump -uroot -p'oldboy123' -B mysql .........分库备份for name in `mysql -e "show databases;"|sed 1d`do mysqldump -uroot -p'oldboy123' -B $namedone

第七章 binlog备份恢复

1. binlog是什么
binlog是一个二进制格式的文件，用于记录用户多数据库更新的SQL语句信息，例如更改数据库库表和更改表内容的SQL语句都会记录到binlog里，但是对库表等内容的查询则不会记录到日志中。

2. binlog对于备份的作用
当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的binlog文件里，这个文件就是上文说的binlog文件。
使用mysqldump备份时，一般是对某一时刻的数据进行全备，例如0点进行数据库备份。

3. 如何配置

[root@db02 ~]# mkdir /application/mysql/logs[root@db02 ~]# chown -R mysql.mysql /application/mysql/logs开启binlog编辑/etc/my.cnf[mysqld]log_bin = /application/mysql/logs/oldboy-bin重启:/etc/init.d/mysqld restart[root@db02 ~]# ll /application/mysql/logs/total 8-rw-rw---- 1 mysql mysql 120 Jun 21 12:04 oldboy-bin.000001-rw-rw---- 1 mysql mysql  42 Jun 21 12:04 oldboy-bin.index

4. -F刷新binlog

mysqldump -A -B -F >/opt/$(date +%F).sql[root@db02 ~]# ll /application/mysql/logs/-rw-rw---- 1 mysql mysql 168 Jun 21 12:06 oldboy-bin.000001-rw-rw---- 1 mysql mysql 168 Jun 21 12:06 oldboy-bin.000002-rw-rw---- 1 mysql mysql 210 Jun 21 12:07 oldboy-bin.index提示:每个库刷新一次.

第八章 innodb事务性引擎备份

1. innodb表特有的备份参数
当使用mysqldump的-single-transaction对innodb表进行备份时，会开启一个事物，并将整个备份过程放到一个事物里，以确保执行本次dump会话时，不会看到其他连接会话已经提交了的数据，即备份开始时刻的数据是什么样，备份出来就是什么样子，也相当于锁表后备份的数据，但整个参数是允许备份期间写入数据的，而不是在使用-x参数锁表后， 备份期间无法写入任何数据。

mysqldump -B --master-data=2 --single-transaction oldboy|gzip>/opt/all.sql.gz

--master-data={1|2} 告诉你备份后时刻的binlog位置2 注释1 非注释,要执行(主从复制)[root@db02 logs]# mysqldump  -B --master-data=2 oldboy >/opt/t.sql锁表:适合所有引擎(myisam,innodb)-x, --lock-all-tables -l, --lock-tablesmysqldump  -B -x oldboy >/opt/t.sqlm--->s(从库备份)基于事务引擎:不用锁表就可以获得一致性的备份.99% 使用innodb事务引擎.ACID四大特性中的隔离性--single-transaction压缩备份:mysqldump  -B --master-data=2 oldboy|gzip >/opt/t.sql.gz解压:zcat t.sql.gz >t1.sqlgzip -d t.sql.gz  #删压缩包innodb引擎的备份命令如下：mysqldump -A -B -R --triggers --master-data=2 --single-transaction |gzip >/opt/all.sql.gz适合多引擎混合（例如：myisam与innodb混合）的备份命令如下：mysqldump -A -B -R --triggers --master-data=2 |gzip >/opt/alL_$(date +%F).sql.gz

第九章 企业级数据备份案例

生产场景下，不同引擎的mysqldump备份命令
innodb引擎的备份命令如下：

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

mysqldump  -B --master-data=2 --single-transaction oldboy |gzip >/opt/all.sql.gz

--single-transaction是innodb引擎转悠备份参数，优势就是备份期间数据依然可以执行写操作。

#--master-data作用 适合多引擎混合（例如myisam与innodb混合）的备份命令如下: 2为注释 1为可用#这个备份的特点是会锁表，在备份期间会影响数据写入，使用--master-data会自动开启-x锁表参数功能。mysqldump -A -B --master-data=2|gzip >/opt/all_$(date +%F).sql.gz

第十章 数据库sleep线程过多案例

set global wait_timeout = 60;
set global interactive_timeout = 60;
然后在配置文件里修改：
[mysqld]
interactive_timeout = 120 #<==此参数设置后wait_timeout自动生效。
wait_timeout = 120

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

第十一章 MySQL重点参数

mysql -e "show variables like '%log_bin%';"mysql -e "show variables like '%timeout%';"mysql -e "show global status;"show processlist                             ##<==查看数据库正在执行的SQL语句，可能无法看全完整SQL语句show full processlise                        ##<==查看正在执行的完整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 global status;                          ##<==查看整个数据库运行状态信息，很重要，要分析并要做好监控show engine innodb status;                   ##<==显示innodb引擎的性能状态(早期版本show innnodb status

MySQL常用参数 说明 -u 指定数据库用户 -p 指定数据库密码 -S 指定数据库socket文件 -h 指定数据库主机，默认为localhost -P 指定数据库端口，默认为3306 -e 不登录数据库执行数据库命令 –default-character-set=name 指定字符集登录数据库或备份

第十二章 mysqladmin重点参数

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 -p'oldboy' shutdown           #<==关闭mysql服务，前文用过的。mysqladmin -uroot -p'oldboy' variables          #<==相当于show variables。

第十三章 MySQL的binlog日志

1. binlog作用
MySQL的binlog日志用来记录MySQL内部增删改等操作，也就是对MySQL数据库更新内容的记录（对数据库的改动），对数据库进行查询的语句（如以show、select开头的语句），不会被binlog日志记录。binlog日志的主要作用是数据库的主从复制以及数据灾难后的增量恢复。

[root@db02 logs]# mysqlbinlog -d oldboy oldboy-bin.000005 oldboy-bin.000006 -r bin.sql[root@db02 logs]# cat bin.sql

2. 截取部分binlog根据pos位置

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

3. 截取部分binlog根据时间

mysqlbinlog oldboy-bin.000009 --start-datetime='2014-10-16 17:14:15' --stop-datetime='2014-10-16 17:15:15' -r time.sqlmysqlbinlog oldboy-bin.000009 --start-datetime='2014-10-16 17:14:15'  -r time.sqlmysqlbinlog oldboy-bin.000009  --stop-datetime='2014-10-16 17:15:15' -r time.sql

第十四章 MySQL企业级数据库备份实践

1. 数据库管理员工作2大核心
能够让数据安全得到保护，所谓的数据安全，最容易被误以为只有备份和恢复，其实还包含数据被脱库、泄密等的方面，本章主要讲解备份和恢复。
能7*24小时提供服务，数据库具备7*24小时的服务能力，数数据库管理员的重要职责，在后文会为大家讲解MySQL的主从复制集群、MySQL Mha+Keepalived集群、读写分离等综合保障数据库具备7*24的服务能力的措施。

1. 全量备份
全量数据就是数据库中的所有数据（或某一库的全部数据）；全量备份就是把数据库中所有的数据进行备份。
以InnoDB引擎数据库为例：

#备份数据库中所有库的所有数据命令为：mysqldump -B --master-data=2 --single-transaction -A |gzip >/opt/all.sql.gz#备份oldboy一个库中所有数据的命令为：mysqldump -B --master-data=2 --single-transaction oldboy|gzip >/opt/oldboy.sql.gz

2. 增量备份
增量数据就是指上一次全量备份数据之后到下一次全备之前数据库所更新的数据。在使用mysqldump命令做全备时，增量数据就是MySQL的binlog日志，因此，对binlog日志的备份在此处就可以称为增量备份，当然，有些工具本身就可以实现全量以及增量数据的备份，例xtrabackup工具。

3. 逻辑备份
逻辑备份的有点为操作简单、方便、可靠，并且备份的数据可以跨平台、跨版本、甚至跨软件、跨操作系统， 还可以实现分库分表备份；逻辑备份也有一定的缺点，例如：备份速度比物理备份慢、恢复的效率也不是特别高。

逻辑备份常用工具
mysqldump是MySQL官方自带的最常用逻辑备份工具，还能实现分表分库备份，也是本章重点讲解的备份工具。除此之外，还有一个mydumper工具，它是一个在GPL许可下发布的高性能MySQL备份和恢复工具集。

适用于数据量不是特别大的场景，例如，打包前不大于30GB的数据库数据，30GB的值主要是考虑备份效率的问题，以及管理员使用复杂度的平衡。
不过，在跨版本、跨软件升级或迁移数据的时候，此时物理备份一般就不能使用了。

第十五章 物理备份方式

1. 冷备
MySQL的物理备份方法之一是使用cp、rsync、tar、scp等复制工具把MySQL数据文件复制成多份，由于在备份期间数据仍然有写入操作，所以直接复制的方式备份会引起数据丢失。另外在恢复数据库时，对新数据库的路径、配置也有要求，一般要和原库的配置保持一致（版本、路径、配置尽可能一样）。

为了确保备份期间的数据一致性，可以选择人工停库或者锁库后在进行物理复制，而这在生产环境中一般是不允许的，除非是可以申请停机或锁表时间，所以使用传统Linux命令拷贝工具还是比较粗的冷备份方式，应避免使用，在后文还会提及根据MySQL主从复制利用从库进行冷备的策略。

一般在进行大规模数据库迁移时，先停库，然后物理迁移，是很有效率的方案。

2. 温备
只可读不可写

3. 热备
除了在Linux命令行通过命令直接复制MySQL数据文件外，还有一些其它第三方的开源或商业物理热备份工具。如xtrabackup。使用这个工具可以实现全量即增量备份。

4. 物理备份的特点
物理备份的优缺点正好和逻辑备份相反，因此在企业里应根据需求，互补使用。
优点：速度快，效率高。
缺点：不容易跨平台、跨版本、跨软件、跨操作系统，可以实现分库分表备份、但恢复麻烦很多，软件的使用也比较复杂一些。

5. 物理备份常用工具和方法
Linux下冷备份工具为cp、tar，备份时需要锁表或者停库确保数据一致性；开源的热备份（基于innodb）工具则是xtrabackup。

6. 物理备份企业应用场景
数据库总数据量超过30GB的，可使用xtrabackup热备工具进行备份，以提升效率。
可以选择在数据库的从库上进行备份，备份时停止SQL线程应用数据到数据库，然后通过cp或tar打包备份，这也是不错的冷备方案，不影响数据库的服务。

7. 逻辑备份和物理备份区别

区别区别区别区别区别 逻辑备份 物理备份 备份原理 以SQL语句形式存储 直接复制磁盘物理文件或其他非SQL语句方式的备份 相关命令 mysqldump、mysql、mysqlbinlog cp、rsync、tar、scp、xtrabackupp（热备） 备份要求 需要锁表但不需要停库。锁表会影响数据库更新，innodb引擎可以不锁表，而是采用事物备份方案。 冷备需要锁表或停机，热备不需要锁表（仅事物引擎，例如innodb）或停机 配置特点 恢复时与系统版本，库的配置甚至版本无关。 物理复制需要系统、配置、版本尽可能的一致。 性能特点 速度慢 速度快 方便性考虑 安全、易掌握、容易控制，一般不会丢失数据 冷备简单，但应用场景少，热备工具操作复杂一些，较难掌握

8. 全备的数据什么时候可以派上用场

1. 迁移或者升级数据库时。
2. 增加从库的时候。
3. 人为执行DDL、DML语句破坏数据库数据时（此时主从库没办法了，所有库都会执行）。
4. 跨机房灾备时，此时需要将全备份拷到异地。
5. 若是因为硬件或删除物理文件导致数据库故障，就不需要用备份数据恢复了，可以直接把主库关闭，在从库上配置好VIP等配置后，启动从库提供服务即可。

9. 企业里MySQL备份策略选择
大多数中小企业的数据库环境都为一主多从，因此，可采取在一个从库服务器上专门做全量以及增量备份（需要开启从库记录binlog日志功能），至于备份方法，用mysqldump、xtrabackup均可。

第十六章 binlog逻辑备份恢复企业案例实操

具备全量备份（mysqldump）
除全量备份以外，还有全量备份之后产生的所有binlog增量日志。

1. 准备环境

mysql> drop database oldboy;mysql> create database oldboy;mysql> use oldboy;

2. 创建表

mysql> CREATE TABLE `test` (   `id` int(4) NOT NULL AUTO_INCREMENT,   `name` char(20) NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

3. 表中插入内容

mysql> insert into `test` values (1,'oldboy'),(2,'olgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');mysql> select * from test;+----+---------+| id | name    |+----+---------+|  1 | oldboy  ||  2 | oldgirl ||  3 | inca    ||  4 | zuma    ||  5 | kaka    |+----+---------+

4. 创建备份目录

[root@mysql_52 logs]# mkdir /data/backup -p[root@mysql_52 logs]# date -s "2017/06/22"

5. 全备份

[root@mysql_52 backup]# mysqldump -B --master-data=2 --single-transaction oldboy|gzip>/data/backup/oldboy_`date +%F`.sql.gz

6. 模拟增量

[root@mysql_52 backup]# mysql -e "use oldboy;insert into test values(6,'bingbing');"[root@mysql_52 backup]# mysql -e "use oldboy;insert into test values(7,'xiaoting');"[root@mysql_52 backup]# mysql -e "use oldboy;select * from test;"+----+----------+| id | name     |+----+----------+|  1 | oldboy   ||  2 | olgirl   ||  3 | inca     ||  4 | zuma     ||  5 | kaka     ||  6 | bingbing ||  7 | xiaoting |+----+----------+[root@mysql_52 backup]# date -s '2017/06/22 11:40'[root@mysql_52 backup]# mysql -e "drop database oldboy;"

7. 开始恢复准备 采用iptable防火墙屏蔽所有应用程序的写入

 ##<==非172.16.1.51禁止访问数据库3306端口[root@mysql_52 backup]# iptables -I INPUT -p tcp --dport 3306 ! -s 172.16.1.51 -j DROP

##<==非172.16.1.51禁止访问数据库3306端口[root@mysql_52 backup]# iptables -I INPUT -p tcp --dport 3306 ! -s 172.16.1.51 -j DROPcp -a /application/mysql/logs/oldboy-bin.* /data/backup/[root@mysql_52 backup]# zcat oldboy_2017-06-22.sql.gz >oldboy_2017-06-22.sql[root@mysql_52 backup]# sed -n '22p' oldboy_2017-06-22.sql[root@mysql_52 backup]# mysqlbinlog  -d oldboy --start-position=1459 oldboy-bin.000001 -r bin.sql[root@mysql_52 backup]# grep -i drop bin.sql drop database oldboy[root@mysql_52 backup]# sed -i '/drop/d' bin.sql[root@mysql_52 backup]# mysql <oldboy_2017-06-22.sql[root@mysql_52 backup]# mysql -e "show databases;"+--------------------+| Database           |+--------------------+| information_schema || chaochao           || mysql              || oldboy             || performance_schema || qqqq               || test               |+--------------------+[root@mysql_52 backup]# mysql -e "use oldboy;select * from test;"+----+---------+| id | name    |+----+---------+|  1 | oldboy  ||  2 | oldgirl ||  3 | inca    ||  4 | zuma    ||  5 | kaka    |+----+---------+[root@mysql_52 backup]# mysql oldboy <bin.sql[root@mysql_52 backup]# mysql -e "use oldboy;select * from test;"+----+----------+| id | name     |+----+----------+|  1 | oldboy   ||  2 | oldgirl  ||  3 | inca     ||  4 | zuma     ||  5 | kaka     ||  6 | bingbing ||  7 | xiaoting |+----+----------+##恢复完毕##调整iptables允许用户访问.[root@mysql_52 backup]# iptables -D INPUT 1[root@mysql_52 backup]# iptables -nL##多个binlog问题==========================================================================mysqlbinlog -d oldboy --start-position=339 oldboy-bin.000009 oldboy-bin.0000010 -r bin1.sqlmysql oldboy <bin1.sql==========================================================================

第十七章 Xtrabackup物理备份实操

1. Xtrabackup介绍
Xtrabackup是Percona公司专门针对MySQL数据库开发的一款开源免费的物理备份（热备）工具。可以对InnoDB和XtraDB等事物引擎的数据库实现非阻塞（即不锁表）方式的备份，也可以针对MyISAM等非事物引擎实现锁表方式备份。

2. Xtrabackup的主要特点

1. 直接拷贝物理文件，备份和恢复数据的速度非常快、安全可靠。
2. 在备份期间执行的事务不会间断，备份InnoDB数据不影响业务。
3. 备份期间不增加太多数据库的性能压力。
4. 支持对备份的数据自动校验。
5. 支持全量、增量、压缩备份及流备份。
6. 支持在线迁移表以及快速创建新的从库。
7. 支持几乎所有版本的MySQL和MariaDB。

3. MySQL数据文件扩展名说明

文件扩展名 文件作用说明 .idb 以独立表空间存储的InnoDB引擎类型的数据文件扩展名 .ibdata文件 以共享表空间存储的InnoDB引擎类型的数据文件扩展名 .frm文件 存放与表相关的元数据（meta）信息及表结构的定义信息 .MYD文件 存放MyISAM引擎表的数据文件扩展名 .MYI文件 存放MyISAM引擎表的索引信息文件扩展名

4. 事务性引擎的ACID特性
在MySQL中，InnoDB和MariaDB中的XtraDB都是事务性引擎，事务性引擎的共同特征是具备事物的4个特性，这4个特性分别是：原子性、一致性、隔离性、持久性，又称ACID特性。

ACID特性 明 原子性（Atomicity） 事务的所有SQL语句操作，要么全部成功，要么全部失败。 一致性（Consistency） 事务开始之前和结束之后，数据库保证数据的完整性不被破坏 隔离性（Isolation） 当多个事务并发访问同一个数据源时，数据库能够保持每个访问的事务之间是隔离的，互不影响的。 持久性（Durability） 事务处理完成之后，事务所做的更改都是持久化存储，不会丢失数据

5. Xtrabackup恢复工作原理过程
Percona Xtrabanckup软件是基于innoDB等事务引擎自带的redo日志和undo日志功能来保持备份和恢复前后数据一致性的，从而确保数据库的数据安全可靠。在innoDB引擎中存在一个redo日志（事务日志）功能。redo日志文件会存储每一个innoDB表中数据被修改的记录。当innoDB数据库启动时，会检查数据文件和redo日志文件，将已经提交到事务日志（redo日志文件）中的信息应用（提交）到数据文件并保存，然后根据undo日志信息将修改过但没有提交的数据记录进行回滚（不提交到数据文件）。

6. Xtrabackup备份原理过程说明
当执行Xtrabackup程序开始备份时，Xtrabackup首先会记录当前redo日志的位置（即对应的LSN号），同时会在后台启动一个进程持续监视redo日志文件的变化，并将变化的信息都记录到Xtrabackup_logfile中，之后会针对所有的innodb数据文件进行备份（复制），待备份innodb数据文件完成后，则执行“flush tables with read lock”命令对整个数据库锁表，然后备份（复制）MyISAM等非事务引擎的数据文件。待全部（包括InnoDB、MyISAM数据文件和Redo日志数据记录）都备份完毕后，获取Binlog二进制日志位置点信息，最后执行unlock tables解锁命令，恢复整个数据库可读写状态。

6. 安装Xtrabackup

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQLwget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpmyum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpmmkdir -p /application/mysql/logs/chown -R mysql.mysql /application/mysql/logs/

7. 配置my.cnf

[client]          user=root         password=oldboy123[mysqld]basedir = /application/mysql/   datadir = /application/mysql/data/###########binlog############log_bin = /application/mysql/logs/oldboy-binexpire_logs_days = 7             [mysqld_safe]log-error = /application/mysql/logs/oldboy.errsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES[root@mysql_52 ~]# /etc/init.d/mysqld restart

8. Xtrabackup命令
Xtrabackup命令是专门用于对innoDB和XtraDB等事务引擎的数据库热备份工具，不能用于备份MyISAM等其他类型的引擎数据，主要特点是备份数据时完全不用锁表。

9. innobackupex命令
innobackupex命令时将上述Xtrabackup命令使用perl脚本进行二次封装的工具，除了可以用于innoDB和XtraDB等引擎外，还可以备份MyISAM及多种引擎混合使用的场景，主要特点是备份事务引擎数据不用锁表，可以备份非事务引擎数据，但要锁表。

鉴于国内运维和DBA人员在企业里常使用功能更多的innobackupex命令，来进行备份和恢复，因此本章页也以此命令为例讲解Xtrabackup软件的原理及备份恢复应用实践。

10. 全备备份操作

mkdir /server/backup -pinnobackupex --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp /server/backup/full

11. 恢复数据操作

恢复数据innobackupex --apply-log --use-memory=32M /server/backup/full/停库:/etc/init.d/mysqld stoplsof -i :3306破坏数据：cd /application/mysql/mv data /opt/恢复cp -a /server/backup/full/ /application/mysql/datachown -R mysql.mysql /application/mysql/data启动：/etc/init.d/mysqld startmysql -e "select * from oldboy.test"成功。

12. innobackupex命令参数

13. 增量备份操作
中小企业MySQL Xtrabackup物理增量恢复案例实战
条件:
 1.具备全量备份（xtrabackup备份的全备）。
 2.具备全量之后的所有增量备份（xtrabckup备份的增量）。
 3.具备最后一次增量备份以后的所有MySQL的Binlog增量日志。

【1】创建环境use oldboydelete from test;insert into test values(1,'full01');insert into test values(2,'full02');insert into test values(3,'full03');insert into test values(4,'full04');insert into test values(5,'full05');检查:select * from test;【2】周一0点全量备份date -s "2017/06/26"innobackupex --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp /server/backup/new_base_full【3】数据库继续更新mysql -e "use oldboy;insert into test values(6,'new_inc_one_1');"mysql -e "use oldboy;insert into test values(7,'new_inc_one_2');"mysql -e "select * from oldboy.test;"【4】周二0点增量备份第一次增量备份date -s "2017/06/27"innobackupex --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp --incremental-basedir=/server/backup/new_base_full --incremental /server/backup/new_one_inc【5】数据库继续更新mysql -e "use oldboy;insert into test values(8,'binlog_data_1');"mysql -e "use oldboy;insert into test values(9,'binlog_data_2');"mysql -e "select * from oldboy.test;"【6】周三0点增量备份第二次增量备份date -s "2017/06/28"innobackupex --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp --incremental-basedir=/server/backup/new_one_inc --incremental /server/backup/new_two_inc【7】数据库继续更新mysql -e "use oldboy;insert into test values(10,'realbinlog_data_3');"mysql -e "use oldboy;insert into test values(11,'realbinlog_data_4');"mysql -e "select * from oldboy.test;"【8】周三上午10点故障##<==例如执行误删除命令【9】开始恢复##<==建议停库：最好用iptables【10】关闭访问3306iptables -I INPUT -p tcp --dport 3306 -j DROP/etc/init.d/mysqld stop【11】合并数据文件innobackupex --apply-log --use-memory=32M --redo-only /server/backup/new_base_full/innobackupex --apply-log --use-memory=32M --redo-only --incremental-dir=/server/backup/new_one_inc /server/backup/new_base_full/innobackupex --apply-log --use-memory=32M --incremental-dir=/server/backup/new_two_inc /server/backup/new_base_full/##<==数据文件准备完毕.【12】开始正式恢复cd /application/mysqlmv data data.ori\cp -a /server/backup/new_base_full datachown -R mysql.mysql data================偷偷的看一下======================[root@db02 mysql]# /etc/init.d/mysqld startStarting MySQL.... SUCCESS! [root@db02 mysql]# mysql -e "select * from oldboy.test;"+----+---------------+| id | name          |+----+---------------+|  1 | full01        ||  2 | full02        ||  3 | full03        ||  4 | full04        ||  5 | full05        ||  6 | new_inc_one_1 ||  7 | new_inc_one_2 ||  8 | binlog_data_1 ||  9 | binlog_data_2 |+----+---------------+[root@db02 mysql]# /etc/init.d/mysqld stopShutting down MySQL.. SUCCESS! ==================================================【13】查看binlog位置点[root@db02 mysql]# cat /server/backup/new_two_inc/xtrabackup_binlog_infooldboy-bin.000011   2399【14】解析binlogcd /application/mysql/logs/mysqlbinlog -d oldboy --start-position=2399 oldboy-bin.000011 -r bin1.sqlmysqlbinlog -d oldboy oldboy-bin.000012 >>bin1.sql(根据你的情况调整)本次不用操作,但工作中可能有会有很多个binlog文件.#mysqlbinlog -d oldboy  oldboy-bin.000012 oldboy-bin.000013 oldboy-bin.000014 >>bin2.sql【15】删除错误的update一行grep update bin1.sql sed -i "/update test set name='oldboy'/d" bin1.sql grep update bin1.sql 开始恢复iptables -I INPUT -p tcp --dport 3306 -j DROP/etc/init.d/mysqld start【16】导入binlog.sql语句mysql oldboy <bin1.sql 检查:[root@db02 logs]# mysql -e "select * from oldboy.test;"+----+-------------------+| id | name              |+----+-------------------+|  1 | full01            ||  2 | full02            ||  3 | full03            ||  4 | full04            ||  5 | full05            ||  6 | new_inc_one_1     ||  7 | new_inc_one_2     ||  8 | binlog_data_1     ||  9 | binlog_data_2     || 10 | realbinlog_data_3 || 11 | realbinlog_data_4 |+----+-------------------+【17】调整防火墙规则iptables -D INPUT -p tcp --dport 3306 -j DROP10.和开发/运营交流,检查数据恢复情况.   专业故障恢复报告\发邮件\当面分享.over.