MySQL主从复制与读写分离
2016.3.16 by linuxfan.cn 环境介绍： 主服务器（master）：192.168.100.155 从服务器（slave1，slave2）：192.168.100.153-154 代理服务器（amoeba）：192.168.100.156 应用客户端（app）：192.168.100.1571.搭建时间服务器：
主节点上安装ntp时间服务：192.168.100.155 yum -y install ntp sed -i '/^server/s/^/#/g' /etc/ntp.conf cat <<END >>/etc/ntp.conf server 127.127.1.0 fudge 127.127.1.0 stratum 8 END /etc/init.d/ntpd restart netstat -utpln |grep ntp 从节点同步时间：192.168.100.153-154 yum -y install ntpdate /usr/sbin/ntpdate 192.168.100.1552.安装mysql：192.168.100.153-155
wget tar Jxvf lamp_install_publis-app-2015-07-16.tar.xz mysql_install.sh mysql_config.sh reboot提示： [root@localhost ~]# cd bin/ [root@localhost bin]# cat mysql_install.sh #!/bin/bash ##第一配置yum，安装ncurses依赖包 yum -y install ncurses-* #解压cmake，安装基础环境 tar zxvf /root/cmake-2.8.6.tar.gz -C /usr/src/ cd /usr/src/cmake-2.8.6 #配置，编译安装cmake ./configure &&gmake &&gmake install ##解压mysql tar zxvf /root/mysql-5.5.22.tar.gz -C /usr/src/ cd /usr/src/mysql-5.5.22/ #cmake进行配置mysql cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql #指定安装目录\ -DDEFAULT_CHARSET=utf8 #指定字符集为utf8 \ -DDEFAULT_COLLATION=utf8_general_ci ##指定字符校验 \ -DWITH_EXTRA_CHARSETS=all ##支持额外字符集\ -DSYSCONFDIR=/etc/ ##指定配置文件位置 make &&make install #编译安装 if [ -e /usr/local/mysql ];then echo "mysql install successfully." fi [root@localhost bin]# [root@localhost bin]# cat mysql_config.sh #!/bin/bash #1.复制配置文件 cp /usr/src/mysql-5.5.22/support-files/my-medium.cnf /etc/my.cnf #2.添加系统服务 cp /usr/src/mysql-5.5.22/support-files/mysql.server /etc/init.d/mysqld chmod +x /etc/init.d/mysqld chkconfig --add mysqld chkconfig mysqld on #3.优化PATH路径，执行命令时方便，单引号双引号都行 grep mysql /etc/profile if [ $? -eq 0 ];then echo "PATH is set." else echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile source /etc/profile ##执行文件 fi #4.初始化mysql，创建用户，赋权 useradd -M -s /sbin/nologin mysql chown -R mysql:mysql /usr/local/mysql /usr/local/mysql/scripts/mysql_install_db \ --basedir=/usr/local/mysql \ --datadir=/usr/local/mysql/data --user=mysql #5.启动mysql，并设置为开机启动 if [ -e /tmp/mysql.sock ];then /etc/init.d/mysqld restart else /etc/init.d/mysqld start fi chkconfig mysqld on #6.修改密码，并提示密码 mysqladmin -u root password '123123' &&echo "mysql root password is 123123"
3.配置MySQL主从复制：
1）主服务器配置：192.168.100.155 sed -i 's/^log-bin=.*/log-bin=master-bin\nlog-slave-updates=ture/g' /etc/my.cnf sed -i '/^server-id/s/1/11/g' /etc/my.cnf /etc/init.d/mysqld restart mysql -uroot -p123123 mysql> grant replication slave on *.* to 'myslave'@'192.168.100.%' identified by '123123'; mysql> flush privileges; mysql> show master status; ##记住File的及Position的值，此处为master-bin.000001和337 mysql> create database db_test; ##创建测试数据库 mysql> quit 2）配置从服务器1：192.168.100.153 sed -i '/^server-id/s/1/22/g' /etc/my.cnf sed -i '/^server-id/arelay-log=relay-log-bin\nrelay-log-index=slave-relay-bin.index' /etc/my.cnf /etc/init.d/mysqld restart mysql -uroot -p123123 mysql> change master to master_host='192.168.100.155',master_user='myslave',master_password='123123',master_log_file='master-bin.000001',master_log_pos=337; mysql> start slave; mysql> show slave status\G; ##查看无error即可 mysql> show databases; ##验证数据库是否同步 mysql> quit 3）配置从服务器2：192.168.100.154 sed -i '/^server-id/s/1/33/g' /etc/my.cnf sed -i '/^server-id/arelay-log=relay-log-bin\nrelay-log-index=slave-relay-bin.index' /etc/my.cnf /etc/init.d/mysqld restart mysql -uroot -p123123 mysql> change master to master_host='192.168.100.155',master_user='myslave',master_password='123123',master_log_file='master-bin.000001',master_log_pos=337; mysql> start slave; mysql> show slave status\G; ##查看无error即可 mysql> show databases; ##验证数据库是否同步 mysql> quit4.搭建MySQL读写分离：
1）安装软件：192.168.100.156 lftp ftp.linuxfan.cn >cd tools/ >get amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin >bye yum -y remove java chmod +x jdk-6u14-linux-x64.bin ./jdk-6u14-linux-x64.bin mv jdk1.6.0_14/ /usr/local/jdk1.6 vi /etc/profile export JAVA_HOME=/usr/local/jdk1.6 export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin export AMOEBA_HOME=/usr/local/amoeba export PATH=$PATH:$AMOEBA_HOME :wq source /etc/profile java -version mkdir /usr/local/amoeba tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ chmod -R 755 /usr/local/amoeba/ /usr/local/amoeba/bin/amoeba ##验证 2）数据授权给amoeba读写权限： mysql> grant all on *.* to linuxfan@'192.168.100.%' identified by '123123'; ##在192.168.100.155上完成 mysql> show grants for linuxfan@'192.168.100.%'; ##在192.168.100.153-154上查看是否同步了权限 3）修改配置文件：192.168.100.156 vim /usr/local/amoeba/conf/amoeba.xml 30 <property name="user">amoeba</property> 31 32 <property name="password">123456</property> 115 <property name="defaultPool">master</property> 116 117 <property name="writePool">master</property> ##注意删除<!-- -->的注释 118 <property name="readPool">slaves</property> :set nu ##显示行号 :wqvim /usr/local/amoeba/conf/dbServers.xml
25 <!-- mysql user --> 26 <property name="user">linuxfan</property> ##该用户必须是上一步授权的用户 27 28 <!-- mysql password --> ##修改 29 <property name="password">123123</property> ##删除下一行的“-->” 44 <dbServer name="master" parent="abstractServer"> ##修改为master 45 <factoryConfig> 46 <!-- mysql ip --> 47 <property name="ipAddress">192.168.100.155</property> ##指定正确的master的ip 51 <dbServer name="slave1" parent="abstractServer"> ##修改为slave1 52 <factoryConfig> 53 <!-- mysql ip --> 54 <property name="ipAddress">192.168.100.153</property> ##指定slave1的ip地址 55 </factoryConfig> 56 </dbServer> 57 <dbServer name="slave2" parent="abstractServer"> ##添加如下6行，指定slave2的ip 58 <factoryConfig> 59 <!-- mysql ip --> 60 <property name="ipAddress">192.168.100.154</property> 61 </factoryConfig> 62 </dbServer> 64 <dbServer name="slaves" virtual="true"> ##修改为slaves 70 <property name="poolNames">slave1,slave2</property> ##修改集群的成员名称用逗号隔开 :wq /usr/local/amoeba/bin/amoeba start& ##启动代理服务 netstat -utpln |grep 8066 ##验证5.测试读写分离 1)验证主从复制：192.168.100.157 yum -y install mysql mysql -uamoeba -p123456 -h 192.168.100.156 -P 8066 ##登录db集群192.168.100.157 mysql>show databases; mysql> use db_test; mysql> create table linuxfan(id int(10),name varchar(10),address varchar(20)); 在192.168.100.153-155上查看结果： mysql -uroot -p123123 mysql> use db_test; mysql> show tables; ##已然同步
2）关闭slave1，slave2的复制功能：192.168.100.153-154
mysql> stop slave;3)分别在master，slave1，slave2上创建不同的数据：
master: mysql> insert into linuxfan values(1,'hehe','this is master'); slave1: mysql> insert into linuxfan values(2,'hehe','this is slave1'); slave2: mysql> insert into linuxfan values(3,'hehe','this is slave2');4）应用客户端验证读：192.168.100.157
mysql> select * from linuxfan; ##第一次查询 +------+------+----------------+ | id | name | address | +------+------+----------------+ | 2 | hehe | this is slave1 | +------+------+----------------+ 1 row in set (0.02 sec)mysql> select * from linuxfan; ##第二次查询
+------+------+----------------+ | id | name | address | +------+------+----------------+ | 3 | hehe | this is slave2 | +------+------+----------------+ 1 row in set (0.01 sec) mysql> select * from linuxfan; ##第三次查询 +------+------+----------------+ | id | name | address | +------+------+----------------+ | 2 | hehe | this is slave1 | +------+------+----------------+ 1 row in set (0.00 sec)5）应用客户端上验证写：
mysql> insert into linuxfan values(4,'hehe','app write test'); ##写入数据 Query OK, 1 row affected (0.02 sec)mysql> select * from linuxfan; ##查不到刚写入的数据
+------+------+----------------+ | id | name | address | +------+------+----------------+ | 3 | hehe | this is slave2 | +------+------+----------------+ 1 row in set (0.01 sec)master上验证：
mysql> select * from linuxfan; ##查到数据 +------+------+----------------+ | id | name | address | +------+------+----------------+ | 1 | hehe | this is master | | 4 | hehe | app write test | +------+------+----------------+ 2 rows in set (0.00 sec)总结：
app写入数据时，amoeba会将数据路由到master上进行存储，app读取数据时，amoeba会将读的请求一轮询的方式发给slaves组（slave1+slave2），实现读写分离。 master和slaves间配置了主从复制，保证了数据的一致性。