mysql 安装参靠一主一从
192.168.10.30 主
192.168.10.204 主
192.168.10.31 从
192.168.10.50 从
搭建完成修改配主从配置
30主
vim /etc/my.conf
[mysqld]
variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#主服务器唯一ID
server-id = 1
# 启动二进制日志
log-bin = mysql-bin
# 设置不要复制的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_scheam
#需要复制的库
binlog-do-db = mydb1
# 设置binlog格式
binlog_format = STATEMENT
#作为从数据库的时候,有些如操作也需要更新二进制日志
log-slave-updates
#自增长每次递增的量 ,指自增字段的起始值,默认1 取值1-65535
auto-increment-increment=2
#从哪里开始,递增多少 取值1-65535
auto-increment-offset =1
31 从
vim /etc/my.conf
[mysqld]
variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id =2
relay-log =mysql-relay
204 主
vim /etc/my.conf
[mysqld]
variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 主服务器唯一ID
server-id = 3
# 启动二进制日志
log-bin = mysql-bin
# 设置不要复制的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_scheam
#需要复制的库
binlog-do-db = mydb1
# 设置binlog格式
binlog_format = STATEMENT
#作为从数据库的时候,有些如操作也需要更新二进制日志
log-slave-updates
#自增长每次递增的量 ,指自增字段的起始值,默认1 取值1-65535
auto-increment-increment=2
#从哪里开始,递增多少 取值1-65535
auto-increment-offset =2
50从
vim /etc/my.conf
[mysqld]
variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id =4
relay-log =mysql-relay
配置完成后重启四台mysql
systemctl restart mysqld
集群复制配置
查看 30 状态
show master status;
查看204 状态
31 复制30 ,在31执行
CHANGE MASTER TO MASTER_HOST='192.168.10.30',
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=157;
50复制204 ,在50 执行
CHANGE MASTER TO MASTER_HOST='192.168.10.204',
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=157;
30复制204, 在30执行
CHANGE MASTER TO MASTER_HOST='192.168.10.204',
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=157;
204复制30 在204执行
CHANGE MASTER TO MASTER_HOST='192.168.10.30',
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=157;
以上的日志文件 和pos必须跟要复制的机器保持一致。
测试
204 主 执行创建数据库 表
30主执行 插入
31 从显示
50从显示
mycat2 配置
cat /usr/local/mycat/conf/datasources/rr0.datasource.json
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"rr0",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://192.168.10.31:3306/mydb1?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"root",
"weight":0
}
cat /usr/local/mycat/conf/datasources/rr1.datasource.json
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"rr1",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://192.168.10.50:3306/mydb1?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"root",
"weight":0
}
cat /usr/local/mycat/conf/datasources/rw0.datasource.json
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"rw0",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://192.168.10.30:3306/mydb1?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"root",
"weight":0
}
cat /usr/local/mycat/conf/datasources/rw1.datasource.json
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"rw1",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://192.168.10.204:3306/mydb1?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"root",
"weight":0
}
cat /usr/local/mycat/conf/clusters/mycluster.cluster.json
{
"clusterType":"GARELA_CLUSTER",
"heartbeat":{
"heartbeatTimeout":1000,
"maxRetry":3,
"minSwitchTimeInterval":300,
"slaveThreshold":0
},
"masters":[
"rw0","rw1"
],
"replicas":["rr0","rr1"],
"maxCon":2000,
"name":"mycluster",
"readBalanceType":"BALANCE_ALL",
"switchType":"SWITCH"
}
cat /usr/local/mycat/conf/schemas/mydb1.schema.json
{
"customTables":{},
"globalTables":{},
"normalProcedures":{},
"normalTables":{},
"schemaName":"mydb1",
"shardingTables":{},
"targetName":"mycluster",
"views":{}
}
重启mycat
测试
mycat插入
31查询
204查询
50查询