主从同步
一、主从同步介绍
数据库复制技术
可以将一个MySQL数据库的变更操作同步到另外一个或多个MySQL数据库中，分为2种角色：
主（master）服务器：接受客户端访问连接
从（slave）服务器：自动同步主服务器数据
二、主从同步原理
1、Master
启用binlog日志，记录所有的数据库更新和修改操作
2、Slave
Slave_IO：复制master主机 binlog 日志文件里的SQL命令到本机的relay-log文件里
Slave_SQL：执行本机relay-log文件里的SQL语句，实现与Master数据一致
三、主从同步结构
- 单向复制（一主一从）
- 一主多从
- 链式复制（主从从）
- 互为主从
构建主从同步
一、构建思路
1、配置主服务器
- 启用binlog日志、授权用户、查看binlog日志信息
2、配置从服务器
- 设置server_id并重启数据库服务
- 指定主服务器信息
- 启动slave进程
- 查看状态信息
二、主配置
1、安装配置
[root@mysql53 ~]# yum -y install mysql mysql-server
[root@mysql53 ~]# systemctl enable mysqld --now
[root@mysql53 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=53
log-bin=mysql53
... ...
[root@mysql53 ~]# systemctl restart mysqld.service
[root@mysql53 ~]# mysql
mysql> create user hmy@"%" identified by '123456';
mysql> grant replication slave on *.* to hmy@"%";
mysql> flush privileges;
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql53.000001 | 824 | | | |
+----------------+----------+--------------+------------------+-------------------+
mysql> quit
三、从配置
1、安装配置
[root@mysql54 ~]# yum -y install mysql mysql-server
[root@mysql54 ~]# systemctl enable mysqld --now
[root@mysql54 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=54
... ...
[root@mysql54 ~]# systemctl restart mysqld.service
[root@mysql54 ~]# mysql
mysql> change master to master_host="192.168.88.53",master_user="hmy",master_password="123456",master_log_file="mysql53.000001",master_log_pos=824;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.88.53
Master_User: hmy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql53.000001
Read_Master_Log_Pos: 824
Relay_Log_File: mysql54-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql53.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: 824
Relay_Log_Space: 533
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: 53
Master_UUID: f33de6ed-ecd7-11ee-9a09-525400c19084
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
四、测试验证
[root@mysql53 ~]# mysql
mysql> create user test@"%" identified by 'test';
mysql> grant all on testdb.* to test@"%";
# 客户端连接主服务器存储数据
[root@client50 ~]# yum -y install mysql
[root@client50 ~]# mysql -h192.168.88.53 -utest -ptest
mysql> create database testdb;
mysql> create table testdb.user(
-> name char(10),
-> age char(2)
-> );
mysql> insert into testdb.user values("haha","99");
mysql> select * from testdb.user;
+------+------+
| name | age |
+------+------+
| haha | 99 |
+------+------+
# 客户端连接从服务器查看数据
[root@client50 ~]# mysql -h192.168.88.54 -utest -ptest -e 'select * from testdb.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| name | age |
+------+------+
| haha | 99 |
+------+------+构建一主多从
一、从安装配置
1、指定server-id，重启服务
[root@mysql55 ~]# yum -y install mysql mysql-server
[root@mysql55 ~]# systemctl enable mysqld --now
[root@mysql55 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=55
... ...
[root@mysql55 ~]# systemctl restart mysqld.service
2、确保与主服务器数据一致
# 在mysql53执行备份命令前查看日志名和偏移量 ，mysql55 在当前查看到的位置同步数据
[root@mysql53 ~]# mysql -e 'show master status'
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql53.000001 | 2030 | | | |
+----------------+----------+--------------+------------------+-------------------+
# 在主服务器存做完全备份
[root@mysql53 ~]# mysqldump -B testdb > /root/testdb.sql
# 主服务器把备份文件拷贝给从服务器mysql55
[root@mysql53 ~]# scp /root/testdb.sql 192.168.88.55:/root
[root@mysql55 ~]# mysql < /root/testdb.sql
3、指定主服务器信息
[root@mysql55 ~]# mysql
mysql> change master to master_user="hmy",master_host="192.168.88.53",master_password="123456",master_log_file="mysql53.000001",master_log_pos=2030;
-----------------------------------------------------------------------------------
注意:日志名和偏移量 要写在mysql53主机执行完全备份之前查看到的日志名和偏移量
-----------------------------------------------------------------------------------
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.88.53
Master_User: hmy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql53.000001
Read_Master_Log_Pos: 2030
Relay_Log_File: mysql55-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql53.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: 2030
Relay_Log_Space: 533
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: 53
Master_UUID: f33de6ed-ecd7-11ee-9a09-525400c19084
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
# 添加客户端访问时使用的用户
mysql> create user test@"%" identified by 'test';
mysql> grant all on testdb.user to test@"%";
二、测试验证
1、client连接master存储数据
[root@client50 ~]# mysql -h192.168.88.53 -utest -ptest
mysql> insert into testdb.user values("hehe","66");
mysql> insert into testdb.user values("lala","88");
2、client分别连接2个从服务器查看数据
[root@client50 ~]# mysql -h192.168.88.54 -utest -ptest -e 'select * from testdb.user';
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| name | age |
+------+------+
| haha | 99 |
| hehe | 66 |
| lala | 88 |
+------+------+
[root@client50 ~]# mysql -h192.168.88.55 -utest -ptest -e 'select * from testdb.user';
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| name | age |
+------+------+
| haha | 99 |
| hehe | 66 |
| lala | 88 |
+------+------+读写分离
一、中间件软件
运行在应用程序和数据库之间的软件，通过拦截和处理数据库请求来实现读写分离、负载均衡、故障切换等功能
| 软件 | 介绍 |
| MySQL Proxy | 代理服务器 |
| MaxScale | 开源的MySQL数据库代理服务器 |
| Mycat2 | 阿里巴巴旗下的一个项目 |
二、拓扑
添加一个MySQL代理
接收客户端访问，为客户端提供访问数据接口
三、原理
客户端连接mycat服务，访问数据库服务
收到SQL写请求时，交给master服务器处理
收到SQL读请求时，交给slave服务器处理
四、构建思路
1、部署MySQL一主一从
2、部署mycat服务
装包、修改配置文件、配置数据库服务、启动服务
3、添加主机
添加master主机
添加slave主机
五、部署MyCAT服务
1、安装软件
# 安装jdk
[root@proxy ~]# yum -y install java-1.8.0-openjdk.x86_64
# 安装解压命令
[root@proxy ~]# which unzip || yum -y install unzip
# 安装mycat
[root@proxy ~]# unzip mycat2-install-template-1.21.zip
[root@proxy ~]# mv mycat /usr/local/
# 安装依赖
[root@proxy ~]# cp mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/
# 修改权限
[root@proxy ~]# chmod -R 777 /usr/local/mycat/
2、修改配置文件
定义客户端连接mycat服务使用的用户名及密码
[root@proxy ~]# vim /usr/local/mycat/conf/users/root.user.json
{
"dialect":"mysql",
"ip":null,
"password":"123456",
"transactionType":"proxy",
"username":"mycat"
}
指定mycat服务启动时连接的数据库服务器
[root@proxy ~]# vim /usr/local/mycat/conf/datasources/prototypeDs.datasource.json
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"prototypeDs",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"hmy",
"weight":0
}
配置数据库服务器
[root@proxy ~]# yum -y install mysql mysql-server
[root@proxy ~]# systemctl enable mysqld --now
[root@proxy ~]# mysql
mysql> create user hmy@"%" identified by '123456';
mysql> grant all on *.* to hmy@"%";
启动mycat服务
[root@proxy ~]# /usr/local/mycat/bin/mycat help
Usage: /usr/local/mycat/bin/mycat { console | start | stop | restart | status | dump }
[root@proxy ~]# /usr/local/mycat/bin/mycat start
Starting mycat2...
[root@proxy ~]# ss -ntulp | grep 8066
tcp LISTEN 0 128 *:8066 *:* users:(("java",pid=22523,fd=71))连接mycat服务
[root@proxy ~]# mysql -h127.0.0.1 -P8066 -umycat -p123456
mysql> show databases;
+--------------------+
| `Database` |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.11 sec)
3、配置读写分离
添加数据源：连接mycat服务后做如下操作
# 连接mycat服务
[root@proxy ~]# mysql -h127.0.0.1 -P8066 -umycat -p123456
# 添加master数据库服务器
mysql> /*+ mycat:createdatasource{
-> "name":"whost56", "url":"jdbc:mysql://192.168.88.56:3306","user":"hmy","password":"123456"}*/;
# 添加slave数据库服务器
mysql> /*+ mycat:createdatasource{
-> "name":"rhost57", "url":"jdbc:mysql://192.168.88.57:3306","user":"hmy","password":"123456"}*/;
# 查看数据源
mysql> /*+mycat:showDataSources{}*/ \G
*************************** 1. row ***************************
NAME: rhost57
USERNAME: hmy
PASSWORD: 123456
MAX_CON: 1000
MIN_CON: 1
EXIST_CON: 0
USE_CON: 0
MAX_RETRY_COUNT: 5
MAX_CONNECT_TIMEOUT: 30000
DB_TYPE: mysql
URL: jdbc:mysql://192.168.88.57:3306?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8&autoReconnect=true
WEIGHT: 0
INIT_SQL:
INIT_SQL_GET_CONNECTION: true
INSTANCE_TYPE: READ_WRITE
IDLE_TIMEOUT: 60000
DRIVER: {
CreateTime:"2024-03-28 19:30:09",
ActiveCount:0,
PoolingCount:0,
CreateCount:0,
DestroyCount:0,
CloseCount:0,
ConnectCount:0,
Connections:[
]
}
TYPE: JDBC
IS_MYSQL: true
*************************** 2. row ***************************
NAME: whost56
USERNAME: hmy
PASSWORD: 123456
MAX_CON: 1000
MIN_CON: 1
EXIST_CON: 0
USE_CON: 0
MAX_RETRY_COUNT: 5
MAX_CONNECT_TIMEOUT: 30000
DB_TYPE: mysql
URL: jdbc:mysql://192.168.88.56:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true
WEIGHT: 0
INIT_SQL:
INIT_SQL_GET_CONNECTION: true
INSTANCE_TYPE: READ_WRITE
IDLE_TIMEOUT: 60000
DRIVER: {
CreateTime:"2024-03-28 19:30:09",
ActiveCount:0,
PoolingCount:0,
CreateCount:0,
DestroyCount:0,
CloseCount:0,
ConnectCount:0,
Connections:[
]
}
TYPE: JDBC
IS_MYSQL: true
*************************** 3. row ***************************
NAME: prototypeDs
USERNAME: hmy
PASSWORD: 123456
MAX_CON: 1000
MIN_CON: 1
EXIST_CON: 0
USE_CON: 0
MAX_RETRY_COUNT: 5
MAX_CONNECT_TIMEOUT: 3000
DB_TYPE: mysql
URL: jdbc:mysql://localhost:3306/mysql?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true
WEIGHT: 0
INIT_SQL:
INIT_SQL_GET_CONNECTION: true
INSTANCE_TYPE: READ_WRITE
IDLE_TIMEOUT: 60000
DRIVER: {
CreateTime:"2024-03-28 19:30:09",
ActiveCount:0,
PoolingCount:0,
CreateCount:0,
DestroyCount:0,
CloseCount:0,
ConnectCount:0,
Connections:[
]
}
TYPE: JDBC
IS_MYSQL: true
3 rows in set (0.02 sec)
# 添加的数据源以文件的形式保存在安装目录下
[root@proxy ~]# ls /usr/local/mycat/conf/datasources/
prototypeDs.datasource.json rhost57.datasource.json whost56.datasource.json配置数据库服务器添加hmy用户
# 在master服务器添加
[root@master ~]# mysql
mysql> create user hmy@"%" identified by '123456';
mysql> grant all on *.* to hmy@"%";
# 在slave服务器查看是否同步成功
[root@slave ~]# mysql -e 'select user,host from mysql.user where user="hmy"'
+------+------+
| user | host |
+------+------+
| hmy | % |
+------+------+创建集群，连接mycat服务后做如下配置
[root@proxy ~]# mysql -h127.0.0.1 -P8066 -umycat -p123456
# 创建集群
mysql> /*!mycat:createcluster{
-> "name":"rwcluster",
-> "masters":["whost56"],
-> "replicas":["rhost57"]
-> }*/ ;
# 查看集群信息
mysql> /*+ mycat:showClusters{}*/ \G
*************************** 1. row ***************************
NAME: rwcluster
SWITCH_TYPE: SWITCH
MAX_REQUEST_COUNT: 2000
TYPE: BALANCE_ALL
WRITE_DS: whost56
READ_DS: whost56,rhost57
WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
AVAILABLE: true
*************************** 2. row ***************************
NAME: prototype
SWITCH_TYPE: SWITCH
MAX_REQUEST_COUNT: 200
TYPE: BALANCE_ALL
WRITE_DS: prototypeDs
READ_DS: prototypeDs
WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
AVAILABLE: true
2 rows in set (0.01 sec)
# 创建的集群以文件的形式保存在目录下
[root@proxy ~]# ls /usr/local/mycat/conf/clusters/
prototype.cluster.json rwcluster.cluster.json指定主机角色
# 修改master角色主机仅负责写访问
[root@proxy ~]# vim /usr/local/mycat/conf/datasources/whost56.datasource.json
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"WRITE", # 仅负责写访问
"logAbandoned":true,
"maxCon":1000,
"maxConnectTimeout":30000,
"maxRetryCount":5,
"minCon":1,
"name":"whost56",
"password":"123456",
"queryTimeout":0,
"removeAbandoned":false,
"removeAbandonedTimeoutSecond":180,
"type":"JDBC",
"url":"jdbc:mysql://192.168.88.56:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
"user":"hmy",
"weight":0
}
# 修改slave角色主机仅负责读访问
[root@proxy ~]# vim /usr/local/mycat/conf/datasources/rhost57.datasource.json
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ", # 仅负责读访问
"logAbandoned":true,
"maxCon":1000,
"maxConnectTimeout":30000,
"maxRetryCount":5,
"minCon":1,
"name":"rhost57",
"password":"123456",
"queryTimeout":0,
"removeAbandoned":false,
"removeAbandonedTimeoutSecond":180,
"type":"JDBC",
"url":"jdbc:mysql://192.168.88.57:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
"user":"hmy",
"weight":0
}
定义集群处理读访问的策略
[root@proxy ~]# vim /usr/local/mycat/conf/clusters/rwcluster.cluster.json
{
"clusterType":"MASTER_SLAVE",
"heartbeat":{
"heartbeatTimeout":1000,
"maxRetryCount":3,
"minSwitchTimeInterval":300,
"showLog":false,
"slaveThreshold":0.0
},
"masters":[
"whost56"
],
"maxCon":2000,
"name":"rwcluster",
"readBalanceType":"BALANCE_ALL_READ",
"replicas":[
"rhost57"
],
"switchType":"SWITCH"
}
# 重启mycat服务
[root@proxy ~]# /usr/local/mycat/bin/mycat restart
Stopping mycat2...
Stopped mycat2.
Starting mycat2...
[root@proxy ~]# ss -ntulp | grep 8066
tcp LISTEN 0 128 *:8066 *:* users:(("java",pid=22810,fd=71))4、测试配置
思路如下：
连接mycat服务建库
指定存储数据使用的集群
连接mycat服务建表
客户端连接mycat服务执行select或insert
[root@proxy ~]# mysql -h127.0.0.1 -P8066 -umycat -p123456
mysql> create database testdb;
# 指定testdb库存储数据使用的集群
[root@proxy ~]# vim /usr/local/mycat/conf/schemas/testdb.schema.json
{
"customTables":{},
"globalTables":{},
"normalProcedures":{},
"normalTables":{},
"schemaName":"testdb",
"targetName":"rwcluster", # 添加此行,之前创建的集群名rwcluster
"shardingTables":{},
"views":{}
}
[root@proxy ~]# /usr/local/mycat/bin/mycat restart
Stopping mycat2...
Stopped mycat2.
Starting mycat2...
[root@proxy ~]# ss -ntulp | grep 8066
tcp LISTEN 0 128 *:8066 *:* users:(("java",pid=23120,fd=72))
# 连接mycat服务建表插入记录
[root@proxy ~]# mysql -h127.0.0.1 -P8066 -umycat -p123456
mysql> create table testdb.user(
-> name varchar(10),
-> password varchar(10)
-> );
mysql> insert into testdb.user values("haha","123456");
mysql> select * from testdb.user;
+------+----------+
| name | password |
+------+----------+
| haha | 123456 |
+------+----------+
1 row in set (0.01 sec)
测试读写分离
# 在从服务器本机插入记录，数据仅在从服务器有，主服务器没有
[root@slave ~]# mysql -e 'insert into testdb.user values("xixi","654321")'
[root@slave ~]# mysql -e 'select * from testdb.user'
+------+----------+
| name | password |
+------+----------+
| haha | 123456 |
| xixi | 654321 |
+------+----------+
# 主服务器数据不变，日志偏移量不不变
[root@master ~]# mysql -e 'select * from testdb.user'
+------+----------+
| name | password |
+------+----------+
| haha | 123456 |
+------+----------+
[root@master ~]# mysql -e 'show master status'
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql56.000001 | 1981 | | | |
+----------------+----------+--------------+------------------+-------------------+
# 客户端连接mycat服务读/写数据
[root@client50 ~]# yum -y install mysql
[root@client50 ~]# mysql -h192.168.88.58 -P8066 -umycat -p123456
# 查看到的是2条记录的行
mysql> select * from testdb.user;
+------+----------+
| name | password |
+------+----------+
| haha | 123456 |
| xixi | 654321 |
+------+----------+
2 rows in set (0.05 sec)
# 插入记录
mysql> insert into testdb.user values("gaga","000000");
mysql> select * from testdb.user;
+------+----------+
| name | password |
+------+----------+
| haha | 123456 |
| gaga | 000000 |
+------+----------+
2 rows in set (0.01 sec)
# 在主服务器查看数据和日志偏移量
[root@master ~]# mysql -e 'select * from testdb.user'
+------+----------+
| name | password |
+------+----------+
| haha | 123456 |
| gaga | 000000 |
+------+----------+
[root@master ~]# mysql -e 'show master status'
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql56.000001 | 2273 | | | |
+----------------+----------+--------------+------------------+-------------------+
# 客户端连接mycat服务查看到的是3条记录
[root@client50 ~]# mysql -h192.168.88.58 -P8066 -umycat -p123456 -e 'select * from testdb.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+----------+
| name | password |
+------+----------+
| haha | 123456 |
| xixi | 654321 |
| gaga | 000000 |
+------+----------+
排错方法
Last_IO_Error: IO线程报错信息
报错的原因：通常是 change master to 的配置项不正确
解决办法：重新指定主服务器信息
stop slave;
change master to 服务器ip 用户 密码 日志文件名 偏移量;
start slave;
Last_SQL_Error: SQL线程报错信息
通常是执行relay-log文件里的sql命令时失败导致的
779
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
