MySQL database "master-slave" configuration in Linux system

MySQL database "master and slave" configuration

First, configuration overview:

It is important to configure the master-slave copy of MySQL database in Linux operating system, why do you say so! is because after you set up a server you configured 2 databases, one master one from. 2 databases are configured later, when one of the databases is attacked, the data for the database you are attacking will be automatically backed up to another database. The data is not lost even if it is attacked. The master-slave is configured to allow the Web site or other software to function properly to ensure the existence of the data.

Second, the configuration steps:

"Configuration Conditions"

1, need to install two hosts on the virtual machine, and install the same version of the MySQL database. Identify a database as the primary database. Another database is a secondary database. Start the configuration.

2. Log in to the MySQL database of the two systems separately .

3. See if the network connection between the two operating systems is healthy

Command: Ifconfig View IP

Use: The IP of pinging the database on the primary database to see if it is interoperable.

Primary -"ping 192.168.1.138"

Sub ----"main "ping192.168.1.128"

Ok

First Step: Configure the primary database.

1. Configure the primary database server.

Open my.cnf configuration file Configuration

vim/etc/my.cnf .

Configuration Statements

the    server-id=1 This is the main database, OK?

Log-bin=/var/lib/mysql/mysql-bin.log "Specifies the location where a database is saved

Save Exit : Wq

To restart the server: servicemysqld Restart

no prompt error means OK

2, Enter the database to view the specific status of the database

Command :mysql-uroot–p into MySQL database.

Command :showmaster status\g; View status.

650) this.width=650; "src=" https://s4.51cto.com/wyfs02/M02/06/60/wKiom1m3WBSh7ARwAABA8oUUIjg273.jpg "title=" 1.jpg "alt=" Wkiom1m3wbsh7arwaaba8ouuijg273.jpg "/>

3. Lock the table of the database

Command:flush tables with read lock;

650) this.width=650; "src=" https://s3.51cto.com/wyfs02/M01/06/60/wKiom1m3WOSBTtT-AAAhAFxgBmQ973.jpg "title=" 2.jpg "alt=" Wkiom1m3wosbttt-aaahafxgbmq973.jpg "/>

4. backup operation to MySQL database ( back up after exiting the database )

command: mysqldump-hlocalhost-uroot-p3306-p Dzx>/home/dzx.sql

650) this.width=650; "src=" https://s3.51cto.com/wyfs02/M01/06/61/wKiom1m3X2rDIt9yAAAetcRe8vA695.jpg "title=" 3.jpg "alt=" Wkiom1m3x2rdit9yaaaetcre8va695.jpg "/>

5. go to the/home directory to view the dzx.sql database file.

650) this.width=650; "src=" https://s5.51cto.com/wyfs02/M00/A5/11/wKioL1m3X3jxR932AAARsjuqZiw343.jpg "title=" 4.jpg "alt=" Wkiol1m3x3jxr932aaarsjuqziw343.jpg "/>

6. Transfer the Dzx.sql file of the primary database to the secondary database to ensure that the table structure and data in the database can be consistent.

Command:scpdzx.sql [email protected]:/home/

650) this.width=650; "src=" https://s4.51cto.com/wyfs02/M00/06/61/wKiom1m3X8mxVldPAAAh9KWfHpY991.jpg "title=" 5.jpg "alt=" Wkiom1m3x8mxvldpaaah9kwfhpy991.jpg "/>

7 . Cancel the lock of the table in the primary database.

650) this.width=650; "src=" https://s3.51cto.com/wyfs02/M00/A5/11/wKioL1m3X8bDpfQrAAAW7JyQsjw307.jpg "title=" 6.jpg "alt=" Wkiol1m3x8bdpfqraaaw7jyqsjw307.jpg "/>

8, in the secondary database configuration file will be used to the user, again in advance to create a good

grantreplication slave on * * to ' dzx ' @ ' 192.168.1.128 ' identified by ' dzx123 ';

Create a user, give it all the permissions to Dzxuser This user, his IP to be 192.168.1.128 , his password is dzx123 .

650) this.width=650; "src=" https://s3.51cto.com/wyfs02/M00/A5/11/wKioL1m3X_GATn1xAAAfDC0S8PA578.jpg "title=" 7.jpg "alt=" Wkiol1m3x_gatn1xaaafdc0s8pa578.jpg "/>

Step Two: Configure the secondary database.

1. Open the main configuration file in the second operating system MY.CNF

Vim/etc/my.cnf

650) this.width=650; "src=" https://s5.51cto.com/wyfs02/M02/06/61/wKiom1m3YJ7BGQiyAABfIG7JhTY075.jpg "title=" 8.jpg "alt=" Wkiom1m3yj7bgqiyaabfig7jhty075.jpg "/>

2, Configure the secondary database configuration commands

server-id=2 This is the ID number of the secondary database and cannot be the same as the primary database

Log-bin=/var/lib/mysql/mysql-bin.log This is consistent with the master database

master-host=192.168.1.128 This is the IP address of the primary database service

MASTER-USER=DZX This is the user name of the primary database

Master-pass=dzx123 This is the corresponding password for the user in the master database

master-port=3306 This is the port number of the book database.

Master-connect-retry=10 This is the length of the two time to configure the connection --- interval connection

REPLICATE-DO-DB=DZX copy dzx This database

Save exit : Wq

Restart service servicemysqld restart

Enter the database.

3, Verify the two parameters, is to ensure that the primary database is communicated with each other.

(1) View configuration

show slave status\g;

650) this.width=650; "src=" https://s5.51cto.com/wyfs02/M00/A5/11/wKioL1m3YVKB-9vcAACq1oMX2fA679.jpg "title=" 9.jpg "alt=" Wkiol1m3yvkb-9vcaacq1omx2fa679.jpg "/>

(This is from the database)

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/A5/11/wKioL1m3YYuiXwLiAABEslj2klU998.jpg "title=" 10. JPG "alt=" wkiol1m3yyuixwliaabeslj2klu998.jpg "/>

(This is the primary database)

The file and position entries of the primary database can be useful when they are consistent from the database. Otherwise it will fail.

Third, concluding remarks:

Until this point the master-slave database configuration is over. In the configuration process should be careful not to configure the wrong, there is a link between each other, a step wrong behind will lead to failure. When the configuration succeeds, the secondary database will also be affected when you manipulate the primary database. The data is also automatically backed up to the secondary database. If there is something wrong in this article, please advise me. Thank you!

This article from "Lonely One Night" blog, declined reprint!

MySQL database "master-slave" configuration in Linux system