MySQL master-slave replication and read-write separation based on mysql-proxy (top)

MySQL master-slave replication and read-write separation based on mysql-proxy (top)

　　Last week the boss assigned a task to realize the MySQL database master-slave replication and read-write separation, and then spent a tea of the research, found that the master-slave replication database configuration directly can be implemented, and read-write separation requires some software support, There are basically two implementations of read and write separations:

• Amoeba (Amoeba): A proxy that is implemented by former Ali employees with MySQL as the underlying data store and provides the MySQL protocol interface to the application. However, because no one is maintained, and the author is said to no longer answer the developer's questions, so do not consider.
• Mysql-proxy: is a simple program between your client and MySQL server that can monitor, analyze, or change their communications. It is flexible, unlimited, and common uses include: load balancing, failure, query analysis, query filtering and modification, and so on. Simply put, MySQL Proxy is a connection pool that forwards the connection requests from the foreground application to the database in the background, and through the use of Lua scripting, enables complex connection control and filtering for read-write separation and load balancing. And there is a variety of information and maintenance, although it is necessary to understand some of the Lua language, but the language, it is very easy to get started.

　　After stability-based, problem-solving, ease-of-use, ease-of-integration with existing platforms, ease of implementation, and a variety of considerations, the final decision was to mysql-proxy the database for read-write separation. Talk so much, and then start talking about the specific implementation.

Environment and configuration

System: CentOS6.5

master:172.16.19.2

slave:172.16.19.24

mysql-proxy:172.16.19.14

Installation is not said, basically is the Yum install installation. is the basic architecture diagram (Forget where Ba la came from, thanks to the author).

Implementation of master-slave replication

　　The implementation of master-slave replication is extremely simple, only need to change vim/etc/my.cnf.

　　Configuration of Master:

vim/etc/my.cnflog-bin=mysql-bin   #新增server-id=1              #新增

after the configuration is complete, restart MySQL, and then execute the following command:

master:mysql> grant replication Slave on * * to ' root ' @ '% ' identified by ' 123456 '; then execute show Master Statusmysql& Gt show master status; +------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000005 | 261 | | |+------------------+----------+--------------+------------------+

　　Use your head or paper pen to record file and Positin, here is mysql-bin.000005 and 261.

　　Configuration of the Slave:

1 vim/etc/my.cnf2 log-bin=mysql-bin   #新增 3 server-id=2             #新增  Server-id can't be the same

　　Again, the MySQL service needs to be restarted after the configuration is complete, and then the following commands are executed:

mysql> Change Master to master_host= ' 172.16.19.2 ', master_user= ' root ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000005 ', master_log_pos=261; MySQL> Start slave;

Next, review the status of the slave to see if the configuration was successful:

1 Show Slave Status\g2==============================================31. Row *******************4 slave_io_state:5master_host:192.168.10.1306 Master_user:rep17master_port:33068Connect_retry:609master_log_file:mysql-bin.000005Tenread_master_log_pos:415 Onerelay_log_file:localhost-relay-bin.000008 Arelay_log_pos:561 -relay_master_log_file:mysql-bin.000005 - Slave_io_running:yes the Slave_sql_running:yes - replicate_do_db: - .........  To omit a number of ...... -Master_server_id:1 +1 row in Set (0.01sec) -==============================================

　　The value of slave_io_running and slave_sql_running must be yes to indicate that the status is normal.

Master-Slave Replication testing

First, look at the master server master:

　　

Then create a database, Y:

Then go to view from the server slave, you will find that the server also has a Y database. This is where the master-slave replication of the database is implemented. It's a bit late, as far as reading and writing are separated, let's say it again.

PS: This blog Welcome to forward, but please specify the blog address and author, because I level limited, if there is wrong, welcome point, Thank you ~

Blog Address: http://www.cnblogs.com/voidy/

Blog: http://voidy.net

<. ))) ≦

Master-slave replication and read-write separation for MySQL based on Mysql-proxy (top)