MySQL5.7 Group Replication (MGR)--Mysql的组复制之多主模式

MGR——Mysql的组复制之多主模式

以下测试在VMware环境：

操作系统：Centos 6.9 X86_64

数据库：Mysql 5.7 (mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) )

所使用虚拟机共计三台，ip分别是192.168.153.157、192.168.153.158、192.168.153.159

MGR模式：多主模式。

一、在三台db服务器上面设置/etc/hosts映射，如下：

[root@mgr157 ~]# cat /etc/hosts

1 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
2 ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
3 
4 192.168.153.157  mgr157
5 192.168.153.158  mgr158
6 192.168.153.159  mgr159

二、my.cnf文件修改：

vi /etc/my.cnf （157服务器）

  1 # For advice on how to change settings please see
  2 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
  3 
  4 [mysqld]
  5 
  6 #-------------------gobal variables------------#
  7 gtid_mode = ON
  8 enforce_gtid_consistency = ON
  9 master_info_repository = TABLE
 10 relay_log_info_repository = TABLE
 11 binlog_checksum = NONE
 12 log_slave_updates = ON
 13 log-bin = mysql-bin
 14 transaction_write_set_extraction = XXHASH64 
 15 loose-group_replication_group_name = '157be252-2b71-11e6-b8f4-00212889f856'
 16 loose-group_replication_start_on_boot = off
 17 loose-group_replication_bootstrap_group = off
 18 loose-group_replication_local_address = '192.168.153.157:33061'
 19 loose-group_replication_group_seeds ='192.168.153.157:33061,192.168.153.158:33061,192.168.153.159:33061'
 20 loose-group_replication_single_primary_mode = off
 21 loose-group_replication_enforce_update_everywhere_checks = on
 22 max_connect_errors = 20000
 23 max_connections = 2000
 24 wait_timeout = 3600
 25 interactive_timeout = 3600
 26 net_read_timeout = 3600
 27 net_write_timeout = 3600
 28 table_open_cache = 1024
 29 table_definition_cache = 1024
 30 thread_cache_size = 512
 31 open_files_limit = 10000
 32 character-set-server = utf8
 33 collation-server = utf8_bin
 34 skip_external_locking
 35 performance_schema = 1
 36 user = mysql
 37 myisam_recover_options = DEFAULT
 38 skip-name-resolve
 39 local_infile = 0
 40 lower_case_table_names = 0
 41 
 42 #--------------------innoDB------------#
 43 innodb_buffer_pool_size = 2000M
 44 #innodb_data_file_path = ibdata1:200M:autoextend
 45 innodb_flush_log_at_trx_commit = 1
 46 innodb_io_capacity = 600
 47 innodb_lock_wait_timeout = 120
 48 innodb_log_buffer_size = 8M
 49 innodb_log_file_size = 200M
 50 innodb_log_files_in_group = 3
 51 innodb_max_dirty_pages_pct = 85
 52 innodb_read_io_threads = 8
 53 innodb_write_io_threads = 8
 54 innodb_support_xa = 1
 55 innodb_thread_concurrency = 32
 56 innodb_file_per_table
 57 innodb_rollback_on_timeout
 58 
 59 #------------session variables-------#
 60 join_buffer_size = 8M
 61 key_buffer_size = 256M
 62 bulk_insert_buffer_size = 8M
 63 max_heap_table_size = 96M
 64 tmp_table_size = 96M
 65 read_buffer_size = 8M
 66 sort_buffer_size = 2M
 67 max_allowed_packet = 64M
 68 read_rnd_buffer_size = 32M
 69 
 70 #------------MySQL Log----------------#
 71 log-bin = my3306-bin
 72 binlog_format = row
 73 sync_binlog = 1
 74 expire_logs_days = 15
 75 max_binlog_cache_size = 128M
 76 max_binlog_size = 500M
 77 binlog_cache_size = 64k
 78 slow_query_log
 79 log-slow-admin-statements
 80 log_warnings = 1
 81 long_query_time = 0.25
 82 
 83 #---------------replicate--------------#
 84 relay-log-index = relay3306.index
 85 relay-log = relay3306
 86 server-id =157
 87 init_slave = 'set sql_mode=STRICT_ALL_TABLES'
 88 log-slave-updates
 89 [myisamchk]
 90 key_buffer = 512M
 91 sort_buffer_size = 512M
 92 read_buffer = 8M
 93 write_buffer = 8M
 94 [mysqlhotcopy]
 95 interactive-timeout
 96 [mysqld_safe]
 97 open-files-limit = 8192
 98 
 99 
100 #
101 # Remove leading # and set to the amount of RAM for the most important data
102 # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
103 # innodb_buffer_pool_size = 128M
104 #
105 # Remove leading # to turn on a very important data integrity option: logging
106 # changes to the binary log between backups.
107 # log_bin
108 #
109 # Remove leading # to set options mainly useful for reporting servers.
110 # The server defaults are faster for transactions and fast SELECTs.
111 # Adjust sizes as needed, experiment to find the optimal values.
112 # join_buffer_size = 128M
113 # sort_buffer_size = 2M
114 # read_rnd_buffer_size = 2M
115 datadir=/var/lib/mysql
116 socket=/var/lib/mysql/mysql.sock
117 
118 # Disabling symbolic-links is recommended to prevent assorted security risks
119 symbolic-links=0
120 
121 log-error=/var/log/mysqld.log
122 pid-file=/var/run/mysqld/mysqld.pid

View Code

vi /etc/my.cnf （158务器）

  1 # For advice on how to change settings please see
  2 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
  3 
  4 [mysqld]
  5 
  6 #-------------------gobal variables------------#
  7 gtid_mode = ON
  8 enforce_gtid_consistency = ON
  9 master_info_repository = TABLE
 10 relay_log_info_repository = TABLE
 11 binlog_checksum = NONE
 12 log_slave_updates = ON
 13 log-bin = mysql-bin
 14 transaction_write_set_extraction = XXHASH64 
 15 loose-group_replication_group_name = '157be252-2b71-11e6-b8f4-00212889f856'
 16 loose-group_replication_start_on_boot = off
 17 loose-group_replication_bootstrap_group = off
 18 loose-group_replication_local_address = '192.168.153.158:33061'
 19 loose-group_replication_group_seeds ='192.168.153.157:33061,192.168.153.158:33061,192.168.153.159:33061'
 20 loose-group_replication_single_primary_mode = off
 21 loose-group_replication_enforce_update_everywhere_checks = on
 22 max_connect_errors = 20000
 23 max_connections = 2000
 24 wait_timeout = 3600
 25 interactive_timeout = 3600
 26 net_read_timeout = 3600
 27 net_write_timeout = 3600
 28 table_open_cache = 1024
 29 table_definition_cache = 1024
 30 thread_cache_size = 512
 31 open_files_limit = 10000
 32 character-set-server = utf8
 33 collation-server = utf8_bin
 34 skip_external_locking
 35 performance_schema = 1
 36 user = mysql
 37 myisam_recover_options = DEFAULT
 38 skip-name-resolve
 39 local_infile = 0
 40 lower_case_table_names = 0
 41 
 42 #--------------------innoDB------------#
 43 innodb_buffer_pool_size = 2000M
 44 #innodb_data_file_path = ibdata1:200M:autoextend
 45 innodb_flush_log_at_trx_commit = 1
 46 innodb_io_capacity = 600
 47 innodb_lock_wait_timeout = 120
 48 innodb_log_buffer_size = 8M
 49 innodb_log_file_size = 200M
 50 innodb_log_files_in_group = 3
 51 innodb_max_dirty_pages_pct = 85
 52 innodb_read_io_threads = 8
 53 innodb_write_io_threads = 8
 54 innodb_support_xa = 1
 55 innodb_thread_concurrency = 32
 56 innodb_file_per_table
 57 innodb_rollback_on_timeout
 58 
 59 #------------session variables-------#
 60 join_buffer_size = 8M
 61 key_buffer_size = 256M
 62 bulk_insert_buffer_size = 8M
 63 max_heap_table_size = 96M
 64 tmp_table_size = 96M
 65 read_buffer_size = 8M
 66 sort_buffer_size = 2M
 67 max_allowed_packet = 64M
 68 read_rnd_buffer_size = 32M
 69 
 70 #------------MySQL Log----------------#
 71 log-bin = my3306-bin
 72 binlog_format = row
 73 sync_binlog = 1
 74 expire_logs_days = 15
 75 max_binlog_cache_size = 128M
 76 max_binlog_size = 500M
 77 binlog_cache_size = 64k
 78 slow_query_log
 79 log-slow-admin-statements
 80 log_warnings = 1
 81 long_query_time = 0.25
 82 
 83 #---------------replicate--------------#
 84 relay-log-index = relay3306.index
 85 relay-log = relay3306
 86 server-id =158
 87 init_slave = 'set sql_mode=STRICT_ALL_TABLES'
 88 log-slave-updates
 89 [myisamchk]
 90 key_buffer = 512M
 91 sort_buffer_size = 512M
 92 read_buffer = 8M
 93 write_buffer = 8M
 94 [mysqlhotcopy]
 95 interactive-timeout
 96 [mysqld_safe]
 97 open-files-limit = 8192
 98 
 99 
100 #
101 # Remove leading # and set to the amount of RAM for the most important data
102 # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
103 # innodb_buffer_pool_size = 128M
104 #
105 # Remove leading # to turn on a very important data integrity option: logging
106 # changes to the binary log between backups.
107 # log_bin
108 #
109 # Remove leading # to set options mainly useful for reporting servers.
110 # The server defaults are faster for transactions and fast SELECTs.
111 # Adjust sizes as needed, experiment to find the optimal values.
112 # join_buffer_size = 128M
113 # sort_buffer_size = 2M
114 # read_rnd_buffer_size = 2M
115 datadir=/var/lib/mysql
116 socket=/var/lib/mysql/mysql.sock
117 
118 # Disabling symbolic-links is recommended to prevent assorted security risks
119 symbolic-links=0
120 
121 log-error=/var/log/mysqld.log
122 pid-file=/var/run/mysqld/mysqld.pid

View Code

vi /etc/my.cnf （159务器）

  1 # For advice on how to change settings please see
  2 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
  3 
  4 [mysqld]
  5 
  6 #-------------------gobal variables------------#
  7 gtid_mode = ON
  8 enforce_gtid_consistency = ON
  9 master_info_repository = TABLE
 10 relay_log_info_repository = TABLE
 11 binlog_checksum = NONE
 12 log_slave_updates = ON
 13 log-bin = mysql-bin
 14 transaction_write_set_extraction = XXHASH64 
 15 loose-group_replication_group_name = '157be252-2b71-11e6-b8f4-00212889f856'
 16 loose-group_replication_start_on_boot = off
 17 loose-group_replication_bootstrap_group = off
 18 loose-group_replication_local_address = '192.168.153.159:33061'
 19 loose-group_replication_group_seeds ='192.168.153.157:33061,192.168.153.158:33061,192.168.153.159:33061'
 20 loose-group_replication_single_primary_mode = off
 21 loose-group_replication_enforce_update_everywhere_checks = on
 22 max_connect_errors = 20000
 23 max_connections = 2000
 24 wait_timeout = 3600
 25 interactive_timeout = 3600
 26 net_read_timeout = 3600
 27 net_write_timeout = 3600
 28 table_open_cache = 1024
 29 table_definition_cache = 1024
 30 thread_cache_size = 512
 31 open_files_limit = 10000
 32 character-set-server = utf8
 33 collation-server = utf8_bin
 34 skip_external_locking
 35 performance_schema = 1
 36 user = mysql
 37 myisam_recover_options = DEFAULT
 38 skip-name-resolve
 39 local_infile = 0
 40 lower_case_table_names = 0
 41 
 42 #--------------------innoDB------------#
 43 innodb_buffer_pool_size = 2000M
 44 #innodb_data_file_path = ibdata1:200M:autoextend
 45 innodb_flush_log_at_trx_commit = 1
 46 innodb_io_capacity = 600
 47 innodb_lock_wait_timeout = 120
 48 innodb_log_buffer_size = 8M
 49 innodb_log_file_size = 200M
 50 innodb_log_files_in_group = 3
 51 innodb_max_dirty_pages_pct = 85
 52 innodb_read_io_threads = 8
 53 innodb_write_io_threads = 8
 54 innodb_support_xa = 1
 55 innodb_thread_concurrency = 32
 56 innodb_file_per_table
 57 innodb_rollback_on_timeout
 58 
 59 #------------session variables-------#
 60 join_buffer_size = 8M
 61 key_buffer_size = 256M
 62 bulk_insert_buffer_size = 8M
 63 max_heap_table_size = 96M
 64 tmp_table_size = 96M
 65 read_buffer_size = 8M
 66 sort_buffer_size = 2M
 67 max_allowed_packet = 64M
 68 read_rnd_buffer_size = 32M
 69 
 70 #------------MySQL Log----------------#
 71 log-bin = my3306-bin
 72 binlog_format = row
 73 sync_binlog = 1
 74 expire_logs_days = 15
 75 max_binlog_cache_size = 128M
 76 max_binlog_size = 500M
 77 binlog_cache_size = 64k
 78 slow_query_log
 79 log-slow-admin-statements
 80 log_warnings = 1
 81 long_query_time = 0.25
 82 
 83 #---------------replicate--------------#
 84 relay-log-index = relay3306.index
 85 relay-log = relay3306
 86 server-id =159
 87 init_slave = 'set sql_mode=STRICT_ALL_TABLES'
 88 log-slave-updates
 89 [myisamchk]
 90 key_buffer = 512M
 91 sort_buffer_size = 512M
 92 read_buffer = 8M
 93 write_buffer = 8M
 94 [mysqlhotcopy]
 95 interactive-timeout
 96 [mysqld_safe]
 97 open-files-limit = 8192
 98 
 99 
100 #
101 # Remove leading # and set to the amount of RAM for the most important data
102 # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
103 # innodb_buffer_pool_size = 128M
104 #
105 # Remove leading # to turn on a very important data integrity option: logging
106 # changes to the binary log between backups.
107 # log_bin
108 #
109 # Remove leading # to set options mainly useful for reporting servers.
110 # The server defaults are faster for transactions and fast SELECTs.
111 # Adjust sizes as needed, experiment to find the optimal values.
112 # join_buffer_size = 128M
113 # sort_buffer_size = 2M
114 # read_rnd_buffer_size = 2M
115 datadir=/var/lib/mysql
116 socket=/var/lib/mysql/mysql.sock
117 
118 # Disabling symbolic-links is recommended to prevent assorted security risks
119 symbolic-links=0
120 
121 log-error=/var/log/mysqld.log
122 pid-file=/var/run/mysqld/mysqld.pid

View Code

三、配置 group_replication

组内每台主机,都需要先安装组复制插件.否则会导致启动失败.
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

1.server157:

1 mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.153.%' IDENTIFIED BY 'RKYSrkys123!@#';
2 mysql>CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='RKYSrkys123!@#' FOR CHANNEL 'group_replication_recovery';
3 mysql>set global group_replication_ip_whitelist="127.0.0.1/32,192.168.153.0/24";
4 mysql>SET GLOBAL group_replication_bootstrap_group = ON; 
5 mysql>START GROUP_REPLICATION;
6 mysql>SET GLOBAL group_replication_bootstrap_group = OFF;

2.server158、159：

1 mysql>CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='RKYSrkys123!@#' FOR mysql>CHANNEL 'group_replication_recovery';
2 mysql>set global group_replication_ip_whitelist="127.0.0.1/32,192.168.153.0/24";
3 mysql>set global group_replication_allow_local_disjoint_gtids_join=ON;
4 mysql>START GROUP_REPLICATION;

3.查看SELECT * FROM performance_schema.replication_group_members;

server157、158、159上都执行SQL语句查看组状态
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | c8a5168d-2103-11e8-a3e1-000c29cb1157 | mgr157      |        3306 | ONLINE       |
| group_replication_applier | c8a5168d-2103-11e8-a3e1-000c29cb1159 | mgr159      |        3306 | ONLINE       |
| group_replication_applier | c8a5168d-2103-11e8-a3e1-000c29cb1ae2 | mgr158      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
online代表已经设置成功。

四、检验：

1： server157、158、159(分别创建test157、test158和test159测试)

server157上：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database test157;
Query OK, 1 row affected (0.04 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test157            |
+--------------------+
5 rows in set (0.00 sec)

server158上：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test157　　　　　　　|
+--------------------+
5 rows in set (0.02 sec)

mysql> create database test158;
Query OK, 1 row affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test157            |
| test158            |
+--------------------+
6 rows in set (0.00 sec)

server159上：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test157　　　　　　　|
| test158　　　　　　　|
+--------------------+
6 rows in set (0.00 sec)

mysql> create database test159;
Query OK, 1 row affected (0.05 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test157            |
| test158            |
| test159            |
+--------------------+
7 rows in set (0.00 sec)

在server157、158上也分别验证：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test157            |
| test158            |
| test159            |
+--------------------+

2：模拟宕机一个节点验证

(1)关闭159的数据库实例
　　service mysqld stop
(2)在157节点操作
查询组成员，发现159已不在组中

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | c8a5168d-2103-11e8-a3e1-000c29cb1157 | mgr157      |        3306 | ONLINE       |
| group_replication_applier | c8a5168d-2103-11e8-a3e1-000c29cb1ae2 | mgr158      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

(3)写入数据

server157,158,159上：
mysql> use test157
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE tb1(id int not null primary key);
Query OK, 0 rows affected (0.41 sec)

server157,158上：
mysql> show tables;
+-------------------+
| Tables_in_test157 |
+-------------------+
| tb1               |
+-------------------+
1 row in set (0.01 sec)


server159上：

mysql> show tables;
Empty set (0.00 sec)

(4)启动159的数据库

#修改配置文件，自动启动组复制
#loose-group_replication_start_on_boot = on
#group_replication_allow_local_disjoint_gtids_join = on

mysql> STOP GROUP_REPLICATION;
Query OK, 0 rows affected (9.30 sec)

(5)再次查看组成员，发现159已重新加入组

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | c8a5168d-2103-11e8-a3e1-000c29cb1157 | mgr157      |        3306 | ONLINE       |
| group_replication_applier | c8a5168d-2103-11e8-a3e1-000c29cb1159 | mgr159      |        3306 | ONLINE       |
| group_replication_applier | c8a5168d-2103-11e8-a3e1-000c29cb1ae2 | mgr158      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

(6)查看数据，发现数据已同步

在server157、158、159上：
mysql> show tables;
+-------------------+
| Tables_in_test157 |
+-------------------+
| tb1               |
+-------------------+
1 row in set (0.01 sec)

到此，已完毕。

######################################################################################################################

附：参考资料

#[client]
#port = 3306
#socket = /usr/local/mysql/tmp/mysql.sock

[mysqld]
#port = 3306
#socket = /usr/local/mysql/tmp/mysql.sock
back_log = 80
basedir = /usr/local/mysql
tmpdir = /tmp
datadir = /usr/local/mysql/data

#-------------------gobal variables------------#
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log-bin = /usr/local/mysql/log/mysql-bin
transaction_write_set_extraction = XXHASH64 #以便在server收集写集合的同时将其记录到二进制日志。写集合基于每行的主键，并且是行更改后的唯一标识此标识将用于检测冲突。
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212889f856' #组的名字可以随便起,但不能用主机的GTID(为mgr高可用组起一个名字，这个名字一定要是uuid格式的。)【此处三台一致】
loose-group_replication_start_on_boot = off #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。
loose-group_replication_bootstrap_group = off #同上
loose-group_replication_local_address = '192.168.153.157:33061' 【三台分别是192.168.153.157:33061/192.168.153.158:33061/192.168.153.159:33061】 #写自己主机所在IP(mgr各实例之前都是要进行通信的、这个配置项设置的就是本实例所监听的ip：端口)
loose-group_replication_group_seeds ='192.168.153.157:33061,192.168.153.158:33061,192.168.153.159:33061' #(各mgr实例所监听的ip:端口信息) 【三台一致】
loose-group_replication_single_primary_mode = off #关闭单主模式的参数
loose-group_replication_enforce_update_everywhere_checks = on #开启多主模式的参数
max_connect_errors = 20000 #max_connect_errors是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。max_connect_errors的值与性能并无太大关系。默认情况下，my.cnf文件中可能没有此行，如果需要设置此数值，手动添加即可。
max_connections = 2000
wait_timeout = 3600
interactive_timeout = 3600
net_read_timeout = 3600
net_write_timeout = 3600
table_open_cache = 1024
table_definition_cache = 1024
thread_cache_size = 512
open_files_limit = 10000
character-set-server = utf8
collation-server = utf8_bin
skip_external_locking
performance_schema = 1
user = mysql
myisam_recover_options = DEFAULT
skip-name-resolve
local_infile = 0
lower_case_table_names = 0
#lower_case_table_names：此参数不可以动态修改，必须重启数据库
#lower_case_table_names = 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
#lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的
#lower_case_table_names=2, 表名存储为给定的大小写但是比较的时候是小写的

#--------------------innoDB------------#
innodb_buffer_pool_size = 2000M
#innodb_data_file_path = ibdata1:200M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 600
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 8M
innodb_log_file_size = 200M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 85
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_support_xa = 1
innodb_thread_concurrency = 32
innodb_file_per_table
innodb_rollback_on_timeout

#------------session variables-------#
join_buffer_size = 8M
key_buffer_size = 256M
bulk_insert_buffer_size = 8M
max_heap_table_size = 96M
tmp_table_size = 96M
read_buffer_size = 8M
sort_buffer_size = 2M
max_allowed_packet = 64M
read_rnd_buffer_size = 32M

#------------MySQL Log----------------#
log-bin = my3306-bin
binlog_format = row #组复制依赖基于行的复制格式
sync_binlog = 1
expire_logs_days = 15
max_binlog_cache_size = 128M
max_binlog_size = 500M
binlog_cache_size = 64k
slow_query_log
log-slow-admin-statements
log_warnings = 1
long_query_time = 0.25

#---------------replicate--------------#
relay-log-index = relay3306.index
relay-log = relay3306
server-id =157 #【每个db的id唯一】
init_slave = 'set sql_mode=STRICT_ALL_TABLES'
log-slave-updates
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
#log-error = /usr/local/mysql/log/mysqld_error.log
######################################################################################################################