當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL搭建主从复制读写分离分库分表 MyCat高可用

發(fā)布時間：2024/9/20 数据库 24 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL搭建主从复制读写分离分库分表 MyCat高可用小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

主從演示

讀寫演示?

分表演示

主從復(fù)制

環(huán)境的介紹?

系統(tǒng)環(huán)境:centos7.0 客戶端連接工具:xshell 遠(yuǎn)程文件傳輸工具:xftp 服務(wù)器: 192.168.126.138(主) 192.168.126.139(從)

基于rpm實現(xiàn)MySQL的安裝

#查看有沒有安裝MySQL rpm -qa | grep -i mysql #卸載 rpm -e --nodeps mysql-community-client-5.7.28-1.el7.x86_64 #查找 find / -name mysql #刪除存在的MySQL文件 rm -rf /etc/selinux/targeted/active/modules/100/mysql /usr/lib64/mysql /usr/share/mysql #安裝server rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm --force --nodeps #安裝客戶端 rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm --force --nodeps

登錄MySQL

[root@localhost home]# mysql -u root -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) #出現(xiàn)錯誤(因為密碼不正確) #實現(xiàn)免密登錄 #修改配置文件 vi /etc/my.cnf #在MySQLd的下面加上跳過授權(quán) skip-grant-tables #重啟MySQL的服務(wù) service mysqld restart #繼續(xù)登錄 mysql -u root -p Enter password:(這個地方不用輸入任何東西直接回車) #其實免密登錄時不安全的,所以進(jìn)來之后一定要設(shè)置密碼 #刷新系統(tǒng)權(quán)限表 flush PRIVILEGES; #重新設(shè)置密碼 #alter user 'root'@'localhost' identified by '123456'; SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456'); #刷新系統(tǒng)權(quán)限表 flush PRIVILEGES; #記得添加一個數(shù)據(jù)庫 mysql> create database youruike; Query OK, 1 row affected (0.00 sec)mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | youruike | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) #退出MySQL mysql> exit;

實現(xiàn)MySQL的主從復(fù)制

#先配置主服務(wù)器 #修改配置文件粘貼在放在[mysqld]下面的 vi /etc/my.cnf #添加以下的內(nèi)容 #日志文件的名字 log_bin=master-a-bin #日志文件的格式 binlog-format=ROW #服務(wù)器的id(zk的集群),一定要是唯一的 server-id=1 #對應(yīng)需要實現(xiàn)主從復(fù)制的數(shù)據(jù)庫 binlog_do_db=youruike#添加完之后需要登錄主服務(wù)器給從服務(wù)器授權(quán) 就是允許從服務(wù)器讀取主服務(wù)器數(shù)據(jù)庫 grant replication slave on *.* to 'root'@'192.168.126.%' identified by '123456'; #刷新系統(tǒng)權(quán)限表 flush PRIVILEGES;

從服務(wù)器的配置

#修改配置文件 vi /etc/my.cnf #添加以下的內(nèi)容 #日志文件的名字 log_bin=master-a-bin #日志文件的格式 binlog-format=ROW #服務(wù)器的id(zk的集群),一定要是唯一的 server-id=2 #雙主互相備份(表示從服務(wù)器可能是另外一臺服務(wù)器的主服務(wù)器) #log-slave-updates=true

設(shè)置并驗證主從復(fù)制

#重啟主服務(wù)器和從服務(wù)器 service mysqld restart #登錄主服務(wù)器 mysql -u root -p #查看主服務(wù)器的狀態(tài) mysql> show master status; +---------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------------+----------+--------------+------------------+-------------------+ | master-a-bin.000001 | 154 | youruike | | | +---------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec) #解釋對應(yīng)的一些名詞 #File 生成的日志文件名 #Position 文件名所處的一個位置(偏移量) #Binlog_Do_DB需要實現(xiàn)主從復(fù)制的數(shù)據(jù)庫 #啟動服務(wù) systemctl mysqld restart#登錄從服務(wù)器 #設(shè)置從服務(wù)器如何找到主服務(wù)器 #設(shè)置主從復(fù)制的日志和偏移量 change master to master_host='192.168.126.165',master_port=3306,master_user='root',master_password='123456',master_log_file='master-a-bin.000001',master_log_pos=154;#啟動slave的數(shù)據(jù)同步 start slave; #停止slave的數(shù)據(jù)同步 stop slave; #查看salve的配置信息 show slave status\G;#這邊還正在連接中 mysql> show slave status\G; *************************** 1. row ***************************Slave_IO_State: Connecting to masterMaster_Host: 192.168.126.138Master_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-a-bin.000001Read_Master_Log_Pos: 154Relay_Log_File: localhost-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: master-a-bin.000001Slave_IO_Running: ConnectingSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 154Relay_Log_Space: 154Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 2003Last_IO_Error: error connecting to master 'root@192.168.126.138:3306' - retry-time: 60 retries: 1Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0Master_UUID: Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: 191119 20:48:42Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specified #需要解決正在連接中的問題 #使用navicat客戶端進(jìn)行連接,發(fā)現(xiàn)連接不上 #1.防火墻的問題 2.端口未開放 3.未授權(quán) #可以開放端口3306 firewall-cmd --zone=public --add-port=3306/tcp --permanent #重新加載防火墻 firewall-cmd --reload #還是沒有將權(quán)限授予客戶端 #with grant option 不僅僅是授予增刪改查的權(quán)限,還授予權(quán)限的權(quán)限 grant all privileges on *.* to root@'%' identified by '123456' with grant option; #授權(quán)報錯 mysql> grant all privileges on *.* to root@'%' identified by '123456' with grant option; ERROR 1819 (HY000): Unknown error 1819 mysql> select @@validate_password_policy; +----------------------------+ | @@validate_password_policy | +----------------------------+ | MEDIUM | +----------------------------+ 1 row in set (0.00 sec)mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | +--------------------------------------+--------+ 7 rows in set (0.00 sec)mysql> set global validate_password_policy=0; Query OK, 0 rows affected (0.00 sec)mysql> set global validate_password_mixed_case_count=0; Query OK, 0 rows affected (0.00 sec)mysql> set global validate_password_number_count=3; Query OK, 0 rows affected (0.00 sec)mysql> set global validate_password_special_char_count=0; Query OK, 0 rows affected (0.00 sec)mysql> set global validate_password_length=3; Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 3 | | validate_password_mixed_case_count | 0 | | validate_password_number_count | 3 | | validate_password_policy | LOW | | validate_password_special_char_count | 0 | +--------------------------------------+-------+ 7 rows in set (0.00 sec)mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456'); Query OK, 0 rows affected (0.00 sec)mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)mysql> grant all privileges on *.* to root@'%' identified by '123456' with grant option; Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)#重啟從服務(wù)器并且查看狀態(tài) #表示成功Slave_IO_Running: Yes Slave_SQL_Running: Yes mysql> stop slave; Query OK, 0 rows affected (0.00 sec)mysql> start slave; Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G; *************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.126.138Master_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-a-bin.000001Read_Master_Log_Pos: 1000Relay_Log_File: localhost-relay-bin.000003Relay_Log_Pos: 323Relay_Master_Log_File: master-a-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1000Relay_Log_Space: 1549Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 0f900ff3-0ac6-11ea-aac7-000c299a89e6Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specified

測試是否成功

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | youruike | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)mysql> use youruike; Database changed mysql> show tables; Empty set (0.00 sec)mysql> create table user(id int primary key auto_increment,name varchar(20) not null)charset='utf8'; Query OK, 0 rows affected (0.12 sec)mysql> show tables; +---------------+ | Tables_in_youruike | +---------------+ | user | +---------------+ 1 row in set (0.00 sec)mysql> insert into user values(null,'a'); Query OK, 1 row affected (0.01 sec)mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec)mysql> insert into user values(null,'b'); Query OK, 1 row affected (0.01 sec)mysql> insert into user values(null,'c'); Query OK, 1 row affected (0.00 sec)mysql> delete from user where id = 3; Query OK, 1 row affected (0.00 sec)mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec)

讀寫分離

要想實現(xiàn)讀寫分離,一定要是基于主從復(fù)制而實現(xiàn)的

系統(tǒng)環(huán)境

系統(tǒng)環(huán)境:centos7.0 mycat的版本:1.6 MySQL主機:192.168.126.143(mycat也使用這臺主機) MySQL從機:192.168.126.148 mycat的下載地址:http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

安裝mycat

#查詢有沒有安裝mycat find / -name mycat #因為mycat是基于java語言編寫的,所以一定要配置jdk的環(huán)境 #解壓jdk tar -zxvf jdk-8u221-linux-x64.tar.gz #配置環(huán)境變量 [root@localhost home]# ls ange jdk1.8.0_221 jdk-8u221-linux-x64.tar.gz [root@localhost home]# mv jdk1.8.0_221/ jdk8 [root@localhost home]# mv jdk8 /usr/ [root@localhost home]# ls ange jdk-8u221-linux-x64.tar.gz [root@localhost home]# cd /usr/ [root@localhost usr]# ls bin etc games include jdk8 lib lib64 libexec local sbin share src tmp [root@localhost usr]# vi /etc/profile #加到最末尾 JAVA_HOME=/usr/jdk8 CLASSPATH=%JAVA_HOME%/lib:%JAVA_HOME%/jre/lib PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin export PATH CLASSPATH JAVA_HOME [root@localhost usr]# source /etc/profile [root@localhost usr]# java -version java version "1.8.0_221" Java(TM) SE Runtime Environment (build 1.8.0_221-b11) Java HotSpot(TM) 64-Bit Server VM (build 25.221-b11, mixed mode) [root@localhost usr]# #安裝mycat #報錯 [root@localhost usr]# wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz-bash: wget: 未找到命令 #安裝wget yum install wget #繼續(xù)執(zhí)行下載命令 wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz #加壓下載好的壓縮文件 tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz #mycat的目錄結(jié)構(gòu) -lib jar包存放的目錄 -conf-schema.xml-server.xml-rule.xml-log4j2.xml -logs-mycat.log-wrapper.log -bin-mycat.shlib jar包 schema.xml主配置文件 server.xml服務(wù)配置文件 rule.xml分片的配置文件 log.xml日志配置文件 mycat.log啟動日志文件 wrapper.log運行配置日志文件 mycat.sh啟動的命令

配置mycat

先配置server.xml

<user name="root"><property name="password">123456</property><property name="schemas">TESTDB</property> </user> <user name="user"><property name="password">123456</property><property name="schemas">TESTDB</property><property name="readOnly">true</property> </user>

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="dn1"></schema><dataNode name="dn1" dataHost="localhost1" database="youruike" /><dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="hostM1" url="127.0.0.1:3306" user="root"password="123456"><readHost host="hostS2" url="192.168.126.148:3306" user="root" password="123456" /></writeHost></dataHost> </mycat:schema>

啟動mycat

[root@localhost bin]# ./mycat start console Starting Mycat-server... [root@localhost bin]# ./mycat status console Mycat-server is running (11067). [root@localhost bin]# netstat -ntlp -bash: netstat: 未找到命令 [root@localhost bin]# yum -y install net-tools [root@localhost bin]# netstat -ntlp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 11069/java tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1030/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1188/master tcp6 0 0 :::1984 :::* LISTEN 11069/java tcp6 0 0 :::8066 :::* LISTEN 11069/java tcp6 0 0 :::9066 :::* LISTEN 11069/java tcp6 0 0 :::3306 :::* LISTEN 1742/mysqld tcp6 0 0 :::39666 :::* LISTEN 11069/java tcp6 0 0 :::22 :::* LISTEN 1030/sshd tcp6 0 0 :::41304 :::* LISTEN 11069/java tcp6 0 0 ::1:25 :::* LISTEN 1188/master

登錄mycat查看配置內(nèi)容

#登錄mycat的管理端口 mysql -uroot -p123456 -h127.0.0.1 -P9066 #查看幫助手冊 show @@help; #查看讀寫分離情況 show @@datasource; #查看心跳檢測 show @@heartbeat;RS_CODE 狀態(tài):OK_STATUS = 1;正常狀態(tài)ERROR_STATUS = -1; 連接出錯TIMEOUT_STATUS = -2; 連接超時INIT_STATUS = 0; 初始化狀態(tài) #連接數(shù)據(jù)端口(登錄mycat的主機，mycat在192.168.126.161) mysql -uroot -p123456 -h127.0.0.1 -P8066

測試讀寫分離情況

#修改日志打印情況 <Loggers><asyncRoot level="debug" includeLocation="true"><AppenderRef ref="Console" /><AppenderRef ref="RollingFile"/></asyncRoot> </Loggers>#查看日志情況 tail -f wrapper.log

分庫分表

為什么要分庫分表（設(shè)計高并發(fā)系統(tǒng)的時候，數(shù)據(jù)庫層面該如何設(shè)計）？用過哪些分庫分表中間件？不同的分庫分表中間件都有什么優(yōu)點和缺點？你們具體是如何對數(shù)據(jù)庫如何進(jìn)行垂直拆分或水平拆分的？

為什么要分庫分表？（設(shè)計高并發(fā)系統(tǒng)的時候，數(shù)據(jù)庫層面該如何設(shè)計？）

說白了，分庫分表是兩回事兒，大家可別搞混了，可能是光分庫不分表，也可能是光分表不分庫，都有可能。給大家拋出來一個場景。

假如我們現(xiàn)在是一個小創(chuàng)業(yè)公司（或者是一個 BAT 公司剛興起的一個新部門），現(xiàn)在注冊用戶就 20 萬，每天活躍用戶就 1 萬，每天單表數(shù)據(jù)量就 1000，然后高峰期每秒鐘并發(fā)請求最多就 10。天，就這種系統(tǒng)，隨便找一個有幾年工作經(jīng)驗的，然后帶幾個剛培訓(xùn)出來的，隨便干干都可以。

結(jié)果沒想到我們運氣居然這么好，碰上個 CEO 帶著我們走上了康莊大道，業(yè)務(wù)發(fā)展迅猛，過了幾個月，注冊用戶數(shù)達(dá)到了 2000 萬！每天活躍用戶數(shù) 100 萬！每天單表數(shù)據(jù)量 10 萬條！高峰期每秒最大請求達(dá)到 1000！同時公司還順帶著融資了兩輪，進(jìn)賬了幾個億人民幣啊！公司估值達(dá)到了驚人的幾億美金！這是小獨角獸的節(jié)奏！

好吧，沒事，現(xiàn)在大家感覺壓力已經(jīng)有點大了，為啥呢？因為每天多 10 萬條數(shù)據(jù)，一個月就多 300 萬條數(shù)據(jù)，現(xiàn)在咱們單表已經(jīng)幾百萬數(shù)據(jù)了，馬上就破千萬了。但是勉強還能撐著。高峰期請求現(xiàn)在是 1000，咱們線上部署了幾臺機器，負(fù)載均衡搞了一下，數(shù)據(jù)庫撐 1000QPS 也還湊合。但是大家現(xiàn)在開始感覺有點擔(dān)心了，接下來咋整呢……

再接下來幾個月，我的天，CEO 太牛逼了，公司用戶數(shù)已經(jīng)達(dá)到 1 億，公司繼續(xù)融資幾十億人民幣啊！公司估值達(dá)到了驚人的幾十億美金，成為了國內(nèi)今年最牛逼的明星創(chuàng)業(yè)公司！天，我們太幸運了。

但是我們同時也是不幸的，因為此時每天活躍用戶數(shù)上千萬，每天單表新增數(shù)據(jù)多達(dá) 50 萬，目前一個表總數(shù)據(jù)量都已經(jīng)達(dá)到了兩三千萬了！扛不住啊！數(shù)據(jù)庫磁盤容量不斷消耗掉！高峰期并發(fā)達(dá)到驚人的 5000~8000 ！別開玩笑了，哥。我跟你保證，你的系統(tǒng)支撐不到現(xiàn)在，已經(jīng)掛掉了！

好吧，所以你看到這里差不多就理解分庫分表是怎么回事兒了，實際上這是跟著你的公司業(yè)務(wù)發(fā)展走的，你公司業(yè)務(wù)發(fā)展越好，用戶就越多，數(shù)據(jù)量越大，請求量越大，那你單個數(shù)據(jù)庫一定扛不住。

分表

比如你單表都幾千萬數(shù)據(jù)了，你確定你能扛住么？絕對不行，單表數(shù)據(jù)量太大，會極大影響你的 sql 執(zhí)行的性能，到了后面你的 sql 可能就跑的很慢了。一般來說，就以我的經(jīng)驗來看，單表到幾百萬的時候，性能就會相對差一些了，你就得分表了。

分表是啥意思？就是把一個表的數(shù)據(jù)放到多個表中，然后查詢的時候你就查一個表。比如按照用戶 id 來分表，將一個用戶的數(shù)據(jù)就放在一個表中。然后操作的時候你對一個用戶就操作那個表就好了。這樣可以控制每個表的數(shù)據(jù)量在可控的范圍內(nèi)，比如每個表就固定在 200 萬以內(nèi)。

分庫

分庫是啥意思？就是你一個庫一般我們經(jīng)驗而言，最多支撐到并發(fā) 2000，一定要擴容了，而且一個健康的單庫并發(fā)值你最好保持在每秒 1000 左右，不要太大。那么你可以將一個庫的數(shù)據(jù)拆分到多個庫中，訪問的時候就訪問一個庫好了。

#分庫分表前分庫分表后


并發(fā)支撐情況	MySQL 單機部署，扛不住高并	MySQL從單機到多機，能承受的并發(fā)增加了多倍
發(fā)


磁盤使用情況	MySQL 單機磁盤容量幾乎撐滿	拆分為多個庫，數(shù)據(jù)庫服務(wù)器磁盤使用率大大降低

SQL 執(zhí)行性能	單表數(shù)據(jù)量太大，SQL 越跑越慢	單表數(shù)據(jù)量減少，SQL 執(zhí)行效率明顯提升

如何對數(shù)據(jù)庫如何進(jìn)行垂直拆分或水平拆分的？

水平拆分的意思，就是把一個表的數(shù)據(jù)給弄到多個庫的多個表里去，但是每個庫的表結(jié)構(gòu)都一樣，只不過每個庫表放的數(shù)據(jù)是不同的，所有庫表的數(shù)據(jù)加起來就是全部數(shù)據(jù)。水平拆分的意義，就是將數(shù)據(jù)均勻放更多的庫里，然后用多個庫來扛更高的并發(fā)，還有就是用多個庫的存儲容量來進(jìn)行擴容。

垂直拆分的意思，就是把一個有很多字段的表給拆分成多個表，或者是多個庫上去。每個庫表的結(jié)構(gòu)都不一樣，每個庫表都包含部分字段。一般來說，會將較少的訪問頻率很高的字段放到一個表里去，然后將較多的訪問頻率很低的字段放到另外一個表里去。因為數(shù)據(jù)庫是有緩存的，你訪問頻率高的行字段越少，就可以在緩存里緩存更多的行，性能就越好。這個一般在表層面做的較多一些。

這個其實挺常見的，不一定我說，大家很多同學(xué)可能自己都做過，把一個大表拆開，訂單表、訂單支付表、訂單商品表。

還有表層面的拆分，就是分表，將一個表變成 N 個表，就是讓每個表的數(shù)據(jù)量控制在一定范圍內(nèi)，保證 SQL 的性能。否則單表數(shù)據(jù)量越大，SQL 性能就越差。一般是 200 萬行左右，不要太多，但是也得看具體你怎么操作，也可能是 500 萬，或者是 100 萬。你的SQL越復(fù)雜，就最好讓單表行數(shù)越少。

好了，無論分庫還是分表，上面說的那些數(shù)據(jù)庫中間件都是可以支持的。就是基本上那些中間件可以做到你分庫分表之后，中間件可以根據(jù)你指定的某個字段值，比如說 userid，自動路由到對應(yīng)的庫上去，然后再自動路由到對應(yīng)的表里去。

你就得考慮一下，你的項目里該如何分庫分表？一般來說，垂直拆分，你可以在表層面來做，對一些字段特別多的表做一下拆分；水平拆分，你可以說是并發(fā)承載不了，或者是數(shù)據(jù)量太大，容量承載不了，你給拆了，按什么字段來拆，你自己想好；分表，你考慮一下，你如果哪怕是拆到每個庫里去，并發(fā)和容量都o(jì)k了，但是每個庫的表還是太大了，那么你就分表，將這個表分開，保證每個表的數(shù)據(jù)量并不是很大。

而且這兒還有兩種分庫分表的方式：

一種是按照 range 來分，就是每個庫一段連續(xù)的數(shù)據(jù)，這個一般是按比如時間范圍來的，但是這種一般較少用，因為很容易產(chǎn)生熱點問題，大量的流量都打在最新的數(shù)據(jù)上了。
或者是按照某個字段 hash 一下均勻分散，這個較為常用。

range 來分，好處在于說，擴容的時候很簡單，因為你只要預(yù)備好，給每個月都準(zhǔn)備一個庫就可以了，到了一個新的月份的時候，自然而然，就會寫新的庫了；缺點，但是大部分的請求，都是訪問最新的數(shù)據(jù)。實際生產(chǎn)用 range，要看場景。

hash 分發(fā)，好處在于說，可以平均分配每個庫的數(shù)據(jù)量和請求壓力；壞處在于說擴容起來比較麻煩，會有一個數(shù)據(jù)遷移的過程，之前的數(shù)據(jù)需要重新計算 hash 值重新分配到不同的庫或表。

安裝mycat

#解壓縮 tar xzvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz#需要jdk環(huán)境變量

?配置mycat server.xml

<property name="sequnceHandlerType">0</property><user name="root"><property name="password">123456</property><property name="schemas">mycat_order</property> </user><user name="user"><property name="password">123456</property><property name="schemas">mycat_order</property><property name="readOnly">true</property> </user>

配置 schema.xml

<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"><schema name="mycat_order" checkSQLschema="false" sqlMaxLimit="100"><table name="t_order" dataNode="dn1,dn2" rule="mod-long"><childTable name="t_order_detail" primaryKey="od_id" joinKey="order_id"parentKey="order_id" /></table></schema><dataNode name="dn1" dataHost="host1" database="youruike2" /><dataNode name="dn2" dataHost="host2" database="youruike2" /><dataHost name="host1" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="host1" url="127.0.0.1:3306" user="root"password="123456"/></dataHost><dataHost name="host2" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="host2" url="192.168.126.147:3306" user="root"password="123456"/></dataHost> </mycat:schema>

創(chuàng)建 youruike2數(shù)據(jù)庫和數(shù)據(jù)庫表：t_order、t_order_detail

CREATE TABLE `t_order` (`order_id` BIGINT (20),`user_id` INT (11),`pay_mode` TINYINT (4),`amount` FLOAT,`order_date` datetime,PRIMARY KEY (`order_id`) );CREATE TABLE `t_order_detail` (`od_id` BIGINT (20),`order_id` INT (11),`goods_id` INT (11),`unit_price` FLOAT,`qty` INT (11),PRIMARY KEY (`od_id`) );

配置rule.xml

<tableRule name="mod-long"><rule><columns>order_id</columns><algorithm>mod-long</algorithm></rule> </tableRule><function name="mod-long" class="io.mycat.route.function.PartitionByMod"><property name="count">2</property> </function>

配置sequence_conf.properties

#default global sequence GLOBAL.HISIDS= GLOBAL.MINID=10001 GLOBAL.MAXID=20000 GLOBAL.CURID=10000# self define sequence COMPANY.HISIDS= COMPANY.MINID=1001 COMPANY.MAXID=2000 COMPANY.CURID=1000CUSTOMER.HISIDS= CUSTOMER.MINID=1001 CUSTOMER.MAXID=2000 CUSTOMER.CURID=1000ORDER.HISIDS= ORDER.MINID=1001 ORDER.MAXID=2000 ORDER.CURID=1000ORDERDETAIL.HISIDS= ORDERDETAIL.MINID=1001 ORDERDETAIL.MAXID=2000 ORDERDETAIL.CURID=1000

啟動并測試

./mycat start

插入測試數(shù)據(jù)

insert into t_order(order_id,user_id,pay_mode,amount) values(next value for MYCATSEQ_ORDER,101,5,11.1);insert into t_order(order_id,user_id,pay_mode,amount) values(next value for MYCATSEQ_ORDER,102,5,22.2);insert into t_order(order_id,user_id,pay_mode,amount) values(next value for MYCATSEQ_ORDER,103,7,33.3);insert into t_order_detail(od_id,order_id,goods_id,unit_price,qty) values(next value for MYCATSEQ_ORDERDETAIL,1001,55,10,20);insert into t_order_detail(od_id,order_id,goods_id,unit_price,qty) values(next value for MYCATSEQ_ORDERDETAIL,1002,66,14,30);insert into t_order_detail(od_id,order_id,goods_id,unit_price,qty) values(next value for MYCATSEQ_ORDERDETAIL,1003,77,14,60);

myCat高可用

我們可以使用 HAProxy + Keepalived 配合兩臺 Mycat 搭起 Mycat 集群，實現(xiàn)高可用性。HAProxy

實現(xiàn)了 MyCat 多節(jié)點的集群高可用和負(fù)載均衡，而 HAProxy 自身的高可用則可以通過 Keepalived 來

實現(xiàn)。

編號角色 IP 地址機器名

1 Mycat1 192.168.140.128 host79.youruike

2 Mycat2 192.168.140.127 host80.youruike

3 HAProxy（master） 192.168.140.126 host81.youruike

4 Keepalived（master） 192.168.140.126 host81.youruike

5 HAProxy（backup） 192.168.140.125 host82.youruike

6 Keepalived（backup） 192.168.140.125 host82.youruike

安裝配置 HAProxy

#1準(zhǔn)備好HAProxy安裝包，傳到/opt目錄下 #2解壓到/usr/local/src tar -zxvf haproxy-1.5.14.tar.gz -C /usr/local/src #3進(jìn)入解壓后的目錄，查看內(nèi)核版本，進(jìn)行編譯 cd /usr/local/src/haproxy-1.5.14/ uname -r make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64 # TARGET=linux310，內(nèi)核版本，使用uname -r查看內(nèi)核，如：3.10.0-514.el7，此時該參數(shù)就為linux310； #ARCH=x86_64，系統(tǒng)位數(shù)； #PREFIX=/usr/local/haprpxy #/usr/local/haprpxy，為haprpxy安裝路徑。 #4編譯完成后，進(jìn)行安裝 make install PREFIX=/usr/local/haproxy #5安裝完成后，創(chuàng)建目錄、創(chuàng)建HAProxy配置文件 mkdir -p /usr/data/haproxy/ vi /usr/local/haproxy/haproxy.conf #6向配置文件中插入以下配置信息,并保存

Mycat之——高可用負(fù)載均衡集群的實現(xiàn)（HAProxy + Keepalived + Mycat）_冰河的專欄-CSDN博客_mycat負(fù)載均衡配置

globallog 127.0.0.1 local0maxconn 4096chroot /usr/local/haproxypidfile /usr/data/haproxy/haproxy.piduid 99gid 99daemon#debug#quiet defaultslog globalmode tcpoption abortoncloseoption redispatchretries 3maxconn 2000timeout connect 5000timeout client 50000timeout server 50000 listen proxy_statusbind :48066mode tcpbalance roundrobinserver mycat_1 192.168.126.161:8066 check inter 10sserver mycat_2 192.168.126.171:8066 check inter 10s frontend admin_stats bind :7777mode httpstats enableoption httplogmaxconn 10stats refresh 30sstats uri /adminstats auth admin:123123stats hide-versionstats admin if TRUE

啟動驗證

#1啟動HAProxy /usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf #2查看HAProxy進(jìn)程 ps -ef|grep haproxy #3打開瀏覽器訪問 http://192.168.126.172:7777/admin #在彈出框輸入用戶名：admin密碼：123123 #如果Mycat主備機均已啟動，則可以看到綠色說明成功 #4驗證負(fù)載均衡，通過HAProxy訪問Mycat mysql -uroot -p123456 -h 127.0.0.1 -P 48066

配置 Keepalived

#1準(zhǔn)備好Keepalived安裝包，傳到/opt目錄下 #2解壓到/usr/local/src tar -zxvf keepalived-1.4.2.tar.gz -C /usr/local/src #3安裝依賴插件 yum install -y gcc openssl-devel popt-devel #3進(jìn)入解壓后的目錄，進(jìn)行配置，進(jìn)行編譯 cd /usr/local/src/keepalived-1.4.2 ./configure --prefix=/usr/local/keepalived #4進(jìn)行編譯，完成后進(jìn)行安裝 make && make install #5運行前配置 cp /usr/local/src/keepalived-1.4.2/keepalived/etc/init.d/keepalived /etc/init.d/ mkdir /etc/keepalived cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ cp /usr/local/src/keepalived-1.4.2/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ #6修改配置文件 vi /etc/keepalived/keepalived.conf #修改內(nèi)容如下 ! Configuration File for keepalived global_defs {notification_email {xlcocoon@foxmail.com}notification_email_from keepalived@showjoy.comsmtp_server 127.0.0.1smtp_connect_timeout 30router_id LVS_DEVELvrrp_skip_check_adv_addrvrrp_garp_interval 0vrrp_gna_interval 0 } vrrp_instance VI_1 {#主機配MASTER，備機配BACKUPstate MASTER#所在機器網(wǎng)卡interface ens33virtual_router_id 51#數(shù)值越大優(yōu)先級越高priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {#虛擬IP192.168.140.200} } virtual_server 192.168.140.200 48066 {delay_loop 6lb_algo rrlb_kind NATpersistence_timeout 50protocol TCPreal_server 192.168.126.172 48066 {weight 1TCP_CHECK {connect_timeout 3retry 3delay_before_retry 3} }real_server 192.168.126.173 48066 {weight 1TCP_CHECK {connect_timeout 3nb_get_retry 3delay_before_retry 3} } }

啟動驗證

#啟動Keepalived service keepalived start #查看啟動情況 ps -ef | grep keepalived #登錄驗證 mysql -uroot -p123456 -h 192.168.140.200 -P 48066

測試高可用

#1關(guān)閉mycat #2通過虛擬ip查詢數(shù)據(jù) mysql -uroot -p123456 -h 192.168.140.200 -P 48066

總結(jié)

以上是生活随笔為你收集整理的MySQL搭建主从复制读写分离分库分表 MyCat高可用的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。