mysql主从复制和读写分离

主从同步

1.主服务器上配置

1.时间同步

[root@server1 ~]# ntpdate ntp.aliyun.com
[root@server1 ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate ntp.aliyun.com

[root@server1 ~]# vi /etc/ntp.conf
restrict 192.168.106.0 mask 255.255.255.0 nomodify notrap
server 127.127.1.0
fudge 127.127.1.0 stratum 8
[root@server1 ~]# systemctl restart mysqld.service 

2.修改mysql配置文件

[root@server1 ~]# vi /etc/my.cnf

[mysqld]
···
log_bin=master_bin
log_slave_updates=true

3.授权给从服务器，并记录下position列的值
mysql> grant replication slave on *.* to 'myslave'@'192.168.106.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)  ##授权

mysql> show grants for 'myslave'@'192.168.106.%';  ##查看授权
+-------------------------------------------------------------+
| Grants for myslave@192.168.106.%                            |
+-------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.106.%' |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> flush privileges; ##刷新权限
Query OK, 0 rows affected (0.00 sec)

mysql> show master status; ##记录下position列的值
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master_bin.000001 |      604 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.从服务器1

1.配置时间同步

[root@server1 ~]# crontab -e

*/30 * * * * /usr/sbin/ntpdate 192.168.106.50

2.更改配置

[root@server1 ~]# vi /etc/my.cnf
server-id = 11 ##id每台机器不相同
relay_log=relay_log_bin #中继
relay_log_index=slave-relay-bin.index

[root@server1 data]# mysql -uroot -p123456
mysql> change master to master_host='192.168.106.50',
master_user='myslave',master_password='123456',master_log_file='master_bin.000001',master_log_pos=604;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G

 Slave_IO_Running: Yes  ##2个yes为正常
 Slave_SQL_Running: Yes

3.从服务器2

1.配置时间同步

[root@server1 ~]# crontab -e

*/30 * * * * /usr/sbin/ntpdate 192.168.106.50

2.更改配置

[root@server1 ~]# vi /etc/my.cnf
server-id = 12 ##id每台机器不相同
relay_log=relay_log_bin #中继日志
relay_log_index=slave-relay-bin.index

[root@server1 data]# mysql -uroot -p123456
mysql> change master to master_host='192.168.106.50',
master_user='myslave',master_password='123456',master_log_file='master_bin.000001',master_log_pos=604;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G

 Slave_IO_Running: Yes  ##2个yes为正常
 Slave_SQL_Running: Yes

4.测试

主：
mysql> create database abc;
Query OK, 1 row affected (0.01 sec)
从1：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
从2：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

主从分离

1.新加一台amoeba实现读写分离

3台mysql服务器授权

grant all privileges on *.* to test@'192.168.106.%' identified by '123abc';
flush privileges;

2.amoeba服务器配置java环境

[root@server1 opt]# tar xf jdk-8u91-linux-x64.tar.gz
[root@server1 opt]# cp -rv jdk1.8.0_91/ /usr/local/java
[root@server1 opt]# vi /etc/profile  ##配值java命令到环境变量中

export JAVA_HOME=/usr/local/java
export JRE_HOME=/usr/local/java/jre
export PATH=$PATH:/usr/local/java/bin
export CLASSPATH=./:/usr/local/java/lib:/usr/local/java/jre/lib

[root@server1 opt]# source /etc/profile
[root@server1 opt]# java -version
openjdk version "1.8.0_131"
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed moe)

3.安装amoeba

[root@server1 opt]# unzip amoeba-mysql-3.0.5-RC-distribution.zip
[root@server1 opt]# mv amoeba-mysql-3.0.5-RC /usr/local/amoeba
[root@server1 opt]# chmod -R 755 /usr/local/amoeba
[root@server1 opt]# cd /usr/local/amoeba/
[root@server1 amoeba]# vi jvm.properties 

#JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m" ##32行添加
JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"

[root@server1 amoeba]# vi /etc/init.d/amoeba


#!/bin/bash 
#chkconfig: 35 62 62
#
export JAVA_HOME=/usr/local/java
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
NAME=Amoeba
AMOEBA_BIN=/usr/local/amoeba/bin/launcher
SHUTDOWN_BIN=/usr/local/amoeba/bin/shutdown
PIDFILE=/usr/local/amoeba/Amoeba-MySQL.pid
SCRIPTNAME=/etc/init.d/amoeba

case "$1" in
start)
echo -n "Starting $NAME..."
$AMOEBA_BIN
echo "done"
;;
stop)
echo -n "Stoping $NAME..."
$SHUTDOWN_BIN
echo "done"
;;
restart)
$SHUTDOWN_BIN
sleep 1
$AMOEBA_BIN
;;
*)
echo "Usage: $SCRIPTNAME {start|stop|restart}"
exit 1
;;
esac


[root@server1 amoeba]# chmod +x /etc/init.d/amoeba 
[root@server1 amoeba]# chkconfig --add amoeba
[root@server1 amoeba]# service amoeba start
[root@server1 ~]# netstat -anpt | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      4675/java

[root@server1 ~]# vi /usr/local/amoeba/conf/amoeba.xml

                                         <property name="user">amoeba</property>  ## 28行到30行修改添加授权账户和密码

                                        <property name="password">123456</property>
   
                                         <property name="defaultPool">master</property>  ##83行去掉注释 修改默认地址池，修改写在master 读在slaves
                                         <property name="writePool">master</property>
                                         <property name="readPool">slaves</property>


                        <property name="user">test</property>   ##添加3台mysql授权的账户和密码

                        <property name="password">123abc</property>
                           

                         <dbServer name="slaves" virtual="true">  ##修改地址池为slaves
                <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
                        <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
                        <property name="loadbalance">1</property>

                        <!-- Separated by commas,such as: server1,server2,server1 -->
                        <property name="poolNames">slave1,slave2</property>  ##对应为slave1 ，slave2
                </poolConfig>
        </dbServer>


[root@server1 ~]# service amoeba restart 

4 新建一台客户机安装登录

[root@server1 ~]# yum -y install mariadb*
[root@server1 ~]# mysql -uamoeba -p123456 -h192.168.106.130 -P 8066
MySQL [(none)]> 

5测试

1.主从状态下，在客户机上test库上新建一个表，所有服务器同步
MySQL [(none)]> use test;
Database changed
MySQL [test]> create table abc(id int(2) not null primary key, name varchar(48), score char(48));
Query OK, 0 rows affected (0.01 sec)

mysql> use test;                        ##每台服务器都能看到
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| abc            |
+----------------+
1 row in set (0.00 sec)

mysql> desc abc;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(2)      | NO   | PRI | NULL    |       |
| name  | varchar(48) | YES  |     | NULL    |       |
| score | char(48)    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.从服务器断开同步，客户端新建一个表，只有主服务器有表，从服务器没有
mysql> stop slave;   ##2台从服务器断开同步
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> insert into abc values(2,'lisi',98);

mysql> select * from abc;  ##主服务器
+----+------+-------+
| id | name | score |
+----+------+-------+
|  2 | lisi | 98    |
+----+------+-------+
1 row in set (0.00 sec)


mysql> select * from abc;   ##从服务器1，2为空
Empty set (0.00 sec)

3.每个服务器上新建一个表，客户端只能看到从服务器1，2的表，看不到主服务器的表

MySQL [test]> select * from abc;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  5 | lili | 90    |
+----+------+-------+
1 row in set (0.00 sec)

MySQL [test]> select * from abc;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  2 | waner | 96    |
+----+-------+-------+
1 row in set (0.00 sec)