MySQL read-Write separation strategy

There are two steps to read and write for MySQL: The first step is to Configure master-slave replication for MySQL; the second step is to use mysql-proxy as MySQL does a read-write separation.

Mysqlmaster-slave replication

to ensure MySQL The master-slave replication is built successfully, it is best to ensure that two machines MySQL version is consistent. Enter the following command in MySQL to view the version information.

Select version ();

the two MySQL I used the addresses were 192.168.10.42 and the 192.168.10.49. the MySQL on the page is used as master , the MySQL on the used as slave .

Primary Database Configuration

Log in to 192.168.10.42, and locate the MySQL configuration file my.cnf. Open my.cnf below [mysqld] to add the following:

log-slow-queries=mysql-slow.loglog-error=mysql.errlog-bin=mysql-binserver-ID =1

The above configuration is primarily to turn on the binary logging feature of the primary database, and it is best to browse the my.cnf file as it is added, since some properties may already exist. After adding complete save exit, and then restart MySQL.

Service mysqld Restart

After the MySQL restart is complete, log in as root to assign the copy permission from the database to the primary database, the command is as follows

Grant Replication  on *. *  to ' User '@'192.168.10.49' by ' password ';

where "*. *" indicates which libraries and tables on the primary database can be replicated from the database, "user" and "password" is the primary database assigned to the user name and password from the database, set according to the requirements, this is only for example use. Set the user name and password to be used when starting from the database.

After permission assignment, enter the following command to view the user name, password, and IP address information that you just set .

Select Host,user from MySQL. user;

Then look at the status information of the primary database and enter the following command

Show master status;

Remember the values of the File and Position fields, which are used when the configuration is from the database.

From the database configuration

Log in to 191.168.10.49, locate the MySQL profile my.cnf, and under [mysqld], add the following:

log-slow-queries=mysql-slow.loglog-error=mysql.errlog-bin=mysql-binserver- id=ten

Before adding, it is best to browse the original configuration of this file, if it is configured enough, no more configuration. Restart the MySQL service after saving exit

Service mysqld Restart

Tests whether the user name and password assigned to the master data can be logged in to the primary database from the database.

- - -h192. 168.10.  the

If you can not log in, first check if you can ping . If you can ping the bucket, check to see if the firewall on the 192.168.10.42 is configured with the 3306 Port. No, the 3306 Port's release policy is added to the iptables , and then the iptablesis restarted.

3306 -j ACCEPT

Restart iptables

Service Iptables Restart

This way, you should be able to log in to the main database.

Log in as root from the database, configure slave information

Change Master to master_host='192.168.10.42', master_user='user' , master_password='password', master_log_file='mysql-bin.000005  ', master_log_pos=3065;

Then turn on the slave state from the database .

Slave start;

View slave information.

show slave status\g;

If slave_io_running and slave_sql_running are yes, the slave state is turned on successfully. Otherwise, see the specific reason according to the mysqld.log log.

Here,MySQL 's master-slave replication is set up. Add a few data on the primary database and you can do the same on the database. Stop the slave State from the database

Slave stop;

Then add the delete data to the primary database, and these operations will not affect the database.

ForMySQLdo the read and write separation function

MySQL read-write separation, using mysql-proxy to achieve. While mysql-proxy relies on the Lua script, the password on the machine installs lua.

the machine for installing mysql-proxy is 192.168.29.132.

The operating system is ubuntu14.1.

The version of Mysql-proxy is mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz.

Because we are using Ubuntu , the way to install Lua is simple.

Apt-get Install Lua

You can install LuaAccording to your system selection, ensuring that the Lua command executes. the mysql-proxy version is also based on its own operating system to make the appropriate choice.

Upload the mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz to the /home/mysql_proxy directory and unzip the

tar zxvf mysql-proxy-0.8. 2-LINUX-GLIBC2. 3-x86-64bit. Tar . gzcd MySQL-proxy-0.8. 2-LINUX-GLIBC2. 3-x86-64bitcp Share/doc/mysql-proxy/rw-splitting.lua./VI Rw-splitting.lua

Find the following fragment

if  not  Then         proxy. global.config.rwsplit = {                = 4,                = 8,                = false        }  End

For testing use change 4 and 8 to 1. In the production environment, according to the requirements can be modified accordingly. After modification as

Now it's time to start mysql-proxy , the command is as follows

./bin/mysql-proxy--proxy-read-only-backend-addresses=192.168. 10.49:3306 --proxy-backend-addresses=192.168. 10.42:3306 --proxy-lua-script=rw-splitting.lua

The console does not have an error output and starts normally.

The Mysql-proxy listening port is 4040, so it is open on 192.168.29.132 machine 4040 Port. In the iptables file, add the following:

4040 -j ACCEPT

Restart iptables

Service Iptables Restart

Enter the following command to view the port listening status.

grep 4040

As the mysql-proxy is set up on the 192.168.29.132 machine, you also need to configure this machine's permissions in the main database, as follows.

Grant  All  on *. *  to ' User '@'192.168.29.132' identified  by 'password  ';

Now MySQL read-write separation is complete, the structure is as follows

The application connects to Mysql-proxy , and all write requests are sent via the red line to master MySQL , and all read requests are sent via the Blue line to slave mysql ,the data is synchronized betweenmaster and slave through the Purple Line.

You can now test whether the read-write separation mechanism works. Connect to the mysql-proxy by using the following command . Then stop the slave State on the 192.168.10.49 .

MySQL -uuser -ppassword -h192. 168.29.  -P4040

On mysql-proxy , Insert several data through SQL. You can see The new data in MySQL on the No new data. Then Select on the Mysql-proxy , and you can see that the selected structure does not have a few data just inserted.

At this point,MySQL 's read-write separation is complete.

MySQL read-Write separation strategy