Shulou(Shulou.com)06/01 Report--

. Requirements: a software of the company has been deployed in 3 computer rooms using phxsql. Now it is necessary to add a phxsql node in the new computer room. The following is the operation process, because 4 IP are used to protect privacy.

1.1.1.1

2.2.2.2

3.3.3.3

The new ip is 4.4.4.4

. Python2.7 version is required to install Phxsql. CentOS defaults to version 2.6 and needs to be upgraded.

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 needs to upgrade the glibc package. CentOS 6.5 defaults to version 2.12 and upgrades to version 2.20. Because glic is the bottom library of the operating system, operate with caution

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

Upgrade operation

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

. For a server with four phxsql nodes, the firewall needs to open the following ports

Iptables-An INPUT-p tcp-m multiport-- dports 11111 ACCEPT 54321 ACCEPT 544322-s 4.4.4.4-j

. In any node in the phxsql cluster, add the new node IP to the cluster before the new node starts to install and deploy phxsql

Phxbinlogsvr_tools_phxrpc-f AddMember-h2.1.1.1-p17000-m 4.4.4.4

Use command line check after addition

Phxbinlogsvr_tools_phxrpc-f GetMemberList-h2.1.1.1-p 17000

. Set the data directory of phxsql. The tutorial definition directory on the official website is / tmp/data, which is changed to / data1/ here.

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/

The following message appears during the installation process

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 hostname 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: 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 04http://www.percona.com/products/mysql-supportinstall 07http://www.percona.com/products/mysql-supportinstall 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

. If the installation is successful, the following three processes will be started

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

Occasionally phxbinlogsvr won't start, but it doesn't matter, because if phxbinlogsvr starts, you need to kill it.

Killall-9 phxbinlogsvr_phxrpc

. Add environment variabl

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

. Phxsql is installed. Currently, it is empty. You need to import data from a node with data. You usually choose a slave point to query which is master by command, and then avoid master. The amount of data on my side is 200G, which will consume disk resources for exporting a large amount of data, and you need to switch the business to other points.

Phxbinlogsvr_tools_phxrpc-f GetMemberList-h2.1.1.1-p 17000

. Use mysqldump to export data, you need to add a-quick parameter, the purpose is to export the data without going through buffer pool, but directly from the disk, and then save to a file, it is recommended to use a script, in order to know how long the derivative takes. Note that you are exporting full data-- all-database, because the amount of data of all nodes under phxsql is the same, which is equivalent to multi-point redundancy.

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 "Export time:" ${backup_time} "seconds"}

200g of data, export file 67G, about three times the compression, a total of 2393 seconds, an average of 11 seconds can be exported 1G

. There has always been a confusion, what is the critical value of iostat occupancy and load, so that there is no need to cut business? Is it necessary to relocate the business in derivative?

Avg-cpu:% user% nice% system% iowait% steal% idle 2.31 0.00 1.13 1.76 0.00 94.79Device: rrqm/s wrqm/s rbank 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

. After the data is exported, it is transferred to a server in another computer room using 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 imports data in the new computer room. No matter where the phxsql writes data, it will be routed to the main database through proxy for writing.

. Because the newly added node is the slave role, while the slave role is not allowed to write, because there is a phxbinlogsvr_phxrpc module constantly listening to ensure that slave is the role of read only. If you want to write data smoothly, you need to kill this module.

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 bashset_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} # Test read / write port 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} # Test port 17000 check_binlog_tool () {for ip in "${ip_array [@]}" Do phxbinlogsvr_tools_phxrpc-f GetMemberList-h "$ip"-p "${port}" echo-e "\ n" done} # create tables locally and insert data to see if check_54321_select () {# Native ip delete= ($local_ip) # excludes Native 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} insert" $port "data-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}"query" $port "result -" ${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}"query" $port "result -" ${cmd_result} "fi done} # mainset_arrayecho-e"\ n-Native IP is "${local_ip}" -\ n "echo-e"\ n-Test percona Port 11111 -\ n "port='11111'check_percona_connectport='54321'echo-e"\ n-Test read-write port 54321 -\ n "port='54321'check_phxsql_connectecho-e"\ n-Test read-write port 54322 -\ n "port='54322'check_phxsql_connectecho-e"\ n-Test binlog port 17000 -\ n "port='17000'check_binlog_toolecho-e"\ n-Test 54321 read-write port- -\ n "port='54321'check_54321_selectecho-e"\ n-Test 54322 read-only port -\ n "port='54322'check_54322_selectecho-e"\ n-Native IP is "${local_ip}" -\ n "# Delete test table cmd=" mysql-h "$local_ip"-uroot-P "54321"-e\ "drop table test.tbl_test \ "eval" $cmd "

. There are generally two reasons why the query between several nodes fails. One is that the firewall is not open to a specific port, and the other is that MySQL is not authorized. If you want to do authorization, you need to perform grant operation at port 54321. In addition, you can also look at the log. The log directory is in / data1/phxsql/tmp/data/log, and the current error log is a hyperlink. Phxsql will automatically cut the log for you.

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

. That's it. Deployment complete.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.