mysql5.7多主模式在Centos7.2上安装布局
发布时间：2022-01-18 13:41:07 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要讲解了mysql5.7多主模式在Centos7.2上安装部署，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习mysql5.7多主模式在Centos7.2上安装部署吧！ MySQL Group Replication（MGR）是MySQL官方在5.7.17
|
这篇文章主要讲解了“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 等参数，再启动组复制。 （编辑：通辽站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
