搭建MYSQL集群（主从复制，读写分离）

搭建MYSQL集群（主从复制，读写分离）

架构：

环境：

CentOS7.3 一台，1核2G；MYSQL 5.7.23；mycat 1.6；

实战：

1. 搭建MYSQL（master01）主库

# 创建master01目录
mkdir /data/mysql/master01 -p
cd /data/mysql/master01/
mkdir conf data 
chmod 777 -R *

# 创建配置文件
cat << EOF > conf/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1
binlog_format=MIXED
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
EOF

# 创建容器
docker create --name mysql-master01 -v /data/mysql/master01/data:/var/lib/mysql -v /data/mysql/master01/conf:/etc/mysql/mysql.conf.d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.23


# 启动
docker start mysql-master01 && docker logs -f mysql-master01



# 创建同步账户并授权
create user 'ko'@'%' identified by '123';
grant replication slave on *.* to 'ko'@'%';
flush privileges;

# 查看master状态并记录下来
show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 750      |              |                  |                   |



# 查看二进制日志相关配置项
show global variables like 'binlog%';

# 查看server相关配置项
show global variables like 'server%';

主数据库配置文件可以有以下可选参数：

2.搭建MYSQL（slave01）从库

# 创建slave01目录
mkdir /data/mysql/slave01 -p
cd /data/mysql/slave01/
mkdir conf data 
chmod 777 -R *


# 创建配置文件
cat << EOF > conf/my.cnf
[mysqld]
server-id=2
relay-log=mysql-relay
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
EOF


# 创建容器
docker create --name mysql-slave01 -v /data/mysql/slave01/data:/var/lib/mysql -v /data/mysql/slave01/conf:/etc/mysql/mysql.conf.d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.23


# 启动
docker start mysql-slave01 && docker logs -f mysql-slave01


# 设置master相关信息
CHANGE MASTER TO
	master_host='172.16.0.4',   # master01 的 ip
	master_user='ko',          # 共享用户名
	master_password='123',      # 共享密码
	master_port='3306',        # master01 的端口
	master_log_file='mysql-bin.000001'    #由master01数据库show master status;产生的File列数据
	master_log_pos=750;        #由master01数据库show master status;产生的Position列数据
	
	
# 启动从库
start slave;

# 查看slave状态
show slave status;

# 这两项为Yes时才算成功！
Slave_IO_Running              | Yes             
Slave_SQL_Running             | Yes


***************************[ 1. row ]***************************
Slave_IO_State                | Waiting for master to send event
Master_Host                   | 172.16.0.4
Master_User                   | ko
Master_Port                   | 3306
Connect_Retry                 | 60
Master_Log_File               | mysql-bin.000001
Read_Master_Log_Pos           | 750
Relay_Log_File                | bc3a7c5b57b5-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           | 750

3.测试一下是否成功

# master01 创建数据库test01
mysql root@localhost:(none)> create database test01;                            
Query OK, 1 row affected
Time: 0.005s

# slave01 中以查询到数据库
mysql root@localhost:(none)> show databases;                                    
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test01             |
+--------------------+
5 rows in set
Time: 0.016s

# master01 创建表stu，并插入数据
mysql root@localhost:test01> create table stu(id int not null, name varchar(10) 
                             not null );                                        
Query OK, 0 rows affected
Time: 0.025s
mysql root@localhost:test01> insert into stu values(1,'a');                     
Query OK, 1 row affected
Time: 0.026s
mysql root@localhost:test01> insert into stu values(2,'b');                     
Query OK, 1 row affected
Time: 0.007s

# slave01 中以查询数据
mysql root@localhost:(none)> use test01;                                        
You are now connected to database "test01" as user "root"
Time: 0.001s
mysql root@localhost:test01> show tables;                                       
+------------------+
| Tables_in_test01 |
+------------------+
| stu              |
+------------------+
1 row in set
Time: 0.019s
mysql root@localhost:test01> select * from stu;                                 
+----+------+
| id | name |
+----+------+
| 1  | a    |
| 2  | b    |
+----+------+
2 rows in set
Time: 0.019s

4.搭建mycat

# 首先安装jdk
yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel

# 安装mycat
wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz 
mv mycat /usr/local/

1）mycat schema.xml配置文件

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
	</schema>
	<dataNode name="dn1" dataHost="host1" database="test01" />
  <!-- 搭建读写分离这里的balance必须改为1(双主双从)，或3(单主单从) -->
	<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<!-- 心跳检测 -->
    <heartbeat>select user()</heartbeat>
		<!-- 写库 -->
		<writeHost host="hostM1" url="localhost:3306" user="root"
				   password="123456">
			<!-- 读库 -->
			<readHost host="hostS2" url="localhost:3307" user="root" password="123456" />
		</writeHost>
	</dataHost>
</mycat:schema>

2) 运行mycat

./bin/mycat console

3) 连接mycat

mycli -h localhost -umycat -P8066
# 如果没有mycli命令可以通过python3的pip3下载, 也可以用mysql自带的mysql命令来连接。
pip3 install mycli

这里的连接需要看自己的mycat配置文件里的server.xml的内容：

4) 测试mycat

[root@ko ~]# mycli -h localhost -umycat -P8066
Password: 
mysql 5.7.23
mycli 1.20.1
Chat: https://gitter.im/dbcli/mycli
Mail: https://groups.google.com/forum/#!forum/mycli-users
Home: http://mycli.net
Thanks to the contributor - Daniel Black
mysql mycat@localhost:(none)> USE TESTDB;                                       
You are now connected to database "TESTDB" as user "mycat"
Time: 0.001s
mysql mycat@localhost:TESTDB> show tables;                                      
+------------------+
| Tables_in_test01 |
+------------------+
| stu              |
+------------------+
1 row in set
Time: 0.020s
mysql mycat@localhost:TESTDB> select * from stu;                                
+----+------+
| id | name |
+----+------+
| 1  | a    |
| 2  | b    |
+----+------+
2 rows in set
Time: 0.018s

经测试mycat读写分离已经搭建成功。