Mysql 主从备份完整版

墨蓝 2022-04-10 12:31 214阅读 0赞

MYSQL 主从配置有很多好处，一个是起着实时备份的作用，另外一个也可以读写分离，减轻网站的负载，baidu出来的很多配置其实很有问题，在这里重新梳理一下，首先了解一下原理。

该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。  
    下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。  
SQL slave thread处理该过程的最后一步。SQL线程从中继日志读取事件，更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

一、 **前提条件：**

1. 数据库服务器最起码两台，假设是数据库MYSQL-A, 数据库MYSQL-B，MYSQL-A的IP 10.0.0.2，MYSQL-B的ip是10.0.0.3；

用户： root /root

数据库 virt

2. 建议主从数据库的版本一致，如果不一致很容易出错，或者在mysql的配置文档 my.cnf(window为my.ini) 加上binlog\_checksum =none 参数

3. 删除所有的外键，表与表的依赖关系最容易导致主从复制失败，一般而言，外键在测试环境上测试校验数据的完整性，而在生产环境最好删除外键，删除外键，可以通过mysql数据字典生成删除脚本，如下

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> <p>SELECT CONCAT("alter table ",a.`TABLE_SCHEMA`,".",TABLE_NAME," drop foreign key ",CONSTRAINT_NAME,";")</p> <p>FROM KEY_COLUMN_USAGE a</p> <p>WHERE a.`table_SCHEMA`="virt" AND &nbsp;REFERENCED_TABLE_NAME &nbsp;IS NOT NULL</p> </td> 
  </tr> 
 </tbody> 
</table>

4. 设置主键的不长，通常情况下，数据库的主键是无意义的ID，而且是自增长的，如果数据库进行主主备份，那么互相复制的过程容易产生主键冲突，所有需要设置自增长的步长和奇偶数。

二、 **同步用户**

你用root做数据库同步账号当然也没有问题，但更多是建议建立复制账号，如下

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> <p>CREATE USER 'backup'@'%' IDENTIFIED BY 'backup';</p> <p>GRANT REPLICATION SLAVE ON *.* TO 'backup'@'10.0.0.%' IDENTIFIED BY 'backup';</p> </td> 
  </tr> 
 </tbody> 
</table>

三、 **在同步前先让两台数据库的数据一致**

1. MYSQL-A  下执行SQL命令：flush tables with read lock;目的是锁表

2. MYSQL-B下面执行命令：mysqldump -h10.0.0.2 -uroot -proot virt > var/backup/virtback.sql；备份数据；

3. MYSQL-A下执行SQL命令：unlock tables；解除锁定。

4. MYSQL-B 执行命令：mysql -uroot -proot virt < var/backup/virtback.sql；还原数据

四、 **配置数据库主从备份参数**

MYSQL-A, vi /etc/my.cnf,下面的参数需要处理一下

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> <p><strong>server-id = 1</strong></p> <p><strong>log-bin=/var/lib/mysql/mysql-bin.log</strong></p> <p><strong>binlog_format=mixed</strong></p> <p><strong>binlog-do-db=virt</strong></p> <p><strong>binlog-ignore-db=mysql &nbsp;</strong></p> <p><strong>binlog-ignore-db=information_schema &nbsp;</strong></p> <p><strong>binlog-ignore-db=performance_schema &nbsp;</strong></p> <p><strong>innodb_flush_log_at_trx_commit=1</strong></p> <p><strong>sync_binlog=1 &nbsp;</strong></p> <p><strong>expire_logs_days = 5</strong></p> <p><strong>auto_increment_increment=2</strong></p> <p><strong>auto_increment_offset=1</strong></p> </td> 
  </tr> 
 </tbody> 
</table>

** **

MYSQL-B, vi /etc/my.cnf,下面的参数需要处理一下

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> <p><strong>server-id = 2</strong></p> <p><strong>log-bin=/var/lib/mysql/mysql-bin.log</strong></p> <p><strong>binlog_format=mixed</strong></p> <p><strong>binlog-do-db=virt</strong></p> <p><strong>binlog-ignore-db=mysql &nbsp;</strong></p> <p><strong>binlog-ignore-db=information_schema &nbsp;</strong></p> <p><strong>binlog-ignore-db=performance_schema &nbsp;</strong></p> <p><strong>innodb_flush_log_at_trx_commit=1</strong></p> <p><strong>sync_binlog=1 &nbsp;</strong></p> <p><strong>expire_logs_days = 5</strong></p> <p><strong>auto_increment_increment=2</strong></p> <p><strong>auto_increment_offset=2</strong></p> </td> 
  </tr> 
 </tbody> 
</table>

** **

五、 **重启MYSQL服务**

MYSQL-A，MYSQL-B数据库服务器重启

命令：service mysql restart

你也可以/etc/init.d/mysqld restart

如果以前有主从备份或者日志，也可以执行SQL命令清除日志

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> <p>PURGE MASTER LOGS BEFORE '2017-02-21 00:00:00';</p> <p>RESET MASTER;</p> </td> 
  </tr> 
 </tbody> 
</table>

** **

六、 **从MYSQL-A到MYSQL-B进行复制过程**

1. 检查MYSQL-A 的日志情况，在**MYSQL-A**执行SQL命令

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> <p>show master status</p> </td> 
  </tr> 
 </tbody> 
</table>

File 和 Position在复制过程中要用到。

MASTER\_LOG\_FILE ， MASTER\_LOG\_POS

** **

2. **在 MYSQL-B执行SQL命令**

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> <p>#SHOW SLAVE STATUS</p> <p>CHANGE MASTER TO MASTER_HOST='10.0.0.2',MASTER_PORT=3306,MASTER_USER='backup',MASTER_PASSWORD='backup',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;</p> <p>START SLAVE;</p> <p>#STOP SLAVE</p> </td> 
  </tr> 
 </tbody> 
</table>

** **

3. **查看状态**

**MYSQL-B 上面SQL 命令 **SHOW SLAVE STATUS

**  你会看到slave\_io\_running,slave\_sql\_runing 的状态都是yes 那么表示从MYSQL-A到MYSQL-B进行复制过好了**

** **

七、 **从MYSQL\_B到MYSQL-A进行复制过程**

4. 检查MYSQL-B 的日志情况，在**MYSQL-B**执行SQL命令

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> <p>show master status</p> </td> 
  </tr> 
 </tbody> 
</table>

File 和 Position在复制过程中要用到。

MASTER\_LOG\_FILE ， MASTER\_LOG\_POS

** **

5. **在 MYSQL-A执行SQL命令**

** **

6. **查看状态**

**MYSQL-A 上面SQL 命令 **SHOW SLAVE STATUS

**  你会看到slave\_io\_running,slave\_sql\_runing 的状态都是yes 那么表示从MYSQL-B到MYSQL-A进行复制过好了**

** **

八、 **测试**

1. 建立表

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> 
    <table> 
     <tbody> 
      <tr> 
       <td> <p>&nbsp;</p> </td> 
      </tr> 
      <tr> 
       <td> <p>CREATE&nbsp;TABLE&nbsp;`masterslavetest`&nbsp;(<br> &nbsp;&nbsp;`id`&nbsp;bigint(20)&nbsp;NOT&nbsp;NULL&nbsp;AUTO_INCREMENT,<br> &nbsp;&nbsp;`item`&nbsp;varchar(30)&nbsp;DEFAULT&nbsp;NULL,<br> &nbsp;&nbsp;`value`&nbsp;varchar(30)&nbsp;DEFAULT&nbsp;NULL,<br> &nbsp;&nbsp;PRIMARY&nbsp;KEY&nbsp;(`id`),<br> &nbsp;&nbsp;KEY&nbsp;`id`&nbsp;(`id`)<br> )&nbsp;ENGINE=InnoDB;</p> </td> 
      </tr> 
     </tbody> 
    </table><p>&nbsp;</p> </td> 
  </tr> 
 </tbody> 
</table>

2. 在mysql-a 插入数据

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> <p>INSERT INTO masterslavetest(`item`,`value`) VALUES ("mysql-a","mysql-a");</p> </td> 
  </tr> 
 </tbody> 
</table>

3. 在mysql-b 查看

执行SQL 命令 SELECT \* FROM masterslavetest

4. 在mysql-a 插入数据

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> <p>INSERT INTO masterslavetest(`item`,`value`) VALUES ("mysql-b","mysql-b");</p> </td> 
  </tr> 
 </tbody> 
</table>

5. 在mysql-A 查看