Mycat实现MySQL主从复制和读写分离
1 机器准备
2 mysql双主双从安装配置
2.1 mysql安装教程
2.2 配置和验证
3 mycat的安装
3.1 mycat的配置文件
3.2 验证服务
4 验证读写分离
4.1 验证写操作
4.2 验证读操作
5 主从切换
5.1 故障演示
5.2 恢复master节点
1 机器准备
image
规划：部署4个mysql数据库，其中mysql1 和mysql2 互为主从，mysql3是mysql1的从，mysql4是mysql2的从，这里数据库采用的mysql-5.7.27版本，mycat采用的是Mycat-server-1.6.7.6版本，mysql主从复制。
2 mysql双主双从安装配置
2.1 mysql安装
四台服务器上都需要安装mysql数据库，一下操作都需要做一遍，前提是已经上传了mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar 安装包
解压资源包，检查之前是否安装过其他版本的 mysql或者 mariadb，如果有则先删除。
#tar -xvf mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar -C /usr/local/src/
#rpm -qa mysql mariadb
#yum remove mysql-libs
#yum -y install perl net-tools
#cd /usr/local/src/
#ll
image.png
开始rpm安装mysql,rpm安装会自己初始化数据库；
# rpm -vih mysql-community-common-5.7.27-1.el7.x86_64.rpm
# rpm -vih mysql-community-libs-5.7.27-1.el7.x86_64.rpm
# rpm -vih mysql-community-libs-compat-5.7.27-1.el7.x86_64.rpm
# rpm -vih mysql-community-client-5.7.27-1.el7.x86_64.rpm
# rpm -vih mysql-community-server-5.7.27-1.el7.x86_64.rpm
# rpm -vih mysql-community-devel-5.7.27-1.el7.x86_64.rpm
# rpm -qa | grep mysql
# systemctl start mysqld
image.png
image.png
查看生成的root账号的初始密码
grep "temporary password" /var/log/mysqld.log
image.png
每个mysql服务器用初始密码登录后统一修改密码，并设置允许外部连接,然后重启服务。
# mysql -u root -p
mysql> show databases;
mysql> use mysql;
mysql> select User,authentication_string,Host from user;
mysql> alter user root@localhost identified by 'PassWod7&';
mysql> flush privileges;
image.png
image.png
设置允许外部连接；创建主从复制账号。
mysql> grant all privileges on *.* to "root"@"%" identified by "密码";
mysql> flush privileges;
mysql> grant replication slave on *.* to 'repl'@'%' identified by '密码';
mysql> flush privileges;
mysql> select User,authentication_string,Host from user;
image.png
# systemctl restart mysqld
2.1 修改配置文件
配置/etc/my.cnf，下面是mysql1上的配置文件内容，mysql2和mysql1上mysql的配置文件内容一致，只是要自增长初始值为2，和修改server-id参数即可。
# vim /etc/my.cnf
#配置内容如下：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=9 #取IP地址最后一段
gtid_mode=on #开启gtid模式
enforce_gtid_consistency=on
log-bin=mysql-bin
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
# character set
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
character-set-client-handshake=FALSE
# Default user
user=mysql
auto_increment_increment=2 #自动增长的步长
auto_increment_offset=1 #自动增长的起始数值
# slow query
slow_query_log=on
slow_query_log_file=/var/lib/mysql/slow-query.log
# Adjust as your needed
max_connections=1000
back_log=256
connect_timeout=10
key_buffer_size=16777216
innodb_buffer_pool_size=536870912
tmp_table_size=536870912
thread_cache_size=100
long_query_time=2
max_allowed_packet=200M
log-slave-updates=1
#log-slave-updates用来控制本机是否把所有的操作写入到binary log，默认的情况下mysql是关闭的;会导致自己作为从机时，自己的从机复制不到数据，需要设置为开启。
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
mysql2上的my.cnf内容
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=182
gtid_mode=on
enforce_gtid_consistency=on
log-bin=mysql-bin
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
# character set
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
character-set-client-handshake=FALSE
# Default user
user=mysql
auto_increment_increment=2 #自动增长的步长
auto_increment_offset=2 #自动增长的起始数值
# slow query
slow_query_log=on
slow_query_log_file=/var/lib/mysql/slow-query.log
max_connections=1000
back_log=256
connect_timeout=10
key_buffer_size=16777216
innodb_buffer_pool_size=536870912
tmp_table_size=536870912
thread_cache_size=100
long_query_time=2
max_allowed_packet=200M
log-slave-updates=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
在mysql1上登录数据库后，查看master的状态
mysql> show master status;
mysql>change master to master_host='192.168.119.182', master_user='repl', master_password='密码', master_port=3306, master_auto_position=1;
#启动slave
mysql>start slave;
mysql>show slave status\G
# 如果看到两个yes ,如下所示,则表示成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
image.png
在mysql2上登录数据库后，查看master的状态
mysql> show master status;
mysql>change master to master_host='192.168.119.9', master_user='repl', master_password='密码', master_port=3306, master_auto_position=1;
#启动slave
mysql>start slave;
mysql>show slave status\G
# 如果看到两个yes ,如下所示,则表示成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
image.png
在mysql3上登录数据库后，查看master的状态
mysql> show master status;
mysql>change master to master_host='192.168.119.9', master_user='repl', master_password='密码', master_port=3306, master_auto_position=1;
#启动slave
mysql>start slave;
mysql>show slave status\G
# 如果看到两个yes ,如下所示,则表示成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
image.png
在mysql4上登录数据库后，查看master的状态
mysql> show master status;
mysql>change master to master_host='192.168.119.182', master_user='repl', master_password='密码', master_port=3306, master_auto_position=1;
#启动slave
mysql>start slave;
mysql>show slave status\G
# 如果看到两个yes ,如下所示,则表示成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
