Centos7 mysql Mutual Master Slave + keepalived
I. Mutual master-slave configuration
1.1. Resource situation
192.168.11.177 server1
192.168.11.180 server2
192.168.11.210 VIP
1.2, server1 configuration
# vi / etc/my.cnf
[mysqld]
Server-id=1
Log-bin=mysql-bin
Relay_log=mysql-realy-bin
Relay_log_index=slave-mysql-realy-bin.index
Expire_logs_days=15
Binlog_format=mixed
Auto-increment-increment=2
Auto-increment-offset= 1
Innodb_flush_log_at_trx_commit=1
Replicate-ignore-db=sys
Replicate-ignore-db=mysql
Replicate-ignore-db=information_schema
Replicate-ignore-db=performance_schema
Datadir=/data/mysql_data
Socket=/var/lib/mysql/mysql.sock
Character_set_server=utf8
Sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
# Disabling symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0
Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid
# innodb optimization
Innodb_buffer_pool_size=8G
Innodb_log_file_size=256M
Innodb_flush_method=O_DIRECT
Max_connections=500
Innodb_autoextend_increment=128
1.3The server2 configuration
# vi / etc/my.cnf
Server-id=2
Log-bin=mysql-bin
Relay_log=mysql-realy-bin
Relay_log_index=slave-realy-bin.index
Expire_logs_days=15
Binlog_format=mixed
Auto-increment-increment=2
Auto-increment-offset= 2
Innodb_flush_log_at_trx_commit=1
Replicate-ignore-db=sys
Replicate-ignore-db=mysql
Replicate-ignore-db=information_schema
Replicate-ignore-db=performance_schema
Datadir=/data/mysql_data
Socket=/var/lib/mysql/mysql.sock
Character_set_server=utf8
Sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
# Disabling symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0
Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid
# innodb optimization
Innodb_buffer_pool_size=8G
Innodb_log_file_size=256M
Innodb_flush_method=O_DIRECT
Max_connections=500
Innodb_autoextend_increment=128
1.4. Change the account password on server1 and server2 to create a synchronous account
# mysql-p
Mysql > set password=password ('*')
Mysql > create user repluser@'%' identified by'*'
Mysql > grant replication slave, replication client on *. * to repluser@'%'
1.5. Establish synchronization with server2
Master status of server1
Mysql > show master status
+-+
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |
+-+
| | mysql-bin.000003 | 882 | |
+-+
Mysql > CHANGE MASTER TO MASTER_HOST='192.168.11.177', MASTER_USER='repluser', MASTER_PASSWORD='*', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=882
Query OK, 0 rows affected, 2 warnings (0.10 sec)
Mysql > show slave status\ G
* * 1. Row *
Slave_IO_State:
Master_Host: 192.168.11.177
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 882
Relay_Log_File: mysql-realy-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: sys,mysql,information_schema,performance_schema
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: 882
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: / data/mysql_data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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:
1 row in set (0.00 sec)
# vi / data/mysql_data/auto.cnf
[auto]
Server-uuid=cbcefb67-9f9a-11e8-91b3-06ba24001d86
>
Server-uuid=cbcefb77-9f9a-11e8-91b3-06ba24001d86
Mysql > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.11.177
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 882
Relay_Log_File: mysql-realy-bin.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: sys,mysql,information_schema,performance_schema
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: 882
Relay_Log_Space: 527
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: 1
Master_UUID: cd146946-9f95-11e8-9a29-063696001d83
Master_Info_File: / data/mysql_data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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:
1 row in set (0.00 sec)
Mysql > start slave
Mysql > show master status
+-+
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |
+-+
| | mysql-bin.000003 | 154 | |
+-+
1.6. establish synchronization with server1
Mysql > CHANGE MASTER TO MASTER_HOST='192.168.11.180', MASTER_USER='repluser', MASTER_PASSWORD='*', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154
Query OK, 0 rows affected, 2 warnings (0.22 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: 192.168.11.180
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-realy-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: sys,mysql,information_schema,performance_schema
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: 154
Relay_Log_Space: 527
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: 2
Master_UUID: cbcefb67-9f9a-11e8-91b3-06ba24001d86
Master_Info_File: / data/mysql_data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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:
1 row in set (0.00 sec)
Mysql > create database ceshi_db
Query OK, 1 row affected (0.01sec)
Mysql > use ceshi_db1
Database changed
Mysql > create table home (id int (10) not null,name char (10))
Query OK, 0 rows affected (0.14 sec)
1.7. Server2 verification
Mysql > show databases
+-+
| | Database |
+-+
| | information_schema |
| | ceshi_db |
| | mysql |
| | performance_schema |
| | sys |
+-+
5 rows in set (0.00 sec)
Mysql > use ceshi_db
Database changed
Mysql > show tables
+-+
| | Tables_in_ceshi_db |
+-+
| | home |
+-+
1 row in set (0.00 sec)
Mysql > create database ceshi_db1
Query OK, 1 row affected (0.01sec)
Mysql > create table home (id int (10) not null,name char (10)); ^ C
Mysql > use ceshi_db1
Database changed
Mysql > create table home (id int (10) not null,name char (10))
Query OK, 0 rows affected (0.09 sec)
1.8. server1 verification
Mysql > show databases
+-+
| | Database |
+-+
| | information_schema |
| | ceshi_db |
| | ceshi_db1 |
| | mysql |
| | performance_schema |
| | sys |
+-+
6 rows in set (0.00 sec)
Mysql > use ceshi_db1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-A
Database changed
Mysql > show tables
+-+
| | Tables_in_ceshi_db1 |
+-+
| | home |
+-+
1 row in set (0.00 sec)
II. Keepalived configuration
2.1By server1 configuration
# cat / etc/keepalived/keepalived.conf
Global_defs {
Smtp_server 127.0.0.1
Smtp_connect_timeout 30
Router_id mysql-1
}
Vrrp_script chk_mysql {
Script "/ etc/keepalived/scripts/mysql_check.sh"
Interval 2
Weight-5
Fall 2
Rise 1
}
Vrrp_instance VI_1 {
State MASTER
Interface eth0
Virtual_router_id 61
Priority 100
Advert_int 1
Authentication {
Auth_type PASS
Auth_pass 1111
}
Track_script {
Chk_mysql
}
Virtual_ipaddress {
182.168.11.210
}
}
2.2, server2 configuration
# cat / etc/keepalived/keepalived.conf
Global_defs {
Smtp_server 127.0.0.1
Smtp_connect_timeout 30
Router_id mysql-2
}
Vrrp_script chk_mysql {
Script "/ etc/keepalived/scripts/mysql_check.sh"
Interval 2
Weight-5
Fall 2
Rise 1
}
Vrrp_instance VI_1 {
State BACKUP
Interface eth0
Virtual_router_id 61
Priority 99
Advert_int 1
Authentication {
Auth_type PASS
Auth_pass 1111
}
Track_script {
Chk_mysql
}
Virtual_ipaddress {
182.168.11.210
}
}
2.3, script
# cat / etc/keepalived/scripts/mysql_check.sh
#! / bin/bash
Counter=$ (netstat-na | grep "LISTEN" | grep "3306" | wc-l)
If ["${counter}"-eq 0]; then
Service keepalived stop
Fi
2.4. Non-preemption mode
If server1 is set to VIP non-preemption mode, make the following changes
Server1
# vi / etc/keepalived/keepalived.conf
Add
Nopreempt
State MASTER
>
State BACKUP
# prevent switching to slave library, automatically switching back to master library after master keepalived is restored
Reference:
Https://blog.csdn.net/qq_36276335/article/details/69942101
Https://www.cnblogs.com/kevingrace/p/6710136.html
