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