### 环境准备 系统: `CentOS7` 数据库: `MySQL5.7` Master1节点: `172.16.0.100(node1)` Master2节点: `172.16.0.101(node2)` VIP: `172.16.0.188` 同步数据库名:`replicatest` &emsp; ### 安装MySQL `Master1/Master2节点` ```bash yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm yum install mysql-community-server ``` 启动数据库 ```bash systemctl enable mysqld systemctl start mysqld ``` 获取数据库临时密码 ```bash grep 'temporary password' /var/log/mysqld.log ``` 对数据库进行加固 ```bash mysql_secure_installation ``` &emsp; ### 配置Master1 修改数据库配置 ```bash vim /etc/my.cnf ``` ```ini bind-address = 172.16.0.100 server-id = 1 log_bin = mysql-bin binlog-do-db = replicatest replicate-do-db = replicatest auto-increment-increment = 2 auto-increment-offset = 1 ``` 重启数据库 ```bash systemctl restart mysqld ``` 创建复制账号 ```sql mysql> CREATE USER 'replica'@'172.16.0.101' IDENTIFIED BY 'strong_password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.16.0.101'; mysql> FLUSH PRIVILEGES; ``` 查看二进制日志文件和位置 ```sql mysql> FLUSH TABLES WITH READ LOCK #锁表，备份数据，恢复到master2，再让master2从当前位置去同步数据，保证了数据的一致性 mysql> SHOW MASTER STATUS\G ``` ``` *************************** 1. row *************************** File: mysql-bin.000001 Position: 623 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) ``` `注意：文件:mysql-bin.000001,位置:623` 备份replicatest数据 ```bash mysqldump -h127.0.0.1 -uroot -pxxxx --databases replicatest --events --triggers --routines >replicatest.sql ``` ### 拷贝数据到master2 ```bash scp replicatest.sql root@node2:/root ``` &emsp; ### 配置Master2 修改数据库配置 ```bash vim /etc/my.cnf ``` ```ini bind-address = 172.16.0.101 server-id = 2 log_bin = mysql-bin binlog-do-db = replicatest replicate-do-db = replicatest #binlog_ignore_db = mysql #binlog_ignore_db = infomation_schema #binlog_ignore_db = performance_schema auto-increment-increment = 2 auto-increment-offset = 2 ``` 重启数据库 ```bash systemctl restart mysqld ``` 恢复数据库 ```sql mysql>source replicatest.sql ``` 停止slave线程 ```sql mysql> STOP SLAVE; ``` 配置master2复制master1 ```sql mysql> CHANGE MASTER TO mysql> MASTER_HOST='172.16.0.100', mysql> MASTER_USER='replica', mysql> MASTER_PASSWORD='strong_password', mysql> MASTER_LOG_FILE='mysql-bin.000001', mysql> MASTER_LOG_POS=623; ``` 启动slave线程 ```sql mysql> START SLAVE; ``` 查看slave状态 ```sql mysql > show slave status\G ``` ``` *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.100 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 623 Relay_Log_File: node2-relay-bin.000001 Relay_Log_Pos: 323 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 623 Relay_Log_Space: 544 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: f8cc4c10-b429-11e9-9ff9-5600023106ec Master_Info_File: /var/lib/mysql/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: ``` 创建复制账号 ```bash mysql> CREATE USER 'replica'@'172.16.0.100' IDENTIFIED BY 'strong_password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.16.0.100'; mysql> FLUSH PRIVILEGES; ``` 查看当前的二进制日志 ```sql mysql> SHOW MASTER STATUS\G ``` ``` *************************** 1. row *************************** File: mysql-bin.000001 Position: 504 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) ``` `注意：文件:mysql-bin.000001,位置:504` &emsp; ### 配置Master1 解锁只读锁 ```sql mysql> UNLOCK TABLES ``` 停止slave线程 ```sql mysql> STOP SLAVE; ``` 配置同步 ```sql mysql> CHANGE MASTER TO mysql> MASTER_HOST='172.16.0.101', mysql> MASTER_USER='replica', mysql> MASTER_PASSWORD='strong_password', mysql> MASTER_LOG_FILE='mysql-bin.000001', mysql> MASTER_LOG_POS=504; ``` 启动slave线程 ```sql mysql> START SLAVE; ``` 查看同步状态 ```sql mysql> show slave status\G ``` ``` *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.101 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 504 Relay_Log_File: node1-relay-bin.000001 Relay_Log_Pos: 245 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 504 Relay_Log_Space: 213 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: 1c505063-b42a-11e9-96c3-560002320f46 Master_Info_File: /var/lib/mysql/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: ``` &emsp; ### 安装Keepalived `Master1节点/Master2节点` ```bash yum install keepalived -y ``` ### 配置Keepalived `Master1` ```bash vim /etc/keepalived/keepalived.conf #除了参数priority两台Master设置优先级不一样，其他参数保存一致 ``` ```ini ! Configuration File for keepalived global_defs { notification_email { acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc } notification_email_from Alexandre.Cassen@firewall.loc smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_DEVEL vrrp_skip_check_adv_addr vrrp_strict vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_script vs_mysql_100 { script "/etc/keepalived/checkmysql.sh" interval 10 } vrrp_instance VI_100 { #集群的名字 state BACKUP #这里两台都配置BACKUP nopreempt #设置为不抢占模式 interface eth1 #VIP绑定的网卡eth1 virtual_router_id 51 #vrid的两台一样 priority 100 #优先级，两台设置不一样，master1设置为100，master2设置为90 advert_int 5 #主备之前同步建成的时间间隔是5秒 authentication { auth_type PASS #验证方式通过密码 auth_pass 9898 #验证密码 } track_script { vs_mysql_100 #执行监控的脚本 } virtual_ipaddress { 172.16.0.188 #虚拟VIP地址 } } ``` #### 创建数据库监控脚本 `Master1/Master2节点` ```bash cat /etc/keepalived/checkmysql.sh ``` ```sh #!/bin/bash mysqlstr=/usr/bin/mysql host=127.0.0.1 user=root password=xxxxxxx port=3306 mysql_status=1 $mysqlstr -h $host -u $user -p$password -P$port -e "show status;" >/dev/null 2>&1 if [ $? -eq 0 ];then echo "mysql_status=1" exit 0 else echo "systemctl stop keepaliaved" systemctl stop keepaliaved fi ``` 添加执行权限 ```bash chmod +x /etc/keepalived/checkmysql.sh ``` ### 启动keepalived `Master1/Master2节点` ```bash systemctl start keepalived ``` &emsp; ### 测试主主 `Master1` 插入测试数据 ```sql mysql> use replicatest; mysql> insert into test(name) values('master1 write'); ``` `Master2` 查看是否同步 ```sql mysql> use replicatest; mysql> select * from test; ``` ``` +----+---------------+ | id | name | +----+---------------+ | 19 | master1 write | +----+---------------+ ``` 插入测试数据 ```sql mysql> insert into test(name) values('master2 write'); ``` `Master1` 查看是否同步 ```sql select * from test; ``` ``` +----+---------------+ | id | name | +----+---------------+ | 19 | master1 write | | 20 | master2 write | +----+---------------+ ``` `通过输出可以看到master1和master2都能同步成功!` &emsp; ### 测试keepalived **停止Master1上的MySQL，查看VIP是否转移到MASTER2** `Master1` ```bash systemcat stop mysqld ``` 查看VIP是否漂移成功 `Master2` ```bash ip a ``` ``` ... 3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1450 qdisc pfifo_fast state UP group default qlen 1000 link/ether 5a:00:02:32:0f:46 brd ff:ff:ff:ff:ff:ff inet 172.16.0.101/16 brd 172.16.255.255 scope global eth1 valid_lft forever preferred_lft forever inet 172.16.0.188/32 scope global eth1 valid_lft forever preferred_lft forever ``` `通过测试发现VIP漂移成功`