mysql的高可用集群

环境设定

server3172.25.34.4主masterserver4172.25.34.5从master，slaveserver6172.25.34.6slave

server3:

安装mysql

[root@server3 ~]# tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar [root@server3 ~]# yum install -y mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm

编辑配置文件

[root@server3 mysql]# vim /etc/my.cnf server_id=1 gtid_mode=ON enforce_gtid_consistency=ON log_slave_update=ON log_bin=binlog

初始化

[root@server4 mysql]# cat /var/log/mysqld.log | grep password [root@server4 mysql]# mysql_secure_installation

进入数据库

[root@server3 mysql]# mysql -uroot -pWestos+001 mysql> grant replication slave on *.* to dd@'172.25.34.%' identified by 'Westos+001'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show master status -> ; +---------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+------------------------------------------+ | binlog.000002 | 689 | | | 81ace76c-f792-11e9-a2a6-5254006dc583:1-2 | +---------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)

server4:

执行安装及相关操作

编辑配置文件

[root@server4 mysql]# vi /etc/my.cnf server_id=2 gtid_mode=ON enforce_gtid_consistency=ON log_slave_updates=ON log_bin=binlog

进入 数据库

[root@server4 mysql]# mysql -uroot -pWestos+001 mysql> change master to ##指定master -> master_host = '172.25.34.4', -> master_user = 'dd', -> master_password = 'Westos+001', -> master_auto_position = 1; Query OK, 0 rows affected, 2 warnings (0.04 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.25.34.4 Master_User: dd Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 689 Relay_Log_File: server4-relay-bin.000002 Relay_Log_Pos: 896 Relay_Master_Log_File: binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes

server5:

与之前在server4上的操作相同

编辑配置文件

[root@server5 ~]# vi /etc/my.cnf server_id=3 gtid_mode =ON enforce-gtid-consistency=true log_bin = mysql-log log_slave_updates=ON

进入数据库

[root@server5 mysql]# mysql -uroot -pWestos+001 mysql> change master to -> master_host = '172.25.34.4', -> master_user = 'dd', -> master_password = 'Westos+001', -> master_auto_position = 1; Query OK, 0 rows affected, 2 warnings (0.04 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.25.34.4 Master_User: dd Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 689 Relay_Log_File: server4-relay-bin.000002 Relay_Log_Pos: 896 Relay_Master_Log_File: binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes

测试环境是否搭建成功

server3上创建数据库:

mysql> create database test; mysql> use test; mysql> create table userlist( -> username varchar(15) not null, -> password varchar(25) not null); mysql> insert into userlist values ('user1','111'); mysql> insert into userlist values ('user1','222');

server 4，server5上查看数据是否同步:

mysql> use test; mysql> select * from userlist;

同步成功就说明一主二从环境搭建成功

现在模拟主master宕机

[root@server3 mysql]# systemctl stop mysqld

在从server4上查看

mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.25.34.4 Master_User: dd Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 689 Relay_Log_File: server4-relay-bin.000002 Relay_Log_Pos: 896 Relay_Master_Log_File: binlog.000002 Slave_IO_Running: connecting Slave_SQL_Running: Yes mysql> insert into userlist values ('user2','222'); # 在server4上插入数据 mysql> select * from userlist;

在server5上查看是否同步

mysql> select * from userlist; # 成功同步

重新开启主master

[root@server3 mysql]# systemctl start mysqld [root@server3mysql]# mysql -p Enter password: mysql> use test; Database changed mysql> select * from userlist; # 不能同步从master上的数据

恢复主master地位

在从master上server4

mysql> stop slave; mysql> start slave; mysql> show slave status\G;