主从的作业

1．理解 MySQL 主从复制原理。

(1) Master的更新事件(update、insert、delete)会按照顺序写入bin-log中。当Slave连接到Master的后,Master机器会为Slave开启
binlog dump线程,该线程会去读取bin-log日志
(2) Slave连接到Master后,Slave库有一个I/O线程 通过请求binlog dump thread读取bin-log日志,然后写入从库的relay log日志中。
(3) Slave还有一个 SQL线程,实时监控 relay-log日志内容是否有更新,解析文件中的SQL语句,在Slave数据库中去执行。

2．完成 MySQL 主从复制（一主两从）。

主库：
（1）设置server-id值并开启binlog参数
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log_bin = mysql-bin
server_id = 1
character-set-server=utf8
gtid-mode=ON
enforce-gtid-consistency=tru

（2） 建立同步账号
[root@localhost ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to rep@'192.168.227.%' identified by '123456';

（3）锁表，保持数据一致
mysql> flush tables with read lock;
（4）保证主从库中的数据库全部一致
[root@localhost ~]# mysqldump -uroot -p123456 --set-gtid-purged=OFF  -B company school school2 > /root/backup/db.sql
进入从库，俩个从库都需要
[root@localhost ~]# vim /etc/my.cnf
[mysqld]#从库1
log_bin=mysql-bin
server_id=2
character-set-server=utf8
gtid-mode=on
enforce-gtid-consistency=true
[root@localhost ~]# systemctl restart mysqld

[mysqld]#从库2
log_bin=mysql-bin
server_id=3
character-set-server=utf8
gtid-mode=on
enforce-gtid-consistency=true
[root@localhost ~]# systemctl restart mysqld
（5）进行还原
[root@localhost ~]# scp /root/backup/db.sql 192.168.227.141:/root/backup/
[root@localhost ~]# scp /root/backup/db.sql 192.168.227.142:/root/backup/
[root@localhost ~]# mysql -uroot -p123456 </root/backup/db.sql
[root@localhost ~]# mysql -uroot -p123456 </root/backup/db.sql
（6）解锁主库
mysql> unlock tables;
（7）查看主库file,position
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| mysql-bin.000002 |      194 |              |                  | 6a0e4dc6-c7c2-11ed-a27b-000c29c1774c:1-4,
ecbe7dde-cd54-11ed-ad0f-000c29c1774c:1 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(8)设定从主库一致
[root@localhost ~]# mysql -uroot -p123456#从库俩个都做
mysql> stop slave;
mysql> change master to
    -> master_host="192.168.227.140",
    -> master_user="rep",
    -> master_port=3306,
    -> master_password="123456",
    -> master_log_file="mysql-bin.000002",
    -> master_log_pos=194;
（9）开启主从复制
mysql> start slave;
（10）查看主从复制是否成功
mysql> show slave status \G #看到双yes代表成功

3．基于 MySQL 一主两从配置，完成 MySQL 读写分离配置

（1）进行环境准备
wget -c http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies.jar
wget -c http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip
yum localinstall -y jdk-8u261-linux-x64.rpm
yum install -y unzip
（2）创建/root/data
[root@localhost ~]# mkdir /root/data
（3）解压到data目录下
[root@localhost ~]# unzip mycat2-install-template-1.21.zip -d /root/data/
（4） 把所需的jar复制到mycat/lib目录
mv mycat2-1.21-release-jar-with-dependencies.jar /root/data/mycat/lib/
（5）修改权限
[root@localhost lib]# chmod +x /root/data/mycat/bin/* 
（6）启动一个3306的MySQL
netstat -anp |grep mysqld#查看端口，一般都是3306
（7）启动之前配置物理库地址。
# cat /data/mycat/conf/datasources/prototypeDs.datasource.json
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"prototypeDs",
"password":"123456",#这个是连接mycat2的密码
"type":"JDBC",
"url":"jdbc:mysql://localhost:3306?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",#中位置如果又mysqld进行删除
"user":"root",#这个是连接mycat2的用户
"weight":0
}
（8）启动mycat
cd /root/data/mycat/bin
./mycat start 启动
（9）进入Navicat Premium 16
输入IP地址、用户名，和端口号（注意端口号为8066）
（10）创建数据源
# 添加读写的数据源
/*+ mycat:createDataSource{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"m1",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://127.0.0.1:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
"user":"root",
"weight":0
} */;

# 添加读1的数据源
/*+ mycat:createDataSource{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"m1s1",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://127.0.0.1:3307useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
"user":"root",
"weight":0
} */;

# 添加读2的数据源
/*+ mycat:createDataSource{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"m1s2",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://127.0.0.1:3308useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
"user":"root",
"weight":0
} */;
（11）创建集群
/*! mycat:createCluster{
"clusterType":"MASTER_SLAVE",
"heartbeat":{
"heartbeatTimeout":1000,
"maxRetry":3,
"minSwitchTimeInterval":300,
"slaveThreshold":0
},
"masters":[
"m1"
],
"maxCon":2000,
"name":"prototype",
"readBalanceType":"BALANCE_ALL",
"replicas":[
"m1s1","m1s2"
],
"switchType":"SWITCH"
} */;
（12）创建逻辑库
CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
（13）修改逻辑库的数据源
修改conf/schemas/db1.schema.json
vim /root/data/mycat/conf/schemas/db1.schema.json
在里面添加 "targetName":"prototype",#修改成你的数据源的名字