安装部署PhxSQL教程

2025-04-16 技术教程

。需求：公司某软件使用phxsql已经部署在3个机房，现需要在新的机房增加一个phxsql节点，以下为操作过程，因为保护隐私把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/optwget--no-check-certificatehttps://www.python.org/ftp/python/2.7.6/Python-2.7.6.tar.xztarxfPython-2.7.6.tar.xzcdPython-2.7.6./configure--prefix=/usr/localmake&&makeinstall

。Phxsql需要升级glibc包，Centos6.5默认为2.12版本，升级为2.20版本。因为glic是操作系统底层库，谨慎操作

glibc-common-2.12-1.149.el6_6.5.x86_64glibc-2.12-1.149.el6_6.5.i686glibc-2.12-1.149.el6_6.5.x86_64glibc-headers-2.12-1.149.el6_6.5.x86_64glibc-devel-2.12-1.149.el6_6.5.x86_64

升级操作

rpm-Uvh--aid--force--nodepsglibc-headers-2.20-8.fc21.x86_64.rpmrpm-Uvh--aid--force--nodepsglibc-devel-2.20-8.fc21.x86_64.rpmrpm-Uvh--aid--force--nodepsglibc-2.20-8.fc21.x86_64.rpmrpm-Uvh--aid--force--nodepsglibc-common-2.20-8.fc21.x86_64.rpm

。四个phxsql节点的服务器，防火墙需要开放如下几个端口

iptables-AINPUT-ptcp-mmultiport--dports11111,54321,54322-s4.4.4.4-jACCEPT

。在phxsql集群里任意一个节点，在新节点还没有开始安装部署phxsql时，把新节点IP添加到集群里面

phxbinlogsvr_tools_phxrpc-fAddMember-h2.1.1.1-p17000-m4.4.4.4

加后使用命令行检查

phxbinlogsvr_tools_phxrpc-fGetMemberList-h2.1.1.1-p17000

。设置phxsql的数据目录，官网教程定义目录为/tmp/data，这里改为/data1/

cd/data1/phxsql/tools/mkdir-p/data1/phxsql/tmp/data/logchown-Rmysql:mysql/data1/phxsql/tmp/data/logpython2.7install.py-i"4.4.4.4"-p54321-g6000-y11111-P17000-a8001-f/data1/phxsql/tmp/data/

安装过程会出现如下信息

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)killallbinariessuccess....mkdir:cannotcreatedirectory`/data1/phxsql/etc':Fileexistsgenerateallconfigssuccess....mkdir:cannotcreatedirectory`/data1/phxsql/tmp/data/log':Fileexists/data1/phxsql/tmp/data/phxbinlogsvrskipdata0mkdir:cannotcreatedirectory`/data1/phxsql/tmp/data/log':Fileexists/data1/phxsql/tmp/data/percona.workspacecd/data1/phxsql/percona.src;./scripts/mysql_install_db--defaults-file=/data1/phxsql/etc/my.cnf--user=mysqlWARNING:Thehost'centos'couldnotbelookedupwith/data1/phxsql/percona.src/bin/resolveip.Thisprobablymeansthatyourlibclibrariesarenot100%compatiblewiththisbinaryMySQLversion.TheMySQLdaemon,mysqld,shouldworknormallywiththeexceptionthathostnameresolvingwillnotwork.ThismeansthatyoushoulduseIPaddressesinsteadofhostnameswhenspecifyingMySQLprivileges!InstallingMySQLsystemtables...2017-05-0404:07:270[Warning]'THREAD_CONCURRENCY'isdeprecatedandwillberemovedinafuturerelease.2017-05-0404:07:270[Note]./bin/mysqld(mysqld5.6.31-77.0-log)startingasprocess55454...OKFillinghelptables...2017-05-0404:07:320[Warning]'THREAD_CONCURRENCY'isdeprecatedandwillberemovedinafuturerelease.2017-05-0404:07:320[Note]./bin/mysqld(mysqld5.6.31-77.0-log)startingasprocess55496...OKTostartmysqldatboottimeyouhavetocopysupport-files/mysql.servertotherightplaceforyoursystemPLEASEREMEMBERTOSETAPASSWORDFORTHEMySQLrootUSER!Todoso,starttheserver,thenissuethefollowingcommands:/data1/phxsql/percona.src/bin/mysqladmin-urootpassword'new-password'/data1/phxsql/percona.src/bin/mysqladmin-uroot-hcentospassword'new-password'Alternativelyyoucanrun:/data1/phxsql/percona.src/bin/mysql_secure_installationwhichwillalsogiveyoutheoptionofremovingthetestdatabasesandanonymoususercreatedbydefault.Thisisstronglyrecommendedforproductionservers.Seethemanualformoreinstructions.YoucanstarttheMySQLdaemonwith:cd.;/data1/phxsql/percona.src/bin/mysqld_safe&YoucantesttheMySQLdaemonwithmysql-test-run.plcdmysql-test;perlmysql-test-run.plPleasereportanyproblemsathttps://bugs.launchpad.net/percona-server/+filebugThelatestinformationaboutPerconaServerisavailableonthewebathttp://www.percona.com/software/percona-serverSupportPerconabybuyingsupportathttp://www.percona.com/products/mysql-supportinstallallsuccess....nohup:appendingoutputto`nohup.out'nohup:appendingoutputto`nohup.out'WARNING:LoggingbeforeInitGoogleLogging()iswrittentoSTDERRW050404:07:39.37802155555phx_glog.cpp:74]GetDefaultPathgetdebufpath/data1/phxsql/etc/W050404:07:39.38552455555phx_glog.cpp:74]RealReadFilereadpath/data1/phxsql/etc/phxsqlproxy.confdoneW050404:07:39.38560955555phx_glog.cpp:74]readpluginconfig[]W050404:07:39.38570155555phx_glog.cpp:74]RealReadFilereadpath/data1/phxsql/etc/phxsqlproxy.confdoneW050404:07:39.38575255555phx_glog.cpp:74]readpluginconfig[]startmasterworkerfinished...startslaveworkerfinished...initpid55580env0x7f77780008c0initpid55582env0x7f777c0008c0initpid55583env0x7f77700008c0initpid55585env0x7f77800008c0initpid55586env0x7f77740008c0initpid55587env0x7f77840008c0initpid55588env0x7f77780008c0initpid55589env0x7f777c0008c0initpid55592env0x7f77680008c0initpid55593env0x7f776c0008c0initpid55598env0x7f77700008c0initpid55602env0x7f77680008c0initpid55600env0x7f77600008c0initpid55608env0x7f77640008c0initpid55605env0x7f77600008c0

。如果安装成功，如下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@centosphxsql]#ps-ef|grep-vgrep|grepperconaps-ef|grep-vgrep|grepphxbinlogsvrmysql56942555529804:07pts/119: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-9phxbinlogsvr_phxrpc

。添加环境变量

vi/etc/profileexportPATH=/data1/phxsql/sbin:$PATH./etc/profile

。phxsql安装好了，目前是空的，需要从一个有数据的节点上把数据导过来，通常会选择从点，通过命令查询哪个是master，然后避开master，我这边数据量是200G，对于导出大量的数据会很消耗磁盘资源，需要让业务切换到其他点。

phxbinlogsvr_tools_phxrpc-fGetMemberList-h2.1.1.1-p17000

。使用mysqldump导出数据，需要加一个--quick参数，目的是导出数据时不经过buffer pool，而是直接从磁盘取数，然后保存到文件，建议使用脚本，为了知道导数到底用了多少时间。注意导出的是全量数据 --all-database，因为phxsql下所有节点的数据量都是一致的，相当于多点冗余

functionoutput_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.alldataend_date=$(date+%s)backup_time=$(($end_date-$start_date))echo"导出耗时："${backup_time}"秒"}

200G的数据，导出文件67G，大概3倍的压缩，一共2393秒，平均11秒可以导出1G

。一直有个困惑，iostat占用率和负载到底临界值是多少，才不需要切业务呢？导数的时候是否有必要去迁业务呢？

avg-cpu:%user%nice%system%iowait%steal%idle2.310.001.131.760.0094.79Device:rrqm/swrqm/sr/sw/srsec/swsec/savgrq-szavgqu-szawaitr_awaitw_awaitsvctm%utilsda0.000.000.001.000.008.008.000.003.000.003.003.000.30sdb0.00117.002048.00362.0065536.003832.0028.780.670.280.230.560.2356.40

。数据导出来以后，使用rsync传输到另外一个机房的服务器上

rsync-av--port=8732--password-file=/home/lijunda/rsyncd_ccopy_passwd1--progressmysql@1.1.1.1::item/data1/phxsql.alldata/data1/receivingincrementalfilelistphxsql.alldata3013781094442%113.03MB/s0:05:52

。在新机房DB导入数据，phxsql不管在哪个节点写入数据，都会通过proxy路由到主库进行写入

。因为新增的节点，是slave角色，而slave角色，是不允许写入的，因为有一个phxbinlogsvr_phxrpc模块不停的去监听，确保slave是read only的角色，如果要让数据能顺利写入，需要把这个模块kill掉

killall -9 phxbinlogsvr_phxrpc

mysql-uroot-S/data1/phxsql/tmp/data/percona.workspace/tmp/percona.sock-e"setglobalsuper_read_only=off;setglobalread_only=off;resetmaster;";mysql-uroot-S/data1/phxsql/tmp/data/percona.workspace/tmp/percona.sock<phxsql.alldata

。200G数据，导出来是67G，导入是197G，导出使用2393秒，一个小时不到，但导入使用了59097秒，16个小时，相当久

。导数完毕，相当于做MySQL的主从，把主的数据，mysqludmp到从库以后，就可以追同步了，于是就打开phxsql的同步模块

python2.7restart.py-pphxbinlogsvr

。接着需要重启MySQL，这一步我也没有想明白，跟着官方教程做的

python2.7restart.py-pmysql

。使用官方脚本做测试，看了脚本默认写账号是写账号+1，如果写账号是54321，那么写账号就是54322，不过这个脚本只是简单的测试，无非就是做一些建表，插入数据，然后再每个节点用不同的端口做读写操作

/bin/bash/data1/phxsql/tools/test_phxsql.sh54321IP1IP2IP3IP4

/bin/bashtest_phxsql.sh54321"1.1.1.1""2.2.2.2""3.3.3.3""4.4.4.4"createdatabasetophxsqlmysql-uroot-h2.1.1.1-P54321-e"createdatabaseifnotexiststest_phxsql;"createdatabasetophxsqldonecreatetabletophxsqlmysql-uroot-h2.1.1.1-P54321-e"usetest_phxsql;createtableifnotexiststest_phxsql(namevarchar(80));"createtabletophxsqldoneinsertdatatophxsqlmysql-uroot-h2.1.1.1-P54321-e"usetest_phxsql;insertintotest_phxsqlvalues(20170508101718);"insertdatatophxsqldoneselectdatafromphxsqlmysql-uroot-h2.1.1.1-P54321-e"usetest_phxsql;selectnamefromtest_phxsql;"name2017050512235020170508101718selectdatafromphxsqldoneselectdatafromphxsqlfromread/writeportmysql-uroot-h2.1.1.1-P54321-e"usetest_phxsql;selectnamefromtest_phxsql;"name2017050512235020170508101718selectdatafromphxsqlfromread/writeportdoneselectdatafromphxsqlfromreadonlyportmysql-uroot-h2.1.1.1-P54322-e"usetest_phxsql;selectnamefromtest_phxsql;"name2017050512235020170508101718selectdatafromphxsqlfromreadonlyportdoneselectdatafromphxsqlfromread/writeportmysql-uroot-h3.2.2.2-P54321-e"usetest_phxsql;selectnamefromtest_phxsql;"name2017050512235020170508101718selectdatafromphxsqlfromread/writeportdoneselectdatafromphxsqlfromreadonlyportmysql-uroot-h3.2.2.2-P54322-e"usetest_phxsql;selectnamefromtest_phxsql;"name2017050512235020170508101718selectdatafromphxsqlfromreadonlyportdoneselectdatafromphxsqlfromread/writeportmysql-uroot-h4.3.3.3-P54321-e"usetest_phxsql;selectnamefromtest_phxsql;"name2017050512235020170508101718selectdatafromphxsqlfromread/writeportdoneselectdatafromphxsqlfromreadonlyportmysql-uroot-h4.3.3.3-P54322-e"usetest_phxsql;selectnamefromtest_phxsql;"name2017050512235020170508101718selectdatafromphxsqlfromreadonlyportdoneselectdatafromphxsqlfromread/writeportmysql-uroot-h5.4.4.4-P54321-e"usetest_phxsql;selectnamefromtest_phxsql;"name2017050512235020170508101718selectdatafromphxsqlfromread/writeportdoneselectdatafromphxsqlfromreadonlyportmysql-uroot-h5.4.4.4-P54322-e"usetest_phxsql;selectnamefromtest_phxsql;"name2017050512235020170508101718selectdatafromphxsqlfromreadonlyportdone

。这里我自己也写了一个shell脚本，会对以下几个方面进行检测,在set_array上修改真实集群的IP即可

# 1.能否互相访问54321端口

# 2.能否互相访问54322端口

# 3.能否互相访问11111端口

# 4.能否互相访问17000端口

# 5.其中一个节点插入的数据，另外的节点是否能够查询得到

#!/usr/bin/envbash

set_array(){#整个phxsql集群ip_array=("1.1.1.1""2.2.2.2""3.3.3.3""4.4.4.4")#本机IPlocal_ip=$(ifconfig|grepcast|cut-d:-f2|cut-d''-f1|head-1)}#测试percona连接check_percona_connect(){foripin"${ip_array[@]}";docmd="mysql-h"$ip"-uroot-P"$port"-e\"showdatabases;\""eval$cmd>>/dev/nullif[$?-eq0];thenecho"$cmd""——ok"elseecho"$cmd""——fail"fidone}#测试读写端口check_phxsql_connect(){foripin"${ip_array[@]}";docmd="mysql-uroot-h"$ip"-P"${port}"-e\"showdatabases;\""eval$cmd>>/dev/nullif[$?-eq0];thenecho"$cmd""——ok"elseecho"$cmd""——fail"fidone}#测试17000端口check_binlog_tool(){foripin"${ip_array[@]}";dophxbinlogsvr_tools_phxrpc-fGetMemberList-h"$ip"-p"${port}"echo-e"\n"done}#本机建表，插入数据，看其他端口是否能看到check_54321_select(){#本机ipdelete=($local_ip)#排除本机ipfortargetin"${delete[@]}";doforiin"${!ip_array[@]}";doif[[${ip_array[i]}="${delete[0]}"]];thenunset'ip_array[i]'fidonedonecmd="mysql-h"$local_ip"-uroot-P"$port"-e\"createtabletest.tbl_test(avarchar(50));insertintotest.tbl_testvalues('datais$local_ip')\""eval"$cmd"if[$?-eq0];thenecho"${local_ip}插入"$port"数据---datais"${local_ip}""fiforipin"${ip_array[@]}";docmd_result=$(mysql-h"$ip"-P"$port"-e"select*fromtest.tbl_test;"|tail-1)if[$?-eq0];thenecho-e"${ip}""查询"$port"结果---""${cmd_result}"fidone}check_54322_select(){set_arrayforipin"${ip_array[@]}";docmd_result=$(mysql-h"$ip"-P"$port"-e"select*fromtest.tbl_test;"|tail-1)if[$?-eq0];thenecho-e"${ip}""查询"$port"结果---""${cmd_result}"fidone}#mainset_arrayecho-e"\n--------本机IP为"${local_ip}"------------------------\n"echo-e"\n--------测试percona端口11111------------------------\n"port='11111'check_percona_connectport='54321'echo-e"\n--------测试读写端口54321------------------------\n"port='54321'check_phxsql_connectecho-e"\n--------测试读写端口54322------------------------\n"port='54322'check_phxsql_connectecho-e"\n--------测试binlog端口17000------------------------\n"port='17000'check_binlog_toolecho-e"\n--------测试54321读写端口------------------------\n"port='54321'check_54321_selectecho-e"\n--------测试54322只读端口------------------------\n"port='54322'check_54322_selectecho-e"\n--------本机IP为"${local_ip}"------------------------\n"#删除测试用表cmd="mysql-h"$local_ip"-uroot-P"54321"-e\"droptabletest.tbl_test;\""eval"$cmd"

。几个节点之间查询失败的，一般有2个原因，一个是防火墙没有开放给特定端口，另外一个是MySQL没有做授权，如果要做授权，需要在54321端口去进行grant的操作，另外也可以看日志，日志的目录在/data1/phxsql/tmp/data/log里面，当前错误日志是超链接，phxsql会自动帮你做日志的切割

mysqld.centos.root.log.ERROR.20170504-235300.59621mysqld.centos.root.log.INFO.20170504-235300.59621mysqld.centos.root.log.WARNING.20170504-235300.59621mysqld.ERROR->mysqld.centos.root.log.ERROR.20170504-235300.59621mysqld.INFO->mysqld.centos.root.log.INFO.20170504-235300.59621mysqld.WARNING->mysqld.centos.root.log.WARNING.20170504-235300.59621phxbinlogsvr.centos.root.log.ERROR.20170504-040739.55554phxbinlogsvr.centos.root.log.ERROR.20170504-235448.58391phxbinlogsvr.centos.root.log.INFO.20170504-040739.55554phxbinlogsvr.centos.root.log.INFO.20170504-235247.58391phxbinlogsvr.centos.root.log.INFO.20170505-000557.58391phxbinlogsvr.centos.root.log.WARNING.20170504-040739.55554phxbinlogsvr.centos.root.log.WARNING.20170504-235448.58391phxbinlogsvr.ERROR->phxbinlogsvr.centos.root.log.ERROR.20170504-235448.58391phxbinlogsvr.INFO->phxbinlogsvr.centos.root.log.INFO.20170505-000557.58391phxbinlogsvr.WARNING->phxbinlogsvr.centos.root.log.WARNING.20170504-235448.58391phxsqlproxy.centos.root.log.ERROR.20170504-040739.55575phxsqlproxy.centos.root.log.ERROR.20170504-040739.55576phxsqlproxy.centos.root.log.INFO.20170504-040739.55575phxsqlproxy.centos.root.log.INFO.20170504-040739.55576phxsqlproxy.centos.root.log.WARNING.20170504-040739.55575phxsqlproxy.centos.root.log.WARNING.20170504-040739.55576phxsqlproxy.ERROR->phxsqlproxy.centos.root.log.ERROR.20170504-040739.55575phxsqlproxy.INFO->phxsqlproxy.centos.root.log.INFO.20170504-040739.55576phxsqlproxy.WARNING->phxsqlproxy.centos.root.log.WARNING.20170504-040739.55576

。到此为止部署完毕