mysql 备份实现

主从复制

采用mysql自带的主从策略功能进行备份

主机配置

找到mysql配置文件：windows系统：mysql.ini，linux系统mysql.cnf，这里使用linux作为例子讲解。

 

打开配置文件，在[mysqld]下根据需要加入如下配置

server_id=1   

log-bin=mysql-bin

innodb_flush_log_at_trx_commit=1

sync_binlog=1

#需要备份的数据库，多个数据库写多行，不写则备份所有数据库

binlog-do-db=mytest   

#不需要备份的数据，多个数据库写多行

binlog-ignore-db=mytest

 

保存mysql.cnf，重启sql服务：service mysql restart

 

登录mysql，创建备份用户

grant replication slave on *.* to 'backup'@'195.23.0.78' identified by '123456'

195.23.0.78 为从机地址，backup用户只有备份权限

 

查看主机二进制日志

show master status;

 

记住file和position的值

导出主机数据

导数之前，最好停掉应用或锁表不允许写入数据，导数方法省略。。。。。。。

flush tables with read lock;   #锁表

mysqldump -uroot -p123456 --all-databases --lock-tables=false --> /all.sql   #导出到all.sql中

数据导出后，解除表锁定

unlock tables;

 

从机配置

导入数据，导数方法省略。。。。。。。。。

找到mysql配置文件，在[mysqld]下根据需要加入下面代码

server_id=2

replicate-do-db=test   #需要复制的数据库

replicate-ignore-db=test #不需要复制的数据库

read_only=1 #从机只允许同步数据，不能写库

innodb_flush_log_at_trx_commit=2 #提高性能，每杪日志写入磁盘

sync_binlog=0  #提高性能，日志内存满时候才写入磁盘

 

 

 

保存文件，重启mysql服务，重启方法省略。。。。。

 

登录mysql，输入命令关联主机

change master to master_host='195.23.0.77',master_user='backup',master_password='123456',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=330,master_connect_retry=60;

 

master_host：主机地址

master_user：主机备份用户

master_password：主机备份用户的密码

master_port：主机端口号

master_log_file：主机file字段的值

master_log_pos：主机position的值

master_connect_retry：主机网络中断时，重新尝试连接主机的间隔时间（秒）

 

启动slave进程

start slave;

 

查看slave状态

show slave status;

 

停止slave进程，停止同步

stop slave;

 

取消主从复制

reset slave;

冷备

利用mysqldump和操作系统计划任务实现数据库完整备份。这里使用windows系统作为例子讲解

配置环境变量

为使用mysql命令方便，需将mysql加入到系统的环境变量中，配置过程省略。。。。。。。。

创建批处理脚本

set date=%date:~0,4%%date:~5,2%%date:~8,2%

mysqldump -h195.23.0.77 -uadmin -pcmbcsymantec1qaz --all-databases > d:\MySQLBackup\%date%.sql

mysql -uroot -pcmbcsymantec1qaz --default-character-set=utf8 < d:\MySQLBackup\%date%.sql

 

以上代码为，将195.23.0.77服务器上的所有数据库备份到本机d:\MySQLBackup\%date%.sql中，其中%date%为当前日期格式为yyyyMMdd。然后覆盖本机所有数据库

 

将以上代码保存为MySQLBack.bat

 

以上批处理无法还原时，使用批处理脚本二，利用source 还原数据库【推荐】

set date=%date:~0,4%%date:~5,2%%date:~8,2%

mysqldump -h195.23.0.79 -uadmin -p123456 data_extraction > d:\MySQLBackup\data_extraction\%date%.sql

mysqldump -h195.23.0.79 -uadmin -p123456 supervise > d:\MySQLBackup\supervise\%date%.sql

( 

  echo use data_extraction

  echo source d:/MySQLBackup/data_extraction/%date%.sql

  echo quit

) | mysql -uroot -p123456 --default-character-set=utf8

 

( 

  echo use supervise

  echo source d:/MySQLBackup/supervise/%date%.sql

  echo quit

) | mysql -uroot -pcmbcsymantec1qaz --default-character-set=utf8

 

net stop mysql56

net start mysql56

//mysql56为windows下mysql的服务名称，如果不重启服务，你会发现服务器mysql内存没有释放

如果以上代码均不能还原，就只能通过MySQL-Front手动还原。打开还原文件窗口时，在右下角选择UTF-8，否则也会存在还原失败

检查配置

找到mysql配置文件，检查max_allowed_packet的值是否足够大，否则还原会失败

max_allowed_packet 用来控制其通信缓冲区的最大长度，max_allowed_packet=32M

创建计划任务

利用windows的计划任务，定时运行MySQLBack.bat，此方法省略。。。。。。。。。。