mysql主从复制和读写分离配置

mysql主从复制 (二进制方式)

mysql-community-client.x86_64 0:5.7.17-1.el6                      mysql-community-common.x86_64 0:5.7.17-1.el6                      mysql-community-libs.x86_64 0:5.7.17-1.el6                 
mysql-community-libs-compat.x86_64 0:5.7.17-1.el6                 mysql-community-server.x86_64 0:5.7.17-1.el6       

/etc/init.d/mysqld start

cat /var/log/mysqld.log   #找初始密码
ALTER USER root@localhost identified by 'Zeinima748!!'; #进入数据库修改密码

• master

修改/etc/my.cnf
最后添加
server-id=1
log-bin=mysql-bin   #开启二进制

• slave

server-id=2

进入数据库

• master

1：创建共享用户
mysql> grant replication slave on *.* to dpq@'172.25.200.2' identified by 'Zeinima748!!';
Query OK, 0 rows affected, 1 warning (0.44 sec)
2：刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.17 sec)
3：查看
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      599 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4：创建 共享目录
mysql> create database westos;
Query OK, 1 row affected (0.13 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.00 sec)
#创建table；
mysql> use westos;
Database changed
mysql> create table usertb (
    -> username varchar(20) not null,
    -> password varchar(30) not null);
Query OK, 0 rows affected (1.41 sec)

mysql> desc usetb;
ERROR 1146 (42S02): Table 'westos.usetb' doesn't exist
mysql> desc usertb;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | NO   |     | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
#插入数据
mysql> insert into usertb values ('user1','123');
Query OK, 1 row affected (0.31 sec)

• slave

连接
mysql> change master to master_host='172.25.200.1',master_user='dpq',master_password='Zeinima748!!',master_log_file='mysql-bin.000001',master_log_pos=599;
Query OK, 0 rows affected, 2 warnings (1.70 sec)

mysql> start slave;
Query OK, 0 rows affected (0.17 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.200.1
                  Master_User: dpq
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 599
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes     #必须为yes
            Slave_SQL_Running: Yes     #必须为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: 599
              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: db2b6e93-0498-11e8-9b4d-525400f2a3c0
             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)

ERROR: 
No query specified

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

mysql> use westos;
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_westos |
+------------------+
| usertb           |
+------------------+
1 row in set (0.00 sec)

mysql> desc usertb;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | NO   |     | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

主从复制 (gtid 方式) (下一个下一个方式)

• master && slave

/etc/my.cnf

gtid_mode=ON
enforce-gtid-consistency=true

• slave

change master to master_host='172.25.200.1',master_user='dpq',master_passsword='Zeinima748!!',master_auto_position=1;

mysql并行复制

• slave

/etc/my.cnf
 31 server-id=2
 32 #log-bin=mysql-bin
 33 gtid_mode=ON
 34 enforce-gtid-consistency=true

 37 slave-parallel-type=LOGICAL_CLOCK
 38 slave-parallel-workers=16
 39 master_info_repository=TABLE
 40 relay_log_info_repository=TABLE
 41 relay_log_recovery=ON
mysql> show processlist;

http://www.ywnds.com/?p=3894

半同步复制

• master
• 1：下载半同步插件

install plugin rpl_semi_sync_master soname 'semisync_master.so';

• 2：查看最后一行是否下载成功

show plugins;
show variables like '%rpl%';
set global rpl_semi_sync_master_enabled =1;
show variables like '%rpl%'
show status like 'Rpl_semi_sync%';  #查看最后一行是否为提交成功
+--------------------------------------------+-------+
| 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              | 1     |
| Rpl_semi_sync_master_no_tx                 | 4     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 988   |
| Rpl_semi_sync_master_tx_wait_time          | 988   |
| 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

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
show plugins;
show variables like '%rpl%';
set global rpl_semi_sync_slave_enabled =1;
show variables like '%rpl%';
stop slave io_thread;
start slave io_thread;

• 参考
  https://dev.mysql.com/doc/refman/5.7/en
  http://www.actionsky.com/mysql-57-semi-sync-intro/

读写分离

• server1 server2 配置好主从复制
• server3

tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
mv mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
/usr/local/mysql-proxy/share/doc/mysql-proxy
 40                 min_idle_connections = 1,
 41                 max_idle_connections = 2,
[root@server3 mysql-proxy]# mkdir etc
[root@server3 mysql-proxy]# mkdir logs
[root@server3 ~]# cat /usr/local/mysql-proxy/etc/mysql-proxy.conf 
[mysql-proxy]
proxy-address=172.25.200.3:3306
proxy-read-only-backend-addresses=172.25.200.2:3306
proxy-backend-addresses=172.25.200.1:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
daemon=true
user=root
log-level=debug
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
keepalive=true
admin-username=admin
admin-password=westos
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
[root@server3 ~]# netstat -antlp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      914/sshd            
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      990/master          
tcp        0      0 0.0.0.0:4041 ！               0.0.0.0:*                   LISTEN      1231/mysql-proxy    
tcp        0      0 172.25.200.3:3306 ！          0.0.0.0:*                   LISTEN      1231/mysql-proxy    
tcp        0      0 172.25.200.3:22             172.25.200.250:46910        ESTABLISHED 1038/sshd           
tcp        0      0 172.25.200.3:22             172.25.200.250:46914        ESTABLISHED 1099/sshd           
tcp        0      0 :::22                       :::*                        LISTEN      914/sshd            
tcp        0      0 ::1:25                      :::*                        LISTEN      990/master    

• 调度器

[root@server3 ~]# mysql -uadmin -pwestos -h 172.25.200.3 -P4041   #查看调度器状态
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2013, 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> select * from backends;
+-------------+-------------------+-------+------+------+-------------------+
| backend_ndx | address           | state | type | uuid | connected_clients |
+-------------+-------------------+-------+------+------+-------------------+
|           1 | 172.25.200.1:3306 | up    | rw   | NULL |                 0 |
|           2 | 172.25.200.2:3306 | up    | ro   | NULL |                 0 |
+-------------+-------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)

• 真机

[root@foundation1 day07]# mysql -uproxy -h 172.25.200.3 -p   #通过调度器查看内容