[面试]mysql主从复制原理，3三种读写分离的方式，mysql主从复制和读写分离

来源：互联网发布：千牛手机淘宝店铺装修编辑：程序博客网时间：2024/06/16 12:05

主从复制（Master-Slave）的方式来同步数据，再通过读写分离（主增删改，从查）来提升数据库的并发的负载均衡能力

一、MySQL主从复制

原理：主从复制的原理其实就是从服务器读取主服务器的binlog，然后根据binlog的记录来更新数据库。

场景描述：
主数据库服务器：192.168.10.130，MySQL已经安装，并且无应用数据。
从数据库服务器：192.168.10.131，MySQL已经安装，并且无应用数据。（VMWare克隆虚拟主机）

1.1 主服务器上进行的操作
启动mysql服务
/opt/mysql/init.d/mysql start

通过命令行登录管理MySQL服务器
/opt/mysql/bin/mysql -uroot -p'new-password'

授权给从数据库服务器192.168.10.131
mysql> grant replication slave on *.* to 'rep1'@'192.168.10.131' identified by ‘123456’; //192.168.10.131也可以改成%，匹配当前主机外的所有主机

记录下 FILE 及 Position 的值，在后面进行从服务器操作的时候需要用到。

1.2 配置从服务器

检查从服务器通过rep1账户能否进入主服务器的数据库

service mysqld start; //第一次使用得开启mysql服务，以后就不用了

mysql> mysql -h192.168.10.130 -urep1 -p123456;

修改从服务器的配置文件/opt/mysql/etc/my.cnf
将 server-id = 1修改为 server-id = 10，并确保这个ID没有被别的MySQL服务所使用。

启动mysql服务
/opt/mysql/init.d/mysql start

通过命令行登录管理MySQL服务器
/opt/mysql/bin/mysql -uroot -p'new-password'

执行同步SQL语句
mysql> change master to
master_host=’192.168.10.130’,
master_user=’rep1’,
master_password=’password’,
master_log_file=’mysql-bin.000005’,
master_log_pos=261;

正确执行后启动Slave同步进程
mysql> start slave;

主从同步检查
mysql> show slave status\G
==============================================
**************** 1. row *******************
Slave_IO_State:
Master_Host: 192.168.10.130
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 415
Relay_Log_File: localhost-relay-bin.000008
Relay_Log_Pos: 561
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: YES
Slave_SQL_Running: YES
Replicate_Do_DB:
……………省略若干……………
Master_Server_Id: 1
1 row in set (0.01 sec)
==============================================

其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。

我在操作的时候遇到Slave_IO_Running=false,

解决办法：http://blog.csdn.net/cug_jiang126com/article/details/46846031，找到/data/mysql/auto.cnf文件，修改里面的uuid值，保证主从db的uuid不一样，然后service mysqld restart重启数据库

如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：
(1)主数据库进行锁表操作，不让数据再进行写入动作
mysql> flush tables with read lock;

(2)查看主数据库状态
mysql> show master status;

(3)复制数据文件
将主服务器的数据文件（整个/opt/mysql/data目录）复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。

(4)取消主数据库锁定
mysql> unlock tables;

1.3 验证主从复制效果

主服务器上的操作
在主服务器上创建数据库first_db
mysql> create database first_db;
Query Ok, 1 row affected (0.01 sec)

在主服务器上创建表first_tb
mysql> create table first_tb(id int(3),name char(10));
Query Ok, 1 row affected (0.00 sec)

在主服务器上的表first_tb中插入记录
mysql> insert into first_tb values (001,’myself’);
Query Ok, 1 row affected (0.00 sec)

mysql> use first_db
Database chaged

mysql> show tables;
=============================
+--------------------+
| Tables_in_first_db |
+--------------------+
| first_tb |
+--------------------+
1 row in set (0.02 sec)
=============================
数据库表first_tb也已经自动创建

mysql> select * from first_tb;
=============================
+------+------+
| id | name |
+------+------+
| 1 | myself |
+------+------+
1 rows in set (0.00 sec)
=============================
记录也已经存在

由此，整个MySQL主从复制的过程就完成了，接下来，我们进行MySQL读写分离的安装与配置。

二、MySQL读写分离

2.1 三种读写分离的方法比较

1 程序修改mysql操作类（推荐，不安装软件时）
可以参考PHP实现的Mysql读写分离，code.google.com/p/mysql-rw-php/ 阿权开始的本项目，以php程序解决此需求。
优点：直接和数据库通信，简单快捷的读写分离和随机的方式实现的负载均衡，权限独立分配
缺点：自己维护更新，增减服务器在代码处理
2 amoeba（推荐，可安装软件时）
参考官网：http://amoeba.meidusa.com/
优点：直接实现读写分离和负载均衡，不用修改代码，有很灵活的数据解决方案
缺点：自己分配账户，和后端数据库权限管理独立，权限处理不够灵活
3 mysql-proxy
参考 mysql-proxy。
优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号
缺点：字符集问题，lua语言编程，还只是alpha版本，时间消耗有点高。
非常不稳定，在高并发或有错误连接的情况下，进程很容易自动关闭，因此打开--keepalive参数让进程自动恢复是个比较好的办法，但还是不能从根本上解决问题，因此通常最稳妥的做法是在每个从服务器上安装一个MySQL-Proxy供自身使用，虽然比较低效但却能保证稳定性；
===>总结：如果你不能安装软件来解决读写分离，那可以尝试程序修改mysql操作类。如果你可以安装软件，那amoeba是不错的，mysql-proxy不太建议，目前只有alpha版本，效率还不太理想，amoeba目前在阿里巴巴是内部项目，正在生产环境使用的。

2.2 amoeba实现读写分离的过程

主数据库服务器：10.20.147.110，从数据库服务器：10.20.147.111

1）安装amoeba

下载amoeba（1.2.0-GA）后解压到本地（D:\openSource\amoeba-mysql-1.2.0-GA），即完成安装

2）配置amoeba

先配置proxy连接和与各后端mysql服务器连接信息（D:\openSource\amoeba-mysql-1.2.0-GA\conf\amoeba.xml）：

    <server>          <!-- proxy server绑定的端口 -->          <property name="port">8066</property>                     <!-- proxy server绑定的IP -->          <!--           <property name="ipAddress">127.0.0.1</property>           -->          <!-- 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>                     <!-- socket Send and receive BufferSize(unit:K)  -->          <property name="netBufferSize">128</property>                     <!-- Enable/disable TCP_NODELAY (disable/enable Nagle's algorithm). -->          <property name="tcpNoDelay">true</property>                     <!-- 对外验证的用户名 -->          <property name="user">root</property>                     <!-- 对外验证的密码 -->          <property name="password">root</property>      </server>

Xhtml代码

    <server>          <!-- proxy server绑定的端口 -->          <property name="port">8066</property>                    <!-- proxy server绑定的IP -->          <!--          <property name="ipAddress">127.0.0.1</property>          -->          <!-- 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>                    <!-- socket Send and receive BufferSize(unit:K)  -->          <property name="netBufferSize">128</property>                    <!-- Enable/disable TCP_NODELAY (disable/enable Nagle's algorithm). -->          <property name="tcpNoDelay">true</property>                    <!-- 对外验证的用户名 -->          <property name="user">root</property>                    <!-- 对外验证的密码 -->          <property name="password">root</property>      </server>

以上是proxy提供给client的连接配置

<dbServerList>      <dbServer name="server1">                    <!-- 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.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.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>      <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>

Xhtml代码

    <dbServerList>          <dbServer name="server1">                       <!-- 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.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.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>          <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>

以上是proxy与后端各mysql数据库服务器配置信息，具体配置见注释

最后配置读写分离策略：

<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">      <property name="LRUMapSize">1500</property>      <property name="defaultPool">server1</property>      <property name="writePool">server1</property>      <property name="readPool">server2</property>      <property name="needParse">true</property>  </queryRouter>

Xhtml代码

    <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">          <property name="LRUMapSize">1500</property>          <property name="defaultPool">server1</property>          <property name="writePool">server1</property>          <property name="readPool">server2</property>          <property name="needParse">true</property>      </queryRouter>

从以上配置不然发现，写操作路由到server1（master），读操作路由到server2（slave）

3）启动amoeba

在命令行里运行D:\openSource\amoeba-mysql-1.2.0-GA\amoeba.bat即可：

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端调用与测试

1）编写client调用程序

具体程序细节就不详述了，只是一个最普通的基于mysql driver的jdbc的数据库操作程序

2）配置数据库连接

本client基于c3p0，具体数据源配置如下：

    <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>

Xhtml代码

    <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>

值得注意是，client端只需连到proxy，与实际的数据库没有任何关系，因此jdbcUrl、user、password配置都对应于amoeba暴露出来的配置信息

3）调用与测试

首先插入一条数据：insert into zone_by_id(id,name) values(20003,'name_20003')

通过查看master机上的日志/var/lib/mysql/mysql_log.log：

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')

得知写操作发生在master机上

通过查看slave机上的日志/var/lib/mysql/mysql_log.log：

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

得知slave同步执行了这条语句

然后查一条数据：select t.name from zone_by_id t where t.id = 20003

通过查看slave机上的日志/var/lib/mysql/mysql_log.log：

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

得知读操作发生在slave机上

并且通过查看slave机上的日志/var/lib/mysql/mysql_log.log发现这条语句没在master上执行

通过以上验证得知简单的master-slave搭建和实战得以生效。

转自：heylinux.com/archives/1004.html

http://kakaluyi.iteye.com/blog/748883

阅读全文

1 0