centos7+mysql5.7主从配置、mycat读写分离

最新推荐文章于 2023-08-06 03:20:03 发布

yarbrough1996

最新推荐文章于 2023-08-06 03:20:03 发布

阅读量359

点赞数

分类专栏： mysql 读写分离文章标签： mysql mycat

本文链接：https://blog.csdn.net/qq_36696616/article/details/102956128

版权

mysql 同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

读写分离

2 篇文章 0 订阅

订阅专栏

本篇文章记录我们主从配置及读写分离配置步骤

主服务器： 114.***.182.128

从服务器： 114..182.62 119..218.237

前提：三台服务器mysql5.7已经安装完成

主从配置步骤

1. 主服务器配置

新增主从配置 vi /etc/my.cnf

[mysqld]

#replication

server-id=1
log-bin=mysql-bin.log
read-only = 0 #读写都可以
binlog-do-db = hm_advertise #只复制某个库
binlog-do-db = hm_common
binlog-do-db = hm_num
binlog-do-db = hm_order
binlog-do-db = hm_user
#innodb_flush_log_at_trx_commit=1
#sync_binlog=1

配置完成后，重启主库的mysql
service mysqld restart
再登录mysql
mysql -uroot -ptoot
(ERROR 1045 (28000): Access denied for user ‘tkuser’@’%’ (using password: YES)) 如果有类似错误请切换到root用户
给从库mysql用户赋予slave权限（前提：从库有以下用户且有远程权限）
mysql> grant replication slave on *.* to 'mysql237'@'119.3.***.237' identified by 'mysql237'; Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant replication slave on *.* to 'mysql62'@'114.116.***.62' identified by 'mysql62'; Query OK, 0 rows affected, 1 warning (0.01 sec)
查看主库状态

mysql> show master status;
±-----------------±---------±-----------------------------------------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-----------------------------------------------±-----------------±------------------+
| mysql-bin.000026 | 526283 | hm_advertise,hm_common,hm_num,hm_order,hm_user | | |
±-----------------±---------±-----------------------------------------------±-----------------±------------------+
1 row in set (0.00 sec)

2.从服务器配置

vi /etc/my.cnf 把以下内容粘到mysqld下。 server_id我的两个从数据库一个是2，一个是3，其余都一样。

#replication
server-id = 2
log_bin = mysql-bin.log
replicate-do-db = hm_advertise #只复制某个库
replicate-do-db = hm_common
replicate-do-db = hm_num
replicate-do-db = hm_order
replicate-do-db = hm_user
#replicate-ignore-db=mysql #不复制某个库

分别重启mysql

[root@ecs-s6-xlarge-2-win-20191105114342 lib]# service mysql restart
Shutting down MySQL… [ OK ]
Starting MySQL. [ OK ]

登录mysql ,从库设置master

[root@ecs-s6-xlarge-2-win-20191105114342 lib]# mysql -umysql237 -pmysql237

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> change master to master_host='114.116.***.128', master_user='mysql237' ,master_password='mysql237'' Query OK, 0 rows affected, 2 warnings (0.03 sec)

[root@ecs-s6-xlarge-2-win-20191105116655 lib]# mysql -umysql62 -pmysql62

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> change master to master_host='114.116.***.62', master_user='mysql62' ,master_password='mysql62'' Query OK, 0 rows affected, 2 warnings (0.03 sec)

主库dump数据文件
[root@ecs-4c8g /]# mysqldump --socket=/tmp/mysql.sock -h localhost -utkuser -pTk123qwe! --opt -R hm_common > /shareZone/db_bak/hm_common_bak-20191107.sql

找到文件后，发到从库所在服务器，从库分别新建要从主库同步的数据库，然后source刚才的dump文件。
新建数据库我通过navicat已经建好，
mysql> use hm_advertise; Database changed mysql> source /home/db_bak/hm_advertise_bak-20191106.sql;
然后发现数据已经从主库全部过来了。

找到主库现在的位置

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000026
         Position: 1716967
     Binlog_Do_DB: hm_advertise,hm_common,hm_num,hm_order,hm_user
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

从库执行
mysql> change master to master_host = '114.116.***.128',master_user='tkuser',master_password='Tk123qwe!',master_port=3306,master_log_file='mysql-bin.000026',master_log_pos=1716967;\g Query OK, 0 rows affected, 2 warnings (0.01 sec)

然后看一下从库状态：

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 114.116.182.128
                  Master_User: tkuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 1716967
               Relay_Log_File: ecs-s6-xlarge-2-win-20191105114342-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000026
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: hm_advertise,hm_common,hm_num,hm_order,hm_user
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1716967
              Relay_Log_Space: 556
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 6ad16843-245d-11e9-a4a5-fa163efa5c52
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

– ---------------------------------------------至此，主从同步完成----------------------------------------

mycat读写分离配置

下载安装

Mycat下载地址
http://dl.mycat.io/
我下载的是Mycat-server-1.6-RELEASE-20161028204710-linux.tar

安装

解压到/usr/local目录下，即mycat目录

配置mycat Home路径
export MYCAT_HOME=/usr/local/mycat
wq保存后，立即生效: source /etc/profile

配置

配置server.xml
[root@ecs-4c8g mycat]# pwd /usr/local/mycat
[root@ecs-4c8g mycat]# vi conf/server.xml

 <user name="mycat">
          <property name="password">mycat</property>
          <property name="schemas">advertise,common,num,order,user</property>
     </user>

说明：name mycat连接账号相当于mysql的账号；schemas 中的advertise，common等是我要进行读写分离的罗辑库

配置schema.xml
[root@ecs-4c8g mycat]# vim conf/schema.xml

<?xml version="1.0"?>

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

    <!--
            <dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
            <heartbeat>             </heartbeat>
             <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng"  password="jifeng"></writeHost>
             </dataHost>

      <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0"   dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>
            <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
            <writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base"       password="123456" > </writeHost> </dataHost>

            <dataHost name="jdbchost" maxCon="1000"         minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">
            <heartbeat>select       user()</heartbeat>
            <writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>

            <dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">
            <heartbeat> </heartbeat>
             <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng"         password="jifeng"></writeHost> </dataHost> -->

    <!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"
            dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"
            url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>
            </dataHost> -->


     <schema name="advertise" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn_advertise" ></schema>
     <schema name="common" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn_common" ></schema>
     <schema name="order" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn_order" ></schema>
     <schema name="num" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn_num" ></schema>
     <schema name="user" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn_user" ></schema>


    <dataNode name="dn_advertise" dataHost="myhost" database="hm_advertise" />
    <dataNode name="dn_common" dataHost="myhost" database="hm_common" />
    <dataNode name="dn_order" dataHost="myhost" database="hm_order" />
    <dataNode name="dn_num" dataHost="myhost" database="hm_num" />
    <dataNode name="dn_user" dataHost="myhost" database="hm_user" />

    <dataHost name="myhost" maxCon="1000" minCon="10" balance="1"
                      writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="hostM1" url="114.116.182.***:3306" user="tkuser"
                               password="Tk123qwe!">
                    <!-- can have multi read hosts -->
                  <!--   <readHost host="hostS2" url="114.116.***.62:3306" user="mysql62" password="mysql62" /> -->
            </writeHost>

           <writeHost host="hostM2"  url="114.116.***.62:3306" user="mysql62" password="mysql62">

            </writeHost>

             <writeHost host="hostM3"  url="119.***.218.237:3306" user="mysql237" password="mysql237">

            </writeHost>



           <!-- <writeHost host="hostS1" url="localhost:3316" user="root"
                               password="123456" /> -->
            <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
    </dataHost>
    </mycat:schema>

配置的各个标签属性，可以参考 https://blog.csdn.net/qq_34457768/article/details/81625758

配置完成后，重启mycat

[root@ecs-4c8g bin]# pwd
/usr/local/mycat/bin
[root@ecs-4c8g bin]# ./mycat restart

mycat内部端口号是 9066，可以登录查看读写分离情况
[root@ecs-4c8g ~]# mysql -umycat -pmycat -P9066 -h127.0.0.1

mysql> show @@datasource;
+--------------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE     | NAME   | TYPE  | HOST            | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+--------------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| dn_advertise | hostM1 | mysql | 114.116.182.128 | 3306 | W    |      0 |    1 | 1000 |      50 |         0 |          0 |
| dn_advertise | hostM2 | mysql | 114.116.182.62  | 3306 | W    |      0 |   11 | 1000 |     125 |        22 |         42 |
| dn_advertise | hostM3 | mysql | 119.3.218.237   | 3306 | W    |      0 |    1 | 1000 |      50 |         0 |          0 |
| dn_user      | hostM1 | mysql | 114.116.182.128 | 3306 | W    |      0 |    1 | 1000 |      50 |         0 |          0 |
| dn_user      | hostM2 | mysql | 114.116.182.62  | 3306 | W    |      0 |   11 | 1000 |     125 |        22 |         42 |
| dn_user      | hostM3 | mysql | 119.3.218.237   | 3306 | W    |      0 |    1 | 1000 |      50 |         0 |          0 |
| dn_order     | hostM1 | mysql | 114.116.182.128 | 3306 | W    |      0 |    1 | 1000 |      50 |         0 |          0 |
| dn_order     | hostM2 | mysql | 114.116.182.62  | 3306 | W    |      0 |   11 | 1000 |     125 |        22 |         42 |
| dn_order     | hostM3 | mysql | 119.3.218.237   | 3306 | W    |      0 |    1 | 1000 |      50 |         0 |          0 |
| dn_common    | hostM1 | mysql | 114.116.182.128 | 3306 | W    |      0 |    1 | 1000 |      50 |         0 |          0 |
| dn_common    | hostM2 | mysql | 114.116.182.62  | 3306 | W    |      0 |   11 | 1000 |     125 |        22 |         42 |
| dn_common    | hostM3 | mysql | 119.3.218.237   | 3306 | W    |      0 |    1 | 1000 |      50 |         0 |          0 |
| dn_num       | hostM1 | mysql | 114.116.182.128 | 3306 | W    |      0 |    1 | 1000 |      50 |         0 |          0 |
| dn_num       | hostM2 | mysql | 114.116.182.62  | 3306 | W    |      0 |   11 | 1000 |     125 |        22 |         42 |
| dn_num       | hostM3 | mysql | 119.3.218.237   | 3306 | W    |      0 |    1 | 1000 |      50 |         0 |          0 |
+--------------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
15 rows in set (0.00 sec)

备注：

我的switchType设置的是2 ，
如果Master1宕机，读写操作全部切换到Master2，此时读操作也不会发往Slave
修复Master1并启动，Master1从Master2同步数据，Master3从Master1同步数据，读数据发往Master1及Master3。
这种情况能够完美保障读数据的准确性，但在Master1修复之前，Master2压力会比较大，因为Master3全不可读。
Master1启动后，Master1和Master3需要重新执行
change master to master_host = ‘114.116.***.128’,master_user=‘tkuser’,master_password=‘Tk123qwe!’,master_port=3306,master_log_file=‘mysql-bin.000023’,master_log_pos=523859;\g
（这里的master是master1），然后又开始正常的读写分离和主从同步了。

yarbrough1996

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
centos7+mysql5.7主从配置、mycat读写分离

本篇文章记录我们主从配置及读写分离配置步骤主服务器： 114.116.182.128从服务器： 114.116.182.62 119.3.218.237前提：三台服务器mysql5.7已经安装完成主从配置步骤1. 主服务器配置新增主从配置 vi /etc/my.cnf[mysqld]#replicationserver-id=1log-bin=mysql-bin....
复制链接

扫一扫