搭建高可用mysql系列（2）-- Percona XtraDB Cluster 安装
本文主要介绍在 centos 下 Percona XtraDB Cluster（下文简称PXC） 的安装, 个人的系统版本信息如下：
[root@c2-d09 worker]# more /etc/redhat-release CentOS Linux release 7.3.1611 (Core)
一 安装前的准备工作
（1） 关闭 SELinux
这是因为SELinux 可能会限制 PXC 的数据访问,关闭的方法为命令行下执行：
setenforce 0
为了防止系统重启后上述操作实效，还需要修改 /etc/selinux/config 文件，将 SELINUX 设置为：permissive 即：
SELINUX=permissive
（2）确保 PXC 默认使用的的4个端口没有被防火墙阻止：
3306 mysql 实例端口
4444：用于SST传送的端口 ，可以在配置中进行修改，比如：wsrep_sst_receive_address=10.11.12.205:5555
4567：pxc cluster相互通讯的端口，可以在配置中进行修改，比如：wsrep_provider_options ="gmcast.listen_addr=tcp://0.0.0.0:4010; "
4568：用于IST传送的端口，可以在配置中进行修改，比如：wsrep_provider_options = "ist.recv_addr=10.11.12.206:7777; "
二 安装的具体步骤
（1）下载安装文件并解压到安装目录，本文的目录为：/home/worker/XtraDB_Cluster/XtraDB_3306
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/Percona-XtraDB- ,!Cluster-5.7.14-26.17/binary/tarball/Percona-XtraDB-Cluster-5.7.14-rel8-26.17.1. ,!Linux.x86_64.ssl101.tar.gz
（2）初始化第一个节点
（a）切换到安装目录，生成日志所需文件夹
cd /home/worker/XtraDB_Cluster/XtraDB_3306 mkdir -p logs/bin_log logs/innodb_log logs/relay_log var
（b）编辑 my.cnf 文件，本文用到的第一个节点 my.cnf 文件如下：
[client] port = 3306 socket = /home/worker/XtraDB_Cluster/XtraDB_3306/var/mysql.sock [mysqld] bind_address = 0.0.0.0 port = 3306 socket = /home/worker/XtraDB_Cluster/XtraDB_3306/var/mysql.sock user = worker datadir = /home/worker/XtraDB_Cluster/XtraDB_3306/data pid_file = /home/worker/XtraDB_Cluster/XtraDB_3306/var/mysqld.pid skip_name_resolve gtid_mode = ON enforce_gtid_consistency = ON tmpdir = /tmp slave_load_tmpdir = /tmp back_log = 1024 max_connections = 4096 max_connect_errors = 4096 table_open_cache = 1024 max_allowed_packet = 1M tmp_table_size = 128M max_heap_table_size = 64M read_buffer_size = 16M read_rnd_buffer_size = 16M sort_buffer_size = 32M join_buffer_size = 8M query_cache_size = 128M query_cache_limit = 32K open_files_limit = 65535 table_open_cache = 1024 table_definition_cache = 4096 thread_cache_size = 256 thread_stack = 192K transaction_isolation = REPEATABLE-READ ft_min_word_len = 4 # Set if mysql only or small db #memlock # Common logs log_error = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/error.log log_warnings slow_query_log = 1 slow_query_log_file = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/slow.log long_query_time = 2 general_log = 0 general_log_file = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/general.log # Replication server_id = 12616010 #log-slave-updates = 1 log_bin = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/bin_log/mysql-bin log_bin_index = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/bin_log/mysql-bin.index binlog_format = ROW binlog_cache_size = 32M expire_logs_days = 31 sync_binlog = 1 log_slave_updates = 1 relay_log = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/relay_log/mysql-relay-bin relay_log_index = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/relay_log/mysql-relay-bin.index relay_log_info_file = relay-log.info master-info-file = master.info skip_slave_start = 1 #*** MyISAM Specific options key_buffer_size = 128M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 32M myisam_max_sort_file_size = 128M myisam_repair_threads = 1 myisam-recover-options # *** INNODB Specific options *** innodb_file_per_table = 1 innodb_buffer_pool_size = 4G innodb_data_home_dir = /home/worker/XtraDB_Cluster/XtraDB_3306/data innodb_data_file_path = ibdata1:256M:autoextend innodb_max_dirty_pages_pct = 90 innodb_log_buffer_size = 16M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_log_group_home_dir = /home/worker/XtraDB_Cluster/XtraDB_3306/logs/innodb_log innodb_flush_method = O_DIRECT innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_flush_log_at_trx_commit = 1 # Speed up InnoDB shutdown. This will disable InnoDB to do a full purge # and insert buffer merge on shutdown. It may increase shutdown time a # lot, but InnoDB will have to do it on the next startup instead. #innodb_fast_shutdown innodb_lock_wait_timeout = 120 #pxc default_storage_engine=Innodb innodb_locks_unsafe_for_binlog=1 innodb_autoinc_lock_mode=2 pxc_strict_mode=ENFORCING wsrep_cluster_address=gcomm://10.126.160.1:33060,10.126.160.2:33070,10.126.160.3:33080 wsrep_provider_options="gmcast.listen_addr=tcp://0.0.0.0:33060;ist.recv_addr=10.126.160.1:33061" wsrep_sst_receive_address=10.126.160.1:33062 wsrep_cluster_name=pxc_default_channel wsrep_node_name=defaultchannel_126_160_1_3306 wsrep_provider=/home/worker/XtraDB_Cluster/XtraDB_3306/lib/libgalera_smm.so wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sst:defaultchannel" wsrep_node_address=10.126.160.1 wsrep_slave_threads=8 [mysqldump] quick max_allowed_packet = 1M [mysql] no_auto_rehash # Only allow UPDATEs and DELETEs that use keys. #safe_updates [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive_timeout [mysqld_safe] open_files_limit = 65535
（c）初始化mysql
sudo ./bin/mysqld --defaults-file=/home/worker/XtraDB_Cluster/XtraDB_3306/my.cnf --basedir=/home/worker/XtraDB_Cluster/XtraDB_3306 --initialize-insecure --user=worker
（d）加载数据并启动mysql
如果有使用 mysql dump 等方式导出的数据，可以将数据copy 到 /home/worker/XtraDB_Cluster/XtraDB_3306/data 目录下 ./bin/mysqld_safe --defaults-file=/home/worker/XtraDB_Cluster/XtraDB_3306/my.cnf
（e）启动PXC第一个节点
nohup ./bin/mysqld_safe --defaults-file=/home/worker/XtraDB_Cluster/XtraDB_3306/my.cnf --wsrep-new-cluster &
（f）验证节点是否正常启动
使用 socket 进入mysql： ./bin/mysql --socket=./var/mysql.sock -uroot 然后执行： show status like 'wsrep%'; 得到如下的结果，代表第一个节点已经正常启动： +------------------------------+-------------------------------------------------------+ | Variable_name | Value | +------------------------------+-------------------------------------------------------+ | wsrep_local_state_uuid | 3afab882-07ef-11e8-b57a-ce630de1af49 | | ... | ... | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | ... | | wsrep_cluster_size | 1 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | ... | ... | wsrep_ready | ON | +------------------------------+-------------------------------------------------------+ 59 rows in set (0.01 sec)
（g）创建sst用户，用于数据同步
在第一个节点 mysql下执行以下命令： CREATE USER 'sst' IDENTIFIED BY 'defaultchannel'; GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sst'; FLUSH PRIVILEGES;
至此，第一个节点创建完毕。
（3） 初始化其它节点
(a) 切换到安装目录，生成日志所需文件夹
跟步骤（2）中 （a）步骤一致
（b）编辑 my.cnf 文件，本文用到的第二，第三节点的 my.cnf 文件如下：
第二节点：
[client] port = 3307 socket = /home/worker/XtraDB_Cluster/XtraDB_3307/var/mysql.sock [mysqld] bind_address = 0.0.0.0 port = 3307 socket = /home/worker/XtraDB_Cluster/XtraDB_3307/var/mysql.sock user = worker datadir = /home/worker/XtraDB_Cluster/XtraDB_3307/data pid_file = /home/worker/XtraDB_Cluster/XtraDB_3307/var/mysqld.pid skip_name_resolve gtid_mode = ON enforce_gtid_consistency = ON tmpdir = /tmp slave_load_tmpdir = /tmp back_log = 1024 max_connections = 4096 max_connect_errors = 4096 table_open_cache = 1024 max_allowed_packet = 1M tmp_table_size = 128M max_heap_table_size = 64M read_buffer_size = 16M read_rnd_buffer_size = 16M sort_buffer_size = 32M join_buffer_size = 8M query_cache_size = 128M query_cache_limit = 32K open_files_limit = 65535 table_open_cache = 1024 table_definition_cache = 4096 thread_cache_size = 256 thread_stack = 192K transaction_isolation = REPEATABLE-READ ft_min_word_len = 4 # Set if mysql only or small db #memlock # Common logs log_error = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/error.log log_warnings slow_query_log = 1 slow_query_log_file = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/slow.log long_query_time = 2 general_log = 0 general_log_file = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/general.log # Replication server_id = 12616021 #log-slave-updates = 1 log_bin = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/bin_log/mysql-bin log_bin_index = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/bin_log/mysql-bin.index binlog_format = ROW binlog_cache_size = 32M expire_logs_days = 31 sync_binlog = 1 log_slave_updates = 1 relay_log = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/relay_log/mysql-relay-bin relay_log_index = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/relay_log/mysql-relay-bin.index relay_log_info_file = relay-log.info master-info-file = master.info skip_slave_start = 1 #*** MyISAM Specific options key_buffer_size = 128M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 32M myisam_max_sort_file_size = 128M myisam_repair_threads = 1 myisam-recover-options # *** INNODB Specific options *** innodb_file_per_table = 1 innodb_buffer_pool_size = 4G innodb_data_home_dir = /home/worker/XtraDB_Cluster/XtraDB_3307/data innodb_data_file_path = ibdata1:256M:autoextend innodb_max_dirty_pages_pct = 90 innodb_log_buffer_size = 16M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_log_group_home_dir = /home/worker/XtraDB_Cluster/XtraDB_3307/logs/innodb_log innodb_flush_method = O_DIRECT innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_flush_log_at_trx_commit = 1 # Speed up InnoDB shutdown. This will disable InnoDB to do a full purge # and insert buffer merge on shutdown. It may increase shutdown time a # lot, but InnoDB will have to do it on the next startup instead. #innodb_fast_shutdown innodb_lock_wait_timeout = 120 #pxc default_storage_engine=Innodb innodb_locks_unsafe_for_binlog=1 innodb_autoinc_lock_mode=2 pxc_strict_mode=ENFORCING wsrep_cluster_address=gcomm://10.126.160.1:33060,10.126.160.2:33070,10.126.160.3:33080 wsrep_provider_options="gmcast.listen_addr=tcp://0.0.0.0:33070;ist.recv_addr=10.126.160.2:33071" wsrep_sst_receive_address=10.126.160.2:33072 wsrep_cluster_name=pxc_default_channel wsrep_node_name=defaultchannel_126_160_2_3307 wsrep_provider=/home/worker/XtraDB_Cluster/XtraDB_3307/lib/libgalera_smm.so wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sst:defaultchannel" wsrep_node_address=10.126.160.2 wsrep_slave_threads=8 [mysqldump] quick max_allowed_packet = 1M [mysql] no_auto_rehash # Only allow UPDATEs and DELETEs that use keys. #safe_updates [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive_timeout [mysqld_safe] open_files_limit = 65535
第三个节点：
[client] port = 3308 socket = /home/worker/XtraDB_Cluster/XtraDB_3308/var/mysql.sock [mysqld] bind_address = 0.0.0.0 port = 3308 socket = /home/worker/XtraDB_Cluster/XtraDB_3308/var/mysql.sock user = worker datadir = /home/worker/XtraDB_Cluster/XtraDB_3308/data pid_file = /home/worker/XtraDB_Cluster/XtraDB_3308/var/mysqld.pid skip_name_resolve gtid_mode = ON enforce_gtid_consistency = ON tmpdir = /tmp slave_load_tmpdir = /tmp back_log = 1024 max_connections = 4096 max_connect_errors = 4096 table_open_cache = 1024 max_allowed_packet = 1M tmp_table_size = 128M max_heap_table_size = 64M read_buffer_size = 16M read_rnd_buffer_size = 16M sort_buffer_size = 32M join_buffer_size = 8M query_cache_size = 128M query_cache_limit = 32K open_files_limit = 65535 table_open_cache = 1024 table_definition_cache = 4096 thread_cache_size = 256 thread_stack = 192K transaction_isolation = REPEATABLE-READ ft_min_word_len = 4 # Set if mysql only or small db #memlock # Common logs log_error = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/error.log log_warnings slow_query_log = 1 slow_query_log_file = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/slow.log long_query_time = 2 general_log = 0 general_log_file = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/general.log # Replication server_id = 12616032 #log-slave-updates = 1 log_bin = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/bin_log/mysql-bin log_bin_index = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/bin_log/mysql-bin.index binlog_format = ROW binlog_cache_size = 32M expire_logs_days = 31 sync_binlog = 1 log_slave_updates = 1 relay_log = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/relay_log/mysql-relay-bin relay_log_index = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/relay_log/mysql-relay-bin.index relay_log_info_file = relay-log.info master-info-file = master.info skip_slave_start = 1 #*** MyISAM Specific options key_buffer_size = 128M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 32M myisam_max_sort_file_size = 128M myisam_repair_threads = 1 myisam-recover-options # *** INNODB Specific options *** innodb_file_per_table = 1 innodb_buffer_pool_size = 4G innodb_data_home_dir = /home/worker/XtraDB_Cluster/XtraDB_3308/data innodb_data_file_path = ibdata1:256M:autoextend innodb_max_dirty_pages_pct = 90 innodb_log_buffer_size = 16M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_log_group_home_dir = /home/worker/XtraDB_Cluster/XtraDB_3308/logs/innodb_log innodb_flush_method = O_DIRECT innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_flush_log_at_trx_commit = 1 # Speed up InnoDB shutdown. This will disable InnoDB to do a full purge # and insert buffer merge on shutdown. It may increase shutdown time a # lot, but InnoDB will have to do it on the next startup instead. #innodb_fast_shutdown innodb_lock_wait_timeout = 120 #pxc default_storage_engine=Innodb innodb_locks_unsafe_for_binlog=1 innodb_autoinc_lock_mode=2 pxc_strict_mode=ENFORCING wsrep_cluster_address=gcomm://10.126.160.1:33060,10.126.160.2:33070,10.126.160.3:33080 wsrep_provider_options="gmcast.listen_addr=tcp://0.0.0.0:33080;ist.recv_addr=10.126.160.3:33081" wsrep_sst_receive_address=10.126.160.3:33082 wsrep_cluster_name=pxc_default_channel wsrep_node_name=defaultchannel_126_160_3_3308 wsrep_provider=/home/worker/XtraDB_Cluster/XtraDB_3308/lib/libgalera_smm.so wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sst:defaultchannel" wsrep_node_address=10.126.160.3 wsrep_slave_threads=8 [mysqldump] quick max_allowed_packet = 1M [mysql] no_auto_rehash # Only allow UPDATEs and DELETEs that use keys. #safe_updates [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive_timeout [mysqld_safe] open_files_limit = 65535
（c）初始化 mysql
sudo ./bin/mysqld --defaults-file=/home/worker/XtraDB_Cluster/XtraDB_3307/my.cnf --basedir=/home/worker/XtraDB_Cluster/XtraDB_3307 --initialize-insecure --user=worker
（d）启动mysql 并 加入cluster
nohup ./bin/mysqld --defaults-file=/home/worker/XtraDB_Cluster/XtraDB_3307/my.cnf &
（f）检查mysql 的状态和cluster size
（4）按照普通节点启动的方式重启第一个节点
至此，PXC 搭建完毕
