MySQL主从复制与读写分离

1、mysql主从复制原理

Mysql的复制类型：
基于语句的复制
基于行的复制
混合类型的复制

2、mysql读写分离原理

只在主服务器上，只在从服务器上读
主服务器处理事务性查询，从服务器处理select查询
数据库复制用于将事务性查询的变更同步到集群中的从数据库

3、实验步骤

Mysql主服务器配置
[root@localhost ~]# iptables -F
[root@localhost ~]# setenforce 0
[root@localhost ~]# hostname master
[root@master ~]# vim /etc/my.cnf
server-id = 11
log-bin=master-bin            ##主服务器日志文件
log-slave-updates=true         ##从服务器更新二进制日志
[root@master ~]# systemctl restart mysqld.service 
[root@master ~]# mysql -uroot -pabc123
mysql> grant replication slave on *.* to 'myslave'@'192.168.169.%' identified by '123456';
mysql> flush privileges;
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      604 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从服务器slave01配置
[root@localhost ~]# iptables -F
[root@localhost ~]# setenforce 0
[root@localhost ~]# hostname slave01
[root@slave01 ~]# vim /etc/my.cnf
server-id = 22
relay-log=relay-log-bin     ##从主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index       ##定义relay-log的位置和名称
[root@slave01 ~]# systemctl restart mysqld.service 
[root@slave01 ~]# mysql -uroot -pabc123
mysql> change master to master_host='192.168.169.100',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;  ##连接master  参数：主服务器地址，用户名，密码，定义日志文件，位置
mysql> start slave;
mysql> show slave status\G;
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes

从服务器slave02配置
[root@localhost ~]# iptables -F
[root@localhost ~]# setenforce 0
[root@localhost ~]# hostname slave02
[root@slave02 ~]# vim /etc/my.cnf
server-id = 23
relay-log=relay-log-bin     
relay-log-index=slave-relay-bin.index
[root@slave02 ~]# systemctl restart mysqld.service 
[root@slave02 ~]# mysql -uroot -pabc123
mysql> change master to master_host='192.168.169.100',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;
mysql> start slave;
mysql> show slave status\G;
      Slave_IO_Running: Yes
      Slave_SQL_Running: Yes


现在主从就同步了  在主服务器上创建的东西在从服务器上也能显示

Amoeba（阿米巴）服务器配置
[root@localhost ~]# iptables -F
[root@localhost ~]# setenforce 0
[root@localhost ~]# hostname amoeba

[root@amoeba opt]# cp jdk-6u14-linux-x64.bin /usr/local/
[root@amoeba opt]# cd /usr/local/
[root@amoeba local]# chmod +x jdk-6u14-linux-x64.bin 
[root@amoeba local]# ./jdk-6u14-linux-x64.bin 
往下翻一直到跳出yes和no的时候选择yes然后回家就好

[root@amoeba local]# vim /etc/profile   ##进去添加环境变量
export JAVA_HOME=/usr/local/jdk1.6     前三行是jdk的环境变量
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     后两行是对amoeba的配置
export PATH=$PATH:$AMOEBA_HOME/bin
[root@amoeba local]# source /etc/profile     让他生效
[root@amoeba local]# mkdir /usr/local/amoeba
[root@amoeba local]# cd /opt/
[root@amoeba opt]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@amoeba opt]# cd /usr/local/amoeba/
[root@amoeba amoeba]# chmod -R 755 /usr/local/amoeba/
[root@amoeba amoeba]# /usr/local/amoeba/bin/amoeba   说明安装成功
amoeba start|stop
接下来在另外三台mysql上添加权限开放给amoeba访问
mysql> grant all on *.* to 'test'@'192.168.169.%' identified by '123.com';
mysql> flush privileges;   每个写完都要刷新一下
回到amoeba服务器

[root@amoeba conf]# vim amoeba.xml   先修改主配置文件

[root@amoeba conf]# vim dbServers.xml 

[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start&   启动amoeba

再开一台虚拟机作为客户端 连接amoeba
[root@localhost ~]# hostname client
[root@localhost ~]# su
[root@client ~]# iptables -F
[root@client ~]# setenforce 0
[root@client ~]# mysql -u amoeba -p123456 -h 192.168.169.88 -P8066