MySQL主从复制

1. MySQL单节点部署（两台服务器）

MySQL5.7部署

2. 主从架构 (读写分离) 及部署

• spark000 master 主 写

  1.修改/etc/my.cnf 配置文件
  [root@spark000 ~]# vim /etc/my.cnf
  [mysqld]
  server_id = 1
  expire_logs_days = 7
  binlog_format = ROW
  log-bin = /usr/local/mysql/arch/mysql-bin
  
  2.主库上创建一个复制用户
  [root@spark000 ~]# su - mysqladmin
  [mysqladmin@spark000 ~]$ mysql -uroot -p
  Enter password:
  mysql> show master status;
  +------------------+----------+--------------+------------------+-------------------+
  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  +------------------+----------+--------------+------------------+-------------------+
  | mysql-bin.000012 |      120 |              |                  |                   |
  +------------------+----------+--------------+------------------+-------------------+
  

• spark001 slave 从 读

  1.修改/etc/my.cnf 配置文件
  [root@spark001 ~]# vim /etc/my.cnf
  [mysqld]
  server_id = 2
  
  2.从库执行
  [root@spark001 ~]# su - mysqladmin
  [mysqladmin@spark001 ~]$ mysql -uroot -p
  mysql> change master to
      -> master_host='192.168.79.xxx',
      -> master_port=3306,
      -> master_user='repluser',
      -> master_password='123456',
      -> master_log_file='mysql-bin.000012',
      -> master_log_pos=120;
  Query OK, 0 rows affected, 2 warnings (0.02 sec)
  
  mysql> start slave;
  mysql> show slave status\G
  *************************** 1. row ***************************
           Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.79.136
              Master_User: repluser
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000012
      Read_Master_Log_Pos: 120
           Relay_Log_File: hostname-relay-bin.000002
            Relay_Log_Pos: 283
    Relay_Master_Log_File: mysql-bin.000012
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
  

说明：

1. 从库的MySQL配置server_id与主库不一致，其余保持一致；
2. 192.168.79.xxx：主库的外网IP地址；
3. 从库执行show slave status\G后，看到
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
   即可

3. 验证同步

1. 在主库上建一个数据库，查看从库是否同步

   # spark000
   mysql> create database wujidata;
   
   # spark001
   mysql> show databases;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | mysql              |
   | performance_schema |
   | test               |
   | wujidata           |
   +--------------------+
   

2. 在主库上建一张表，并插入数据

   # spark000 
   mysql> use wujidata;
   Database changed
   mysql> create table dept(
       -> deptno int , 
       -> dname varchar(14) ,
       -> loc varchar(13)  
       -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   mysql> insert into dept values(10,'ACCOUNTING','NEW YORK');
   mysql> insert into dept values(20,'RESEARCH','DALLAS');
   mysql> insert into dept values(30,'SALES','CHICAGO');
   mysql> insert into dept values(40,'OPERATIONS','BOSTON');
   
   # spark001
   mysql> use wujidata;
   Database changed
   
   mysql> select * from dept;
   +--------+------------+----------+
   | deptno | dname      | loc      |
   +--------+------------+----------+
   |     10 | ACCOUNTING | NEW YORK |
   |     20 | RESEARCH   | DALLAS   |
   |     30 | SALES      | CHICAGO  |
   |     40 | OPERATIONS | BOSTON   |
   +--------+------------+----------+
   4 rows in set (0.00 sec)
   

4. 遇到的问题

分析：

1. 查看log-error日志，发现如下内容
   2020-01-06 10:30:32 10216 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error_code: 1593
2. 从[ERROR]日志中可以看出，master和slave有相同的server ids，在/usr/local/mysql/data/auto.cnf中有配置server ids，其实就是UUID的值。也就是说master和slave的UUID一致。
3. 此时我意识到spark001是从spark000克隆过来的，UUID肯定是一致的。

解决：

1.在spark001重新生成一个UUID
[root@spark001 ~]# uuidgen
7d486472-65f4-4fcf-bd57-96884cf50959

2.将其配置到ifcfg-ens33文件中
[root@spark001 ~]# vim /etc/sysconfig/network-scripts/ifcfg-ens33

3.重启网络生效
[root@spark001 ~]# systemctl restart network

4.配置auto.cnf
[mysqladmin@spark001 ~]$ vim data/auto.cnf
[auto]
server-uuid=7d486472-65f4-4fcf-bd57-96884cf50959

5.重启MySQL
[mysqladmin@spark001 ~]$ service mysql restart

6.在按照上文spark001的步骤操作即可