Centos7——Mysql读写分离操作步骤

提高可用性,当一台数据库服务器宕机后能通过调整另外一台从库来以最快的速度恢复服务
准备环境：192.168.122.102 master
192.168.122.103 slave
192.168.122.104 mycat

先对102与103做repliction操作
一、master：在122.102上
1、编辑/etc/my.cnf，在mysqld下添加以下信息
server-id=1
log-bin=/var/lib/mysql/mysql.log
2、保存退出后创建binlog日志文件路径
[root@kvm122102 ~]# mkdir /var/lib/mysql
[root@kvm122102 ~]# chown -R mysql:mysql /var/lib/mysql
3、重启数据库
[root@kvm122102 ~]# service mysql start
4、登陆数据库授权
[root@kvm122102 ~]# mysql -u root -p
mysql> grant replication slave on . to ‘repl’@‘192.168.122.103’ identified by ‘lifulai_000’;
mysql> flush privileges;
5、查看主状态
mysql> show master status \G;

二、slave，做从配置
1、编辑/etc/my.cnf文件，在mysqld下添加以下信息[mysqld]
server-id=2
2、重新启动数据库
[root@kvm122103 ~]# service mysql restart
3、在slave上配置slave replication
[root@kvm122103 ~]# mysql -u root -p
change master to
master_host=‘192.168.122.102’,
master_user=‘repl’,
master_password=‘lifulai_000’,
master_log_file=‘mysql.000001’,
master_log_pos=603;

mysql> start slave;
mysql> show slave status \G;

三、检测主从是否可用
先在master里面进行创建库以及表并插入数据，在slave里面进行查看，若能够看到master的数据，则证明replication操作是成功的

四、mycat进行安装配置
1、将文件复制到192.168.122.104环境上
[root@localhost ~]# scp -r Desktop/uplooking/Software/Mysql/ 192.168.122.104:/root

2、安装JDK
[root@kvm122104 ~]# mount /dev/cdrom /mnt/
[root@kvm122104 ~]# yum -y localinstall jdk-8u66-linux-x64.rpm

3、检查jdk版本
[root@kvm122104 ~]# java -version

4、解压mycat
[root@kvm122104 Mysql]# tar -xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

5、移动到/usr/local目录下
[root@kvm122104 Mysql]# mv mycat/ /usr/local/

6、授权mycat
在master上
mysql> grant all privileges on . to ‘mycat’@‘192.168.122.104’ identified by ‘lifulai_000’;
mysql> flush privileges;
mysql> grant all on . to ‘mycat’@‘192.168.122.104’ identified by ‘lifulai_000’;
mysql> flush privileges;

7、进入schemax.html文件
[root@kvm122104 Mysql]# vim /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>

<mycat:schema xmlns:mycat=“http://io.mycat/”>

    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    </schema>
    <dataNode name="dn1" dataHost="localhost1" database="db1" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                      writeType="0" dbType="mysql" dbDriver="native" switchType="0"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="hostM1" url="192.168.122.102:3306" user="mycat"
                               password="lifulai_000">
                    <!-- can have multi read hosts -->
                    <readHost host="hostS2" url="192.168.122.103:3306" user="mycat" password="lifulai_000" />
            </writeHost>
            </dataHost>

</mycat:schema>

开启日志，把info改成debug[root@kvm122104 mycat]# vim /usr/local/mycat/conf/log4j2.xml

8、调试测试
[root@kvm122104 local]# cd /usr/local/mycat/
[root@kvm122104 mycat]# ./bin/mycat console

9、启动mycat
[root@kvm122104 mycat]# ./bin/mycat start

10、查看端口号
#安装netstat命令功能
[root@kvm122104 mycat]# yum -y install net-tools

#能够看到8066就证明启动成功
[root@kvm122104 mycat]# netstat -tunpl | grep java
tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 2824/java
tcp6 0 0 :::35318 ::? LISTEN 2824/java
tcp6 0 0 :::44894 ::? LISTEN 2824/java
tcp6 0 0 :::1984 ::? LISTEN 2824/java
tcp6 0 0 :::8066 ::? LISTEN 2824/java
tcp6 0 0 :::9066 ::? LISTEN 2824/java
[root@kvm122104 mycat]#

11、在mycat里面登陆虚拟数据库
[root@kvm122104 mycat]# mysql -u root -p123456 -h 192.168.122.104 -P8066

12、查看database、table

mysql> show databases;
±---------+
| DATABASE |
±---------+
| TESTDB |
±---------+
1 row in set (0.01 sec)

mysql>
mysql> show tables;
±--------------+
| Tables_in_db1 |
±--------------+
| db1 |
±--------------+
1 row in set (0.02 sec)

mysql>

查看日志，写落在master，读落在slave上
[root@kvm122104 ~]# tail -f /usr/local/mycat/logs/mycat.log