这里有新鲜出炉的 Mysql 教程,程序狗速度看过来!
MySQL 是一个开放源码的小型关联式数据库管理系统,开发者为瑞典 MySQL AB 公司。MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。
下面小编就为大家带来一篇 MySQL-group-replication 配置步骤 (推荐)。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
MySQL-Group-Replication 是 mysql-5.7.17 版本开发出来的新特性;它在 master-slave 之间实现了强一致性,
但是就目前来说主要是性能不太好。
【1】确定当前的 mysql 数据库版本为 5.7.17 及以上
- /usr/local / mysql / bin / mysqld--version / usr / local / mysql / bin / mysqld Ver 5.7.17
- for linux - glibc2.5 on x86_64(MySQL Community Server(GPL))
【2】实验环境为一台主机上安装 3 台 mysql,它们三个组成一个 group-replication 组
/tmp/4406.cnf 内容如下:
- [mysqld]
- ####: for global
- user =jianglexing # mysql
- basedir =/usr/local/mysql # /usr/local/mysql/
- datadir =/tmp/4406/ # /usr/local/mysql/data
- server_id =4406 # 0
- port =4406 # 3306
- socket =/tmp/4406/mysql.sock # /tmp/mysql.sock
- auto_increment_increment =1 # 1
- auto_increment_offset =1 # 1
- lower_case_table_names =1 # 0
- secure_file_priv = # null
- ####: for binlog
- binlog_format =row # row
- log_bin =mysql-bin # off
- binlog_rows_query_log_events =on # off
- log_slave_updates =on # off
- expire_logs_days =4 # 0
- binlog_cache_size =32768 # 32768(32k)
- binlog_checksum =none # CRC32
- sync_binlog =1 # 1
- ####: for error-log
- log_error =mysql-err.log # /usr/local/mysql/data/localhost.localdomain.err
- ####: for slow query log
- ####: for gtid
- gtid_mode =on # off
- enforce_gtid_consistency =on # off
- ####: for replication
- master_info_repository =table # file
- relay_log_info_repository =table # file
- ####: for group replication
- transaction_write_set_extraction =XXHASH64 # off
- loose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #
- loose-group_replication_start_on_boot =off # off
- loose-group_replication_local_address ="127.0.0.1:24901" #
- loose-group_replication_group_seeds ="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
- loose-group_replication_bootstrap_group =off # off
- ####: for innodb
- default_storage_engine =innodb # innodb
- default_tmp_storage_engine =innodb # innodb
- innodb_data_file_path =ibdata1:12M:autoextend # ibdata1:12M:autoextend
- innodb_temp_data_file_path =ibtmp1:12M:autoextend # ibtmp1:12M:autoextend
- innodb_log_group_home_dir =./ # ./
- innodb_log_files_in_group =2 # 2
- innodb_log_file_size =48M # 50331648(48M)
- innodb_file_format =Barracuda # Barracuda
- innodb_file_per_table =on # on
- innodb_page_size =16k # 16384(16k)
- innodb_thread_concurrency =0 # 0
- innodb_read_io_threads =4 # 4
- innodb_write_io_threads =4 # 4
- innodb_purge_threads =4 # 4
- innodb_print_all_deadlocks =on # off
- innodb_deadlock_detect =on # on
- innodb_lock_wait_timeout =50 # 50
- innodb_spin_wait_delay =6 # 6
- innodb_autoinc_lock_mode =2 # 1
- innodb_stats_persistent =on # on
- innodb_stats_persistent_sample_pages =20 # 20
- innodb_adaptive_hash_index =on # on
- innodb_change_buffering =all # all
- innodb_change_buffer_max_size =25 # 25
- innodb_flush_neighbors =1 # 1
- innodb_flush_method =O_DIRECT #
- innodb_doublewrite =on # on
- innodb_log_buffer_size =16M # 16777216(16M)
- innodb_flush_log_at_timeout =1 # 1
- innodb_flush_log_at_trx_commit =1 # 1
- autocommit =1 # 1
- [client]
- auto-rehash
/tmp/5506.cnf 内容如下:
- [mysqld]
- ####: for global
- user =jianglexing # mysql
- basedir =/usr/local/mysql # /usr/local/mysql/
- datadir =/tmp/5506 # /usr/local/mysql/data
- server_id =5506 # 0
- port =5506 # 3306
- socket =/tmp/5506/mysql.sock # /tmp/mysql.sock
- auto_increment_increment =1 # 1
- auto_increment_offset =1 # 1
- lower_case_table_names =1 # 0
- secure_file_priv = # null
- ####: for binlog
- binlog_format =row # row
- log_bin =mysql-bin # off
- binlog_rows_query_log_events =on # off
- log_slave_updates =on # off
- expire_logs_days =4 # 0
- binlog_cache_size =32768 # 32768(32k)
- binlog_checksum =none # CRC32
- sync_binlog =1 # 1
- ####: for error-log
- log_error =mysql-err.log # /usr/local/mysql/data/localhost.localdomain.err
- ####: for slow query log
- ####: for gtid
- gtid_mode =on # off
- enforce_gtid_consistency =on # off
- ####: for replication
- master_info_repository =table # file
- relay_log_info_repository =table # file
- ####: for group replication
- transaction_write_set_extraction =XXHASH64 # off
- loose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #
- loose-group_replication_start_on_boot =off # off
- loose-group_replication_local_address ="127.0.0.1:24902" #
- loose-group_replication_group_seeds ="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
- loose-group_replication_bootstrap_group =off # off
- ####: for innodb
- default_storage_engine =innodb # innodb
- default_tmp_storage_engine =innodb # innodb
- innodb_data_file_path =ibdata1:12M:autoextend # ibdata1:12M:autoextend
- innodb_temp_data_file_path =ibtmp1:12M:autoextend # ibtmp1:12M:autoextend
- innodb_log_group_home_dir =./ # ./
- innodb_log_files_in_group =2 # 2
- innodb_log_file_size =48M # 50331648(48M)
- innodb_file_format =Barracuda # Barracuda
- innodb_file_per_table =on # on
- innodb_page_size =16k # 16384(16k)
- innodb_thread_concurrency =0 # 0
- innodb_read_io_threads =4 # 4
- innodb_write_io_threads =4 # 4
- innodb_purge_threads =4 # 4
- innodb_print_all_deadlocks =on # off
- innodb_deadlock_detect =on # on
- innodb_lock_wait_timeout =50 # 50
- innodb_spin_wait_delay =6 # 6
- innodb_autoinc_lock_mode =2 # 1
- innodb_stats_persistent =on # on
- innodb_stats_persistent_sample_pages =20 # 20
- innodb_adaptive_hash_index =on # on
- innodb_change_buffering =all # all
- innodb_change_buffer_max_size =25 # 25
- innodb_flush_neighbors =1 # 1
- innodb_flush_method =O_DIRECT #
- innodb_doublewrite =on # on
- innodb_log_buffer_size =16M # 16777216(16M)
- innodb_flush_log_at_timeout =1 # 1
- innodb_flush_log_at_trx_commit =1 # 1
- autocommit =1 # 1
/tmp/6606.cnf 内容如下:
- [mysqld]
- ####: for global
- user =jianglexing # mysql
- basedir =/usr/local/mysql # /usr/local/mysql/
- datadir =/tmp/6606/ # /usr/local/mysql/data
- server_id =6606 # 0
- port =6606 # 3306
- socket =/tmp/6606/mysql.sock # /tmp/mysql.sock
- auto_increment_increment =1 # 1
- auto_increment_offset =1 # 1
- lower_case_table_names =1 # 0
- secure_file_priv = # null
- ####: for binlog
- binlog_format =row # row
- log_bin =mysql-bin # off
- binlog_rows_query_log_events =on # off
- log_slave_updates =on # off
- expire_logs_days =4 # 0
- binlog_cache_size =32768 # 32768(32k)
- binlog_checksum =none # CRC32
- sync_binlog =1 # 1
- ####: for error-log
- log_error =mysql-err.log # /usr/local/mysql/data/localhost.localdomain.err
- ####: for slow query log
- ####: for gtid
- gtid_mode =on # off
- enforce_gtid_consistency =on # off
- ####: for replication
- master_info_repository =table # file
- relay_log_info_repository =table # file
- ####: for group replication
- transaction_write_set_extraction =XXHASH64 # off
- loose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #
- loose-group_replication_start_on_boot =off # off
- loose-group_replication_local_address ="127.0.0.1:24903" #
- loose-group_replication_group_seeds ="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
- loose-group_replication_bootstrap_group =off # off
- ####: for innodb
- default_storage_engine =innodb # innodb
- default_tmp_storage_engine =innodb # innodb
- innodb_data_file_path =ibdata1:12M:autoextend # ibdata1:12M:autoextend
- innodb_temp_data_file_path =ibtmp1:12M:autoextend # ibtmp1:12M:autoextend
- innodb_log_group_home_dir =./ # ./
- innodb_log_files_in_group =2 # 2
- innodb_log_file_size =48M # 50331648(48M)
- innodb_file_format =Barracuda # Barracuda
- innodb_file_per_table =on # on
- innodb_page_size =16k # 16384(16k)
- innodb_thread_concurrency =0 # 0
- innodb_read_io_threads =4 # 4
- innodb_write_io_threads =4 # 4
- innodb_purge_threads =4 # 4
- innodb_print_all_deadlocks =on # off
- innodb_deadlock_detect =on # on
- innodb_lock_wait_timeout =50 # 50
- innodb_spin_wait_delay =6 # 6
- innodb_autoinc_lock_mode =2 # 1
- innodb_stats_persistent =on # on
- innodb_stats_persistent_sample_pages =20 # 20
- innodb_adaptive_hash_index =on # on
- innodb_change_buffering =all # all
- innodb_change_buffer_max_size =25 # 25
- innodb_flush_neighbors =1 # 1
- innodb_flush_method =O_DIRECT #
- innodb_doublewrite =on # on
- innodb_log_buffer_size =16M # 16777216(16M)
- innodb_flush_log_at_timeout =1 # 1
- innodb_flush_log_at_trx_commit =1 # 1
- autocommit =1 # 1
【3】初始化三个数据库实例
- cd /usr/local/mysql/
- ./bin/mysqld --defautls-file=/tmp/4406.cnf --datadir=/tmp/4406 --initialize-insecrue
- ./bin/mysqld --defautls-file=/tmp/5506.cnf --datadir=/tmp/5506 --initialize-insecrue
- ./bin/mysqld --defautls-file=/tmp/6606.cnf --datadir=/tmp/6606 --initialize-insecrue
【4】配置 group-replication 的初始实例
- /usr/local / mysql / bin / mysqld--defaults - file = /tmp/4406.cnf &
- mysql - h127.0.0.1 - uroot - P4406
- --增加用户set sql_log_bin = 0;
- create user rpl_user@'%'identified by '123456';
- grant replication slave,
- replication client on * . * to rpl_user@'%';
- create user rpl_user@'127.0.0.1'identified by '123456';
- grant replication slave,
- replication client on * . * to rpl_user@'127.0.0.1';
- create user rpl_user@'localhost'identified by '123456';
- grant replication slave,
- replication client on * . * to rpl_user@'localhost';
- set sql_log_bin = 1;
- --增加复制凭证change master to master_user = 'rpl_user',
- master_password = '123456'
- for channel 'group_replication_recovery';
- --安装组复制物件install plugin group_replication soname 'group_replication.so';
- --启动组复制set global group_replication_bootstrap_group = on;
- start group_replication;
- set global group_replication_bootstrap_group = off;
【5】5506 实例的配置过程如下:
- /usr/local / mysql / bin / mysqld--defaults - file = /tmp/5506.cnf &
- mysql - h127.0.0.1 - uroot - P5506
- --增加用户set sql_log_bin = 0;
- create user rpl_user@'%'identified by '123456';
- grant replication slave,
- replication client on * . * to rpl_user@'%';
- create user rpl_user@'127.0.0.1'identified by '123456';
- grant replication slave,
- replication client on * . * to rpl_user@'127.0.0.1';
- create user rpl_user@'localhost'identified by '123456';
- grant replication slave,
- replication client on * . * to rpl_user@'localhost';
- set sql_log_bin = 1;
- --增加复制凭证change master to master_user = 'rpl_user',
- master_password = '123456'
- for channel 'group_replication_recovery';
- --安装组复制物件install plugin group_replication soname 'group_replication.so';
- --启动组复制start group_replication;#注意这里不是初始化了,只要加入就行
【6】6606 实例的操作与 5506 的操作一样,这样 group replication 的配置就完成了。
以上这篇 MySQL-group-replication 配置步骤 (推荐) 就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持 PHPERZ。
来源: http://www.phperz.com/article/17/0815/339390.html