MySQL Master-slave configuration

Build the Environment:

Master 192.168.127.131

Slave 192.168.127.128

The premise of master-slave configuration: Two database data need to be identical, so we:

Build a database on the master here, we'll back up with MySQL.

Mysqldump db1 >123.sql (Backup)

To create a database on the master DB1 need to log in to the database to perform this: (Create database db1;)

MySQL db1 <123.sql (revert to DB1 Library)
The same is done from above, keeping two databases identical

Main:

To change the configuration file/etc/my.cnf

[Mysqld]server-id=1log-bin=mysql-bin (can be customized name) BINLOG-DO-DB=DB1 (Specify the name of the database to synchronize)

Log in to the database and then:

Grant Replication Slave on * * to ' repl ' @ ' 192.168.75.128 ' identified by ' 123123 '; (from the host IP, and establish a synchronized user repl)

Flush privileges; Refresh Permissions

Flush tables with read lock; (lock Read) to make the data unchanged)

Show Master status (remember to restart the MySQL service before viewing)

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/80/58/wKioL1c-dxnzazW6AAAeQNuDSp0781.png "title=" master. png " alt= "Wkiol1c-dxnzazw6aaaeqnudsp0781.png"/>

From: Configuration file changes:
Server-id=2 not the same as the Lord)
Restart MYSQLD Service

Log in from MySQL

slave stop; stop from the service

Log in from MySQL and write the following command:

Change Master to master_host= ' 192.168.75.131 ', master_port=3306, master_user= ' repl ', master_password= ' 123123 ',      Master_log_file= ' mysql-bin.000018 ', master_log_pos=106; (Both of the bold need to be consistent with the above master view, each time the MySQL service restarts,mysql-bin.000018 This will increase the 1,IP is the primary IP address )

slave startshow slave status\g; When the following two appear, the master-slave synchronization is correct
Slave_io_running:yes

Slave_sql_running:yes

Then log on to the main database and unlock it.

MYSQL-E "Unlock tables" (without landing MySQL to perform unlocking) unlock tables; (Login MySQL required)

Finally, you can do some work on the master to test the

Test: The Lord empties the DB table of the DB1 library: use DB1;

Lord Delete table Db:drop tables db;

Lord See the DB table does not exist show tables;

Then go to view from top: show tables;

Note: Do not do anything from above, otherwise the master and slave will stop

MySQL master-slave mechanism is relatively fragile, cautious operation. If you restart Master, be sure to stop the slave first, that is, you need to slave up the slave Stop command, and then go to restart the master MySQL service, otherwise it is likely to be interrupted. Of course, after the restart, you also need to slave to open slave start.

This article is from the "Drifting Away" blog, make sure to keep this source http://825536458.blog.51cto.com/4417836/1775299

MySQL Master-slave configuration