Mycat实现MySQL主从复制和读写分离----mysql双主双从安装配置

m0_67505608

于 2022-03-16 15:42:10 发布

阅读量167

点赞数

文章标签： mysql mariadb 数据库

原文链接：https://www.jianshu.com/p/71cf1643e74d

版权

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 机器准备

规划：部署4个mysql数据库，其中mysql1 和mysql2 互为主从，mysql3是mysql1的从，mysql4是mysql2的从，这里数据库采用的mysql-5.7.27版本，mycat采用的是Mycat-server-1.6.7.6版本。

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

开始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

查看生成的root账号的初始密码

grep "temporary password" /var/log/mysqld.log

每个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;

设置允许外部连接；创建主从复制账号。

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;

# 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

在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

在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

在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