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
1
# yum -y install ntp
--配置ntp，添加
1
2
3
# vim /etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8
--重启服务
1
# service ntpd restart
--从节点时间同步
1
2
3
# yum -y install ntpdate
# ntpdate 192.168.10.130
8Dec 14:16:14 ntpdate: step time server 192.168.10.130 offset 1.107709sec
-编译安装MySQL
1
# yum -y installncurses-devel
--安装cmake
1
2
3
# tar zxfcmake-2.8.6.tar.gz
# cd cmake-2.8.6
# ./configure&& gmake && gmake install
--安装MySQL
1
2
3
# tar zxfmysql-5.5.22.tar.gz
# cd 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
--优化调整
1
2
3
4
5
6
# cpsupport-files/my-medium.cnf /etc/my.cnf
# cpsupport-files/mysql.server /etc/init.d/mysqld
# chmod +x /etc/init.d/mysqld
# chkconfig --addmysqld
# echo"PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
# source/etc/profile
--初始化数据库
1
2
3
# useradd -M -s/sbin/nologin mysql
# chown -Rmysql:mysql /usr/local/mysql
#/usr/local/mysql/scripts/mysql_install_db --user=mysql--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
--启动服务
1
2
3
4
# service mysqld start
Starting MySQL.. [确定]
# chkconfig mysqld on
# mysqladmin -u rootpassword '123456'
-配置master主服务器--修改配置文件
1
2
3
4
# vim /etc/my.cnf
log-bin=master-bin #修改
log-slave-updates=true #修改
server-id = 11 #增加
--重启服务
1
2
3
# service mysqld restart
Shutting down MySQL. [确定]
Starting MySQL.. [确定]
--给从服务器授权
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 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)
-配置从服务器--修改配置文件
1
2
3
4
# vim /etc/my.cnf
server-id = 22 #修改
relay-log=relay-log-bin #增加
relay-log-index=slave-relay-bin.index #增加
--重启服务
1
# service mysqld restart
--登陆MySQL，配置同步
---根据主服务器的file和position,配置master_log_file和master_log_pos参数
1
2
3
# 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
-测试主从复制--在主服务器上新建数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 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)
--从服务器如果同步显示，则表示成功
1
2
3
4
5
6
7
8
9
10
11
12
# 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环境
1
2
3
# chmod +xjdk-6u14-linux-x64.bin
# ./jdk-6u14-linux-x64.bin
# mv jdk1.6.0_14//usr/local/jdk1.6
--添加环境变量
1
2
3
4
5
6
7
8
9
10
11
# 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
# source /etc/profile
# 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
1
2
3
4
5
# mkdir /usr/local/amoeba
# tar zxfamoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
# chmod -R 755 /usr/local/amoeba/
#/usr/local/amoeba/bin/amoeba
amoeba start|stop#显示此说明成功
-配置amoeba读写分离--节点服务器开放权限
1
2
3
4
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
# 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
# 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
1
2
3
4
#/usr/local/amoeba/bin/amoeba start &
# 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上测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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上建表
1
2
3
4
mysql> use db_test;
Database changed
mysql> create table test (idint(10),name char(20));
Query OK, 0 rows affected (0.01 sec)
--从服务器停止同步
1
2
3
4
5
6
7
8
9
10
11
12
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)
--主服务器上插入数据
1
2
mysql> insert into testvalues('1','master');
Query OK, 1 row affected (0.00 sec)
-在从服务器上插入数据
--slave1
1
2
mysql> insert into test values('2','slave1');
Query OK, 1 row affected (0.01 sec)
--slave2
1
2
mysql> insert into test values('3','slave2');
Query OK, 1 row affected (0.03 sec)
-测试读
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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
1
2
3
4
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
1
2
3
4
5
6
7
8
9
# mysql -uroot -p123456
mysql> select * from db_test.test;
+------+--------+
| id| name |
+------+--------+
| 1 | master |
| 4 | client |
+------+--------+
2 rows in set (0.00 sec)
--slave1
1
2
3
4
5
6
7
8
# mysql –uroot –p123456
mysql> select * from db_test.test;
+------+--------+
| id| name |
+------+--------+
| 2 | slave1 |
+------+--------+
1 row in set (0.00 sec)
--slave2
1
2
3
4
5
6
7
8
# mysql -uroot -p123456
mysql> select * from db_test.test;
+------+--------+
| id| name |
+------+--------+
| 3 | slave2 |
+------+--------+
1 row in set (0.00 sec)
页:
[1]