mycat学习01– mycat我带你入门

请耐心读完整篇文章，过程中出现的错误点在文章结尾都有总结和解决办法。服务器架构
安装MySQL创建mysql用户groupadd mysqluseradd -r -g mysql -s /bin/false mysql安装MySQLyum install -y libaiocd /usr/local/src/wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gztar -zxf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz cp -rf mysql-5.7.17-linux-glibc2.5-x86_64 /data/app/mysql-3306cp -rf mysql-5.7.17-linux-glibc2.5-x86_64 /data/app/mysql-3307chown -R mysql:mysql /data/app/mysql-3306chown -R mysql:mysql /data/app/mysql-3307/data/app/mysql-3306/bin/mysqld –initialize-insecure –user=mysql –basedir=/data/app/mysql-3306 –datadir=/data/app/mysql-3306/data/data/app/mysql-3307/bin/mysqld –initialize-insecure –user=mysql –basedir=/data/app/mysql-3307 –datadir=/data/app/mysql-3307/data修改my.cnf需要修改的参数： server-id：保证每个配置文件唯一 两台master的自增长ID必须不同linux-node2mastercat > /data/app/mysql-3306/my.cnf[client]port = 3306socket = /data/app/mysql-3306/mysql.sock[mysqld]port = 3306user = mysqlserver-id = 1bind-address = 0.0.0.0basedir = /data/app/mysql-3306datadir = /data/app/mysql-3306/datasocket = /data/app/mysql-3306/mysql.sockpid-file = /data/app/mysql-3306/mysql.pidlog-error = /data/app/mysql-3306/mysqld.logskip-name-resolvelog_bin = mysql-binlog-slave-updatesauto-increment-increment = 2 auto-increment-offset = 1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES EOFchown mysql.mysql /data/app/mysql-3306/my.cnfslavecat > /data/app/mysql-3307/my.cnf[client]port = 3307socket = /data/app/mysql-3307/mysql.sock[mysqld]port = 3307user = mysqlserver-id = 11bind-address = 0.0.0.0basedir = /data/app/mysql-3307datadir = /data/app/mysql-3307/datasocket = /data/app/mysql-3307/mysql.sockpid-file = /data/app/mysql-3307/mysql.pidlog-error = /data/app/mysql-3307/mysqld.logskip-name-resolvelog_bin = mysql-binsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES EOFchown mysql.mysql /data/app/mysql-3307/my.cnflinux-node3mastercat > /data/app/mysql-3306/my.cnf[client]port = 3306socket = /data/app/mysql-3306/mysql.sock[mysqld]port = 3306user = mysqlserver-id = 2bind-address = 0.0.0.0basedir = /data/app/mysql-3306datadir = /data/app/mysql-3306/datasocket = /data/app/mysql-3306/mysql.sockpid-file = /data/app/mysql-3306/mysql.pidlog-error = /data/app/mysql-3306/mysqld.logskip-name-resolvelog_bin = mysql-binlog-slave-updatesauto-increment-increment = 2 auto-increment-offset = 2sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES EOFchown mysql.mysql /data/app/mysql-3306/my.cnfslavecat > /data/app/mysql-3307/my.cnf[client]port = 3307socket = 开发云主机域名/data/app/mysql-3307/mysql.sock[mysqld]port = 3307user = mysqlserver-id = 22bind-address = 0.0.0.0basedir = /data/app/mysql-3307datadir = /data/app/mysql-3307/datasocket = /data/app/mysql-3307/mysql.sockpid-file = /data/app/mysql-3307/mysql.pidlog-error = /data/app/mysql-3307/mysqld.logskip-name-resolvelog_bin = mysql-binsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES EOFchown mysql.mysql /data/app/mysql-3307/my.cnf启动MySQL启动服务linux-node2和linux-node3都执行如下命令touch /data/app/mysql-3306/mysqld.log && chown mysql.mysql /data/app/mysql-3306/mysqld.logsed -i ‘s#/usr/local/mysql#/data/app/mysql-3306#g’ /data/app/mysql-3306/bin/mysqld_safe /data/app/mysql-3306/bin/mysqld_safe –defaults-file=/data/app/mysql-3306/my.cnf –basedir=/data/app/mysql-3306 –datadir=/data/app/mysql-3306/data –user=mysql &touch /data/app/mysql-3307/mysqld.log && chown mysql.mysql /data/app/mysql-3307/mysqld.logsed -i ‘s#/usr/local/mysql#/data/app/mysql-3307#g’ /data/app/mysql-3307/bin/mysqld_safe /data/app/mysql-3307/bin/mysqld_safe –defaults-file=/data/app/mysql-3307/my.cnf –basedir=/data/app/mysql-3307 –datadir=/data/app/mysql-3307/data –user=mysql &检查端口ss -lntup |egrep ‘3306|3307’tcp LISTEN 0 80 *:3306 *:* users:((“mysqld”,19973,22))tcp LISTEN 0 80 *:3307 *:* users:((“mysqld”,20537,22))配置双主配置主从linux-node2mastercd /data/app/mysql-3306/./bin/mysql -uroot -p -S mysql.sock -P 3306mysql> CREATE USER ‘repl’@’192.%’ IDENTIFIED BY ‘mysql’;Query OK, 0 rows affected (0.05 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.%’;Query OK, 0 rows affected (0.00 sec)mysql> show master status;+——————+———-+————–+——————+——————-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+——————+———-+————–+——————+——————-+| mysql-bin.000001 | 613 | | | |+——————+———-+————–+——————+——————-+1 row in set (0.00 sec)slavecd /data/app/mysql-3307/./bin/mysql -uroot -p -S mysql.sock -P 3307mysql> CHANGE MASTER TO MASTER_HOST=’192.168.56.12′, -> MASTER_PORT=3306, -> MASTER_USER=’repl’, -> MASTER_PASSWORD=’mysql’, -> MASTER_LOG_FILE=’mysql-bin.000001′, -> MASTER_LOG_POS=613;Query OK, 0 rows affected, 2 warnings (0.04 sec)mysql> start slave;Query OK, 0 rows affected (0.02 sec)mysql> show slave statusG*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.12 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 613 Relay_Log_File: linux-node2-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yeslinux-node3mastercd /data/app/mysql-3306/./bin/mysql -uroot -p -S mysql.sock -P 3306mysql> CREATE USER ‘repl’@’192.%’ IDENTIFIED BY ‘mysql’;Query OK, 0 rows affected (0.05 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.%’;Query OK, 0 rows affected (0.00 sec)mysql> show master status;+——————+———-+————–+——————+——————-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+——————+———-+————–+——————+——————-+| mysql-bin.000001 | 613 | | | |+——————+———-+————–+——————+——————-+1 row in set (0.00 sec)slavecd /data/app/mysql-3307/./bin/mysql -uroot -p -S mysql.sock -P 3307mysql> CHANGE MASTER TO MASTER_HOST=’192.168.56.13′, MASTER_PORT=3306, MASTER_USER=’repl’, MASTER_PASSWORD=’mysql’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=613;Query OK, 0 rows affected, 2 warnings (0.04 sec)mysql> start slave;Query OK, 0 rows affected (0.02 sec)mysql> show slave statusG*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.13 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 613 Relay_Log_File: linux-node2-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes配置双主master的binlog位置linux-node2 master端d /data/app/mysql-3306/./bin/mysql -uroot -p -S mysql.sock -P 3306mysql> show master status;+——————+———-+————–+——————+——————-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+——————+———-+————–+——————+——————-+| mysql-bin.000001 | 613 | | | |+——————+———-+————–+——————+——————-+1 row in set (0.00 sec)linux-node3 master端cd /data/app/mysql-3306/./bin/mysql -uroot -p -S mysql.sock -P 3306mysql> show master status;+——————+———-+————–+——————+——————-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+——————+———-+————–+——————+——————-+| mysql-bin.000001 | 613 | | | |+——————+———-+————–+——————+——————-+1 row in set (0.00 sec)linux-node2 master配置跟linux-node3 master同步mysql> CHANGE MASTER TO MASTER_HOST=’192.168.56.13′, MASTER_PORT=3306, MASTER_USER=’repl’, MASTER_PASSWORD=’mysql’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=613;mysql> start slave;Query OK, 0 rows affected (0.03 sec)mysql> show slave statusG*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.13 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 613 Relay_Log_File: linux-node2-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes linux-node3 master配置跟linux-node2 master同步mysql> CHANGE MASTER TO MASTER_HOST=’192.168.56.12′, MASTER_PORT=3306, MASTER_USER=’repl’, MASTER_PASSWORD=’mysql’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=613;mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> mysql> show slave statusG*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.12 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 613 Relay_Log_File: linux-node3-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes验证从linux-node2上验证在linux-node2 master上创建数据mysql> create database test;Query OK, 1 row affected (0.01 sec)mysql> use test;Database changedmysql> create table temp(id int,name varchar(64));Query OK, 0 rows affected (0.11 sec)mysql> insert into temp values(1,’aaa’);Query OK, 1 row affected (0.28 sec)mysql> CREATE TABLE temp2(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT ,nname VARCHAR(64));Query OK, 0 rows affected (0.01 sec)mysql> insert into temp2(nname) values(‘bbb’);Query OK, 1 row affected (0.01 sec)mysql> select * from test.temp;+——+——+| id | name |+——+——+| 1 | aaa |+——+——+1 row in set (0.01 sec)在linux-node2 slave上查看数据mysql> select * from test.temp;+——+——+| id | name |+——+——+| 1 | aaa |+——+——+1 row in set (0.00 sec)在linux-node3 master上查看数据mysql> select * from test.temp;+——+——+| id | name |+——+——+| 1 | aaa |+——+——+1 row in set (0.00 sec)在linux-node3 slave上查看数据mysql> select * from test.temp;+——+——+| id | name |+——+——+| 1 | aaa |+——+——+1 row in set (0.00 sec)从linux-node3上验证在linux-node3 master上创建数据mysql> use test;mysql> insert into temp2(nname) values(‘ddd’);Query OK, 1 row affected (0.02 sec)mysql> insert into temp2(nname) values(‘fff’);Query OK, 1 row affected (0.00 sec)mysql> select * from test.temp2;+—-+——-+| id | nname |+—-+——-+| 1 | bbb || 2 | ddd || 4 | fff |+—-+——-+在linux-node3 slave上查看数据mysql> select * from test.temp2;+—-+——-+| id | nname |+—-+——-+| 1 | bbb || 2 | ddd || 4 | fff |+—-+——-+3 rows in set (0.00 sec)在linux-node2 master上查看数据mysql> select * from test.temp2;+—-+——-+| id | nname |+—-+——-+| 1 | bbb || 2 | ddd || 4 | fff |+—-+——-+3 rows in set (0.00 sec)在linux-node2 slave上查看数据mysql> select * from test.temp2;+—-+——-+| id | nname |+—-+——-+| 1 | bbb || 2 | ddd || 4 | fff |+—-+——-+3 rows in set (0.00 sec)结论 在任意一个master端更新数据，其他任意端都可以更新数据 两台服务器配置了间隔自增长，数据不同冲突linux-node1上安装mycat安装mycatcd /usr/local/srcwget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gztar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz mv mycat /data/app/mycat-1.6ln -s /data/app/mycat-1.6 /data/app/mycat修改schema.xml balance=”1″
全部的readHost与stand by writeHost参与select语句的负载均衡 writeType=”0″
所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个 writeHost，重新启动后以切换后的为准，切换记录在配置文件中:dnindex.properties switchType=”1″
默认值为1，自动切换cd /data/app/mycatcp conf/schema.xml conf/schema.xml.bakcat > conf/schema.xml select user() EOF启动mycat./bin/mycat start ss -lntup |egrep ‘(8066|9066)’ tcp LISTEN 0 100 :::8066 :::* users:((“java”,16546,79))tcp LISTEN 0 100 :::9066 :::* users:((“java”,16546,75))验证mycat服务是否正常在linux-node2-master端配置mycat连接账号mysql> GRANT ALL PRIVILEGES ON *.* TO root@’192.%’ IDENTIFIED BY ‘mysql’; Query OK, 0 rows affected, 1 warning (0.07 sec在mycat服务器上安装mysql服务，但是不启动步骤省略，详细内容可以参考上面的MySQL安装使用mysql的客户端连接mycatcd /data/app/mysql/./bin/mysql -uroot -p -P 8066 -h 192.168.56.11 ##连接mycat，初始密码123456mysql> show databases;+———-+| DATABASE |+———-+| TESTDB |+———-+1 row in set (0.00 sec)mysql> use TESTDB;mysql> insert into temp2(nname) values(‘eee’);Query OK, 1 row affected (0.09 sec)mysql> insert into temp2(nname) values(‘ggg’);Query OK, 1 row affected (0.01 sec)linux-node3 slave端查看数据是否同步mysql> select * from test.temp2;+—-+——-+| id | nname |+—-+——-+| 1 | bbb || 2 | ddd || 4 | fff || 5 | eee || 7 | ggg |+—-+——-+5 rows in set (0.00 sec)结果发现数据写入到了linux-node2 slave端测试服务自动迁移关闭linux-node2 master的MySQL服务mysql> shutdown;Query OK, 0 rows affected (0.01 sec)shell > ss -lntup |grep 3306mycat端插入新的数据查看数据是否同步mysql> insert into temp2(nname) values(‘mmmm’);Query OK, 1 row affected (0.07 sec)mysql> insert into temp2(nname) values(‘nnnn’);Query OK, 1 row affected (0.01 sec)linux-node3 slave端查看数据是否同步mysql> select * from test.temp2;+—-+——-+| id | nname |+—-+——-+| 1 | bbb || 2 | ddd || 4 | fff || 5 | eee || 7 | ggg || 8 | mmmm || 10 | nnnn |+—-+——-+7 rows in set (0.00 sec)linux-node2 slave端查看数据是否同步mysql> select * from test.temp2;+—-+——-+| id | nname |+—-+——-+| 1 | bbb || 2 | ddd || 4 | fff || 5 | eee || 7 | ggg |+—-+——-+5 rows in set (0.00 sec)发现因为linux-node2的master端已经挂了，数据不能同步了数据访问是否正常登录到mycat服务器上执行如下命令：mysql> select * from temp2;+—-+——-+| id | nname |+—-+——-+| 1 | bbb || 2 | ddd || 4 | fff || 5 | eee || 7 | ggg || 8 | mmmm || 10 | nnnn |+—-+——-+7 rows in set (0.00 sec) 执行多次发现结果一样，说明在一台master端挂掉的情况下，其连接的slave端也被剔除，因此数据完整性可以保证故障汇总第一次配置的时候maser端没有配置log-slave-updates，导致node3-slave上没有node2-master端的数据。
解释：
从库开启log-bin参数，如果直接往从库写数据，是可以记录log-bin日志的，但是从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据，是不会记录binlog日志的。也就是说从库从主库上复制的数据，是不写入从库的binlog日志的。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。解决办法：[mysqld]log-slave-updates

相关推荐: 远程登陆mysql5.7服务端

最开始登陆直接报错 我的远程登陆系统是Mac os nancylulululudeMacBook-Air:bin nancy$ mysql -h 172.16.158.132 -u root -p Enter password: ERROR 2003 (HY0…