mysql5.7主从复制,基于GTID的复制,MysqlProxy实现读写分离

最新推荐文章于 2023-11-23 23:56:22 发布

gd0307

最新推荐文章于 2023-11-23 23:56:22 发布

阅读量265

点赞数

本文链接：https://blog.csdn.net/weixin_42167918/article/details/81487724

版权

server10和server11安装mysql:

yum install -y 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
/etc/init.d/mysqld start
#初始化（方式一）
grep password /var/log/mysqld.log
mysql_secure_installtion
mysql -p
#初始化方式二：
/etc/init.d/mysqld start
mysql -p
mysql> ALTER USER root@localhost identified by 'Workhard@345';
mysql> flush privileges;

主从复制

master配置：

vim /etc/my.cnf
添加：
server-id=1
log-bin=mysql-bin

master授权：

mysql -p
mysql> grant replication slave on *.* to ly@'172.25.92.%' identified by 'Workhard@345';
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      597 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

slave配置：

vim /etc/my.cnf
添加：
server-id=2

slave指定mster：

[root@server11 mysql]# mysql -uroot -p
mysql> CHANGE MASTER TO MASTER_HOST='172.25.92.10',MASTER_USER='ly',MASTER_PASSWORD='Workhard@345',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=597;
Query OK, 0 rows affected, 2 warnings (1.21 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event #这个是指slave 连接到master的状态。
                  Master_Host: 172.25.92.10 #master主机
                  Master_User: ly  #这个是master上面的一个用户。用来负责主从复制的用户 ，创建主从复制的时候建立
                  Master_Port: 3306
                Connect_Retry: 60 # master-connect-retry选项的当前值
              Master_Log_File: mysql-bin.000001 #I/O线程当前正在读取的主服务器二进制日志文件的名称。
          Read_Master_Log_Pos: 597 #在当前的主服务器二进制日志中，I/O线程已经读取的位置。
               Relay_Log_File: server11-relay-bin.000002 #slave的SQL线程当前正在读取和执行的中继日志文件的名称。
                Relay_Log_Pos: 320 #在当前的中继日志中，slave的SQL线程已读取和执行的位置。
        Relay_Master_Log_File: mysql-bin.000001 #由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称。
             Slave_IO_Running: Yes #I/O线程是否被启动并成功地连接到主服务器上。
            Slave_SQL_Running: Yes #SQL线程是否被启动。
              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:     #slave的SQL线程读取日志参数的的错误数量和错误消息
      Exec_Master_Log_Pos: 597  #来自主服务器的二进制日志的由SQL线程执行的上一个时间的位置
              Relay_Log_Space: 530     #所有原有的中继日志结合起来的总大小。
              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  #表示主从之间的时间差 是数字的时候表示相差多少秒  null表示未知数，一般主从复制出问题了会出现null的情况。
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: 24f8e881-0499-11e8-9bc0-52540039110b
             Master_Info_File: /var/lib/mysql/master.info
           Skip_Counter: 0
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)

测试：master插入数据，slave可以看见：

master上：
mysql> create database test;
mysql> create table userdb (
    -> username varchar(15) not null,
    -> password varchar(20) not null);
mysql> desc userdb;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(15) | NO   |     | NULL    |       |
| password | varchar(20) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
mysql> insert into userdb values ('user1','111');
Query OK, 1 row affected (0.15 sec)

mysql> insert into userdb values ('user2','222');
Query OK, 1 row affected (0.37 sec)

slave上可以查看到。

数据备份

[root@server10 mysql]# mysqlbinlog -v mysql-bin.000001
[root@server10 mysql]# mysqlbinlog --start-datetime='2018-01-29 11:00:12' --stop-datetime='2018-01-29 13:56:41' mysql-bin.000001 > test.sql     #按时间端备份并保存zai
[root@server10 mysql]# mysql -p test < test.sql        #将备份数据倒入到test库中

基于GTID的复制

master和slave上：

vim /etc/my.cnf 
添加：
gtid_mode=ON
enforce-gtid-consistency=true

slave上：
mysql> STOP SLAVE;
mysql> change master to master_host='172.25.92.10',master_user='ly',master_password='Workhard@345',master_auto_position=1;
/etc/init.d/mysql restart

测试：

master：
[root@server10 mysql]# mysql -uroot -p
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
slave：
mysql> show slave status\G;
    Retrieved_Gtid_Set: 
         Executed_Gtid_Set: 
            Auto_Position: 1
  master:
  mysql> insert into userdb values('usera','aaa');
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000004 |      422 |              |                  | 24f8e881-0499-11e8-9bc0-52540039110b:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
slave上：
mysql> show slave status\G;
Retrieved_Gtid_Set: 24f8e881-0499-11e8-9bc0-52540039110b:1
 Executed_Gtid_Set: 
        Auto_Position: 1

MysqlProxy实现读写分离

server6上：

server6上：
wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 
mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

vim /etc/mysql-proxy.cnf 
[mysql-proxy]
#user=root
#plugins=proxy
proxy-address=172.25.92.6
proxy-read-only-backend-addresses=172.25.92.5:3306
proxy-backend-addresses=172.25.92.4:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
log-file=/var/log/mysql-proxy.log
log-level=debug
daemon=true
#keepalive=true
plugins=admin
admin-username='admin'
admin-password='admin'
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua


vim /usr/local/mysql-proxy/share/doc/mysql-proxy/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         }


ll /etc/mysql-proxy.cnf
chmod 660 /etc/mysql-proxy.cnf 
cd /usr/local/mysql-proxy/bin/
./mysql-proxy --defaults-file=/etc/mysql-proxy.cnf --plugins=proxy --plugins=admin  #启动mysql-proxy和admin
[root@server6 bin]# netstat -antlp   #可以看到3306和4041端口
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      890/sshd            
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      966/master          
tcp        0      0 172.25.92.6:4041            0.0.0.0:*                   LISTEN      1144/mysql-proxy    
tcp        0      0 172.25.92.6:3306            0.0.0.0:*                   LISTEN      1144/mysql-proxy

server10上：主

mysql> grant select,update,insert on *.* to proxy@'172.25.92.%' identified by 'Workhard@345';           #授权远程用户proxy可以执行select，update，insert操作
mysql> flush privileges;
mysql> use mysql;
mysql> select * from user;    #两台主机上都有proxy用户。

令外开启三个tables，一个是主机（172.25.254.92）另外两个是server6：

一个server6安装数据包监控：

[root@server6 ~]# yum install -y tcpdump
[root@server6 ~]# tcpdump -i eth0 port 3306
可以看到经过server6的数据包走向

一个server6执行admin查看状态：

mysql -uadmin -padmin -h 172.25.92.6 -P 4041  #登陆管理端
mysql> select * from backends;
+-------------+-------------------+-------+------+------+-------------------+
| backend_ndx | address           | state | type | uuid | connected_clients |
+-------------+-------------------+-------+------+------+-------------------+
|           1 | 172.25.92.10:3306 | up    | rw   | NULL |                 0 |
|           2 | 172.25.92.11:3306 | up    | ro   | NULL |                 0 |
+-------------+-------------------+-------+------+------+-------------------+
这里的server6可以用来查看后端读写分离的主机状态

最后个server6和本机用来充当客户端，执行读写操作：

[root@foundation92 Desktop]# mysql -h 172.25.92.6 -uproxy -pWorkhard@345 
MySQL [(none)]> show databases;
MySQL [test]> select * from userdb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
| user4    | 444      |
+----------+----------+
3 rows in set (0.00 sec)

MySQL [test]> insert into userdb values ('user5','555');
#当使用插入命令的使用，在admin的管理端可以看到server10开启（up）了

在第三台server6上：

mysql> use test;
mysql> select * from userdb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
| user4    | 444      |
| user5    | 555      |
+----------+----------+
4 rows in set (0.01 sec)
使用查询（读操作）操作在admin端可以看到server11也开启（up）

gd0307

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql5.7主从复制,基于GTID的复制,MysqlProxy实现读写分离

server10和server11安装mysql:yum install -y 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-li...
复制链接

扫一扫