MySQL 主从复制 读写分离 （amoeba,mycat数据库分片、表内分片,）

只有案例的版本
系统 IP地址 主机名 所需软件 硬件
Centos 7.4 1708 64bit 192.168.59.209 master. boost_1_59_0.tar.gz
mysql-5.7.12.tar.gz 内存：4G
Centos 7.4 1708 64bit 192.168.59.210 slave1. boost_1_59_0.tar.gz
mysql-5.7.12.tar.gz 内存：4G
Centos 7.4 1708 64bit 192.168.59.211 slave2.boost_1_59_0.tar.gz
mysql-5.7.12.tar.gz 内存：4G
内存看情况，虚拟机的话其实1G就ok，系统版本建议7.4之后
链接：https://pan.baidu.com/s/1rYmNYs-6v9cgEWd9-0Nikg
提取码：ekeh
有五个要用到的软件包， mysql5.7、cmake、boost、amoeba、mycat
复制这段内容后打开百度网盘手机App，操作更方便哦
安装在我前几篇的博客里有；这里就不再示范啦。

时间同步：
在master主机上安装ntp时间服务；
[[e-mail] ~]# yum -y install ntp
[[e-mail] ~]# sed -i ‘/^server/s//#/g’ /etc/ntp.conf
[[e-mail] ~]# cat <>/etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8
END
[[e-mail] ~]# systemctl start ntpd
[[e-mail] ~]# systemctl enable ntpd
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.

在两台slave主机中配置同步master的ntp时间（在此配置相同，在此只列举slave1的配置）；
[[e-mail] ~]# yum -y install ntpdate
[[e-mail] ~]# ntpdate 192.168.59.209
4 Aug 17:27:31 ntpdate[1740]: adjust time server 192.168.59.209 offset 0.000048 sec
[[e-mail] ~]# date
2018年 08月 04日 星期六 17:27:41 CST
[[e-mail] ~]# echo "/usr/sbin/ntpdate 192.168.59.209 " >>/etc/rc.local
[[e-mail] ~]# chmod +x /etc/rc.local

我只做了开启master的二进制文件，和slave的中继日志
下文的有一些优化的的 可写可不写
优化配置Master主机的mysql服务；
[[e-mail] ~]# vi /etc/my.cnf ##末尾添加
log-bin=master-bin
log-slave-updates
server-id=1
innodb_flush_log_at_trx_commit=2
master_info_repository=table
relay_log_info_repository=TABLE
:wq
注解：
log-bin=master-bin ##指定生成二进制文件，可加目录
log-slave-updates ##指定开启slave角色的更新
server-id=1 ##指定id号码
innodb_flush_log_at_trx_commit=2 ##见下方
master_info_repository=table
relay_log_info_repository=TABLE
[[e-mail] ~]# systemctl restart mysqld
[[e-mail] ~]# ls /usr/local/mysql/data/
master-bin.000001 master-bin.index …

优化配置slave1主机的mysql服务；
[[e-mail] ~]# vi /etc/my.cnf ##末尾添加
relay-log=relay1-log-bin
relay-log-index=slave-relay1-bin.index
server-id=2
innodb_flush_log_at_trx_commit=2
slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=16
master_info_repository=table
relay_log_info_repository=TABLE
:wq
注解：
relay-log=relay-log-bin ##中继日志文件名
relay-log-index=slave-relay-bin.index ##中继日志索引文件
server-id=2 ##id号码
innodb_flush_log_at_trx_commit=2
##指定数据库在存储数据时，事务的刷新方式，如下：
0：该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
1：最安全的，默认值，但是最慢是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。
2：比0安全，速度比较快，是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
slave-parallel-type=LOGICAL_CLOCK
##选择并行复制的类型
slave_parallel_workers=16
##worker线程数量
master_info_repository=table
##默认是file，master的更新或者slave的更新直接存放在文件内，若出现故障，会出现数据丢失；若改为table，则为表的方式进行存储更新，支持事务，安全性更高；
relay_log_info_repository=TABLE
##指定中继日志更新，提升性能
[[e-mail] ~]# systemctl restart mysqld
[[e-mail] ~]# ls /usr/local/mysql/data/
relay1-log-bin.000001 slave-relay1-bin.index …


配置master主机上的master角色，配置主节点；
mysql> grant replication slave on . to ‘linuxfan’@‘192.168.59.%’ identified by ‘123123’;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;

从节点上；
mysql> change master to master_host=‘192.168.59.209’,master_user=‘linuxfan’,master_password=‘123123’,master_log_file=‘master-bin.000001’,master_log_pos=154; ##指定时slave的状态必须处于stop状态
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;

change master to master_auto_position=0;
这条命令是我个人遇到问题时用来解决的命令
问题是我第一天做完这个实验是没做快照 导致再用时候发现不是主从同步的状态了，重新授权会报错用这条命令解决，

还可能会遇到主从同步时报UUID的错，如果你是用虚拟机做的实验，然后克隆几台MySQL快照的话，就很正常，
解决方法就是将 /usr/local/mysql/data/auto.cnf这个文件删除，重启mysql，会重新生成一份uuid，就可以了，
常见的实现主从复制的方式有两种
上面演示的就是二进制的方式还有一种就是GTID的方式
不从头演示了，
只需要在master 的/etc/my.cnf
添加
gtid-mode=ON
enforce-gtid-consistency=true
在slave 的/etc/my.cnf
log-bin=slave1-bin
log-bin-index=slave1-log-bin.index
gtid-mode=ON
enforce-gtid-consistency=true
log-slave-updates
重启mysqld
重新授权、查看master的二进制文件及position
读写分离 (amoeba,mycat)
amoeba在之前的博客里也演示过了，主要介绍mycat
再开一台Linux做mycat节点 192.168.59.201
安装mycat节点的mycat代理程序；
[[e-mail] ~]# tar zxvf jdk-8u171-linux-x64.tar.gz
[[e-mail] ~]# mv jdk1.8.0_171/ /usr/local/java
[[e-mail] ~]# vi /etc/profile
export JAVA_HOME=/usr/local/java
export PATH= P A T H : PATH: PATH:JAVA_HOME/bin
:wq
[[e-mail] ~]# source /etc/profile
[[e-mail] ~]# java -version
java version “1.8.0_171”
Java™ SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot™ 64-Bit Server VM (build 25.171-b11, mixed mode)
[[e-mail] ~]# tar zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[[e-mail] ~]# mv mycat/ /usr/local/
[[e-mail] ~]# ls /usr/local/mycat/
bin catlet conf lib logs version.txt

在master节点进行授权允许mycat程序能够连接Mysql集群，slave节点会同步权限；
[[e-mail] ~]# mysql -uroot -p123123
mysql> grant all on . to ‘linux’@‘192.168.59.201’ identified by ‘123123’;
mysql> flush privileges;
mysql> exit

[[e-mail] ~]# vi /usr/local/mycat/conf/server.xml
80 ##管理员账户
81 admin
82 VIR
95 ##普通用户，只读权限
96 user
97 VIR
98 true
99
:wq
[[e-mail] ~]# vi /usr/local/mycat/conf/log4j2.xml
25
:wq
[[e-mail] ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server…
[[e-mail] ~]# netstat -utpln |grep 8066
tcp 0 0 0.0.0.0:8066 0.0.0.0:* LISTEN 13090/java
[[e-mail] ~]# netstat -utpln |grep 9066
tcp 0 0 0.0.0.0:9066 0.0.0.0:* LISTEN 13090/java
[[e-mail] ~]# tail /usr/local/mycat/logs/wrapper.log ##服务日志
[[e-mail] ~]# tail /usr/local/mycat/logs/mycat.log ##读写分离日志

在Master节点创建测试数据库及表；
[[e-mail] ~]# mysql -uroot -p123123
mysql> create database cloud;
Query OK, 1 row affected (0.00 sec)
mysql> create table cloud.it (id int,name char(16));
Query OK, 0 rows affected (0.02 sec)
mysql> exit

lamp节点客户端测试数据读写分离；
[[e-mail] ~]# mysql -uadmin -padmin -h 192.168.59.201 -P 8066



lamp节点客户端查看数据读写请求分布；
[[e-mail] ~]# mysql -uadmin -padmin -h 192.168.59.201 -P 9066
mysql> show @@datasource;

案例：实现双Mysql实例+数据库分片（mycat方式）；
库分片案例环境
192.168.59.207 mysql centos7.4 ·1708
192.168.59.208 MySQL centos7.4 1708
192.168.59.201 mycat centos7.4 1708
搭建部署双Mysql实例环境并且授权访问（在此列举mysql1节点配置）；
[[e-mail] ~]# mysql -uroot -p123123
mysql> grant all on . to ‘mycat’@‘192.168.59.201’ identified by ‘123123’;
mysql> flush privileges;
mysql> exit

安装部署mycat节点程序；
[[e-mail] ~]# tar zxvf jdk-8u171-linux-x64.tar.gz
[[e-mail] ~]# mv jdk1.8.0_171/ /usr/local/java
[[e-mail] ~]# vi /etc/profile
export JAVA_HOME=/usr/local/java
export PATH= P A T H : PATH: PATH:JAVA_HOME/bin
:wq
[[e-mail] ~]# source /etc/profile
[[e-mail] ~]# java -version
java version “1.8.0_171”
Java™ SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot™ 64-Bit Server VM (build 25.171-b11, mixed mode)
[[e-mail] ~]# tar zxvf Mycat-server-1.6-RELEASE-20162088204710-linux.tar.gz
[[e-mail] ~]# mv mycat/ /usr/local/
[[e-mail] ~]# ls /usr/local/mycat/
bin catlet conf lib logs version.txt

配置mycat程序部署数据库分片；
[[e-mail] ~]# vi /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="VIR" checkSQLschema="false" sqlMaxLimit="59">
				<table name="t1" primaryKey="ID" type="global" dataNode="dn1,dn2" />
                <table name="t2" primaryKey="ID" type="global" dataNode="dn1,dn2" />
                <table name="t3" dataNode="dn1" />
                <table name="t4" dataNode="dn2" />
        </schema>

        <dataNode name="dn1" dataHost="mysql1" database="cloud" />
        <dataNode name="dn2" dataHost="mysql2" database="cloud" />

        <dataHost name="mysql1" maxCon="590" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="59">
                <heartbeat>select user()</heartbeat>
                <writeHost host="192.168.59.207_write" url="192.168.59.207:3306" user="mycat" password="123123">
                        <readHost host="192.168.59.207_read" url="192.168.59.207:3306" user="mycat" password="123123"/>
                </writeHost>
        </dataHost>

        <dataHost name="mysql2" maxCon="590" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="59">
                <heartbeat>select user()</heartbeat>
                <writeHost host="192.168.59.208_write" url="192.168.59.208:3306" user="mycat" password="123123">
                        <readHost host="192.168.59.208_read" url="192.168.59.208:3306" user="mycat" password="123123"/>
                </writeHost>
        </dataHost>
</mycat:schema>

[[e-mail] ~]# vi /usr/local/mycat/conf/server.xml

  80         <user name="admin">
     81                 <property name="password">admin</property>
     82                 <property name="schemas">VIR</property>
...
     95         <user name="user">
     96                 <property name="password">user</property>
     97                 <property name="schemas">VIR</property>
     98                 <property name="readOnly">true</property>
     99         </user>
:wq

[[e-mail] ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server…
[[e-mail] ~]# netstat -utpln |grep 066
tcp 0 0 0.0.0.0:8066 0.0.0.0:* LISTEN 1147/java
tcp 0 0 0.0.0.0:9066 0.0.0.0:* LISTEN 1147/java

两台数据库节点创建测试数据库（在此列举mysql1节点配置）；
[[e-mail] ~]# mysql -uroot -p123123
mysql> create database cloud;
Query OK, 1 row affected (0.00 sec)

客户端访问mycat创建表；
[[e-mail] ~]# mysql -uadmin -padmin -h192.168.59.201 -P 8066
mysql> show databases;
±---------+
| DATABASE |
±---------+
| VIR |
±---------+
mysql> use VIR;
mysql> show tables;
±--------------+
| Tables in VIR |
±--------------+
| t1 |
| t2 |
| t3 |
| t4 |
±--------------+
mysql> select * from t1;
ERROR 1105 (HY000): Table ‘cloud.t1’ doesn’t exist
mysql> select * from t2;
ERROR 1146 (42S02): Table ‘cloud.t2’ doesn’t exist
mysql> select * from t3;
ERROR 1146 (42S02): Table ‘cloud.t3’ doesn’t exist
mysql> select * from t4;
ERROR 1146 (42S02): Table ‘cloud.t4’ doesn’t exist

mysql> create table cloud.t1 (id int,name char(16));
Query OK, 0 rows affected (0.33 sec)
mysql> create table cloud.t2 (id int,name char(16));
Query OK, 0 rows affected (0.04 sec)
mysql> create table cloud.t3 (id int,name char(16));
Query OK, 0 rows affected (0.01 sec)
mysql> create table cloud.t4 (id int,name char(16));
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
±--------------+
| Tables in VIR |
±--------------+
| t1 |
| t2 |
| t3 |
| t4 |
±--------------+
分别进两台MySQL数据库中查看表


表内分片案例
和库分片的的环境一样，只修改对应配置文件就可以了，
建议直接将配置文件删除，完整复制
[[e-mail] ~]# vi /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="VIR" checkSQLschema="false" sqlMaxLimit="59">
		<table name="t1" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2" rule="sharding-by-intfile" />
        </schema>

        <dataNode name="dn1" dataHost="mysql1" database="linuxfan" />
        <dataNode name="dn2" dataHost="mysql2" database="linuxfan" />

        <dataHost name="mysql1" maxCon="590" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="59">
                <heartbeat>select user()</heartbeat>
                <writeHost host="192.168.59.207_write" url="192.168.59.207:3306" user="mycat" password="123123">
                        <readHost host="192.168.59.207_read" url="192.168.59.207:3306" user="mycat" password="123123"/>
                </writeHost>
        </dataHost>

        <dataHost name="mysql2" maxCon="590" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="59">
                <heartbeat>select user()</heartbeat>
                <writeHost host="192.168.59.208_write" url="192.168.59.208:3306" user="mycat" password="123123">
                        <readHost host="192.168.59.208_read" url="192.168.59.208:3306" user="mycat" password="123123"/>
                </writeHost>
        </dataHost>
</mycat:schema>
:wq

[[e-mail] ~]# vi /usr/local/mycat/conf/rule.xml

    26         <tableRule name="sharding-by-intfile">
     27                 <rule>
     28                         <columns>num</columns>
     29                         <algorithm>hash-int</algorithm>
     30                 </rule>
     97         <function name="hash-int"
     98                 class="io.mycat.route.function.PartitionByFileMap">
     99                 <property name="mapFile">partition-hash-int.txt</property>
	59					<property name="type">1</property> 
    207					<property name="defaultNode">0</property>
    208         </function>
:wq

[[e-mail] ~]# vi /usr/local/mycat/conf/partition-hash-int.txt

bj=0
sh=1
:wq

[[e-mail] ~]# /usr/local/mycat/bin/mycat start

Starting Mycat-server...

[[e-mail] ~]# netstat -utpln |grep 066
tcp        0      0 0.0.0.0:8066            0.0.0.0:*               LISTEN      1598/java           
tcp        0      0 0.0.0.0:9066            0.0.0.0:*               LISTEN      1598/java

两台数据库节点创建测试数据库（在此列举mysql1节点配置）； 没密码就直接mysql输入进入
[[e-mail] ~]# mysql -uroot -p123123
mysql> create database linuxfan;

客户端访问mycat创建表，并插入测试数据；
[[e-mail] ~]# mysql -uadmin -padmin -h 192.168.59.201 -P 8066

mysql> show databases;
+----------+
| DATABASE |
+----------+
| VIR      |
+----------+
mysql> use VIR
Database changed
mysql> show tables;
+---------------+
| Tables in VIR |
+---------------+
| t1            |
+---------------+

mysql> select * from t1;
ERROR 1146 (42S02): Table 'linuxfan.t1' doesn't exist

mysql> create table linuxfan.t1 (id int,name char(16),num char(8));
Query OK, 0 rows affected (0.33 sec)

mysql> insert into linuxfan.t1 (id,name,num)values(1,“zs”,“bj”);
Query OK, 1 row affected (0.20 sec)
mysql> insert into linuxfan.t1 (id,name,num)values(2,“ls”,“bj”);
Query OK, 1 row affected (0.02 sec)
mysql> insert into linuxfan.t1 (id,name,num)values(3,“ww”,“sh”);
Query OK, 1 row affected (0.01 sec)
mysql> insert into linuxfan.t1 (id,name,num)values(4,“lb”,“sh”);
Query OK, 1 row affected (0.01 sec)
mysql> insert into linuxfan.t1 (id,name,num)values(5,“gy”,“gz”);
Query OK, 1 row affected (0.01 sec)

验证：