分类 Database 下的文章

前言:

备份很重要、备份很重要、备份很重要。(PS:对应备份我也不免入俗了,重要的事情说三遍!!!)
但是我们不用每次都自己操作备份或导出数据文件,可以Shell脚本帮我们自动完成这项重复劳动。

备份数据库前的思考:

  • 确定备份文件存放位置、备份文件名策略、是否自动删除超过指定天数的历史文件。
  • 需要备份哪些数据库还是所有的数据库。
  • 导出备份文件后,是否需要对备份的数据文件进行压缩减小体积。

想清楚上面的几个问题后,就可以着手操作了。

准备条件

使用[mysqldump]配置文件方法

  1. 创建备份专用账号

    CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'p455w0rd';
    GRANT SELECT, SHOW VIEW, LOCK TABLES, RELOAD, REPLICATION CLIENT ON *.* TO 'backupuser'@'localhost';
    FLUSH PRIVILEGES;
  2. 将1中添加的账号添加至配置文件中

    [mysqldump]
    user=backupuser
    password=p455w0rd

备份脚本

#!/bin/bash

# 直接指定备份数据库使用的用户名、密码。(不建议)
# user="db_username"
# password="db_password"

# 指定备份具体的数据库,多个数据库,中间用空格进分割。如果全部备份可以使用参数 --all-databases
# dbname="db1 db2 db3"

# 指定备份存放目录、备份文件名、备份文件名日期信息
backup_path="/data/bak/db"
backupname="db"
date=$(date "+%Y%m%d")

echo "Backing up mysql databases..."

# 检测是否已存在备份文件,如有,进行删除操作
if [ -f "$backup_path/$backupname-$date.sql.bz2" ]; then
    rm -fv $backup_path/$backupname-$date.sql*
fi

echo "Dumping databases..."

## 直接指定username, password(不推荐)
# mysqldump --user=$user --password=$password --databases $dbname > $backup_path/$backupname-$date.sql

## 将username, password放在配置文件中[mysqldump]下(推荐)
# mysqldump --databases $dbname > $backup_path/$backupname-$date.sql
## 备份所有数据库
mysqldump --all-databases > $backup_path/$backupname-$date.sql

echo "Compressing databases..."
bzip2 $backup_path/$backupname-$date.sql

chmod 600 $backup_path/$backupname-$date.sql.bz2

# Delete files older than 30 days(移除超过30天的历史备份文件)
echo "Checking for old files..."
find $backup_path/* -mtime +30 -exec rm {} \;
echo "Done."

使用Crontab定时执行

进入Crontab编辑模式:

crontab -e

添加定时任务:

# m h  dom mon dow   command
30  1  *   *   *     . /etc/profile; /data/soft/script/mysql_backup.sh > /data/soft/script/mysql_backup.log 2>&1 &

说明:

  • 每天凌晨1:30执行。
  • 由于后台MySQL是使用源码形式安装的,所有MySQL的命令不能直接运行,我在环境变量文件/etc/profile最后添加了export PATH=$PATH:/data/soft/mysql/bin
  • . /etc/profile;,使环境变量生效。
  • 2>&1:如果执行过程中有异常信息,将stderr也重定向标准输出流stdout

恢复数据

通过mysql命令

mysql -u username -p new_database < data-dump.sql

通过source命令:

# 登录数据库
mysql -u root -p

# 切换需要导入数据的数据库
use new_database;

# 通过source快速导入
source {folder_path}/data-dump.sql;

MySQL的函数replace(str, search_str, replace_str),用于从str中查找匹配search_str并替换为replace_str
通过这个函数可以移除内容中的空格,比如:

update table_a set column_a = replace(column_a, ' ', '')

但是对于tab、回车换行等字符不能直接用文本,此时我们可以考虑使用ASCII码来处理:

  • 空格的ASCII码为char(21)
  • tab的ASCII码为char(9)
  • 换行符的ASCII码为char(10)

SQL替换语句为:

-- 移除空格
update table_a set column_a = replace(column_a, char(21), '');

-- 移除tab
update table_a set column_a = replace(column_a, char(9), '');

-- 移除替换换行符
update table_a set column_a = replace(column_a, char(10), '');

参考文章:
sql: 去除数据库表中tab、空格、回车符等特殊字符的解决方法 去除tab、空格、回车符等使用replace语句 按照ASCII码

背景:最近业务上经常遇到客户试用系统一段时候后,由于试用阶段对系统不熟悉,试用后需要将使用阶段的测试数据都要清空,然后进行正式使用系统。清空基础数据相关的表操作,但是有外键约束,是无法直接删除,需要先将有外键关联的表的数据清空,解除外键约束。

如果才能获取有哪些表引用了该表呢?其实我们可用从MySQL中的information_schema.KEY_COLUMN_USAGE来获取。

-- 获取所有信息
SELECT *
FROM information_schema.KEY_COLUMN_USAGE
WHERE CONSTRAINT_SCHEMA = '{database}'
  AND REFERENCED_TABLE_NAME = '{table}';

-- 获取引用表及外键关联列名
SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE CONSTRAINT_SCHEMA = '{database}'
  AND REFERENCED_TABLE_NAME = '{table}';

表information_schema.KEY_COLUMN_USAGE字段说明

- 阅读剩余部分 -

全国行政区域数据库

SQL脚本:

CREATE TABLE `area` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL COMMENT '名称',
  `abbr` varchar(50) NOT NULL COMMENT '简称',
  `sortBy` int(3) DEFAULT '0' COMMENT '排序',
  `level` int(1) DEFAULT NULL COMMENT '等级(1省/直辖市,2地级市,3区县,4镇/街道)',
  `longitude` varchar(50) DEFAULT NULL COMMENT '经度',
  `latitude` varchar(50) DEFAULT NULL COMMENT '纬度',
  `parentId` int(11) DEFAULT NULL COMMENT '父级ID',
  PRIMARY KEY (`id`),
  KEY `fk_area_parent` (`parentId`),
  CONSTRAINT `fk_area_parent` FOREIGN KEY (`parentId`) REFERENCES `area` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

- 阅读剩余部分 -

最近处理通过JDBC导出备份mysql数据库,中间历经波折踩了不少坑,不过也收获颇多,在此分享下。

MySQL转义符

  • '0':ASCII 0 (NUL)符;
  • 'n':换行符;
  • 'r':回车符;
  • '\':反斜杠(“”)符;
  • ''':单引号(“'”)符;
  • '"':双引号(“"”)符

- 阅读剩余部分 -

现在的应用系统越来越多的都是业务系统与数据库系统是分离的,这里就会涉及到数据库备份的问题。如果业务系统与数据库是在同一服务器,可以很简单的用mysql自带的命令:

mysqldump --databases dbName > dbNameBak.sql

现在是数据库服务器与应用系统不在同一台服务器,我们可以通过JDBC方式进行备份数据库操作,

- 阅读剩余部分 -