MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的。
MySQL主从是基于binlog的，主上须开启binlog才能进行主从。
主从过程大致有3个步骤：
1）主将更改操作记录到binlog里
2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里中继日志
3）从根据relaylog里面的sql语句按顺序执行

MySQL主从配置 - 图2

配置

准备

主机名 IP MySQL版本 centos版本
server 192.168.100.10 mysql-5.6.47-linux-glibc2.12-x86_64.tar.gz centos7.2-1511
client 192.168.100.20

过程

主节点配置

  1. # err为错误日志
  2. [root@lnmp ~]# ls -a /data/mysql/
  3. .  ..  auto.cnf  ibdata1  ib_logfile0  ib_logfile1  lnmp.err  mysql  performance_schema  wordpress
  4. [root@lnmp ~]# vim /etc/my.cnf
  5. log-bin=wsw
  6. server-id= 41
  7. [root@lnmp ~]# service mysqld restart
  8. Shutting down MySQL. SUCCESS! 
  9. Starting MySQL.. SUCCESS! 
  10. # 可以看到多出log_bin名为开头的两个文件，wsw.000001、wsw.index
  11. # 这两个文件为bin_log文件和索引文件 可以参考流程图
  12. [root@server ~]# ls -a /data/mysql/   
  13. .   auto.cnf  ib_logfile0  mysql               server.err  test        zzy.index
  14. ..  ibdata1   ib_logfile1  performance_schema  server.pid  zzy.000001
  15. [root@server ~]# mysql -uroot -p000000
  16. Warning: Using a password on the command line interface can be insecure.
  17. Welcome to the MySQL monitor.  Commands end with ; or \g.
  18. Your MySQL connection id is 1
  19. Server version: 5.6.47-log MySQL Community Server (GPL)
  21. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  23. Oracle is a registered trademark of Oracle Corporation and/or its
  24. affiliates. Other names may be trademarks of their respective
  25. owners.
  27. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  28. # 创建主从用户权限
  29. mysql> grant replication slave on *.* to 'repl'@192.168.100.20 identified by '000000';           
  30. Query OK, 0 rows affected (0.00 sec)
  31. # 刷新权限
  32. mysql> flush privileges;
  33. Query OK, 0 rows affected (0.00 sec)
  34. # 查看server节点状态
  35. mysql> show master status;
  36. +------------+----------+--------------+------------------+-------------------+
  37. | File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  38. +------------+----------+--------------+------------------+-------------------+
  39. | zzy.000001 |      410 |              |                  |                   |
  40. +------------+----------+--------------+------------------+-------------------+
  41. 1 row in set (0.00 sec)

从节点配置

  1. # 从节点修改配置文件，确保有server_id 这个字段，
  2. # 字段id不要一样，一般以ip最后一位结尾
  3. [root@client ~]# vim /etc/my.cnf
  4. server_id = 42
  5. [root@server ~]# mysql -uroot -p000000
  6. Warning: Using a password on the command line interface can be insecure.
  7. Welcome to the MySQL monitor.  Commands end with ; or \g.
  8. Your MySQL connection id is 1
  9. Server version: 5.6.47 MySQL Community Server (GPL)
  11. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  13. Oracle is a registered trademark of Oracle Corporation and/or its
  14. affiliates. Other names may be trademarks of their respective
  15. owners.
  17. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  19. mysql> stop slave;
  20. Query OK, 0 rows affected, 1 warning (0.00 sec)
  22. mysql> change master to master_host='192.168.100.10',master_user='repl',master_password='000000',master_log_file='zzy.000001',master_log_pos=410;
  23. Query OK, 0 rows affected, 2 warnings (0.01 sec)
  25. mysql> start slave;
  26. Query OK, 0 rows affected (0.00 sec)
  28. mysql> show slave status\G;
  29. *************************** 1. row ***************************
  30.                Slave_IO_State: Waiting for master to send event
  31.                   Master_Host: 192.168.100.10
  32.                   Master_User: repl
  33.                   Master_Port: 3306
  34.                 Connect_Retry: 60
  35.               Master_Log_File: zzy.000001
  36.           Read_Master_Log_Pos: 580
  37.                Relay_Log_File: server-relay-bin.000007
  38.                 Relay_Log_Pos: 277
  39.         Relay_Master_Log_File: zzy.000001
  40.              Slave_IO_Running: Yes
  41.             Slave_SQL_Running: Yes
  42.               Replicate_Do_DB: 
  43.           Replicate_Ignore_DB: 
  44.            Replicate_Do_Table: 
  45.        Replicate_Ignore_Table: 
  46.       Replicate_Wild_Do_Table: 
  47.   Replicate_Wild_Ignore_Table: 
  48.                    Last_Errno: 0
  49.                    Last_Error: 
  50.                  Skip_Counter: 0
  51.           Exec_Master_Log_Pos: 580
  52.               Relay_Log_Space: 451
  53.               Until_Condition: None
  54.                Until_Log_File: 
  55.                 Until_Log_Pos: 0
  56.            Master_SSL_Allowed: No
  57.            Master_SSL_CA_File: 
  58.            Master_SSL_CA_Path: 
  59.               Master_SSL_Cert: 
  60.             Master_SSL_Cipher: 
  61.                Master_SSL_Key: 
  62.         Seconds_Behind_Master: 0
  63. Master_SSL_Verify_Server_Cert: No
  64.                 Last_IO_Errno: 0
  65.                 Last_IO_Error: 
  66.                Last_SQL_Errno: 0
  67.                Last_SQL_Error: 
  68.   Replicate_Ignore_Server_Ids: 
  69.              Master_Server_Id: 29
  70.                   Master_UUID: 4f641af6-14f5-11ec-bb85-000c29e43ba6
  71.              Master_Info_File: /data/mysql/master.info
  72.                     SQL_Delay: 0
  73.           SQL_Remaining_Delay: NULL
  74.       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
  75.            Master_Retry_Count: 86400
  76.                   Master_Bind: 
  77.       Last_IO_Error_Timestamp: 
  78.      Last_SQL_Error_Timestamp: 
  79.                Master_SSL_Crl: 
  80.            Master_SSL_Crlpath: 
  81.            Retrieved_Gtid_Set: 
  82.             Executed_Gtid_Set: 
  83.                 Auto_Position: 0
  84. 1 row in set (0.00 sec)
  86. ERROR: 
  87. No query specified

测试

mysql> create database zzy;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;      
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| zzy                |
+--------------------+
5 rows in set (0.01 sec)

小错

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;  these UUIDs must be different for replication to work.

这个错误提示。即主从架构中使用了相同的UUID。检查server_id系统变量，已经是不同的设置，那原因是？
1、错误消息

mysql> show slave status\G;
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.


2、查看主从的server_id变量

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 29    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 20    |
+---------------+-------+
1 row in set (0.00 sec)

从上面的情形可知，主从mysql已经使用了不同的server_id
3、解决故障
查看auto.cnf文件

#主
[root@server ~]# cat /data/mysql/auto.cnf 
[auto]
server-uuid=4f641af6-14f5-11ec-bb85-000c29e43ba6
#从
[root@server ~]# cat /data/mysql/auto.cnf
[auto]
server-uuid=4f641af6-14f5-11ec-bb85-000c29e43ba6

uuid出现了重复，原因是克隆了虚拟机，只改server_id不行

[root@server ~]# mv /data/mysql/auto.cnf /data/mysql/auto.cnf.bk  //重命名该文件
#重启后自动生成新的auto.cnf文件，即新的UUID
[root@server ~]# service mysql restart
[root@server ~]# more /data/mysql/auto.cnf 
[auto]
server-uuid=0fc81527-1502-11ec-bbd8-000c2924f51a


再次查看slave的状态已经正常