EXPLAIN命令是查看优化器如何决定执行查询的主要方法。可以帮助我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

执行计划包含的信息：

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

id

包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下，id值越大，优先级越高，越先执行

select_type

每个select子句的类型（简单OR复杂）

SIMPLE：查询中不包含子查询或者UNION
PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
DERIVED：在FROM列表中包含的子查询被标记为：DERIVED（衍生）用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的
UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT：从UNION表获取结果的SELECT被标记为：UNION RESULT

SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。DEPENDENT意味着select依赖于外层查询中发现的数据。UNCACHEABLE意味着select中的某些 特性阻止结果被缓存于一个item_cache中。

type

表示MySQL在表中找到所需行的方式：ALL, index, range, ref, eq_ref, const, system, NULL 从左到右，性能从最差到最好。

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index：Full Index Scan，index与ALL区别为index类型只遍历索引树
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<, >查询。当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。
ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行
eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system。
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度）

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra

包含不适合在其他列中显示但十分重要的额外信息

Using index

该值表示相应的select操作中使用了覆盖索引（Covering Index）

覆盖索引（Covering Index）MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件包含所有满足查询需要的数据的索引称为覆盖索引（Covering Index）

注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降

Using where

表示mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where字句的查询都会显示”Using where”。有时”Using where”的出现就是一个暗示：查询可受益与不同的索引。

Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。

这个值表示使用了内部临时(基于内存的)表。一个查询可能用到多个临时表。有很多原因都会导致MySQL在执行查询期间创建临时表。两个常见的原因是在来自不同表的上使用了DISTINCT,或者使用了不同的ORDER BY和GROUP BY列。可以强制指定一个临时表使用基于磁盘的MyISAM存储引擎。这样做的原因主要有两个：

内部临时表占用的空间超过min(tmp_table_size，max_heap_table_size)系统变量的限制
使用了TEXT/BLOB 列

Using filesort

MySQL中无法利用索引完成的排序操作称为”文件排序”

Using join buffer

改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where

这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away

这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行.

Index merges

当MySQL 决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。

Using sort_union(...)
Using union(...)
Using intersect(...)

总结：

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

一、Mysql-Proxy原理

Mysql-Proxy是一个处于你的client端和Mysql Server端之间的一个简单程序，它可以监测、分析和改变他们的通信。它使用灵活没有限制，常见的用途包括：负载平衡，故障、查询分析，查询过滤和修改等等。它一个中间层代理，简单的说，Mysql-Proxy就是一个连接池，负责将前台应用的请求转发给后台数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多个proxy的连接参数即可。<摘自百度百科>

二、MySQL-Proxy安装配置

实现读写分离是有lua脚本实现的，现在mysql-proxy里面已经集成，无需再安装lua。

1、解压到/usr/local/mysql-proxy，开始配置mysql-proxy

创建脚本存放目录
[root@localhost mysql-proxy]# mkdir lua
[root@localhost mysql-proxy]# mkdir logs

2、复制读写分离配置文件和管理脚本。

[root@localhost mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua ./lua
[root@localhost mysql-proxy]# cp share/doc/mysql-proxy/admin-sql.lua ./lua

3、创建配置文件

[root@localhost mysql-proxy]# vim /etc/mysql-proxy.cnf
配置内容如下

[mysql-proxy]
daemon=true#守护进程方式
user=root#运行mysql-proxy用户
admin-username=mysqlproxy #主从mysql共有的用户
admin-password=123456 #用户的密码
proxy-address=192.168.1.105:4040#mysql-proxy运行ip和端口，不加端口，默认4040
proxy-read-only-backend-addresses=192.168.1.104#指定从slave读取数据
proxy-backend-addresses=192.168.1.103#指定主master写入数据
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua#指定读写分离配置文件位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua#指定管理脚本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log#日志位置
log-level=info#定义log日志级别，由高到低分别有(error|warning|info|message|debug)
keepalive=true#mysql-proxy崩溃时，尝试重启

注意配置的时候不能有注释，坑爹的。
由于安全要求，必须将配置文件权限设为660（创建人可读写，同组人可读），否则不允许启动。

4、配置读写分离

[root@localhost mysql-proxy]# vim lua/rw-splitting.lua

在这段代码做修改如下

1
2
3
4
5
6
7
8
9

-- connection pool
if not proxy.global.config.rwsplit then
       proxy.global.config.rwsplit = {
               min_idle_connections = 1，--4,  --默认是超过4个连接数才开始读写分离
               max_idle_connections = 1，--8,默认为8，改为1

               is_debug = false
       }
end

三、启动测试

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
netstat -tupln | grep 4040 #已经启动
killall -9 mysql-proxy #关闭mysql-proxy使用

在配置文件中配置访问主从的用户，主从数据库中还没有，在主库创建用户名密码并赋予权限，从库也会相应创建。

mysql> grant all privileges on *.* to 'mysqlproxy'@'192.168.1.105' identified by '123456' with grant option;
mysql> flush privileges;
mysql> select user,host from user;
| mysqlproxy | 192.168.1.103         |
| slave      | 192.168.1.104         |

使用客户端连接MySQL-Proxy，进行测试，这里使用win端

C:\Users\Administrator>net start mysql
MySQL 服务正在启动 ........
MySQL 服务已经启动成功。
C:\Users\Administrator> mysql -u mysqlproxy -p123456 -h 192.168.1.105 -P 4040
mysql>

使用两个远程客户端连接MySQL-Proxy。

在主节点查看3306端口，总的有三个客户端连接主库：分别是slave，两个代理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

[root@localhost mysql-proxy]# lsof -i :3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  16087 mysql   15u  IPv6 105964      0t0  TCP *:mysql (LISTEN)
mysqld  16087 mysql   42u  IPv6 106004      0t0  TCP 192.168.1.103:mysql->192.168.1.104:56687 (ESTABLISHED)
mysqld  16087 mysql   44u  IPv6 118888      0t0  TCP 192.168.1.103:mysql->192.168.1.105:60578 (ESTABLISHED)
mysqld  16087 mysql   76u  IPv6 119880      0t0  TCP 192.168.1.103:mysql->192.168.1.105:60592 (ESTABLISHED)
       在slave节点查看3306端口，总的有二个客户端连接主库：分别是代理和主节点
        [root@localhost ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  7775 mysql   13u  IPv6  25595      0t0  TCP *:mysql (LISTEN)
mysqld  7775 mysql   34u  IPv4  49633      0t0  TCP 192.168.1.104:56687->192.168.1.103:mysql (ESTABLISHED)
mysqld  7775 mysql   38u  IPv6  55458      0t0  TCP 192.168.1.104:mysql->192.168.1.105:50689 (ESTABLISHED)
       在代理节点查看4040端口，总的有二个客户端连接主库：分别是远程连接代理的两个客户端
[root@localhost lua]# lsof -i :4040
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 3853 root   10u  IPv4  31717      0t0  TCP 192.168.1.105:yo-main (LISTEN)
mysql-pro 3853 root   11u  IPv4  31753      0t0  TCP 192.168.1.105:yo-main->192.168.1.1:56767 (ESTABLISHED)
mysql-pro 3853 root   13u  IPv4  33954      0t0  TCP 192.168.1.105:yo-main->192.168.1.1:57368 (ESTABLISHED)

在master和slave上开启日志实时监测sql执行测试。

查看日志配置是否打开

mysql> SHOW VARIABLES LIKE "general_log%"; SET GLOBAL general_log = 'ON';
+------------------+--------------------------------------------+
| Variable_name    | Value                                      |
+------------------+--------------------------------------------+
| general_log      | OFF                                        |
| general_log_file | /usr/local/mysql-5.6.31/data/localhost.log |
+------------------+--------------------------------------------+

打开日志

SET GLOBAL general_log = 'ON';

查看执行的sql日志
[root@localhost mysql-proxy]# tail -f /usr/local/mysql-5.6.31/data/localhost.log
/usr/local/mysql-5.6.31/bin/mysqld, Version: 5.6.31-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock

关闭从机上的slave功能

当远程执行mysql> select * from demo.user;时：
master  日志无变化
slave  变化：
190226  3:18:47           12 Query        select * from demo.user
当执行   insert into demo.user values(default,'欧阳询')
        master 日志变化：7 Query        insert into demo.user values(default,'欧阳询')
        slave无变化  并且在slave上也查不到这一条数据。
注意：测试读写分离需要在slave上关闭slave功能，如果开启的话slave日志会增加一条信息COMMIT /* implicit, from Xid_log_event */这个因为是主从开启的，主库新增，从库也跟着变化。

主从配置

MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。

MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以”事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，根据变化把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的”事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

实现MySQL主从复制需要进行的配置

主服务器：

开启二进制日志log_bin 不是执行日志/状态日志，而是操作命令日志。一般配置主从才需要配置
配置唯一的server-id
获得master二进制日志文件名及位置
创建一个用于slave和master通信的用户账号

从服务器：

配置唯一的server-id
使用master分配的用户账号读取master二进制日志
启用slave服务

主从配置开始，环境如下：

2台Linux虚拟机，MySQL5.6，确保主从数据库一模一样。
192.168.1.103 master
192.168.1.104 slave

1、master库修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

修改配置文件my.cnf，插入配置
[root@localhost ~]# vim /etc/my.cnf
       [mysqld]
       log_bin=master_bin #开启二进制日志
       server_id=1 #设置server_id
重启MySQL，创建用于slave连接master的用户账号
[root@localhost ~]# service mysql start
Starting MySQL. SUCCESS!
[root@localhost ~]# mysql -u root -p123456
创建用户
mysql> grant all privileges on *.* to 'slave'@'192.168.1.104' identified by 'slave' with grant option;
刷新权限
mysql> flush privileges;
mysql> select user,host from user;
...
| slave | 192.168.1.104         |

查看master状态
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master_bin.000001 |      425 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

2、从库修改

同样修改my.cnf，只需要添加添加server_id

server_id=2 #设置server-id，必须唯一

3、关于auto.cnf

MySQL数据目录中通常存在一个名为auto.cnf文件，存储了server-uuid的值，MySQL启动时，会自动从data_dir/auto.cnf 文件中获取server-uuid值，并将这个值存储在全局变量server_uuid中。如果这个值或者这个文件不存在，那么将会生成一个新的uuid值，并将这个值保存在auto.cnf文件中。server-uuid与server-id一样，用于标识MySQL实例在集群中的唯一性，这两个参数在主从复制中具有重要作用，默认情况下，如果主、从库的server-uuid或者server-id的值一样，将会导致主从复制报错中断。
由于我的虚拟机是安装好MySQL后直接克隆的，所以他们的这个uuid值是一样的，所以需要做一步，把其中一个值改了。如如果是分别单独安装的MySQL，则不用改。
[root@localhost ~]# cat /usr/local/mysql-5.6.31/data/auto.cnf
 [auto]
 server-uuid=6c73ecbe-3138-11e9-a3d0-000c29e525bc

4、从库配置slave

分别启动主从的MySQL，MySQL的slave会默认启动，但是空配置。
在从库上配置slave
配置的依据是master的信息，ip为master的IP，用户名密码是master提供给slave访问的用户密码，日志文件是在master中查看主库保存的命令的日志信息文件。
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.103',MASTER_USER='slave',MASTER_PASSWORD='slave',MASTER_LOG_FILE='master_bin.000001';
mysql> start slave;

查看slave

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

mysql> show slave status\G;
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: 192.168.1.103
                 Master_User: slave
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: master_bin.000001
         Read_Master_Log_Pos: 120
              Relay_Log_File: localhost-relay-bin.000001
               Relay_Log_Pos: 284
       Relay_Master_Log_File: master_bin.000001
           Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
             Replicate_Do_DB:
         Replicate_Ignore_DB:
          Replicate_Do_Table:
      Replicate_Ignore_Table:
     Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
                  Last_Errno: 0        最后一次错误的信息描述
                  Last_Error:
                Skip_Counter: 0         中断了多少次
         Exec_Master_Log_Pos: 120
             Relay_Log_Space: 930
             Until_Condition: None
              Until_Log_File:
               Until_Log_Pos: 0
          Master_SSL_Allowed: No
          Master_SSL_CA_File:
          Master_SSL_CA_Path:
             Master_SSL_Cert:
           Master_SSL_Cipher:
              Master_SSL_Key:
       Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
              Last_IO_Errno: 0 最后一次错误的IO请求编号
               Last_IO_Error:
              Last_SQL_Errno: 0 最后一次错误执行sql命令编号
              Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
            Master_Server_Id: 1
                 Master_UUID: 6c73ecbe-3138-11e9-a3d0-000c29e525bc
            Master_Info_File: /usr/local/mysql-5.6.31/data/master.info
                   SQL_Delay: 0
         SQL_Remaining_Delay: NULL
     Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
          Master_Retry_Count: 86400
                 Master_Bind:
     Last_IO_Error_Timestamp:
    Last_SQL_Error_Timestamp:
              Master_SSL_Crl:
          Master_SSL_Crlpath:
          Retrieved_Gtid_Set:
           Executed_Gtid_Set:
               Auto_Position: 0
1 row in set (0.00 sec)

5、测试

在主库中创建demo库，并且创建一个表，并添加一条数据，在从库中查看有没有对应有相应数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use demo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

mysql> select * from user;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张三丰    |
|  2 | 江小白    |
+----+-----------+
2 rows in set (0.01 sec)

mysql>
执行，没问题，当在主库删除一条数据，从库中也对应消失一条数据。
主库执行：
mysql> select * from user;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张三丰    |
|  2 | 江小白    |
+----+-----------+
2 rows in set (0.03 sec)
mysql> delete from user where id=1;
Query OK, 1 row affected (0.14 sec)
从库执行：
mysql> mysql> select * from user;
+----+-----------+
| id | name      |
+----+-----------+
|  2 | 江小白    |
+----+-----------+
1 row in set (0.00 sec)

完全OK！

还可以用到的其他相关参数：

master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：

1
2
3
4
5
6

# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  
# 只同步哪些数据库，除此之外，其他不同步  
binlog-do-db = game

MySQL主从之change master语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

CHANGE MASTER TO option [, option] ...  
 
option:  
   MASTER_BIND = 'interface_name'  
 | MASTER_HOST = 'host_name'  
 | MASTER_USER = 'user_name'  
 | MASTER_PASSWORD = 'password'  
 | MASTER_PORT = port_num  
 | MASTER_CONNECT_RETRY = interval  
 | MASTER_RETRY_COUNT = count  
 | MASTER_DELAY = interval  
 | MASTER_HEARTBEAT_PERIOD = interval  
 | MASTER_LOG_FILE = 'master_log_name'  
 | MASTER_LOG_POS = master_log_pos  
 | MASTER_AUTO_POSITION = {0|1}  
 | RELAY_LOG_FILE = 'relay_log_name'  
 | RELAY_LOG_POS = relay_log_pos  
 | MASTER_SSL = {0|1}  
 | MASTER_SSL_CA = 'ca_file_name'  
 | MASTER_SSL_CAPATH = 'ca_directory_name'  
 | MASTER_SSL_CERT = 'cert_file_name'  
 | MASTER_SSL_CRL = 'crl_file_name'  
 | MASTER_SSL_CRLPATH = 'crl_directory_name'  
 | MASTER_SSL_KEY = 'key_file_name'  
 | MASTER_SSL_CIPHER = 'cipher_list'  
 | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}  
 | IGNORE_SERVER_IDS = (server_id_list)  
 
server_id_list:  
   [server_id [, server_id] ... ]

执行change master语句前如果从机上slave io及sql线程已经启动，需要先停止（执行stop slave）。change master to后面不指定某个参数的话，该参数保留原值或默认值。

MySQL同步故障：” Slave_SQL_Running:No” 两种解决办法

解决办法一

Slave_SQL_Running: No
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后，事务回滚造成的.
一般是事务回滚造成的：
解决办法：
mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

解决办法二

首先停掉Slave服务：slave stop
到主服务器上查看主机状态：记录File和Position对应的值
进入master
mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhost-bin.000094 | 33622483 |              |                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


然后到slave服务器上执行手动同步：
mysql> change master to
......
> master_log_file=localhost-bin.000094',
> master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> start slave ;
1 row in set (0.00 sec)
手动同步需要停止master的写操作！

网络通信

网络上的两个程序通过一个双向的通信连接实现数据的交换，这个连接的一端称为一个socket。

建立网络通信连接至少要一个端口号。socket 本质是编程接口(API)，对 TCP/IP 的封装，TCP/IP 也要提供可供程序员做网络开发所用的接口，这就是 Socket 编程接口。

套接字之间的连接过程可以分为三个步骤：服务器监听，客户端请求，连接确认。【如果包含数据交互+断开连接，那么一共是五个步骤】

1
2
3

（1）服务器监听：是服务器端套接字并不定位具体的客户端套接字，而是处于等待连接的状态，实时监控网络状态。
（2）客户端请求：是指由客户端的套接字提出连接请求，要连接的目标是服务器端的套接字。
（3）连接确认：是指当服务器端套接字监听到或者说接收到客户端套接字的连接请求，它就响应客户端套接字的请求，建立一个新的线程，把服务器端套接字的描述发给客户端，一旦客户端确认了此描述，连接就建立好了。而服务器端套接字继续处于监听状态，继续接收其他客户端套接字的连接请求。

在 java.net 包是网络编程的基础类库。其中 ServerSocket 和 Socket 是网络编程的基础类型。ServerSocket 是服务端应用类型。Socket 是建立连接的类型。当连接建立成功后，服务器和客户端都会有一个 Socket 对象示例，可以通过这个 Socket 对象示例，完成会话的所有操作。

同步与异步

同步： 同步就是发起一个调用后，被调用者未处理完请求之前，调用不返回。

异步： 异步就是发起一个调用后，立刻得到被调用者的回应表示已接收到请求，但是被调用者并没有返回结果，此时我们可以处理其他的请求，被调用者通常依靠事件，回调等机制来通知调用者其返回结果。
同步和异步的区别最大在于异步的话调用者不需要等待处理结果，被调用者会通过回调等机制来通知调用者其返回结果。

阻塞和非阻塞

阻塞： 阻塞就是发起一个请求，调用者一直等待请求结果返回，也就是当前线程会被挂起，无法从事其他任务，只有当条件就绪才能继续。

非阻塞： 非阻塞就是发起一个请求，调用者不用一直等着结果返回，可以先去干其他事情。

举个生活中简单的例子，你妈妈让你烧水，小时候你比较笨啊，在那里傻等着水开（同步阻塞）。等你稍微再长大一点，你知道每次烧水的空隙可以去干点其他事，然后只需要时不时来看看水开了没有（同步非阻塞）。后来，你们家用上了水开了会发出声音的壶，这样你就只需要听到响声后就知道水开了，在这期间你可以随便干自己的事情，你需要去倒水了（异步非阻塞）。

BIO (Blocking I/O)

同步阻塞I/O模式，数据的读取写入必须阻塞在一个线程内等待其完成。

传统 BIO

BIO通信（一请求一应答)

采用 BIO 通信模型 的服务端，通常由一个独立的 Acceptor 线程负责监听客户端的连接。我们一般通过在while(true) 循环中服务端会调用 accept() 方法等待接收客户端的连接的方式监听请求，请求一旦接收到一个连接请求，就可以建立通信套接字在这个通信套接字上进行读写操作，此时不能再接收其他客户端连接请求，只能等待同当前连接的客户端的操作执行完成， 不过可以通过多线程来支持多个客户端的连接，如上图所示。

采用 BIO 通信模型 的服务端，通常由一个独立的 Acceptor 线程负责监听客户端的连接。我们一般通过在while(true) 循环中服务端会调用 accept() 方法等待接收客户端的连接的方式监听请求，请求一旦接收到一个连接请求，就可以建立通信套接字在这个通信套接字上进行读写操作，此时不能再接收其他客户端连接请求，只能等待同当前连接的客户端的操作执行完成， 不过可以通过多线程来支持多个客户端的连接，如上图所示。

我们再设想一下当客户端并发访问量增加后这种模型会出现什么问题？

在 Java 虚拟机中，线程是宝贵的资源，线程的创建和销毁成本很高，除此之外，线程的切换成本也是很高的。尤其在 Linux 这样的操作系统中，线程本质上就是一个进程，创建和销毁线程都是重量级的系统函数。如果并发访问量增加会导致线程数急剧膨胀可能会导致线程堆栈溢出、创建新线程失败等问题，最终导致进程宕机或者僵死，不能对外提供服务。

伪异步 IO

为了解决同步阻塞I/O面临的一个链路需要一个线程处理的问题，通过线程池可以灵活地调配线程资源，设置线程的最大值，防止由于海量并发接入导致线程耗尽。

采用线程池和任务队列可以实现一种叫做伪异步的 I/O 通信框架，当有新的客户端接入时，将客户端的 Socket 封装成一个Task（该任务实现java.lang.Runnable接口）投递到后端的线程池中进行处理，JDK 的线程池维护一个消息队列和 N 个活跃线程，对消息队列中的任务进行处理。由于线程池可以设置消息队列的大小和最大线程数，因此，它的资源占用是可控的，无论多少个客户端并发访问，都不会导致资源的耗尽和宕机。

伪异步I/O通信框架采用了线程池实现，因此避免了为每个请求都创建一个独立线程造成的线程资源耗尽问题。不过因为它的底层仍然是同步阻塞的BIO模型，因此无法从根本上解决问题。

代码示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

public class BIOClient {
    public static void main(String[] args) {
        //  创建多个线程，模拟多个客户端连接服务端
        new Thread(() -> {
            try {
                Socket socket = new Socket("127.0.0.1", 3333);
                while (true) {
                    try {
                        socket.getOutputStream().write((new Date() + ": hello ").getBytes());
                        Thread.sleep(2000);
                    } catch (Exception e) {
                    }
                }
            } catch (IOException e) {
            }
        }).start();
        new Thread(() -> {
            try {
                Socket socket = new Socket("127.0.0.1", 3333);
                while (true) {
                    try {
                        socket.getOutputStream().write((new Date() + ": hello two").getBytes());
                        Thread.sleep(2000);
                    } catch (Exception e) {
                    }
                }
            } catch (IOException e) {
            }
        }).start();
    }
}

服务端：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

public class BIOServer {
    public static void main(String[] args) throws IOException {
        ServerSocket serverSocket = new ServerSocket(3333);
        // 接收到客户端连接请求之后为每个客户端创建一个新的线程进行链路处理
        while (true) {
            try {
                // 阻塞方法获取新的连接
                Socket socket = serverSocket.accept();
                // 每一个新的连接都创建一个线程，负责读取数据
                new Thread(() -> {
                    try {
                        int len;
                        byte[] data = new byte[1024];
                        InputStream inputStream = socket.getInputStream();
                        // 按字节流方式读取数据
                        while ((len = inputStream.read(data)) != -1) {
                            System.out.println(new String(data, 0, len));
                        }
                    } catch (IOException e) {
                    }
                }).start();

            } catch (IOException e) {
            }

        }

    }
}

直接使用线程池：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

public class BIOServer1 {
    public static void main(String[] args) throws Exception {
        ExecutorService executor = Executors.newFixedThreadPool(100);
        //线程池
        ServerSocket serverSocket = new ServerSocket(1111);
        //主线程死循环等待新连接到来
        while (!Thread.currentThread().isInterrupted()) {
            Socket socket = serverSocket.accept();
            //为新的连接创建新的线程
            executor.submit(new ConnectIOnHandler(socket));
        }
    }
}
class ConnectIOnHandler extends Thread {
    private Socket socket;

    public ConnectIOnHandler(Socket socket) {
        this.socket = socket;
    }

    public void run() {
        //死循环处理读写事件
        while (!Thread.currentThread().isInterrupted() && !socket.isClosed()) {
            try {
                int len;
                byte[] data = new byte[1024];
                InputStream inputStream = socket.getInputStream();
                // 按字节流方式读取数据
                String body = null;
                while ((len = inputStream.read(data)) != -1) {
                    body = new String(data, 0, len);
                    System.out.println("服务端接收到: " + body);
                }
            } catch (Exception e) {

            }
        }
    }
}

或：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

public class BIOServer {
    /*public static void main(String[] args) throws IOException {
        ServerSocket serverSocket = new ServerSocket(3333);
        // 接收到客户端连接请求之后为每个客户端创建一个新的线程进行链路处理
        while (true) {
            try {
                // 阻塞方法获取新的连接
                Socket socket = serverSocket.accept();
                // 每一个新的连接都创建一个线程，负责读取数据
                new Thread(() -> {
                    try {
                        int len;
                        byte[] data = new byte[1024];
                        InputStream inputStream = socket.getInputStream();
                        // 按字节流方式读取数据
                        while ((len = inputStream.read(data)) != -1) {
                            System.out.println(new String(data, 0, len));
                        }
                    } catch (IOException e) {
                    }
                }).start();

            } catch (IOException e) {
            }

        }

    }*/
    public static void main(String[] args){
        ServerSocket serverSocket = null;
        try {
            serverSocket = new ServerSocket(5645);
            Socket socket = null;
            ServerThreadExcutorPool singleExcutor = new ServerThreadExcutorPool(50, 1000);
            while (true) {
                socket = serverSocket.accept();
                singleExcutor.execute(new ServerHandler(socket));
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != serverSocket) {
                try {
                    serverSocket.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


}
class ServerThreadExcutorPool {
    private ExecutorService executorService;

    public ServerThreadExcutorPool(int maxPoolSize, int queneSize) {
        executorService = new ThreadPoolExecutor(Runtime.getRuntime().availableProcessors(), maxPoolSize, 120L, TimeUnit.SECONDS, new ArrayBlockingQueue<>(queneSize));
    }
    public void execute(Runnable task) {
        executorService.execute(task);
    }
}
class ServerHandler implements Runnable {

    private Socket socket;

    public ServerHandler(Socket socket) {
        this.socket = socket;
    }

    @Override
    public void run() {
        try {
            while (true) {
                int len;
                byte[] data = new byte[1024];
                InputStream inputStream = socket.getInputStream();
                // 按字节流方式读取数据
                String body = null;
                while ((len = inputStream.read(data)) != -1) {
                    body = new String(data, 0, len);
                }
                System.out.println("服务端接收到: " + body);
                socket.getOutputStream().write(body.getBytes());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (this.socket != null) {
                try {
                    this.socket.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

活动连接数不是特别高（小于单机1000）的情况下，这种模型是比较不错的，可以让每一个连接专注于自己的 I/O 并且编程模型简单，也不用过多考虑系统的过载、限流等问题。线程池本身就是一个天然的漏斗，可以缓冲一些系统处理不了的连接或请求。但是，当面对十万甚至百万级连接的时候，传统的 BIO 模型是无能为力的。因此，我们需要一种更高效的 I/O 处理模型来应对更高的并发量。

NIO (New I/O)

NIO是一种同步非阻塞的I/O模型，在Java 1.4 中引入了NIO框架，对应 java.nio 包，提供了 Channel , Selector，Buffer等抽象。

NIO中的N可以理解为Non-blocking，它支持面向缓冲的，基于通道的I/O操作方法。 NIO提供了与传统BIO模型中的 Socket 和 ServerSocket相对应的 SocketChannel和 ServerSocketChannel 两种不同的套接字通道实现，支持阻塞和非阻塞两种模式。

阻塞模式使用就像传统中的支持一样，比较简单，但是性能和可靠性都不好；

非阻塞模式正好与之相反。对于低负载、低并发的应用程序，可以使用同步阻塞I/O来提升开发速率和更好的维护性；对于高负载、高并发的（网络）应用，应使用 NIO 的非阻塞模式来开发。

NIO与IO区别

NIO 流是非阻塞 IO 而 IO 流是阻塞 IO 说起。然后，可以从 NIO 的3个核心组件/特性为 NIO 带来的一些改进来分析。

1)Non-blocking IO（非阻塞IO）

IO流是阻塞的，NIO流是不阻塞的。

Java NIO使我们可以进行非阻塞IO操作。比如说，单线程中从通道读取数据到buffer，同时可以继续做别的事情，当数据读取到buffer中后，线程再继续处理数据。写数据也是一样的。另外，非阻塞写也是如此。一个线程请求写入一些数据到某通道，但不需要等待它完全写入，这个线程同时可以去做别的事情。

Java IO的各种流是阻塞的。这意味着，当一个线程调用 read() 或 write() 时，该线程被阻塞，直到有一些数据被读取，或数据完全写入。该线程在此期间不能再干其他任何任务。

2)Buffer(缓冲区)

IO 面向流(Stream oriented)，而 NIO 面向缓冲区(Buffer oriented)。

Buffer是一个对象，它包含一些要写入或者要读出的数据。在NIO类库中加入Buffer对象，体现了新库与原I/O的一个重要区别。在面向流的I/O中·可以将数据直接写入或者将数据直接读到 Stream 对象中。虽然 Stream 中也有 Buffer 开头的扩展类，但只是流的包装类，还是从流读到缓冲区，而 NIO 却是直接读到 Buffer 中进行操作。

在NIO厍中，所有数据都是用缓冲区处理的。在读取数据时，它是直接读到缓冲区中的; 在写入数据时，写入到缓冲区中。任何时候访问NIO中的数据，都是通过缓冲区进行操作。

最常用的缓冲区是 ByteBuffer,一个 ByteBuffer 提供了一组功能用于操作 byte 数组。除了ByteBuffer,还有其他的一些缓冲区，事实上，每一种Java基本类型（除了Boolean类型）都对应有一种缓冲区。

3)Channel (通道)

NIO 通过Channel（通道） 进行读写。

通道是双向的，可读也可写，而流的读写是单向的。无论读写，通道只能和Buffer交互。因为 Buffer，通道可以异步地读写。

4)Selectors(选择器)

NIO有选择器，而IO没有。

选择器用于使用单个线程处理多个通道。因此，它需要较少的线程来处理这些通道。线程之间的切换对于操作系统来说是昂贵的。

NIO 读数据和写数据方式

通常来说NIO中的所有IO都是从 Channel（通道） 开始的。

• 从通道进行数据读取 ：创建一个缓冲区，然后请求通道读取数据。
• 从通道进行数据写入 ：创建一个缓冲区，填充数据，并要求通道写入数据。

NIO核心组件简单介绍

NIO 包含下面几个核心的组件：

Channel(通道)
Buffer(缓冲区)
Selector(选择器)

代码示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

public class NIOServer {
    public static void main(String[] args) throws IOException {
        // serverSelector负责轮询是否有新的连接，服务端监测到新的连接之后，不再创建一个新的线程，而是直接将新连接绑定到clientSelector上，这样就不用 IO 模型中 while 循环死等
        Selector serverSelector = Selector.open();
        // clientSelector负责轮询连接是否有数据可读
        Selector clientSelector = Selector.open();

        new Thread(() -> {
            try {
                // 对应IO编程中服务端启动
                ServerSocketChannel listenerChannel = ServerSocketChannel.open();
                listenerChannel.socket().bind(new InetSocketAddress(1111));
                listenerChannel.configureBlocking(false);
                listenerChannel.register(serverSelector, SelectionKey.OP_ACCEPT);

                while (true) {
                    // 监测是否有新的连接，这里的1指的是阻塞的时间为 1ms
                    if (serverSelector.select(1) > 0) {
                        Set<SelectionKey> set = serverSelector.selectedKeys();
                        Iterator<SelectionKey> keyIterator = set.iterator();

                        while (keyIterator.hasNext()) {
                            SelectionKey key = keyIterator.next();

                            if (key.isAcceptable()) {
                                try {
                                    // 每来一个新连接，不需要创建一个线程，而是直接注册到clientSelector
                                    SocketChannel clientChannel = ((ServerSocketChannel) key.channel()).accept();
                                    clientChannel.configureBlocking(false);
                                    clientChannel.register(clientSelector, SelectionKey.OP_READ);
                                } finally {
                                    keyIterator.remove();
                                }
                            }

                        }
                    }
                }
            } catch (IOException ignored) {
            }
        }).start();

        new Thread(() -> {
            try {
                while (true) {
                    // 批量轮询是否有哪些连接有数据可读，这里的1指的是阻塞的时间为 1ms
                    if (clientSelector.select(1) > 0) {
                        Set<SelectionKey> set = clientSelector.selectedKeys();
                        Iterator<SelectionKey> keyIterator = set.iterator();

                        while (keyIterator.hasNext()) {
                            SelectionKey key = keyIterator.next();

                            if (key.isReadable()) {
                                try {
                                    SocketChannel clientChannel = (SocketChannel) key.channel();
                                    ByteBuffer byteBuffer = ByteBuffer.allocate(1024);
                                    // 面向 Buffer
                                    clientChannel.read(byteBuffer);
                                    byteBuffer.flip();
                                    System.out.println(Charset.defaultCharset().newDecoder().decode(byteBuffer).toString());
                                } finally {
                                    keyIterator.remove();
                                    key.interestOps(SelectionKey.OP_READ);
                                }
                            }

                        }
                    }
                }
            } catch (IOException ignored) {
            }
        }).start();

    }
}

JDK 原生 NIO 编程复杂、编程模型难：

JDK 的 NIO 底层由 epoll 实现，该实现饱受诟病的空轮询 bug 会导致 cpu 飙升 100%

项目庞大之后，自行实现的 NIO 很容易出现各类 bug，维护成本较高。

Netty 的出现很大程度上改善了 JDK 原生 NIO 所存在的一些让人难以忍受的问题。

Java NIO浅析 https://zhuanlan.zhihu.com/p/23488863

AIO (Asynchronous I/O)

AIO 也就是 NIO 2。在 Java 7 中引入了 NIO 的改进版 NIO 2,它是异步非阻塞的IO模型。异步 IO 是基于事件和回调机制实现的，也就是应用操作之后会直接返回，不会堵塞在那里，当后台处理完成，操作系统会通知相应的线程进行后续的操作。

AIO 是异步IO的缩写，虽然 NIO 在网络操作中，提供了非阻塞的方法，但是 NIO 的 IO 行为还是同步的。对于 NIO 来说，我们的业务线程是在 IO 操作准备好时，得到通知，接着就由这个线程自行进行 IO 操作，IO操作本身是同步的。（除了 AIO 其他的 IO 类型都是同步的，这一点可以从底层IO线程模型解释，推荐一篇文章：https://mp.weixin.qq.com/s?__biz=Mzg3MjA4MTExMw==&mid=2247484746&idx=1&sn=c0a7f9129d780786cabfcac0a8aa6bb7&source=41#wechat_redirect