MySQL-One master and multiple slaves read and write separation configuration method based on amoeba

MySql is used as the database in the recently developed system. Since the data involves Money, we have to be cautious. At the same time, users also put forward requirements for the maximum number of visits. In order to prevent MySQL from becoming a performance bottleneck and to have good fault tolerance, master-slave hot backup and read-write separation are implemented. I’ll keep a brief note here for future reference!

1. Configure master and slave

Conditions: Two PCs, IPs are 192.168.168.253 and 192.168.168.251 respectively. The Mysql version on both PCs is 5.0. Mysql on 253 is Master, and Mysql on 251 is Slave.

1. Main database server configuration

Enter the main database server installation directory, open my.ini, and add the following configuration at the end of the file:

#数据库ID号， 为1时表示为Master,其中master_id必须为1到232–1之间的一个正整数值; 
server-id = 1
#启用二进制日志；
log-bin=mysql-bin
#需要同步的二进制数据库名；
binlog-do-db=minishop
#不同步的二进制数据库名,如果不设置可以将其注释掉;
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=personalsite
binlog-ignore-db=test
#设定生成的log文件名；
log-bin="D:/Database/materlog"
#把更新的记录写到二进制文件中；
log-slave-updates

Save the file. Restart the Mysql service.

Enter the installation directory of the secondary database server, open my.ini, and add the following configuration at the end of the file:

#如果需要增加Slave库则，此id往后顺延；
server-id = 2  
log-bin=mysql-bin
#主库host
master-host = 192.168.168.253
#在主数据库服务器中建立的用于该从服务器备份使用的用户
master-user = forslave
master-password = ******
master-port = 3306
#如果发现主服务器断线，重新连接的时间差；
master-connect-retry=60
#不需要备份的数据库； 
replicate-ignore-db=mysql
#需要备份的数据库
replicate-do-db=minishop
log-slave-update

Save the file. Restart the Mysql service.

Enter the main database server, create the user name and password used for the above backup, and authorize replication slave, super and reload

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

Enter the slave database server and start Slave.

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

Test: Enter the main database server, insert a piece of data into a table in Minishop, and then check whether the slave database server contains the data just inserted. complete!

Remarks: 1) Run the configured master database server before the slave database server. In this way, when running the slave database server, the File and Position of the master database will be consistent with the Master_Log_File and Read_Master_Log_Pos settings of the slave database. Otherwise, inconsistencies may occur. This can also be adjusted via commands.

2) If you find that the master-slave replication fails, you can first shut down the slave database server, and then delete the relay-log.info, hosname-relay-bin*, master.info and other files in the data directory of the slave database server. , restart the slave server.

2. Read-write separation configuration

I originally wanted to use Mysql Proxy to achieve read-write separation, but the lua script used was really a headache. In the end, I decided to use the open source database proxy middle developed by Chinese people. Pieces of Amoeba. Using Amoeba, reading and writing separation can be easily achieved with simple xml configuration.

Amoeba is between the application and the database server, acting as an intermediate proxy layer. It supports load balancing, high availability, Query filtering, read-write separation, can route related queries to the target database, and can concurrently request multiple databases to merge results. The function is very powerful.

The default port of Amoeba is 8066, which implements the Mysql protocol. The application only needs to modify a database connection to use Amoeba to proxy database access. For example: in a java application, if your original jdbc connection string is: jdbc:mysql://192.168.168.42:3306/minishop, then if you want to use Amoeba as the database access proxy, you only need to change the above connection string Change it to the following (if the IP of the machine where Amoeba is located is 192.168.168.88): jdbc:mysql://192.168.168.88:8066/minishop. Amoeba does a great job with transparency.

The main thing is to configure Amoeda, but the configuration is also quite simple. Basically, only two files need to be configured: conf\dbServers.xml and conf\amoeba.xml. For the meaning of each item in the configuration, you can refer to the amoeda Chinese guide. I won’t explain too much here. Just record the configuration.

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 &#39;multiPool&#39; 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 thread
		manager 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>

At this point, Mysql master-slave hot standby and read-write separation are configured. However, the specific application in the production environment has yet to be tested and investigated. Later, when testing one master and multiple slaves, a Mysql slave database server was added. This is why there is an additional IP of 119 in the amoeba configuration above.

The above is the detailed content of MySQL-One master and multiple slaves read and write separation configuration method based on amoeba. For more information, please follow other related articles on the PHP Chinese website!