MySQL数据库的主从复制

一、 mysql主从复制介绍

mysql主从复制有利于数据库架构的健壮性、提升访问速度和易于维护管理。
MySQL支持单向、双向、链式级联、实时、异步复制。在复制过程中，一台服务器充当主服务器（Master），而一个或多个其他的服务器充当从服务器（slave）。
复制可以是单向：M==>S,也可以是双向M<==>M,也可以是多M环状同步等。
如果设置了链式级联复制，那么从（slave）服务器本身除了充当从服务器外，也会同时充当下面其他从服务器的主服务器。链式级联复制类似A->B->C->D的复制形式。
各种形式如图：
1.单向主从复制

2.双向主从同步

3.线性级联单向双主从同步

4.环状级联单向多主同步

5.环状级联单向多主多从同步

最适合选的组合如下：

大多数应用的MySQL主从同步都是异步的复制方式，即不是严格的实时数据同步。
当配置好主从复制后，对所有的数据库内容的更新就必须在主服务器上进行，以避免用户对主服务器上的数据库内容的更新与对从服务器上的数据库内容的更新不一致而导致发生冲突。

二、防止写从服务器的方法

有两种方法：
一是：
采取忽略授权标的方式同步，然后对从服务器（slave）上的用户仅授权select读权限。保证主库和丛库相同的用户可以授权不同的权限
二是：
在从服务器（salve）上启动选项增加参数或者在my.cnf配置文件中添加read-only参数来确保丛库只读。read-only参数可以让salve服务器只允许来自salve服务器线程或具有SUPER权限的用户更新。

三、MySQL主从复制的应用场景以及原理

• 主从服务器互为备份

主从服务器架构的设置，可以大大的加强数据库架构的健壮性。例如：当主服务器出现问题时，我们可以人工或自动切换到从服务器继续提供服务

• 主从服务器读写分离分担网站压力

主从服务器架构可通过程序（php，java）或代理软件（mysql-proxy，amoeba）对客户端的请求实现读写分离。即通过在从服务器上仅仅处理用户的select查询请求

• MySQL数据库主从复制原理介绍
  mysql主从复制是异步的复制，复制过程如下：
  1.salve服务器上执行start slave，开启主从复制开关
  2.此时，salve服务器的io线程会通过在master上授权的复制用户权限请求连接master服务器，并请求从指定binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令时指定的）之后发送binlog日志内容
  3.master服务器接收到来自salve服务器的IO线程的请求后，master服务器上负责复制的IO线程根据salve服务器的IO线程请求的信息读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给salve端的IO线程。返回的信息中除了binlog日志内容外，还有本次返回日志内容在master服务器端的新的binlog文件名称以及在binlog中的下一个指定更新位置。
  4.当salve服务器的IO线程获取来自master服务器上的IO线程发送日志内容以及日志文件以及位置点后，将binlog日志内容依次写入到salve端自身的relay log（中继日志）文件（MySQL-relay-bin.XXXXXX）中的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志能够告诉master服务器需要从新binlog日志的那个文件哪个位置开始请求新的binlog日志内容。
  5.salve服务器端的SQL线程会实时的检测本地relay log 中新增加的日志内容，然后及时把log文件中的内容解析成在master端曾经执行的SQL语句的内容，并在自身salve服务器上按照语句的顺序执行应用这些SQL语句，应用完毕后清理用过的日志
  6.经过了上面的过程，就可以确保master端和salve端执行了同样的SQL语句。
  如下图：
  
  丛库原理:
  如果丛库后面还有丛库应该在丛库上开启binlog。其他操作一样
  

四、mysql数据库主库宕机丛库不丢数据方案

4.1其中一个丛库只同步主库的更改操作，如果主库宕机则这个丛库自动切换为主库继续提供服务。并连接主库把宕机时来不及记录的binlog日志复制过来然后进行处理
4.2两个主库采用双写操作
4.3另外写一个程序记录主库宕机一分钟内的binglog。

五、MySQL数据库读写分离介绍

主从服务器架构对客户端的请求实现读写分离，即通过在从服务器上仅仅处理用户的select查询请求，但是对数据的其他操作（update，insert，delete）仍然交给主服务器，以确保主服务器和从服务器保持实时同步。
如果网站是以非更新为主的业务如blog，www服务等查询比较多时，这时读写分离就很有效了。
读写分离实现方法：
中大型公司：通过程序（php，java）
测试环境：代理软件（mysql-proxy，amoeba）
门户网站：分布式dbproxy
下图是一个读写分离的例子：

mysql主从读写分离实现方法
1.通过程序实现读写分离（性能，效率最佳，推荐）
php和java程序都可以通过设置多个连接文件轻松的时实现对数据库的读写分离，即当select时，就去连接读库的连接文件，当update，insert，delete时就连接写库的连接文件
2.通过软件实现读写分离
mysql-proxy，amoeba等代理软件也可以实现读写分离功能，但是最好还是用程序实现读写分离。
3.开发dbproxy

6、配置主从复制

可以开启两台mysql服务器，也可以在一个服务器上开启两个实例来模拟。这里采用多实例来演示。

• 1.复制环境准备
  定义服务器角色
  主库（MySQL master）：ip为192.168.31.217 port为3307
  丛库1（MySQL salve）：ip为192.168.31.217 port为3308
  丛库2（MySQL salve）：ip为192.168.31.217 port为3309

• 2.主库上的操作

  设置server-id的值并开启binlog参数，因为复制的关键就是在master上开启binlog
  vim /data/3307/my.cnf更改server-id和开启
  log-bin=/data/3309/mysql-bin。这两个参数放在[mysqld]模块下。

  egrep "server-id|log-bin" /data/3307/my.cnf 
  log-bin=/data/3307/mysql-bin
  server-id = 3
  

  查看log-bin是否生效的方法：

  mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show variables like 'log_bin'"  #查看参数是否开启
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | log_bin       | ON    |
  +---------------+-------+
  mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show master logs" #第二种查看是否有log文件
  ll /data/3307/  #第三种查看是否有log文件
  

暂时停止二进制日志功能方法
set sql_log_bin=0; 关闭
set sql_log_bin=0; 开启二进制日志功能

在主库(master)上建立用于同步的账号rep
登录主库：

    mysql -uroot -p123456 -S /data/3307/mysql.sock 
 授权：
 
    grant replication slave on *.* to 'rep'@'192.168.31.%' identified by '123456';
    #replication slave 为MySQL同步必须的权限，此处不要授权all
    #*.*表示所有的库，也可以指定具体的库进行复制
    #'rep'@'192.168.31.%'   rep为同步账号，192.168.31.%授权主机网段。
     flush privileges;  #刷新


**在主库(master)上备份以前的数据**
    需要备份以前的数据是因为在丛库备份时主库上已近有一部分数据，丛库只能备份当前开始以及以后的数据，丛库开始备份以前的数据需要手动备份下来，然后发给主库以保证主从数据一致。
    第一步：主库锁表，成为只读状态
    
     flush table with read lock; #让主库只读
      flush tables with read lock；（5.1锁表命令）
对于锁表如果超过设定时间不操作则锁表自动失效，就是interactive_timeout和lock_wait_timeout设置的值：

查看：

    mysql> show variables like '%timeout%';
     | interactive_timeout        | 28800    |
     | lock_wait_timeout          | 31536000 |
  第二步：开始备份主   
    
        mysql> show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000008 |      334 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec) #此时位置为334
       mysql> show binary logs;  二进制文件大小和个数
   +------------------+-----------+
   | Log_name         | File_size |
   +------------------+-----------+
   | mysql-bin.000001 |       907 |
   +------------------+-----------+
   1 row in set (0.00 sec)

   mysql> show master logs;
   +------------------+-----------+
   | Log_name         | File_size |
   +------------------+-----------+
   | mysql-bin.000001 |       907 |
   +------------------+-----------+
   1 row in set (0.00 sec

查看错误日志位置：
mysql> show variables like ‘log_error’;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 1
Current database: hr

±--------------±----------------------------+
| Variable_name | Value |
±--------------±----------------------------+
| log_error | ./localhost.localdomain.err | 在databases目录下
±--------------±----------------------------+
1 row in set (0.00 sec)
可在配合文件my.cnf重配置log_error位置
log-error=path
备份导出数据：

   mysqldump -uroot -p123456 -S /data/3307/mysql.sock -A -B --events --master-data=2 >/opt/rep.sql  
   #--master-data=2可以记录当前备份到哪个位置点
   解锁：
   unlock tables；

还有一种备份主库数据方法，直接一条命令即可；

   mysqldump -uroot -p123456 -S /data/3307/mysql.sock -A -B -F --events --master-data=2 -x >/opt/rep.sql  
   #加上-x参数则在备份时锁表，确保数据一致，且用-F刷新日志文件

• 3.丛库上的操作
  在salve上log-bin可开也可以不开，server-id要和主库不一样，丛库3308配置如下：

    egrep "server-id|log-bin" /data/3308/my.cnf 
    #log-bin=/data/3308/mysql-bin
    server-id = 4
  

  备份主从同步开始之前的数据：

   mysql -uroot -p123456 -S /data/3308/mysql.sock </opt/rep.sql 
   #将主库3307备份数据恢复到丛库3308
  

  在丛库上配置CHANGE MASTER

    mysql -uroot -p123456 -S /data/3308/mysql.sock#登录丛库
  
  mysql> CHANGE MASTER TO
  MASTER_HOST='192.168.31.217',
  MASTER_PORT=3307,
  MASTER_USER='rep',#主库上设置的用户丛库备份的用户
  MASTER_PASSWORD='123456',#用户密码
  MASTER_LOG_FILE='mysql-bin.000008',
  MASTER_LOG_POS=334; #设置备份开始点，就是手动备份结束点
  

  说明：如果备份使用如下命令
  mysqldump -uroot -p123456 -S /data/3307/mysql.sock -A -B -F --events --master-data=1 -x >/opt/rep.sql，则在在丛库上配置CHANGE MASTER时不需要设置 MASTER_LOG_FILE 和 MASTER_LOG_POS两个参数，因为 --master-data=1 这个选项在备份时已近记录了点位。

  备份开始点pos放在丛库master-info下，查看一下：

  #cat /data/3308/data/master.info 
    18
    mysql-bin.000008  #binlog文件名
   334  #pos点位置，和我们设置的一样
   192.168.31.217
   rep
   123456
   3307
   60
    0
  

  对于pos位置，丛库每次复制之后都会更新本次复制对应的post，以保证下次知道从主库那个地方开始复制。
  在丛库3308上开启同步：

   start slave;
  

  查看一下是否生效：

   mysql> show slave status\G;
   *************************** 1. row ***************************
          Slave_IO_State: Waiting for master to send event
             Master_Host: 192.168.31.217
             Master_User: rep
             Master_Port: 3307
           Connect_Retry: 60
         Master_Log_File: mysql-bin.000008
     Read_Master_Log_Pos: 775
          Relay_Log_File: mysql-relay-bin.000002
           Relay_Log_Pos: 694
   Relay_Master_Log_File: mysql-bin.000008
        Slave_IO_Running: Yes   #io线程运行
       Slave_SQL_Running: Yes   #sql线程运行，
  

  io和sql线程都是yes说明正却开启。

  cat /data/3308/data/relay-log.info#sql线程确定点位
  cat /data/3308/data/master.info  #io线程确定点位，与relay-log.info值一致
  

  总结：
  快速配置MySQL主从复制方案
  1.安装好要配置的丛库的数据库，配置好log-bin和server-id参数
  2.无需配置主库my.cnf文件，主库的log-bin和server-id参数默认已经配置好
  3.登录主库增加用于丛库同步的账户，例如user1，并授权replication slave同步的权限
  4.使用mysqldump带–master-data=1备份的全备数据恢复到丛库
  5.在丛库执行changge master to 。。。语句，无需binlog文件以及对应位置
  6.丛库开启同步开关，start slave。
  7.丛库show slave status\G,检查同步状态，并在主库进行更新测试

  七、mysql主从复制故障总结：

• show master status没结果
  原因是主库binlog功能开关没开或者没生效

  grep -E "log-bin|server-id" /data/3307/my.cnf 
  log-bin=/data/3307/mysql-bin
  server-id = 3
  

• mysql没有正确关闭，导致启动不了。
  解决办法就是删除mysql.pid和mysql.sock，重启

• MySQL丛库数据冲突导致同步停止

  show slave status；报错且sql线程停止运行。错误代码位1007.解决办法：
  方法一：

   stop slave；
   set global sql_slave_skip_counter=1；指针向下走一个
  start slave；
  

  这种方法对于普通互联网业务影响不大
  方法二：
  在my.cnf中加上slave-skip-errors =1032,1007参数。

  MySQL错误代码总结：
  1005：创建表失败
  1006：创建数据失败
  1007：数据库已存在，创建数据库失败
  1008：数据库不存在，删除数据库失败
  1009：不蹦删除数据库文件导致删除数据库失败
  1010：不能删除数据目录导致删除数据失败
  1011：删除数据文件失败
  1012：不能读取系统表中的记录
  1020：记录已被其他用户修改
  1021：硬盘空间不足，请加大硬盘可用空间
  1022：关键字重复，更改记录失败
  1023：关闭文件时发生错误
  1024：读文件错误
  1025：更改名字时发生错误
  1026：写文件错误
  1032：记录不存在

  八，MySQL主从复制读写分离授权方案

  当配置好MySQL主从复制后，对所有数据库内容的更新就必须在主服务器上进行。因为数据是单向的，只有在主库上更新，再能让主从服务器上的内容一致。

  方法一：
  主库新建一个名为web的用户，设置select，insert，delete，update权限，在主库的web用户同步到丛库后回收insert，delete，update权限。具体如下
  主库：web用户 123密码 192.168.0.1IP地址 3306端口 （select，insert，delete，update）权限。
  丛库：主库的web用户同步到丛库，然后回收insert，delete，update权限。也可以不收回权限在my.cnf的【mysqld】下设置read-only
  方法二：
  主库和丛库分别建立不同的用户，然后在丛库只授权读的权限。这种方式存在的风险是如果以主库web_w的身份连接丛库则可以在丛库写（主库的web用户会同步到丛库）。解决办法是在my.cnf配置文件的【mysqld】下设置read-only。
  对于read-only，在5.5.X中root以及具有all权限的用户就算设置了read-only也一样可以修改，插入数据。但是具备insert，update，delete等权限的用户无法通过read-only的限制，也只能读。
  主库：web_w用户 123密码 192.168.0.1IP地址 3306端口 （select，insert，delete，update）权限。
  丛库：web_r 123 192.168.0.2 3306
  方法三：
  通过忽略授权表的方式防止数据写入丛库。然后对从服务器上的用户授权select权限。不让同步MySQL库，这样就保证主库和丛库相同的用户可以授权不同的权限(web用户在mysql.user表里，不同步mysql库丛库就不会同步web用户)。
  主库：web用户 123密码 192.168.0.1IP地址 3306端口 （select，insert，delete，update）权限。
  丛库：web用户 123密码 192.168.0.1IP地址 3306端口 （select）权限。
  例如：忽略mysql库和information_schema库的主从同步

   replicate-ignore-db=mysql
   binlog-do-db=test   #只同步test库
  
   binlog-ignore-db=mysql    #忽略记录mysql库的binlog数据，不同步mysql库
   binlog-ignore-db=performance_schema
   binlog-ignore-db=information_schema
  

  例如：这里给10.0.0.0/24的用户blog管理blog数据库的所有表只读权限，密码为123456
  主丛库授权

  grant select,insert,update,delete on 'blog'.* to 'blog'@'10.0.0%' identified by '123456'
  

  丛库：

  grant select on 'blog'.* to 'blog'@'10.0.0%' identified by '123456'
  

  然后在主库上配置binlog-ignore-db=mysql

九，master故障让其中一个slave充当主库方法

第一步：
查看master.info看看谁的pos点最大，选谁当主库

cat /data/3308/data/master.info 

第二步：
在丛库上执行

stop slave io_thread;show processlist;

直到看到has read all relay log；表示丛库更新执行完毕
第三部：
登录丛库

mysql -uroot -p123456 -S /data/3308/mysql.sock
stop slave
retset master  #设置为主库
quit

第四步：
进入到数据库数据目录，

删除 master.info 和 relay-log.info
  cd /data/3308/data
rm -f master.info relay-log.info

第五步：
将3308提升为主库

vi /data/3308/my.cnf

开启：

log-bin=/data/3308/mysql-bin

注释掉log-slave-updates,read-only然后重启3308

十、丛库记录binlog方法

丛库需要记录binlog情形：
1.当前丛库还要作为其他丛库的主库，也就是级联同步
2.把丛库作为数据库备份服务器时需要开启binlog
开启方法：

vim /data/3308/my.cnf

在服务端加上如下参数：

 log-bin=/data/3308/mysql-bin
 log-slave-updates   #以上两个放在一块
 expire_logs_days=7  #只保留7天，超过7天的日志删掉
 相当于find /data/3308/ -type f -name 'mysql-bin.00*' -mtime +7|xargs rm -f

十一、全量备份和增量备份

1. 全量备份和增量备份介绍
   全量备份就是备份数据库中的所有数据。
   例如：
   mysqldump -uroot -p123456 -S /data/3307/mysql.sock -F -B data_default|gzip >/opt/mysqlbak_$(date +%F).sql.gz

增量备份就是从上次全量备份之后，更新的数据。binlog就是一个增量备份
增量备份时要通过防火墙禁止web等应用向主库写数据。
然后刷新binlog，从上一个binlog开始恢复

mysqladmin -uroot -p123456 -S /data/3307/mysql.sock flush-logs

因为在用恢复时会把恢复语句写进去，所以可以关闭 sql_log_bin，状态改为off，就不记录这条语句，恢复后改回on

 mysql> show variables like '%log_bin%';
 +---------------------------------+-------+
 | Variable_name                   | Value |
 +---------------------------------+-------+
 | log_bin                         | OFF   |
 | log_bin_trust_function_creators | OFF   |
 | sql_log_bin                     | OFF   |
 +---------------------------------+-------+