Installation steps for MySQL8.0.13 group replication 01/20 Update SLTechnology News&Howtos

Installation steps for MySQL8.0.13 group replication

2025-01-20 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article mainly explains the "installation steps of MySQL8.0.13 group replication". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn the installation steps of MySQL8.0.13 group replication.

Environment description:

Host:

192.168.0.51 alpha-mysql-0-51 Primary Node

192.168.0.52 alpha-mysql-0-52 slave node

192.168.16.15 zhsq-mysql16-15 slave node

Operating system version: CentOS release 6.8

Start installing group replication on the primary node:

The contents of the profile on the first primary node (192.168.0.51):

[root@alpha-mysql-0-51] # cat / etc/my.cnf

[mysqld]

Sql_mode = NO_ENGINE_SUBSTITUTION

Server_id=14051

Port=3306

User=mysql

Character_set_server=utf8mb4

Skip_name_resolve

Max_connections=100

Basedir=/usr/local/mysql-8.0.13

Datadir=/home/mysql/data

Socket=/tmp/mysql.sock

Pid-file=/home/mysql/mysqld.pid

# transaction_isolation=read-committed

Default_storage_engine=innodb

Max_allowed_packet=128M

Max_heap_table_size=64M

Tmp_table_size=64M

Read_buffer_size=2M

Sort_buffer_size=2M

Read_rnd_buffer_size=4M

Open_files_limit=81920

Table_open_cache=10000

Table_definition_cache=10000

Secure-file-priv = NULL

# secure_file_priv=''

Wait_timeout=86400

Default_authentication_plugin=mysql_native_password

Log_error=/home/mysql/log/mysqld.err

Log_timestamps=system

Slow_query_log=1

Slow_query_log_file=/home/mysql/slow_query.log

Long_query_time=3

Log_bin=/home/mysql/data/mysql-bin

Binlog_format=row

Binlog_row_image=minimal

Binlog_rows_query_log_events

Binlog_error_action=ABORT_SERVER

# expire_logs_days=1-disabled from mysql8.0

Binlog_expire_logs_seconds=86400

Slave_parallel_type=LOGICAL_CLOCK

Slave_parallel_workers=8

Master_info_repository=TABLE

Relay_log_info_repository=TABLE

Relay_log=/home/mysql/data/relay-log

Relay_log_recovery=ON

Log_slave_updates

Skip_slave_start

Innodb_open_files=8000

Innodb_buffer_pool_size=4G

Innodb_max_dirty_pages_pct=90

Innodb_buffer_pool_instances=8

Innodb_buffer_pool_dump_at_shutdown=ON

Innodb_lock_wait_timeout=120

Innodb_io_capacity=1000

Innodb_io_capacity_max=2000

Innodb_flush_method=O_DIRECT

Innodb_file_per_table=1

Innodb_flush_log_at_trx_commit=0

Innodb_log_file_size=100M

Innodb_log_buffer_size=10M

Innodb_log_files_in_group=3

Innodb_purge_threads=4

Innodb_thread_concurrency=0

Innodb_print_all_deadlocks=ON

Innodb_deadlock_detect=ON

Innodb_strict_mode=ON

Innodb_sort_buffer_size=64M

Innodb_read_io_threads=6

Innodb_write_io_threads=6

Gtid_mode=ON

Enforce_gtid_consistency=ON

Binlog_checksum=NONE

Transaction_write_set_extraction=XXHASH64

Loose-group_replication_group_name= "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

Loose-group_replication_start_on_boot=off

Loose-group_replication_local_address= "192.168.0.51 purl 24901"

Loose-group_replication_group_seeds= "10.186.0.51 24901192.168.0.52" 24902192.168.16.15 "

Loose-group_replication_bootstrap_group= off

[mysql]

Socket=/tmp/mysql.sock

Default-character-set = utf8mb4

Prompt= "\\ u@\\ h:\\ d\\ r:\\ m:\\ s >"

[mysqldump]

Socket=/tmp/mysql.sock

[mysqladmin]

Socket=/tmp/mysql.sock

Note:

"loose-group_replication_start_on_boot=off" means that group replication is not automatically started when mysqld starts. If you want to start it automatically, you can change it to on.

Initialize the mysql service:

# mysqld-initialize-insecure-user=mysql

Start mysql8.0 on the first node:

# mysqld_safe-- user=mysql &

[2] 27593

[root@alpha-mongo-140-51 / home/mysql] # 2018-11-07T06:17:06.255849Z mysqld_safe Logging to'/ home/mysql/log/mysqld.err'.

2018-11-07T06:17:06.323053Z mysqld_safe Starting mysqld daemon with databases from / home/mysql/data

Create a replication user and install the group replication plug-in:

Mysql > set sql_log_bin=0

Query OK, 0 rows affected (0.01 sec)

Mysql > CREATE USER repl@'%' identified by '123456'

Query OK, 0 rows affected (0.00 sec)

Note: the step of setting sql_log_bin=0 is very important. The creation of user actions cannot be recorded in the binlog file, otherwise an error will be reported:

[ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

Mysql > GRANT REPLICATION SLAVE ON *. * TO repl@'%'

Query OK, 0 rows affected (0.00 sec)

Mysql > set sql_log_bin=1

Query OK, 0 rows affected (0.00 sec)

Mysql > CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'

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

Mysql > INSTALL PLUGIN group_replication SONAME 'group_replication.so'

Query OK, 0 rows affected (0.02 sec)

Set whitelist (need to be set when the machines in the LAN are not in a network segment):

Mysql > set global group_replication_ip_whitelist= "192.168.0.51192.168.0.52192.168.16.15"

Query OK, 0 rows affected (0.00 sec)

View the whitelist:

Mysql > show global variables like'% white%'

+-+ +

| | Variable_name | Value |

+-+ +

| | group_replication_ip_whitelist | 192.168.0.51192.168.0.52192.168.16.15 |

+-+ +

1 row in set (0.01 sec)

Mysql > SELECT * FROM information_schema.plugins WHERE PLUGIN_NAME LIKE'% group%'\ G

* * 1. Row *

PLUGIN_NAME: group_replication

PLUGIN_VERSION: 1.1

PLUGIN_STATUS: ACTIVE

PLUGIN_TYPE: GROUP REPLICATION

PLUGIN_TYPE_VERSION: 1.2

PLUGIN_LIBRARY: group_replication.so

PLUGIN_LIBRARY_VERSION: 1.9

PLUGIN_AUTHOR: ORACLE

PLUGIN_DESCRIPTION: Group Replication (1.1.0)

PLUGIN_LICENSE: GPL

LOAD_OPTION: ON

1 row in set (0.10 sec)

View installed plug-ins:

Mysql > show plugins

+-+

+-+

+-+

45 rows in set (0.01 sec)

Note: looking at the last line, group_replication ACTIVE indicates that the group replication plug-in is installed.

Turn on the first node group replication:

Mysql > SET GLOBAL group_replication_bootstrap_group=ON

Query OK, 0 rows affected (0.00 sec)

Start group replication:

Mysql > START GROUP_REPLICATION

ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

An error has been reported. The alarm message in the log is as follows:

[Warning] [MY-011682] [Repl] Plugin group_replication reported: 'Group Replication requires slave-preserve-commit-order to be set to ON when using more than 1 applier threads.'

Prompt: you need to set the parameter slave-preserve-commit-order, which is set to control the order of binlog submission on Slave is the same as that of binlog on Master, ensuring the order of GTID.

Mysql > set global slave_preserve_commit_order=on

Query OK, 0 rows affected (0.00 sec)

Start group replication again:

Root@localhost: (none) 02:23:14 > START GROUP_REPLICATION

Query OK, 0 rows affected (3.47 sec)

Start successfully. The log is as follows:

2018-11-07T14:23:22.130524+08:00 9 [Warning] [MY-011735] [Repl] Plugin group_replication reported:'[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'

2018-11-07T14:23:22.351635+08:00 259 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL' group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.

View group replication members:

Mysql > select * from performance_schema.replication_group_members

+-- -+

+-- -+

+-- -+

1 row in set (0.02 sec)

Create test data:

Mysql > create database test

Query OK, 1 row affected (0.05sec)

Mysql > use test

Database changed

Mysql > create table test (id int primary key,name varchar (10))

Query OK, 0 rows affected (0.15 sec)

Mysql > insert into test values (1 recording lovepeihy')

Query OK, 1 row affected (0.07 sec)

The second node (slave node) profile:

[root@alpha-mysql-0-52 /] # cat / etc/my.cnf

[mysqld]

Sql_mode = NO_ENGINE_SUBSTITUTION

Server_id=14052

Port=3306

User=mysql

Character_set_server=utf8mb4

Skip_name_resolve

Max_connections=100

Basedir=/usr/local/mysql-8.0.13

Datadir=/home/mysql/data

Socket=/tmp/mysql.sock

Pid-file=/home/mysql/mysqld.pid

# transaction_isolation=read-committed

Default_storage_engine=innodb

Max_allowed_packet=128M

Max_heap_table_size=64M

Tmp_table_size=64M

Read_buffer_size=2M

Sort_buffer_size=2M

Read_rnd_buffer_size=4M

Open_files_limit=81920

Table_open_cache=10000

Table_definition_cache=10000

Secure-file-priv = NULL

# secure_file_priv=''

Wait_timeout=86400

Default_authentication_plugin=mysql_native_password

Log_error=/home/mysql/log/mysqld.err

Log_timestamps=system

Slow_query_log=1

Slow_query_log_file=/home/mysql/slow_query.log

Long_query_time=3

Log_bin=/home/mysql/data/mysql-bin

Binlog_format=row

# binlog_checksum=NONE

Binlog_row_image=minimal

Binlog_rows_query_log_events

Binlog_error_action=ABORT_SERVER

# expire_logs_days=1-disabled in mysql8.0

Binlog_expire_logs_seconds=86400

Slave_parallel_type=LOGICAL_CLOCK

Slave_parallel_workers=8

Master_info_repository=TABLE

Relay_log_info_repository=TABLE

Relay_log=/home/mysql/data/relay-log

Relay_log_recovery=ON

Log_slave_updates

Skip_slave_start

# key_buffer_size=4M

# bulk_insert_buffer_size=4M

# myisam_sort_buffer_size=6M

# myisam_max_sort_file_size=10G

# myisam_repair_threads=1

# myisam_recover_options=default

Innodb_open_files=8000

# innodb_page_size=8192

Innodb_buffer_pool_size=4G

Innodb_max_dirty_pages_pct=90

# innodb_buffer_pool_dump_pct=40

Innodb_buffer_pool_instances=8

# innodb_buffer_pool_load_at_startup=ON

Innodb_buffer_pool_dump_at_shutdown=ON

Innodb_lock_wait_timeout=120

Innodb_io_capacity=1000

Innodb_io_capacity_max=2000

Innodb_flush_method=O_DIRECT

# innodb_file_format=Barracuda

Innodb_file_per_table=1

# innodb_undo_directory=/undolog/

# innodb_undo_logs=128

# innodb_undo_tablespaces=3

# innodb_undo_log_truncate=1

# innodb_max_undo_log_size=2G

# innodb_purge_rseg_truncate_frequency=128

# innodb_flush_neighbors=2

Innodb_flush_log_at_trx_commit=0

Innodb_log_file_size=100M

Innodb_log_buffer_size=10M

Innodb_log_files_in_group=3

Innodb_purge_threads=4

Innodb_thread_concurrency=0

Innodb_print_all_deadlocks=ON

Innodb_deadlock_detect=ON

Innodb_strict_mode=ON

Innodb_sort_buffer_size=64M

Innodb_read_io_threads=6

Innodb_write_io_threads=6

Gtid_mode=ON

Enforce_gtid_consistency=ON

Binlog_checksum=NONE

Slave_preserve_commit_order=ON

Transaction_write_set_extraction=XXHASH64

Loose-group_replication_group_name= "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

Loose-group_replication_start_on_boot=off

Loose-group_replication_local_address= "192.168.0.52 purl 24902"

Loose-group_replication_group_seeds= "192.168.0.51 24901192.168.0.52" 24902192.168.16.1524903 "

Loose-group_replication_bootstrap_group= off

[mysql]

Socket=/tmp/mysql.sock

Default-character-set = utf8mb4

Prompt= "\\ u@\\ h:\\ d\\ r:\\ m:\\ s >"

[mysqldump]

Socket=/tmp/mysql.sock

[mysqladmin]

Socket=/tmp/mysql.sock

Initialize the second node service:

# mysqld-initialize-insecure-user=mysql

Start mysql8.0 on the second node:

# mysqld_safe-- user=mysql &

Repeat the steps of the first node to create the user and install the group replication plug-in:

Mysql > set sql_log_bin=0

Query OK, 0 rows affected (0.00 sec)

Mysql > CREATE USER repl@'%' identified by '123456'

Query OK, 0 rows affected (0.01 sec)

Mysql > GRANT REPLICATION SLAVE ON *. * TO repl@'%'

Query OK, 0 rows affected (0.00 sec)

Mysql > set sql_log_bin=1

Query OK, 0 rows affected (0.00 sec)

Mysql > CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'

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

Mysql > set global group_replication_ip_whitelist= "192.168.0.51192.168.0.52192.168.16.15"

Query OK, 0 rows affected (0.00 sec)

Mysql > show variables like'% white%'

+-+ +

| | Variable_name | Value |

+-+ +

| | group_replication_ip_whitelist | 192.168.0.51192.168.0.52192.168.16.15 |

+-+ +

1 row in set (0.01 sec)

Root@localhost: (none) 02:37:26 > INSTALL PLUGIN group_replication SONAME 'group_replication.so'

Query OK, 0 rows affected (0.02 sec)

Root@localhost: (none) 02:37:37 > START GROUP_REPLICATION

Query OK, 0 rows affected (4.05 sec)

Root@localhost: (none) 02:37:59 > select * from performance_schema.replication_group_members

+-- -+

+-- -+

+-- -+

2 rows in set (0.09 sec)

View the data created by the primary node:

Root@localhost: (none) 02:46:38 > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | sys |

| | test |

+-+

5 rows in set (0.04 sec)

Root@localhost: test02:46:42 > show tables

+-+

| | Tables_in_test |

+-+

| | test |

+-+

1 row in set (0.00 sec)

Root@localhost: test02:46:45 > desc test

+-+ +

+-+ +

| | id | int (11) | NO | PRI | NULL |

+-+ +

2 rows in set (0.00 sec)

Root@localhost: test02:46:47 > select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | lovepeihy |

+-+ +

1 row in set (0.00 sec)

The third node does the same thing as the second node:

The final results are as follows:

Root@localhost: test03:59:14 > select * from performance_schema.replication_group_members

+-- -+

+-- -+

+-- -+

3 rows in set (0.00 sec)

Note: if you insert data on a slave node, an error will be reported:

Mysql > insert into test select * from test

ERROR 1290 (HY000): The MySQL server is running with the-- super-read-only option so it cannot execute this statement

At this point, the group replication installation of MySQL 8.0.13 is complete.

Add:

Explain the parameters related to the group replication configuration of mysql:

Gtid_mode=ON # whether to turn on GTID mode

Enforce_gtid_consistency=ON # whether to force transaction consistency

Binlog_checksum=NONE # # whether to enable the binlog check function and set it to disable

Slave_preserve_commit_order=ON # controls the order of binlog submission on Slave is the same as that of binlog on Master, ensuring the order of GTID

Transaction_write_set_extraction=XXHASH64 # # enable primary key information collection. The default value is XXHASH64 since 8.0.2.

Loose-group_replication_group_name= "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" # set the group name as you like, but cannot repeat it with UUID

Loose-group_replication_start_on_boot=off # # MySQL SERVER does not start group replication automatically when it starts

Loose-group_replication_local_address= "192.168.0.51 24901" # set the local address of the member, followed by the port number copied by the group

Loose-group_replication_group_seeds= "192.186.140.51, 24901192.168.0.52, 24902192.168.16.15, 24903" # set the addresses of seed members, and set up several machines.

Loose-group_replication_bootstrap_group= off # configure whether to automatically boot the group

Thank you for your reading, the above is the "MySQL8.0.13 group replication installation steps" content, after the study of this article, I believe you have a deeper understanding of the installation steps of MySQL8.0.13 group replication, the specific use of the need for you to practice and verify. 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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.