配置Mysql主从复制

要同步的库：test_master_slave

1、Master节点配置

基本配置

  1. vim /etc/my.cnf
  3. # 基本配置
  4. [client]
  5. default-character-set=utf8
  7. [mysql]
  8. default-character-set=utf8
  10. [mysqld]
  11. init_connect='SET collation_connection = utf8_unicode_ci'
  12. init_connect='SET NAMES utf8'
  13. character-set-server=utf8
  14. collation-server=utf8_unicode_ci
  15. skip-character-set-client-handshake
  16. skip-name-resolve

Master节点主从配置

  1. vim /etc/my.cnf
  3. # 添加主从复制部分配置，添加到[mysqld]下面
  4. server_id=1
  5. log-bin=mysql-bin
  6. binlog_format=MIXED
  7. read-only=0
  8. binlog-do-db=test_master_slave
  9. # 如果有多个库需要同步，可以继续添加
  10. #binlog-do-db=test_master_slave2
  12. # 忽略mysql自带的库的同步
  13. replicate-ignore-db=mysql
  14. replicate-ignore-db=sys
  15. replicate-ignore-db=information_schema
  16. replicate-ignore-db=performance_schema

重启数据库

  1. systemctl restart mysqld

2、Slave节点配置

基本配置

  1. vim /etc/my.cnf
  3. # 基本配置
  4. [client]
  5. default-character-set=utf8
  7. [mysql]
  8. default-character-set=utf8
  10. [mysqld]
  11. init_connect='SET collation_connection = utf8_unicode_ci'
  12. init_connect='SET NAMES utf8'
  13. character-set-server=utf8
  14. collation-server=utf8_unicode_ci
  15. skip-character-set-client-handshake
  16. skip-name-resolve

Slave节点主从复制配置

  1. vim /etc/my.cnf
  3. # 添加主从复制部分配置，添加到[mysqld]下面
  4. server_id=2
  5. log-bin=mysql-bin
  6. binlog_format=MIXED
  7. read-only=1
  8. binlog-do-db=test_master_slave
  9. # 如果有多个库需要同步，可以继续添加
  10. #binlog-do-db=test_master_slave2
  12. # 忽略mysql自带的库的同步
  13. replicate-ignore-db=mysql
  14. replicate-ignore-db=sys
  15. replicate-ignore-db=information_schema
  16. replicate-ignore-db=performance_schema

重启数据库

  1. systemctl restart mysqld

3、为master授权用户来同步它的数据

  1. # 使用mysql命令登录到master客户端
  2. mysql -uroot -p
  4. # 添加来同步数据的用户
  5. mysql> grant replication slave on *.* to 'backup'@'%' identified by 'Backup@123';
  7. # 查看master状态
  8. mysql> show master status\G;
  10. *************************** 1. row ***************************
  11.              File: mysql-bin.000001
  12.          Position: 724
  13.      Binlog_Do_DB: test_master_slave
  14.  Binlog_Ignore_DB: 
  15. Executed_Gtid_Set: 
  16. 1 row in set (0.00 sec)

4、配置slave同步

  1. # 使用mysql命令登录到master客户端
  2. mysql -uroot -p
  4. # 设置主库连接
  5. change master to master_host='192.168.14.132',master_user='backup',master_password='Backup@123',master_log_file='mysql-bin.000001',master_log_pos=0,master_port=3306;
  7. # 启动从库同步
  8. start slave;
  10. # 查看从库状态
  11. show slave status\G;