。需求：公司某軟件使用phxsql已經(jīng)部署在3個機房，現(xiàn)需要在新的機房增加一個phxsql節(jié)點，以下為操作過程，因為保護隱私把4臺IP用
1.1.1.1
2.2.2.2
3.3.3.3
新的ip為4.4.4.4
。安裝Phxsql需要Python2.7版本，Centos默認為2.6版本，需要進行升級
cd /opt wget --no-check-certificate https://www.python.org/ftp/python/2.7.6/Python-2.7.6.tar.xz tar xf Python-2.7.6.tar.xz cd Python-2.7.6 ./configure --prefix=/usr/local make && make install。Phxsql需要升級glibc包，Centos6.5默認為2.12版本，升級為2.20版本。因為glic是操作系統(tǒng)底層庫，謹慎操作
glibc-common-2.12-1.149.el6_6.5.x86_64 glibc-2.12-1.149.el6_6.5.i686 glibc-2.12-1.149.el6_6.5.x86_64 glibc-headers-2.12-1.149.el6_6.5.x86_64 glibc-devel-2.12-1.149.el6_6.5.x86_64升級操作
rpm -Uvh --aid --force --nodeps glibc-headers-2.20-8.fc21.x86_64.rpm rpm -Uvh --aid --force --nodeps glibc-devel-2.20-8.fc21.x86_64.rpm rpm -Uvh --aid --force --nodeps glibc-2.20-8.fc21.x86_64.rpm rpm -Uvh --aid --force --nodeps glibc-common-2.20-8.fc21.x86_64.rpm。四個phxsql節(jié)點的服務器，防火墻需要開放如下幾個端口
iptables -A INPUT -p tcp -m multiport --dports 11111,54321,54322 -s 4.4.4.4 -j ACCEPT。在phxsql集群里任意一個節(jié)點，在新節(jié)點還沒有開始安裝部署phxsql時，把新節(jié)點IP添加到集群里面
phxbinlogsvr_tools_phxrpc -f AddMember -h2.1.1.1 -p17000 -m 4.4.4.4加后使用命令行檢查
phxbinlogsvr_tools_phxrpc -f GetMemberList -h2.1.1.1 -p 17000。設置phxsql的數(shù)據(jù)目錄，官網(wǎng)教程定義目錄為/tmp/data，這里改為/data1/
cd /data1/phxsql/tools/ mkdir -p /data1/phxsql/tmp/data/log chown -R mysql:mysql /data1/phxsql/tmp/data/log python2.7 install.py -i"4.4.4.4" -p 54321 -g 6000 -y 11111 -P 17000 -a 8001 -f /data1/phxsql/tmp/data/安裝過程會出現(xiàn)如下信息
Namespace(agent_port=6000, base_dir='/data1/phxsql/', data_dir='/data1/phxsql/tmp/data/', inner_ip='183.36.122.100', ip_list='4.4.4.4', module_name='test', mysql_port=11111, paxos_port=8001, phxbinlogsvr_port=17000, phxsqlproxy_port=54321, process_name='all', skip_data=0) kill all binaries success.... mkdir: cannot create directory `/data1/phxsql/etc': File exists generate all configs success.... mkdir: cannot create directory `/data1/phxsql/tmp/data/log': File exists /data1/phxsql/tmp/data/phxbinlogsvr skip data 0 mkdir: cannot create directory `/data1/phxsql/tmp/data/log': File exists /data1/phxsql/tmp/data/percona.workspace cd /data1/phxsql/percona.src; ./scripts/mysql_install_db --defaults-file=/data1/phxsql/etc/my.cnf --user=mysql WARNING: The host 'centos' could not be looked up with /data1/phxsql/percona.src/bin/resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL daemon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Installing MySQL system tables...2017-05-04 04:07:27 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 2017-05-04 04:07:27 0 [Note] ./bin/mysqld (mysqld 5.6.31-77.0-log) starting as process 55454 ... OK Filling help tables...2017-05-04 04:07:32 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 2017-05-04 04:07:32 0 [Note] ./bin/mysqld (mysqld 5.6.31-77.0-log) starting as process 55496 ... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /data1/phxsql/percona.src/bin/mysqladmin -u root password 'new-password' /data1/phxsql/percona.src/bin/mysqladmin -u root -h centos password 'new-password' Alternatively you can run: /data1/phxsql/percona.src/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; /data1/phxsql/percona.src/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems at https://bugs.launchpad.net/percona-server/+filebug The latest information about Percona Server is available on the web at http://www.percona.com/software/percona-server Support Percona by buying support at http://www.percona.com/products/mysql-support install all success.... nohup: appending output to `nohup.out' nohup: appending output to `nohup.out' WARNING: Logging before InitGoogleLogging() is written to STDERR W0504 04:07:39.378021 55555 phx_glog.cpp:74] GetDefaultPath get debuf path /data1/phxsql/etc/ W0504 04:07:39.385524 55555 phx_glog.cpp:74] RealReadFile read path /data1/phxsql/etc/phxsqlproxy.conf done W0504 04:07:39.385609 55555 phx_glog.cpp:74] read plugin config [] W0504 04:07:39.385701 55555 phx_glog.cpp:74] RealReadFile read path /data1/phxsql/etc/phxsqlproxy.conf done W0504 04:07:39.385752 55555 phx_glog.cpp:74] read plugin config [] start master worker finished ... start slave worker finished ... init pid 55580 env 0x7f77780008c0 init pid 55582 env 0x7f777c0008c0 init pid 55583 env 0x7f77700008c0 init pid 55585 env 0x7f77800008c0 init pid 55586 env 0x7f77740008c0 init pid 55587 env 0x7f77840008c0 init pid 55588 env 0x7f77780008c0 init pid 55589 env 0x7f777c0008c0 init pid 55592 env 0x7f77680008c0 init pid 55593 env 0x7f776c0008c0 init pid 55598 env 0x7f77700008c0 init pid 55602 env 0x7f77680008c0 init pid 55600 env 0x7f77600008c0 init pid 55608 env 0x7f77640008c0 init pid 55605 env 0x7f77600008c0。如果安裝成功，如下3個進程會啟動
ps -ef|grep -v grep | grep phxsqlproxy
ps -ef|grep -v grep | grep percona
ps -ef|grep -v grep | grep phxbinlogsvr
[root@centos phxsql]# ps -ef|grep -v grep | grep phxsqlproxy
root 55575 1 10 04:07 pts/1 02:02:57 /data1/phxsql/sbin/phxsqlproxy_phxrpc /data1/phxsql/etc/phxsqlproxy.conf daemon
root 55576 1 10 04:07 pts/1 02:02:33 /data1/phxsql/sbin/phxsqlproxy_phxrpc /data1/phxsql/etc/phxsqlproxy.conf daemon
[root@centos phxsql]# ps -ef|grep -v grep | grep percona ps -ef|grep -v grep | grep phxbinlogsvr mysql 56942 55552 98 04:07 pts/1 19:30:57 /data1/phxsql/sbin/mysqld --defaults-file=/data1/phxsql/etc/my.cnf --basedir=/data1/phxsql/percona.src --datadir=/data1/phxsql/tmp/data/percona.workspace/data --plugin-dir=/data1/phxsql/lib --user=mysql --super-read-only --plugin-load=phxsync_master_phxrpc.so --log-error=/data1/phxsql/tmp/data/percona.workspace/log.err --pid-file=/data1/phxsql/tmp/data/percona.workspace/data/percona.pid --socket=/data1/phxsql/tmp/data/percona.workspace/tmp/percona.sock --port=11111偶爾phxbinlogsvr不會啟動，不過沒有關系，因為如果phxbinlogsvr 啟動了，就需要把它kill掉
killall -9 phxbinlogsvr_phxrpc。添加環(huán)境變量
vi /etc/profile export PATH=/data1/phxsql/sbin:$PATH . /etc/profile。phxsql安裝好了，目前是空的，需要從一個有數(shù)據(jù)的節(jié)點上把數(shù)據(jù)導過來，通常會選擇從點，通過命令查詢哪個是master，然后避開master，我這邊數(shù)據(jù)量是200G，對于導出大量的數(shù)據(jù)會很消耗磁盤資源，需要讓業(yè)務切換到其他點。
phxbinlogsvr_tools_phxrpc -f GetMemberList -h2.1.1.1 -p 17000。使用mysqldump導出數(shù)據(jù)，需要加一個--quick參數(shù)，目的是導出數(shù)據(jù)時不經(jīng)過buffer pool，而是直接從磁盤取數(shù)，然后保存到文件，建議使用腳本，為了知道導數(shù)到底用了多少時間。注意導出的是全量數(shù)據(jù) --all-database，因為phxsql下所有節(jié)點的數(shù)據(jù)量都是一致的，相當于多點冗余
重慶分公司
重慶分公司
function output_data(){
start_date=$(date +%s)
/data1/phxsql/percona.src/bin/mysqldump --quick -uroot -S /data1/phxsql/tmp/data/percona.workspace/tmp/percona.sock --all-databases > phxsql.alldata
end_date=$(date +%s)
backup_time=$(( $end_date - $start_date ))
echo "導出耗時："${backup_time}"秒"
}200G的數(shù)據(jù)，導出文件67G，大概3倍的壓縮，一共2393秒，平均11秒可以導出1G
。一直有個困惑，iostat占用率和負載到底臨界值是多少，才不需要切業(yè)務呢？導數(shù)的時候是否有必要去遷業(yè)務呢？
avg-cpu: %user %nice %system %iowait %steal %idle 2.31 0.00 1.13 1.76 0.00 94.79 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await r_await w_await svctm %util sda 0.00 0.00 0.00 1.00 0.00 8.00 8.00 0.00 3.00 0.00 3.00 3.00 0.30 sdb 0.00 117.00 2048.00 362.00 65536.00 3832.00 28.78 0.67 0.28 0.23 0.56 0.23 56.40。數(shù)據(jù)導出來以后，使用rsync傳輸?shù)搅硗庖粋€機房的服務器上
rsync -av --port=8732 --password-file=/home/lijunda/rsyncd_ccopy_passwd1 --progress mysql@1.1.1.1::item/data1/phxsql.alldata /data1/ receiving incremental file list phxsql.alldata 30137810944 42% 113.03MB/s 0:05:52。在新機房DB導入數(shù)據(jù)，phxsql不管在哪個節(jié)點寫入數(shù)據(jù)，都會通過proxy路由到主庫進行寫入
。因為新增的節(jié)點，是slave角色，而slave角色，是不允許寫入的，因為有一個phxbinlogsvr_phxrpc模塊不停的去監(jiān)聽，確保slave是read only的角色，如果要讓數(shù)據(jù)能順利寫入，需要把這個模塊kill掉
killall -9 phxbinlogsvr_phxrpc
mysql -uroot -S /data1/phxsql/tmp/data/percona.workspace/tmp/percona.sock -e "set global super_read_only=off; set global read_only=off;reset master;"; mysql -uroot -S /data1/phxsql/tmp/data/percona.workspace/tmp/percona.sock < phxsql.alldata。200G數(shù)據(jù)，導出來是67G，導入是197G，導出使用2393秒，一個小時不到，但導入使用了59097秒，16個小時，相當久
。導數(shù)完畢，相當于做MySQL的主從，把主的數(shù)據(jù)，mysqludmp到從庫以后，就可以追同步了，于是就打開phxsql的同步模塊
python2.7 restart.py -pphxbinlogsvr。接著需要重啟MySQL，這一步我也沒有想明白，跟著官方教程做的
python2.7 restart.py -pmysql。使用官方腳本做測試，看了腳本默認寫賬號是寫賬號+1，如果寫賬號是54321，那么寫賬號就是54322，不過這個腳本只是簡單的測試，無非就是做一些建表，插入數(shù)據(jù)，然后再每個節(jié)點用不同的端口做讀寫操作
/bin/bash /data1/phxsql/tools/test_phxsql.sh 54321 IP1 IP2 IP3 IP4/bin/bash test_phxsql.sh 54321 "1.1.1.1" "2.2.2.2" "3.3.3.3" "4.4.4.4" create database to phxsql mysql -uroot -h2.1.1.1 -P54321 -e "create database if not exists test_phxsql;" create database to phxsql done create table to phxsql mysql -uroot -h2.1.1.1 -P54321 -e "use test_phxsql; create table if not exists test_phxsql(name varchar(80));" create table to phxsql done insert data to phxsql mysql -uroot -h2.1.1.1 -P54321 -e "use test_phxsql; insert into test_phxsql values(20170508101718);" insert data to phxsql done select data from phxsql mysql -uroot -h2.1.1.1 -P54321 -e "use test_phxsql; select name from test_phxsql;" name 20170505122350 20170508101718 select data from phxsql done select data from phxsql from read/write port mysql -uroot -h2.1.1.1 -P54321 -e "use test_phxsql; select name from test_phxsql;" name 20170505122350 20170508101718 select data from phxsql from read/write port done select data from phxsql from readonly port mysql -uroot -h2.1.1.1 -P54322 -e "use test_phxsql; select name from test_phxsql;" name 20170505122350 20170508101718 select data from phxsql from readonly port done select data from phxsql from read/write port mysql -uroot -h3.2.2.2 -P54321 -e "use test_phxsql; select name from test_phxsql;" name 20170505122350 20170508101718 select data from phxsql from read/write port done select data from phxsql from readonly port mysql -uroot -h3.2.2.2 -P54322 -e "use test_phxsql; select name from test_phxsql;" name 20170505122350 20170508101718 select data from phxsql from readonly port done select data from phxsql from read/write port mysql -uroot -h4.3.3.3 -P54321 -e "use test_phxsql; select name from test_phxsql;" name 20170505122350 20170508101718 select data from phxsql from read/write port done select data from phxsql from readonly port mysql -uroot -h4.3.3.3 -P54322 -e "use test_phxsql; select name from test_phxsql;" name 20170505122350 20170508101718 select data from phxsql from readonly port done select data from phxsql from read/write port mysql -uroot -h5.4.4.4 -P54321 -e "use test_phxsql; select name from test_phxsql;" name 20170505122350 20170508101718 select data from phxsql from read/write port done select data from phxsql from readonly port mysql -uroot -h5.4.4.4 -P54322 -e "use test_phxsql; select name from test_phxsql;" name 20170505122350 20170508101718 select data from phxsql from readonly port done。這里我自己也寫了一個shell腳本，會對以下幾個方面進行檢測,在set_array上修改真實集群的IP即可
# 1.能否互相訪問54321端口
# 2.能否互相訪問54322端口
# 3.能否互相訪問11111端口
# 4.能否互相訪問17000端口
# 5.其中一個節(jié)點插入的數(shù)據(jù)，另外的節(jié)點是否能夠查詢得到
#!/usr/bin/env bashset_array(){ # 整個phxsql集群 ip_array=( "1.1.1.1" "2.2.2.2" "3.3.3.3" "4.4.4.4" ) # 本機IP local_ip=$(ifconfig|grep cast|cut -d: -f2|cut -d' ' -f1|head -1) } # 測試percona連接 check_percona_connect(){ for ip in "${ip_array[@]}";do cmd="mysql -h"$ip" -uroot -P"$port" -e\"show databases;\"" eval $cmd >> /dev/null if [ $? -eq 0 ];then echo "$cmd"" —— ok" else echo "$cmd"" —— fail" fi done } # 測試讀寫端口 check_phxsql_connect(){ for ip in "${ip_array[@]}";do cmd="mysql -uroot -h"$ip" -P"${port}" -e\"show databases;\"" eval $cmd >> /dev/null if [ $? -eq 0 ];then echo "$cmd"" —— ok" else echo "$cmd"" —— fail" fi done } # 測試17000端口 check_binlog_tool(){ for ip in "${ip_array[@]}";do phxbinlogsvr_tools_phxrpc -f GetMemberList -h"$ip" -p "${port}" echo -e "\n" done } # 本機建表，插入數(shù)據(jù)，看其他端口是否能看到 check_54321_select(){ # 本機ip delete=($local_ip) # 排除本機ip for target in "${delete[@]}"; do for i in "${!ip_array[@]}"; do if [[ ${ip_array[i]} = "${delete[0]}" ]]; then unset 'ip_array[i]' fi done done cmd="mysql -h"$local_ip" -uroot -P"$port" -e\"create table test.tbl_test (a varchar(50));insert into test.tbl_test values ('data is $local_ip')\"" eval "$cmd" if [ $? -eq 0 ];then echo "${local_ip}插入"$port"數(shù)據(jù) --- data is "${local_ip}"" fi for ip in "${ip_array[@]}";do cmd_result=$(mysql -h"$ip" -P"$port" -e"select * from test.tbl_test;"|tail -1) if [ $? -eq 0 ];then echo -e "${ip}""查詢"$port"結果 --- ""${cmd_result}" fi done } check_54322_select(){ set_array for ip in "${ip_array[@]}";do cmd_result=$(mysql -h"$ip" -P"$port" -e"select * from test.tbl_test;"|tail -1) if [ $? -eq 0 ];then echo -e "${ip}""查詢"$port"結果 --- ""${cmd_result}" fi done } # main set_array echo -e "\n-------- 本機IP為"${local_ip}" ------------------------\n" echo -e "\n-------- 測試percona端口11111 ------------------------\n" port='11111' check_percona_connect port='54321' echo -e "\n-------- 測試讀寫端口54321 ------------------------\n" port='54321' check_phxsql_connect echo -e "\n-------- 測試讀寫端口54322 ------------------------\n" port='54322' check_phxsql_connect echo -e "\n-------- 測試binlog端口17000 ------------------------\n" port='17000' check_binlog_tool echo -e "\n-------- 測試54321讀寫端口 ------------------------\n" port='54321' check_54321_select echo -e "\n-------- 測試54322只讀端口 ------------------------\n" port='54322' check_54322_select echo -e "\n-------- 本機IP為"${local_ip}" ------------------------\n" #刪除測試用表 cmd="mysql -h"$local_ip" -uroot -P"54321" -e\"drop table test.tbl_test;\"" eval "$cmd"
。幾個節(jié)點之間查詢失敗的，一般有2個原因，一個是防火墻沒有開放給特定端口，另外一個是MySQL沒有做授權，如果要做授權，需要在54321端口去進行grant的操作，另外也可以看日志，日志的目錄在/data1/phxsql/tmp/data/log里面，當前錯誤日志是超鏈接，phxsql會自動幫你做日志的切割
mysqld.centos.root.log.ERROR.20170504-235300.59621 mysqld.centos.root.log.INFO.20170504-235300.59621 mysqld.centos.root.log.WARNING.20170504-235300.59621 mysqld.ERROR -> mysqld.centos.root.log.ERROR.20170504-235300.59621 mysqld.INFO -> mysqld.centos.root.log.INFO.20170504-235300.59621 mysqld.WARNING -> mysqld.centos.root.log.WARNING.20170504-235300.59621 phxbinlogsvr.centos.root.log.ERROR.20170504-040739.55554 phxbinlogsvr.centos.root.log.ERROR.20170504-235448.58391 phxbinlogsvr.centos.root.log.INFO.20170504-040739.55554 phxbinlogsvr.centos.root.log.INFO.20170504-235247.58391 phxbinlogsvr.centos.root.log.INFO.20170505-000557.58391 phxbinlogsvr.centos.root.log.WARNING.20170504-040739.55554 phxbinlogsvr.centos.root.log.WARNING.20170504-235448.58391 phxbinlogsvr.ERROR -> phxbinlogsvr.centos.root.log.ERROR.20170504-235448.58391 phxbinlogsvr.INFO -> phxbinlogsvr.centos.root.log.INFO.20170505-000557.58391 phxbinlogsvr.WARNING -> phxbinlogsvr.centos.root.log.WARNING.20170504-235448.58391 phxsqlproxy.centos.root.log.ERROR.20170504-040739.55575 phxsqlproxy.centos.root.log.ERROR.20170504-040739.55576 phxsqlproxy.centos.root.log.INFO.20170504-040739.55575 phxsqlproxy.centos.root.log.INFO.20170504-040739.55576 phxsqlproxy.centos.root.log.WARNING.20170504-040739.55575 phxsqlproxy.centos.root.log.WARNING.20170504-040739.55576 phxsqlproxy.ERROR -> phxsqlproxy.centos.root.log.ERROR.20170504-040739.55575 phxsqlproxy.INFO -> phxsqlproxy.centos.root.log.INFO.20170504-040739.55576 phxsqlproxy.WARNING -> phxsqlproxy.centos.root.log.WARNING.20170504-040739.55576。到此為止部署完畢
另外有需要云服務器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。
