MySQL实现实时备份[转]

本文转自：http://www.cnblogs.com/vforbox/p/4860422.html

环境介绍 

1.两台服务器进行主从同步复制（安装可以通过google查询相关资料或者看笔者的这篇免安装版）

2.利用Windows脚本和Windows自带的计划任务，将主服务器进行实时备份（下面进行演示）

Maseter MySQL: 192.168.1.253

Slave    MySQL: 192.168.1.254

3.在主服务器上开启二进制日志功能，设置唯一的服务器ID编号，这些设置需要重启MySQL服务

4.在从服务器上设置唯一的服务器ID编号，这些设置需要重启MySQL服务

5.在主服务器上为了不用从服务器创建可以读取主服务器日志文件的用户，或使用相同的统一用户

6.在进行数据复制之前，需要记录主服务器上二进制文件的位置的标记

7.在进行数据复制之前，保证从服务器和主服务器上的数据一致

主服务器的配置

在实际的环境中，可能在我们还没有部署数据库的同步前，数据库中就已经存在大量的数据。所以，在操作数据库的时候一定记住备份

mysqldump -u root -p --all-databases --lock-all-tables > G:/dbdump.sql
Enter password: ******

我们需要在主服务器上开启二进制日志并设置服务器编号，服务器唯一编号是2的32次方减1之间的整数，根据自己的实际情况而设置

C:\Users\Administrator>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

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

C:\Users\Administrator>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

为了使从服务器能够同步复制，我们需要创建一个同步复制的用户

1.执行数据复制时，所有的从服务器都需要使用用户与密码连接MySQL主服务器，所以在主服务器上必须存在至少一个用户及相应的密码提供从服务器来连接

2.但是这个用户必须拥有"REPLICATION SLAVE" 权限，当然你可以给不同的从服务器创建不用的用户与密码，也可以使用统一的用户与密码

3.如果该用户仅为数据库复制所使用，则该用户仅需要"REPLICATION SLAVE"权限即可

C:\Users\Administrator>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE USER 'slave'@'%' IDENTIFIED BY 'slaveAdmin';
Query OK, 0 rows affected (0.02 sec)
 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
 
mysql> exit
Bye

获取主服务器二进制日志信息

1.首先我们来了解一下二进制文件的基本信息，这些信息在对从服务器的设置需要用到，它包括服务器二进制文件名称及当前日志记录位置，这样从服务器就可以知道哪里开始进行复制操作

2.当我们得到二进制文件名以及二进制当前记录的位置时请牢记，在从服务器上会用到

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |  9876212 |              |                  |    |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

1.File列显示的是二进制日志文件名，Position为当前日志记录位置

2.FLUSH TABLES WITH READ LOCK 命令的作用是对所有数据库的表执行只读锁定，当只读锁定后所有数据库写操作都将被拒绝，但读操作可以继续

3.执行锁定可以防止在查看二进制日志信息的同时有人对操作进行修改操作，最后使用 UNLOCK TABLES 命令对全局锁执行结束操作

从服务器的配置

和主服务器一样，需要配置my.ini或者my.cnf 文件，注意这里的从服务器ID编号

C:\Users\Administrator>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

[mysqld]
server-id=2

C:\Users\Administrator>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

对于复制而言，MySQL从服务器二进制功能是不需要开启的，当然也可以开启从服务器上的二进制功能来实现数据备份与恢复

在介绍里面我已经说到在进行数据复制之前，保证从服务器和主服务器上的数据一致，还记得我刚才将主服务器的MySQL进行备份吗？！ 可以将这备份还原到从服务器上面，这样主从的数据就是一样的了

mysql -u root -p < D:/dbdump.sql
Enter password: ******

配置从服务器连接主服务器进行数据复制

1.其实数据复制的关键操作是配置从服务器去连接主服务器进行数据复制，我们需要告诉从服务器建立网络连接所有必要的信息

2.使用 CHANGE MASTER TO 语句完成与主服务器的连接工作

† MASTER_HOST 指定主服务器主机名或者Ip地址

† MASTER_USER 为刚才在主服务器上创建的拥有复制权限的用户

† MASTER_PASSWORD 为改用户的秘密

†  MASTER_LOG_FILE 指定主服务器二进制日志文件名称

† MASTER_LOG_POS 指定主服务器二进制日志文件当前的位置

START LSAVE 开启从服务器功能进行主从连接

SHOW SLAVE STATUS 查看从服务器状态

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.253',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='slaveAdmin',
    -> MASTER_LOG_FILE='mysql-bin.000004',
    -> MASTER_LOG_POS=9876212;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G;

查看状态时，下面两个都要为 YES 说明才是正常运行状态，如果IO为No 则检查密码或者从新写入

1. Slave_IO_Running: 
2. Slave_SQL_Running:

现在当我们在主服务器上创建一个数据库时，我们再到从服务器上查看，这时就可以看到已经把主服务器上新创建数据库的同步过来了

mysql> create database vforbox;
Query OK, 1 row affected (0.00 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| vforbox            |
+--------------------+

Windows 定时备份数据库脚本

上面介绍中已经提到用Windows脚本和Windows自带的计划任务，将主服务器进行实时备份

新建一个 "mysql_auto_bak.bat"

@echo off
cls
color 3E
title %date% %time:~,5% 备份MySQL数据库 By:vforobx
::::::::::::::::::::::::以下是需要配置的参数::::::::::::::::::::::::::::::::::::::::
 
        rem 设置 MySQL服务器root账号的密码,特殊符号需要在其前添加两个^
        SET MySQL_pw=123456
        rem 设置 数据库备份目录
        SET BAK_dir=D:\mysqlbak
        rem 设置 需要备份的myisam格式数据库
        SET BAK_db_myisam=myisam_db
        rem 设置 需要备份的innodb格式数据库
        SET BAK_db_innodb=innodb
        rem 设置 WinRAR压缩软件的路径
        SET RAR_dir="C:\Program files (x86)\WinRAR\WinRAR.exe"
        rem 设置 以2015-10-01格式的日期为子目录
        SET BAK_dir2=%date:~0,4%-%date:~5,2%-%date:~8,2%
        rem 设置 备份文件名
        SET BAK_file=%%i_%BAK_dir2%.sql
        rem 设置日志文件里面指向的rar文件路径
        SET BAK_file2=%%i_%BAK_dir2%
        rem 设置 日志文件名
        SET LOG_file=%BAK_dir%\%BAK_dir2%\Mysql_bak.log
 
::::::::::::::::::::::::以上是需要配置的参数::::::::::::::::::::::::::::::::::::::::
 
::::::::::::::::::::::::以下是判断变量是否定义::::::::::::::::::::::::::::::::::::::
 
if not defined MySQL_pw (echo MySQL_pw 尚未定义！)
if not defined BAK_dir (echo BAK_dir 尚未定义！)
if not defined RAR_dir (RAR_dir 尚未定义！)
 
::::::::::::::::::::::::以下是记录时间日志::::::::::::::::::::::::::::::::::::::::::
 
if not defined BAK_db_myisam (goto innodb)
echo. 开始以当前日期创建文件夹
if not exist %BAK_dir%\%BAK_dir2% md %BAK_dir%\%BAK_dir2%
cd /d %BAK_dir%\%BAK_dir2%
echo. 开始建立(%BAK_dir2%)的备份
::::::::::::::::::::::::以下是备份的核心代码::::::::::::::::::::::::::::::::::::::::
 
echo 备份时间: %BAK_dir2% %time:~0,8%  >> %LOG_file%
echo ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >> %LOG_file%
SETLocal DisableDelayedExpansion
for %%i in (%BAK_db_myisam%) do (
mysqldump -h 192.168.1.253 -uroot -p%MySql_pw% --all-databases >%BAK_file%
%RAR_Dir% a %BAK_file:~0,-4%.rar %BAK_file%
DEL /F /A /Q %BAK_file%
echo 数据库【%%i 格式】已经备份到: %BAK_dir%\%BAK_dir2%\%BAK_file2%.rar >> %LOG_file%)
::::::::::::::::::::::::以下是 innodb格式的备份代码::::::::::::::::::::::::::::::::::
 
if not defined BAK_db_innodb (goto exitbat)
echo. 开始以当前日期创建文件夹
if not exist %BAK_dir%\%BAK_dir2% md %BAK_dir%\%BAK_dir2%
cd /d %BAK_dir%\%BAK_dir2%
echo. 开始建立今天(%BAK_dir2%)的备份
SETLocal DisableDelayedExpansion
for %%i in (%BAK_db_innodb%) do (
mysqldump -h 192.168.1.253 -uroot -p%MySQL_pw% --all-databases >%BAK_file% 
%RAR_dir% a %BAK_file:~0,-4%.rar %BAK_file%
DEL /F /A /Q %BAK_file%
echo 数据库【%%i 格式】已经备份到：%BAK_dir%\%BAK_dir2%\%BAK_file2%.rar >> %LOG_file%)
echo ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >> %LOG_file%
echo. 所有备份建立完毕
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
rem 清除变量
        SET MySQL_pw=
        SET BAK_dir=
        SET RAR_dir=
        SET BAK_dir2=
        SET BAK_file=
        SET BAK_file2
        SET LOG_file=

如果觉得复制麻烦-点击下载

然后将脚本加入Windwos自带计划任务，这里笔者将不截图演示如何创建计划任务了，如需帮助请自行google