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
master
cat > /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
slave
cat > /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
master
cat > /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
slave
cat > /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-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 <<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的客戶端連線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中介軟體實現Mysql主從讀寫分離MySql
- Mycat實現mysql的負載均衡讀寫分離MySql負載
- 高可用Mysql架構_Mysql主從複製、Mysql雙主熱備、Mysql雙主雙從、Mysql讀寫分離（Mycat中介軟體）、Mysql分庫分表架構（Mycat中介軟體）的演變MySql架構
- Mycat讀寫分離配置實踐
- 搭建MySQL主從實現Django讀寫分離MySqlDjango
- MySQL運維16-雙主雙從讀寫分離MySql運維
- haproxy+keepalived+mycat+mysql (讀寫分離）MySql
- CentOS7系統下使用Mycat實現mysql讀寫分離部署CentOSMySql
- MyCat分庫分表、讀寫分離
- Mycat 讀寫分離+分庫分表
- ProxySQL實現MySQL讀寫分離MySql
- Amoeba 實現 MySQL 讀寫分離MySql
- Mycat中介軟體實現Percona Cluster讀寫分離
- MySQL怎麼實現主從同步和Django實現MySQL讀寫分離MySql主從同步Django
- 【Mycat】Mycat讀寫分離與負載均衡2負載
- Mycat讀寫分離、主從切換、分庫分表的操作記錄
- Amoeba+Mysql 實現讀寫分離MySql
- mysql-proxy實現讀寫分離MySql
- MYSQL 主從 + ATLAS 讀寫分離 搭建MySql
- 配置\清除 MySQL 主從 讀寫分離MySql
- MySQL主從複製讀寫分離MySql
- docker+atlas+mysql實現讀寫分離DockerMySql
- Mysql讀寫分離叢集的搭建且與MyCat進行整合MySql
- Kubernetes 中實現 MySQL 的讀寫分離MySql
- MySQL主從複製與讀寫分離MySql
- [Mysql]主從複製和讀寫分離MySql
- MySQL主從分離實現MySql
- mysql讀寫分離的最佳實踐MySql
- ProxySQL實現Mysql讀寫分離 - 部署手冊MySql
- MySQL 中介軟體Atlas 實現讀寫分離MySql
- Amoeba實現讀寫分離
- MysqL讀寫分離的實現-Mysql proxy中介軟體的使用MySql
- MySQL主從同步讀寫分離的叢集配置MySql主從同步
- docker+mysql叢集+讀寫分離+mycat管理+垂直分庫+負載均衡DockerMySql負載
- springboot+mybatis+druid實現mysql主從讀寫分離(五)Spring BootMyBatisUIMySql
- 資料庫讀寫分離，主從同步實現方法資料庫主從同步
- Mysql-主從複製與讀寫分離MySql
- 【Mongo】Mongo讀寫分離的實現Go