共计 46249 个字符,预计需要花费 116 分钟才能阅读完成。
最近公司某客户要求我们的数据库搭建 PXC 集群以保证他们的系统高性能和搞稳定性,以后花费了一些时间去搭建和测试,也踩过一些坑,准备分享出来
系统:CentOS6.6
Percona XtraDB Cluster:5.6.26
建议关闭 iptables,4 个端口 3306,4444,4567,4568
Percona XtraDB Cluster 环境所涉及的端口:
#MySQL 实例端口
1. Regular MySQL port, default 3306.
#Percona XtraDB Cluster 相互通讯的端口
2. Port for group communication, default 4567. It can be changed by the option:
wsrep_provider_options =”gmcast.listen_addr=tcp://0.0.0.0:4010; “
# 用于 SST 传送的端口
3. Port for State Transfer, default 4444. It can be changed by the option:
wsrep_sst_receive_address=10.11.12.205:5555
# 用于 IST 传送的端口
4. Port for Incremental State Transfer, default port for group communication + 1 (4568). It can be changed by the option:
wsrep_provider_options = “ist.recv_addr=10.11.12.206:7777; “
node1 192.168.3.130
node2 192.168.3.129
node3 192.168.3.128
1. 安装软件依赖包(添加 repl 源,再安装依赖,3 台 server 操作一样。)
rpm -ivh http://dl.Fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm | |
yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes socat nc -y |
2. 安装 xtrabackup(PXC 同步数据需要用到)
yum install -y http://www.percona.com/downloads/percona-release/RedHat/0.1-3/percona-release-0.1-3.noarch.rpm | |
yum install -y percona-xtrabackup-24 |
3. 下载安装 Percona-XtraDB-Cluster
# 安装开发包
yum install -y libaio* | |
yum groupinstall -y 'Development tools' | |
yum install -y libssl.so.6 | |
ln -sf /usr/lib64/libssl.so.10 /usr/lib64/libssl.so.6 | |
ln -sf /usr/lib64/libcrypto.so.10 /usr/lib64/libcrypto.so.6 | |
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/Percona-XtraDB-Cluster-5.6.26-25.12/binary/tarball/Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz | |
tar zxvf Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz | |
useradd -M -s /sbin/nologin mysql | |
mkdir -p /usr/local/pxc | |
mkdir -p /data/pxc/mysql3306/{data,tmp,logs} | |
mv /data/download/Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64/* /usr/local/pxc/ | |
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql | |
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile | |
source /etc/profile | |
chown -R mysql:mysql /data/pxc/mysql3306/ | |
chown -R mysql:mysql /usr/local/pxc |
修改 my.cnf 配置文件,注意在 [mysqld] 段落添加 PXC 的参数:
vi /etc/my.cnf
130
[client] | |
port = 3306 | |
socket = /data/mysql/mysql3306/tmp/mysql.sock | |
# The MySQL server | |
[mysqld] | |
#########Basic################## | |
explicit_defaults_for_timestamp=true | |
port = 3306 | |
user = mysql | |
basedir = /usr/local/mysql | |
datadir = /data/mysql/mysql3306/data | |
tmpdir = /data/mysql/mysql3306/tmp | |
pid-file = /data/mysql/mysql3306/tmp/mysql.pid | |
socket = /data/mysql/mysql3306/tmp/mysql.sock | |
#skip-grant-tables | |
#character set | |
character_set_server = utf8 | |
open_files_limit = 65535 | |
back_log = 500 | |
#event_scheduler = ON | |
#lower_case_table_names=1 | |
skip-external-locking | |
skip_name_resolve = 1 | |
default-storage-engine = InnoDB | |
#timeout | |
wait_timeout=1000 | |
interactive_timeout=1000 | |
connect_timeout = 20 | |
server-id =1303306 #ip 最后一位 + 端口号 = 1003376 | |
#percona 的--recursion-method slavehost 模式 | |
#report_host = 10.105.9.115 | |
#report_port = 3306 | |
#plugin | |
plugin-load="semisync_master.so;semisync_slave.so" | |
#########error log############# | |
log-error = /data/mysql/mysql3306/logs/error.log | |
log-warnings = 2 | |
#########general log############# | |
#general_log=1 | |
#general_log_file=/data/mysql/mysql3306/logs/mysql.log | |
#########slow log############# | |
slow_query_log = 1 | |
long_query_time=1 | |
slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow | |
############# for replication################### | |
log-bin = /data/mysql/mysql3306/logs/mysql-bin | |
binlog_format = row | |
max_binlog_size = 500M | |
binlog_cache_size = 2M | |
max_binlog_cache_size = 2M | |
expire-logs-days = 7 | |
slave-net-timeout=30 | |
log_bin_trust_function_creators = 1 | |
log-slave-updates = 1 | |
skip-slave-start = 1 | |
#read_only =1 #从上设置,5.7 是 super_read_only | |
#GTID | |
gtid-mode = on | |
binlog_gtid_simple_recovery=1 | |
enforce_gtid_consistency=1 | |
#relay log | |
relay-log = /data/mysql/mysql3306/logs/mysql-relay | |
relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index | |
max-relay-log-size = 500M | |
#replication crash safe | |
sync_master_info = 1 | |
sync_relay_log_info = 1 | |
sync_relay_log = 1 | |
relay_log_recovery = 1 | |
master_info_repository = TABLE | |
relay_log_info_repository = TABLE | |
#semisync 动态开启 主从切换的时候用 | |
#rpl_semi_sync_master_enabled = 1 | |
#rpl_semi_sync_master_wait_no_slave = 1 | |
#rpl_semi_sync_master_timeout = 1000 | |
#rpl_semi_sync_slave_enabled = 1 | |
#rpl_semi_sync_master_timeout = 100000000 #不要超时, 防止切异步, 保证主从数据的完全一致性, 默认是 10000 | |
#rpl_semi_sync_master_wait_point = 'after_sync' #5.7 新增参数 after_sync 5.7 增强半同步 after_commit5.6 普通半同步 | |
#rpl_semi_sync_master_wait_for_slave_count = 2 #5.7 新增参数 等待多少个从库接收到 binlog | |
#ignore | |
#replicate-ignore-db = 'school','school2' | |
#replicate-do-db = 'school','school2' | |
#Multi-threaded Slave | |
#slave_parallel_workers=16 | |
#slave-parallel-type=LOGICAL_CLOCK | |
#slave-parallel-type=DATABASE | |
#replication error | |
#slave-skip-errors=1007,1051,1062 | |
#######per_thread_buffers##################### | |
max_connections=1100 | |
max_user_connections=1000 | |
max_connect_errors=1000 | |
#myisam_recover | |
key_buffer_size = 64M | |
max_allowed_packet = 16M | |
#table_cache = 3096 | |
table_open_cache = 6144 | |
table_definition_cache = 4096 | |
read_buffer_size = 1M | |
join_buffer_size = 128K | |
read_rnd_buffer_size = 1M | |
#myisam | |
sort_buffer_size = 128K | |
myisam_max_sort_file_size = 10G | |
myisam_repair_threads = 1 | |
myisam_sort_buffer_size = 32M | |
tmp_table_size = 32M | |
max_heap_table_size = 64M | |
query_cache_type=0 | |
query_cache_size = 0 | |
bulk_insert_buffer_size = 32M | |
thread_cache_size = 64 | |
#thread_concurrency = 32 | |
thread_stack = 192K | |
###############InnoDB########################### | |
innodb_data_home_dir = /data/mysql/mysql3306/data | |
innodb_log_group_home_dir = /data/mysql/mysql3306/logs | |
innodb_data_file_path = ibdata1:1000M:autoextend | |
innodb_buffer_pool_size = 1G #根据内存大小设置 | |
innodb_buffer_pool_instances = 8 | |
#innodb_additional_mem_pool_size = 16M | |
innodb_log_file_size = 500M | |
innodb_log_buffer_size = 16M | |
innodb_log_files_in_group = 3 | |
innodb_flush_log_at_trx_commit = 1 | |
sync_binlog = 1 | |
innodb_lock_wait_timeout = 10 | |
innodb_sync_spin_loops = 40 | |
innodb_max_dirty_pages_pct = 80 | |
innodb_support_xa = 1 | |
innodb_thread_concurrency = 0 | |
innodb_thread_sleep_delay = 500 | |
innodb_concurrency_tickets = 1000 | |
innodb_flush_method = O_DIRECT | |
innodb_file_per_table = 1 | |
innodb_read_io_threads = 16 | |
innodb_write_io_threads = 16 | |
innodb_io_capacity = 800 #机械盘 800 ssd 2000 | |
innodb_flush_neighbors = 1 | |
innodb_file_format = Barracuda | |
innodb_purge_threads=1 #5.6 只能有一个,5.7 可以设置多个 | |
innodb_purge_batch_size = 32 | |
innodb_old_blocks_pct=75 | |
innodb_change_buffering=all | |
innodb_stats_on_metadata=OFF | |
innodb_print_all_deadlocks = 1 | |
#innodb_status_output=1 | |
#innodb_status_output_locks=1 | |
performance_schema=0 #是否打开 P_S 库进行性能收集 | |
transaction_isolation = READ-COMMITTED | |
#innodb_force_recovery=0 | |
#innodb_fast_shutdown=1 | |
#innodb_status_file = 1 #实时输出 show engine innodb status 到 innodb_status 文件 | |
############# PXC ##################### | |
innodb_autoinc_lock_mode=2 | |
wsrep_cluster_name=pxc-dongzheng | |
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so | |
wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129 | |
wsrep_node_address=192.168.3.130 | |
wsrep_slave_threads=2 | |
wsrep_sst_auth=sst:dongzheng | |
wsrep_sst_method=xtrabackup-v2 | |
wsrep_provider_options="debug=1" | |
[mysqldump] | |
quick | |
max_allowed_packet = 128M | |
[mysql] | |
no-auto-rehash | |
max_allowed_packet = 128M | |
prompt = '(product)\u@\h:\p [\d]> ' | |
default_character_set = utf8 | |
[myisamchk] | |
key_buffer_size = 64M | |
sort_buffer_size = 512k | |
read_buffer = 2M | |
write_buffer = 2M | |
[mysqlhotcopy] | |
interactive-timeout | |
[mysqld_safe] | |
#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so |
129
[client] | |
port = 3306 | |
socket = /data/mysql/mysql3306/tmp/mysql.sock | |
# The MySQL server | |
[mysqld] | |
#########Basic################## | |
explicit_defaults_for_timestamp=true | |
port = 3306 | |
user = mysql | |
basedir = /usr/local/mysql | |
datadir = /data/mysql/mysql3306/data | |
tmpdir = /data/mysql/mysql3306/tmp | |
pid-file = /data/mysql/mysql3306/tmp/mysql.pid | |
socket = /data/mysql/mysql3306/tmp/mysql.sock | |
#skip-grant-tables | |
#character set | |
character_set_server = utf8 | |
open_files_limit = 65535 | |
back_log = 500 | |
#event_scheduler = ON | |
#lower_case_table_names=1 | |
skip-external-locking | |
skip_name_resolve = 1 | |
default-storage-engine = InnoDB | |
#timeout | |
wait_timeout=1000 | |
interactive_timeout=1000 | |
connect_timeout = 20 | |
server-id =1293306 #ip 最后一位 + 端口号 = 1003376 | |
#percona 的--recursion-method slavehost 模式 | |
#report_host = 10.105.9.115 | |
#report_port = 3306 | |
#plugin | |
plugin-load="semisync_master.so;semisync_slave.so" | |
#########error log############# | |
log-error = /data/mysql/mysql3306/logs/error.log | |
log-warnings = 2 | |
#########general log############# | |
#general_log=1 | |
#general_log_file=/data/mysql/mysql3306/logs/mysql.log | |
#########slow log############# | |
slow_query_log = 1 | |
long_query_time=1 | |
slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow | |
############# for replication################### | |
log-bin = /data/mysql/mysql3306/logs/mysql-bin | |
binlog_format = row | |
max_binlog_size = 500M | |
binlog_cache_size = 2M | |
max_binlog_cache_size = 2M | |
expire-logs-days = 7 | |
slave-net-timeout=30 | |
log_bin_trust_function_creators = 1 | |
log-slave-updates = 1 | |
skip-slave-start = 1 | |
#read_only =1 #从上设置,5.7 是 super_read_only | |
#GTID | |
gtid-mode = on | |
binlog_gtid_simple_recovery=1 | |
enforce_gtid_consistency=1 | |
#relay log | |
relay-log = /data/mysql/mysql3306/logs/mysql-relay | |
relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index | |
max-relay-log-size = 500M | |
#replication crash safe | |
sync_master_info = 1 | |
sync_relay_log_info = 1 | |
sync_relay_log = 1 | |
relay_log_recovery = 1 | |
master_info_repository = TABLE | |
relay_log_info_repository = TABLE | |
#semisync 动态开启 主从切换的时候用 | |
#rpl_semi_sync_master_enabled = 1 | |
#rpl_semi_sync_master_wait_no_slave = 1 | |
#rpl_semi_sync_master_timeout = 1000 | |
#rpl_semi_sync_slave_enabled = 1 | |
#rpl_semi_sync_master_timeout = 100000000 #不要超时, 防止切异步, 保证主从数据的完全一致性, 默认是 10000 | |
#rpl_semi_sync_master_wait_point = 'after_sync' #5.7 新增参数 after_sync 5.7 增强半同步 after_commit5.6 普通半同步 | |
#rpl_semi_sync_master_wait_for_slave_count = 2 #5.7 新增参数 等待多少个从库接收到 binlog | |
#ignore | |
#replicate-ignore-db = 'school','school2' | |
#replicate-do-db = 'school','school2' | |
#Multi-threaded Slave | |
#slave_parallel_workers=16 | |
#slave-parallel-type=LOGICAL_CLOCK | |
#slave-parallel-type=DATABASE | |
#replication error | |
#slave-skip-errors=1007,1051,1062 | |
#######per_thread_buffers##################### | |
max_connections=1100 | |
max_user_connections=1000 | |
max_connect_errors=1000 | |
#myisam_recover | |
key_buffer_size = 64M | |
max_allowed_packet = 16M | |
#table_cache = 3096 | |
table_open_cache = 6144 | |
table_definition_cache = 4096 | |
read_buffer_size = 1M | |
join_buffer_size = 128K | |
read_rnd_buffer_size = 1M | |
#myisam | |
sort_buffer_size = 128K | |
myisam_max_sort_file_size = 10G | |
myisam_repair_threads = 1 | |
myisam_sort_buffer_size = 32M | |
tmp_table_size = 32M | |
max_heap_table_size = 64M | |
query_cache_type=0 | |
query_cache_size = 0 | |
bulk_insert_buffer_size = 32M | |
thread_cache_size = 64 | |
#thread_concurrency = 32 | |
thread_stack = 192K | |
###############InnoDB########################### | |
innodb_data_home_dir = /data/mysql/mysql3306/data | |
innodb_log_group_home_dir = /data/mysql/mysql3306/logs | |
innodb_data_file_path = ibdata1:1000M:autoextend | |
innodb_buffer_pool_size = 1G #根据内存大小设置 | |
innodb_buffer_pool_instances = 8 | |
#innodb_additional_mem_pool_size = 16M | |
innodb_log_file_size = 500M | |
innodb_log_buffer_size = 16M | |
innodb_log_files_in_group = 3 | |
innodb_flush_log_at_trx_commit = 1 | |
sync_binlog = 1 | |
innodb_lock_wait_timeout = 10 | |
innodb_sync_spin_loops = 40 | |
innodb_max_dirty_pages_pct = 80 | |
innodb_support_xa = 1 | |
innodb_thread_concurrency = 0 | |
innodb_thread_sleep_delay = 500 | |
innodb_concurrency_tickets = 1000 | |
innodb_flush_method = O_DIRECT | |
innodb_file_per_table = 1 | |
innodb_read_io_threads = 16 | |
innodb_write_io_threads = 16 | |
innodb_io_capacity = 800 #机械盘 800 ssd 2000 | |
innodb_flush_neighbors = 1 | |
innodb_file_format = Barracuda | |
innodb_purge_threads=1 #5.6 只能有一个,5.7 可以设置多个 | |
innodb_purge_batch_size = 32 | |
innodb_old_blocks_pct=75 | |
innodb_change_buffering=all | |
innodb_stats_on_metadata=OFF | |
innodb_print_all_deadlocks = 1 | |
#innodb_status_output=1 | |
#innodb_status_output_locks=1 | |
performance_schema=0 #是否打开 P_S 库进行性能收集 | |
transaction_isolation = READ-COMMITTED | |
#innodb_force_recovery=0 | |
#innodb_fast_shutdown=1 | |
#innodb_status_file = 1 #实时输出 show engine innodb status 到 innodb_status 文件 | |
############# PXC ##################### | |
innodb_autoinc_lock_mode=2 | |
wsrep_cluster_name=pxc-dongzheng | |
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so | |
wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129 | |
wsrep_node_address=192.168.3.129 | |
wsrep_slave_threads=2 | |
wsrep_sst_auth=sst:dongzheng | |
wsrep_sst_method=xtrabackup-v2 | |
[mysqldump] | |
quick | |
max_allowed_packet = 128M | |
[mysql] | |
no-auto-rehash | |
max_allowed_packet = 128M | |
prompt = '(product)\u@\h:\p [\d]> ' | |
default_character_set = utf8 | |
[myisamchk] | |
key_buffer_size = 64M | |
sort_buffer_size = 512k | |
read_buffer = 2M | |
write_buffer = 2M | |
[mysqlhotcopy] | |
interactive-timeout | |
[mysqld_safe] | |
#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so |
View Code
128

[client] | |
port = 3306 | |
socket = /data/mysql/mysql3306/tmp/mysql.sock | |
# The MySQL server | |
[mysqld] | |
#########Basic################## | |
explicit_defaults_for_timestamp=true | |
port = 3306 | |
user = mysql | |
basedir = /usr/local/mysql | |
datadir = /data/mysql/mysql3306/data | |
tmpdir = /data/mysql/mysql3306/tmp | |
pid-file = /data/mysql/mysql3306/tmp/mysql.pid | |
socket = /data/mysql/mysql3306/tmp/mysql.sock | |
#skip-grant-tables | |
#character set | |
character_set_server = utf8 | |
open_files_limit = 65535 | |
back_log = 500 | |
#event_scheduler = ON | |
#lower_case_table_names=1 | |
skip-external-locking | |
skip_name_resolve = 1 | |
default-storage-engine = InnoDB | |
#timeout | |
wait_timeout=1000 | |
interactive_timeout=1000 | |
connect_timeout = 20 | |
server-id =1283306 #ip 最后一位 + 端口号 = 1003376 | |
#percona 的--recursion-method slavehost 模式 | |
#report_host = 10.105.9.115 | |
#report_port = 3306 | |
#plugin | |
plugin-load="semisync_master.so;semisync_slave.so" | |
#########error log############# | |
log-error = /data/mysql/mysql3306/logs/error.log | |
log-warnings = 2 | |
#########general log############# | |
#general_log=1 | |
#general_log_file=/data/mysql/mysql3306/logs/mysql.log | |
#########slow log############# | |
slow_query_log = 1 | |
long_query_time=1 | |
slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow | |
############# for replication################### | |
log-bin = /data/mysql/mysql3306/logs/mysql-bin | |
binlog_format = row | |
max_binlog_size = 500M | |
binlog_cache_size = 2M | |
max_binlog_cache_size = 2M | |
expire-logs-days = 7 | |
slave-net-timeout=30 | |
log_bin_trust_function_creators = 1 | |
log-slave-updates = 1 | |
skip-slave-start = 1 | |
#read_only =1 #从上设置,5.7 是 super_read_only | |
#GTID | |
gtid-mode = on | |
binlog_gtid_simple_recovery=1 | |
enforce_gtid_consistency=1 | |
#relay log | |
relay-log = /data/mysql/mysql3306/logs/mysql-relay | |
relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index | |
max-relay-log-size = 500M | |
#replication crash safe | |
sync_master_info = 1 | |
sync_relay_log_info = 1 | |
sync_relay_log = 1 | |
relay_log_recovery = 1 | |
master_info_repository = TABLE | |
relay_log_info_repository = TABLE | |
#semisync 动态开启 主从切换的时候用 | |
#rpl_semi_sync_master_enabled = 1 | |
#rpl_semi_sync_master_wait_no_slave = 1 | |
#rpl_semi_sync_master_timeout = 1000 | |
#rpl_semi_sync_slave_enabled = 1 | |
#rpl_semi_sync_master_timeout = 100000000 #不要超时, 防止切异步, 保证主从数据的完全一致性, 默认是 10000 | |
#rpl_semi_sync_master_wait_point = 'after_sync' #5.7 新增参数 after_sync 5.7 增强半同步 after_commit5.6 普通半同步 | |
#rpl_semi_sync_master_wait_for_slave_count = 2 #5.7 新增参数 等待多少个从库接收到 binlog | |
#ignore | |
#replicate-ignore-db = 'school','school2' | |
#replicate-do-db = 'school','school2' | |
#Multi-threaded Slave | |
#slave_parallel_workers=16 | |
#slave-parallel-type=LOGICAL_CLOCK | |
#slave-parallel-type=DATABASE | |
#replication error | |
#slave-skip-errors=1007,1051,1062 | |
#######per_thread_buffers##################### | |
max_connections=1100 | |
max_user_connections=1000 | |
max_connect_errors=1000 | |
#myisam_recover | |
key_buffer_size = 64M | |
max_allowed_packet = 16M | |
#table_cache = 3096 | |
table_open_cache = 6144 | |
table_definition_cache = 4096 | |
read_buffer_size = 1M | |
join_buffer_size = 128K | |
read_rnd_buffer_size = 1M | |
#myisam | |
sort_buffer_size = 128K | |
myisam_max_sort_file_size = 10G | |
myisam_repair_threads = 1 | |
myisam_sort_buffer_size = 32M | |
tmp_table_size = 32M | |
max_heap_table_size = 64M | |
query_cache_type=0 | |
query_cache_size = 0 | |
bulk_insert_buffer_size = 32M | |
thread_cache_size = 64 | |
#thread_concurrency = 32 | |
thread_stack = 192K | |
###############InnoDB########################### | |
innodb_data_home_dir = /data/mysql/mysql3306/data | |
innodb_log_group_home_dir = /data/mysql/mysql3306/logs | |
innodb_data_file_path = ibdata1:1000M:autoextend | |
innodb_buffer_pool_size = 1G #根据内存大小设置 | |
innodb_buffer_pool_instances = 8 | |
#innodb_additional_mem_pool_size = 16M | |
innodb_log_file_size = 500M | |
innodb_log_buffer_size = 16M | |
innodb_log_files_in_group = 3 | |
innodb_flush_log_at_trx_commit = 1 | |
sync_binlog = 1 | |
innodb_lock_wait_timeout = 10 | |
innodb_sync_spin_loops = 40 | |
innodb_max_dirty_pages_pct = 80 | |
innodb_support_xa = 1 | |
innodb_thread_concurrency = 0 | |
innodb_thread_sleep_delay = 500 | |
innodb_concurrency_tickets = 1000 | |
innodb_flush_method = O_DIRECT | |
innodb_file_per_table = 1 | |
innodb_read_io_threads = 16 | |
innodb_write_io_threads = 16 | |
innodb_io_capacity = 800 #机械盘 800 ssd 2000 | |
innodb_flush_neighbors = 1 | |
innodb_file_format = Barracuda | |
innodb_purge_threads=1 #5.6 只能有一个,5.7 可以设置多个 | |
innodb_purge_batch_size = 32 | |
innodb_old_blocks_pct=75 | |
innodb_change_buffering=all | |
innodb_stats_on_metadata=OFF | |
innodb_print_all_deadlocks = 1 | |
#innodb_status_output=1 | |
#innodb_status_output_locks=1 | |
performance_schema=0 #是否打开 P_S 库进行性能收集 | |
transaction_isolation = READ-COMMITTED | |
#innodb_force_recovery=0 | |
#innodb_fast_shutdown=1 | |
#innodb_status_file = 1 #实时输出 show engine innodb status 到 innodb_status 文件 | |
############# PXC ##################### | |
innodb_autoinc_lock_mode=2 | |
wsrep_cluster_name=pxc-dongzheng | |
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so | |
wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129 | |
wsrep_node_address=192.168.3.128 | |
wsrep_slave_threads=2 | |
wsrep_sst_auth=sst:dongzheng | |
wsrep_sst_method=xtrabackup-v2 | |
wsrep_provider_options="debug=1;socket.checksum=1" | |
[mysqldump] | |
quick | |
max_allowed_packet = 128M | |
[mysql] | |
no-auto-rehash | |
max_allowed_packet = 128M | |
prompt = '(product)\u@\h:\p [\d]> ' | |
default_character_set = utf8 | |
[myisamchk] | |
key_buffer_size = 64M | |
sort_buffer_size = 512k | |
read_buffer = 2M | |
write_buffer = 2M | |
[mysqlhotcopy] | |
interactive-timeout | |
[mysqld_safe] | |
#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so |
View Code
cd /usr/local/pxc/ | |
./scripts/mysql_install_db --defaults-file=/etc/my.cnf |
看到两次 ok
# 修改启动脚本
vi /etc/init.d/mysql
basedir=/usr/local/pxc | |
datadir=/data/pxc/mysql3306/data |
4. 启动,进行授权操作,对于第一个节点必须以特殊方式启动,如下:
/etc/init.d/mysql --help | |
Usage: mysql {start|stop|restart|restart-bootstrap|reload|force-reload|status|bootstrap-pxc} [MySQL (Percona XtraDB Cluster) options ] |
第一个节点启动
/etc/init.d/mysql bootstrap-pxc
5. 安全加固
连接 mysql:mysql -uroot -p -S /data/mysql/mysql3306/tmp/mysql.sock
delete from mysql.user where user!=’root’ or host!=’localhost’;
truncate table mysql.db;
drop database test;
进行授权,这里用户名密码是 my.cnf 里定义的用户名 sst 密码 dongzheng
GRANT ALL PRIVILEGES ON *.* TO ‘sst’@’%’ identified by ‘dongzheng’;
use mysql;
UPDATE user SET password=PASSWORD(‘123456′) WHERE user=’root’;
flush privileges;
PXC 状态确认
show global status like ‘wsrep%’;
6.netstat 进行查看,可以发现启动两个端口
netstat -lntp | grep mysql
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2964/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2964/mysqld
更多详情见请继续阅读下一页的精彩内容:http://www.linuxidc.com/Linux/2017-05/143501p2.htm
另外两个节点的启动和配置
node2 my.cnf 的 PXC 配置节:
############# PXC #####################
innodb_autoinc_lock_mode=2 #自增锁的优化
wsrep_cluster_name=pxc-dongzheng #集群名字
wsrep_provider=/usr/local/MySQL/lib/libgalera_smm.so #库文件
wsrep_cluster_address=gcomm://192.168.2.130,192.168.3.128,192.168.0.129 #节点中所有 ip
wsrep_node_address=192.168.0.129 #节点的 ip
wsrep_slave_threads=2 #开启的复制线程数,cpu 核数 *2
wsrep_sst_auth=sst:dongzheng #sst 模式需要的用户名和密码
wsrep_sst_method=xtrabackup-v2 #采用什么方式复制数据。还支持 mysqldump,rsync
wsrep_provider_options=”debug=1;socket.checksum=1″ #打开调试模式
node3 my.cnf 的 PXC 配置节:
############# PXC #####################
innodb_autoinc_lock_mode=2 #自增锁的优化
wsrep_cluster_name=pxc-dongzheng #集群名字
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so #库文件
wsrep_cluster_address=gcomm://192.168.2.130,192.168.3.128,192.168.0.129 #节点中所有 ip
wsrep_node_address=192.168.0.128 #节点的 ip
wsrep_slave_threads=2 #开启的复制线程数,cpu 核数 *2
wsrep_sst_auth=sst:dongzheng #sst 模式需要的用户名和密码
wsrep_sst_method=xtrabackup-v2 #采用什么方式复制数据。还支持 mysqldump,rsync
wsrep_provider_options=”debug=1;socket.checksum=1″ #打开调试模式
启动
/etc/init.d/mysql start
启动的时候会把主节点的所有数据传送过从节点包括所有数据库和日志,简单来讲从节点在启动的时候会做 SST 同步
从节点不需要做安全加固,因为从节点跟主节点的数据库和数据都一样
PXC 状态确认
show global status like ‘wsrep%’;
netstat 进行查看,可以发现启动两个端口
netstat -lntp | grep mysql
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2964/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2964/mysqld
###############################################################
集群关闭
所有节点都用
/etc/init.d/mysql stop
节点重启
第一个节点用:/etc/init.d/mysql restart-bootstrap
其他节点用:/etc/init.d/mysql restart
集群关闭之后,再启动,谁做第一个节点谁就执行 /etc/init.d/mysql bootstrap-pxc
如果不是所有节点都关闭,那么当启动集群节点的时候不需要执行 /etc/init.d/mysql bootstrap-pxc
/etc/init.d/mysql –help
Usage: mysql {start|stop|restart|restart-bootstrap|reload|force-reload|status|bootstrap-pxc} [MySQL (Percona XtraDB Cluster) options ]
到这里搭建就基本结束了。3 个节点同时支持 read/write 操作。
在 130 上执行
show global status like 'wsrep%'\G; | |
ERROR 2006 (HY000): MySQL server has gone away | |
No connection. Trying to reconnect... | |
Connection id: 6 | |
Current database: *** NONE *** | |
*************************** 1. row *************************** | |
Variable_name: wsrep_local_state_uuid | |
Value: f4cb2250-30ac-11e7-8315-17918c780c9e | |
*************************** 2. row *************************** | |
Variable_name: wsrep_protocol_version | |
Value: 7 | |
*************************** 3. row *************************** | |
Variable_name: wsrep_last_committed | |
Value: 4 | |
*************************** 4. row *************************** | |
Variable_name: wsrep_replicated | |
Value: 0 | |
*************************** 5. row *************************** | |
Variable_name: wsrep_replicated_bytes | |
Value: 0 | |
*************************** 6. row *************************** | |
Variable_name: wsrep_repl_keys | |
Value: 0 | |
*************************** 7. row *************************** | |
Variable_name: wsrep_repl_keys_bytes | |
Value: 0 | |
*************************** 8. row *************************** | |
Variable_name: wsrep_repl_data_bytes | |
Value: 0 | |
*************************** 9. row *************************** | |
Variable_name: wsrep_repl_other_bytes | |
Value: 0 | |
*************************** 10. row *************************** | |
Variable_name: wsrep_received | |
Value: 7 | |
*************************** 11. row *************************** | |
Variable_name: wsrep_received_bytes | |
Value: 797 | |
*************************** 12. row *************************** | |
Variable_name: wsrep_local_commits | |
Value: 0 | |
*************************** 13. row *************************** | |
Variable_name: wsrep_local_cert_failures | |
Value: 0 | |
*************************** 14. row *************************** | |
Variable_name: wsrep_local_replays | |
Value: 0 | |
*************************** 15. row *************************** | |
Variable_name: wsrep_local_send_queue | |
Value: 0 | |
*************************** 16. row *************************** | |
Variable_name: wsrep_local_send_queue_max | |
Value: 1 | |
*************************** 17. row *************************** | |
Variable_name: wsrep_local_send_queue_min | |
Value: 0 | |
*************************** 18. row *************************** | |
Variable_name: wsrep_local_send_queue_avg | |
Value: 0.000000 | |
*************************** 19. row *************************** | |
Variable_name: wsrep_local_recv_queue | |
Value: 0 | |
*************************** 20. row *************************** | |
Variable_name: wsrep_local_recv_queue_max | |
Value: 1 | |
*************************** 21. row *************************** | |
Variable_name: wsrep_local_recv_queue_min | |
Value: 0 | |
*************************** 22. row *************************** | |
Variable_name: wsrep_local_recv_queue_avg | |
Value: 0.000000 | |
*************************** 23. row *************************** | |
Variable_name: wsrep_local_cached_downto | |
Value: 0 | |
*************************** 24. row *************************** | |
Variable_name: wsrep_flow_control_paused_ns | |
Value: 0 | |
*************************** 25. row *************************** | |
Variable_name: wsrep_flow_control_paused | |
Value: 0.000000 | |
*************************** 26. row *************************** | |
Variable_name: wsrep_flow_control_sent | |
Value: 0 | |
*************************** 27. row *************************** | |
Variable_name: wsrep_flow_control_recv | |
Value: 0 | |
*************************** 28. row *************************** | |
Variable_name: wsrep_cert_deps_distance | |
Value: 0.000000 | |
*************************** 29. row *************************** | |
Variable_name: wsrep_apply_oooe | |
Value: 0.000000 | |
*************************** 30. row *************************** | |
Variable_name: wsrep_apply_oool | |
Value: 0.000000 | |
*************************** 31. row *************************** | |
Variable_name: wsrep_apply_window | |
Value: 0.000000 | |
*************************** 32. row *************************** | |
Variable_name: wsrep_commit_oooe | |
Value: 0.000000 | |
*************************** 33. row *************************** | |
Variable_name: wsrep_commit_oool | |
Value: 0.000000 | |
*************************** 34. row *************************** | |
Variable_name: wsrep_commit_window | |
Value: 0.000000 | |
*************************** 35. row *************************** | |
Variable_name: wsrep_local_state | |
Value: 4 | |
*************************** 36. row *************************** | |
Variable_name: wsrep_local_state_comment | |
Value: Synced | |
*************************** 37. row *************************** | |
Variable_name: wsrep_cert_index_size | |
Value: 0 | |
*************************** 38. row *************************** | |
Variable_name: wsrep_cert_bucket_count | |
Value: 22 | |
*************************** 39. row *************************** | |
Variable_name: wsrep_gcache_pool_size | |
Value: 1584 | |
*************************** 40. row *************************** | |
Variable_name: wsrep_causal_reads | |
Value: 0 | |
*************************** 41. row *************************** | |
Variable_name: wsrep_cert_interval | |
Value: 0.000000 | |
*************************** 42. row *************************** | |
Variable_name: wsrep_incoming_addresses | |
Value: 192.168.3.129:3306,192.168.3.128:3306,192.168.3.130:3306 | |
*************************** 43. row *************************** | |
Variable_name: wsrep_evs_delayed | |
Value: | |
*************************** 44. row *************************** | |
Variable_name: wsrep_evs_evict_list | |
Value: | |
*************************** 45. row *************************** | |
Variable_name: wsrep_evs_repl_latency | |
Value: 0/0/0/0/0 | |
*************************** 46. row *************************** | |
Variable_name: wsrep_evs_state | |
Value: OPERATIONAL | |
*************************** 47. row *************************** | |
Variable_name: wsrep_gcomm_uuid | |
Value: ff5c9d60-3171-11e7-a1d2-236e2814c35b | |
*************************** 48. row *************************** | |
Variable_name: wsrep_cluster_conf_id | |
Value: 3 | |
*************************** 49. row *************************** | |
Variable_name: wsrep_cluster_size | |
Value: 3 | |
*************************** 50. row *************************** | |
Variable_name: wsrep_cluster_state_uuid | |
Value: f4cb2250-30ac-11e7-8315-17918c780c9e | |
*************************** 51. row *************************** | |
Variable_name: wsrep_cluster_status | |
Value: Primary | |
*************************** 52. row *************************** | |
Variable_name: wsrep_connected | |
Value: ON | |
*************************** 53. row *************************** | |
Variable_name: wsrep_local_bf_aborts | |
Value: 0 | |
*************************** 54. row *************************** | |
Variable_name: wsrep_local_index | |
Value: 2 | |
*************************** 55. row *************************** | |
Variable_name: wsrep_provider_name | |
Value: Galera | |
*************************** 56. row *************************** | |
Variable_name: wsrep_provider_vendor | |
Value: Codership Oy <info@codership.com> | |
*************************** 57. row *************************** | |
Variable_name: wsrep_provider_version | |
Value: 3.12.2(rf3e626d) | |
*************************** 58. row *************************** | |
Variable_name: wsrep_ready | |
Value: ON | |
58 rows in set (0.00 sec) |
新加入节点
只需要安装好 Percona-XtraDB-Cluster,并且在 my.cnf 添加 PXC 配置节即可
报错处理
(1)加载 so 文件加不到
如:
Installing MySQL system tables... | |
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libssl.so.6: cannot open | |
shared object file: No such file or directory |
可能的问题:
openssl 没装:
yum install –y openssl openssl-devel
如果提示已经安装,可以使用
updatedb | |
locate libssl.so |
然后用
ldd /usr/local/mysql/bin/mysqld
查看还有那些依赖需安装
确认一下:
看样子都是存在的,但版本不对。做一个软链就可以修复了。
#cd /usr/lib64/
最后在用 ldd /usr/local/mysql/bin/mysqld 去确认是否有没加载的 so, 如果存在同样的方法修复。
(2)安装中 socat 找不到
/usr/local/pxc_56/bin/mysqld_safe --defaults-file=/home/mysql/my3307.cnf --ledir=/usr/local/pxc_56/bin/ & | |
有可能启动会提示:WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/usr/local/pxc_56//bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/opt/lamp/mysql/bin:/root/bi | |
n (20160509 10:58:04.404) | |
2016-05-09 10:58:04 54809 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.238' --datadir '/home/mysql/I3307/' --defaults-file '/home/mysql/I3307/my3307.cnf' --defaults-group-suffix '' --parent '54809' '' | |
Read: '(null)' | |
2016-05-09 10:58:04 54809 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.238' --datadir '/home/mysql/I3307/' --de | |
faults-file '/home/mysql/I3307/my3307.cnf' --defaults-group-suffix '' --parent '54809' '' : 2 (No such file or directory) | |
2016-05-09 10:58:04 54809 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable. | |
2016-05-09 10:58:04 54809 [ERROR] Aborting |
yum install -y socat
因为在 my.cnf 里写错 ip,导致出现下面错误,改正 my.cnf 就可以了
wsrep_cluster_address=gcomm://192.168.2.130,192.168.3.128,192.168.0.129
wsrep_node_address=192.168.1.130
2017-05-05 09:42:33 2902 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out) | |
at gcomm/src/pc.cpp:connect():162 | |
2017-05-05 09:42:33 2902 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():206: Failed to open backend connection: -110 (Connection timed out) | |
2017-05-05 09:42:33 2902 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1379: Failed to open channel 'pxc-dongzheng' at 'gcomm://192.168.2.130,192.168.3.128,192.168.0.129': -110 (Connection timed out) | |
2017-05-05 09:42:33 2902 [ERROR] WSREP: gcs connect failed: Connection timed out | |
2017-05-05 09:42:33 2902 [ERROR] WSREP: wsrep::connect(gcomm://192.168.2.130,192.168.3.128,192.168.0.129) failed: 7 | |
2017-05-05 09:42:33 2902 [ERROR] Aborting |
关于 PXC 版本
如果是安装 5.6.24 版本或以上的需要下载 openssl
CentOS6 和 centos7 需要 openssl 版本 1.0
https://www.percona.com/doc/percona-server/5.6/installation.html#installing-percona-server-from-a-binary-tarball
In Percona Server 5.6.24-72.2 and newer, the single binary tarball was replaced with multiple tarballs depending on the OpenSSL library available in the distribution:
ssl100 – for all Debian/Ubuntu versions except Debian Squeeze (libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0 (0x00007f2e389a5000));
ssl098 – only for Debian Squeeze (libssl.so.0.9.8 => /usr/lib/libssl.so.0.9.8 (0x00007f9b30db6000));
ssl101 – for CentOS 6 and CentOS 7 (libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007facbe8c4000));
ssl098e – to be used only for CentOS 5 (libssl.so.6 => /lib64/libssl.so.6 (0x00002aed5b64d000)).
下载地址
https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/LATEST/binary/tarball/
本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-05/143501.htm
最近公司某客户要求我们的数据库搭建 PXC 集群以保证他们的系统高性能和搞稳定性,以后花费了一些时间去搭建和测试,也踩过一些坑,准备分享出来
系统:CentOS6.6
Percona XtraDB Cluster:5.6.26
建议关闭 iptables,4 个端口 3306,4444,4567,4568
Percona XtraDB Cluster 环境所涉及的端口:
#MySQL 实例端口
1. Regular MySQL port, default 3306.
#Percona XtraDB Cluster 相互通讯的端口
2. Port for group communication, default 4567. It can be changed by the option:
wsrep_provider_options =”gmcast.listen_addr=tcp://0.0.0.0:4010; “
# 用于 SST 传送的端口
3. Port for State Transfer, default 4444. It can be changed by the option:
wsrep_sst_receive_address=10.11.12.205:5555
# 用于 IST 传送的端口
4. Port for Incremental State Transfer, default port for group communication + 1 (4568). It can be changed by the option:
wsrep_provider_options = “ist.recv_addr=10.11.12.206:7777; “
node1 192.168.3.130
node2 192.168.3.129
node3 192.168.3.128
1. 安装软件依赖包(添加 repl 源,再安装依赖,3 台 server 操作一样。)
rpm -ivh http://dl.Fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm | |
yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes socat nc -y |
2. 安装 xtrabackup(PXC 同步数据需要用到)
yum install -y http://www.percona.com/downloads/percona-release/RedHat/0.1-3/percona-release-0.1-3.noarch.rpm | |
yum install -y percona-xtrabackup-24 |
3. 下载安装 Percona-XtraDB-Cluster
# 安装开发包
yum install -y libaio* | |
yum groupinstall -y 'Development tools' | |
yum install -y libssl.so.6 | |
ln -sf /usr/lib64/libssl.so.10 /usr/lib64/libssl.so.6 | |
ln -sf /usr/lib64/libcrypto.so.10 /usr/lib64/libcrypto.so.6 | |
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/Percona-XtraDB-Cluster-5.6.26-25.12/binary/tarball/Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz | |
tar zxvf Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz | |
useradd -M -s /sbin/nologin mysql | |
mkdir -p /usr/local/pxc | |
mkdir -p /data/pxc/mysql3306/{data,tmp,logs} | |
mv /data/download/Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64/* /usr/local/pxc/ | |
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql | |
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile | |
source /etc/profile | |
chown -R mysql:mysql /data/pxc/mysql3306/ | |
chown -R mysql:mysql /usr/local/pxc |
修改 my.cnf 配置文件,注意在 [mysqld] 段落添加 PXC 的参数:
vi /etc/my.cnf
130
[client] | |
port = 3306 | |
socket = /data/mysql/mysql3306/tmp/mysql.sock | |
# The MySQL server | |
[mysqld] | |
#########Basic################## | |
explicit_defaults_for_timestamp=true | |
port = 3306 | |
user = mysql | |
basedir = /usr/local/mysql | |
datadir = /data/mysql/mysql3306/data | |
tmpdir = /data/mysql/mysql3306/tmp | |
pid-file = /data/mysql/mysql3306/tmp/mysql.pid | |
socket = /data/mysql/mysql3306/tmp/mysql.sock | |
#skip-grant-tables | |
#character set | |
character_set_server = utf8 | |
open_files_limit = 65535 | |
back_log = 500 | |
#event_scheduler = ON | |
#lower_case_table_names=1 | |
skip-external-locking | |
skip_name_resolve = 1 | |
default-storage-engine = InnoDB | |
#timeout | |
wait_timeout=1000 | |
interactive_timeout=1000 | |
connect_timeout = 20 | |
server-id =1303306 #ip 最后一位 + 端口号 = 1003376 | |
#percona 的--recursion-method slavehost 模式 | |
#report_host = 10.105.9.115 | |
#report_port = 3306 | |
#plugin | |
plugin-load="semisync_master.so;semisync_slave.so" | |
#########error log############# | |
log-error = /data/mysql/mysql3306/logs/error.log | |
log-warnings = 2 | |
#########general log############# | |
#general_log=1 | |
#general_log_file=/data/mysql/mysql3306/logs/mysql.log | |
#########slow log############# | |
slow_query_log = 1 | |
long_query_time=1 | |
slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow | |
############# for replication################### | |
log-bin = /data/mysql/mysql3306/logs/mysql-bin | |
binlog_format = row | |
max_binlog_size = 500M | |
binlog_cache_size = 2M | |
max_binlog_cache_size = 2M | |
expire-logs-days = 7 | |
slave-net-timeout=30 | |
log_bin_trust_function_creators = 1 | |
log-slave-updates = 1 | |
skip-slave-start = 1 | |
#read_only =1 #从上设置,5.7 是 super_read_only | |
#GTID | |
gtid-mode = on | |
binlog_gtid_simple_recovery=1 | |
enforce_gtid_consistency=1 | |
#relay log | |
relay-log = /data/mysql/mysql3306/logs/mysql-relay | |
relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index | |
max-relay-log-size = 500M | |
#replication crash safe | |
sync_master_info = 1 | |
sync_relay_log_info = 1 | |
sync_relay_log = 1 | |
relay_log_recovery = 1 | |
master_info_repository = TABLE | |
relay_log_info_repository = TABLE | |
#semisync 动态开启 主从切换的时候用 | |
#rpl_semi_sync_master_enabled = 1 | |
#rpl_semi_sync_master_wait_no_slave = 1 | |
#rpl_semi_sync_master_timeout = 1000 | |
#rpl_semi_sync_slave_enabled = 1 | |
#rpl_semi_sync_master_timeout = 100000000 #不要超时, 防止切异步, 保证主从数据的完全一致性, 默认是 10000 | |
#rpl_semi_sync_master_wait_point = 'after_sync' #5.7 新增参数 after_sync 5.7 增强半同步 after_commit5.6 普通半同步 | |
#rpl_semi_sync_master_wait_for_slave_count = 2 #5.7 新增参数 等待多少个从库接收到 binlog | |
#ignore | |
#replicate-ignore-db = 'school','school2' | |
#replicate-do-db = 'school','school2' | |
#Multi-threaded Slave | |
#slave_parallel_workers=16 | |
#slave-parallel-type=LOGICAL_CLOCK | |
#slave-parallel-type=DATABASE | |
#replication error | |
#slave-skip-errors=1007,1051,1062 | |
#######per_thread_buffers##################### | |
max_connections=1100 | |
max_user_connections=1000 | |
max_connect_errors=1000 | |
#myisam_recover | |
key_buffer_size = 64M | |
max_allowed_packet = 16M | |
#table_cache = 3096 | |
table_open_cache = 6144 | |
table_definition_cache = 4096 | |
read_buffer_size = 1M | |
join_buffer_size = 128K | |
read_rnd_buffer_size = 1M | |
#myisam | |
sort_buffer_size = 128K | |
myisam_max_sort_file_size = 10G | |
myisam_repair_threads = 1 | |
myisam_sort_buffer_size = 32M | |
tmp_table_size = 32M | |
max_heap_table_size = 64M | |
query_cache_type=0 | |
query_cache_size = 0 | |
bulk_insert_buffer_size = 32M | |
thread_cache_size = 64 | |
#thread_concurrency = 32 | |
thread_stack = 192K | |
###############InnoDB########################### | |
innodb_data_home_dir = /data/mysql/mysql3306/data | |
innodb_log_group_home_dir = /data/mysql/mysql3306/logs | |
innodb_data_file_path = ibdata1:1000M:autoextend | |
innodb_buffer_pool_size = 1G #根据内存大小设置 | |
innodb_buffer_pool_instances = 8 | |
#innodb_additional_mem_pool_size = 16M | |
innodb_log_file_size = 500M | |
innodb_log_buffer_size = 16M | |
innodb_log_files_in_group = 3 | |
innodb_flush_log_at_trx_commit = 1 | |
sync_binlog = 1 | |
innodb_lock_wait_timeout = 10 | |
innodb_sync_spin_loops = 40 | |
innodb_max_dirty_pages_pct = 80 | |
innodb_support_xa = 1 | |
innodb_thread_concurrency = 0 | |
innodb_thread_sleep_delay = 500 | |
innodb_concurrency_tickets = 1000 | |
innodb_flush_method = O_DIRECT | |
innodb_file_per_table = 1 | |
innodb_read_io_threads = 16 | |
innodb_write_io_threads = 16 | |
innodb_io_capacity = 800 #机械盘 800 ssd 2000 | |
innodb_flush_neighbors = 1 | |
innodb_file_format = Barracuda | |
innodb_purge_threads=1 #5.6 只能有一个,5.7 可以设置多个 | |
innodb_purge_batch_size = 32 | |
innodb_old_blocks_pct=75 | |
innodb_change_buffering=all | |
innodb_stats_on_metadata=OFF | |
innodb_print_all_deadlocks = 1 | |
#innodb_status_output=1 | |
#innodb_status_output_locks=1 | |
performance_schema=0 #是否打开 P_S 库进行性能收集 | |
transaction_isolation = READ-COMMITTED | |
#innodb_force_recovery=0 | |
#innodb_fast_shutdown=1 | |
#innodb_status_file = 1 #实时输出 show engine innodb status 到 innodb_status 文件 | |
############# PXC ##################### | |
innodb_autoinc_lock_mode=2 | |
wsrep_cluster_name=pxc-dongzheng | |
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so | |
wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129 | |
wsrep_node_address=192.168.3.130 | |
wsrep_slave_threads=2 | |
wsrep_sst_auth=sst:dongzheng | |
wsrep_sst_method=xtrabackup-v2 | |
wsrep_provider_options="debug=1" | |
[mysqldump] | |
quick | |
max_allowed_packet = 128M | |
[mysql] | |
no-auto-rehash | |
max_allowed_packet = 128M | |
prompt = '(product)\u@\h:\p [\d]> ' | |
default_character_set = utf8 | |
[myisamchk] | |
key_buffer_size = 64M | |
sort_buffer_size = 512k | |
read_buffer = 2M | |
write_buffer = 2M | |
[mysqlhotcopy] | |
interactive-timeout | |
[mysqld_safe] | |
#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so |
129
[client] | |
port = 3306 | |
socket = /data/mysql/mysql3306/tmp/mysql.sock | |
# The MySQL server | |
[mysqld] | |
#########Basic################## | |
explicit_defaults_for_timestamp=true | |
port = 3306 | |
user = mysql | |
basedir = /usr/local/mysql | |
datadir = /data/mysql/mysql3306/data | |
tmpdir = /data/mysql/mysql3306/tmp | |
pid-file = /data/mysql/mysql3306/tmp/mysql.pid | |
socket = /data/mysql/mysql3306/tmp/mysql.sock | |
#skip-grant-tables | |
#character set | |
character_set_server = utf8 | |
open_files_limit = 65535 | |
back_log = 500 | |
#event_scheduler = ON | |
#lower_case_table_names=1 | |
skip-external-locking | |
skip_name_resolve = 1 | |
default-storage-engine = InnoDB | |
#timeout | |
wait_timeout=1000 | |
interactive_timeout=1000 | |
connect_timeout = 20 | |
server-id =1293306 #ip 最后一位 + 端口号 = 1003376 | |
#percona 的--recursion-method slavehost 模式 | |
#report_host = 10.105.9.115 | |
#report_port = 3306 | |
#plugin | |
plugin-load="semisync_master.so;semisync_slave.so" | |
#########error log############# | |
log-error = /data/mysql/mysql3306/logs/error.log | |
log-warnings = 2 | |
#########general log############# | |
#general_log=1 | |
#general_log_file=/data/mysql/mysql3306/logs/mysql.log | |
#########slow log############# | |
slow_query_log = 1 | |
long_query_time=1 | |
slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow | |
############# for replication################### | |
log-bin = /data/mysql/mysql3306/logs/mysql-bin | |
binlog_format = row | |
max_binlog_size = 500M | |
binlog_cache_size = 2M | |
max_binlog_cache_size = 2M | |
expire-logs-days = 7 | |
slave-net-timeout=30 | |
log_bin_trust_function_creators = 1 | |
log-slave-updates = 1 | |
skip-slave-start = 1 | |
#read_only =1 #从上设置,5.7 是 super_read_only | |
#GTID | |
gtid-mode = on | |
binlog_gtid_simple_recovery=1 | |
enforce_gtid_consistency=1 | |
#relay log | |
relay-log = /data/mysql/mysql3306/logs/mysql-relay | |
relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index | |
max-relay-log-size = 500M | |
#replication crash safe | |
sync_master_info = 1 | |
sync_relay_log_info = 1 | |
sync_relay_log = 1 | |
relay_log_recovery = 1 | |
master_info_repository = TABLE | |
relay_log_info_repository = TABLE | |
#semisync 动态开启 主从切换的时候用 | |
#rpl_semi_sync_master_enabled = 1 | |
#rpl_semi_sync_master_wait_no_slave = 1 | |
#rpl_semi_sync_master_timeout = 1000 | |
#rpl_semi_sync_slave_enabled = 1 | |
#rpl_semi_sync_master_timeout = 100000000 #不要超时, 防止切异步, 保证主从数据的完全一致性, 默认是 10000 | |
#rpl_semi_sync_master_wait_point = 'after_sync' #5.7 新增参数 after_sync 5.7 增强半同步 after_commit5.6 普通半同步 | |
#rpl_semi_sync_master_wait_for_slave_count = 2 #5.7 新增参数 等待多少个从库接收到 binlog | |
#ignore | |
#replicate-ignore-db = 'school','school2' | |
#replicate-do-db = 'school','school2' | |
#Multi-threaded Slave | |
#slave_parallel_workers=16 | |
#slave-parallel-type=LOGICAL_CLOCK | |
#slave-parallel-type=DATABASE | |
#replication error | |
#slave-skip-errors=1007,1051,1062 | |
#######per_thread_buffers##################### | |
max_connections=1100 | |
max_user_connections=1000 | |
max_connect_errors=1000 | |
#myisam_recover | |
key_buffer_size = 64M | |
max_allowed_packet = 16M | |
#table_cache = 3096 | |
table_open_cache = 6144 | |
table_definition_cache = 4096 | |
read_buffer_size = 1M | |
join_buffer_size = 128K | |
read_rnd_buffer_size = 1M | |
#myisam | |
sort_buffer_size = 128K | |
myisam_max_sort_file_size = 10G | |
myisam_repair_threads = 1 | |
myisam_sort_buffer_size = 32M | |
tmp_table_size = 32M | |
max_heap_table_size = 64M | |
query_cache_type=0 | |
query_cache_size = 0 | |
bulk_insert_buffer_size = 32M | |
thread_cache_size = 64 | |
#thread_concurrency = 32 | |
thread_stack = 192K | |
###############InnoDB########################### | |
innodb_data_home_dir = /data/mysql/mysql3306/data | |
innodb_log_group_home_dir = /data/mysql/mysql3306/logs | |
innodb_data_file_path = ibdata1:1000M:autoextend | |
innodb_buffer_pool_size = 1G #根据内存大小设置 | |
innodb_buffer_pool_instances = 8 | |
#innodb_additional_mem_pool_size = 16M | |
innodb_log_file_size = 500M | |
innodb_log_buffer_size = 16M | |
innodb_log_files_in_group = 3 | |
innodb_flush_log_at_trx_commit = 1 | |
sync_binlog = 1 | |
innodb_lock_wait_timeout = 10 | |
innodb_sync_spin_loops = 40 | |
innodb_max_dirty_pages_pct = 80 | |
innodb_support_xa = 1 | |
innodb_thread_concurrency = 0 | |
innodb_thread_sleep_delay = 500 | |
innodb_concurrency_tickets = 1000 | |
innodb_flush_method = O_DIRECT | |
innodb_file_per_table = 1 | |
innodb_read_io_threads = 16 | |
innodb_write_io_threads = 16 | |
innodb_io_capacity = 800 #机械盘 800 ssd 2000 | |
innodb_flush_neighbors = 1 | |
innodb_file_format = Barracuda | |
innodb_purge_threads=1 #5.6 只能有一个,5.7 可以设置多个 | |
innodb_purge_batch_size = 32 | |
innodb_old_blocks_pct=75 | |
innodb_change_buffering=all | |
innodb_stats_on_metadata=OFF | |
innodb_print_all_deadlocks = 1 | |
#innodb_status_output=1 | |
#innodb_status_output_locks=1 | |
performance_schema=0 #是否打开 P_S 库进行性能收集 | |
transaction_isolation = READ-COMMITTED | |
#innodb_force_recovery=0 | |
#innodb_fast_shutdown=1 | |
#innodb_status_file = 1 #实时输出 show engine innodb status 到 innodb_status 文件 | |
############# PXC ##################### | |
innodb_autoinc_lock_mode=2 | |
wsrep_cluster_name=pxc-dongzheng | |
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so | |
wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129 | |
wsrep_node_address=192.168.3.129 | |
wsrep_slave_threads=2 | |
wsrep_sst_auth=sst:dongzheng | |
wsrep_sst_method=xtrabackup-v2 | |
[mysqldump] | |
quick | |
max_allowed_packet = 128M | |
[mysql] | |
no-auto-rehash | |
max_allowed_packet = 128M | |
prompt = '(product)\u@\h:\p [\d]> ' | |
default_character_set = utf8 | |
[myisamchk] | |
key_buffer_size = 64M | |
sort_buffer_size = 512k | |
read_buffer = 2M | |
write_buffer = 2M | |
[mysqlhotcopy] | |
interactive-timeout | |
[mysqld_safe] | |
#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so |
View Code
128

[client] | |
port = 3306 | |
socket = /data/mysql/mysql3306/tmp/mysql.sock | |
# The MySQL server | |
[mysqld] | |
#########Basic################## | |
explicit_defaults_for_timestamp=true | |
port = 3306 | |
user = mysql | |
basedir = /usr/local/mysql | |
datadir = /data/mysql/mysql3306/data | |
tmpdir = /data/mysql/mysql3306/tmp | |
pid-file = /data/mysql/mysql3306/tmp/mysql.pid | |
socket = /data/mysql/mysql3306/tmp/mysql.sock | |
#skip-grant-tables | |
#character set | |
character_set_server = utf8 | |
open_files_limit = 65535 | |
back_log = 500 | |
#event_scheduler = ON | |
#lower_case_table_names=1 | |
skip-external-locking | |
skip_name_resolve = 1 | |
default-storage-engine = InnoDB | |
#timeout | |
wait_timeout=1000 | |
interactive_timeout=1000 | |
connect_timeout = 20 | |
server-id =1283306 #ip 最后一位 + 端口号 = 1003376 | |
#percona 的--recursion-method slavehost 模式 | |
#report_host = 10.105.9.115 | |
#report_port = 3306 | |
#plugin | |
plugin-load="semisync_master.so;semisync_slave.so" | |
#########error log############# | |
log-error = /data/mysql/mysql3306/logs/error.log | |
log-warnings = 2 | |
#########general log############# | |
#general_log=1 | |
#general_log_file=/data/mysql/mysql3306/logs/mysql.log | |
#########slow log############# | |
slow_query_log = 1 | |
long_query_time=1 | |
slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow | |
############# for replication################### | |
log-bin = /data/mysql/mysql3306/logs/mysql-bin | |
binlog_format = row | |
max_binlog_size = 500M | |
binlog_cache_size = 2M | |
max_binlog_cache_size = 2M | |
expire-logs-days = 7 | |
slave-net-timeout=30 | |
log_bin_trust_function_creators = 1 | |
log-slave-updates = 1 | |
skip-slave-start = 1 | |
#read_only =1 #从上设置,5.7 是 super_read_only | |
#GTID | |
gtid-mode = on | |
binlog_gtid_simple_recovery=1 | |
enforce_gtid_consistency=1 | |
#relay log | |
relay-log = /data/mysql/mysql3306/logs/mysql-relay | |
relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index | |
max-relay-log-size = 500M | |
#replication crash safe | |
sync_master_info = 1 | |
sync_relay_log_info = 1 | |
sync_relay_log = 1 | |
relay_log_recovery = 1 | |
master_info_repository = TABLE | |
relay_log_info_repository = TABLE | |
#semisync 动态开启 主从切换的时候用 | |
#rpl_semi_sync_master_enabled = 1 | |
#rpl_semi_sync_master_wait_no_slave = 1 | |
#rpl_semi_sync_master_timeout = 1000 | |
#rpl_semi_sync_slave_enabled = 1 | |
#rpl_semi_sync_master_timeout = 100000000 #不要超时, 防止切异步, 保证主从数据的完全一致性, 默认是 10000 | |
#rpl_semi_sync_master_wait_point = 'after_sync' #5.7 新增参数 after_sync 5.7 增强半同步 after_commit5.6 普通半同步 | |
#rpl_semi_sync_master_wait_for_slave_count = 2 #5.7 新增参数 等待多少个从库接收到 binlog | |
#ignore | |
#replicate-ignore-db = 'school','school2' | |
#replicate-do-db = 'school','school2' | |
#Multi-threaded Slave | |
#slave_parallel_workers=16 | |
#slave-parallel-type=LOGICAL_CLOCK | |
#slave-parallel-type=DATABASE | |
#replication error | |
#slave-skip-errors=1007,1051,1062 | |
#######per_thread_buffers##################### | |
max_connections=1100 | |
max_user_connections=1000 | |
max_connect_errors=1000 | |
#myisam_recover | |
key_buffer_size = 64M | |
max_allowed_packet = 16M | |
#table_cache = 3096 | |
table_open_cache = 6144 | |
table_definition_cache = 4096 | |
read_buffer_size = 1M | |
join_buffer_size = 128K | |
read_rnd_buffer_size = 1M | |
#myisam | |
sort_buffer_size = 128K | |
myisam_max_sort_file_size = 10G | |
myisam_repair_threads = 1 | |
myisam_sort_buffer_size = 32M | |
tmp_table_size = 32M | |
max_heap_table_size = 64M | |
query_cache_type=0 | |
query_cache_size = 0 | |
bulk_insert_buffer_size = 32M | |
thread_cache_size = 64 | |
#thread_concurrency = 32 | |
thread_stack = 192K | |
###############InnoDB########################### | |
innodb_data_home_dir = /data/mysql/mysql3306/data | |
innodb_log_group_home_dir = /data/mysql/mysql3306/logs | |
innodb_data_file_path = ibdata1:1000M:autoextend | |
innodb_buffer_pool_size = 1G #根据内存大小设置 | |
innodb_buffer_pool_instances = 8 | |
#innodb_additional_mem_pool_size = 16M | |
innodb_log_file_size = 500M | |
innodb_log_buffer_size = 16M | |
innodb_log_files_in_group = 3 | |
innodb_flush_log_at_trx_commit = 1 | |
sync_binlog = 1 | |
innodb_lock_wait_timeout = 10 | |
innodb_sync_spin_loops = 40 | |
innodb_max_dirty_pages_pct = 80 | |
innodb_support_xa = 1 | |
innodb_thread_concurrency = 0 | |
innodb_thread_sleep_delay = 500 | |
innodb_concurrency_tickets = 1000 | |
innodb_flush_method = O_DIRECT | |
innodb_file_per_table = 1 | |
innodb_read_io_threads = 16 | |
innodb_write_io_threads = 16 | |
innodb_io_capacity = 800 #机械盘 800 ssd 2000 | |
innodb_flush_neighbors = 1 | |
innodb_file_format = Barracuda | |
innodb_purge_threads=1 #5.6 只能有一个,5.7 可以设置多个 | |
innodb_purge_batch_size = 32 | |
innodb_old_blocks_pct=75 | |
innodb_change_buffering=all | |
innodb_stats_on_metadata=OFF | |
innodb_print_all_deadlocks = 1 | |
#innodb_status_output=1 | |
#innodb_status_output_locks=1 | |
performance_schema=0 #是否打开 P_S 库进行性能收集 | |
transaction_isolation = READ-COMMITTED | |
#innodb_force_recovery=0 | |
#innodb_fast_shutdown=1 | |
#innodb_status_file = 1 #实时输出 show engine innodb status 到 innodb_status 文件 | |
############# PXC ##################### | |
innodb_autoinc_lock_mode=2 | |
wsrep_cluster_name=pxc-dongzheng | |
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so | |
wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129 | |
wsrep_node_address=192.168.3.128 | |
wsrep_slave_threads=2 | |
wsrep_sst_auth=sst:dongzheng | |
wsrep_sst_method=xtrabackup-v2 | |
wsrep_provider_options="debug=1;socket.checksum=1" | |
[mysqldump] | |
quick | |
max_allowed_packet = 128M | |
[mysql] | |
no-auto-rehash | |
max_allowed_packet = 128M | |
prompt = '(product)\u@\h:\p [\d]> ' | |
default_character_set = utf8 | |
[myisamchk] | |
key_buffer_size = 64M | |
sort_buffer_size = 512k | |
read_buffer = 2M | |
write_buffer = 2M | |
[mysqlhotcopy] | |
interactive-timeout | |
[mysqld_safe] | |
#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so |
View Code
cd /usr/local/pxc/ | |
./scripts/mysql_install_db --defaults-file=/etc/my.cnf |
看到两次 ok
# 修改启动脚本
vi /etc/init.d/mysql
basedir=/usr/local/pxc | |
datadir=/data/pxc/mysql3306/data |
4. 启动,进行授权操作,对于第一个节点必须以特殊方式启动,如下:
/etc/init.d/mysql --help | |
Usage: mysql {start|stop|restart|restart-bootstrap|reload|force-reload|status|bootstrap-pxc} [MySQL (Percona XtraDB Cluster) options ] |
第一个节点启动
/etc/init.d/mysql bootstrap-pxc
5. 安全加固
连接 mysql:mysql -uroot -p -S /data/mysql/mysql3306/tmp/mysql.sock
delete from mysql.user where user!=’root’ or host!=’localhost’;
truncate table mysql.db;
drop database test;
进行授权,这里用户名密码是 my.cnf 里定义的用户名 sst 密码 dongzheng
GRANT ALL PRIVILEGES ON *.* TO ‘sst’@’%’ identified by ‘dongzheng’;
use mysql;
UPDATE user SET password=PASSWORD(‘123456′) WHERE user=’root’;
flush privileges;
PXC 状态确认
show global status like ‘wsrep%’;
6.netstat 进行查看,可以发现启动两个端口
netstat -lntp | grep mysql
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2964/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2964/mysqld
更多详情见请继续阅读下一页的精彩内容:http://www.linuxidc.com/Linux/2017-05/143501p2.htm
