Mycat & Mysql # Mycat配置文件 & Mysql主从复制

Mycat

一：部署mycat
1.安装jdk环境，在/etc/profile下面写java的环境变量

tar xf jdk-9.0.1_linux-x64_bin.tar.gz  -C /usr/local/
mv /usr/local/jdk-9.0.1/ /usr/local/java
vim /etc/profile
    JAVA_HOME=/usr/local/java
    PATH=$JAVA_HOME/bin:$PATH
    export JAVA_HOME PATH
# java -version
java version "9.0.1"
Java(TM) SE Runtime Environment (build 9.0.1+11)
Java HotSpot(TM) 64-Bit Server VM (build 9.0.1+11, mixed mode)
#安装mycat
tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/

vim ~/.bash_profile
    
    PATH=$PATH:$HOME/bin:/usr/local/mycat/bin

source ~/.bash_profile

配置文件：

vim server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <user name="yang">
                <property name="password">666</property>
                <property name="schemas">yang</property>
        </user>
</mycat:server>

vim schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="yang" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema>
        <dataNode name="dn1" dataHost="localhost" database="user" />
        <dataHost name="localhost" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>show status like 'wsrep%'</heartbeat>
                <writeHost host="10.11.67.57" url="10.11.67.57:3306" user="yang" password="666">
                        <readHost host="10.11.67.58" url="10.11.67.58:3306" user="yang" password="666" />
                        <readHost host="10.11.67.59" url="10.11.67.59:3306" user="yang" password="666" />
                </writeHost>
        </dataHost>
</mycat:schema>

然后启动(先配置数据库再启动)
测试端口：

mycat start 
jps

[root@clone ~]# jps
3393 WrapperSimpleApp
4150 Jps
[root@clone ~]# jps
3393 WrapperSimpleApp
4162 Jps

mycat stop
#进入mycat
mysql -u shop -p'123456' -h 192.168.245.3 -P8066

===================================================

Mysql

授权账户

创建授权账户：
    grant all on *.* to 'slave'@'%'  identified by '123';

单主单从&多从

主配置文件

vim /etc/my.cnf
log-bin = my1log
server-id=1
gtid_mode = ON
enforce_gtid_consistency=1

从配置文件

log-bin = my2log
server-id=2
gtid_mode = ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay_log_recovery = on

#这两个参数会将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议用

重启服务:

systemctl restart mysqld

单从edit

change master to
master_host='主ip',
MASTER_PORT=3306,
master_user='授权用户',
master_password='授权密码',
master_auto_position=1;

多从 deit

CHANGE MASTER TO
MASTER_HOST='10.11.65.18',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='zxc',
MASTER_AUTO_POSITION=1 FOR CHANNEL '10.11.65.18'

启动slave，查看slave，删除slave

#################################################
mysql > start slave;    #启动slave

mysql > show slave status\G；

mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
Empty set (0.00 sec)

==================================================
多主多从
主1配置文件

vim /etc/my.cnf
log-bin = my1log
server-id = 1
gtid_mode=ON
enforce_gtid_consistency=1

主2配置文件

vim /etc/my.cnf
log-bin = my2log
server-id = 2
gtid_mode=ON
enforce_gtid_consistency=1

重启服务:

systemctl restart mysqld

主1 edit

change master to
master_host='主ip2',
MASTER_PORT=3306,
master_user='授权用户',
master_password='授权密码',
master_auto_position=1;

主2 edit

change master to
master_host='主ip1',
MASTER_PORT=3306,
master_user='授权用户',
master_password='授权密码',
master_auto_position=1;

从1 配置文件

log-bin = my3log
server-id = 3
gtid_mode=ON
enforce_gtid_consistency=1
relay_log_info_repository = TABLE
master_info_repository    = TABLE
relay_log_recovery        = on

从2 配置文件

log-bin = my4log
server-id = 4
gtid_mode=ON
enforce_gtid_consistency=1
relay_log_info_repository = TABLE
master_info_repository    = TABLE
relay_log_recovery        = on

重启服务:

systemctl restart mysqld

从1 edit

CHANGE MASTER TO
MASTER_HOST='主1-ip',
MASTER_PORT=3306,
MASTER_USER='授权账户',
MASTER_PASSWORD='授权密码',
MASTER_AUTO_POSITION=1 FOR CHANNEL '主1-ip'

从2 edit

```bash
CHANGE MASTER TO
MASTER_HOST='主2-ip',
MASTER_PORT=3306,
MASTER_USER='授权账户',
MASTER_PASSWORD='授权密码',
MASTER_AUTO_POSITION=1 FOR CHANNEL '主2-ip'

================================================
查看mysql端口：

show global variables like 'port';  

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

查询主

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql1-bin.000002
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

查询id

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

修改mysql默认端口
my.cnf添加

[mysqld]  
port=3307

================================================

报错-解决：

问题1200

mysql> edit;
mysql> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

解决
授权问题-主库重新授权
问题2003

                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'kk@10.11.65.218:3306' - retry-time: 60  retries: 1

主库未开启-主库启动
&
从库slave输入错误：
show slave status \G
检查ip，授权账户，是否输错
stop slave;
reset slave all；
重新edit

===================================================

*注：docker中使用mysql没有vim命令
需要安装下列即可-（还可以安装yum 但没什么卵用=。=）

apt-get update
apt-get install -y vim

远程登录数据库：

mysql -h 10.11.67.218 -P3307 -ukk -pzxc