mycat mysql 一主从多

Alex_z0897

已于 2022-06-16 13:57:38 修改

阅读量458

点赞数

分类专栏：服务器文章标签： mysql 数据库 database

于 2020-11-11 22:23:16 首次发布

本文链接：https://blog.csdn.net/zoeou/article/details/109632316

版权

服务器专栏收录该内容

25 篇文章 0 订阅

订阅专栏

启动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';

完成…

Alex_z0897

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
mycat mysql 一主从多

1. 启动mysql主,mysql从,mycat服务2. 创建备份用户repl3. 查看当position, 主库未被写入时,可通过show master status查看4. 切换到从库, 编写CHANGE MASTER TO 相关配置语句,并执行5. 从库执行start slave,通过show slave status;查看主从同步状态6. mycat配置数据库连接7. mycat配置重新加载...
复制链接

扫一扫