mysql5.7多主模式在Centos7.2上安装部署

这篇文章主要讲解了“Mysql5.7多主模式在Centos7.2上安装部署”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“mysql5.7多主模式在Centos7.2上安装部署”吧！

Mysql Group Replication（MGR）是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案，以插件形式提供，实现了分布式下数据的最终一致性，总结MGR特点如下：

高一致性：基于分布式paxos协议实现组复制，保证数据一致性；

高容错性：自动检测机制，只要不是大多数节点都宕机就可以继续工作，内置防脑裂保护机制；

高扩展性：节点的增加与移除会自动更新组成员信息，新节点加入后，自动从其他节点同步增量数据，直到与其他节点数据一致；

高灵活性：提供单主模式和多主模式，单主模式在主库宕机后能够自动选主，所有写入都在主节点进行，多主模式支持多节点写入。

1、设置SElinux

在/etc/sysconfig/selinux文件，修改SELINUX=disabled。

2、设置防火墙，或者关闭防火墙，安装完成后再开启。

systemctl stop firewalld.service #停止firewall

firewall-cmd --state

wsrep(Write-Set Replication)

#mkdir -p /app/mysql/3306

#mkdir -p /app/mysql/binlog

3、修改/etc/security/limits.conf

* soft nproc 16384

* hard nproc 16384

* soft nofile 16384

* hard nofile 65535

4、安装RPM包

# rpm -e mariadb-libs-1:5.5.52-1.el7.x86_64 --nodeps

# rpm -e mariadb-libs-5.5.44-2.el7.centos.x86_64 --nodeps

# rpm -ivh mysql-commUnity-common-5.7.27-1.el7.x86_64.rpm 

warning: mysql-community-common-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-common-5.7.27-1.e################################# [100%]

[root@bsmysql01 soft]# rpm -ivh mysql-community-libs-5.7.27-1.el7.x86_64.rpm 

warning: mysql-community-libs-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-libs-5.7.27-1.el7################################# [100%]

[root@bsmysql01 soft]# rpm -ivh mysql-community-libs-compat-5.7.27-1.el7.x86_64.rpm 

warning: mysql-community-libs-compat-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-libs-compat-5.7.2################################# [100%]

[root@bsmysql01 soft]# rpm -ivh mysql-community-embedded-compat-5.7.27-1.el7.x86_64.rpm 

warning: mysql-community-embedded-compat-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-embedded-compat-5################################# [100%]

[root@bsmysql01 soft]# rpm -ivh mysql-community-embedded-5.7.27-1.el7.x86_64.rpm 

warning: mysql-community-embedded-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-embedded-5.7.27-1################################# [100%]

[root@bsmysql01 soft]# rpm -ivh mysql-community-devel-5.7.27-1.el7.x86_64.rpm 

warning: mysql-community-devel-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-devel-5.7.27-1.el################################# [100%]

[root@bsmysql01 soft]# rpm -ivh mysql-community-embedded-devel-5.7.27-1.el7.x86_64.rpm 

warning: mysql-community-embedded-devel-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-embedded-devel-5.################################# [100%]

[root@bsmysql01 soft]# rpm -ivh mysql-community-client-5.7.27-1.el7.x86_64.rpm 

warning: mysql-community-client-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-client-5.7.27-1.e################################# [100%]

[root@bsmysql01 soft]# rpm -ivh mysql-community-server-5.7.27-1.el7.x86_64.rpm 

warning: mysql-community-server-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-community-server-5.7.27-1.e################################# [100%]

5、创建并配置参数文件

---创建并配置my.cnf文件

# cat /etc/my.cnf

# For advice on how to change settings please see

# Http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

!includedir  /etc/my.cnf.d/

cat mgrep.conf

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

[mysqld]

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

# basedir = .....

# datadir = .....

# port = .....

# server_id = .....

# Socket = .....

explicit_defaults_for_timestamp=true

log_timestamps=SYSTEM

#character_set_server = utf8

character_set_server = utf8mb4  

collation_server = utf8mb4_unicode_ci  

init_connect='set names utf8mb4'

skip_character_set_client_handshake = true 

server-id=3921

gtid_mode=on

basedir=/usr

user=mysql

#skip-name-resolve

lower_case_table_names=1

max_connections=2000

max_connect_errors=5000

event_scheduler=on

datadir=/app/mysql/3306

default-storage-engine = InnoDB

socket=/app/mysql/3306/mysql.sock

innodb_autoinc_lock_mode=2

innodb_flush_log_at_trx_commit=2

transaction-isolation = READ-COMMITTED

#sync_binlog=1,no data lost;0,best perfORMance;ref 20

sync_binlog=1

innodb_buffer_pool_size=16G

thread_cache_size=128

max_allowed_packet=256M

sort_buffer_size=8M

join_buffer_size=8M

tmp_table_size=128M

#pid_file=/app/mysql/mysqld.pid

port=3306

log-error=/app/mysql/mysql.err

slow_query_log=on

long_query_time=5

#add for bin-log

enforce_gtid_consistency=on

master_info_repository=TABLE

relay_log_info_repository=TABLE

binlog_checksum=none

log_slave_updates=on

log_bin=on

binlog_format=row

####binlog_format=mixed

log_bin=/app/mysql/binlog/mysql-bin

log_bin_index=/app/mysql/binlog/mysql-bin.index

expire_logs_days=32

max_binlog_size=1024M

innodb_log_file_size=1024M

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT'

##add for group replication

auto_increment_increment=5

auto_increment_offset=1

transaction_write_set_extraction=XXHASH64

#loose-group_replication_group_name="9166859b-cef1-11e9-a8c9-005056873ade"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address= "10.70.39.159:24901"

loose-group_replication_group_seeds= "10.70.39.159:24901,10.70.39.160:24901,10.70.39.179:24901"

loose-group_replication_bootstrap_group=off

#loose-group_replication_ip_whitelist="127.0.0.1/8,172.25.51.0/24"

loose-group_replication_single_primary_mode=false

loose-group_replication_enforce_update_everywhere_checks=true

###Add for slave

slave_parallel_workers=8

slave-parallel-type=LOGICAL_CLOCK

relay_log_recovery=on

slave_net_timeout=30

#read_only=1

[client]

socket=/app/mysql/3306/mysql.sock

#####default-character-set=utf8

default_character_set = utf8mb4

[mysql]

#default-character-set=utf8

default_character_set = utf8mb4

socket=/app/mysql/3306/mysql.sock

[mysqldump]

max_allowed_packet = 512M

[mysqld_safe]

malloc-lib=/usr/lib64/libjemalloc.so.1

# id mysql

uid=27(mysql) gid=27(mysql) groups=27(mysql)

# chmod -R 775 /app/mysql

# chown -R mysql.mysql /app/mysql

mysqld --initialize  --user=mysql

9ZI8dcM,o5Do

[root@bsmysql01 my.cnf.d]# mysqld --initialize  --user=mysql

2019-09-04T08:54:13.040689Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2019-09-04T08:54:13.237393Z 0 [Warning] InnoDB: New log files created, LSN=45790

2019-09-04T08:54:13.278526Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2019-09-04T08:54:13.336631Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9166859b-cef1-11e9-a8c9-005056873ade.

2019-09-04T08:54:13.337829Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2019-09-04T08:54:13.338429Z 1 [Note] A temporary passWord is generated for root@localhost: 0(gqhhyYp<3f

# mysql_ssl_rsa_setup

cd /usr/lib/systemd/system

vi mysqld.service

# cat mysqld.service

# Copyright (c) 2015, 2016, oracle and/or its affiliates. All rights reserved.

#

# This program is free software; you can Redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; version 2 of the License.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

# GNU General Public License for more details.

#

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#

# systemd service file for MySQL forking server

# Modified for wsrep (Galera): Recovery

#

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=mysql

Group=mysql

Type=forking

#PIDFile=/var/run/mysqld/mysqld.pid

PIDFile=/app/mysql/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.

TimeoutSec=0

# Execute pre and post scripts as root

PermissionsStartOnly=true

# Needed to create system tables and to check for cluster crash recovery

#ExecStartPre=/usr/bin/mysqld_pre_systemd --pre

# Start main service

#ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/app/mysql/mysqld.pid $MYSQLD_OPTS $MYSQLD_RECOVER_START

ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/app/mysql/mysqld.pid $MYSQLD_OPTS

# Needed to reset cluster crash recovery

#ExecStartPost=/usr/bin/mysqld_pre_systemd --post

# Use this to switch malloc implementation

EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit

LimitNOFILE = 65535

Restart=on-failure

# Dirty hack to prevent fast restart in case of configuration problem.

# Longer-term fix will be to ensure exit status 1 for "RestartPreventExitStatus=1" below.

RestartSec=1

RestartPreventExitStatus=1

PrivateTmp=false

systemctl daemon-reload

systemctl enable mysqld.service

systemctl is-enabled mysqld

systemctl start mysqld

mysql -p

set password=password("Bs#2019My");

flush privileges;

systemctl stop mysqld

systemctl start mysqld

export MYSQL_PS1="\u@[\d]> "

alias m='mysql -uroot -p"Bs#2019My"'

PATH=$PATH:$HOME/bin

loose-group_replication_ip_whitelist="172.25.92.0/24"

install plugin group_replication soname 'group_replication.so';

select * from performance_schema.replication_group_members;

+---------------------------+-----------+-------------+-------------+--------------+

| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

+---------------------------+-----------+-------------+-------------+--------------+

| group_replication_applier |           |             |        NULL | OFFLINE      |

+---------------------------+-----------+-------------+-------------+--------------+

1 row in set (0.00 sec)

root@[(none)]> show variables like '%group_replication%';

+----------------------------------------------------+----------------------------------------------------------+

| Variable_name                                      | Value                                                    |

+----------------------------------------------------+----------------------------------------------------------+

| group_replication_allow_local_disjoint_gtids_join  | OFF                                                      |

| group_replication_allow_local_lower_version_join   | OFF                                                      |

| group_replication_auto_increment_increment         | 7                                                        |

| group_replication_bootstrap_group                  | OFF                                                      |

| group_replication_components_stop_timeout          | 31536000                                                 |

| group_replication_compression_threshold            | 1000000                                                  |

| group_replication_enforce_update_everywhere_checks | ON                                                       |

| group_replication_exit_state_action                | READ_ONLY                                                |

| group_replication_flow_control_applier_threshold   | 25000                                                    |

| group_replication_flow_control_certifier_threshold | 25000                                                    |

| group_replication_flow_control_mode                | QUOTA                                                    |

| group_replication_force_members                    |                                                          |

| group_replication_group_name                       | f184c7a7-cf7f-11e9-bef9-005056873ade                     |

| group_replication_group_seeds                      | 10.70.39.159:24901,10.70.39.160:24901,10.70.39.179:24901 |

| group_replication_gtid_assignment_block_size       | 1000000                                                  |

| group_replication_ip_whitelist                     | 10.70.39.0/24,127.0.0.1/8                                |

| group_replication_local_address                    | 10.70.39.179:24901                                       |

| group_replication_member_weight                    | 50                                                       |

| group_replication_poll_spin_loops                  | 0                                                        |

| group_replication_recovery_complete_at             | TRANSACTIONS_APPLIED                                     |

| group_replication_recovery_reconnect_interval      | 60                                                       |

| group_replication_recovery_retry_count             | 10                                                       |

| group_replication_recovery_ssl_ca                  |                                                          |

| group_replication_recovery_ssl_capath              |                                                          |

| group_replication_recovery_ssl_cert                |                                                          |

| group_replication_recovery_ssl_cipher              |                                                          |

| group_replication_recovery_ssl_crl                 |                                                          |

| group_replication_recovery_ssl_crlpath             |                                                          |

| group_replication_recovery_ssl_key                 |                                                          |

| group_replication_recovery_ssl_verify_server_cert  | OFF                                                      |

| group_replication_recovery_use_ssl                 | OFF                                                      |

| group_replication_single_primary_mode              | OFF                                                      |

| group_replication_ssl_mode                         | DISABLED                                                 |

| group_replication_start_on_boot                    | OFF                                                      |

| group_replication_transaction_size_limit           | 0                                                        |

| group_replication_unreachable_majority_timeout     | 0                                                        |

+----------------------------------------------------+----------------------------------------------------------+

36 rows in set (0.00 sec)

set sql_log_bin=0;

create user bsrep@'10.70.39.%' identified by 'Bs$Rep202o';

grant replication slave on *.* to bsrep@'10.70.39.%';

flush privileges;

set sql_log_bin=1;

--change master to master_user = 'bsrep',master_password='Bs$Rep202o' for channel 'group_replication_recovery';

root@[(none)]> set global group_replication_bootstrap_group=ON; 

Query OK, 0 rows affected (0.00 sec)

root@[(none)]> change master to master_user = 'bsrep',master_password='Bs$Rep202o' for channel 'group_replication_recovery';

Query OK, 0 rows affected, 2 warnings (0.01 sec)

root@[(none)]> start group_replication;

Query OK, 0 rows affected (2.02 sec)

root@[(none)]> set global group_replication_bootstrap_group=OFF;

Query OK, 0 rows affected (0.00 sec)

root@[(none)]> select * from performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------+-------------+--------------+

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

| group_replication_applier | 14beae8e-cf8d-11e9-baa8-005056873ade | bsmysql01   |        3306 | ONLINE       |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

1 row in set (0.00 sec)

6、-----配置第二个节点：

set sql_log_bin=0;

create user bsrep@'10.70.39.%' identified by 'Bs$Rep202o';

grant replication slave on *.* to bsrep@'10.70.39.%';

flush privileges;

set sql_log_bin=1;

change master to master_user = 'bsrep',master_password='Bs$Rep202o' for channel 'group_replication_recovery';

set global group_replication_allow_local_disjoint_gtids_join=on;

start group_replication;

select * from performance_schema.replication_group_members;

root@[(none)]> set sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

root@[(none)]> create user bsrep@'10.70.39.%' identified by 'Bs$Rep202o';

Query OK, 0 rows affected (0.00 sec)

root@[(none)]> grant replication slave on *.* to bsrep@'10.70.39.%';

Query OK, 0 rows affected (0.00 sec)

root@[(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

root@[(none)]> set sql_log_bin=1;

Query OK, 0 rows affected (0.00 sec)

root@[(none)]> 

root@[(none)]> change master to master_user = 'bsrep',master_password='Bs$Rep202o' for channel 'group_replication_recovery';

Query OK, 0 rows affected, 2 warnings (0.01 sec)

root@[(none)]> set global group_replication_allow_local_disjoint_gtids_join=on;

Query OK, 0 rows affected, 1 warning (0.00 sec)

root@[(none)]> start group_replication;

Query OK, 0 rows affected, 1 warning (5.78 sec)

root@[(none)]> select * from performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------+-------------+--------------+

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

| group_replication_applier | 14beae8e-cf8d-11e9-baa8-005056873ade | bsmysql01   |        3306 | ONLINE       |

| group_replication_applier | 3a6e0efb-cf8b-11e9-a364-0050568728b9 | bsmysql02   |        3306 | ONLINE       |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

2 rows in set (0.00 sec)

7、-----配置第三个节点：

set sql_log_bin=0;

create user bsrep@'10.70.39.%' identified by 'Bs$Rep202o';

grant replication slave on *.* to bsrep@'10.70.39.%';

flush privileges;

set sql_log_bin=1;

change master to master_user = 'bsrep',master_password='Bs$Rep202o' for channel 'group_replication_recovery';

set global group_replication_allow_local_disjoint_gtids_join=on;

start group_replication;

select * from performance_schema.replication_group_members;

root@[(none)]> set sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

root@[(none)]> create user bsrep@'10.70.39.%' identified by 'Bs$Rep202o';

Query OK, 0 rows affected (0.00 sec)

root@[(none)]> grant replication slave on *.* to bsrep@'10.70.39.%';

Query OK, 0 rows affected (0.00 sec)

root@[(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

root@[(none)]> set sql_log_bin=1;

Query OK, 0 rows affected (0.00 sec)

root@[(none)]> change master to master_user = 'bsrep',master_password='Bs$Rep202o' for channel 'group_replication_recovery';

Query OK, 0 rows affected, 2 warnings (0.01 sec)

root@[(none)]> set global group_replication_allow_local_disjoint_gtids_join=on;

Query OK, 0 rows affected, 1 warning (0.00 sec)

root@[(none)]> start group_replication;

Query OK, 0 rows affected, 1 warning (3.24 sec)

root@[(none)]> select * from performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------+-------------+--------------+

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

| group_replication_applier | 14beae8e-cf8d-11e9-baa8-005056873ade | bsmysql01   |        3306 | ONLINE       |

| group_replication_applier | 3a6e0efb-cf8b-11e9-a364-0050568728b9 | bsmysql02   |        3306 | ONLINE       |

| group_replication_applier | 79509566-cf8c-11e9-9c16-0050568794c1 | bsmysql03   |        3306 | ONLINE       |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

3 rows in set (0.00 sec)

root@[(none)]> set global group_replication_allow_local_disjoint_gtids_join=off;

Query OK, 0 rows affected, 1 warning (0.00 sec)

MGR单主多主切换模式需要重启动组复制，需要在所有节点上先关闭组复制，设置group_replication_single_primary_mode=OFF 等参数，再启动组复制。

感谢各位的阅读，以上就是“mysql5.7多主模式在Centos7.2上安装部署”的内容了，经过本文的学习后，相信大家对mysql5.7多主模式在Centos7.2上安装部署这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是编程网，小编将为大家推送更多相关知识点的文章，欢迎关注！