一笋籽、讀寫分離
1.atlas實(shí)現(xiàn)讀寫分離
大型網(wǎng)站為了軟件大量的并發(fā)訪問(wèn)蹦漠，除了在網(wǎng)站實(shí)現(xiàn)分布式負(fù)載均衡，遠(yuǎn)遠(yuǎn)不夠车海。到了數(shù)據(jù)業(yè)務(wù) 層笛园、數(shù)據(jù)訪問(wèn)層，如果還是傳統(tǒng)的數(shù)據(jù)結(jié)構(gòu)，或者只是單單靠一臺(tái)服務(wù)器扛，如此多的數(shù)據(jù)庫(kù)連 接操作，數(shù)據(jù)庫(kù)必然會(huì)崩潰杨凑，數(shù)據(jù)丟失的話恒序，后果更是 不堪設(shè)想。這時(shí)候，我們會(huì)考慮如何減少 數(shù)據(jù)庫(kù)的聯(lián)接，一方面采用優(yōu)秀的代碼框架，進(jìn)行代碼的優(yōu)化虱肄，采用優(yōu)秀的數(shù)據(jù)緩存技術(shù)， 如: memcached,如果資 金豐厚的話交煞，必然會(huì)想到假設(shè)服務(wù)器群咏窿，來(lái)分擔(dān)主數(shù)據(jù)庫(kù)的壓力。那么通過(guò) MySQL主從配置错敢，實(shí)現(xiàn)讀寫分離翰灾，減輕數(shù)據(jù)庫(kù)壓力。
- 基本原理 讓master來(lái)響應(yīng)事務(wù)性操作稚茅，讓slave來(lái)響應(yīng)select非事務(wù)性操作纸淮，然后再采用主從復(fù)制來(lái)把
master上的事務(wù)性操作同步到slave數(shù)據(jù)庫(kù)中。
[root@db03 /server/tools]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm Preparing... ################################# [100%]
Updating / installing...
1:Atlas-2.2.1-1 ################################# [100%]
[root@db03 /server/tools]# cd /usr/local/mysql-proxy/conf [root@db03 /usr/local/mysql-proxy/conf]# mv test.cnf test.cnf.bak
1.2 配置
[root@db03 /usr/local/mysql-proxy/conf]# cat > test.cnf <<EOF
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 10.0.0.55:3306
proxy-read-only-backend-addresses = 10.0.0.52:3306,10.0.0.53:3306
pwds = #寫 從庫(kù) repl:3yb5jEku5h4=,mha:O2jBXONX098=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log=ON
proxy-address = 0.0.0.0:33060
admin-address = 0.0.0.0:2345
charset=utf8
EOF
[root@db03 /usr/local/mysql-proxy/conf]# masterha_check_status -- conf=/etc/mha/app1.cnf
app1 (pid:8231) is running(0:PING_OK), master:10.0.0.51
1.3 啟動(dòng)atlas
[root@db03 /usr/local/mysql-proxy/conf]# /usr/local/mysql-proxy/bin/mysql- proxyd test start
error: MySQL-Proxy of test is running now
[root@db03 /usr/local/mysql-proxy/conf]# netstat -lntup|grep proxy
tcp 0 0 0.0.0.0:2345 0.0.0.0:* 8164/mysql-proxy
tcp 0 0 0.0.0.0:33060 0.0.0.0:* 8164/mysql-proxy
1.4 遠(yuǎn)程登錄mha用戶
[root@db03 /usr/local/mysql-proxy/conf]# mysql -umha -pmha -h 10.0.0.53 - P33060
db03 [(none)]>
1.5 檢查只讀id亚享，讀的操作會(huì)在2個(gè)從節(jié)點(diǎn)上漂移咽块，自帶負(fù)載均衡
測(cè)試讀操作
db03 [(none)]>select @@server_id; +-------------+
| @@server_id |
+-------------+
| 52 |
+-------------+
1 row in set (0.01 sec)
db03 [(none)]>select @@server_id; +-------------+
| @@server_id |
+-------------+
| 53 | +-------------+
1 row in set (0.00 sec)
測(cè)試寫操作
mysql> begin;select @@server_id;commit; Query OK, 0 rows affected (0.01 sec)
+-------------+
| @@server_id | +-------------+
| 51 | +-------------+
1 row in set (0.00 sec)
1.6 生產(chǎn)授權(quán)用戶
(1)、業(yè)務(wù)主庫(kù)中創(chuàng)建用戶
例子:root@'10.0.0.%'
業(yè)務(wù)主庫(kù)中
db02 [(none)]>grant all on *.* to root@'10.0.0.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
db02 [(none)]>
(2)將密碼進(jìn)行加密
cd /usr/local/mysql-proxy/bin/
[root@db03 /usr/local/mysql-proxy/bin]# ./encrypt 123 3yb5jEku5h4=
(3) 修改配置文件
vim /usr/local/mysql-proxy/conf/test.cnf
...
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=,root:3yb5jEku5h4= ...
(4) 重啟atlas
[root@db03 /usr/local/mysql-proxy/conf]# /usr/local/mysql-proxy/bin/mysql-proxyd test restart
OK: MySQL-Proxy of test is stopped
OK: MySQL-Proxy of test is started
(5)登錄測(cè)試
mysql -uroot -p123 -h 10.0.0.53 -P 33060
1.7 altas基礎(chǔ)管理
連接管理接口
[root@db03 ~]# mysql -uuser -ppwd -h10.0.0.53 -P2345
atlas常用的
(1) 查幫助
SELECT * FROM help ;
(2)查看節(jié)點(diǎn)信息
SELECT * FROM backends ;
(3)上線和下線節(jié)點(diǎn)
SET OFFLINE $backend_id ;
SET ONLINE $backend_id
(4)刪除和添加節(jié)點(diǎn)
REMOVE BACKEND 3;
ADD SLAVE 10.0.0.53:3306;
(5) 添加用戶和刪除用戶
SELECT * FROM pwds ; 查看
REMOVE PWD $pwd ; 刪除
ADD PWD root:123; 添加 明文
ADD ENPWD $pwd 添加 密文
(6) 持久化配置
SAVE CONFIG
2.分布式數(shù)據(jù)庫(kù)架構(gòu)-MyCAT應(yīng)用
image.png
2.1 環(huán)境準(zhǔn)備:
兩臺(tái)虛擬機(jī) db01 db02
每臺(tái)創(chuàng)建四個(gè)mysql實(shí)例:3307 3308 3309 3310
2.2 2 刪除歷史環(huán)境:
pkill mysqld
rm -rf /data/330*
mv /etc/my.cnf /etc/my.cnf.bak
2.3 創(chuàng)建相關(guān)目錄初始化數(shù)據(jù)
mkdir /data/33{07..10}/data -p
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3310/data --basedir=/application/mysql
2.4 準(zhǔn)備配置文件和啟動(dòng)腳本
========db01==============
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=7
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=8
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=9
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=10
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3308.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3309.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF
========db02===============
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=17
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=18
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=19
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=20
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3308.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3309.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF
2.5 修改權(quán)限欺税，啟動(dòng)多實(shí)例
chown -R mysql.mysql /data/*
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310
mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"
2.6 節(jié)點(diǎn)主從規(guī)劃
箭頭指向誰(shuí)是主庫(kù)
10.0.0.51:3307 <-----> 10.0.0.52:3307
10.0.0.51:3309 ------> 10.0.0.51:3307
10.0.0.52:3309 ------> 10.0.0.52:3307
10.0.0.52:3308 <-----> 10.0.0.51:3308
10.0.0.52:3310 -----> 10.0.0.52:3308
10.0.0.51:3310 -----> 10.0.0.51:3308
2.7 分片規(guī)劃
shard1：
Master：10.0.0.51:3307
slave1：10.0.0.51:3309
Standby Master：10.0.0.52:3307
slave2：10.0.0.52:3309
shard2：
Master：10.0.0.52:3308
slave1：10.0.0.52:3310
Standby Master：10.0.0.51:3308
slave2：10.0.0.51:3310
2.8 開始配置
shard1
10.0.0.51:3307 <-----> 10.0.0.52:3307
db02
mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
mysql -S /data/3307/mysql.sock -e "grant all on *.* to root@'10.0.0.%' identified by '123' with grant option;"
db01
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3307/mysql.sock -e "start slave;"
mysql -S /data/3307/mysql.sock -e "show slave status\G"
db02
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3307/mysql.sock -e "start slave;"
mysql -S /data/3307/mysql.sock -e "show slave status\G"
10.0.0.51:3309 ------> 10.0.0.51:3307
db01
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3309/mysql.sock -e "start slave;"
mysql -S /data/3309/mysql.sock -e "show slave status\G"
10.0.0.52:3309 ------> 10.0.0.52:3307
db02
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3309/mysql.sock -e "start slave;"
mysql -S /data/3309/mysql.sock -e "show slave status\G"
shard2
10.0.0.52:3308 <-----> 10.0.0.51:3308
db01
mysql -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
mysql -S /data/3308/mysql.sock -e "grant all on *.* to root@'10.0.0.%' identified by '123' with grant option;"
db02
mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3308/mysql.sock -e "start slave;"
mysql -S /data/3308/mysql.sock -e "show slave status\G"
db01
mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3308/mysql.sock -e "start slave;"
mysql -S /data/3308/mysql.sock -e "show slave status\G"
10.0.0.52:3310 -----> 10.0.0.52:3308
db02
mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3310/mysql.sock -e "start slave;"
mysql -S /data/3310/mysql.sock -e "show slave status\G"
10.0.0.51:3310 -----> 10.0.0.51:3308
db01
mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3310/mysql.sock -e "start slave;"
mysql -S /data/3310/mysql.sock -e "show slave status\G"
2.9 檢測(cè)主從狀態(tài)
mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Running:
mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Running:
mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Running:
mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Running:
2.10 如果中間出現(xiàn)錯(cuò)誤侈沪，在每個(gè)節(jié)點(diǎn)進(jìn)行執(zhí)行以下命令(2.9 狀態(tài)正確,忽略此步驟)
mysql -S /data/3307/mysql.sock -e "stop slave; reset slave all;"
mysql -S /data/3308/mysql.sock -e "stop slave; reset slave all;"
mysql -S /data/3309/mysql.sock -e "stop slave; reset slave all;"
mysql -S /data/3310/mysql.sock -e "stop slave; reset slave all;"
3. Mycat的安裝
3.1 安裝java環(huán)境
yum install -y java
3.2 上傳到/application 并解壓
略
3.3 軟件介紹
conf:
schema.xml 主配置文件
rule.xml 分片策略定義文件
server.xml mycat服務(wù)有關(guān)的配置
xxxx.txt 分片參數(shù)功能有關(guān)
3.4 啟動(dòng)mycat
[root@db02 ~]# vim /etc/profile
export PATH=/application/mycat/bin:$PATH
source /etc/profile
[root@db02 ~]# mycat start
[root@db02 ~]# mysql -uroot -p123456 -h 10.0.0.52 -P8066
4. 測(cè)試數(shù)據(jù)準(zhǔn)備
db01:
mysql -S /data/3307/mysql.sock
grant all on *.* to root@'10.0.0.%' identified by '123';
source /root/world.sql
mysql -S /data/3308/mysql.sock
grant all on *.* to root@'10.0.0.%' identified by '123';
source /root/world.sql
5. 核心配置文件使用介紹(schema.xml)
5.1 邏輯庫(kù)定義
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
</schema>
5.2 數(shù)據(jù)節(jié)點(diǎn)定義
<dataNode name="sh1" dataHost="old1" database= "world" />
5.3 數(shù)據(jù)主機(jī)定義
<dataHost name="old1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.0.0.51:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.51:3309" user="root" password="123" />
</writeHost>
</dataHost>
6. Mycat實(shí)現(xiàn)讀寫分離功能
[root@db02 /application/mycat/conf]# cat schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "world" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.0.0.51:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.51:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
重啟生效:
[root@db02 /application/mycat/conf]# mycat restart
測(cè)試:
[root@db02 /application/mycat/conf]# mysql -uroot -p123456 -h 10.0.0.52 -P8066
mysql> select @@server_id;
mysql> begin; select @@server_id;commit;
7. 讀寫分離+高可用功能
[root@db01 conf]# mv schema.xml schema.xml.rw
[root@db01 conf]# vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
</schema>
<dataNode name="sh1" dataHost="old1" database= "world" />
<dataHost name="old1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.0.0.51:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.51:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.52:3307" user="root" password="123">
<readHost host="db4" url="10.0.0.52:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
真正的 writehost：負(fù)責(zé)寫操作的writehost
standby writeHost ：和readhost一樣，只提供讀服務(wù)
當(dāng)寫節(jié)點(diǎn)宕機(jī)后晚凿，后面跟的readhost也不提供服務(wù)亭罪，這時(shí)候standby的>writehost就提供寫服務(wù)，
后面跟的readhost提供讀服務(wù)
測(cè)試：
mysql -uroot -p123456 -h 127.0.0.1 -P 8066
show variables like 'server_id';
讀寫分離測(cè)試
mysql -uroot -p -h 127.0.0.1 -P8066
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
begin;
show variables like 'server_id';
對(duì)db01 3307節(jié)點(diǎn)進(jìn)行關(guān)閉和啟動(dòng),測(cè)試讀寫操作
8. 配置中的屬性介紹:(了解一下)
balance屬性
負(fù)載均衡類型歼秽，目前的取值有3種：
1. balance="0", 不開啟讀寫分離機(jī)制应役，所有讀操作都發(fā)送到當(dāng)前可用的writeHost上。
2. balance="1"燥筷，全部的readHost與standby writeHost參與select語(yǔ)句的負(fù)載均衡箩祥，簡(jiǎn)單的說(shuō)，
當(dāng)雙主雙從模式(M1->S1肆氓，M2->S2袍祖，并且M1與 M2互為主備)，正常情況下谢揪，M2,S1,S2都參與select語(yǔ)句的負(fù)載均衡蕉陋。
3. balance="2"捐凭，所有讀操作都隨機(jī)的在writeHost、readhost上分發(fā)寺滚。
writeType屬性
負(fù)載均衡類型柑营，目前的取值有2種：
1. writeType="0", 所有寫操作發(fā)送到配置的第一個(gè)writeHost，
第一個(gè)掛了切到還生存的第二個(gè)writeHost村视，重新啟動(dòng)后已切換后的為主，切換記錄在配置文件中:dnindex.properties .
2. writeType=“1”酒奶，所有寫操作都隨機(jī)的發(fā)送到配置的writeHost蚁孔，但不推薦使用
switchType屬性
-1 表示不自動(dòng)切換
1 默認(rèn)值，自動(dòng)切換
2 基于MySQL主從同步的狀態(tài)決定是否切換 惋嚎，心跳語(yǔ)句為 show slave status
datahost其他配置
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
maxCon="1000"：最大的并發(fā)連接數(shù)
minCon="10" ：mycat在啟動(dòng)之后杠氢，會(huì)在后端節(jié)點(diǎn)上自動(dòng)開啟的連接線程
tempReadHostAvailable="1"
這個(gè)一主一從時(shí)（1個(gè)writehost，1個(gè)readhost時(shí)）另伍，可以開啟這個(gè)參數(shù)鼻百，如果2個(gè)writehost，2個(gè)readhost時(shí)
<heartbeat>select user()</heartbeat> 監(jiān)測(cè)心跳
10. 垂直分表
mv schema.xml schema.xml.ha
vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
<table name="user" dataNode="sh1"/>
<table name="order_t" dataNode="sh2"/>
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "taobao" />
<dataNode name="sh2" dataHost="oldguo2" database= "taobao" />
<dataHost name="old1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.0.0.51:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.51:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.52:3307" user="root" password="123">
<readHost host="db4" url="10.0.0.52:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="old2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.0.0.51:3308" user="root" password="123">
<readHost host="db2" url="10.0.0.51:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.52:3308" user="root" password="123">
<readHost host="db4" url="10.0.0.52:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
創(chuàng)建測(cè)試庫(kù)和表:
mysql -S /data/3307/mysql.sock -e "create database taobao charset utf8;"
mysql -S /data/3308/mysql.sock -e "create database taobao charset utf8;"
mysql -S /data/3307/mysql.sock -e "use taobao;create table user(id int,name varchar(20))";
mysql -S /data/3308/mysql.sock -e "use taobao;create table order_t(id int,name varchar(20))"
11. MyCAT核心特性——分片（水平拆分）
分片：對(duì)一個(gè)"bigtable"摆尝，比如說(shuō)t3表
(1)行數(shù)非常多温艇，800w
(2)訪問(wèn)非常頻繁
分片的目的：
（1）將大數(shù)據(jù)量進(jìn)行分布存儲(chǔ)
（2）提供均衡的訪問(wèn)路由
分片策略：
范圍 range 800w 1-400w 400w01-800w
取模 mod 取余數(shù)
枚舉
哈希 hash
時(shí)間 流水
優(yōu)化關(guān)聯(lián)查詢
全局表
ER分片
12 .范圍分片
比如說(shuō)t3表
(1)行數(shù)非常多，2000w（1-1000w:sh1 1000w01-2000w:sh2）
(2)訪問(wèn)非常頻繁堕汞，用戶訪問(wèn)較離散
cp schema.xml schema.xml.1
vim schema.xml
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
<table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" />
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "taobao" />
<dataNode name="sh2" dataHost="oldguo2" database= "taobao" />
vim rule.xml
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
===================================
vim autopartition-long.txt
0-10=0
11-20=1
創(chuàng)建測(cè)試表：
mysql -S /data/3307/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
測(cè)試：
重啟mycat
mycat restart
mysql -uroot -p123456 -h 127.0.0.1 -P 8066
insert into t3(id,name) values(1,'a');
insert into t3(id,name) values(2,'b');
insert into t3(id,name) values(3,'c');
insert into t3(id,name) values(4,'d');
insert into t3(id,name) values(11,'aa');
insert into t3(id,name) values(12,'bb');
insert into t3(id,name) values(13,'cc');
insert into t3(id,name) values(14,'dd');
13. 取模分片（mod-long）
取余分片方式：分片鍵（一個(gè)列）與節(jié)點(diǎn)數(shù)量進(jìn)行取余勺爱，得到余數(shù)，將數(shù)據(jù)寫入對(duì)應(yīng)節(jié)點(diǎn)
vim schema.xml
<table name="t4" dataNode="sh1,sh2" rule="mod-long" />
vim rule.xml
<property name="count">2</property>
準(zhǔn)備測(cè)試環(huán)境
創(chuàng)建測(cè)試表：
mysql -S /data/3307/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
重啟mycat
mycat restart
測(cè)試：
mysql -uroot -p123456 -h10.0.0.52 -P8066
use TESTDB
insert into t4(id,name) values(1,'a');
insert into t4(id,name) values(2,'b');
insert into t4(id,name) values(3,'c');
insert into t4(id,name) values(4,'d');
分別登錄后端節(jié)點(diǎn)查詢數(shù)據(jù)
mysql -S /data/3307/mysql.sock -e "use taobao;select * from t4;"
mysql -S /data/3308/mysql.sock -e "use taobao;select * from t4;"
14. 枚舉分片
t5 表
id name telnum
1 bj 1212
2 sh 22222
3 bj 3333
4 sh 44444
5 bj 5555
sharding-by-intfile
vim schema.xml
<table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" />
vim rule.xml
<tableRule name="sharding-by-intfile">
<rule> <columns>name</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property>
</function>
partition-hash-int.txt 配置：
bj=0
sh=1
DEFAULT_NODE=1
columns 標(biāo)識(shí)將要分片的表字段讯检，algorithm 分片函數(shù)琐鲁， 其中分片函數(shù)配置中，mapFile標(biāo)識(shí)配置文件名稱
準(zhǔn)備測(cè)試環(huán)境
mysql -S /data/3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
重啟mycat
mycat restart
mysql -uroot -p123456 -h10.0.0.52 -P8066
use TESTDB
insert into t5(id,name) values(1,'bj');
insert into t5(id,name) values(2,'sh');
insert into t5(id,name) values(3,'bj');
insert into t5(id,name) values(4,'sh');
insert into t5(id,name) values(5,'tj');
分別登錄后端節(jié)點(diǎn)查詢數(shù)據(jù)
mysql -S /data/3307/mysql.sock -e "use taobao;select * from t5;"
mysql -S /data/
15 . Mycat全局表
a b c d
join
t
select t1.name ,t.x from t1
join t
select t2.name ,t.x from t2
join t
select t3.name ,t.x from t3
join t
使用場(chǎng)景：
如果你的業(yè)務(wù)中有些數(shù)據(jù)類似于數(shù)據(jù)字典人灼，比如配置文件的配置围段，
常用業(yè)務(wù)的配置或者數(shù)據(jù)量不大很少變動(dòng)的表，這些表往往不是特別大投放，
而且大部分的業(yè)務(wù)場(chǎng)景都會(huì)用到奈泪，那么這種表適合于Mycat全局表，無(wú)須對(duì)數(shù)據(jù)進(jìn)行切分跪呈，
要在所有的分片上保存一份數(shù)據(jù)即可段磨，Mycat 在Join操作中，業(yè)務(wù)表與全局表進(jìn)行Join聚合會(huì)優(yōu)先選擇相同分片內(nèi)的全局表join耗绿，
避免跨庫(kù)Join苹支，在進(jìn)行數(shù)據(jù)插入操作時(shí)，mycat將把數(shù)據(jù)分發(fā)到全局表對(duì)應(yīng)的所有分片執(zhí)行误阻，在進(jìn)行數(shù)據(jù)讀取時(shí)候?qū)?huì)隨機(jī)獲取一個(gè)節(jié)點(diǎn)讀取數(shù)據(jù)债蜜。
vim schema.xml
<table name="t_area" primaryKey="id" type="global" dataNode="sh1,sh2" />
后端數(shù)據(jù)準(zhǔn)備
mysql -S /data/3307/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"
重啟mycat
mycat restart
測(cè)試：
mysql -uroot -p123456 -h10.0.0.52 -P8066
use TESTDB
insert into t_area(id,name) values(1,'a');
insert into t_area(id,name) values(2,'b');
insert into t_area(id,name) values(3,'c');
insert into t_area(id,name) values(4,'d');
分別登錄后端節(jié)點(diǎn)查詢數(shù)據(jù)
mysql -S /data/3307/mysql.sock -e "use taobao;select * from t_area;"
16. E-R分片
A join B
為了防止跨分片join晴埂，可以使用E-R模式
join C
on A.id=C.id
<table name="A" dataNode="sh1,sh2" rule="mod-long">
<childTable name="B" joinKey="yy" parentKey="xx" />
</table>
