MySQL8.0.13組復(fù)制的安裝步驟

發(fā)布時間：2021-08-30 17:05:58 來源：億速云閱讀：182 作者：chen 欄目：MySQL數(shù)據(jù)庫

這篇文章主要講解了“MySQL8.0.13組復(fù)制的安裝步驟”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“MySQL8.0.13組復(fù)制的安裝步驟”吧！

環(huán)境描述:

主機:

192.168.0.51 alpha-mysql-0-51 主節(jié)點

192.168.0.52 alpha-mysql-0-52 從節(jié)點

192.168.16.15 zhsq-mysql16-15 從節(jié)點

操作系統(tǒng)版本:CentOS release 6.8

開始在主節(jié)點上安裝組復(fù)制:

第一個主節(jié)點(192.168.0.51)上的配置文件內(nèi)容:

[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:24901"

loose-group_replication_group_seeds= "10.186.0.51:24901,192.168.0.52:24902,192.168.16.15:24903"

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

備注：

loose-group_replication_start_on_boot=off 這句的意思是在mysqld啟動時不自動啟動組復(fù)制，如果想自動啟動，可以改成on

初始化mysql服務(wù):

# mysqld --initialize-insecure --user=mysql

啟動第一個節(jié)點上的mysql8.0:

# 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

創(chuàng)建復(fù)制用戶并安裝組復(fù)制插件:

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)

備注：設(shè)置sql_log_bin=0這個步驟很重要，創(chuàng)建用戶操作不能記錄到binlog文件中，否則會報錯:

[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)

設(shè)置白名單(局域網(wǎng)內(nèi)機器不在一個網(wǎng)段時候需要設(shè)置)：

mysql>set global group_replication_ip_whitelist="192.168.0.51,192.168.0.52,192.168.16.15";

Query OK, 0 rows affected (0.00 sec)

查看白名單:

mysql>show global variables like '%white%';

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

| Variable_name | Value |

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

| group_replication_ip_whitelist | 192.168.0.51,192.168.0.52,192.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)

查看已安裝的插件:

mysql>show plugins;

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

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

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

45 rows in set (0.01 sec)

備注：查看最后一行，group_replication ACTIVE表明組復(fù)制插件已安裝。

開啟第一個節(jié)點組復(fù)制:

mysql>SET GLOBAL group_replication_bootstrap_group=ON;

Query OK, 0 rows affected (0.00 sec)

啟動組復(fù)制:

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.

報錯了，日志里的告警信息如下：

[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.'

提示需要設(shè)置參數(shù)slave-preserve-commit-order,設(shè)置此參數(shù)是為了控制Slave上的binlog提交順序和Master上的binlog的提交順序一樣,保證GTID的順序。

mysql>set global slave_preserve_commit_order=on;

Query OK, 0 rows affected (0.00 sec)

再次啟動group replication:

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

Query OK, 0 rows affected (3.47 sec)

啟動成功,日志如下:

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='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.

查看組復(fù)制成員:

mysql>select * from performance_schema.replication_group_members;

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

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

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

1 row in set (0.02 sec)

創(chuàng)建測試數(shù)據(jù):

mysql>create database test;

Query OK, 1 row affected (0.05 sec)

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,'lovepeihy');

Query OK, 1 row affected (0.07 sec)

第二個節(jié)點(從節(jié)點)配置文件:

[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:24902"

loose-group_replication_group_seeds= "192.168.0.51:24901,192.168.0.52:24902,192.168.16.15:24903"

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

初始化第二個節(jié)點服務(wù):

# mysqld --initialize-insecure --user=mysql

啟動第二個節(jié)點上的mysql8.0:

# mysqld_safe --user=mysql &

重復(fù)第一個節(jié)點創(chuàng)建用戶并安裝組復(fù)制插件的步驟：

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.51,192.168.0.52,192.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.51,192.168.0.52,192.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)

查看主節(jié)點創(chuàng)建的數(shù)據(jù)：

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)

第三個節(jié)點按照第二個節(jié)點同樣的方法做：

最終查出來的結(jié)果如下:

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

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

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

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

3 rows in set (0.00 sec)

注意:如果在從節(jié)點上插入數(shù)據(jù)，會報錯：

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

至此，MySQL 8.0.13的組復(fù)制安裝完畢。

補充:

mysql的組復(fù)制配置有關(guān)的參數(shù)解釋:

gtid_mode=ON ###是否打開GTID模式

enforce_gtid_consistency=ON ###是否強制事務(wù)一致

binlog_checksum=NONE ###是否開啟binlog校驗功能,設(shè)置成不開啟

slave_preserve_commit_order=ON ###控制Slave上的binlog提交順序和Master上的binlog的提交順序一樣,保證GTID的順序

transaction_write_set_extraction=XXHASH64 ###開啟主鍵信息采集功能，8.0.2開始默認值為XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" ###設(shè)置組名，隨便起，但是不能與UUID重復(fù)

loose-group_replication_start_on_boot=off ###MySQL SERVER啟動時不自動啟動組復(fù)制

loose-group_replication_local_address= "192.168.0.51:24901" ###設(shè)置成員的本地地址，后面端口號為組復(fù)制的端口號

loose-group_replication_group_seeds= "192.186.140.51:24901,192.168.0.52:24902,192.168.16.15:24903" ###設(shè)置種子成員的地址,有幾臺機器設(shè)置幾個

loose-group_replication_bootstrap_group= off ###配置是否自動引導(dǎo)組

感謝各位的閱讀，以上就是“MySQL8.0.13組復(fù)制的安裝步驟”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對MySQL8.0.13組復(fù)制的安裝步驟這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！

向AI問一下細節(jié)

MySQL8.0.13組復(fù)制的安裝步驟

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標簽