欢迎您光临本站,如有问题请及时联系我们。

mycat结合MySQL双主实现读写分离

  服务器拓扑

  服务器架构

  安装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

  mastercat > /data/app/mysql-3306/my.cnf<<EOF

  [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

  slavecat > /data/app/mysql-3307/my.cnf<<EOF

  [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

  mastercat > /data/app/mysql-3306/my.cnf<<EOF

  [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

  slavecat > /data/app/mysql-3307/my.cnf<<EOF

  [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-node2mastercd /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)

  slavecd /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-node3mastercd /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)

  slavecd /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"

  全部的readHost与stand by writeHost参与select语句的负载均衡

  writeType="0"

  所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个 writeHost,重新启动后以切换后的为准,切换记录在配置文件中:dnindex.properties

  switchType="1"

  默认值为1,自动切换

  cd /data/app/mycat

  cp conf/schema.xml conf/schema.xml.bak

  cat > conf/schema.xml <<EOF

  <?xml version="1.0"?>

  <!DOCTYPE mycat:schema SYSTEM "schema.dtd">

  <mycat:schema xmlns:mycat="http://io.mycat/">

  <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema>

  <dataNode name="dn1" dataHost="node1" database="test" />

  <dataHost name="node1" maxCon="10" minCon="5" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">

  <heartbeat>select user()</heartbeat>

  <writeHost host="master1" url="192.168.56.12:3306" user="root" password="mysql">

  <readHost host="slave2" url="192.168.56.13:3307" user="root" password="mysql" />

  </writeHost>

  <writeHost host="master2" url="192.168.56.13:3306" user="root" password="mysql">

  <readHost host="slave2" url="192.168.56.13:3307" user="root" password="mysql" />

  </writeHost>

  </dataHost>

  </mycat:schema>

  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,初始密码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端的数据。

  解释:

  从库开启log-bin参数,如果直接往从库写数据,是可以记录log-bin日志的,但是从库通过I0线程读取主库二进制日志文件,然后通过SQL线程写入的数据,是不会记录binlog日志的。也就是说从库从主库上复制的数据,是不写入从库的binlog日志的。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。

  解决办法:

  [mysqld]

  log-slave-updates


来源:本文由E8运维原创撰写,欢迎分享本文,转载请保留出处和链接!