centos7 mysql互为主从+keepalived
一、互为主从配置1.1、资源情况
192.168.11.177 server1
192.168.11.180 server2
192.168.11.210 VIP
1.2、server1配置
# vi /etc/my.cnf
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.3、server2配置
# 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、server1、server2上修改账户密码，创建同步账户
# mysql -p
mysql> set password=password('******');
mysql> create user repluser@'%' identified by '******';
mysql> grant replication slave, replication client on *.* to repluser@'%';
1.5、server2建立同步
server1的master状态
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
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、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.01 sec)
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验证
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.01 sec)
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验证
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)
二、keepalived配置
2.1、server1配置
# 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配置
# 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、脚本
# 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、不抢占模式
如果把server1设置为VIP不抢占模式，做下面修改
server1
# vi /etc/keepalived/keepalived.conf
添加
nopreempt
state MASTER
>
state BACKUP
#防止切换到从库后，主keepalived恢复后自动切换回主库
参考：
https://blog.csdn.net/qq_36276335/article/details/69942101
https://www.cnblogs.com/kevingrace/p/6710136.html
页:
[1]