mycat学习01--mycat我带你入门
请耐心读完整篇文章，过程中出现的错误点在文章结尾都有总结和解决办法。
目前创新互联已为近千家的企业提供了网站建设、域名、虚拟空间、网站托管、服务器租用、企业网站设计、清江浦网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。
服务器架构
安装MySQL
创建mysql用户
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
安装MySQL
yum install -y libaio
cd /usr/local/src/
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
tar -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-3306
cp -rf mysql-5.7.17-linux-glibc2.5-x86_64 /data/app/mysql-3307
chown -R mysql:mysql /data/app/mysql-3306
chown -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-node2
master
cat > /data/app/mysql-3306/my.cnf< [client] port = 3306 socket = /data/app/mysql-3306/mysql.sock [mysqld] port = 3306 user = mysql server-id = 1 bind-address = 0.0.0.0 basedir = /data/app/mysql-3306 datadir = /data/app/mysql-3306/data socket = /data/app/mysql-3306/mysql.sock pid-file = /data/app/mysql-3306/mysql.pid log-error = /data/app/mysql-3306/mysqld.log skip-name-resolve log_bin = mysql-bin log-slave-updates auto-increment-increment = 2 auto-increment-offset = 1 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES EOF chown mysql.mysql /data/app/mysql-3306/my.cnf slave cat > /data/app/mysql-3307/my.cnf< [client] port = 3307 socket = /data/app/mysql-3307/mysql.sock [mysqld] port = 3307 user = mysql server-id = 11 bind-address = 0.0.0.0 basedir = /data/app/mysql-3307 datadir = /data/app/mysql-3307/data socket = /data/app/mysql-3307/mysql.sock pid-file = /data/app/mysql-3307/mysql.pid log-error = /data/app/mysql-3307/mysqld.log skip-name-resolve log_bin = mysql-bin sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES EOF chown mysql.mysql /data/app/mysql-3307/my.cnf linux-node3 master cat > /data/app/mysql-3306/my.cnf< [client] port = 3306 socket = /data/app/mysql-3306/mysql.sock [mysqld] port = 3306 user = mysql server-id = 2 bind-address = 0.0.0.0 basedir = /data/app/mysql-3306 datadir = /data/app/mysql-3306/data socket = /data/app/mysql-3306/mysql.sock pid-file = /data/app/mysql-3306/mysql.pid log-error = /data/app/mysql-3306/mysqld.log skip-name-resolve log_bin = mysql-bin log-slave-updates auto-increment-increment = 2 auto-increment-offset = 2 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES EOF chown mysql.mysql /data/app/mysql-3306/my.cnf slave cat > /data/app/mysql-3307/my.cnf< [client] port = 3307 socket = /data/app/mysql-3307/mysql.sock [mysqld] port = 3307 user = mysql server-id = 22 bind-address = 0.0.0.0 basedir = /data/app/mysql-3307 datadir = /data/app/mysql-3307/data socket = /data/app/mysql-3307/mysql.sock pid-file = /data/app/mysql-3307/mysql.pid log-error = /data/app/mysql-3307/mysqld.log skip-name-resolve log_bin = mysql-bin sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES EOF chown 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.log sed -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.log sed -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-node2 master cd /data/app/mysql-3306/ ./bin/mysql -uroot -p -S mysql.sock -P 3306 mysql> 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) slave cd /data/app/mysql-3307/ ./bin/mysql -uroot -p -S mysql.sock -P 3307 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; Query OK, 0 rows affected, 2 warnings (0.04 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec) mysql> show slave status\G *************************** 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: Yes linux-node3 master cd /data/app/mysql-3306/ ./bin/mysql -uroot -p -S mysql.sock -P 3306 mysql> 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) slave cd /data/app/mysql-3307/ ./bin/mysql -uroot -p -S mysql.sock -P 3307 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; Query OK, 0 rows affected, 2 warnings (0.04 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec) mysql> show slave status\G *************************** 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 3306 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) linux-node3 master端 cd /data/app/mysql-3306/ ./bin/mysql -uroot -p -S mysql.sock -P 3306 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) 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 status\G *************************** 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 status\G *************************** 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 changed mysql> 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 安装mycat cd /usr/local/src wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz mv mycat /data/app/mycat-1.6 ln -s /data/app/mycat-1.6 /data/app/mycat 修改schema.xml · balance="1" · writeType="0" · switchType="1" cd /data/app/mycat cp conf/schema.xml conf/schema.xml.bak cat > conf/schema.xml < 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的客户端连接mycat cd /data/app/mysql/ ./bin/mysql -uroot -p -P 8066 -h 192.168.56.11 ##连接mycat，初始密码123456 mysql> 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 3306 mycat端插入新的数据查看数据是否同步 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端的数据。 解决办法： [mysqld] log-slave-updates
全部的readHost与stand by writeHost参与select语句的负载均衡
所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个 writeHost，重新启动后以切换后的为准，切换记录在配置文件中:dnindex.properties
默认值为1，自动切换
解释：
从库开启log-bin参数，如果直接往从库写数据，是可以记录log-bin日志的，但是从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据，是不会记录binlog日志的。也就是说从库从主库上复制的数据，是不写入从库的binlog日志的。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。
文章标题：mycat学习01--mycat我带你入门
本文链接：http://mswzjz.cn/article/podish.html