[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"
全部的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 <
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的客户端连接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端的数据。
解释：
从库开启log-bin参数，如果直接往从库写数据，是可以记录log-bin日志的，但是从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据，是不会记录binlog日志的。也就是说从库从主库上复制的数据，是不写入从库的binlog日志的。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。
解决办法：
[mysqld]
log-slave-updates
当前名称：mycat学习01--mycat我带你入门
路径分享：http://cdkjz.cn/article/podish.html
