MySQL master-slave and read-write splitting Configuration

Recently developed systems use MySQL as a database. Because the data involves money, you have to exercise caution. At the same time, the user also puts forward requirements for the maximum traffic. To prevent MySQL from becoming a performance bottleneck and have good fault tolerance capabilities, we hereby implement master-slave hot standby and read/write splitting. Make a summary here for future use!

1. Configure Master/Slave

Condition: the IP addresses of two PCs are 192.168.168.253 and 192.168.168.20. The MySQL version on the two PCs is 5.0. MySQL on MySQL 253 is the master and MySQL on 251 is the slave.

1. master database server configuration

Go to the installation directory of the master database server, open my. ini, and add the following configuration at the end of the file:

# Database ID. If it is 1, it is expressed as master. master_id must be a positive integer between 1 and 232-1. Server-id = 1 # enable binary log; log-bin = mysql-bin # Name of the binary database to be synchronized; BINLOG-do-DB = minishop # Name of the non-synchronous binary database. If this parameter is not set, comment it out; BINLOG-ignore-DB = information_schemabinlog-ignore-db = mysqlbinlog-ignore-DB = personalsitebinlog-ignore-DB = test # Set the generated log file name; log-bin = "D: /database/materlog "# Write updated records to binary files; log-slave-Updates

Save the file. Restart the MySQL service.

Go to the installation directory from the database server, open my. ini, and add the following configuration at the end of the file:

# If you need to add the slave library, this ID will be postponed; server-id = 2 log-bin = mysql-bin # master database hostmaster-host = 192.168.168.253 # Master-user = forslavemaster created on the master database server for backup from the server -Password = ****** master-Port = 3306 # If the master server is disconnected, time Difference of reconnection; master-connect-retry = 60 # databases that do not need to be backed up; replicate-ignore-DB = MySQL # replicate-do-DB = minishoplog-slave-Update

Save the file. Restart the MySQL service.

Go to the master database server, create the user name and password used for the backup, and authorize replication slave, super, and reload

mysql>grant replication slave,super,reload on minishop.* to forslave@192.168.168.251 identified by '******';

Go to the slave database server and start slave.

mysql>slave start;mysql>show slave status\G;

Test: log on to the master database server, insert a data entry in a table in minishop, and check whether the inserted data exists in the database server. Finished!

Note: 1) when the master database server runs the configuration prior to the slave database server, the file and position of the master database are the same as those of master_log_file and read_master_log_pos. Otherwise, inconsistency may occur. This can also be adjusted using commands.

2) If the master-slave replication fails, you can first shut down the slave database server, and then delete the files from the database server under the Data Directory relay-log.info, hosname-relay-Bin *, master.info, etc, restart the slave server.

Ii. read/write splitting Configuration

I wanted to use MySQL proxy to implement read/write separation, but the Lua script I used was really a headache. Finally, I decided to use the open source database Proxy Middleware amoeba developed by Chinese people. With amoeba, you can easily implement read/write splitting by simply configuring XML.

Amoeba is located between the application and the database server and acts as an intermediate proxy layer. It supports load balancing, high availability, query filtering, read/write splitting, routing-related query to the target database, and concurrent requests to merge results from multiple databases. Powerful functions.

Amoeba uses the default port 8066 to implement the MySQL protocol. In the application, you only need to modify a database connection to use amoeba for proxy database access. For example, in a Java application, if your original JDBC connection string is JDBC: mysql: // 192.168.168.42: 3306/minishop, you want to use amoeba as the database access proxy, you only need to change the above connection string to the following (if the sub-IP address of the amoeba is 192.168.168.88): JDBC: mysql: // 192.168.168.88: 8066/minishop. Amoeba transparency is awesome.

The main task is to configure amoeda, but the configuration is quite simple. You only need to configure two files: conf \ dbservers. xml and conf \ amoeba. xml. For more information about the configurations, see the amoeda Chinese guide. Only the configuration is recorded.

Dbservers. xml main configuration

<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/"><!-- Each dbServer needs to be configured into a Pool,If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration: add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig such as 'multiPool' dbServer   --><dbServer name="abstractServer" abstractive="true"><factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory"><property name="manager">${defaultManager}</property><property name="sendBufferSize">64</property><property name="receiveBufferSize">128</property><!-- mysql port --><property name="port">3306</property><!-- mysql schema --><property name="schema">minishop</property><!-- mysql user --><property name="user">chenjie</property><!--  mysql password --><property name="password">chenjie</property></factoryConfig><poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool"><property name="maxActive">500</property><property name="maxIdle">500</property><property name="minIdle">10</property><property name="minEvictableIdleTimeMillis">600000</property><property name="timeBetweenEvictionRunsMillis">600000</property><property name="testOnBorrow">true</property><property name="testWhileIdle">true</property></poolConfig></dbServer><dbServer name="master"  parent="abstractServer"><factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.168.253</property></factoryConfig></dbServer><dbServer name="slave1"  parent="abstractServer"><factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.168.119</property></factoryConfig></dbServer><dbServer name="slave2"  parent="abstractServer"><factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.168.251</property></factoryConfig></dbServer><dbServer name="multiPool" virtual="true"><poolConfig class="com.meidusa.amoeba.server.MultipleServerPool"><!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA--><property name="loadbalance">1</property><!-- Separated by commas,such as: server1,server2,server1 --><property name="poolNames">slave1,slave2</property></poolConfig></dbServer></amoeba:dbServers>

Amoeba. xml configuration:

<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/"><proxy><!-- service class must implements com.meidusa.amoeba.service.Service --><service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager"><!-- port --><property name="port">8066</property><!-- bind ipAddress --><property name="ipAddress">192.168.168.253</property><property name="manager">${clientConnectioneManager}</property><property name="connectionFactory"><bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory"><property name="sendBufferSize">128</property><property name="receiveBufferSize">64</property></bean></property><property name="authenticator"><bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator"><property name="user">chenjie</property><property name="password">chenjie</property><property name="filter"><bean class="com.meidusa.amoeba.server.IPAccessController"><property name="ipFile">${amoeba.home}/conf/access_list.conf</property></bean></property></bean></property></service><!-- server class must implements com.meidusa.amoeba.service.Service --><service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer"><!-- port --><!--  default value: random number<property name="port">9066</property>--><!-- bind ipAddress --><property name="ipAddress">127.0.0.1</property><property name="daemon">true</property><property name="manager">${clientConnectioneManager}</property><property name="connectionFactory"><bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean></property></service><runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext"><!-- proxy server net IO Read thread size --><property name="readThreadPoolSize">20</property><!-- proxy server client process thread size --><property name="clientSideThreadPoolSize">30</property><!-- mysql server data packet process thread size --><property name="serverSideThreadPoolSize">30</property><!-- per connection cache prepared statement size  --><property name="statementCacheSize">500</property><!-- query timeout( default: 60 second , TimeUnit:second) --><property name="queryTimeout">60</property></runtime></proxy><!-- Each ConnectionManager will start as threadmanager responsible for the Connection IO read , Death Detection--><connectionManagerList><connectionManager name="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper"><property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property><!--   default value is avaliable Processors <property name="processors">5</property> --></connectionManager><connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper"><property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property><!--   default value is avaliable Processors <property name="processors">5</property> --></connectionManager></connectionManagerList><!-- default using file loader --><dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader"><property name="configFile">${amoeba.home}/conf/dbServers.xml</property></dbServerLoader><queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter"><property name="ruleLoader"><bean class="com.meidusa.amoeba.route.TableRuleFileLoader"><property name="ruleFile">${amoeba.home}/conf/rule.xml</property><property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property></bean></property><property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property><property name="LRUMapSize">1500</property><property name="defaultPool">master</property><property name="writePool">master</property><property name="readPool">multiPool</property><property name="needParse">true</property></queryRouter></amoeba:configuration>

So far, the configuration of MySQL master-slave hot standby and read/write splitting is complete. However, the specific application to the production environment remains to be tested and tested. Later, I tested a master-slave database and added a MySQL slave database server, which is why the above amoeba configuration has an IP address of 119.