mysql 数据库 主从配置
一、安装第一台mysql：【mysql01】
1、安装前准备
（1）关闭selinux和防火墙（修改完重启服务器）
# vim /etc/selinux/config
SELINUX=disabled
# systemctl stop firewalld; systemctl disable firewalld
（2）创建mysql用户
# useradd mysql -s /sbin/nologin -M
（3）创数据存储目录
# mkdir -p /data/mysql
# chown -R mysql.mysql /data/mysql
2、安装
（1）安装依赖
# yum install -y perl-DBD-MySQL.x86_64 perl-IO-Socket-SSL.noarch socat.x86_64 nc lsof rsync
（2）解压二进制安装包
# cd /opt/
# tar -xzf Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl101.tar.gz -C /opt
# mv /opt/Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl101 /opt/mysql
# chown -R mysql.mysql /opt/mysql
（3）手动复制systemctl启动方式的命令文件
# \cp mysql.service mysql@.service /usr/lib/systemd/system
# \cp mysql.bootstrap /etc/sysconfig
# \cp mysql-systemd /opt/mysql/bin
# chmod +x /opt/mysql/bin/mysql-systemd
# systemctl daemon-reload
（4）配置环境变量
# echo "PATH=$PATH:/opt/mysql/bin" >> /etc/profile
# source /etc/profile
（5）初始化数据库
# /opt/mysql/bin/mysqld --initialize --basedir=/opt/mysql --datadir=/data/mysql/ --user=mysql
- 注：初始化命令会生成一个初始密码，请保存。
- 生成的初始化密码范例：2019-03-23T06:10:25.597912Z 1 [Note] A temporary password is generated for root@localhost: y.9=3J)j(yi1
3、配置数据库
需要修改wsrep_cluster_address和wsrep_node_address两个配置中的ip地址
# vim /etc/my.cnf
[client]
socket = /data/mysql/mysql.socket
[mysqld]
server-id = 1
lower_case_table_names = 1
default-storage-engine = InnoDB
port = 3306
datadir = /data/mysql
socket = /data/mysql/mysql.socket
character-set-server = utf8
log_bin=mysql-bin
binlog_format = ROW
max_binlog_size = 500M
expire_logs_days = 7
innodb_buffer_pool_size = 2G
slave-parallel-workers = 8
thread_cache_size = 600
back_log = 600
slave_net_timeout = 60
key_buffer_size = 8M
query_cache_size = 64M
join_buffer_size = 2M
sort_buffer_size = 2M
query_cache_type = 1
thread_stack = 192K
innodb_thread_concurrency = 8
connect_timeout = 300
max_connections = 6000
max_allowed_packet = 128M
max_connect_errors = 6000
wait_timeout = 240
interactive_timeout = 240
table_open_cache = 256
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_flush_log_at_trx_commit = 2
slow_query_log = 1
long_query_time = 1
lower_case_table_names = 1
skip-name-resolve
log_bin_trust_function_creators=TRUE
pxc_strict_mode=ENFORCING
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
wsrep_provider=/opt/mysql/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.51,192.168.1.52,192.168.1.53
wsrep_node_name=pxc-node01
wsrep_node_address=192.168.1.51
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc-cluster
wsrep_sst_auth=sst:U+xwf02a
wsrep_sst_method=rsync
4、启动以及赋权
（1）启动数据库
# systemctl start mysql@bootstrap.service
- 备注：以此方式启动节点，停止服务需要使用systemctl stop mysql@bootstrap.service，而不是systemctl stop mysql（此命令停止会不起作用）。
（2）修改初始密码，以及赋权sst同步账户
# mysql -uroot -p'+gUE>Txz-6(.'
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '初始密码';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sst'@'%' IDENTIFIED BY 'U+xwf02a';
Query OK, 0 rows affected, 1 warning (0.00 sec)
- 注：密码为上面初始化之后保存的密码。
5、检查启动状态
mysql> show global status where Variable_name in ('wsrep_local_index','wsrep_incoming_addresses','wsrep_cluster_status','wsrep_connected','wsrep_ready');
+--------------------------+---------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------+
| wsrep_incoming_addresses | 192.168.1.51:3306 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_index | 0 |
| wsrep_ready | ON |
+--------------------------+---------------------------------------+
5 rows in set (0.00 sec)
- 注：结果与上面一致，表示启动正常。
二、安装第二台mysql：【mysql02】
1、安装前准备（同上步骤1）
2、安装（同上步骤2）
3、配置数据库
需要修改wsrep_cluster_address和wsrep_node_address两个配置中的ip地址
# vim /etc/my.cnf
[client]
socket = /data/mysql/mysql.socket
[mysqld]
server-id = 2
lower_case_table_names = 1
default-storage-engine = InnoDB
port = 3306
datadir = /data/mysql
socket = /data/mysql/mysql.socket
character-set-server = utf8
log_bin=mysql-bin
binlog_format = ROW
max_binlog_size = 500M
expire_logs_days = 7
innodb_buffer_pool_size = 2G
slave-parallel-workers = 8
thread_cache_size = 600
back_log = 600
slave_net_timeout = 60
key_buffer_size = 8M
query_cache_size = 64M
join_buffer_size = 2M
sort_buffer_size = 2M
query_cache_type = 1
thread_stack = 192K
innodb_thread_concurrency = 8
connect_timeout = 300
max_connections = 6000
max_allowed_packet = 128M
max_connect_errors = 6000
wait_timeout = 240
interactive_timeout = 240
table_open_cache = 256
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_flush_log_at_trx_commit = 2
slow_query_log = 1
long_query_time = 1
lower_case_table_names = 1
skip-name-resolve
log_bin_trust_function_creators=TRUE
pxc_strict_mode=ENFORCING
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
wsrep_provider=/opt/mysql/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.51,192.168.1.52,192.168.1.53
wsrep_node_name=pxc-node02
wsrep_node_address=192.168.1.52
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc-cluster
wsrep_sst_auth=sst:U+xwf02a
wsrep_sst_method=rsync
4、启动数据库
# systemctl start mysql
5、检查启动状态
mysql> show global status where Variable_name in ('wsrep_local_index','wsrep_incoming_addresses','wsrep_cluster_status','wsrep_connected','wsrep_ready');
+--------------------------+---------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------+
| wsrep_incoming_addresses
|192.168.1.51:3306,192.168.1.52:3306 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_index | 1 |
| wsrep_ready | ON |
+--------------------------+---------------------------------------+
5 rows in set (0.00 sec)
- 注：结果与上面一致，表示启动正常。
三、安装第三台mysql：【mysql03】
1、安装前准备（同上步骤1）
2、安装（同上步骤2）
3、配置数据库
需要修改wsrep_cluster_address和wsrep_node_address两个配置中的ip地址
# vim /etc/my.cnf
[client]
socket = /data/mysql/mysql.socket
[mysqld]
server-id = 3
lower_case_table_names = 1
default-storage-engine = InnoDB
port = 3306
datadir = /data/mysql
socket = /data/mysql/mysql.socket
character-set-server = utf8
log_bin=mysql-bin
binlog_format = ROW
max_binlog_size = 500M
expire_logs_days = 7
innodb_buffer_pool_size = 2G
slave-parallel-workers = 8
thread_cache_size = 600
back_log = 600
slave_net_timeout = 60
key_buffer_size = 8M
query_cache_size = 64M
join_buffer_size = 2M
sort_buffer_size = 2M
query_cache_type = 1
thread_stack = 192K
innodb_thread_concurrency = 8
connect_timeout = 300
max_connections = 6000
max_allowed_packet = 128M
max_connect_errors = 6000
wait_timeout = 240
interactive_timeout = 240
table_open_cache = 256
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_flush_log_at_trx_commit = 2
slow_query_log = 1
long_query_time = 1
lower_case_table_names = 1
skip-name-resolve
log_bin_trust_function_creators=TRUE
pxc_strict_mode=ENFORCING
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
wsrep_provider=/opt/mysql/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.51,192.168.1.52,192.168.1.53
wsrep_node_name=pxc-node03
wsrep_node_address=192.168.1.53
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc-cluster
wsrep_sst_auth=sst:U+xwf02a
wsrep_sst_method=rsync
4、启动数据库
# systemctl start mysql
5、检查启动状态
mysql> show global status where Variable_name in ('wsrep_local_index','wsrep_incoming_addresses','wsrep_cluster_status','wsrep_connected','wsrep_ready');
+--------------------------+---------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------+
| wsrep_incoming_addresses
| 192.168.1.51:3306,192.168.1.52:3306,192.168.1.53:3306 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_index | 2 |
| wsrep_ready | ON |
+--------------------------+---------------------------------------+
5 rows in set (0.00 sec)
- 注：结果与上面一致，表示启动正常。
