Linux定时备份与同步MySQL数据库

喜欢ヅ旅行 2022-01-26 09:49 499阅读 0赞

### 文章目录 ###

*   *   *  1 摘要
         *  2 备份前的准备工作
         *   *  2.1 备份流程
             *  2.2 创建数据库配置信息
         *  3 数据备份命令
         *   *  3.1 备份数据库至文件
             *  3.2 数据库同步
         *  4 定时同步数据库
         *   *  4.1 数据库同步脚本
             *  4.2 定时同步功能
         *  5 参考文档推荐

### 1 摘要 ###

在网络安全事故频发的今天，数据备份对于一家公司而言尤为重要，尤其是互联网公司。这里作者简单介绍使用 Linux 脚本定时自动备份 MYSQL 数据库。

mysql 备份官方文档: [mysqldump — A Database Backup Program][mysqldump _ A Database Backup Program]

### 2 备份前的准备工作 ###

#### 2.1 备份流程 ####

将需要备份的数据库(可能运行在远程服务器)备份至本地(内网)服务器的文件中，或者备份至本地数据库中。

![备份流程][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx_size_16_color_FFFFFF_t_70]

#### 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/][http_cron.qqe2.com]  
**注意事项** : `*/5 * * * *` 表示每 5 分钟执行一次，但是可能会在部分系统中不执行

### 5 参考文档推荐 ###

[官方文档: mysqldump — A Database Backup Program][mysqldump _ A Database Backup Program]

[官方文档: Copying MySQL Databases to Another Machine][Copying MySQL Databases to Another Machine]

[Use mysqldump to Back Up MySQL or MariaDB][]

[Scheduling Cron Jobs with Crontab][]

[mysqldump _ A Database Backup Program]: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx_size_16_color_FFFFFF_t_70]: /images/20220126/00d1c5bf0dd840a6899cf2b68d503297.png
[http_cron.qqe2.com]: http://cron.qqe2.com/
[Copying MySQL Databases to Another Machine]: https://dev.mysql.com/doc/refman/5.7/en/copying-databases.html
[Use mysqldump to Back Up MySQL or MariaDB]: https://www.linode.com/docs/databases/mysql/use-mysqldump-to-back-up-mysql-or-mariadb/
[Scheduling Cron Jobs with Crontab]: https://linuxize.com/post/scheduling-cron-jobs-with-crontab/