首页 > 代码库 > MySQL主从复制与读写分离
MySQL主从复制与读写分离
一、准备工作
1、拓扑
2、环境简介
主机 | 系统 | IP | 主要软件 |
Client | Windows 10 | 192.168.10.1 | Mysql |
Amoeba | Centos6.5 64位 | 192.168.10.129 | amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin |
Master | Centos6.5 64位 | 192.168.10.130 | Ntp cmake-2.8.6.tar.gz mysql-5.5.22.tar.gz |
Slave1 | Centos6.5 64位 | 192.168.10.131 | Ntp cmake-2.8.6.tar.gz mysql-5.5.22.tar.gz |
Slave2 | Centos6.5 64位 | 192.168.10.134 | Ntp cmake-2.8.6.tar.gz mysql-5.5.22.tar.gz |
二、实施过程
1、搭建MySQL主从复制
-时间同步,主节点搭建时间同步服务器
--安装ntp
[root@master src]# yum -y install ntp
--配置ntp,添加
[root@master src]# vim /etc/ntp.conf server 127.127.1.0 fudge 127.127.1.0 stratum 8
--重启服务
[root@master src]# service ntpd restart
--从节点时间同步
[root@slave1 ~]# yum -y install ntpdate [root@slave1 ~]# ntpdate 192.168.10.130 8Dec 14:16:14 ntpdate[28137]: step time server 192.168.10.130 offset 1.107709sec
-编译安装MySQL
[root@master src]# yum -y installncurses-devel
--安装cmake
[root@master src]# tar zxfcmake-2.8.6.tar.gz [root@master src]# cd cmake-2.8.6 [root@master cmake-2.8.6]# ./configure&& gmake && gmake install
--安装MySQL
[root@master src]# tar zxfmysql-5.5.22.tar.gz [root@master src]# cd mysql-5.5.22 [root@master mysql-5.5.22]# cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all&& make && make install
--优化调整
[root@master mysql-5.5.22]# cpsupport-files/my-medium.cnf /etc/my.cnf [root@master mysql-5.5.22]# cpsupport-files/mysql.server /etc/init.d/mysqld [root@master mysql-5.5.22]# chmod +x /etc/init.d/mysqld [root@master mysql-5.5.22]# chkconfig --addmysqld [root@master mysql-5.5.22]# echo"PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile [root@master mysql-5.5.22]# source/etc/profile
--初始化数据库
[root@master mysql-5.5.22]# useradd -M -s/sbin/nologin mysql [root@master mysql-5.5.22]# chown -Rmysql:mysql /usr/local/mysql [root@master mysql-5.5.22]#/usr/local/mysql/scripts/mysql_install_db --user=mysql--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
--启动服务
[root@master ~]# service mysqld start Starting MySQL.. [确定] [root@master ~]# chkconfig mysqld on [root@master ~]# mysqladmin -u rootpassword ‘123456‘
-配置master主服务器
--修改配置文件
[root@master ~]# vim /etc/my.cnf log-bin=master-bin #修改 log-slave-updates=true #修改 server-id = 11 #增加
--重启服务
[root@master ~]# service mysqld restart Shutting down MySQL. [确定] Starting MySQL.. [确定]
--给从服务器授权
[root@master ~]# mysql -uroot -p123456 mysql> grant replication slave on *.* to‘slave‘@‘192.168.10.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB |Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000001 | 336 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
-配置从服务器
--修改配置文件
[root@slave1 ~]# vim /etc/my.cnf server-id = 22 #修改 relay-log=relay-log-bin #增加 relay-log-index=slave-relay-bin.index #增加
--重启服务
[root@slave1 data]# service mysqld restart
--登陆MySQL,配置同步
---根据主服务器的file和position,配置master_log_file和master_log_pos参数
[root@slave1 data]# mysql -uroot –p mysql> change master tomaster_host=‘192.168.10.130‘,master_user=‘slave‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=336; Query OK, 0 rows affected (0.07 sec)
--启动同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 192.168.10.130
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:master-bin.000001
Read_Master_Log_Pos: 336
Relay_Log_File:relay-log-bin.000002
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes #确保两个yes
-测试主从复制
--在主服务器上新建数据库
[root@master ~]# mysql -uroot -p123456 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.01 sec) mysql> create database db_test; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_test | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
--从服务器如果同步显示,则表示成功
[root@slave2 ~]# mysql -uroot -p123456 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_test | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.12 sec)
2、搭建MySQL读写分离
-在主机amoeba上安装Java环境
[root@amoeba src]# chmod +xjdk-6u14-linux-x64.bin [root@amoeba src]# ./jdk-6u14-linux-x64.bin [root@amoeba src]# mv jdk1.6.0_14//usr/local/jdk1.6
--添加环境变量
[root@amoeba src]# vim /etc/profile export JAVA_HOME=/usr/local/jdk1.6 exportCLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib exportPATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin export AMOEBA_HOME=/usr/local/amoeba/ export PATH=$PATH:$AMOEBA_HOME/bin [root@amoeba src]# source /etc/profile [root@amoeba src]# java -version java version "1.6.0_14" Java(TM) SE Runtime Environment (build1.6.0_14-b08) Java HotSpot(TM) 64-Bit Server VM (build14.0-b16, mixed mode)
-安装amoeba
[root@amoeba src]# mkdir /usr/local/amoeba [root@amoeba src]# tar zxfamoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ [root@amoeba src]# chmod -R 755 /usr/local/amoeba/ [root@amoeba src]#/usr/local/amoeba/bin/amoeba amoeba start|stop #显示此说明成功
-配置amoeba读写分离
--节点服务器开放权限
mysql> grant all on *.* totest@‘192.168.10.%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
--修改配置文件-amoeba.xml
[root@amoeba ~]# vim/usr/local/amoeba/conf/amoeba.xml
……
<propertyname="authenticator">
<beanclass="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
<property name="user">amoeba</property>
<propertyname="password">123456</property>
<property name="filter">
<beanclass="com.meidusa.amoeba.server.IPAccessController">
<propertyname="ipFile">${amoeba.home}/conf/access_list.conf</property>
</bean>
</property>
</bean>
</property>
……
<queryRouterclass="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<propertyname="ruleLoader">
<beanclass="com.meidusa.amoeba.route.TableRuleFileLoader">
<propertyname="ruleFile">${amoeba.home}/conf/rule.xml</property>
<propertyname="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
</bean>
</property>
<propertyname="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
<propertyname="LRUMapSize">1500</property>
<propertyname="defaultPool">master</property>
<property name="writePool">master</property>
<propertyname="readPool">slaves</property>
<propertyname="needParse">true</property>
</queryRouter>
--修改配置文件-dbServers.xml
[root@amoeba ~]# vim/usr/local/amoeba/conf/dbServers.xml
<!-- mysql user-->
<propertyname="user">test</property>
<propertyname="password">123456</property>
<dbServer name="master" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<propertyname="ipAddress">192.168.10.130</property>
</factoryConfig>
</dbServer>
<dbServer name="slave1" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<propertyname="ipAddress">192.168.10.131</property>
</factoryConfig>
</dbServer>
<dbServer name="slave2" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<propertyname="ipAddress">192.168.10.134</property>
</factoryConfig>
</dbServer>
<dbServer name="slaves"virtual="true">
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancingstrategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<!-- Separated by commas,suchas: server1,server2,server1 -->
<propertyname="poolNames">slave1,slave2</property>
</dbServer>
--启动amoeba,8066/tcp
[root@amoeba ~]#/usr/local/amoeba/bin/amoeba start & [root@amoeba ~]# netstat -antp | grep java tcp 0 0::ffff:127.0.0.1:8819 :::* LISTEN 55982/java tcp 0 0 :::8066 :::* LISTEN 55982/java
3、测试
-在client上测试
C:\Users\Administrator>mysql -u amoeba-p123456 -h192.168.10.129 -P8066 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_test | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.11 sec)
-在master上建表
mysql> use db_test; Database changed mysql> create table test (idint(10),name char(20)); Query OK, 0 rows affected (0.01 sec)
--从服务器停止同步
mysql> use db_test; Database changed mysql> show tables; +-------------------+ | Tables_in_db_test | +-------------------+ | test | +-------------------+ 1 row in set (0.02 sec) mysql> stop slave; Query OK, 0 rows affected (0.02 sec)
--主服务器上插入数据
mysql> insert into testvalues(‘1‘,‘master‘); Query OK, 1 row affected (0.00 sec)
-在从服务器上插入数据
--slave1
mysql> insert into test values(‘2‘,‘slave1‘); Query OK, 1 row affected (0.01 sec)
--slave2
mysql> insert into test values(‘3‘,‘slave2‘); Query OK, 1 row affected (0.03 sec)
-测试读
C:\Users\Administrator>mysql -u amoeba-p123456 -h192.168.10.129 -P8066 mysql> use db_test; mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 2 | slave1 | +------+--------+ 1 row in set (0.00 sec) mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 3 | slave2 | +------+--------+ 1 row in set (0.00 sec) mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 2 | slave1 | +------+--------+ 1 row in set (0.00 sec)
-测试写
--client
C:\Users\Administrator>mysql -u amoeba-p123456 -h192.168.10.129 -P8066 mysql> insert into test values(‘4‘,‘client‘); Query OK, 1 row affected (0.10 sec)
--master
[root@master ~]# mysql -uroot -p123456 mysql> select * from db_test.test; +------+--------+ | id | name | +------+--------+ | 1 | master | | 4 | client | +------+--------+ 2 rows in set (0.00 sec)
--slave1
[root@slave1 data]# mysql –uroot –p123456 mysql> select * from db_test.test; +------+--------+ | id | name | +------+--------+ | 2 | slave1 | +------+--------+ 1 row in set (0.00 sec)
--slave2
[root@slave2 ~]# mysql -uroot -p123456 mysql> select * from db_test.test; +------+--------+ | id | name | +------+--------+ | 3 | slave2 | +------+--------+ 1 row in set (0.00 sec)
本文出自 “JackeyGe” 博客,请务必保留此出处http://jackeyge.blog.51cto.com/12241288/1880834
MySQL主从复制与读写分离