Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people are at a loss about how to achieve the separation of master-slave replication and reading and writing in MySQL. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1the configuration of MySQL

CentOS 5.xpro 6.0 compiles and installs Nginx1.2.3+MySQL5.5.15+PHP5.3.6

I did the test in this environment. I configured one. Use VMware Workstation to copy out multiple servers, so the environment is absolutely the same.

2, scene description

Main database server: 192.168.10.130

From the database server: 192.168.10.131

3, configure the primary server

Modify the configuration file of the slave server, the my.cnf may not necessarily have the same path as me, oh, pay attention to the modification

Vi etc/my.cnf

Make sure that server-id = 1 and make sure that this ID is not used by other MySQL services.

Start the mysql service (192.168.10.130)

/ etc/init.d/mysqld start

Log in to manage the MySQL server through the command line

Mysql-uroot-p

Authorized to slave database server 192.168.10.131

Mysql > GRANT REPLICATION SLAVE ON *. * to 'rep1'@'192.168.10.131' identified by' 123'

Query the status of the master database

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000005 | 261 |

+-+

Record the values of FILE (mysql-bin.000005) and Position (261), which are needed later for slave server operations.

4, configure slave server

Modify the configuration file of the slave server, the my.cnf may not necessarily have the same path as me, oh, pay attention to the modification

Vi etc/my.cnf

Change server-id = 1 to server-id = 2 and make sure that the ID is not used by other MySQL services.

Start the mysql service

/ opt/mysql/init.d/mysql start

Log in to manage the MySQL server through the command line

Mysql-uroot-p

Execute synchronous SQL statement

Mysql > change master to

Master_host='192.168.10.130'

Master_user='rep1'

Master_password='123'

Master_log_file='mysql-bin.000005'

Master_log_pos=261

Note that the above data should be the same as the main server, otherwise the synchronization will fail!

Start the Slave synchronization process after correct execution

Mysql > start slave

Master-slave synchronization check

Mysql > show slave status\ G

The values of Slave_IO_Running and Slave_SQL_Running must be YES to indicate that the state is normal.

If not, please check to see if there is a problem with that step! You can leave me a message. If all YES can proceed to the next step

5. Synchronize master-slave server data for the first time

Lock the primary server mysql:

Mysql > FLUSH TABLES WITH READ LOCK

Lock slave server mysql:

Mysql > FLUSH TABLES WITH READ LOCK

Copy the data file of the master server (the entire / opt/mysql/data directory) to the slave server. It is recommended that you archive and compress it through tar and then transfer it to the slave server to decompress the overlay.

Open the master server mysql:

Mysql > UNLOCK TABLES

Enable slave server mysql:

Mysql > UNLOCK TABLES;6, test whether to synchronize

Operate on the primary server:

Mysql > create database first_db; mysql > use first_db; mysql > create table first_tb (id int (3), name char (10)); mysql > INSERT INTO `first_ tb` VALUES ('1th,' 1')

View it from the server.

Mysql > show databases

= =

+-+

| | Database |

+-+

| | information_schema |

| | first_db |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.01 sec)

= =

After reading the above, have you mastered the method of how to achieve master-slave replication and read-write separation in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.