keeplived + mysql Dual master replication Deployment--Original

Environment:

Master 1:	192.168.100.10	Oracle Linux 7.4	MySQL 5.7.1
Master 2:	192.168.100.11	Oracle Linux 7.4	MySQL 5.7.1
Keepalived VIP	192.168.100.12

Configuring the Local Yum Repository

Vim/etc/yum.repo.d/public-yum-ol7.repo

[Yum_repo]name=yum_repobaseurl=file://share/repogpgcheck=0 enabled=1

Install MySQL

Check if you have installed

grep MySQL

1.mysql Install package install sequence installation package dependencies must be installed in order

1. mysql-community-common-5.7.21-1.el7.x86_64.rpm
2.mysql-community-libs-5.7.21-1.el7.x86_64.rpm
3.mysql-community-client-5.7.21-1.el7.x86_64.rpm
4.mysql-community-server-5.7.21-1.el7.x86_64.rpm

2. Initializing MySQL will generate an expired root password after initialization is completed, and you will need to change it immediately after logging in to MySQL, otherwise you cannot use any command

grep MySQL

3. Start MySQL

Systemctl Start mysqld

4. View the MySQL root password

Cat grep Password

5.mysql security settings (optional, can reset root password)
　　

Mysql_secure_installation

6. Log in to MySQL
　　

Mysql-u root-p

7. Change the root password
　　

' Root '@'%'len#qwer';

8. Set root account never expires (optional)
　　

' Root '@'%' password expire never;

Configure Dual-Master

Modify MySQL configuration file C

Master1 node

vim/etc/My.cnf[mysqld]    log-bin=mysql-bin    #开启二进制日志server-id=1 # Set server-ID  Master repository must be different log_slave_updates=1

Master2 node

vim/etc/My.cnf[mysqld]    log-bin=mysql-bin    #开启二进制日志server-ID=2 #设置server-
   
    id 
     Master repository must be different log_slave_updates=
    1
   

2. Restart MySQL,
Master1 node

Systemctl Restart Mysqld

Master2 node

Systemctl Restart Mysqld

Create a synced user
Master1 node

' Repl ' @'192.168.100.11'phjj#qwer'  repl' @'192.168.100.11';

Master2 node

' Repl ' @'192.168.100.10'phjj#qwer'  repl' @'192.168.100.10';

View the Binlog file name and post location for two nodes for configuring replication

Master1 node

Mysql> Show master status; +------------------+----------+--------------+------------------+-------------------+| File             | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+-------------------+| Mysql-bin. 000004 |      619 | | | |                   +------------------+----------+--------------+------------------+-------------------+1 in Set ( 0.00 sec)

Master2 node

Mysql> Show Master status;+------------------+----------+--------------+------------------+--------------- ----+| File             | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000002 |     1175 | | | |                   +------------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec)

Master1 node

Start slave;

Master2 node

Start slave;

Configure replication

Master1 node

Change MASTER tomaster_host='192.168.100.11', master_user=' Repl ' , Master_password='phjj#qwer', master_log_file=' mysql-bin.000002 ' , Master_log_pos=1175;

Master2 node

Change MASTER tomaster_host='192.168.100.10', master_user=' Repl ' , Master_password='phjj#qwer', master_log_file=' mysql-bin.000004 ' , Master_log_pos=619;

Master1 initiating replication

Start slave;

Master2 Initiating replication

Start slave;

View replication status Slave_io_running,slave_sql_running status must be Running to be normal

Mysql> show Slave status\g; Slave_IO_Running:YesSlave_SQL_Running:Yes

Installing keepalived

Yum Install -y keepalived

Configure keepalived

Master1 node

Vim/etc/keepalived/keepalived.conf

! Configuration File forKeepalivedglobal_defs {notification_email {[email protected][email protected][email Protected]}notification_ email_from [Email Protected]smtp_server192.168.200.1Smtp_connect_timeout -router_id lvs_develvrrp_skip_check_adv_addr# vrrp_strictvrrp_garp_interval0Vrrp_gna_interval0}vrrp_instance vi_1 {State backupinterface ens160virtual_router_idWuyi Priority -Advert_int1Authentication {Auth_type Passauth_pass1111}virtual_ipaddress {192.168.100.12/ -}}

Virtual_server 192.168.100.12 3306 {
Delay_loop 6
Lb_algo RR
Lb_kind NAT
Persistence_timeout 50
Protocol TCP

Real_server 192.168.100.10 3306 {
Weight 3
notify_down/etc/keepalived/kill_keepalived.sh

Tcp_check {
Connect_timeout 10
Nb_get_retry 3
Delay_before_retry 3
Connect_port 3306

}
}
}

Master2 node

Vim/etc/keepalived/keepalived.conf

! Configuration File for keepalivedglobal_defs {notification_email {[email protected][email protected][email Protected]}notification_email_from [email protected]    smtp_server 192.168.200.1smtp_connect_timeout  router_id lvs_develvrrp_skip_check_adv_addr# vrrp_strictvrrp_garp_interval 0vrrp_gna_interval 0}vrrp_ Instance vi_1 {State Backupinterface ens160virtual_router_id1 advert_int{ Auth_type passauth_pass 1111}virtual_ipaddress {192.168.100.12/24}}

Virtual_server 192.168.100.12 3306 {
Delay_loop 6
Lb_algo RR
Lb_kind NAT
Persistence_timeout 50
Protocol TCP

Real_server 192.168.100.11 3306 {
Weight 3
notify_down/etc/keepalived/kill_keepalived.sh

Tcp_check {
Connect_timeout 10
Nb_get_retry 3
Delay_before_retry 3
Connect_port 3306

}
}
}

Configuring the Close Keepalived script

vim/etc/keepalived/kill_keepalived.sh

#!/bin/Bashpkill keepalived

Add execute permission to close keepalived script

chmod +x/etc/keepalived/kill_keepalived. SH

View keepalived VIP Address

The same UUID causes MySQL to not replicate

Failure phenomena

Vim/var/log/mysqld.log

2018- Geneva-12T02: -: -.499315Z7[Note] Slave SQL Thread forChannel"'Initialized, starting replicationinchLog'mysql-bin.000001'At position619, Relay Log'./ajiamysql1-relay-bin.000001'Position42018- Geneva-12T02: -: -.499839Z6[Note] Slave I/O thread forChannel"': Connected to Master'[Email protected]:3306', Replication startedinchLog'mysql-bin.000001'At position6192018- Geneva-12T02: -: -.501900Z6[ERROR] Slave I/O forChannel"': Fatal error:the slave I/O thread stops because master and slave have equal MySQL server UUIDs; These uuids must be different forReplication to work. Error_code:1593

Cause of failure: Direct replication of a virtual machine that has already been installed MySQL causes the same MySQL UUID cannot be copied connection note: UUID and server_id are not the same.

Troubleshooting: Modifying MySQL UUID

[Email protected] mysql]# VIM/VAR/LIB/MYSQL/AUTO.CNF

[Auto]server-uuid=6fe77f0a-eae1-11e7-805e-005056b309100

keeplived + mysql Dual master replication Deployment--Original