MySQL主从复制与读写分离

2341233333

一、准备工作
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	[iyunv@master src]# yum -y install ntp

--配置ntp，添加

1 2 3

[iyunv@master src]# vim /etc/ntp.conf server 127.127.1.0 fudge 127.127.1.0 stratum 8

--重启服务

1	[iyunv@master src]# service ntpd restart

--从节点时间同步

1 2 3

[iyunv@slave1 ~]# yum -y install ntpdate [iyunv@slave1 ~]# ntpdate 192.168.10.130 8Dec 14:16:14 ntpdate[28137]: step time server 192.168.10.130 offset 1.107709sec

-编译安装MySQL

1	[iyunv@master src]# yum -y installncurses-devel

--安装cmake

1 2 3

[iyunv@master src]# tar zxfcmake-2.8.6.tar.gz [iyunv@master src]# cd cmake-2.8.6 [iyunv@master cmake-2.8.6]# ./configure&& gmake && gmake install

--安装MySQL

1 2 3

[iyunv@master src]# tar zxfmysql-5.5.22.tar.gz [iyunv@master src]# cd mysql-5.5.22 [iyunv@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

--优化调整

1 2 3 4 5 6

[iyunv@master mysql-5.5.22]# cpsupport-files/my-medium.cnf /etc/my.cnf [iyunv@master mysql-5.5.22]# cpsupport-files/mysql.server /etc/init.d/mysqld [iyunv@master mysql-5.5.22]# chmod +x /etc/init.d/mysqld [iyunv@master mysql-5.5.22]# chkconfig --addmysqld [iyunv@master mysql-5.5.22]# echo"PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile [iyunv@master mysql-5.5.22]# source/etc/profile

--初始化数据库

1 2 3

[iyunv@master mysql-5.5.22]# useradd -M -s/sbin/nologin mysql [iyunv@master mysql-5.5.22]# chown -Rmysql:mysql /usr/local/mysql [iyunv@master mysql-5.5.22]#/usr/local/mysql/scripts/mysql_install_db --user=mysql--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/

--启动服务

1 2 3 4

[iyunv@master ~]# service mysqld start Starting MySQL..                                           [确定] [iyunv@master ~]# chkconfig mysqld on [iyunv@master ~]# mysqladmin -u rootpassword '123456'

-配置master主服务器--修改配置文件

1 2 3 4

[iyunv@master ~]# vim /etc/my.cnf log-bin=master-bin     #修改 log-slave-updates=true #修改 server-id       = 11   #增加

--重启服务

1 2 3

[iyunv@master ~]# service mysqld restart Shutting down MySQL.                                       [确定] Starting MySQL..                                           [确定]

--给从服务器授权

1 2 3 4 5 6 7 8 9 10 11 12 13 14

[iyunv@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)

-配置从服务器--修改配置文件

1 2 3 4

[iyunv@slave1 ~]# vim /etc/my.cnf server-id       = 22   #修改 relay-log=relay-log-bin      #增加 relay-log-index=slave-relay-bin.index #增加

--重启服务

1	[iyunv@slave1 data]# service mysqld restart

--登陆MySQL，配置同步
---根据主服务器的file和position,配置master_log_file和master_log_pos参数

1 2 3

[iyunv@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
-测试主从复制--在主服务器上新建数据库

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

[iyunv@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)

--从服务器如果同步显示，则表示成功

1 2 3 4 5 6 7 8 9 10 11 12

[iyunv@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环境

1 2 3

[iyunv@amoeba src]# chmod +xjdk-6u14-linux-x64.bin [iyunv@amoeba src]# ./jdk-6u14-linux-x64.bin [iyunv@amoeba src]# mv jdk1.6.0_14//usr/local/jdk1.6

--添加环境变量

1 2 3 4 5 6 7 8 9 10 11

[iyunv@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 [iyunv@amoeba src]# source /etc/profile [iyunv@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

1 2 3 4 5

[iyunv@amoeba src]# mkdir /usr/local/amoeba [iyunv@amoeba src]# tar zxfamoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ [iyunv@amoeba src]# chmod -R 755 /usr/local/amoeba/ [iyunv@amoeba src]#/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
[iyunv@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
[iyunv@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

1 2 3 4

[iyunv@amoeba ~]#/usr/local/amoeba/bin/amoeba start & [iyunv@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上测试

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

[iyunv@master ~]# 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

[iyunv@slave1 data]# 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

[iyunv@slave2 ~]# mysql -uroot -p123456 mysql> select * from db_test.test; +------+--------+ | id  | name   | +------+--------+ |   3 | slave2 | +------+--------+ 1 row in set (0.00 sec)