MySQL master-slave split read/write replication

Source: Internet
Author: User
Tags mysql version

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.