mysql主从复制

mysql主从复制

复制是指将主数据库的DDL,DML操作通过二进制日志传到复制服务器（从库）上，然后从库对这些日志进行重新执行，保持从库和主库同步。

一台主库可以同时向多台从库进行复制，从库同时也可以作为其他服务器的主库，实现链状的复制。

主从优点：备援、分压、从库执行备份。

对于实时性要求比较高的数据建议还是从主库查询。

概述

原理

主库：管理bin log。分配账号
从库：管理relay log，连接主库。
数据从bin log 到 relay log ，然后sql进程将relay log操作更新到从库中去，实现主从同步。

复制中的各类文件

bin-log二进制日志文件：
        二进制日志文件会把mysql中的所有数据修改以二进制的形式记录日志文件中。
        show variables like "%binlog_format"查看binlog格式。
    relay-log中继日志文件：
        和bin-log差不多，从库上的SQL线程在执行完当前中继relay-log事件后，会自动删除当前的ralay log避免占用太多磁盘。
        同时为了保证从库crash重启后，从库知道从哪里开始复制，会默认创建两个日志文件master.info和relay-log.info
        master.info记录读取主库二进制日志binlog的进度。
        relay-log.info SQL线程应用中继日志relay log的进度。
        show slave status\G;查看从库复制状态。

查看master.info和relay-log.info

[root@localhost data]# ls
auto.cnf localhost-relay-bin.000002  master.info     merchant         mybinlog.index   relay-log.info
[root@localhost data]# pwd
/usr/local/mysql/data

查看从库的slave状态

mysql>show slave status \g;
                mster_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mybinlog.000001
          Read_Master_Log_Pos: 286252802
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 286252683
        Relay_Master_Log_File: mybinlog.000001
             Slave_IO_Running: Yes
            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: 286252802
              Relay_Log_Space: 286252860
              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: 1
                  Master_UUID: 4a7c7e5c-0486-11e7-b5b1-000c29a85f36
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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

查看从库线程

I/O线程和SQL线程，I/O线程等待主库上的binlog dump线程发送事件并更新到中继日志relay log，sql线程读取中继日志relay log并应用变更到数据库中。

mysql>show processlist \G;
*************************** 1. row ***************************
     Id: 3
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 3050
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 4
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 0
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 3. row ***************************
     Id: 13
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist

查看主库线程

查看到主库的线程。

mysql>show processlist;
*************************** 1. row ***************************
     Id: 7
   User: repl
   Host: 192.168.0.112:55188
     db: NULL
Command: Binlog Dump
   Time: 3095
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row ***************************

三种复制方式

查看当前binlog复制方式show variables like “%binlog_format”;

mysql> show variables like "%binlog_format";

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

statement复制方式

基于sql语句级别的binlog复制方式，每条修改数据SQL都保存到binlog里。

row复制方式

基于行级别的复制方式，将每行的数据变化都记录在binlog里面，记录非常详细，不记录原始sql。
在复制的湿乎乎不会因为存储过程或者触发器造成主从不一致问题，但是记录日志量较大。

mixed混合模式statement,row模式

默认情况使用statement模式，某些情况会切换到row模式。

应用场景：

update age=age+1 where id=3;语句长而磁盘变化少，适合用row
update salary=salary+100;语句短，影响行数比较多，磁盘变化大，适合用statement.
一般使用mixed让系统去决定使用什么模式来复制。

设置binlog_format

set global binlog_format='mixed';

配置主从

异步复制搭建

每个mysql服务器都是需要设置一个server-id用于标识。

1、确保相同数据库版本。

2、修改主库配置文件：vi /etc/my.cnf

#binlog
        #binlog存放格式mixed statement row
        binlog_format = mixed
        #设置一个服务器独特的id
        server-id = 1
        #声明二进制日志文件为mybinlog
        log-bin = mybinlog
        #og-bin = /usr/local/mysql/mybinlog
        #binlog_cache缓存
        binlog_cache_size = 4M
        #最大允许binlog_size
        max_binlog_size = 1G
        #最大缓存binlog大小
        max_binlog_cache_size = 2G
        sync_binlog = 1
        expire_logs_days = 10

查看binlog日志，删除原来日志

[root@localhost data]# ls
auto.cnf  ib_logfile0  localhost.localdomain.err  mybinlog.000001  mybinlog.index  performance_schema
ibdata1   ib_logfile1  localhost.localdomain.pid  mybinlog.000002  mysql           test
[root@localhost data]# pwd
/usr/local/mysql/data
[root@localhost data]# rm -rf mybinlog*

主库上设置一个复制使用的账户，并授予replication slave权限

库上设置一个复制使用的账户，方便从库连接
mysql>
grant replication slave on *.* to 'repl'@'192.168.0.112' identified by 'test';
刷新权限
mysql> flush privileges;
Query OK, 0 rows affected

指定sock文件连接,
#mysql -uroot -p -S /var/lib/mysql/mysql.sock
建立软连接。
ln -s /var/lib/mysql/mysql.sock /tmp/mysql/mysql.sock

查看主库状态

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mybinlog.000001 |      322 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set
mysql> show slave status;
Empty set

3、配置从服务器
配置binlog二进制日志，一般建议打开。数据库down掉的时候可以进行恢复。
配置relaylog

#relay log
        #启动从数据库，这样不会立即启动从数据库服务上的复制进程
        skip_slave_start = 1
        max_relay_log_size = 1G
        relay_log_purge = 1
        relay_log_recovery = 1
        log_slave_updates

4、备份主库上的数据库数据到从数据库上的数据库上。

5、配置从库上的指定主库，实现连接复制。

mysql> change master to
    -> master_host='192.168.0.110',
    -> master_user='repl',
    -> master_password='test',
    -> master_log_file='mybinlog.000001',
    -> master_log_pos=401;
Query OK, 0 rows affected

查看从库数据库

show slave status;

6、在从库上启动slave线程，开始复制同步。

mysql> start slave;
Query OK, 0 rows affected

复制常用命令

show slave status;查看从库状态
show master status 查看主库状态
show processlist;查看线程

复制的3种常见的架构

一主多从复制架构

在主库读取请求压力非常大的场景下，可以通过配置一主多从复制架构实现读写分离。
把大量对实时性要求不是特别高的读请求通过负载均衡分布到多个从库上，降低主库的读压力。

多级复制架构

考虑到mysql复制是主库“推送“binlog日志到从库，主库的I/O压力和网络压力会随着从库的增加而增长。
每个从库都会在主库上有一个独立的binlog dump线程来发送事件。
使用多级复制架构解决主库的额外I/O和网络压力。

缺点：mysql复制是异步复制，多级复制的话经历两次复制才到达从库，延迟比较大。
解决方案：二级主库选择引擎为blackhole来降低多级复制的延迟。黑洞引擎。
        写入blackhole数据并不会写回磁盘，blackhole表永远是一个空表，所有的DML,DCL操作仅仅在binlog记录。

双主复制/dual master架构

双主复制架构比较适合DBA做维护等需要主从切换场景使用。

常见错误排查

查看日志

[root@localhost logs]# pwd
/usr/local/mysql/logs
[root@localhost logs]# tail error.log

查看报错原因：
mysql 5.6的复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，但是查看到直接copy data文件夹后server_uuid是相同的，show variables like ‘%server_uuid%’;

mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 4a7c7e5c-0486-11e7-b5b1-000c29a85f36 |
+---------------+--------------------------------------+
1 row in set

解决方法：
找到data文件夹下的auto.cnf文件，修改里面的uuid值，保证各个db的uuid不一样，重启db即可

[root@localhost data]# vi auto.cnf 
[root@localhost data]# pwd
/usr/local/mysql/data

日常管理维护

查看从库状态show slave status\G;
slave_io_running:是否yes，slave_sql_running是否yes。