MySQL master-slave split read/write replication

The test results show that the performance of the system can be significantly improved by separating the database from the reading and writing in the high-load production environment. The following is a read- write separation of MySQL.

Test environment

Machine A: ip:192.168.0.1 mysql version: mysql-5.6.4, master Data server (write-only operation)

B machine: ip:192.168.0.2 mysql version: mysql-5.6.4, from the data server (read-only operation)

Steps

1, modify the main database server configuration file, under Windows for My.ini,linux: MY.CNF. We add the following two lines to the configuration file [mysqld] Area:

Server-id = 1--Each MySQL instance is different, usually an integer, where 1 is written.

Log-bin=mysql-bin--copy using MySQL binary mode, this step must be configured.

Use the command to create the user backup:mysql> GRANT REPLICATION SLAVE on *. * to [email protected] identified by ' 111111 ' for copying data;

2. Restart main MySQL, enter show master Status\g; command to display the status of the primary database server:

3. In the configuration file [mysqld] from the database server, add the following two lines:

Server-id = 2--Each MySQL instance is different, usually an integer, where 2 is written.

Log-bin=mysql-bin--copy using MySQL binary mode.

4. Log in from the database server and enter the following command:

Mysql> Change MASTER to
Master_host= ' 192.168.0.1 ',
-Master_user= ' backup ',
-master_password= ' 111111 ',
-master_log_file= ' mysql-bin.000003 '-this is a FILE that corresponds to the state of the primary server
master_log_pos=370558; ---here for position of the primary server state

5, the command starts from the server slave, mysql> start slave;

6. View the status from the server: show slave status\g Note the following two states are yes, indicating that the operation is normal:

Slave_io_running:yes
Slave_sql_running:yes

7, testing, the main data server to insert, UPDATE, delete data, view data from the server