一、主从复制（常用方法）

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)