云枢认证考试_云枢

MySQL5.7 配置 GTID 主从同步

前言

mysql要求5.7以上

Master开启gtid

[mysqld]

basedir=/usr/local/mysql_1

datadir=/usr/local/mysql_1/data

port=3307

socket=/usr/local/mysql_1/mysql.sock

character-set-server=utf8

log-error=/usr/local/mysql_1/log/mysqld.log

pid-file=/usr/local/mysql_1/mysqld.pid

#GTID:

gtid_mode=on

enforce_gtid_consistency=on

server_id=7

#每个实例的server_id都要不一样

#binloglog/

log-bin=/usr/local/mysql_1/log/log-bin

log-slave-updates=1

#允许后端接入slave

binlog_format=row

#强烈建议，其他格式可能造成数据不一致

#relay log

skip_slave_start=1

[mysql]

socket=/usr/local/mysql_1/mysql.sock

[client]

socket=/usr/local/mysql_1/mysql.sock

Slave 开启gtid

[mysqld]

basedir=/usr/local/mysql_2

datadir=/usr/local/mysql_2/data

port=3308

socket=/usr/local/mysql_2/mysql.sock

character-set-server=utf8

log-error=/usr/local/mysql_2/log/mysqld.log

pid-file=/usr/local/mysql_2/mysqld.pid

#GTID:

gtid_mode=on

enforce_gtid_consistency=on

server_id=8

#每个实例的server_id都要不一样log/

#binlog

log-bin=/usr/local/mysql_2/log/log-bin

log-slave-updates=1

#允许后端接入slave

binlog_format=row

#强烈建议，其他格式可能造成数据不一致

#relay log

skip_slave_start=1

[mysql]

socket=/usr/local/mysql_2/mysql.sock

[client]

socket=/usr/local/mysql_2/mysql.sock

查看gtid开启状态

show variables like '%gtid%';

+----------------------------------+-----------+

| Variable_name | Value |

+----------------------------------+-----------+

| binlog_gtid_simple_recovery | ON |

| enforce_gtid_consistency | ON |

| gtid_executed_compression_period | 1000 |

| gtid_mode | ON |

| gtid_next | AUTOMATIC |

| gtid_owned | |

| gtid_purged | |

| session_track_gtids | OFF |

+----------------------------------+-----------+

8 rows inset (0.01 sec)

创建同步用户

CREATE USER 'gtid'@'%' IDENTIFIED BY '1qaz@WSX';

GRANT REPLICATION SLAVE ON *.* TO 'gtid'@'%';

配置从库gtid复制

主库开启只读

SET @@global.read_only = ON;

从库添加配置

#停掉同步线程

stop slave;

#设置同步对象

CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3307, MASTER_USER='gtid', MASTER_PASSWORD='1qaz@WSX', MASTER_AUTO_POSITION = 1;

#开启同步线程

start slave;

show slave status\G

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting formaster to send event

Master_Host: 127.0.0.1

Master_User: gtid

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: log-bin.000001

Read_Master_Log_Pos: 603

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 812

Relay_Master_Log_File: log-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: 1023

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: 7

Master_UUID: 8c890bcd-ba6f-11ea-ab68-080027178650

Master_Info_File: /usr/local/mysql_2/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has readall relay log; waiting formore updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 8c890bcd-ba6f-11ea-ab68-080027178650:1-2

Executed_Gtid_Set: 8c890bcd-ba6f-11ea-ab68-080027178650:1-2

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row inset (0.00 sec)

主库停用只读

#要求以下两个参数相同：

#Retrieved_Gtid_Set: 8c890bcd-ba6f-11ea-ab68-080027178650:1-2

# Executed_Gtid_Set: 8c890bcd-ba6f-11ea-ab68-080027178650:1-2

#再恢复主库可写

SET @@global.read_only = OFF;

测试主从复制

#mysql_1上写入

create database db_test;

show databases;

use db_test;

create table ifnot exists user_info(

username varchar(16) not null,

password varchar(32) not null,

realname varchar(16) default '',

primary key (username)

)default charset=utf8;

show tables;

insert into user_info(username, password, realname) values

('10001', '123456', 'aa'),

('10002', '123456', 'bb'),

('10003', '123456', 'cc'),

('10004', '123456', 'dd'),

('10005', '123456', 'ee');

#mysql_2上查询

select * from user_info ;

MySQL-proxy读写分离

安装mysql-proxy

mkdirlogs lua conf

cpshare/doc/mysql-proxy/rw-splitting.lua ./lua/

cpshare/doc/mysql-proxy/admin-sql.lua ./lua/

配置并启动

mysql添加用户myproxy

CREATE USER 'myproxy '@'%' IDENTIFIED BY '123456';

GRANT ALL ON *.* TO 'myproxy '@'%';

编辑 conf/mysql-proxy.cnf

注意：该配置文件注释不可同行标注，否则程序启动失败

#启动用户

user=root

admin-username=myproxy

admin-password=123456

#proxy代理的地址和端口

proxy-address=0.0.0.0:3306

#只读mysql实例

proxy-read-only-backend-addresses=127.0.0.1:3308

#读写mysql实例

proxy-backend-addresses=127.0.0.1:3307

proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua

admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua

log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log

log-level=debug

pid-file=/usr/local/mysql-proxy/proxy.pid

chmod600 conf/mysql-proxy.cnf

以守护进程方式启动

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.cnf --daemon

测试

tcpdump抓包

#新版本mysql-proxy对mysql客户端支持不好，会卡死；需要安装mariadb-client

#mysql -umyproxy -p123456 -h127.0.0.1 -P3306

tcpdump -i lo -nn -XX ip dst 127.0.0.1 and tcp dst port 3307

tcpdump -i lo -nn -XX ip dst 127.0.0.1 and tcp dst port 3308