本文详细介绍了MySQL主从复制的配置方法，包括基本的主从复制、GTID方式的主从复制、半同步复制、并行复制等高级配置。此外还介绍了如何利用mysql-proxy进行读写分离配置以及分布式MySQL集群的搭建。
一、主从复制（常用方法）
1、主从复制（异步复制）过程
1.主数据库（Master）将变更信息写入到二进制日志文件中，这里需要注意的是旧版本的MySQL数据库默认是不开启二进制日志的，强烈建议在安装好数据库启动之前一定要先检查一下二进制日志文件是否开启，即使不做主从复制架构也要开启，否则当数据库启动之后再开启二进制日志时需要重新启动数据库。
2.从数据库（Slave）开启一个IO工作线程，通过该IO线程与主数据库建立一个普通客户端连接，主数据库会启动一个二进制日志转储线程（binglog dump thread），从数据库的IO线程通过这个转储线程读取主库上的变更事件，并将变更事件记录到中继日志中（relay_log），如果从数据库的IO线程读取速度追赶上主库的事件变更，在没有得到新变更的通知时，IO线程会进入Sleep状态。
3.从数据库还会启动一个SQL Thread线程，这个线程从中继日志（relay_log）中读取变更事件，并将变更同步到从数据库中。同时，可以通过配置选项，除了将变更存储到数据库中，也可以将变更事件同时存储在从数据库的二进制日志中
缺点
mysql主从复制存在的问题：
主库宕机后，数据可能丢失
从库只有一个sql Thread，主库写压力大，复制很可能延时
2、主从复制配置
配置环境
master机：server1（172.25.254.1）
slave机：server2（172.25.254.2）
1、master机和slave机安装mysql
slave机同master机
[root@server1 ~]# ls
mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-devel-5.7.17-1.el6.x86_64.rpm
mysql-community-embedded-5.7.17-1.el6.x86_64.rpm
mysql-community-embedded-devel-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
mysql-community-test-5.7.17-1.el6.x86_64.rpm
[root@server1 ~]# yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm -y
2、mysql初始化配置
slave机通master机
[root@server1 ~]# /etc/init.d/mysqld start ##开启mysql
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
[root@server1 ~]# grep password /var/log/mysqld.log ##mysql开启时会生成在临时密码，可在日志中找到
2018-07-06T16:24:35.681054Z 1 [Note] A temporary password is generated for root@localhost: 6daOpiXZ/70r ##临时密码
2018-07-06T16:25:52.144630Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.kQ4uRR.sql' started.
2018-07-06T16:25:52.335081Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.kQ4uRR.sql' ended.
2018-07-06T16:25:54.406557Z 0 [Note] Shutting down plugin 'sha256_password'
2018-07-06T16:25:54.406560Z 0 [Note] Shutting down plugin 'mysql_native_password'
2018-07-06T16:25:56.577087Z 3 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
[root@server1 ~]# mysql_secure_installation ##初始化
3、master机配置
[root@server1 ~]# vim /etc/my.cnf
29 server-id=1
30 log-bin=mysql-bin ##开启二进制日志
[root@server1 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@server1 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to wuyanzu@'172.25.254.%' identified by 'Wuyanzu+007'; ##授权
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql> flush privileges; ##同步
Query OK, 0 rows affected (0.10 sec)
mysql> show master status; ##查看master机状态
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 603 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
4、slave机配置
[root@server2 ~]# vim /etc/my.cnf
29 server-id=2
[root@server2 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld:
[root@server2 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='172.25.254.1',master_user='wuyanzu',master_password='Wuyanzu+007',master_log_file='mysql-bin.000001',master_log_pos=603; ##和master机建立认证
Query OK, 0 rows affected, 2 warnings (1.02 sec)
mysql> start slave; ##开启slave
Query OK, 0 rows affected (0.67 sec)
mysql> show slave status\G; ##查看
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.1
Master_User: wuyanzu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 603
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-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: 603
Relay_Log_Space: 529
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
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 0e09c92a-8139-11e8-9d84-52540013d792
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
如果 Slave_IO_Running 是 Connecting，原因：网络、授权、log_file/log_pos
5、测试
master机写入数据：
mysql> create database user;
Query OK, 1 row affected (0.14 sec)
mysql> use user;
Database changed
mysql> create table usertb;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table usertb (
-> username varchar(20) not null,
-> age varchar(4) not null);
Query OK, 0 rows affected (1.04 sec)
mysql> show tables;
+----------------+
| Tables_in_user |
+----------------+
| usertb |
+----------------+
1 row in set (0.00 sec)
mysql> insert into usertb values('wuyanzu','18');
Query OK, 1 row affected (0.42 sec)
mysql> select * from usertb;
+----------+-----+
| username | age |
+----------+-----+
| wuyanzu | 18 |
+----------+-----+
1 row in set (0.00 sec)
slave机查看：
mysql> select * from user.usertb;
+----------+-----+
| username | age |
+----------+-----+
| wuyanzu | 18 |
+----------+-----+
1 row in set (0.00 sec)
二、GTID方式配置主从复制
1、slave机配置
关闭slave，修改配置文件，重新配置
mysql> stop slave;
Query OK, 0 rows affected (0.05 sec)
mysql> ^DBye
[root@server2 ~]# vim /etc/my.cnf
29 server-id=2
30 gtid_mode = ON
31 enforce-gtid-consistency = true
[root@server2 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
2、master机配置
[root@server1 ~]# vim /etc/my.cnf
29 server-id=1
30 log-bin=mysql-bin
31 gtid_mode = ON
32 enforce-gtid-consistency = true
[root@server1 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@server1 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> insert into user.usertb values('chenguanxi','19');
Query OK, 1 row affected (0.38 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000002 | 422 | | | 0e09c92a-8139-11e8-9d84-52540013d792:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
3、slave机做认证配置
root@server2 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> stop slave;
Query OK, 0 rows affected (0.05 sec)
mysql> change master to master_host='172.25.254.1',master_user='wuyanzu',master_password='Wuyanzu+007',master_auto_position=1;
Quemysql> start slave;
Query OK, 0 rows affected (0.54 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.1
Master_User: wuyanzu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 422
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 635
Relay_Master_Log_File: mysql-bin.000002
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: 422
Relay_Log_Space: 844
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
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 0e09c92a-8139-11e8-9d84-52540013d792
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 0e09c92a-8139-11e8-9d84-52540013d792:1
Executed_Gtid_Set: 0e09c92a-8139-11e8-9d84-52540013d792:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ry OK, 0 rows affected, 2 warnings (0.44 sec)
4、测试
1、master机写入
mysql> insert into user.usertb values('zhoujielun','20');
Query OK, 1 row affected (0.25 sec)
mysql> select * from user.usertb;
+------------+-----+
| username | age |
+------------+-----+
| wuyanzu | 18 |
| chenguanxi | 19 |
| zhoujielun | 20 |
+------------+-----+
3 rows in set (0.00 sec)
2、slave机查看
mysql> select * from user.usertb;
+------------+-----+
| username | age |
+------------+-----+
| wuyanzu | 18 |
| chenguanxi | 19 |
| zhoujielun | 20 |
+------------+-----+
3 rows in set (0.00 sec)
主从复制效果达到
三、半同步复制
1、原理
2、在GTID主从复制的基础上导入半同步模块模块
1、master机：
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; ##导入半同步master模块
Query OK, 0 rows affected (0.11 sec)
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
7 rows in set (0.00 sec)
mysql> show status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
2、slave机配置
mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0e09c92a-8139-11e8-9d84-52540013d792 | 1 | 1 |
| 0e09c92a-8139-11e8-9d84-52540013d792 | 2 | 2 |
+--------------------------------------+----------------+--------------+
2 rows in set (0.00 sec)
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.11 sec)
mysql> show variables like 'rpl%';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
3 rows in set (0.02 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'rpl%';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
3 rows in set (0.01 sec)
mysql> show status like 'rpl%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> stop slave io_thread; ##重新开启io线程，使它完全打开
Query OK, 0 rows affected (0.01 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'rpl%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
3、测试
master机写入数据，并接受到slave机返回的ack值
mysql> insert into user.usertb values('liudehua','21');
Query OK, 1 row affected (0.26 sec)
mysql> show status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 1372 |
| Rpl_semi_sync_master_tx_wait_time | 1372 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
将slave机的io线程停掉，master机写入数据，会默认等待10s，如果超时还未等到slave机的ack，将自动切换到异步复制，如果slave机的io线程再次开启，复制将自动切换到半同步方式
slave机
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.04 sec)
master机
mysql> insert into user.usertb values('zhangxueyou','22');
Query OK, 1 row affected (10.45 sec)
mysql> show status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 1372 |
| Rpl_semi_sync_master_tx_wait_time | 1372 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
四、并行复制
1、查看从库的sql类型
mysql> show processlist;
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 52393 | Slave has read all relay log; waiting for more updates | NULL |
| 2 | system user | | NULL | Connect | 415 | Waiting for master to send event | NULL |
| 7 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
2、slave机配置
[root@server2 ~]# vim /etc/my.cnf
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16 ##线程数
master_info_repository=TABLE ##master_info存储方式为：table
relay_log_info_repository=TABLE ##relay_log_info存储方式为：table
relay_log_recovery=ON
[root@server2 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
3、登陆从库查看sql类型
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 86 | Slave has read all relay log; waiting for more updates | NULL |
| 2 | system user | | NULL | Connect | 87 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 5 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 6 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 11 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 13 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 15 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 16 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 17 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 18 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 19 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 20 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 21 | system user | | NULL | Connect | 87 | Waiting for an event from Coordinator | NULL |
| 22 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
19 rows in set (0.00 sec)
查看mysql的表，新增了下面两个表
mysql> use mysql;
mysql> show tables;
----------------------------
slave_master_info
slave_relay_log_info
----------------------------
五、mysql-proxy读写分离配置
配置环境
master机：server1（172.25.254.1）
slave机：server2(172.25.254.2)
调度器：server3（172.25.254.3）
1、调度器配置
1、安装读写分离代理mysql-proxy
[root@server3 ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
[root@server3 ~]# cd /usr/local/
[root@server3 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
[root@server3 local]# ll
total 44
drwxr-xr-x. 2 root root 4096 Jun 28 2011 bin
drwxr-xr-x. 2 root root 4096 Jun 28 2011 etc
drwxr-xr-x. 2 root root 4096 Jun 28 2011 games
drwxr-xr-x. 2 root root 4096 Jun 28 2011 include
drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib
drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib64
drwxr-xr-x. 2 root root 4096 Jun 28 2011 libexec
lrwxrwxrwx 1 root root 38 Jul 7 16:56 mysql-proxy -> mysql-proxy-0.8.5-linux-el6-x86-64bit/
drwxr-xr-x 8 7161 wheel 4096 Aug 19 2014 mysql-proxy-0.8.5-linux-el6-x86-64bit
drwxr-xr-x. 2 root root 4096 Jun 28 2011 sbin
drwxr-xr-x. 5 root root 4096 Jun 19 23:38 share
drwxr-xr-x. 2 root root 4096 Jun 28 2011 src
2、为了测试读写分离效果，修改lua脚本
[root@server3 local]# cd mysql-proxy/share/doc/mysql-proxy/
[root@server3 mysql-proxy]# vim rw-splitting.lua
38 if not proxy.global.config.rwsplit then
39 proxy.global.config.rwsplit = {
40 min_idle_connections = 1,
41 max_idle_connections = 2, ##表示连接数超过两个就开始读写分离
42
43 is_debug = false
44 }
45 end
3、编写配置文件
[root@server3 mysql-proxy]# pwd
/usr/local/mysql-proxy
[root@server3 mysql-proxy]# mkdir logs
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# vim conf/mysql-proxy.conf
1 [mysql-proxy]
2 user=root
3 proxy-address=172.25.254.3:3306
4 proxy-read-only-backend-addresses=172.25.254.2:3306
5 proxy-backend-addresses=172.25.254.1:3306
6 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.l ua
7 log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
8 log-level=debug
9 daemon=true
10 keepalive=true
3、启动mysql-proxy
[root@server3 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf ##第一次启动失败，原因是因为配置文件权限过大
2018-07-07 17:13:41: (critical) mysql-proxy-cli.c:326: loading config from '/usr/local/mysql-proxy/conf/mysql-proxy.conf' failed: permissions of /usr/local/mysql-proxy/conf/mysql-proxy.conf aren't secure (0660 or stricter required)
2018-07-07 17:13:41: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2018-07-07 17:13:41: (message) shutting down normally, exit code is: 1
[root@server3 mysql-proxy]# chmod 550 conf/mysql-proxy.conf
[root@server3 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf ##再次启动成功
测试：
1、master机授权用户可远程登陆：
mysql> grant select,insert,update on user.* to wuyanzu@'172.25.254.%' identified by 'Wuyanzu+007';
Query OK, 0 rows affected, 1 warning (0.36 sec)
2、master机和slave机安装lsof，以便查看效果
3、物理机拿三个shell测试
[root@foundation77 ~]# mysql -h 172.25.254.3 -u wuyanzu -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
master机查看
[root@server1 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 1168 mysql 34u IPv6 8487 0t0 TCP *:mysql (LISTEN)
mysqld 1168 mysql 52u IPv6 9264 0t0 TCP server1:mysql->server3:51667 (ESTABLISHED)
mysqld 1168 mysql 53u IPv6 9165 0t0 TCP server1:mysql->server2:57130 (ESTABLISHED)
mysqld 1168 mysql 55u IPv6 9265 0t0 TCP server1:mysql->server3:51668 (ESTABLISHED)
slave机查看
[root@server2 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 1705 mysql 32u IPv6 9424 0t0 TCP *:mysql (LISTEN)
mysqld 1705 mysql 48u IPv6 9577 0t0 TCP server2:mysql->server3:60675 (ESTABLISHED)
mysqld 1705 mysql 50u IPv4 9438 0t0 TCP server2:57130->server1:mysql (ESTABLISHED)
我们在连接slave机的客户端上写入数据，看是否写入到master机，如果写入，表示读写分离成功
连接slave机客户端写入
MySQL [user]> show tables;
+----------------+
| Tables_in_user |
+----------------+
| usertb |
+----------------+
1 row in set (0.00 sec)
MySQL [user]> insert into user.usertn values('huge','23');
ERROR 1146 (42S02): Table 'user.usertn' doesn't exist
MySQL [user]> insert into user.usertb values('huge','23');
Query OK, 1 row affected (0.38 sec)
master机查看
mysql> select * from user.usertb;
+-------------+-----+
| username | age |
+-------------+-----+
| wuyanzu | 18 |
| chenguanxi | 19 |
| zhoujielun | 20 |
| liudehua | 21 |
| zhangxueyou | 22 |
| huge | 23 |
+-------------+-----+
6 rows in set (0.00 sec)
写入成功，读写分离效果实现
六、分布式mysql配置
1、server1配置
1、配置文件修改
[root@server1 ~]# vim /etc/my.cnf
29 server_id=1
30 gtid_mode=ON
31 enforce_gtid_consistency=ON
32 master_info_repository=TABLE
33 relay_log_info_repository=TABLE
34 binlog_checksum=NONE
35 log_slave_updates=ON
36 log_bin=binlog
37 binlog_format=ROW
38
39 transaction_write_set_extraction=XXHASH64
40 loose-group_replication_group_name="6884089d-81cd-11e8-b10c-52540013d792" ##uuid 可以在数据库中用select uuid();查看
41 loose-group_replication_start_on_boot=off
42 loose-group_replication_local_address= "172.25.254.1:24901"
43 loose-group_replication_group_seeds= "172.25.254.1:24901,172.25.254.2:24901, 172.25.254.3:24901"
44 loose-group_replication_bootstrap_group=off
45
46 loose-group_replication_ip_whitelist='127.0.0.1/8,172.25.254.0/24'
47 loose-group_replication_enforce_update_everywhere_checks=true
48 loose-group_replication_single_primary_mode=false
2、重置mysql
[root@server1 mysql]# rm -fr *
[root@server1 mysql]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
[root@server1 mysql]# pwd
/var/lib/mysql
[root@server1 mysql]# grep password /var/log/mysqld.log ##查找临时密码
3、master机配置
[root@server1 mysql]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-log
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user root@localhost identified by 'Wuyanzu+007';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to wuyanzu@'%' identified by 'Wuyanzu+007';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_user='wuyanzu',master_password='Wuyanzu+007' for channe
Query OK, 0 rows affected, 2 warnings (0.65 sec)
mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.19 sec)
mysql> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)
mysql> start group_replication;
Query OK, 0 rows affected (1.59 sec)
mysql> set global group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 57b54493-81cf-11e8-8a9a-52540013d792 | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
2、server2配置
1、配置文件
29 server_id=2
30 gtid_mode=ON
31 enforce_gtid_consistency=ON
32 master_info_repository=TABLE
33 relay_log_info_repository=TABLE
34 binlog_checksum=NONE
35 log_slave_updates=ON
36 log_bin=binlog
37 binlog_format=ROW
38
39 transaction_write_set_extraction=XXHASH64
40 loose-group_replication_group_name="6884089d-81cd-11e8-b10c-52540013d792"
41 loose-group_replication_start_on_boot=off
42 loose-group_replication_local_address= "172.25.254.2:24901"
43 loose-group_replication_group_seeds= "172.25.254.1:24901,172.25.254.2:24901,172.25.254.3 :24901"
44 loose-group_replication_bootstrap_group=off
45
46 loose-group_replication_ip_whitelist='127.0.0.1/8,172.25.254.0/24'
47 loose-group_replication_enforce_update_everywhere_checks=true
48 loose-group_replication_single_primary_mode=false
2、初始化
[root@server2 ~]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@server2 ~]# vim /etc/my.cnf
[root@server2 ~]# cd /var/lib/mysql
[root@server2 mysql]# ls
auto.cnf ib_buffer_pool performance_schema server2-relay-bin.000010 user
ca-key.pem ibdata1 private_key.pem server2-relay-bin.index
ca.pem ib_logfile0 public_key.pem server-cert.pem
client-cert.pem ib_logfile1 server2-relay-bin.000008 server-key.pem
client-key.pem mysql server2-relay-bin.000009 sys
[root@server2 mysql]# rm -fr *
3、mysql脚本文件修改,注释掉密码插件
/etc/init.d/mysqld
111 ret=$?
112 #[ $ret -ne 0 ] && return $ret
113 #initfile="$(install_validate_password_sql_file)"
114 #action $"Installing validate password plugin: " /usr/sbin/mysqld --datadir= "$datadir" --user=mysql --init-file="$initfile"
115 #ret=$?
116 #rm -f "$initfile"
117 chown -R mysql:mysql "$datadir"
4、开启mysql并配置
[root@server2 mysql]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-log
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user root@localhost identified by 'Wuyanzu+007';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to wuyanzu@'%' identified by 'Wuyanzu+007';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_user='wuyanzu',master_password='Wuyanzu+007' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.89 sec)
mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.28 sec)
mysql> set global group_replication_allow_local_disjoint_gtids_join=on;
Query OK, 0 rows affected (0.00 sec)
mysql> start group_replication;
Query OK, 0 rows affected (7.37 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 57b54493-81cf-11e8-8a9a-52540013d792 | server1 | 3306 | ONLINE |
| group_replication_applier | c33132d0-81d1-11e8-9ff1-525400677cc8 | server2 | 3306 | RECOVERING |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
###首次查看状态RECOVERING，有可能是在同步数据，可以等会再查看，
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 57b54493-81cf-11e8-8a9a-52540013d792 | server1 | 3306 | ONLINE |
| group_replication_applier | c33132d0-81d1-11e8-9ff1-525400677cc8 | server2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
3、server3配置同server2
[root@server3 mysql]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Starting mysqld: [ OK ]
[root@server3 mysql]# grep password /var/log/mysqld.log
2018-07-07T10:57:25.628882Z 1 [Note] A temporary password is generated for root@localhost: p&,!dOLNA83p
2018-07-07T10:58:12.128260Z 3 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
[root@server3 mysql]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-log
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user root@localhost identified by 'Wuyanzu+007';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to wuyanzu@'%' identified by 'Wuyanzu+007';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_user='wuyanzu',master_password='Wuyanzu+007' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (1.38 sec)
mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.20 sec)
mysql> set global group_replication_allow_local_disjoint_gtids_join=on;
Query OK, 0 rows affected (0.00 sec)
mysql> start group_replication;
Query OK, 0 rows affected (4.31 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 57b54493-81cf-11e8-8a9a-52540013d792 | server1 | 3306 | ONLINE |
| group_replication_applier | 88460791-81d4-11e8-a6e6-525400e9cc87 | server3 | 3306 | RECOVERING |
| group_replication_applier | c33132d0-81d1-11e8-9ff1-525400677cc8 | server2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 57b54493-81cf-11e8-8a9a-52540013d792 | server1 | 3306 | ONLINE |
| group_replication_applier | 88460791-81d4-11e8-a6e6-525400e9cc87 | server3 | 3306 | ONLINE |
| group_replication_applier | c33132d0-81d1-11e8-9ff1-525400677cc8 | server2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
4、测试：
server3写入
mysql> create database test;
Query OK, 1 row affected (0.26 sec)
mysql> use test;
Database changed
mysql> create table t1 (c1 int primary key,c2 text not null); ##插入表时需要设置主键
Query OK, 0 rows affected (1.11 sec)
mysql> insert into t1 values(1,'wuyanzu');
Query OK, 1 row affected (0.24 sec)
server2查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| westos |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test;
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_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+----+---------+
| c1 | c2 |
+----+---------+
| 1 | wuyanzu |
+----+---------+
1 row in set (0.00 sec)
扫一扫
613
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
