双主双从构建

搭建过程

1. 安装 MySQL

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum update
yum install mysql-server

2. 配置 MySQL 的配置文件

vi /etc/mysql/my.ini

• Master1

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# 服务器唯一ID
server-id=1
# 启用二进制日志
log-bin=mysql-bin

log-slave-updates=1

# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

# 设置需要复制的数据库
# 如果要配置了此项，则 mysql 只复制下面指定的数据库。
# 如果不配置此项，则 mysql 默认复制所有的数据库（不包含 binlog-ignore-db 的数据库）
binlog-do-db=test

auto-increment-increment=2
auto-increment-offset=1

• Master2

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
#主服务器唯一ID
server-id=2
#启用二进制日志
log-bin=mysql-bin

log-slave-updates=1

# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

# 设置需要复制的数据库
# 如果要配置了此项，则 mysql 只复制下面指定的数据库。
# 如果不配置此项，则 mysql 默认复制所有的数据库（不包含 binlog-ignore-db 的数据库）
binlog-do-db=test

auto-increment-increment=2
auto-increment-offset=2

• Slave1

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL

#从服务器唯一ID
server-id=3

log-bin=mysql-slave-bin   
relay-log=mysql-relay

• Slave2

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL

#从服务器唯一ID
server-id=4

log-bin=mysql-slave-bin   
relay-log=mysql-relay

3. 启动 Mysql

chown mysql:mysql -R /var/lib/mysql

mysqld --initialize

systemctl start mysqld

打开对应端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent

4. 创建用户并赋予权限

• 登录 Master1 的数据库授权并刷新

create user 'slave'@'%' identified by '123456';

grant replication slave,replication client on *.* to 'slave'@'%';

flush privileges;

• 登录 Master2 的数据库授权并刷新

create user 'slave'@'%' identified by '123456';

grant replication slave,replication client on *.* to 'slave'@'%';

flush privileges;

5. 查看配置

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 450
     Binlog_Do_DB: test
 Binlog_Ignore_DB: information_schema,mysql,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)

名称	IP	File	Position
Master1	172.17.0.2	mysql-bin.000003	450
Master2	172.17.0.3	mysql-bin.000003	470
Slave1	172.17.0.4	mysql-bin.000002	516
Slave2	172.17.0.5	mysql-bin.000002	653

6. 配置主从

• 登录从库配置

Slave1

change master to master_host='172.17.0.2',
master_port=3306,
master_user='slave',
master_password='123456',
master_connect_retry=60,
master_log_file='mysql-bin.000003',
master_log_pos=450;

Slave2

change master to master_host='172.17.0.3	',
master_port=3306,
master_user='slave',
master_password='123456',
master_connect_retry=60,
master_log_file='mysql-bin.000003',
master_log_pos=470;

• 启动配置

start slave;

若没有配置错误，则会输出：

show slave status\G

...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

7. 配置主主

• Master1 上配置

change master to master_host='172.17.0.3',
master_port=3306,
master_user='slave',
master_password='123456',
master_connect_retry=60,
master_log_file='mysql-bin.000003',
master_log_pos=470;

• Master2 上配置

change master to master_host='172.17.0.2',
master_port=3306,
master_user='slave',
master_password='123456',
master_connect_retry=60,
master_log_file='mysql-bin.000003',
master_log_pos=450;

• 启动

start slave

Mycat

搭建过程

1. 下载 Mycat

wget http://dl.mycat.org.cn/1.6.7.6/20201104174609/Mycat-server-1.6.7.6-test-20201104174609-linux.tar.gz

tar -zxvf Mycat-server-1.6.7.6-test-20201104174609-linux.tar.gz

2. 配置 server.xml

vi mycat/conf/server.xml

<user name="root">
	<property name="password">123456</property>
	<property name="schemas">TESTDB</property>
</user>

<user name="user">
	<property name="password">user</property>
	<property name="schemas">TESTDB</property>
	<property name="readOnly">true</property>
</user>

3. 配置 schema.xml

主库负责读写，从库只负责读。

vi mycat/conf/schema.xml

<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
</schema>
<dataNode name="dn1" dataHost="localhost1" database="test" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
	<writeHost host="hostM1" url="172.17.0.2:3306" user="slave" password="123456">
        <readHost host="hostS1" url="172.17.0.4:3306" user="slave" password="123456" />
    </writeHost>
    <writeHost host="hostM2" url="172.17.0.3:3306" user="slave" password="123456">
        <readHost host="hostS2" url="172.17.0.5:3306" user="slave" password="123456" />
    </writeHost>
</dataHost>

4. 启动

打开对应端口

firewall-cmd --zone=public --add-port=8066/tcp --permanent

./mycat/bin/mycat start