MySQL master-slave replication + dual Master mode

MySQL Master-slave replicationDeployment Environment:

MySQL Master 192.168.40.21

MySQL slave 192.168.40.22

Ideas:

When the data on the main MySQL operation or changes, the main MySQL binary log files (binary logs) will be changed, when the I/O thread and SQL thread from MySQL;

The I/O thread reads the binary log file on the primary MySQL and reads it to the trunk log file from MySQL (relay log)

SQL thread is to read and execute data on the relay log file, the whole process is to achieve the master-slave replication process, the effect is when the main MySQL changes, from the same will be synchronized changes, also known as the main standby mode

Build Master and Slave:

The premise needs to pay attention to two service time problem, the time needs synchronization consistent. If not, you can do it:

Install NTP on master MySQL

[[email protected] ~]# yum-y install NTP

[Email protected] ~]# vim/etc/ntp.conf

#手动添加

Server 127.127.1.0
Fudge 127.127.1.0 Startum 8

Restart NTP

[[Email protected] ~] #chkconf ntpd on #开机启动

build time sync from MySQL, install ntpdate

[Email protected] ~]# yum-y install Ntpdate

[Email protected] ~]# ntpdate 192.168.40.21

Configuring the primary MySQL server

[Email protected] ~]# CAT/ETC/MY.CNF

#手动添加在MySQLD下

Log-bin=mysql-bin
Log-slave-updates=true
Server-id =11

[email protected] ~]#/etc/init.d/msyql restart

#进入MySQL里, give permission from the service

mysql> grant replication slave on *.*to ' myslave ' @ ' 192.168.40.% ' identified by ' 123123 ';

mysql> flush Privileges; #刷新授权表

Mysql> Show master status; #查看当前二进制文件

configure from MySQL server　　

[Email protected] ~]# VIM/ETC/MY.CNF

Relay-log=relay-log-bin

Relay-log-index=slave-relay-bin.index
Server-id =12 #id不能与主服务一样

Restart MySQL and enter

Mysql> Change Master to

mysql> master_host= ' 192.168.40.21 ', master_user= ' myslave ', master_password= ' 123123 ', master_log_file= ' mysql-bin.000036 ', master_log_pos=267;

mysql> start Slave

mysql> show slave status\g;

The next two lines of the picture Yes for the IO thread and the SQL thread are OK, now to test!

Create a WWW library on master MySQL

Now check to see if there are any changes to the library from MySQL

It can be seen from MySQL has been updated in real-time data, the WWW library appeared!

MySQL Dual master mode

Build the environment and ideas:

On the basis of the above experimental environment, the reverse construction of master-slave, that is, on the basis of the previous experimental environment in a master from above, in the Lord to do a slave, that is, a MySQL is another master from, and then install keepalived for high-availability and failover

Construction process:

Build a master from the previous case

In fact, the configuration process as in the previous case, but is the direction of the configuration, the process is not written so detailed

Modifying a configuration file/etc/my.cnf

#手动添加

Log-bin=mysql-bin
Log-slave-updates=true
Server =20

Restart MySQL and go to the permissions from the service

mysql> grant replication slave on *.*to ' myslave ' @ ' 192.168.40.% ' identified by ' 123123 ';

mysql> flush Privileges;

Mysql> Show master status;

In the last case the Lord configures from

Modifying a configuration file/etc/my.cnf

#手动添加

Relay-log=relay-log-bin
Relay-log-index=slave-bin.index
Server-id =22

Restart the service and log in

Mysql> Change Master to

mysql> master_host= ' 192.168.40.22 ', master_user= ' myslave ', master_password= ' 123123 ', master_log_file= ' Mysql-bin.000002 ', master_log_pos=501;

mysql> start Slave

Mysql> Show Slave Status\g

Dual master configuration successful, now need to write a healthy MySQL script and install a keepalived high-availability software, because before the cluster was installed, so it is not installed keepalived

Script:

#!/bin/bash
#by:D Fengshuo
#检测端口脚本
#检测mysql the 3306 port is on, if it is not turned on, try to open
jc= ' Ps-c mysqld-no-header|wc-l '
If [$JC-eq 0];then
/etc/init.d/mysqld restart
Sleep 2
#如果mysql尝试开启失败, then close the keepalived service for VIP drift
Jc2= ' ps-c haproxy-no-header |wc-l '
If [$JC-eq 0];then
/etc/init.d/keepalived stop
Fi
Fi

[Email protected] ~]# SCP mysql.sh 192.168.40.22:/root #把脚本发给192.168.40.22 Root

Modify the keepalived configuration file

Modify the keepalived configuration file on the 192.168.40.22 because you just sent the script to root, so you don't have to write a script

Restart the keepalived service after configuration to see if the VIP exists

Now test keepalived down, vip whether drift

This shows that the VIP has drifted to 40.22, Test success!!!

===== If there are errors in the text, please point out! =====

MySQL master-slave replication + dual Master mode