mysql主从复制+读写分离

配置主服务器(master)

1、编辑数据库配置文件my.cnf,一般在/etc/目录下。

#vi /etc/my.cnf

在[mysqld]的下面加入下面代码：

log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-do-db=wordpress
binlog_ignore_db=mysql

server-id=1中的1可以任定义，只要是唯一的就行。
binlog-do-db=wordpress是表示只备份wordpress。
binlog_ignore_db=mysql表示忽略备份mysql。
不加binlog-do-db和binlog_ignore_db，那就表示备份全部数据库。
2、然后重启MySQL:

#service mysqld restart

3、登录MySQL服务器。

#mysql -uroot -p

在主服务器新建一个用户赋予“REPLICATION SLAVE”的权限。你不需要再赋予其它的权限。在下面的命令，把X.X.X.X替换为从服务器的IP。

mysql>CREATE USER 'user'@'X.X.X.X' IDENTIFIED BY 'password';
mysql>GRANT REPLICATION SLAVE ON *.* TO 'user'@'X.X.X.X' IDENTIFIED BY 'password';

4、执行以下命令锁定数据库以防止写入数据。

mysql>FLUSH TABLES WITH READ LOCK;

这时不要退出mysql命令行，因为退出命令行后，全局表锁就失效，新开一个窗口继续执行以下命令。
5、导出数据库

#mysqldump -u root -p123456 --all-databases > /root/all.sql

6、再次连接数据库进入mysql命令行查看master状态。

mysql>SHOW MASTER STATUS;

mysql>UNLOCK TABLES;

8、使用scp命令传输数据库文件all.sql到从服务器。

#scp /root/all.sql root@www.example.com:/root

配置从服务器(slave)

登录从服务器。
1、导入主服务器的数据库。

#mysql -u root -p123456 < /root/all.sql

2、编辑配置文件my.cnf,在[mysqld]下面加入：

server-id=2

2可以自己定义，只要保证唯一的就行。
3、保存文件并重启mysqld。

#service mysqld restart

4、登录mysql服务器，执行以下命令。

mysql>CHANGE MASTER TO
MASTER_HOST='X.X.X.X',
MASTER_USER='user',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=98,
MASTER_CONNECT_RETRY=10;

MASTER_HOST:主服务器的IP。
MASTER_USER：配置主服务器时建立的用户名
MASTER_PASSWORD：用户密码
MASTER_PORT：主服务器mysql端口，如果未曾修改，默认即可。

这里注意 6和7就是前面查询到的结果
5、启动slave进程。

mysql>START SLAVE;

至此主从复制就做完了，可以进行测试。

下面是使用Amoeba for mysql实现mysql读写分离

Amoeba的安装

Amoeba下载地址：http://sourceforge.net/projects/amoeba/
下面是安装步骤：

cd /tmp
mkdir /usr/local/amoeba
wget http://softlayer.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/2.x/amoeba-mysql-binary-2.2.0-RC5.tar.gz
tar xzf amoeba-mysql-binary-2.2.0-RC5.tar.gz -C /usr/local/amoeba

我这里下载是的2.2版本3.X版本好像还是beta的，链接地址可能有错误，反正下载下来直接解压就可以用了，当然Amoeba是基于java写的，1.6我测试正常1.7没测试

所以需要配置环境变量，最后把/usr/local/amoeba也添加到环境变量里面，就可以直接使用命令了

主要配置很简单，主要是dbServer.xml和amoeba.xml

dbServer.xml

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
<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">test</property>
			
			<!-- mysql user -->
			<property name="user">root</property>
			
			<!--  mysql password -->
			<property name="password">test</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="testOnReturn">true</property>
			<property name="testWhileIdle">true</property>
		</poolConfig>
	</dbServer>

	<dbServer name="Master"  parent="abstractServer">
		<factoryConfig>
			<!-- mysql ip -->
			<property name="ipAddress">192.168.1.200</property>
		</factoryConfig>
	</dbServer>
	
	<dbServer name="Slave"  parent="abstractServer">
		<factoryConfig>
			<!-- mysql ip -->
			<property name="ipAddress">192.168.1.88</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">Master,Slave</property>
		</poolConfig>
	</dbServer>
		
</amoeba:dbServers>

amoeba.xml

View Code

  1 <?xml version="1.0" encoding="gbk"?>
  2 
  3 <!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
  4 <amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">
  5 
  6     <proxy>
  7     
  8         <!-- service class must implements com.meidusa.amoeba.service.Service -->
  9         <service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">
 10             <!-- port -->
 11             <property name="port">8066</property>
 12             
 13             <!-- bind ipAddress -->
 14              
 15             <property name="ipAddress">192.168.1.200</property>
 16             
 17             
 18             <property name="manager">${clientConnectioneManager}</property>
 19             
 20             <property name="connectionFactory">
 21                 <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
 22                     <property name="sendBufferSize">128</property>
 23                     <property name="receiveBufferSize">64</property>
 24                 </bean>
 25             </property>
 26             
 27             <property name="authenticator">
 28                 <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
 29                     
 30                     <property name="user">webadmin</property>
 31                     
 32                     <property name="password">webadmin</property>
 33                     
 34                     <property name="filter">
 35                         <bean class="com.meidusa.amoeba.server.IPAccessController">
 36                             <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
 37                         </bean>
 38                     </property>
 39                 </bean>
 40             </property>
 41             
 42         </service>
 43         
 44         <!-- server class must implements com.meidusa.amoeba.service.Service -->
 45         <service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer">
 46             <!-- port -->
 47             <!--  default value: random number
 48             <property name="port">9066</property>
 49             -->
 50             <!-- bind ipAddress -->
 51             <property name="ipAddress">127.0.0.1</property>
 52             <property name="daemon">true</property>
 53             <property name="manager">${clientConnectioneManager}</property>
 54             <property name="connectionFactory">
 55                 <bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>
 56             </property>
 57             
 58         </service>
 59         
 60         <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
 61             <!-- proxy server net IO Read thread size -->
 62             <property name="readThreadPoolSize">20</property>
 63             
 64             <!-- proxy server client process thread size -->
 65             <property name="clientSideThreadPoolSize">30</property>
 66             
 67             <!-- mysql server data packet process thread size -->
 68             <property name="serverSideThreadPoolSize">30</property>
 69             
 70             <!-- per connection cache prepared statement size  -->
 71             <property name="statementCacheSize">500</property>
 72             
 73             <!-- query timeout( default: 60 second , TimeUnit:second) -->
 74             <property name="queryTimeout">60</property>
 75         </runtime>
 76         
 77     </proxy>
 78     
 79     <!-- 
 80         Each ConnectionManager will start as thread
 81         manager responsible for the Connection IO read , Death Detection
 82     -->
 83     <connectionManagerList>
 84         <connectionManager name="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
 85             <property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>
 86             <!-- 
 87               default value is avaliable Processors 
 88             <property name="processors">5</property>
 89              -->
 90         </connectionManager>
 91         <connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
 92             <property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>
 93             
 94             <!-- 
 95               default value is avaliable Processors 
 96             <property name="processors">5</property>
 97              -->
 98         </connectionManager>
 99     </connectionManagerList>
100     
101         <!-- default using file loader -->
102     <dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">
103         <property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
104     </dbServerLoader>
105     
106     <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
107         <property name="ruleLoader">
108             <bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
109                 <property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
110                 <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
111             </bean>
112         </property>
113         <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
114         <property name="LRUMapSize">1500</property>
115         <property name="defaultPool">Master</property>
116         
117         
118         <property name="writePool">Master</property>
119         <property name="readPool">Slave</property>
120         
121         <property name="needParse">true</property>
122     </queryRouter>
123 </amoeba:configuration>

这个配置都很简单，如果实在不会看官方文档，因为这个代理是国人编写的，文档还是慢清晰的，我就是看文档学会的，不用去google或者百度

文档在这里，好像现在不能访问了，直接搜索amoeba文档吧就可以找到

posted on 2013-03-01 14:19 coolnet 阅读(283) 评论(0) 编辑收藏举报

刷新页面返回顶部

coolnet