1、MySQL5.6开始主从复制有两种方式:
基于日志(binlog);
基于GTID(全局事务标示符)。
需要注意的是:GTID方式不支持临时表!所以如果你的业务系统要用到临时表的话就不要考虑这种方式了,至少目前最新版本MySQL5.6.33的GTID复制还是不支持临时表的。
所以本教程主要是告诉大家如何通过日志(binlog)方式做主从复制!
2、主服务器配置:
[root@localhost ~]# vim /etc/my.cnf
server-id = 1
log-bin=mysql-bin
log_bin_index=mysql-bin.index
sync_binlog=1
relay-log = mysql-relay-bin
log-slave-updates = 1
read_only = 0
innodb_log_buffer_size = 512M
innodb_flush_log_at_trx_commit = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
log_bin_trust_function_creators=1
3、从服务器配置
server-id = 2
log-bin=mysql-bin
sync_binlog=1
relay-log = mysql-relay-bin
log-slave-updates = 1
read_only = 1
innodb_log_buffer_size = 512M
innodb_flush_log_at_trx_commit = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
log_bin_trust_function_creators=1
4、在主数据库上创建用于主从复制的账户
mysql> grant replication slave on *.* to 'slave'@'192.168.40.%' identified by '123.com';
Query OK, 0 rows affected (0.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
mysql> show master status;
+------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------+----------+--------------+------------------+-------------------+
| mysqlmaster-bin.000001 | 551 | | | |
+------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从服务器:
mysql> change master to master_host='192.168.40.16',master_user='slave',master_password='123.com',master_log_file='master-bin.000001',master_log_pos=551;
Query OK, 0 rows affected, 2 warnings (0.50 sec)
mysql> start slave;
Query OK, 0 rows affected (0.07 sec)
基于日志(binlog);
基于GTID(全局事务标示符)。
需要注意的是:GTID方式不支持临时表!所以如果你的业务系统要用到临时表的话就不要考虑这种方式了,至少目前最新版本MySQL5.6.33的GTID复制还是不支持临时表的。
所以本教程主要是告诉大家如何通过日志(binlog)方式做主从复制!
2、主服务器配置:
[root@localhost ~]# vim /etc/my.cnf
server-id = 1
log-bin=mysql-bin
log_bin_index=mysql-bin.index
sync_binlog=1
relay-log = mysql-relay-bin
log-slave-updates = 1
read_only = 0
innodb_log_buffer_size = 512M
innodb_flush_log_at_trx_commit = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
log_bin_trust_function_creators=1
3、从服务器配置
server-id = 2
log-bin=mysql-bin
sync_binlog=1
relay-log = mysql-relay-bin
log-slave-updates = 1
read_only = 1
innodb_log_buffer_size = 512M
innodb_flush_log_at_trx_commit = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
log_bin_trust_function_creators=1
4、在主数据库上创建用于主从复制的账户
mysql> grant replication slave on *.* to 'slave'@'192.168.40.%' identified by '123.com';
Query OK, 0 rows affected (0.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
mysql> show master status;
+------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------+----------+--------------+------------------+-------------------+
| mysqlmaster-bin.000001 | 551 | | | |
+------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从服务器:
mysql> change master to master_host='192.168.40.16',master_user='slave',master_password='123.com',master_log_file='master-bin.000001',master_log_pos=551;
Query OK, 0 rows affected, 2 warnings (0.50 sec)
mysql> start slave;
Query OK, 0 rows affected (0.07 sec)