MySQL primary/secondary replication
Deployment environment:
MySQL master 192.168.40.21
MySQL slave 192.168.40.22
Ideas:
The binary log file will change when the data is operated or changed on the primary MySQL database. In this case, the I/O thread and THE SQL thread will be started on the primary MySQL database.
The I/O thread reads binary log files from the primary MySQL relay log file to the secondary MySQL relay log file.
The SQL thread reads and executes data from the relay log file. The whole process is to achieve master/slave replication. The effect after implementation is that when the master MySQL changes, the slave will also change synchronously, also known as master/slave mode
Setting up master and slave:
Before setting up services, ensure that the time of the two services is synchronized. If no, perform the following operations:
NTP is installed on the primary MySQL server
[root@6 ~]# yum -y install ntp
[root@6 ~]# vim /etc/ntp.conf
# Manually add
22 server 127.127.1.0
23 fudge 127.127.1.0 startum 8
Restart the NTP
[root@6 ~]#chkconf NTPD on
MySQL > install ntpDate
[root@6 ~]# yum -y install ntpdate
/ root @ 6 ~ # ntpdate 192.168.40.21
Configure the primary MySQL server
[root@6 ~]# cat /etc/my.cnf
Add it manually under MySQLD
log-bin=mysql-bin
log-slave-updates=true
server-id =11
[root@6 ~]# /etc/init.d/msyql restart
# enter MySQL and give secondary server permission
Mysql > grant replication slave on *.*to’myslave’@’192.168.40.%’identified by ‘123123’;
mysql> flush privileges; Update the authorization table
mysql> show master status; View the current binary file
Configure from the MySQL server
[root@root ~]# vim /etc/my.cnf
relay-log=relay-log-bin
Relay log-index=slave-relay-bin.index server-id =12 # ID Cannot be the same as the master service
Restart MySQL and enter
mysql> change master to
mysql> Master_host = ‘192.168.40.21, master_user =’ myslave ‘, master_password = ‘123123’, master_log_file = ‘000036’ mysql – bin., master_log _pos=267;
mysql> start slave
mysql> show slave status\G;
The first two lines of yes indicate that IO thread and SQL thread are ok, now let’s test it!
Create a WWW library on primary MySQL
Now check to see if the libraries from MySQL have changed
As you can see from the MySQL update data in real time, there is a WWW library!
MySQL dual primary mode
Setting up environment and thinking:
On the basis of the above experimental environment, reverse build master slave, that is, on the basis of the last experimental environment in the above do a master, do a slave on the master, that is, a MySQL is another master is slave, and then install Keepalived for high availability and failover
Setup process:
Build a master from the previous case
In fact, the configuration process and the previous case, but the direction of the configuration, the process will not write so detailed
Modify the configuration file /etc/my.cnf
# Manually add
log-bin=mysql-bin
log-slave-updates=true
server =20
Restart MySQL and grant permission to the secondary server
Mysql > grant replication slave on *.*to’myslave’@’192.168.40.%’identified by’123123′;
mysql> flush privileges;
mysql> show master status;
In the previous case, the master configured the slave
Modify the configuration file /etc/my.cnf
# Manually add
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 = ‘000002’ mysql – bin., master_log _pos=501;
mysql> start slave
mysql> show slave status\G
Dual master configuration is successful, now need to write a healthy MySQL script and install keepalived high availability software, because the previous cluster was installed, so do not install Keepalived
Script:
#! /bin/bash
#by:Dfengshuo
Check the port script
Check whether mysql 3306 port is enabled
jc=`ps -C mysqld -no-header|wc -l`
if [ $jc -eq 0 ]; then
/etc/init.d/mysqld restart
sleep 2
# If mysql fails to open, then disable keepalived service for VIP floating
jc2=`ps -C haproxy -no-header |wc -l`
if [ $jc -eq 0 ]; then
/etc/init.d/keepalived stop
fi
fi
[root@root ~]# SCP mysql.sh 192.168.40.22:/root
Modify keepalived configuration files
“Keepalived” configuration file “192.168.40.22”, “keepalived” configuration file “192.168.40.22
After configuration, restart keepalived service to check whether THE VIP exists
Now test whether the VIP floats after Keepalived goes down
It can be seen that VIP has drifted to 40.22, test success!!