Pxc

1. 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

 

 

1. 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

三台服务器时间也要一样

1. 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的，需要在下面修改配置文件

1. 配置 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

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;