MySQL主从配置

MySQL主从介绍

• MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的

• MySQL主从是基于binlog的，主上须开启binlog才能进行主从。 主从过程大致有3个步骤
  1）主将更改操作记录到binlog里
  2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
  3）从根据relaylog里面的sql语句按顺序执行

• 主上有一个log dump线程，用来和从的I/O线程传递binlog

• 从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地

MySQL主从原理图

• 使用场景

1. 在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力;（主库写，从库读，降压）
2. 当主服务器出现问题时，可以切换到从服务器。（提升性能）

主从配置

• 准备工作
  两台机器安装好MySQL

• 主机操作

1. 修改my.cnf

[root@draft support-files]# vi /etc/my.cnf #添加以下语句；

server-id=149	#id可以自己设置，跟从机器要不一样；
log_bin=main		#配置binlog的名字，可自定义，配置后在数据目录里，生成main前缀的文件，恢复数据的重要文件；

[root@draft support-files]# ll /data/mysql
总用量 191652
-rw-r-----. 1 mysql mysql  3226709 11月  5 10:22 main.000001
-rw-r-----. 1 mysql mysql     1304 11月  5 13:21 main.000002
-rw-r-----. 1 mysql mysql       28 11月  5 10:22 main.index

service mysqld restart   #重启服务；

2. 使两台机器的数据库内容相同

如在主机器上有bbs数据库，可以把这个数据库用sqldump备份，在从机器上新建bbs数据库，并将备份内容导入

这个实验的逻辑是：
开启binlog之前的数据，使用mysqldump同步；然后再确认主从同步的状态点，状态点之后对主机的操作，都可以利用主从配置同步；这两个操作之间不能对主数据库操作，这两个点之间的操作会丢失；

3. 创建用作数据同步的用户；

grant reload, super, replication slave, replication client on *.* to 'repli'@'192.168.87.150' identified by 'password';
flush tables with read lock;  #关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，直到显示地执行unlock tables，该操作常常用于数据备份的时候；运行后只能读操作，不能写；



mysql> show master status;		#需要记录File和Position信息；开始主从同步的状态点；
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| main.000001 |  3225820 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

• 从机操作

1. 修改my.cnf

[root@second ~]# vi /etc/my.cnf #添加以下语句；

server-id=150	#id可以自己设置，跟从机器要不一样；

service mysqld restart   #重启服务；

2. 使用mysqldump同步基础数据，不具体说明；
3. 配置同步状态点后的数据

 进入从数据库；

stop slave;   #重启后slave会启动（之前已经启动slave的情况下)，必须要stop，再做以下的连接主库的操作，不能做两次slave；

ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.		#不停止slave，做change master操作的错误；

change master to master_host='192.168.87.149', master_user='repl', master_password='password', master_log_file='main.000001', master_log_pos=3225820;
#将数据库设置从主数据库的状态点开始建立同步，需要用户信息，从主数据库获取的File和Position信息用在这里;
start slave;       #启动从机的同步；

start slave说明：
不带任何参数，表示同时启动I/O 线程和SQL线程。
I/O线程从主库读取bin log，并存储到relay log中继日志文件中。
SQL线程读取中继日志，解析后，在从库重放。
stop slave说明：停止I/O 线程和SQL线程。

4. 主机unlock tables;

进入主机数据库；
unlock tables;   #主库可以重新写操作；

5. 查看连接情况
   连接正常的情况如下：
   操作主机 数据库，自动同步到 从机 数据库；

进入从机数据库；

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.87.149
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: main.000002
          Read_Master_Log_Pos: 336
               Relay_Log_File: second-relay-bin.000003
                Relay_Log_Pos: 539
        Relay_Master_Log_File: main.000002
             Slave_IO_Running: Yes			#需要关注，stop slave;命令后变成No;
            Slave_SQL_Running: Yes		#需要关注;
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 336
              Relay_Log_Space: 1774
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0		#需要关注;
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0				#需要关注，这些是错误信息;
                Last_IO_Error: 				#需要关注;
               Last_SQL_Errno: 0			#需要关注;
               Last_SQL_Error: 				#需要关注;
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 149
                  Master_UUID: 996fb237-ff4e-11e9-8cda-000c29bde900
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

几个配置参数

主服务器上 的配置项：
binlog-do-db= #配置后仅同步指定的库;
binlog-ignore-db= #配置后忽略指定库的同步;

从服务器上 的配置项：
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
replicate_wild_do_table= #如aming.%, 支持通配符%; 建议使用wild语句，更精准和灵活；
replicate_wild_ignore_table=

按上面配置后所有表都会同步，包括mysql表，新增用户都会同步，如不需同步，可利用上边的配置设置；

错误

由于是克隆的机器，也会造成同步不成功，错误信息如下：

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.

解决办法是修改auto.cnf;

[root@second ~]# vi /data/mysql/auto.cnf 	#修改UUID的编号，让两台机器的编号不一样；

[auto]
server-uuid=996fb237-ff4e-11e9-8cda-000c29bde903

备注

• 个人觉得，主从配置是动态的，从开始binlog后，所有的操作都会有一个记录，这个可以由show master status;查看到；一个文件写完后，再生成下一个文件记录；假设数据在file1, 333这个点两个数据库的数据是完全同步的，然后在主机上做的操作（从机不做操作），都可以通过这个点同步；
• 但是不能主机上做了操作，状态点变成file1, 444时，然后使用这个点同步 从机（从机 数据还是在file1, 333上），缺失了111个状态，肯定是有问题的；
• 主从之间要确立一个数据完全同步的点，并记录状态点，之后主机上不锁表，从机也是可以同步到跟主机一个状态，从旧的状态一直恢复到最新状态；