MySQL read and write separation practice - code examples for building high-performance web

A complete mysql read-write separation environment includes the following parts:

• Application client

• database proxy

• database cluster

In this actual combat, the application client connects to the back-end database proxy based on c3p0. The database proxy is responsible for managing the routing strategy for clients to actually access the database, using the open source framework amoeba. The database cluster uses mysql's master-slave replication solution. The structure diagram of the entire environment is as follows:

Practical steps and detailed explanation

1. Build the mysql master-slave environment

1) Install mysql (5.0.45) on host1 (10.20.147.110) and host2 (10.20.147.111) respectively. The specific installation method can be found in the official document

2) Configure master

First edit /etc/my.cnf and add the following configuration:

log-bin=mysql-bin #slave会基于此log-bin来做replication
server-id=1 #master的标示
binlog-do-db = amoeba_study #用于master-slave的具体数据库

Then add a user specifically for replication:

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@10.20.147.111 IDENTIFIED BY '111111';

Restart mysql to make the configuration take effect:

/etc/init.d/mysqld restart

Finally check the master status:

3) Configure slave

First edit /etc/my.cnf and add the following configuration:

server-id=2 #slave's label

After the configuration takes effect, configure the connection with the master:

mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.20.147.110',
   
 -> MASTER_USER='repl',
    -> MASTER_PASSWORD='111111',
   
 -> MASTER_LOG_FILE='mysql-bin.000003',
    -> 
MASTER_LOG_POS=161261;

where MASTER_HOST is the ip of the master machine, MASTER_USER and MASTER_PASSWORD are us The user just added on the master, MASTER_LOG_FILE and MASTER_LOG_POS correspond to the information in the master status

Finally start the slave:

mysql> start slave;

4) Verify that the master-slave setup takes effect

Check the slave machine's log (/var/log/mysqld.log):

100703 10:51:42 [Note] Slave I/O thread: connected to master 'repl@10.20.147.110:3306',  
replication started in log 'mysql-bin.000003' at position 161261

If you see the above information, it proves that the setup is successful. If there is a problem, you can also find the reason through this log

2. Build database proxy

In this actual combat, the database proxy uses amoeba , its related information can be found in the official documents, and will not be detailed here

1) Install amoeba

Download amoeba (1.2.0-GA) and unzip it locally (D:/openSource/amoeba -mysql-1.2.0-GA), that is, the installation is completed

2) Configure amoeba

First configure the proxy connection and the connection information with each back-end mysql server (D:/openSource/amoeba- mysql-1.2.0-GA/conf/amoeba.xml):

The above is the connection configuration provided by proxy to the client

<dbServerList>  
    <dbServer name="server1">           
        <!-- PoolableObjectFactory实现类 -->  
        <factoryConfig class="com.meidusa.amoeba.mysql
        <a href="http://lib.csdn.net/base/dotnet" class=&#39;replace_word&#39; title=".NET知识库" target=&#39;_blank&#39; style=&#39;color:#df3434; font-weight:bold;&#39;>.NET</a>
        .MysqlServerConnectionFactory">  
            <property name="manager">defaultManager</property>  
              
            <!-- 真实mysql数据库端口 -->  
            <property name="port">3306</property>  
              
            <!-- 真实mysql数据库IP -->  
            <property name="ipAddress">10.20.147.110</property>  
            <property name="schema">amoeba_study</property>  
              
            <!-- 用于登陆mysql的用户名 -->  
            <property name="user">root</property>  
              
            <!-- 用于登陆mysql的密码 -->  
            <property name="password"></property>  
              
        </factoryConfig>  
          
        <!-- ObjectPool实现类 -->  
        <poolConfig class="com.meidusa.amoeba
        <a href="http://lib.csdn.net/base/dotnet" class=&#39;replace_word&#39; title=".NET知识库" target=&#39;_blank&#39; style=&#39;color:#df3434; font-weight:bold;&#39;>.Net</a>
        .poolable.PoolableObjectPool">  
            <property name="maxActive">200</property>  
            <property name="maxIdle">200</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="server2">  
          
        <!-- PoolableObjectFactory实现类 -->  
        <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">  
            <property name="manager">defaultManager</property>  
              
            <!-- 真实mysql数据库端口 -->  
            <property name="port">3306</property>  
              
            <!-- 真实mysql数据库IP -->  
            <property name="ipAddress">10.20.147.111</property>  
            <property name="schema">amoeba_study</property>  
              
            <!-- 用于登陆mysql的用户名 -->  
            <property name="user">root</property>  
              
            <!-- 用于登陆mysql的密码 -->  
            <property name="password"></property>  
              
        </factoryConfig>  
          
        <!-- ObjectPool实现类 -->  
        <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">  
            <property name="maxActive">200</property>  
            <property name="maxIdle">200</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>         
</dbServerList>

The above is the configuration information of proxy and back-end mysql database server, The specific configuration is very clear in the comments

Finally configure the read-write separation strategy:

From the above configuration, it is found that the write operation is routed to server1 (master), and the read operation is routed to server2 (slave)

3) Start amoeba

Run D:/openSource/amoeba-mysql-1.2.0-GA/amoeba.bat in the command line:

log4j:WARN log4j config load completed from file:D:/openSource/amoeba-mysql-1.2.0-GA/conf/log4j.xml
log4j:WARN ip access config load completed from file:D:/openSource/amoeba-mysql-1.2.0-GA/conf/access_list.conf
2010-07-03 09:55:33,821 INFO  net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066.

三.Client-side calling and testing

1) Write the client calling program

The specific program details will not be detailed, it is just the most common one based on mysql driver jdbc database operation program

2) Configure database connection

This client is based on c3p0, the specific data source configuration is as follows:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"  
    destroy-method="close">  
    <property name="driverClass" value="com.mysql.jdbc.Driver" />  
    <property name="jdbcUrl" value="jdbc:mysql://localhost:8066/amoeba_study" />  
    <property name="user" value="root" />  
    <property name="password" value="root" />  
    <property name="minPoolSize" value="1" />  
    <property name="maxPoolSize" value="1" />  
    <property name="maxIdleTime" value="1800" />  
    <property name="acquireIncrement" value="1" />  
    <property name="maxStatements" value="0" />  
    <property name="initialPoolSize" value="1" />  
    <property name="idleConnectionTestPeriod" value="1800" />  
    <property name="acquireRetryAttempts" value="6" />  
    <property name="acquireRetryDelay" value="1000" />  
    <property name="breakAfterAcquireFailure" value="false" />  
    <property name="testConnectionOnCheckout" value="true" />  
    <property name="testConnectionOnCheckin" value="false" />  
</bean>

It is worth noting that the client only needs to connect to proxy has nothing to do with the actual database, so the jdbcUrl, user, and password configurations all correspond to the configuration information exposed by amoeba

3) Calling and testing

First insert a piece of data:

insert into zone_by_id(id,name) values(20003,'name_20003')

By checking the log /var/lib/mysql/mysql_log.log on the master machine:

100703 11:58:42       1 Query       set names latin1
                      1 Query       SET NAMES latin1
                      1 Query       SET character_set_results = NULL
                      1 Query       SHOW VARIABLES
                      1 Query       SHOW COLLATION
                      1 Query       SET autocommit=1
                      1 Query       SET sql_mode='STRICT_TRANS_TABLES'
                      1 Query       SHOW VARIABLES LIKE 'tx_isolation'
                      1 Query       SHOW FULL TABLES FROM `amoeba_study` LIKE 'PROBABLYNOT'
                      1 Prepare     [1] insert into zone_by_id(id,name) values(?,?)
                      1 Prepare     [2] insert into zone_by_id(id,name) values(?,?)           
                      1 Execute     [2] insert into zone_by_id(id,name) values(20003,'name_20003')

Know that the write operation occurred on the master machine

By checking the log on the slave machine/ var/lib/mysql/mysql_log.log:

100703 11:58:42       2 Query       insert into zone_by_id(id,name) values(20003,'name_20003')

I learned that the slave executed this statement synchronously

Then check a piece of data: select t.name from zone_by_id t where t. id = 20003

By checking the log /var/lib/mysql/mysql_log.log on the slave machine:

100703 12:02:00      33 Query       set names latin1
                     33 Prepare     [1] select t.name from zone_by_id t where t.id = ?
                     33 Prepare     [2] select t.name from zone_by_id t where t.id = ?    
                     33 Execute     [2] select t.name from zone_by_id t where t.id = 20003

It is known that the read operation occurred on the slave machine

and by checking The log /var/lib/mysql/mysql_log.log on the slave machine found that this statement was not executed on the master

Through the above verification, it is known that the simple master-slave setup and actual combat can take effect

The above is the detailed content of MySQL read and write separation practice - code examples for building high-performance web. For more information, please follow other related articles on the PHP Chinese website!