一、安裝Percona數(shù)據(jù)庫(kù)
1. 離線安裝Percona
-
進(jìn)入RPM安裝文件目錄，執(zhí)行下面的腳本
yum localinstall *.rpm -
管理MySQL服務(wù)
systemctl start mysqld systemctl stop mysqld systemctl restart mysqld
2. 在線安裝Percona
-
使用yum命令安裝
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm yum install Percona-Server-server-57 -
管理MySQL服務(wù)
service mysql start service mysql stop service mysql restart
3. 開放防火墻端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
4. 修改MySQL配置文件
vi /etc/my.cnf
[mysqld]
character_set_server = utf8
bind-address = 0.0.0.0
#跳過DNS解析
skip-name-resolve
service mysql restart
5. 禁止開機(jī)啟動(dòng)MySQL
chkconfig mysqld off
6. 初始化MySQL數(shù)據(jù)庫(kù)
-
查看MySQL初始密碼
cat /var/log/mysqld.log | grep "A temporary password" -
修改MySQL密碼
mysql_secure_installation -
創(chuàng)建遠(yuǎn)程管理員賬戶
mysql -u root -pCREATE USER 'admin'@'%' IDENTIFIED BY 'Abc_123456'; GRANT all privileges ON *.* TO 'admin'@'%'; FLUSH PRIVILEGES;
二、創(chuàng)建PXC集群
1. 刪除MariaDB程序包
yum -y remove mari*
2. 開放防火墻端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=4444/tcp --permanent
firewall-cmd --zone=public --add-port=4567/tcp --permanent
firewall-cmd --zone=public --add-port=4568/tcp --permanent
3. 關(guān)閉SELINUX
vi /etc/selinux/config
把SELINUX屬性值設(shè)置成disabled
reboot
4. 離線安裝PXC
-
進(jìn)入RPM文件目錄，執(zhí)行安裝命令
yum localinstall *.rpm 參考第一章內(nèi)容，修改MySQL配置文件、創(chuàng)建賬戶等操作
5. 創(chuàng)建PXC集群
停止MySQL服務(wù)
-
修改每個(gè)PXC節(jié)點(diǎn)的/etc/my.cnf文件（在不同節(jié)點(diǎn)上，注意調(diào)整文件內(nèi)容）
server-id=1 #PXC集群中MySQL實(shí)例的唯一ID，不能重復(fù)，必須是數(shù)字 wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_name=pxc-cluster #PXC集群的名稱 wsrep_cluster_address=gcomm://192.168.99.151,192.168.99.159,192.168.99.215 wsrep_node_name=pxc1 #當(dāng)前節(jié)點(diǎn)的名稱 wsrep_node_address=192.168.99.151 #當(dāng)前節(jié)點(diǎn)的IP wsrep_sst_method=xtrabackup-v2 #同步方法（mysqldump、rsync、xtrabackup） wsrep_sst_auth= admin:Abc_123456 #同步使用的帳戶 pxc_strict_mode=ENFORCING #同步嚴(yán)厲模式 binlog_format=ROW #基于ROW復(fù)制（安全可靠） default_storage_engine=InnoDB #默認(rèn)引擎 innodb_autoinc_lock_mode=2 #主鍵自增長(zhǎng)不鎖表 -
主節(jié)點(diǎn)的管理命令（第一個(gè)啟動(dòng)的PXC節(jié)點(diǎn)）
systemctl start mysql@bootstrap.service systemctl stop mysql@bootstrap.service systemctl restart mysql@bootstrap.service -
非主節(jié)點(diǎn)的管理命令（非第一個(gè)啟動(dòng)的PXC節(jié)點(diǎn)）
service start mysql service stop mysql service restart mysql -
查看PXC集群狀態(tài)信息
show status like 'wsrep_cluster%' ; 按照上述配置方法，創(chuàng)建兩組PXC集群
6. PXC節(jié)點(diǎn)啟動(dòng)與關(guān)閉
- 如果最后關(guān)閉的PXC節(jié)點(diǎn)是安全退出的，那么下次啟動(dòng)要最先啟動(dòng)這個(gè)節(jié)點(diǎn)，而且要以主節(jié)點(diǎn)啟動(dòng)
- 如果最后關(guān)閉的PXC節(jié)點(diǎn)不是安全退出的，那么要先修改
/var/lib/mysql/grastate.dat文件，把其中的safe_to_bootstrap屬性值設(shè)置為1，再安裝主節(jié)點(diǎn)啟動(dòng)
三、安裝MyCat
1. JDK安裝與配置
-
安裝JDK
#搜索JDK版本 yum search jdk #安裝JDK1.8開發(fā)版 yum install java-1.8.0-openjdk-devel.x86_64 -
配置環(huán)境變量
#查看JDK安裝路徑 ls -lrt /etc/alternatives/java vi /etc/profile #在文件結(jié)尾加上JDK路徑，例如export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-8.b10.el7_5.x86_64/ source /etc/profile
2. 創(chuàng)建數(shù)據(jù)表
-
在兩組PXC集群中分別創(chuàng)建t_user數(shù)據(jù)表
CREATE TABLE t_user( id INT UNSIGNED PRIMARY KEY, username VARCHAR(200) NOT NULL, password VARCHAR(2000) NOT NULL, tel CHAR(11) NOT NULL, locked TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, INDEX idx_username(username) USING BTREE, UNIQUE INDEX unq_username(username) USING BTREE );
3. MyCat安裝與配置
-
下載MyCat
http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
上傳MyCat壓縮包到虛擬機(jī)
-
安裝unzip程序包，解壓縮MyCat
yum install unzip unzip MyCAT壓縮包名稱 開放防火墻8066和9066端口，關(guān)閉SELINUX
-
修改MyCat的bin目錄中所有.sh文件的權(quán)限
chmod -R 777 ./*.sh -
MyCat啟動(dòng)與關(guān)閉
#cd MyCat的bin目錄 ./startup_nowrap.sh #啟動(dòng)MyCat ps -aux #查看系統(tǒng)進(jìn)程 kill -9 MyCat進(jìn)程編號(hào) -
修改server.xml文件，設(shè)置MyCat帳戶和虛擬邏輯庫(kù)
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mycat:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <system> <property name="nonePasswordLogin">0</property> <property name="useHandshakeV10">1</property> <property name="useSqlStat">0</property> <property name="useGlobleTableCheck">0</property> <property name="sequnceHandlerType">2</property> <property name="subqueryRelationshipCheck">false</property> <property name="processorBufferPoolType">0</property> <property name="handleDistributedTransactions">0</property> <property name="useOffHeapForMerge">1</property> <property name="memoryPageSize">64k</property> <property name="spillsFileBufferSize">1k</property> <property name="useStreamOutput">0</property> <property name="systemReserveMemorySize">384m</property> <property name="useZKSwitch">false</property> </system> <!--這里是設(shè)置的admin用戶和虛擬邏輯庫(kù)--> <user name="admin" defaultAccount="true"> <property name="password">Abc_123456</property> <property name="schemas">test</property> </user> </mycat:server>
-
修改schema.xml文件，設(shè)置數(shù)據(jù)庫(kù)連接和虛擬數(shù)據(jù)表
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!--配置數(shù)據(jù)表--> <schema name="test" checkSQLschema="false" sqlMaxLimit="100"> <table name="t_user" dataNode="dn1,dn2" rule="mod-long" /> </schema> <!--配置分片關(guān)系--> <dataNode name="dn1" dataHost="cluster1" database="test" /> <dataNode name="dn2" dataHost="cluster2" database="test" /> <!--配置連接信息--> <dataHost name="cluster1" maxCon="1000" minCon="10" balance="2" writeType="1" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="W1" url="192.168.99.151:3306" user="admin" password="Abc_123456"> <readHost host="W1R1" url="192.168.99.159:3306" user="admin" password="Abc_123456" /> <readHost host="W1R2" url="192.168.99.215:3306" user="admin" password="Abc_123456" /> </writeHost> <writeHost host="W2" url="192.168.99.159:3306" user="admin" password="Abc_123456"> <readHost host="W2R1" url="192.168.99.151:3306" user="admin" password="Abc_123456" /> <readHost host="W2R2" url="192.168.99.215:3306" user="admin" password="Abc_123456" /> </writeHost> </dataHost> <dataHost name="cluster2" maxCon="1000" minCon="10" balance="2" writeType="1" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="W1" url="192.168.99.121:3306" user="admin" password="Abc_123456"> <readHost host="W1R1" url="192.168.99.122:3306" user="admin" password="Abc_123456" /> <readHost host="W1R2" url="192.168.99.123:3306" user="admin" password="Abc_123456" /> </writeHost> <writeHost host="W2" url="192.168.99.122:3306" user="admin" password="Abc_123456"> <readHost host="W2R1" url="192.168.99.121:3306" user="admin" password="Abc_123456" /> <readHost host="W2R2" url="192.168.99.123:3306" user="admin" password="Abc_123456" /> </writeHost> </dataHost> </mycat:schema> -
修改rule.xml文件，把mod-long的count值修改成2
<function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <property name="count">2</property> </function> 重啟MyCat
-
向t_user表寫入數(shù)據(jù)，感受數(shù)據(jù)的切分
USE test; #第一條記錄被切分到第二個(gè)分片 INSERT INTO t_user(id,username,password,tel,locked) VALUES(1,"A",HEX(AES_ENCRYPT('123456','HelloWorld'))); #第二條記錄被切分到第一個(gè)分片 INSERT INTO t_user(id,username,password,tel,locked) VALUES(2,"B",HEX(AES_ENCRYPT('123456','HelloWorld')));
4. 配置父子表
-
在conf目錄下創(chuàng)建
customer-hash-int文件，內(nèi)容如下：101=0 102=0 103=0 104=1 105=1 106=1 -
在rule.xml文件中加入自定義<function>和<tableRule>
<function name="customer-hash-int" class="io.mycat.route.function.PartitionByFileMap"> <property name="mapFile">customer-hash-int.txt</property> </function><tableRule name="sharding-customer"> <rule> <columns>sharding_id</columns> <algorithm>customer-hash-int</algorithm> </rule> </tableRule> -
修改schema.xml文件，添加父子表定義
<table name="t_customer" dataNode="dn1,dn2" rule="sharding-customer"> <childTable name="t_orders" primaryKey="ID" joinKey="customer_id" parentKey="id"/> </table> -
在MyCat上執(zhí)行如下SQL：
USE test; CREATE TABLE t_customer( id INT UNSIGNED PRIMARY KEY, username VARCHAR(200) NOT NULL, sharding_id INT NOT NULL ); CREATE TABLE t_orders( id INT UNSIGNED PRIMARY KEY, customer_id INT NOT NULL, datetime TIMESTAMP DEFAULT CURRENT_TIMSTAMP ); 向t_customer表和t_orders表寫入數(shù)據(jù)，查看字表數(shù)據(jù)跟隨父表切分到同一個(gè)分片
5. 創(chuàng)建雙機(jī)熱備的MyCat集群
用兩個(gè)虛擬機(jī)實(shí)例，各自部署MyCat
-
用一個(gè)虛擬機(jī)實(shí)例部署Haproxy
-
安裝Haproxy
yum install -y haproxy -
編輯配置文件
vi /etc/haproxy/haproxy.cfgglobal log 127.0.0.1 local2 chroot /var/lib/haproxy pidfile /var/run/haproxy.pid maxconn 4000 user haproxy group haproxy daemon # turn on stats unix socket stats socket /var/lib/haproxy/stats defaults mode http log global option httplog option dontlognull option http-server-close option forwardfor except 127.0.0.0/8 option redispatch retries 3 timeout http-request 10s timeout queue 1m timeout connect 10s timeout client 1m timeout server 1m timeout http-keep-alive 10s timeout check 10s maxconn 3000 listen admin_stats bind 0.0.0.0:4001 mode http stats uri /dbs stats realm Global\ statistics stats auth admin:abc123456 listen proxy-mysql bind 0.0.0.0:3306 mode tcp balance roundrobin option tcplog #日志格式 server mycat_1 192.168.99.131:3306 check port 8066 maxconn 2000 server mycat_2 192.168.99.132:3306 check port 8066 maxconn 2000 option tcpka #使用keepalive檢測(cè)死鏈 -
啟動(dòng)Haproxy
service haproxy start -
訪問Haproxy監(jiān)控畫面
-
用另外一個(gè)虛擬機(jī)同樣按照上述操作安裝Haproxy
-
在某個(gè)Haproxy虛擬機(jī)實(shí)例上部署Keepalived
-
開啟防火墻的VRRP協(xié)議
#開啟VRRP firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --protocol vrrp -j ACCEPT #應(yīng)用設(shè)置 firewall-cmd --reload -
安裝Keepalived
yum install -y keepalived -
編輯配置文件
vim /etc/keepalived/keepalived.confvrrp_instance VI_1 { state MASTER interface ens33 virtual_router_id 51 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 123456 } virtual_ipaddress { 192.168.99.133 } } -
啟動(dòng)Keepalived
service keepalived start ping 192.168.99.133
-
在另外一個(gè)Haproxy虛擬機(jī)上，按照上述方法部署Keepalived
使用MySQL客戶端連接192.168.99.133，執(zhí)行增刪改查數(shù)據(jù)
四、Sysbench基準(zhǔn)測(cè)試
1. 安裝Sysbench
-
在線安裝
curl -s https://packagecloud.io/install/ repositories/akopytov/sysbench/script.rpm.sh | sudo bashyum -y install sysbench -
本地安裝
-
下載壓縮文件
-
安裝依賴包
yum install -y automake libtool yum install -y mysql-devel -
執(zhí)行安裝
#cd sysbench ./autogen.sh ./configure make make install sysbench --version
-
2. 執(zhí)行測(cè)試
-
準(zhǔn)備測(cè)試庫(kù)
sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.99.131 --mysql-port=3306 --mysql-user=admin --mysql-password=Abc_123456 --oltp-tables-count=10 --oltp-table-size=100000 prepare -
執(zhí)行測(cè)試
sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.99.131 --mysql-port=3306 --mysql-user=admin --mysql-password=Abc_123456 --oltp-test-mode=complex --threads=10 --time=300 --report-interval=10 run >> /home/mysysbench.log -
清理數(shù)據(jù)
sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.99.131 --mysql-port=3306 --mysql-user=admin --mysql-password=Abc_123456 --oltp-tables-count=10 cleanup
五、tpcc-mysql 壓力測(cè)試
1. 準(zhǔn)備工作
-
修改my.cnf配置文件
vi /etc/my.cnfpxc_strict_mode=DISABLED
-
修改某個(gè)Haproxy的配置文件
server mysql_1 192.168.99.151:3306 check port 3306 weight 1 maxconn 2000 server mysql_2 192.168.99.159:3306 check port 3306 weight 1 maxconn 2000 server mysql_3 192.168.99.215:3306 check port 3306 weight 1 maxconn 2000 重新啟動(dòng)Haproxy
-
安裝依賴程序包
yum install -y gcc yum install -y mysql-devel
2. 安裝tpcc-mysql
-
下載壓縮包
https://codeload.github.com/Percona-Lab/tpcc-mysql/zip/master
-
執(zhí)行安裝
#cd tpcc的src目錄 make 執(zhí)行
create_table.sql和add_fkey_idx.sql兩個(gè)文件-
執(zhí)行數(shù)據(jù)初始化
./tpcc_load -h 192.168.99.131 -d tpcc -u admin -p Abc_123456 -w -
執(zhí)行壓力測(cè)試
./tpcc_start -h 192.168.99.131 -d tpcc -u admin -p Abc_123456 -w 1 -c 5 -r 300 -l 600 ->tpcc-output-log
六、導(dǎo)入數(shù)據(jù)
1. 生成1000萬(wàn)條數(shù)據(jù)
import java.io.FileWriter
import java.io.BufferedWriter
class Test {
def static void main(String[] args) {
var writer=new FileWriter("D:/data.txt")
var buff=new BufferedWriter(writer)
for(i:1..10000000){
buff.write(i+",測(cè)試數(shù)據(jù)\n")
}
buff.close
writer.close
}
}
2. 執(zhí)行文件切分
上傳data.txt文件到linux
-
執(zhí)行文件切分
split -l 1000000 -d data.txt
3. 準(zhǔn)備數(shù)據(jù)庫(kù)
每個(gè)PXC分片只開啟一個(gè)節(jié)點(diǎn)
-
修改PXC節(jié)點(diǎn)文件，然后重啟PXC服務(wù)
innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb_buffer_pool_size = 200M -
創(chuàng)建t_test數(shù)據(jù)表
CREATE TABLE t_test( id INT UNSIGNED PRIMARY KEY, name VARCHAR(200) NOT NULL ); -
配置MyCat
<table name="t_test" dataNode="dn1,dn2" rule="mod-long" /><dataHost name="cluster1" maxCon="1000" minCon="10" balance="0" writeType="1" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="W1" url="192.168.99.151:3306" user="admin" password="Abc_123456"/> </dataHost> <dataHost name="cluster2" maxCon="1000" minCon="10" balance="0" writeType="1" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="W1" url="192.168.99.121:3306" user="admin" password="Abc_123456"/> </dataHost>
4. 執(zhí)行Java程序，多線程導(dǎo)入數(shù)據(jù)
import org.eclipse.xtend.lib.annotations.Accessors
import java.io.File
import java.sql.DriverManager
class Task implements Runnable{
@Accessors
File file;
override run() {
var url="jdbc:mysql://192.168.99.131:8066/test"
var username="admin"
var password="Abc_123456"
var con=DriverManager.getConnection(url,username,password)
var sql='''
load data local intfile '/home/data/?file.name?' ignore into table t_test
character set 'utf8'
fields terminated by ',' optionally enclosed by '\"'
lines terminated by '\n' (id,name);
'''
var pst=con.prepareStatement(sql);
pst.execute
con.close
LoadData.updateNum();
}
}
import com.mysql.jdbc.Driver
import java.sql.DriverManager
import java.util.concurrent.LinkedBlockingQueue
import java.util.concurrent.ThreadPoolExecutor
import java.util.concurrent.TimeUnit
import java.io.File
class LoadData {
var static int num=0;
var static int end=0;
var static pool=new ThreadPoolExecutor(1,5,60,TimeUnit.SECONDS,new LinkedBlockingQueue(200))
def static void main(String[] args) {
DriverManager.registerDriver(new Driver)
var folder=new File("/home/data")
var files=folder.listFiles
end=files.length //線程池結(jié)束條件
files.forEach[one|
var task=new Task();
task.file=one;
pool.execute(task)
]
}
synchronized def static updateNum(){
num++;
if(num==end){
pool.shutdown();
println("執(zhí)行結(jié)束")
}
}
}
七、大數(shù)據(jù)歸檔
1. 安裝TokuDB
-
安裝jemlloc
yum install -y jemalloc -
編輯配置文件
vi /etc/my.cnf…… [mysqld_safe] malloc-lib=/usr/lib64/libjemalloc.so.1 …… 重啟MySQL
-
開啟Linux大頁(yè)內(nèi)存
echo never > /sys/kernel/mm/transparent_hugepage/enabled echo never > /sys/kernel/mm/transparent_hugepage/defrag -
安裝TokuDB
yum install -y Percona-Server-tokudb-57.x86_64 ps-admin --enable -uroot -p service mysql restart ps-admin --enable -uroot -p -
查看安裝結(jié)果
show engines ;
2. 配置Replication集群
-
在兩個(gè)TokuDB數(shù)據(jù)庫(kù)上創(chuàng)建用戶
CREATE USER 'backup'@'%' IDENTIFIED BY 'Abc_123456' ;GRANT super, reload, replication slave ON *.* TO 'backup'@'%' ;FLUSH PRIVILEGES ; -
修改兩個(gè)TokuDB的配置文件，如下：
[mysqld] server_id = 101 log_bin = mysql_bin relay_log = relay_bin ……[mysqld] server_id = 102 log_bin = mysql_bin relay_log = relay_bin 重新啟動(dòng)兩個(gè)TokuDB節(jié)點(diǎn)
-
分別在兩個(gè)TokuDB上執(zhí)行下面4句SQL
#關(guān)閉同步服務(wù) stop slave; #設(shè)置同步的Master節(jié)點(diǎn) change master to master_host="192.168.99.155",master_port=3306,master_user="backup", master_password="Abc_123456"; #啟動(dòng)同步服務(wù) start slave; #查看同步狀態(tài) show slave status;#關(guān)閉同步服務(wù) stop slave; #設(shè)置同步的Master節(jié)點(diǎn) change master to master_host="192.168.99.102",master_port=3306,master_user="backup", master_password="Abc_123456"; #啟動(dòng)同步服務(wù) start slave; #查看同步狀態(tài) show slave status;
3. 創(chuàng)建歸檔表
CREATE TABLE t_purchase (
id INT UNSIGNED PRIMARY KEY,
purchase_price DECIMAL(10,2) NOT NULL,
purchase_num INT UNSIGNED NOT NULL,
purchase_sum DECIMAL (10,2) NOT NULL,
purchase_buyer INT UNSIGNED NOT NULL,
purchase_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
company_id INT UNSIGNED NOT NULL,
goods_id INT UNSIGNED NOT NULL,
KEY idx_company_id(company_id),
KEY idx_goods_id(goods_id)
)engine=TokuDB;
4. 配置Haproxy+Keepalived雙機(jī)熱備
-
在兩個(gè)節(jié)點(diǎn)上安裝Haproxy
yum install -y haproxy -
修改配置文件
vi /etc/haproxy/haproxy.cfgglobal log 127.0.0.1 local2 chroot /var/lib/haproxy pidfile /var/run/haproxy.pid maxconn 4000 user haproxy group haproxy daemon # turn on stats unix socket stats socket /var/lib/haproxy/stats defaults mode http log global option httplog option dontlognull option http-server-close option forwardfor except 127.0.0.0/8 option redispatch retries 3 timeout http-request 10s timeout queue 1m timeout connect 10s timeout client 1m timeout server 1m timeout http-keep-alive 10s timeout check 10s maxconn 3000 listen admin_stats bind 0.0.0.0:4001 mode http stats uri /dbs stats realm Global\ statistics stats auth admin:abc123456 listen proxy-mysql bind 0.0.0.0:4002 mode tcp balance roundrobin option tcplog #日志格式 server backup_1 192.168.99.102:3306 check port 3306 maxconn 2000 server backup_2 192.168.99.155:3306 check port 3306 maxconn 2000 option tcpka #使用keepalive檢測(cè)死鏈 重啟Haproxy
-
開啟防火墻的VRRP協(xié)議
firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --protocol vrrp -j ACCEPTfirewall-cmd --reload -
在兩個(gè)節(jié)點(diǎn)上安裝Keepalived
yum install -y keepalived -
編輯Keepalived配置文件
vim /etc/keepalived/keepalived.confvrrp_instance VI_1 { state MASTER interface ens33 virtual_router_id 51 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 123456 } virtual_ipaddress { 192.168.99.211 } } 重啟Keepalived
5. 準(zhǔn)備歸檔數(shù)據(jù)
-
在兩個(gè)PXC分片上創(chuàng)建進(jìn)貨表
CREATE TABLE t_purchase ( id INT UNSIGNED PRIMARY KEY, purchase_price DECIMAL(10,2) NOT NULL, purchase_num INT UNSIGNED NOT NULL, purchase_sum DECIMAL (10,2) NOT NULL, purchase_buyer INT UNSIGNED NOT NULL, purchase_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, company_id INT UNSIGNED NOT NULL, goods_id INT UNSIGNED NOT NULL, KEY idx_company_id(company_id), KEY idx_goods_id(goods_id) ) -
配置MyCat的schema.xml文件，并重啟MyCat
<table name="t_purchase" dataNode="dn1,dn2" rule="mod-long" />
6. 執(zhí)行數(shù)據(jù)歸檔
-
安裝pt-archiver
yum install percona-toolkit pt-archiver --version pt-archiver --help -
執(zhí)行數(shù)據(jù)歸檔
pt-archiver --source h=192.168.99.102,P=8066,u=admin,p=Abc_123456,D=test,t=t_purchase --dest h=192.168.99.102,P=3306,u=admin,p=Abc_123456,D=test,t=t_purchase --no-check-charset --where 'purchase_date<"2018-09"' --progress 5000 --bulk-delete --bulk-insert --limit=10000 --statistics
本篇文章由一文多發(fā)平臺(tái)ArtiPub自動(dòng)發(fā)布
