Build MySQL master-slave synchronization Service

Mysql Master-slave synchronous setup

MySQL master-slave synchronization uses the SQL statements recorded in the binlog log to achieve data synchronization, which enables automatic data backup.

building mysql master-Slave synchronization requires a server to do master that is, the primary server, a server to do slave slave server from master synchronization on server binlog recorded in the journal sql statement that executes these statements in the local database for database synchronization purposes.

note, in the building mysql master-Slave synchronization service, slave master

This article uses two RHEL5.9 operating system virtual machines to complete the experiment, where themaster server IP address is 192.168.1.253,the IP address of theslave server is 192.168.1.252

Experimental steps:

1. Configure a static IP address on both servers and make sure that the physical network is connected properly

2. Configuring the Master server

(1) Yum- style installation of MySQL,mysql-server Packages

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/45/B2/wKiom1Ppus6iGF48AAA8BPPv8zc438.jpg "title=" 1.png " alt= "Wkiom1ppus6igf48aaa8bppv8zc438.jpg"/>

(2) Modify The configuration file for the MySQL service

[email protected] ~]# vim/etc/my.cnf //mysql Service configuration file

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/45/B3/wKioL1PpvCvT8O0GAAE9ZUZDiFI368.jpg "title=" 2.png " alt= "Wkiol1ppvcvt8o0gaae9zuzdifi368.jpg"/>

(3) start the mysqld service and set the boot-up from

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/45/B2/wKiom1PpuyCS7BlIAAB1qr78yOA466.jpg "title=" 3.png " alt= "Wkiom1ppuycs7bliaab1qr78yoa466.jpg"/>

(4) log in to the MySQL database locally, authorize slave and view the current binlog logs and offsets used

[[email protected] ~]# MySQL -uroot //log in to the database locally using the root user name

Authorized:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/45/B3/wKioL1PpvEnxidPsAADqG3pSR1o823.jpg "title=" 4.png " alt= "Wkiol1ppvenxidpsaadqg3psr1o823.jpg"/>

To view the master Status:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/45/B3/wKioL1PpvFiQjEhfAADZ-fLI0Yw158.jpg "title=" 5.png " alt= "Wkiol1ppvfiqjehfaadz-fli0yw158.jpg"/>

3. Configuring the slave server

(1) Yum- style installation of MySQL,mysql-server Packages

(2) Modify The configuration file for the MySQL service, start the mysqld Service, and set the boot

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

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/45/B3/wKioL1PpvGPT5r62AAFg38O5PbY544.jpg "title=" 6.png " alt= "Wkiol1ppvgpt5r62aafg38o5pby544.jpg"/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/45/B3/wKioL1PpvHXTBYLbAAB-xhUhW2w653.jpg "title=" 7.png " alt= "Wkiol1ppvhxtbylbaab-xhuhw2w653.jpg"/>

(3) log in to the MySQL database locally, specify the primary server from the server and turn on the slave feature

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/45/B2/wKiom1Ppu2nwTDIDAADd2kHhpl8783.jpg "title=" 8.png " alt= "Wkiom1ppu2nwtdidaadd2khhpl8783.jpg"/>

Change Master to

master_host= ' 192.168.1.253 ', // Specify primary server address

master_user= ' Slaveuser ', // Specify the user name to use when synchronizing

master_password= ' 123 ', // Specify the password to use when synchronizing

master_log_file= ' mysqld-bin.00000.1 ', //Specify the binlog log used by the current master server

master_log_pos=98; // Specifies the bit offset on the current primary server

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/45/B3/wKioL1PpvJLCfQRDAABRUKmJnc4193.jpg "title=" 9.png " alt= "Wkiol1ppvjlcfqrdaabrukmjnc4193.jpg"/>

(4) See if the slave feature is working properly

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/45/B2/wKiom1Ppu4bjYsx9AAIsQ_DKuM4589.jpg "title=" 10.png "alt=" wkiom1ppu4bjysx9aaisq_dkum4589.jpg "/>

4. Test

Create a new library on the master server zhy

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/45/B2/wKiom1Ppu5jxnv6WAADa7bHsnSE940.jpg "title=" 11.png "alt=" wkiom1ppu5jxnv6waada7bhsnse940.jpg "/>

Slave the database on the server, the zhy database also appears:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/45/B2/wKiom1Ppu6bxolEdAACFFASrqtI554.jpg "title=" 12.png "alt=" wkiom1ppu6bxoledaacffasrqti554.jpg "/>

At this point,Mysql master-Slave synchronization is completed and can work properly, but there are a few points to note:

1. set the slave server with a comma separated by each item when you specify its home server

2, slave The server's primary server settings need to be stopped first slave service, then change, use command stop slave can stop slave service

3. only when the state of the two processes of slave_io_running and slave_sql_running is all yse is it indicated slave The service is working properly, otherwise it indicates that it is working abnormally.

4. The reason for the slave_io_running process status is NO :

(1) connection not on primary database server (physical lines, security settings issues, authorization issues)

(2) inconsistencies between the current binlog log name and the pos Point and the primary server

5. The reason for the slave_sql_running process status is NO : There is no inconsistency between the library and table or table structure on the primary database from the server native.

This article is from the "Cigarette Butts Webmaster" blog, please be sure to keep this source http://8838848.blog.51cto.com/8828848/1538999