-
my.cnf讲解： pxc部分，其他都一样
[client]
port=3306
socket = /mysql/data/3306/mysql.sock
[mysql]
no-beep
prompt="\u@itpux \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8
[mysqld]
########basic settings########
server-id=513306
port=3306
user = mysql
bind_address= 0.0.0.0
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
socket = /mysql/data/3306/mysql.sock
pid-file=/mysql/data/3306/mysql.pid
character-set-server=utf8
skip-character-set-client-handshake=1
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
#event_scheduler =1
query_cache_type = 1
query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M
back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000
########log settings########
log-output=FILE
general_log = 0
general_log_file=/mysql/log/3306/mysqldb-general.err
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/mysqldb-query.err
long_query_time=10
log-error=/mysql/log/3306/mysqldb-error.err
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100
#pxc parameter
log_bin=/mysql/log/3306/binlog/mysqldb-binlog
log_bin_index=/mysql/log/3306/binlog/mysqldb-binlog.index
#pxc必须是行格式
binlog_format=ROW
#主库的记录信息
log-slave-updates = 1
innodb_locks_unsafe_for_binlog = 1
#自增模式，主键自增模式设置为交叉模式
innodb_autoinc_lock_mode = 2
#这个三个参数根据实际情况决定（如果对数据要求高，建议都设置成1）
sync_binlog=0 #事务已提交，而无需同步到磁盘
innodb_flush_method = O_DIRECT #避免双缓冲技术
innodb_flush_log_at_trx_commit=0 #0意味着刷新到磁盘，但不同步 （提交时不执行实际IO）
#同一个cluster ，wsrep_cluster_name 要一致
wsrep_cluster_name=itpux_mysql
#线程数量，cpu核数的两倍
wsrep_slave_threads=2
#galera的支持库
wsrep_provider=/mysql/app/mysql/lib/libgalera_smm.so
#各个节点的IP，三个节点一样
wsrep_cluster_address=gcomm://192.168.1.51,192.168.1.52,192.168.1.53
#sst复制时需要知道集群中某个节点的ip，根据不通的node ip 进行修改
wsrep_sst_receive_address=192.168.1.51
#指定wsrep启动时的地址，根据不通的node ip进行修改
wsrep_node_incoming_address=192.168.1.51
#本节点地址，根据不通的node ip进行修改
wsrep_node_address=192.168.1.51
#wsrep_provider_options="gmcast.listen_addr=tcp://192.168.1.51;ist.recv_addr=192.168.1.51"
#不用ssl的方式认证的话，注释掉
#本节点在集群的唯一标识 这个要和 /etc/hosts文件下一致
wsrep_node_name=itpuxdb1
#sst复制方式
wsrep_sst_method=xtrabackup-v2
#复制时候用的用户密码
wsrep_sst_auth="sstuser:sstuser123"
#严格模式，默认就是 enforcing
pxc_strict_mode=ENFORCING
#类似于redo，默认128M,生产要配置，4/8G
#wsrep_provider_options="gcache.size=4G"
#节点应用完，事务才返回查询请求
wsrep_causal_reads=ON
########innodb settings########
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_flush_neighbors = 1
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
#innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
show_compatibility_56=on
-
Percona XtraDB Cluster 项目准备
192.168.0.151 zhangpxc01 pxc 节点 1
192.168.0.152 zhangpxc02 pxc 节点 2
192.168.0.153 zhangpxc03 pxc 节点 3
192.168.0.154 zhangpxc04 pxc 节点 4
192.168.0.61 deenfz01
192.168.0.62 deenfz02
192.168.0.65 VIP
四台机器都添加 hosts 表：
echo "127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4" > /etc/hosts
echo "::1 localhost localhost.localdomain localhost6 localhost6.localdomain6" >> /etc/hosts
echo "192.168.0.151 zhangpxc01" >> /etc/hosts
echo "192.168.0.152 zhangpxc02" >> /etc/hosts
echo "192.168.0.153 zhangpxc03" >> /etc/hosts
echo "192.168.0.154 zhangpxc04" >> /etc/hosts
echo "192.168.0.61 deenfz01" >> /etc/hosts
echo "192.168.0.62 deenfz02 ">> /etc/hosts
另外，要么关掉防火墙，要么开放端口，建议关掉，由硬件去控制对外的访问。
systemctl stop firewalld.service
– On all 3 Percona nodes we have to configure the firewall to allow the connection to ports 3306, 4444, 4567 and 4568
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=4567/tcp --permanent
firewall-cmd --zone=public --add-port=4567/udp --permanent
firewall-cmd --zone=public --add-port=4568/tcp --permanent
firewall-cmd --zone=public --add-port=4444/tcp --permanent
firewall-cmd --reload
三台服务器时间也要一样
-
Percona XtraDB Cluster 集群数据库安装和配置
######################联网的方法：用的少
rpm -Uvh https://www.percona.com/downloads/percona-release/redhat/latest/percona-release-0.1-4.noarch.rpm
yum install Percona-XtraDB-Cluster-57 -y
#########################################
不联网比较规范的安装方法-3 个节点：
mount /dev/cdrom /mnt
yum remove mariadb-libs -y
yum install -y gcc
yum install -y gcc-c++
yum install -y ncurses-devel.x86_64
yum install -y cmake.x86_64
yum install -y libaio.x86_64
yum install -y libaio-devel
yum install -y bison.x86_64
yum install -y gcc-c++.x86_64
yum install -y bind-utils
yum install -y wget
yum install -y curl
yum install -y curl-devel
yum install -y perl
yum install -y openssh-clients
yum install -y setuptool
yum install -y sysstat
yum install -y make
yum install -y libev
yum install -y redhat-lsb*
yum install -y lrzsz.x86_64 -y
yum install -y perl-DBD-MySQL
yum install -y perl-IO-Socket-SSL.noarch
yum install -y git
yum install -y scons
yum install -y socat
yum install -y check
yum install -y boost-devel
yum install -y asio-devel
yum install -y readline-devel
rm -rf /etc/my.cnf*
scp 192.168.0.151:/opt/*ercona* /opt/
cd /mysql/app
tar zxvf /opt/percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gz
mv percona-xtrabackup-2.4.11-Linux-x86_64 xtrabackup
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
cd /mysql/app
tar zxvf /opt/Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101.tar.gz
mv Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101 mysql
cp /mysql/app/xtrabackup/bin/* /usr/sbin/
echo "PATH=\$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin:$HOME/bin" >> /etc/profile
echo "PATH=\$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin:$HOME/bin" >> ~/.bash_profile
source /etc/profile
source ~/.bash_profile
xtrabackup --version
mysql --version
mkdir -p /mysql/data/3306/data
mkdir -p /mysql/log/3306/binlog
mkdir -p /mysql/log/3306/relaylog
mkdir -p /mysql/backup/backup-db
mkdir -p /mysql/backup/backup-tmp
mkdir -p /mysql/backup/backup-binlog
chown -R mysql:mysql /mysql/*
修改 mysql.server 文件，不建议开机启动脚本了 （三个文件都在百度云课件 606中）
rm -rf /etc/my.cnf*
cd /mysql/app/mysql/support-files/
mv mysql.server mysql.server.bak
cp /opt/mysql.server.pxc /mysql/app/mysql/support-files/mysql.server
chown mysql:mysql /mysql/app/mysql/support-files/mysql.server
chmod +x /mysql/app/mysql/support-files/mysql.server
cp /mysql/app/mysql/support-files/mysql.server /mysql/app/mysql/bin/mysqlpxc
mysqlpxc status
cp /opt/my.cnf /mysql/data/3306/ #这里的配置文件复制上面的
此时mysqlpxc是没有running的，需要在下面修改配置文件
-
配置 XtraDB Cluster
第一台机： (每台机器的配置文件是一样的，只有IP和名字，上面my.cnf的配置文件不一样 )
vi /mysql/data/3306/my.cnf
#pxc parameter
log_bin=/mysql/log/3306/binlog/mysqldb-binlog
log_bin_index=/mysql/log/3306/binlog/mysqldb-binlog.index
binlog_format=ROW
log-slave-updates = 1
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
sync_binlog=0
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit=0
wsrep_cluster_name=zhangpxc_mysql
wsrep_slave_threads=2
wsrep_provider=/mysql/app/mysql/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.0.151,192.168.0.152,192.168.0.153
wsrep_sst_receive_address=192.168.0.151
wsrep_node_incoming_address=192.168.0.151
wsrep_node_address=192.168.0.151
#wsrep_provider_options="gmcast.listen_addr=tcp://192.168.0.151;ist.recv_addr=192.168.0.151"
wsrep_node_name=zhangpxc01
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:sstuser123"
pxc_strict_mode=ENFORCING
#wsrep_provider_options="gcache.size=4G"
wsrep_causal_reads=ON
------------------------------------------
rm -rf /mysql/data/3306/data/*
/mysql/app/mysql/bin/mysqld --defaults-file=/mysql/data/3306/my.cnf --initialize --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data
ln -sf /mysql/data/3306/mysql.sock /tmp/mysql.sock
三种 启动方式：
/etc/init.d/mysql bootstrap-pxc
systemctl start mysql@bootstrap.service
mysqlpxc bootstrap-pxc
tail -100f /mysql/log/3306/mysqldb-error.err
tail -1000f /mysql/log/3306/mysqldb-error.err |grep password
/mysql/app/mysql/bin/mysql -uroot --password='vm0ztm:puk6W' --connect-expired-password --socket='/mysql/data/3306/mysql.sock' -e " alter user 'root'@'localhost' identified by 'root';"
/mysql/app/mysql/bin/mysql -uroot --password='root' --socket='/mysql/data/3306/mysql.sock' -e "create USER 'root'@'%' IDENTIFIED BY 'root';"
/mysql/app/mysql/bin/mysql -uroot --password='root' --socket='/mysql/data/3306/mysql.sock' -e "grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;flush privileges;"
mysql -uroot -proot
CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'sstuser123';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;
show status like 'wsrep%';
导数据报错：table with read lock with pxc_strict_mode=enforcing
mysql -uroot -proot -e " set global pxc_strict_mode='PERMISSIVE';"
mysql -uroot -proot < itpuxdb.sql
mysql -uroot -proot -e " set global pxc_strict_mode='ENFORCING';"
第二台机：
vi /mysql/data/3306/my.cnf
#pxc parameter
log_bin=/mysql/log/3306/binlog/mysqldb-binlog
log_bin_index=/mysql/log/3306/binlog/mysqldb-binlog.index
#pxc必须是行格式
binlog_format=ROW
#主库的记录信息
log-slave-updates = 1
innodb_locks_unsafe_for_binlog = 1
#自增模式，主键自增模式设置为交叉模式
innodb_autoinc_lock_mode = 2
#这个三个参数根据实际情况决定（如果对数据要求高，建议都设置成1）
sync_binlog=0 #事务已提交，而无需同步到磁盘
innodb_flush_method = O_DIRECT #避免双缓冲技术
innodb_flush_log_at_trx_commit=0 #0意味着刷新到磁盘，但不同步 （提交时不执行实际IO）
#同一个cluster ，wsrep_cluster_name 要一致
wsrep_cluster_name=zhangpxc_mysql
#线程数量，cpu核数的两倍
wsrep_slave_threads=2
#galera的支持库
wsrep_provider=/mysql/app/mysql/lib/libgalera_smm.so
#各个节点的IP，三个节点一样
wsrep_cluster_address=gcomm://192.168.0.151,192.168.0.152,192.168.0.153
#sst复制时需要知道集群中某个节点的ip，根据不通的node ip 进行修改
wsrep_sst_receive_address=192.168.0.153
#指定wsrep启动时的地址，根据不通的node ip进行修改
wsrep_node_incoming_address=192.168.0.153
#本节点地址，根据不通的node ip进行修改
wsrep_node_address=192.168.0.153
#wsrep_provider_options="gmcast.listen_addr=tcp://192.168.0.153;ist.recv_addr=192.168.0.153"
#不用ssl的方式认证的话，注释掉
#本节点在集群的唯一标识
wsrep_node_name=zhangpxc03
#sst复制方式
wsrep_sst_method=xtrabackup-v2
#复制时候用的用户密码
wsrep_sst_auth="sstuser:sstuser123"
#严格模式，默认就是 enforcing
pxc_strict_mode=ENFORCING
#类似于redo，默认128M,生产要配置，4/8G
#wsrep_provider_options="gcache.size=4G"
#节点应用完，事务才返回查询请求
wsrep_causal_reads=ON
---------------------------------------------------------
rm -rf /mysql/data/3306/data/*
ln -sf /mysql/data/3306/mysql.sock /tmp/mysql.sock
mysqlpxc start
tail -100f /mysql/log/3306/mysqldb-error.err
第三台机：
vi /mysql/data/3306/my.cnf
log_bin=/mysql/log/3306/binlog/itpuxdb-binlog
log_bin_index=/mysql/log/3306/binlog/itpuxdb-binlog.index
binlog_format=ROW
#pxc parameter
log-slave-updates = 1
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
wsrep_cluster_name=itpux_mysql
wsrep_slave_threads=2
wsrep_provider=/mysql/app/mysql/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.51,192.168.1.52,192.168.1.53
wsrep_node_address=192.168.1.53
wsrep_node_name=itpuxdb03
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:sstuser123"
pxc_strict_mode=ENFORCING
wsrep_provider_options="gcache.size=200M"
#wsrep_provider_options="gcache.size=4G"
---------------------------------------------
rm -rf /mysql/data/3306/data/*
ln -sf /mysql/data/3306/mysql.sock /tmp/mysql.sock
mysqlpxc start
ln -sf /mysql/data/3306/mysql.sock /tmp/mysql.sock
tail -100f /mysql/log/3306/mysqldb-error.err
图解： 启动好以后，每个节点都会有这么一个文件，safe_to_bootstrap :0 当集群关闭时，会显示1
-
数据测试： 三台机器都是可写的
151.
mysql -uroot -proot
create database itpuxdb1;
use itpuxdb1;
create table itpuxdb1.itpuxbak11 (id int primary key ,name varchar(40));
insert into itpuxdb1.itpuxbak11 values(1,'itpux111'),(2,'itpux112'),(3,'itpux113'),(4,'itpux114'),(5,'itpux115');
commit;
select * from itpuxdb1.itpuxbak11;
152.
create database itpuxdb2;
use itpuxdb2;
create table itpuxdb2.itpuxbak21 (id int primary key ,name varchar(40));
insert into itpuxdb2.itpuxbak21 values(1,'itpux211'),(2,'itpux212'),(3,'itpux213'),(4,'itpux214'),(5,'itpux215');
commit;
select * from itpuxdb2.itpuxbak21;
select * from itpuxdb2.itpuxbak21;
153.
create database itpuxdb3;
use itpuxdb3;
create table itpuxdb3.itpuxbak31 (id int primary key ,name varchar(40));
insert into itpuxdb3.itpuxbak31 values(1,'itpux311'),(2,'itpux312'),(3,'itpux313'),(4,'itpux314'),(5,'itpux315');
commit;
select * from itpuxdb3.itpuxbak31;
select * from itpuxdb3.itpuxbak31;
3151
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
