Mycat基礎(chǔ)環(huán)境

  1. 準(zhǔn)備環(huán)境
    2.1 環(huán)境準(zhǔn)備：
    兩臺(tái)虛擬機(jī) db01 db02
    每臺(tái)創(chuàng)建四個(gè)mysql實(shí)例：3307 3308 3309 3310

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=/usr/local/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/usr/local/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/usr/local/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3310/data --basedir=/usr/local/mysql

2.4 準(zhǔn)備配置文件和啟動(dòng)腳本
========db01==============
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/usr/local/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=/usr/local/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=/usr/local/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=/usr/local/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=/usr/local/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=/usr/local/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=/usr/local/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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF

========db02===============
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/usr/local/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=/usr/local/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=/usr/local/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=/usr/local/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=/usr/local/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=/usr/local/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=/usr/local/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=/usr/local/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 開(kāi)始配置主從環(huán)境

節(jié)點(diǎn)主從規(guī)劃

開(kāi)始配置主從環(huán)境

# shard1
##  10.0.0.51:3307 <-----> 10.0.0.52:3307
# db02
mysql  -S /data/3307/mysql.sock -e "create user repl@'10.0.0.%' identified with mysql_native_password by '123'; grant replication slave on *.* to repl@'10.0.0.%';"

mysql  -S /data/3307/mysql.sock -e "create user root@'10.0.0.%' identified with mysql_native_password by '123'; grant all  on *.* to root@'10.0.0.%'; "

# 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"|grep Running:

# 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"|grep Running:


## 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"|grep Running:

## 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"|grep Running:

====================================================================
# shard2
## 10.0.0.52:3308 <-----> 10.0.0.51:3308

# db01

mysql  -S /data/3308/mysql.sock -e "create user repl@'10.0.0.%' identified with mysql_native_password by '123'; grant replication slave on *.* to repl@'10.0.0.%';"

mysql  -S /data/3308/mysql.sock -e "create user root@'10.0.0.%' identified with mysql_native_password by '123'; grant all  on *.* to root@'10.0.0.%'; "

# 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"|grep Running:

# 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"|grep Running:

## 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"|grep Running:

##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"|grep Running:



2.7 檢測(cè)主從狀態(tài)
mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

注：如果中間出現(xiàn)錯(cuò)誤觉既，在每個(gè)節(jié)點(diǎn)進(jìn)行執(zhí)行以下命令播歼，從2.6從頭執(zhí)行
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;"

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 人面猴
    序言：七十年代末窟坐，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌，老刑警劉巖，帶你破解...
    沈念sama閱讀 219,110評(píng)論 6贊 508
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件搂擦，死亡現(xiàn)場(chǎng)離奇詭異，居然都是意外死亡哗脖，警方通過(guò)查閱死者的電腦和手機(jī)瀑踢，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,443評(píng)論 3贊 395
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門(mén)，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)懒熙，“玉大人丘损，你說(shuō)我怎么就攤上這事」ぴ” “怎么了徘钥？”我有些...
    開(kāi)封第一講書(shū)人閱讀 165,474評(píng)論 0贊 356
  • 道士緝兇錄：失蹤的賣(mài)姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長(zhǎng)肢娘。 經(jīng)常有香客問(wèn)我呈础，道長(zhǎng)，這世上最難降的妖魔是什么橱健？ 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 58,881評(píng)論 1贊 295
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任而钞，我火速辦了婚禮，結(jié)果婚禮上拘荡，老公的妹妹穿的比我還像新娘臼节。我一直安慰自己，他們只是感情好，可當(dāng)我...
    茶點(diǎn)故事閱讀 67,902評(píng)論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
    文/花漫 我一把揭開(kāi)白布网缝。 她就那樣靜靜地躺著巨税，像睡著了一般。 火紅的嫁衣襯著肌膚如雪粉臊。 梳的紋絲不亂的頭發(fā)上草添，一...
    開(kāi)封第一講書(shū)人閱讀 51,698評(píng)論 1贊 305
  • 城市分裂傳說(shuō)
    那天，我揣著相機(jī)與錄音扼仲，去河邊找鬼远寸。 笑死，一個(gè)胖子當(dāng)著我的面吹牛屠凶，可吹牛的內(nèi)容都是我干的驰后。 我是一名探鬼主播，決...
    沈念sama閱讀 40,418評(píng)論 3贊 419
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開(kāi)眼阅畴，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼倡怎！你這毒婦竟也來(lái)了？” 一聲冷哼從身側(cè)響起贱枣，我...
    開(kāi)封第一講書(shū)人閱讀 39,332評(píng)論 0贊 276
  • 萬(wàn)榮殺人案實(shí)錄
    序言：老撾萬(wàn)榮一對(duì)情侶失蹤，失蹤者是張志新（化名）和其女友劉穎颤专，沒(méi)想到半個(gè)月后纽哥，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 45,796評(píng)論 1贊 316
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡栖秕，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,968評(píng)論 3贊 337
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年春塌，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片簇捍。...
    茶點(diǎn)故事閱讀 40,110評(píng)論 1贊 351
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡只壳，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出暑塑，到底是詐尸還是另有隱情吼句，我是刑警寧澤，帶...
    沈念sama閱讀 35,792評(píng)論 5贊 346
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布事格，位于F島的核電站惕艳，受9級(jí)特大地震影響，放射性物質(zhì)發(fā)生泄漏驹愚。R本人自食惡果不足惜远搪，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,455評(píng)論 3贊 331
  • 男人毒藥：我在死后第九天來(lái)索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望逢捺。 院中可真熱鬧谁鳍，春花似錦、人聲如沸。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 32,003評(píng)論 0贊 22
  • 一樁弒父案余佛，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)。三九已至窍荧，卻和暖如春辉巡，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背蕊退。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 33,130評(píng)論 1贊 272
  • 情欲美人皮
    我被黑心中介騙來(lái)泰國(guó)打工郊楣， 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人瓤荔。 一個(gè)月前我還...
    沈念sama閱讀 48,348評(píng)論 3贊 373
  • 代替公主和親
    正文 我出身青樓净蚤，卻偏偏與公主長(zhǎng)得像，于是被迫代替她去往敵國(guó)和親输硝。 傳聞我的和親對(duì)象是個(gè)殘疾皇子今瀑，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,047評(píng)論 2贊 355

推薦閱讀更多精彩內(nèi)容