文章目录
1 摘要
在网络安全事故频发的今天,数据备份对于一家公司而言尤为重要,尤其是互联网公司。这里作者简单介绍使用 Linux 脚本定时自动备份 MYSQL 数据库。
mysql 备份官方文档: mysqldump — A Database Backup Program
2 备份前的准备工作
2.1 备份流程
将需要备份的数据库(可能运行在远程服务器)备份至本地(内网)服务器的文件中,或者备份至本地数据库中。
2.2 创建数据库配置信息
选择一个目录,用户保存需要备份的源数据库、目标数据库的配置信息
如创建 /etc/mysql
目录
sudo mkdir /etc/mysql
创建源数据库(待备份的数据库)配置文件 xxx-login-remote.cnf
vim /etc/mtsql/xxx-login-remote.cnf
配置文件内容:
[client]
user = 'username'
password = 'password'
host = '192.168.6.6'
如果是将源数据库备份至本地数据库,则再创建一个本地数据库的配置文件,格式与上边的相同,如果是备份至文件(sql语句),则不需要
本地数据库配置文件 xxx-login-local.cnf
配置文件内容:
user = 'username'
password = 'password'
host = '127.0.0.1'
3 数据备份命令
3.1 备份数据库至文件
将源数据库备份至文件中,该文件为 sql 语句
mysqldump --defaults-extra-file=/etc/mysql/xxx-login-remote.cnf --set-gtid-purged=OFF --databases database_name --result-file=database_name.sql
其中
xxx-login-remote.cnf
: 源数据库配置信息
set-gtid-purged
: 记录数据库的事务 id
database_name
: 需要备份的数据库名称
database_name.sql
: 备份后的数据库sql语句
该语句可以在源数据库所在服务器执行,也可以在本地执行(前提是本地可以连接到源数据库)
--databases
后边可以接多个数据库
备份后的数据库sql :
-- MySQL dump 10.13 Distrib 5.7.25, for Linux (x86_64)
--
-- Host: 127.0.0.1 Database: demo
-- ------------------------------------------------------
-- Server version 5.7.25
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `demo`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `demo` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `demo`;
--
-- Table structure for table `user`
--
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id 主键',
`user_name` varchar(30) DEFAULT NULL COMMENT '用户名',
`user_passcode` varchar(100) DEFAULT NULL COMMENT '登陆密码',
`user_email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`user_insert_time` varchar(30) DEFAULT NULL COMMENT '用户注册时间',
`user_update_time` varchar(30) DEFAULT NULL COMMENT '用户更新时间',
`user_status` tinyint(4) DEFAULT '1' COMMENT '用户账号状态,1正常(默认),2禁止登陆',
`user_version` int(10) unsigned DEFAULT '1' COMMENT '版本控制字段(默认1)',
`user_del` tinyint(4) DEFAULT '0' COMMENT '逻辑删除字段,0正常(默认),1删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user`
--
LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1,'tom','ed0de7252acf2980e677bacab01bde25','tom@example.com','2019-04-25 17:07:30','2019-04-25 17:07:30',1,1,0),(2,'bob','ed0de7252acf2980e677bacab01bde25','bob@example.com','2019-04-25 17:07:30','2019-04-25 17:07:30',1,1,0),(3,'jack','ed0de7252acf2980e677bacab01bde25','jack@example.com','2019-04-25 17:07:30','2019-04-25 17:07:30',1,1,0),(4,'lily','ed0de7252acf2980e677bacab01bde25','lily@example.com','2019-04-25 17:07:30','2019-04-25 17:07:30',1,1,0),(5,'liming','ed0de7252acf2980e677bacab01bde25','liming@example.com','2019-04-25 17:07:30','2019-04-25 17:07:30',1,1,0);
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2019-06-04 13:37:53
3.2 数据库同步
将远程数据库中的数据同步至本地数据库中
数据库级别,同步整个数据库
mysqldump --defaults-extra-file=/etc/mysql/xxx-login-remote.cnf --set-gtid-purged=OFF source_database_name | mysql --defaults-file=/etc/mysql/xxx-login-local.cnf target_database_name
数据库表级别,仅同步部分数据库表
mysqldump --defaults-extra-file=/etc/mysql/xxx-login-remote.cnf --set-gtid-purged=OFF source_database_name source_table_name | mysql --defaults-file=/etc/mysql/xxx-login-local.cnf target_database_name
其中
source_database_name
: 源数据库名称
target_database_name
: 目标数据库名称
source_table_name
: 源数据库表名
4 定时同步数据库
Linux 系统中使用 cron 服务处理定时任务
将上述的命令写进 shell 脚本,即可实现定时备份数据库的功能
4.1 数据库同步脚本
脚本路径: /root/script/xxx-database-sync.sh
脚本功能: 同步数据库表
# !/bin/bash
#
# xxx项目数据库表同步脚本
#
# (远程)源数据库配置信息
sourceDbConfig=/etc/mysql/xxx-login-remote.cnf
# (远程)源数据库名称
sourceDbName=remote_database_name
# (远程)源数据库表名
sourceDbTable=table_name
# (本地)目标数据库配置信息
targetDbConfig=/etc/mysql/xxx-login-local.cnf
# (本地)目标数据库名称
targetDbName=local_database_name
# (本地)日志文件目录
syncLogDir=/var/log/xxx-database-sync/
# 这里的-x 参数判断${logDir}是否存在并且是否具有可执行权限
if [ ! -x "${syncLogDir}" ]; then
mkdir -p "${syncLogDir}"
fi
# 同步数据库表
mysqldump --defaults-extra-file=${sourceDbConfig} --set-gtid-purged=OFF ${sourceDbName} ${sourceDbTable} | mysql --defaults-file=${targetDbConfig} ${targetDbName};
# 日志记录
echo "$(date "+%Y-%m-%d %H:%M:%S") | 数据库同步" >> ${syncLogDir}$(date "+%Y-%m-%d").log
4.2 定时同步功能
定时任务 crontab 简单使用,以 centOS 7 为例:
开机启动定时任务服务
systemctl enable cornd
启动定时任务
systemctl start cornd
关闭定时任务服务
systemctl stop crond
添加、编辑定时任务
crontab -e
内容如下:
00,10,20,30,40,50 * * * * /root/script/xxx-database-sync.sh
当前定时任务意思为每 10 分钟执行一次同步脚本
cron 表达式说明:
* * * * * command(s)
- - - - -
| | | | |
| | | | ----- Day of week (0 - 7) (Sunday=0 or 7)
| | | ------- Month (1 - 12)
| | --------- Day of month (1 - 31)
| ----------- Hour (0 - 23)
------------- Minute (0 - 59)
在线生成 cron : http://cron.qqe2.com/
注意事项 : */5 * * * *
表示每 5 分钟执行一次,但是可能会在部分系统中不执行
5 参考文档推荐
官方文档: mysqldump — A Database Backup Program
官方文档: Copying MySQL Databases to Another Machine