Mysql主从备份

主从复制原理

主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

---

测试运行环境

• Ubuntu 16.04 Gnome
• MySQL 5.7.16
• 主服务器(Master)IP:192.168.1.10
• 从服务器(Slave)IP:192.168.1.213
• 搭建时间：2016-12-12

---

配置主服务器

设置ID

在Master, 必须启用二进制日志, 并且设置唯一ID(Master和 Slave都必须设置唯一id标识, 一般为内网地址结尾的数字), 并且应拥有管理员权限以操作my.cnf(不同系统对应文件不知道相同不相同, 如果不同,自行百度)。

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
log-bin=mysql-bin
server-id=10

Note
下列选项对这个过程产生影响：

• 如果省略server-id（或者明确地将其设置为0的默认值），Master拒绝任何Slave的连接。
• 如果是InnoDB引擎，为了保证高可用性和一致性，建议在配置文件中增加 innodb_flush_log_at_trx_commit=1与 sync_binlog=1。
• 确保 skip-networking选项没有在Master启用。如果网络已被禁用，Slave无法与主沟通，复制失败。

重启Master

sudo service mysql restart

获取Master二进制日志文件名, 获取Master的状态

mysql> SHOW MASTER STATUS;

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
master-bin.000003	154

Master创建允许Slave登录的用户:1

mysql> GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.1.213' IDENTIFIED BY 'asdasd' WITH GRANT OPTION;

配置Slave

在开始之前，确保配置如下选项：

1. 已经配置Master必要的配置属性。
2. 获得Master的状态信息。
3. 在Master 发布了读锁
   

   mysql> UNLOCK TABLES;

设置主从复制的Slave配置

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

• Slave不需要设置log-bin, 除非此Slave要给其他机器当Master。

[mysqld]
server-id=213

• 进行更改后，重启Slave的mysql服务

sudo service mysql restart

如果要设置多个Slave，每一个都必须有一个独特的server-id。

设置Slave对应的master信息:

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.1.10',
    ->     MASTER_USER='repl',
    ->     MASTER_PASSWORD='asdasd',
    ->     MASTER_LOG_FILE='master-bin.000003',
    ->     MASTER_LOG_POS=4;

开启Slave监控

mysql> START SLAVE;

查看slave状态

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 1079213
               Relay_Log_File: slave-relay-bin.000008
                Relay_Log_Pos: 1077792
        Relay_Master_Log_File: master-bin.000003
             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: 1079213
              Relay_Log_Space: 1078166
              Until_Condition: None
                Until_Log_Pos: 0
        Seconds_Behind_Master: 0
                Last_IO_Errno: 0
               Last_SQL_Errno: 0 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 4efe36c0-a55e-11e6-a9ef-000c29fb1374
             Master_Info_File: /var/lib/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:  
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
(由于列数过多,博主只截取部分列)

查看slave的状态时，Slave_IO_Running，和Slave_SQL_Running两项必须为Yes, 其中一项为no, 整个复制都不能正常进行,

Slave_SQL_Running：负责自己的slave mysql进程
Slave_IO_Running：负责与主机的io通信
如这两项有问题，先查看mysql的error_log,在 /var/log/mysql/ 目录下,然后自行百度解决或者留言，以下为网上总结的常见问题:
出现Slave_IO_Running:No问题的解决方法小结
主从同步常见故障

开启Slave后, 会监控Master的所有变化, 但是之前Master已存在的数据库，Slave不会复制，所以，如果想复制Master之前已存在的数据库，需要用mysqldump工具将需要复制的数据库导入Slave中：

mysqldump --all-databases --master-data > dbdump.db

如果你想选择某个数据库而不是 –all-databases。选择下列选项之一:

• 排除所有在数据库中使用的表 –ignore-table选项。
• 指定某个数据库 –database_name

---

1. *.* 表示所有数据库的所有表, “repl”->Salve用来登录的用户名, “asdasd” -> 登录所需的密码 ↩