Mysql mysqldump备份数据库

谁践踏了优雅 2022-05-29 20:42 308阅读 0赞

# mysqldump备份数据库 #

*  mysqldump备份数据库
    
     *  背景
     *  mysqldump 语法
     *  备份脚本
     *  crontab
     *  导入
     *  其他备份方法

## 背景 ##

*  数据库在测试环境，不是很稳定，存在丢失的风险
 *  数据库表结构变化较频繁，存在业务系统产生的测试数据，过程繁琐不能伪造
 *  有些数据，比如日志流水，备份时需要先删除老数据，只保留部分最近的日志即可

因此直接使用`crontab`每天定时备份数据库，同时做一些清理。

## mysqldump 语法 ##

Dumping structure and contents of MySQL databases and tables.
    Usage: mysqldump [OPTIONS] database [tables]
    OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

参数说明：[MySQL mysqldump数据导出详解][MySQL mysqldump]

## 备份脚本 ##

#!/bin/bash 
    # base dir
    BASE_DIR=/any/path
    # mysql common params
    MYSQL_COMMON_PARAMS="-h192.168.0.192 -uroot -proot"
    # date
    DATE=`date "+%Y-%m-%d"`
    echo "[`date`] database scheduler back up start $DATE --------------------" >> $BASE_DIR/back.log
    
    # back up start
    mysqldump $MYSQL_COMMON_PARAMS --databases scheduler | gzip > $BASE_DIR/data/scheduler_$DATE.sql.gz
    
    # delete old back up data
    # delete file 10 days ago
    find $BASE_DIR/data -mtime +10  -name "*.gz" -exec rm -rf {} \;
    
    # delete old data from scheduler.qrtz_log
    # too much unuseful log
    echo "DELETE FROM qrtz_log WHERE CREATE_TIME < '$DATE';" > $BASE_DIR/delete_qrtz_log.sql
    echo "[`date`] DELETE FROM qrtz_log WHERE CREATE_TIME < '$DATE';" >> $BASE_DIR/back.log
    
    # execute sql from file
    mysql $MYSQL_COMMON_PARAMS -DScheduler < $BASE_DIR/delete_qrtz_log.sql
    echo "[`date`] database scheduler back up end $DATE --------------------" >> $BASE_DIR/back.log

## crontab ##

0 2 * * * sh /any/path/back.sh >> /any/path/back.log

## 导入 ##

*  可以直接在`mysql`控制台用`source`命令导入

[root]# mysql
    MySQL > source backupfile.sql

*  也可以直接用命令

# for sql
    mysql -hhostname -uusername -ppassword [databasename] < backupfile.sql
    
    #for gzip
    gunzip < backupfile.sql.gz | mysql -uusername -ppassword [databasename]

## 其他备份方法 ##

当需要根据`where`条件导出时，一种办法是使用`--databases db1 --tables tb1 tb2 --where 'col_name=col_value'`，但是这种办法只能导出一个表或者导出多个表中相同条件的数据，如果条件比较复杂或者需要连表查询，可能不太方便，如果在`--where`条件里面这样写`--where 'CREATE_TIME>(select max(CREATE_TIME) from t1)'`会报错`Table 't1' was not locked with LOCK TABLES (1100)`，这时候可以用`select into outfile`和`load data local infile into table`来操作。

[root]# mysql
    MySQL [(none)] > use test;
    Database changed
    MySQL [test]> SELECT @@global.secure_file_priv;
    +---------------------------+
    | @@global.secure_file_priv |
    +---------------------------+
    | /var/lib/mysql-files/     |
    +---------------------------+
    1 row in set (0.00 sec) # 上面这个路径是select into 和 load into文件存放的位置，可以在my.ini修改 # 如果导入导出的文件不在这个目录下，会报下面的错 # [Err] 1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement MySQL [(test)] > SELECT t1.* FROM t1 WHERE col2 IN (SELECT col1 FROM t2) INTO OUTFILE '/var/lib/mysql-files/test.sql';
    Query OK, 2 rows affected (0.00 sec)
    
    MySQL [(test)] > DELETE FROM t1;
    Query OK, 2 rows affected (0.00 sec)
    
    MySQL [(test)] > LOAD DATA INFILE '/var/lib/mysql-files/test.sql' INTO TABLE t1;
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

[MySQL mysqldump]: https://www.cnblogs.com/chenmh/p/5300370.html