目录[-]

Centos7 mysql5.7主从双机备份

主从备份介绍

保持不同主机数据库的同步,起到实时备份的作用,或者是读写分离的功能,如果主数据库废掉了,能够立马替换。

mysql备份工作原理

  • 主数据库(Master)的sql语句对从数据库(Slave)执行一遍。记录sql语句(除了show和select)就是binlog(Binarylog )日志!它是已二进制的形式存储,速度快,开销小。
    1.Master把每一次改动的sql语句记录到Binarylog 中
    2.Slave用Master允许的账号登录Master,读取Binarylog写入到自己的中继日志 Relaylog。
    3.Slave的SQL slave thread(sql线程)会负责读取这个中继日志,更新Slave的数据,保持自己和Master数据一致(中继日志通常会位于OS的缓存中,所以中继日志的开销很小)。
    

Master指主数据库 ,Slave 指 从数据库

准备工作:

  • 准备两台配置环境相同,mysql版本相同的服务器,防止错误。
  • 提前对两台服务器的数据库进行同步。
  • 了解其工作原理,提前对mysql数据库有一定的了解

主从数据库配置文件

主服务器数据库配置文件

log-bin = mysql-bin #开启binlog
binlog_format = row #日志格式
server-id = 1 # 主服务器id,主从不一致即可
binlog_do_db = mall  #需要备份的数据库,如果有多个,可以添加多个
binlog_do_db = mall2  
binlog-ignore-db=mysql  #忽略的数据库。
#binlog-ignore-db=information_schema
#binlog-ignore-db=performance_schema 
#auto_increment_increment=2 表示需要2台服务器做备份
#auto_increment_offset=1 #表示这台服务器的序号,从1开始, 不超过auto-increment-increment。

注意:binlog_do_db和binlog-ignore-db参数相互对立,写一种就可以;

从服务器数据库配置文件

log-bin=mysql-bin
binlog_format = row
server-id = 2
replicate-do-db=mall
replicate-do-db=mall2
replicate-ignore-db=mysql    
#replicate-ignore-db=information_schema
#replicate-ignore-db=performance_schema
relay_log=mysql-relay-bin #中继日志的名字
log-slave-updates=OFF #中继日志执行之后,这些变化是否需要计入自己的binarylog。 当Slave需要作为另外一个服务器的Master的时候需要打开。  就是多机异地热备份, 我们不需要, 不开。

写完配置文件重启两台服务器

创建用来访问Master账号,也就是主服务器的mysql访问账号:

1.创建账号:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
host指ip,如果不限制填写 %
2.授权
grant replication slave on *.* to 'username'@'%' identified by 'password';
3检查是否可以登录
mysql -u username -p
如果在从服务器上不能登录的话,请检查连接和授权问题

查看Master的binlog日志位置

mysql> show master status;
+------------------+----------+----------------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB         | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+----------------------+------------------+-------------------+
| mysql-bin.000004 |      544 | mall,mall2 |                  |                   |
+------------------+----------+----------------------+------------------+-------------------+
1 row in set (0.00 sec)

接下来可能会用到的信息是 File列和Position列

Slave配置

到从数据库执行该命令,自己先修改其中的部分信息
mysql> CHANGE MASTER TO MASTER_HOST='hostname',MASTER_USER='username',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=544;
注释:
MASTER_LOG_FILE #bing-log 文件名
MASTER_LOG_POS 就是Position 列

slave命令

  • slave stop #停止slave
  • slave start #开启slave

重启mysql,查看slave线程开启

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: host
                  Master_User: username
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 544
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 706
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

注释:只要Slave_IO_Running 和 Slave_SQL_Running 的状态为yes就可以。 如果不是的话,就可以查看错误信息,进行具体信息来解决。