MySQL Group Replication

Source: Internet
Author: User
Tags dba flush

Group replication is a new high-availability, high-expansion MySQL Cluster service.
High consistency, based on the native replication and Paxos protocol group replication Technology, plug-in to provide consistent data security assurance;
High fault tolerance, the majority of services can continue to work, automatic detection of different nodes of resource acquisition conflict, priority in order, built-in automatic anti-brain crack mechanism;
High scalability, automatically add remove nodes, and update group information;
High flexibility, single-master mode and multi-master mode. Single main mode auto-select master, all update operations are performed in the main, multi-master mode, all servers are updated simultaneously.

0, modify the hosts
Vim/etc/hosts
localhost 127.0.0.1

1, experimental environment
m1:127.0.0.1 3307
m2:127.0.0.1 3308

2, install MySQL
/usr/local/mysql57/bin/mysqld--initialize-insecure--user=dba--basedir=/usr/local/mysql57--datadir=/data1/ mysql3307
/usr/local/mysql57/bin/mysqld--initialize-insecure--user=dba--basedir=/usr/local/mysql57--datadir=/data1/ mysql3308

3, configuration
M1:
[Mysqld]

# General con#
user = dBA
Port = 3307
Default_storage_engine = InnoDB
Socket =/tmp/mysql3307.sock
Pid_file =/data1/mysql3307/mysql.pid

# SAFETY #
Max_allowed_packet = 64M
Max_connect_errors = 1000000

# DATA STORAGE #binlog-format
DataDir =/data1/mysql3307/

# BINARY LOGGING #
Log_bin =/data1/mysql3307/3307-binlog
Expire_logs_days = 10
#sync_binlog = 1
relay-log=/data1/mysql3307/3307-relaylog
#replicate-wild-do-table=hostility_url.%
#replicate-wild-do-table=guards.%

# CACHES and LIMITS #
Tmp_table_size = 32M
Max_heap_table_size = 32M
Query_cache_type = 1
Query_cache_size = 0
Max_connections = 5000
#max_user_connections = 200
Thread_cache_size = 512
Open_files_limit = 65535
Table_definition_cache = 4096
Table_open_cache = 4096
wait_timeout=7500
interactive_timeout=7500
Binlog-format=row
Character-set-server=utf8
Skip-name-resolve
Skip-character-set-client-handshake
back_log=1024

# INNODB #
#innodb_flush_method = O_direct
Innodb_data_home_dir =/data1/mysql3307/
#innodb_data_file_path = Ibdata1:1g:autoextend
innodb_log_group_home_dir=/data1/mysql3307/
Innodb_log_files_in_group = 3
Innodb_log_file_size = 1G
Innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
Innodb_file_format=barracuda
Innodb_support_xa=0


innodb_io_capacity=500
Innodb_max_dirty_pages_pct=90
Innodb_read_io_threads=16
Innodb_write_io_threads=8
Innodb_buffer_pool_instances=4
Innodb_thread_concurrency=0

#GTID

#gtid_mode = On
#enforce_gtid_consistency = On


# LOGGING #
Log_error =/data1/mysql3307/error.log
#log_queries_not_using_indexes = 1
Slow_query_log = 1
Slow_query_log_file =/data1/mysql3307/mysql-slow.log
long_query_time=0.05
server_id=3307
Innodb_buffer_pool_size = 1G
Report-host=localhost #此处的localhost一定要存在与hosts文件里, i.e.: localhost 127.0.0.1
report-port=3307
Log_slave_updates = On

#group replication
Gtid_mode = On
Enforce_gtid_consistency = On
Master_info_repository = table
Relay_log_info_repository = table
Binlog_checksum=none
Log_slave_updates=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= "localhost:24901"
Loose-group_replication_group_seeds= "localhost:24901,localhost:24902"
Loose-group_replication_single_primary_mode = True
loose-group_replication_bootstrap_group= off
Loose-group_replication_enforce_update_everywhere_checks = False


# # MYSQL_VERSION=MYSQL57
[MySQL]
prompt = \[email protected]\h:\p [\d]>

4. Start MySQL
Nohup/usr/local/mysql57/bin/mysqld_safe--defaults-file=/data1/mysql3307/my3307.cnf 2>/dev/null &
Mysql-uroot-s/tmp/mysql3307.sock-p123456

Set sql_log_bin=0;
Create user [email protected] '% ';
Grant Replication Slave on * * to [e-mail protected] '% ' identified by ' rpl_pass ';
Flush privileges;
Set sql_log_bin=1;
Change Master to master_user= ' Rpl_user ', master_password= ' Rpl_pass ' for Channel ' group_replication_recovery ';

5, Start group replication
Install plugin group_replication soname ' group_replication.so ';
Show plugins;
Set global group_replication_bootstrap_group=on;
Start group_replication;
Set global Group_replication_bootstrap_group=off;
SELECT * from Performance_schema.replication_group_members;

6, test (Create a database, a new instance is added to the group replication and will be automatically synced to the new instance)
Create DATABASE test;
Use test;
CREATE TABLE t (ID int primary key auto_increment,name text);
INSERT into t values (1, ' Lucas ');
Show Binlog evnets;

7, add a new instance
M2:

[Mysqld]

# General con#
user = dBA
Port = 3308
Default_storage_engine = InnoDB
Socket =/tmp/mysql3308.sock
Pid_file =/data1/mysql3308/mysql.pid

# SAFETY #
Max_allowed_packet = 64M
Max_connect_errors = 1000000

# DATA STORAGE #binlog-format
DataDir =/data1/mysql3308/

# BINARY LOGGING #
Log_bin =/data1/mysql3308/3308-binlog
Expire_logs_days = 10
#sync_binlog = 1
relay-log=/data1/mysql3308/3308relaylog
#replicate-wild-do-table=hostility_url.%
#replicate-wild-do-table=guards.%

# CACHES and LIMITS #
Tmp_table_size = 32M
Max_heap_table_size = 32M
Query_cache_type = 1
Query_cache_size = 0
Max_connections = 5000
#max_user_connections = 200
Thread_cache_size = 512
Open_files_limit = 65535
Table_definition_cache = 4096
Table_open_cache = 4096
wait_timeout=7500
interactive_timeout=7500
Binlog-format=row
Character-set-server=utf8
Skip-name-resolve
Skip-character-set-client-handshake
back_log=1024

# INNODB #
#innodb_flush_method = O_direct
Innodb_data_home_dir =/data1/mysql3308/
#innodb_data_file_path = Ibdata1:1g:autoextend
innodb_log_group_home_dir=/data1/mysql3308/
Innodb_log_files_in_group = 3
Innodb_log_file_size = 1G
Innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
Innodb_file_format=barracuda
Innodb_support_xa=0


innodb_io_capacity=500
Innodb_max_dirty_pages_pct=90
Innodb_read_io_threads=16
Innodb_write_io_threads=8
Innodb_buffer_pool_instances=4
Innodb_thread_concurrency=0

#GTID

#gtid_mode = On
#enforce_gtid_consistency = On


# LOGGING #
Log_error =/data1/mysql3308/error.log
#log_queries_not_using_indexes = 1
Slow_query_log = 1
Slow_query_log_file =/data1/mysql3308/mysql-slow.log
long_query_time=0.05
server_id=3307
Innodb_buffer_pool_size = 1G
Report-host=localhost #此处的localhost一定要存在与hosts文件里, i.e.: localhost 127.0.0.1
report-port=3307
Log_slave_updates = On

#group replication
Gtid_mode = On
Enforce_gtid_consistency = On
Master_info_repository = table
Relay_log_info_repository = table
Binlog_checksum=none
Log_slave_updates=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= "localhost:24901"
Loose-group_replication_group_seeds= "localhost:24901,localhost:24902"
Loose-group_replication_single_primary_mode = True
loose-group_replication_bootstrap_group= off
Loose-group_replication_enforce_update_everywhere_checks = False


# # MYSQL_VERSION=MYSQL57
[MySQL]
prompt = \[email protected]\h:\p [\d]>

8, launch instance & Configure Group replication
Nohup/usr/local/mysql57/bin/mysqld_safe--defaults-file=/data1/mysql3308/my3308.cnf 2>/dev/null &
Mysql-uroot-s/tmp/mysql3308.sock-p123456

Set sql_log_bin=0;
Create user [email protected] '% ';
Grant Replication Slave on * * to [e-mail protected] '% ' identified by ' rpl_pass ';
Flush privileges;
Set sql_log_bin=1;
Change Master to master_user= ' Rpl_user ', master_password= ' Rpl_pass ' for Channel ' group_replication_recovery ';

9, Start group replication
Install plugin group_replication soname ' group_replication.so ';
Show plugins;
Start group_replication;
SELECT * from Performance_schema.replication_group_members;

10, test the synchronization is normal
/usr/local/mysql57/bin/mysql-uroot-p123456-s/tmp/mysql3308.sock

show databases;


Note: The new instance has been reported in the process of recovering, mainly in the configuration of MySQL Report-host is not configured, or the value of report-host configuration is not in the system hosts

MySQL Group Replication

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.