安装部署PhxSQL教程

。需求：公司某软件使用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-certificate https://www.python.org/ftp/python/2.7.6/Python-2.7.6.tar.xztar xf Python-2.7.6.tar.xzcd Python-2.7.6./configure --prefix=/usr/localmake && make install

。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 --nodeps glibc-headers-2.20-8.fc21.x86_64.rpmrpm -Uvh --aid --force --nodeps glibc-devel-2.20-8.fc21.x86_64.rpmrpm -Uvh --aid --force --nodeps glibc-2.20-8.fc21.x86_64.rpmrpm -Uvh --aid --force --nodeps glibc-common-2.20-8.fc21.x86_64.rpm

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

iptables -A INPUT -p tcp -m multiport --dports 11111,54321,54322 -s 4.4.4.4 -j ACCEPT

。在phxsql集群里任意一个节点，在新节点还没有开始安装部署phxsql时，把新节点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的数据目录，官网教程定义目录为/tmp/data，这里改为/data1/

cd /data1/phxsql/tools/mkdir -p /data1/phxsql/tmp/data/logchown -R mysql:mysql /data1/phxsql/tmp/data/logpython2.7 install.py -i"4.4.4.4" -p 54321 -g 6000 -y 11111 -P 17000 -a 8001 -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)kill all binaries success....mkdir: cannot create directory `/data1/phxsql/etc': File existsgenerate all configs success....mkdir: cannot create directory `/data1/phxsql/tmp/data/log': File exists/data1/phxsql/tmp/data/phxbinlogsvrskip data 0mkdir: cannot create directory `/data1/phxsql/tmp/data/log': File exists/data1/phxsql/tmp/data/percona.workspacecd /data1/phxsql/percona.src; ./scripts/mysql_install_db --defaults-file=/data1/phxsql/etc/my.cnf --user=mysqlWARNING: 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 % compatiblewith this binary MySQL version. The MySQL daemon, mysqld, should worknormally with the exception that host name resolving will not work.This means that you should use IP addresses instead of hostnameswhen 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 ...OKFilling 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 ...OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE 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_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default.  This isstrongly 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.plPlease report any problems at https://bugs.launchpad.net/percona-server/+filebugThe latest information about Percona Server is available on the web at  http://www.percona.com/software/percona-serverSupport Percona by buying support at http://www.percona.com/products/mysql-supportinstall all success....nohup: appending output to `nohup.out'nohup: appending output to `nohup.out'WARNING: Logging before InitGoogleLogging() is written to STDERRW0504 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 doneW0504 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 doneW0504 04:07:39.385752 55555 phx_glog.cpp:74] read plugin config []start master worker finished ...start slave worker finished ...init pid 55580 env 0x7f77780008c0init pid 55582 env 0x7f777c0008c0init pid 55583 env 0x7f77700008c0init pid 55585 env 0x7f77800008c0init pid 55586 env 0x7f77740008c0init pid 55587 env 0x7f77840008c0init pid 55588 env 0x7f77780008c0init pid 55589 env 0x7f777c0008c0init pid 55592 env 0x7f77680008c0init pid 55593 env 0x7f776c0008c0init pid 55598 env 0x7f77700008c0init pid 55602 env 0x7f77680008c0init pid 55600 env 0x7f77600008c0init pid 55608 env 0x7f77640008c0init 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 perconaps -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

。添加环境变量

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

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

phxbinlogsvr_tools_phxrpc -f GetMemberList -h2.1.1.1 -p 17000

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

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的数据，导出文件67G，大概3倍的压缩，一共2393秒，平均11秒可以导出1G

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

avg-cpu:  %user   %nice %system %iowait  %steal   %idle           2.31    0.00    1.13    1.76    0.00   94.79Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %utilsda               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.30sdb               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

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

 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 listphxsql.alldata 30137810944  42%  113.03MB/s    0: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 "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数据，导出来是67G，导入是197G，导出使用2393秒，一个小时不到，但导入使用了59097秒，16个小时，相当久

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

 python2.7 restart.py -pphxbinlogsvr

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

python2.7 restart.py -pmysql

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

/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 phxsqlmysql -uroot -h2.1.1.1 -P54321 -e "create database if not exists test_phxsql;"create database to phxsql donecreate table to phxsqlmysql -uroot -h2.1.1.1 -P54321 -e "use test_phxsql; create table if not exists test_phxsql(name varchar(80));"create table to phxsql doneinsert data to phxsqlmysql -uroot -h2.1.1.1 -P54321 -e "use test_phxsql; insert into test_phxsql values(20170508101718);"insert data to phxsql doneselect data from phxsqlmysql -uroot -h2.1.1.1 -P54321 -e "use test_phxsql; select name from test_phxsql;"name2017050512235020170508101718select data from phxsql doneselect data from phxsql from read/write portmysql -uroot -h2.1.1.1 -P54321 -e "use test_phxsql; select name from test_phxsql;"name2017050512235020170508101718select data from phxsql from read/write port doneselect data from phxsql from readonly portmysql -uroot -h2.1.1.1 -P54322 -e "use test_phxsql; select name from test_phxsql;"name2017050512235020170508101718select data from phxsql from readonly port doneselect data from phxsql from read/write portmysql -uroot -h3.2.2.2 -P54321 -e "use test_phxsql; select name from test_phxsql;"name2017050512235020170508101718select data from phxsql from read/write port doneselect data from phxsql from readonly portmysql -uroot -h3.2.2.2 -P54322 -e "use test_phxsql; select name from test_phxsql;"name2017050512235020170508101718select data from phxsql from readonly port doneselect data from phxsql from read/write portmysql -uroot -h4.3.3.3 -P54321 -e "use test_phxsql; select name from test_phxsql;"name2017050512235020170508101718select data from phxsql from read/write port doneselect data from phxsql from readonly portmysql -uroot -h4.3.3.3 -P54322 -e "use test_phxsql; select name from test_phxsql;"name2017050512235020170508101718select data from phxsql from readonly port doneselect data from phxsql from read/write portmysql -uroot -h5.4.4.4 -P54321 -e "use test_phxsql; select name from test_phxsql;"name2017050512235020170508101718select data from phxsql from read/write port doneselect data from phxsql from readonly portmysql -uroot -h5.4.4.4 -P54322 -e "use test_phxsql; select name from test_phxsql;"name2017050512235020170508101718select data from phxsql from readonly port done

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

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

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

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

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

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

#!/usr/bin/env bash

set_array(){# 整个phxsql集群ip_array=("1.1.1.1""2.2.2.2""3.3.3.3""4.4.4.4")# 本机IPlocal_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}# 本机建表，插入数据，看其他端口是否能看到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"数据 --- 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}# 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\"drop table test.tbl_test;\""eval "$cmd"

。几个节点之间查询失败的，一般有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.58391phxsqlproxy.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

。到此为止部署完毕