- 启动mysql主,mysql从,mycat服务
- 创建备份用户repl
- 查看当position, 主库未被写入时,可通过show master status查看
- 切换到从库, 编写CHANGE MASTER TO 相关配置语句,并执行
- 从库执行start slave,通过show slave status;查看主从同步状态
- mycat配置数据库连接
- mycat配置重新加载
运行mycat 和mysql主从
$ docker-compose config
services:
m1:
environment:
MYSQL_ROOT_PASSWORD: '123456'
TZ: Asia/Shanghai
image: mysql:5.7.26
ports:
- 32808:3306/tcp
volumes:
- ./mycat/data/m1:/var/lib/mysql:rw
- ./mycat/mysql/master.cnf:/etc/mysql/conf.d/master.cnf:rw
mycat:
depends_on:
- m1
- s1
- s2
image: mycat:latest
ports:
- 8066:8066/tcp
- 9066:9066/tcp
volumes:
- ./mycat/conf:/usr/local/mycat/conf:ro
- ./mycat/logs:/usr/local/mycat/logs:rw
s1:
environment:
MYSQL_ROOT_PASSWORD: '123456'
TZ: Asia/Shanghai
image: mysql:5.7.26
ports:
- 32807:3306/tcp
volumes:
- ./mycat/data/s1:/var/lib/mysql:rw
- ./mycat/mysql/slave-1.cnf:/etc/mysql/conf.d/slave.cnf:rw
s2:
depends_on:
- m1
environment:
MYSQL_ROOT_PASSWORD: '123456'
TZ: Asia/Shanghai
image: mysql:5.7.26
ports:
- 32809:3306/tcp
volumes:
- ./mycat/data/s2:/var/lib/mysql:rw
- ./mycat/mysql/slave-2.cnf:/etc/mysql/conf.d/slave.cnf:rw
version: '3.0'
配置master
创建用户
获取boglog Position
mysql> create user 'repl'@'%' identified by 'repl';
mysql> grant replication slave on *.* to 'repl'@'%';
mysql> show master status;#如主库正在使用,得先备份出来,在备份文件里看position
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 595 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysqld.conf
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-ignore-db = mysql
#省略其它配置...
配置slave
查看slave状态
使用CHANGE MASTER TO语句,进行主从复制
开启复制线程start slave
master_host :m1 使用容器名访问master,或者可以在docker-compose里配置docker网络固定ip配置
mysql> show slave status\G
Empty set (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='m1',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=595 ;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: m1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 595
Relay_Log_File: 5bb2af6fa29b-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
略....
成功与否主要看
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
都是yes,就代表成功
配置mycat
schema.xml
略....
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" />
<!--配置多个表,对应dn1也要增加一条,db1 可以共用 schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" /-->
<dataNode name="dn1" dataHost="dh1" database="TESTDB" />
<dataHost name="dh1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="m1" url="m1:3306" user="root" password="123456">
<readHost host="s1" url="s1:3306" user="root" password="123456" />
<readHost host="s2" url="s2:3306" user="root" password="123456" />
</writeHost>
</dataHost>
略....
balance:
1. balance=“0”,不开启读写分离,所有读操作都发送到当前可用的writeHost上。
2. balance=“1”,所有读操作都随机的发送到第一个writeHost以外的所有节点上。
3. balance=“2”,所有读操作都随机的在writeHost、readhost上分发。
4. balance=“3”,所有读操作都随机的在readhost上分发。
writeType
负载均衡类型,目前的取值有3种:
1、writeType=”0″, 所有写操作发送到配置的第一个writeHost。
2、writeType=”1″,所有写操作都随机的发送到配置的writeHost。
3、writeType=”2″,不执行写操作。
switchType
1、switchType=-1 表示不自动切换
2、switchType=1 默认值,自动切换
3、switchType=2 基于MySQL 主从同步的状态决定是否切换
service.xml
略....
<user name="root">
<property name="password">123456</property>
<property name="schemas">TESTDB,TESTDB2</property> //这里可以配置多个表,
</user>
略....
使mycat配置生效
如果配置在mycat启动之后有变更,方法如下:
$ docker exec -it mycat_1 bash
container $: mysql -uroot -p -P9066 -h127.0.0.1
mysql> reload @@config all; //会加载全局配置文件
报错1:can’t get connection for sql :show slave status
mycat log里报这个错,slave配置有问题
报错2: schema 表名 didn’t config tables,so you must set dataNode property!
mycat配置xml格式有错
报错3: ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL ‘’ first
slave线程正在执行同步, 必须先停止执行 -> STOP SLAVE IO_THREAD;
主库停机时:
应用连接数据库报错:
1053, u’Server shutdown in progress
2003, "Can’t connect to MySQL server on ‘xxx.xxx.xxx.xxx’
从库同步时报错:
error reconnecting to master ‘repl@m1:3306’ - retry-time: 60 retries: 14
stop slave;
show slave status\G;
reset slave;
参数 --read_only=1
1290 - The MySQL server is running with the --read-only option so it cannot execute this statement
只读read_only
1142 - DELETE command denied to user ‘user1’@‘xxx.xxx.x.xxx’ for table ‘project’
mysql主从复制原理: 对master节点进行写操作,当前操作会被保存到mysql的binary-log(bin-log)日志中,当slave连接到master时,master机器会为slave开启binlogDump线程,当master的binlog发生变化时,dump线程会通知slave,并将相应的binlog内容发送给slave,而slave节点在主从同步开启时,会创建两个线程,I/O线程和SQL线程,
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
MASTER_HOST写错了
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not open log file’
主库对应binlog文件
清除master历史binlog
查询当前master文件
$ show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 154 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
截断当前binlog
$ flush logs
Query OK, 0 rows affected (0.04 sec)
清除 mysql-bin.000017之前的记录
$ PURGE MASTER LOGS TO 'mysql-bin.000017';
完成…