Shulou(Shulou.com)05/31 Report--

This article mainly explains the "mysql5.7 multi-master mode installation and deployment on Centos7.2", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "mysql5.7 multi-master mode installation and deployment on Centos7.2" bar!

MySQL Group Replication (MGR) is a database solution with high availability and high expansion introduced by MySQL officially in version 5.7.17. It is provided as a plug-in to achieve the ultimate consistency of distributed data. The characteristics of MGR are summarized as follows:

High consistency: group replication based on distributed paxos protocol to ensure data consistency

High fault tolerance: automatic detection mechanism, as long as not most of the nodes are down, can continue to work, built-in anti-brain fissure protection mechanism

High scalability: the addition and removal of nodes automatically updates the group member information, and after the new node is added, the incremental data is automatically synchronized from other nodes until it is consistent with the data of other nodes.

High flexibility: provides single-master mode and multi-master mode. Single-master mode can automatically select master after the main database is down. All writes are carried out on the master node, and multi-master mode supports multi-node writes.

1. Set SELinux

In the / etc/sysconfig/selinux file, modify the SELINUX=disabled.

2. Set up the firewall, or turn it off, and then turn it on after the installation is complete.

Systemctl stop firewalld.service # stop firewall

Firewall-cmd-state

Wsrep (Write-Set Replication)

# mkdir-p / app/mysql/3306

# mkdir-p / app/mysql/binlog

3. Modify / etc/security/limits.conf

* soft nproc 16384

* hard nproc 16384

* soft nofile 16384

* hard nofile 65535

4. Install RPM package

# 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 million # [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. Create and configure parameter files

-create and configure my.cnf files

# 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.159purl 24901"

Loose-group_replication_group_seeds= "10.70.39.159VOR 24901Magnet 10.70.39.160VOR 24901JI 10.70.39.179Rod 24901"

Loose-group_replication_bootstrap_group=off

# loose-group_replication_ip_whitelist= "127.0.0.1 swap 8172.25.51.0 apprentice 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 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.159Vor24901Magol 10.70.39.160Fringe 24901Magne10.70.39.179Rd 24901 |

| | group_replication_gtid_assignment_block_size | 1000000 | |

| | group_replication_ip_whitelist | 10.70.39.0swap 24127.0.0.1On8 |

| | group_replication_local_address | 10.70.39.179VR 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.-configure the second node:

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.-configure the third node:

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)

In MGR single-master and multi-master switching mode, group replication needs to be restarted, group replication needs to be turned off on all nodes, group_replication_single_primary_mode=OFF and other parameters are set, and then group replication is started.

Thank you for reading, the above is the content of "mysql5.7 multi-master mode installation and deployment on Centos7.2". After the study of this article, I believe you have a deeper understanding of the problem of mysql5.7 multi-master mode installation and deployment on Centos7.2, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.