MySQL One-way replication

mysql One-way replication

In order to improve the robustness of the master-slave server, we chose the method of MySQL one-way replication, when the primary server down, from the server can still take over, and maintain the relative integrity of the data, and from the server backup will not interfere with the work of the primary server, can be said to be a powerful feature.

Principle: The data update in the main server is encapsulated as a binary file, sent to update from the server, from the server to the primary server, the master server can also get its last update location. All updates must be made on the primary server.

Environment :Rhel 6.5

Host :192.168.122.102(abbreviation server2~)

Secondary Machine :192.168.122.103 ( abbreviation server3~)

First, install the MySQL environment on both hosts (you can also select multiple slave servers) to perform

[email protected] ~]# Yum install mysqlmysql-server-y

[email protected] ~]# Yum install mysqlmysql-server-y

after installation, start MySQL on the server2, execute

[[email protected] ~]#/etc/init.d/mysqld start

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/8C/05/wKioL1hfjDSAv6GCAAAjbPGteJ0370.png-wh_500x0-wm_3 -wmp_4-s_1302078551.png "title=" screenshot from 2016-12-25 14_43_55.png "alt=" Wkiol1hfjdsav6gcaaajbpgtej0370.png-wh _50 "/>

For example, you can see that this error occurred when MySQL was turned on, and you need to delete the previous socker : Execute

Rm/var/lib/mysql/mysql.sock

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/8C/09/wKiom1hfjFLBm0PkAAA2QCrgG2M778.png-wh_500x0-wm_3 -wmp_4-s_3673757335.png "title=" screenshot from 2016-12-25 14_49_43.png "alt=" Wkiom1hfjflbm0pkaaa2qcrgg2m778.png-wh _50 "/>

you can see that this launch is successful, and then enter directly in the Server2.

Mysql> GRANT replicationslave,reload,super On * * to [e-mail protected] ' 192.168.122.103 ' identified by ' Adol '; (set up a sync account for the host)

mysql> Flush privileges;

This allows server3 to log on to Server2 as slave and continue to change the /etc/my.cnf file on Server2

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

in the [Mysqld] Add the following four lines of content in the

Log-bin=mysql-bin(Start the binary log system)

Binlog-do-db=adol ( the name of the database in which the binary needs to be synchronized)

Server-id=1 ( must be a positive integer value between 1 and 232–1, and cannot be duplicated)

Binlog-ignore-db=mysql ( avoid syncing mysql User Configuration to avoid unnecessary hassles)

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8C/09/wKiom1hfjKyxcOozAAB7qnXvTbY657.png-wh_500x0-wm_3 -wmp_4-s_372291112.png "title=" screenshot from 2016-12-25 14_53_47.png "alt=" Wkiom1hfjkyxcoozaab7qnxvtby657.png-wh_ "/>

:wq exit Save

perform /etc/init.d/mysql restart update configuration

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/8C/05/wKioL1hfjMTgr8gpAAAexWSx_gs177.png-wh_500x0-wm_3 -wmp_4-s_211337558.png "title=" screenshot from 2016-12-25 15_01_15.png "alt=" Wkiol1hfjmtgr8gpaaaexwsx_gs177.png-wh_ "/>

Modify the /etc/my.cnf file on server3

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

Add the following line to "mysqld"

server-id=2 (cannot be like Server2, this is the identity mark, Lenovo ID number)

master-host=192.168.122.102(host server2 IP)

Master-user=test ( host-Available user name )

Master-password=adol(password for host available name)

master-port=3306(3306 is the default mysql port)

Master-connect-retry=60(Disconnects the reconnect time in seconds)

Replicate-ignore-db=mysql(Ignore database MySQL to avoid synchronization )

Replicate-do-db=adol(database that needs to be synchronized)

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8C/05/wKioL1hfjNnypudOAACfpu9fiv4146.png-wh_500x0-wm_3 -wmp_4-s_2088521982.png "title=" screenshot from 2016-12-25 16_08_10.png "alt=" Wkiol1hfjnnypudoaacfpu9fiv4146.png-wh _50 "/>

[[email protected] ~]#/etc/init.d/mysqld restart ( restart MySQL)

execute within server2 MySQL

Mysql> Show master status;

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/8C/09/wKiom1hfjOaCqgmpAAAVLQPISAY725.png-wh_500x0-wm_3 -wmp_4-s_3813667397.png "title=" screenshot from 2016-12-25 16_18_39.png "alt=" Wkiom1hfjoacqgmpaaavlqpisay725.png-wh _50 "/>

The result can be obtained by obtaining its File and position values.

Enter the database update configuration within SERVER3, as follows

mysql> stop Slave;

Mysql> Change Master to master changemaster_host= ' 192.168.122.102 ', master_user= ' test ', master_password= ' Adol ', master_log_file= ' mysql-bin.000002 ', master_log_pos=106;

mysql > Start slave;

the color tag is the data we get from Master .

Server3 execute mysql > show slave status\g

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/8C/05/wKioL1hfjPXyXDZUAACQpP0B5lM455.png-wh_500x0-wm_3 -wmp_4-s_995200875.png "title=" screenshot from 2016-12-25 16_34_06.png "alt=" Wkiol1hfjpxyxdzuaacqpp0b5lm455.png-wh_ "/>

can get Slave_io_running:yes

Slave_sql_running:yes

when their value is YES , the description slave can be copied from Master synchronously.

Detection:

my own The data in Server2 and Server3 are as follows

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Adol |

| Adold |

| Marjur |

| MySQL |

| ooo |

+--------------------+

6 rows in Set (0.09 sec)

mysql> use Adol;

Database changed

Mysql> Show tables;

+----------------+

| Tables_in_adol |

+----------------+

| A |

| K |

+----------------+

2 rows in Set (0.00 sec)

now add a table in Server2

Mysql> CREATE TABLE DD (name varchar (20));

Query OK, 0 rows affected (0.06 sec)

Execute within Server3 into database Adol

Mysql> Show tables;

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/8C/09/wKiom1hfjQ6gD9utAAAbm1JubUM143.png-wh_500x0-wm_3 -wmp_4-s_4163284677.png "title=" screenshot from 2016-12-25 16_40_44.png "width=" "height=" 129 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:250PX;HEIGHT:129PX; "alt=" Wkiom1hfjq6gd9utaaabm1jubum143.png-wh_50 "/>

As you can see,server3 has a table for DD , stating that one-way replication is successful.

(Note: The default replication can only replicate content within the Adol database, and content changes outside of Adol do not

Server3 have an impact . )

This article is from the "11827782" blog, please be sure to keep this source http://11837782.blog.51cto.com/11827782/1885969

MySQL One-way replication