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 #停止firewallfirewall-cmd –statewsrep(Write-Set Replication)#mkdir -p /app/mysql/3306#mkdir -p /app/mysql/binlog3、修改/etc/security/limits.conf* soft nproc 16384* hard nproc 16384* soft nofile 16384* hard nofile 655354、安装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.rpmwarning: mysql-community-common-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing… ################################# [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.rpmwarning: mysql-community-libs-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing… ################################# [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.rpmwarning: mysql-community-libs-compat-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing… ################################# [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.rpmwarning: mysql-community-embedded-compat-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing… ################################# [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.rpmwarning: mysql-community-embedded-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing… ################################# [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.rpmwarning: mysql-community-devel-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing… ################################# [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.rpmwarning: mysql-community-embedded-devel-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing… ################################# [100%]Updating / installing… 1:mysql-commun免费主机域名ity-embedded-devel-5.################################# [100%][root@bsmysql01 soft]# rpm -ivh mysql-community-client-5.7.27-1.el7.x86_64.rpmwarning: mysql-community-client-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing… ################################# [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.rpmwarning: mysql-community-server-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing… ################################# [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=truelog_timestamps=SYSTEM#character_set_server = utf8character_set_server = utf8mb4 collation_server = utf8mb4_unicode_ci init_connect=’set names utf8mb4’skip_character_set_client_handshake = trueserver-id=3921gtid_mode=onbasedir=/usruser=mysql#skip-name-resolvelower_case_table_names=1max_connections=2000max_connect_errors=5000event_scheduler=ondatadir=/app/mysql/3306default-storage-engine = InnoDBsocket=/app/mysql/3306/mysql.sockinnodb_autoinc_lock_mode=2innodb_flush_log_at_trx_commit=2transaction-isolation = READ-COMMITTED#sync_binlog=1,no data lost;0,best performance;ref 20sync_binlog=1innodb_buffer_pool_size=16Gthread_cache_size=128max_allowed_packet=256Msort_buffer_size=8Mjoin_buffer_size=8Mtmp_table_size=128M#pid_file=/app/mysql/mysqld.pidport=3306log-error=/app/mysql/mysql.errslow_query_log=onlong_query_time=5#add for bin-logenforce_gtid_consistency=onmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=nonelog_slave_updates=onlog_bin=onbinlog_format=row####binlog_format=mixedlog_bin=/app/mysql/binlog/mysql-binlog_bin_index=/app/mysql/binlog/mysql-bin.indexexpire_logs_days=32max_binlog_size=1024Minnodb_log_file_size=1024Msql_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 replicationauto_increment_increment=5auto_increment_offset=1transaction_write_set_extraction=XXHASH64#loose-group_replication_group_name=”9166859b-cef1-11e9-a8c9-005056873ade”loose-group_replication_start_on_boot=offloose-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=falseloose-group_replication_enforce_update_everywhere_checks=true###Add for slaveslave_parallel_workers=8slave-parallel-type=LOGICAL_CLOCKrelay_log_recovery=onslave_net_timeout=30#read_only=1[client]socket=/app/mysql/3306/mysql.sock#####default-character-set=utf8default_character_set = utf8mb4[mysql]#default-character-set=utf8default_character_set = utf8mb4socket=/app/mysql/3306/mysql.sock[mysqldump]max_allowed_packet = 512M[mysqld_safe]malloc-lib=/usr/lib64/libjemalloc.so.1# id mysqluid=27(mysql) gid=27(mysql) groups=27(mysql)# chmod -R 775 /app/mysql# chown -R mysql.mysql /app/mysqlmysqld –initialize –user=mysql9ZI8dcM,o5Do[root@bsmysql01 my.cnf.d]# mysqld –initialize –user=mysql2019-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=457902019-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
# mysql_ssl_rsa_setupcd /usr/lib/systemd/systemvi 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 ServerDocumentation=man:mysqld(8)Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlType=forking#PIDFile=/var/run/mysqld/mysqld.pidPIDFile=/app/mysql/mysqld.pid# Disable service start and stop timeout logic of systemd for mysqld service.TimeoutSec=0# Execute pre and post scripts as rootPermissionsStartOnly=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_STARTExecStart=/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 implementationEnvironmentFile=-/etc/sysconfig/mysql# Sets open_files_limitLimitNOFILE = 65535Restart=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=1RestartPreventExitStatus=1PrivateTmp=falsesystemctl daemon-reloadsystemctl enable mysqld.servicesystemctl is-enabled mysqldsystemctl start mysqldmysql -pset password=password(“Bs#2019My”);flush privileges;systemctl stop mysqldsystemctl start mysqldexport MYSQL_PS1=”u@[d]> “alias m=’mysql -uroot -p”Bs#2019My”‘PATH=$PATH:$HOME/binloose-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上安装部署这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是云技术，小编将为大家推送更多相关知识点的文章，欢迎关注！

相关推荐: PostgreSQL中函数reconsider_outer_join_clauses的主要实现逻辑是什么

本篇内容介绍了“PostgreSQL中函数reconsider_outer_join_clauses的主要实现逻辑是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有…