MySQL 第八次练习（主从复制、读写分离）

MySQL 第八次练习（主从复制、读写分离） 1、理解MySQL主从复制原理。

主要基于 MySQL 二进制日志 主要包括三个线程（2个 I/O 线程， 1 个 SQL 线程） 1 、 MySQL 将数据变化记录到二进制日志中； 2 、 Slave 将 MySQL 的二进制日志拷贝到 Slave 的中继日志中； 3 、 Slave 将中继日志中的事件在做一次，将数据变化，反应到自身（ Slave ）的数据库

2、完成MySQL主从复制。 主库配置 1）、设置server-id 值并开启binlog日志

[root@localhost ~]# vi /etc/my.cnf
[mysqld]
log_bin = mysql-bin
server_id = 120

重启MySQL

[root@localhost ~]# systemctl restart mysql.server

 2）、创建用户并授权

mysql> create user rep@'192.168.159.%' identified by '123456';
mysql> grant replication slave on *.* to rep@'192.168.159.%';

3）、锁表设置只读

mysql> flush tables with read lock;

 4）、查看主库状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      617 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

 5）、备份数据库

[root@localhost ~]# mysqldump -uroot -p123456 -A -B | gzip > /tmp/db.sql.gz

 6）、解锁

mysql> unlock tables;

7）、将主库的备份传到从库

[root@localhost ~]# scp /tmp/db.sql.gz 192.168.159.134:/tmp
root@192.168.159.134's password: 
db.sql.gz 

 从库配置 1）、设置server-id值并关闭binlog参数

[root@localhost ~]# vim /etc/my.cnf

[mysqld]
server_id=130
#log_bin = /data/mysql/data/mysql-bin

 重启MySQL

[root@localhost ~]# systemctl restart mysqld

2）、还原数据库

[root@localhost ~]# zcat /tmp/db.sql.gz | mysql -uroot -p123456

 3）设定从主库同步

mysql> change master to
    -> master_host='192.168.159.136',
    -> master_user='rep',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001',  //此处为主库状态的第一项
    -> master_log_pos=617;                  //此处为主库状态的第二项

 4）、启动从库同步开关

mysql> start slave;

检查状态：

mysql> show slave statusG

3、完成MySQL读写分离配置 MyCAT(1.6.5)的安装及部署 (mycat下载链接：Index of /1.6.5/ (mycat.org.cn)) 1、部署jdk环境 1）、安装JDK（已经上传JDK）

[root@localhost ~]# mkdir /usr/java
[root@localhost ~]# tar xf jdk-8u311-linux-x64.tar.gz -C /usr/java/

2）、设置环境变量

[root@localhost ~]# vi /etc/profile.d/java.sh
[root@localhost ~]# cat /etc/profile.d/java.sh
export JAVA_HOME=/usr/java/jdk1.8.0_311/
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

使环境变量生效

[root@localhost ~]# source /etc/profile.d/java.sh

3）、测试

[root@localhost ~]# java -version
java version "1.8.0_311"
Java(TM) SE Runtime Environment (build 1.8.0_311-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.311-b11, mixed mode)

2、下载文件 1）、下载对应的tar安装包,以及对应的jar包

[root@localhost ~]# wget -c http://dl.mycat.org.cn/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

2）、解压

[root@localhost ~]# tar -xf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz -C /usr/local/

3）、添加环境变量

[root@localhost ~]# cd /usr/local/
[root@localhost local]# echo "export PATH=$PATH:/usr/local/mycat/bin" > /etc/profile.d/mycat.sh
[root@localhost local]# source /etc/profile.d/mycat.sh

4）、准备环境（一主一从都执行如下 *** 作初始化MySQL，rpm安装的MySQL可用）

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -rf /var/lib/mysql/* 
[root@localhost ~]# mysqld --initialize --datadir=/var/lib/  //初始化
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# grep -i password /var/log/mysqld.log   //查找初始化后的密码

5)、配置主从

主库

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              | sys,mysql        |                   |
+------------------+----------+--------------+------------------+-------------------+

#创建用户
mysql> grant replication slave on *.* to rep@'192.168.159.%' identified by '123456';

#指定不需要同步的数据库
[root@localhost ~]# vi /etc/my.cnf
binlog_ignore_db=sys
binlog_ignore_db=mysql

[root@localhost ~]# systemctl restart mysqld

从库

mysql> change master to 
    -> master_host='192.168.159.135', 
    -> master_user='rep', 
    -> master_password='123456' , 
    -> MASTER_AUTO_POSITION=0;

mysql> start slave;   

mysql> show slave status G   
 
#两个都为yes即可
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

6、配置读写分离 1）、mycat端修改配置文件

[root@localhost ~]# cd /usr/local/mycat/conf/

[root@localhost conf]# cp schema.xml{,.bak}


[root@localhost conf]# vim schema.xml





        
        
        
        
                select user()
                
                        
                
        

2)、主库创建db1数据库

mysql> create database db1;

3）、创建用户 主库上对 mycat 用户授权如下：

mysql> grant insert,delete,update,select on db1.* to mycat@'192.168.159.%' identified by '123456';

从库上mycat_r用户授权如下：

mysql> grant select on db1.* to mycat_r@'192.168.159.%' identified by '123456';

4）、tomcat端修改server.xml文件(使用默认即可)

[root@localhost conf]# vim server.xml 
  ① 这里配置的是可以连接主库的两个用户 用户： root  密码： 123456  给予此用户 TESTDB 数据库增删改查的权限。 用户： user 密码： user  给予此用户 TESTDB 数据库读的权限。

5）、启动mycat

方法一： # mycat console #<= 通过 console 命令启动 mycat ，这样方便提取信息 方法二： # mycat start 方法三： # startup_nowrap.sh # 服务脚本方式启动

[root@localhost ~]# mycat console
[root@localhost ~]# netstat -lnupt | egrep "(8|9)066"
tcp6       0      0 :::8066                 :::*                    LISTEN      1913/java           
tcp6       0      0 :::9066                 :::*                    LISTEN      1913/java  

 6）、在管理端连接mysql主库服务器

[root@localhost ~]# mysql -uroot -p123456 -P8066 -DTESTDB -h192.168.159.136

7）、主从同步读写分离测试 主库创建表 :

mysql> use db1
mysql> CREATE TABLE test1 (id int(10),name varchar(10),address varchar(20) DEFAULT NULL);

管理端插入数据：

mysql> insert into test1 values(1,'test1','master');
mysql> insert into test1 values(2,'test1','slave1');
mysql> insert into test1 values(3,'test1','slave2');

验证：

管理端登录9066端口

[root@localhost ~]# mysql -uroot -p123456 -P9066 -DTESTDB -h192.168.159.136

mysql> show @@database;   #显示mycat数据库的列表，对应的在scehma.xml配置的逻辑库
+----------+
| DATAbase |
+----------+
| TESTDB   |
+----------+

mysql> show @@datanode;  #）显示mycat数据节点的列表，对应的是scehma.xml配置文件的dataNode节点
+------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST        | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1  | localhost1/db1 |     0 | mysql |      0 |   10 | 1000 |     139 |          0 |        0 |       0 |            -1 |
+------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+

ysql> show @@heartbeat;  #查看心跳检测
+--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME   | TYPE  | HOST            | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | 192.168.159.135 | 3306 |       1 |     0 | idle   |       0 | 2,2,2        | 2022-01-30 23:11:32 | false |
| hostS2 | mysql | 192.168.159.134 | 3306 |       1 |     0 | idle   |       0 | 1,2,2        | 2022-01-30 23:11:32 | false |
+--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+

查看所有的命令 mysql> show @@help ; 获取当前 mycat 的版本 mysql> show @@version ; 显示 mycat 前端连接状态 mysql> show @@connection ; 显示 mycat 后端连接状态 mysql> show @@backend ; 显示数据源 mysql> show @@datasource