开启MySQL复制

所有数据库建立复制账户

MHA需求，因为切换主库后，其他从库会作为主库，这样就不用单独再创建账户了、

  1. CREATE USER 'repl'@'192.168.%' IDENTIFIED BY 'repl';
  2. GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%';

修改配置文件

  1. master_info_repository = TABLE
  2. relay_log_info_repository = TABLE
  3. sync_binlog = 1
  4. log-slave-updates = 1
  5. log-bin=mysql-bin
  6. binlog_format = ROW
  7. server-id=168001230

注释：

  1. #Master信息存储在表里
  2. master_info_repository = TABLE
  3. #Relaylog信息存储在表里
  4. relay_log_info_repository = TABLE
  5. #所有事务提交钱，写入binlog
  6. sync_binlog = 1
  7. #角色是从库时，产生binlog，级联复制用
  8. log-slave-updates = 1
  9. #binlog日志开关及名称
  10. log-bin=mysql-bin
  11. #binlog格式
  12. binlog_format = ROW
  13. #Server-id，保持唯一，建议IP地址
  14. server-id=168001230

配置MySQL复制

查看MySQL主库Position情况

  1. show master status\G

主库执行

  1. [root@localhost][(none)]> show master status;
  2. +------------------+----------+--------------+------------------+-------------------+
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  4. +------------------+----------+--------------+------------------+-------------------+
  5. | mysql-bin.000002 |      323 |              |                  |                   |
  6. +------------------+----------+--------------+------------------+-------------------+

从库创建同步配置

从库执行

  1. CHANGE MASTER TO MASTER_HOST='192.168.0.230',
  2. MASTER_USER='repl',
  3. MASTER_PASSWORD='repl',
  4. MASTER_LOG_FILE='mysql-bin.000002', 
  5. MASTER_LOG_POS=323;

出现两个warning

  1. | Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.   |
  2. | Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |

建议SSL和 start slave 时指定用户名密码，不将密码保存在主库

从库执行

  1. change master to  master_host='192.168.0.230',,master_log_file='mysql-bin.000002',master_log_pos=323;

启动复制进程

  1. start slave user='repl' password='repl';
  2. show slave status\G;

测试主库写入

主库查看状态

出现Binlog Dump 线程

  1. [root@localhost][(none)]> show processlist;
  2. +----+------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
  3. | Id | User | Host                | db   | Command     | Time | State                                                         | Info             |
  4. +----+------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
  5. |  6 | repl | 192.168.0.236:10745 | NULL | Binlog Dump | 5014 | Master has sent all binlog to slave; waiting for more updates | NULL             |
  6. |  8 | repl | 192.168.0.235:1619  | NULL | Binlog Dump |  101 | Master has sent all binlog to slave; waiting for more updates | NULL             |