2023-02-25

Mysql 备份/还原

words: 756 views: time: 3min

mysql

mysql备份常用操作

备份指定数据库

## 格式：
mysqldump -h主机名 -P端口 -u用户名 -p"密码" --database 数据库名1  数据库名2 > 文件名.sql

## 示例：
mysqldump -h10.64.32.69 -P3306 -uroot -proot \
          --single-transaction --master-data=2 --set-gtid-purged=OFF \
          --database -B db_1 db_2 > db_dump_`date +%Y%m%d-%H%M`.sql

-B 参数备份出来的内容自带create database库名和use 库名，数据还原时不用先创建和指定库；
–single-transaction 基于事务快照，看到的是备份开始时的数据，期间其他事务的DML操作不会参与备份；
–master-data=2 记录备份时binlog的坐标（文件名和位置），主要用于搭建主从复制或基于时间点的增量恢复；

备份指定表

## 格式：
mysqldump -h主机名 -P端口 -u用户名 -p"密码" 数据库名 表名1 表名2 > 文件名.sql

## 示例：
mysqldump -h10.64.32.69 -P3306 -uroot -proot \
          --single-transaction --master-data=2 --set-gtid-purged=OFF \
          db_1 tb_1 tb_2 > tb_dump_`date +%Y%m%d-%H%M`.sql

–no-data 仅备份数据库结构，不包含数据

还原数据

## 示例：还原db_1库中的tb_1表的备份
mysql -uroot -proot -e "CREATE DATABASE IF NOT EXISTS db_1"
mysql -uroot -proot db_1 < tb_dump.sql

## 示例：还原数据库
mysql -uroot -proot < db_dump.sql

线上清洗数据

场景：数据脏了，但表数据量大，现在需要只保留表中部分有用的数据，以便快速恢复关键业务。

问题：千万级数据，直接清理大量数据会产生海量log，超过限制，导致SQL执行失败。并且长事务持有锁，会阻塞其他DML操作，可能引发死锁或连接超时，影响业务可用性。

思路1：使用create table as将数据抽出来，将原表数据truncate后，再放进去；

-- 将正常数据复制到临时表
CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE group <> 'bad_group';

-- 清空原表数据，但不删除表
TRUNCATE TABLE MY_TABLE;

-- 将临时表数据插入到原表
INSERT INTO MY_TABLE SELECT * FROM TEMP_TABLE;

思路2：使用like命令快速复制一个表（结构一模一样），再将需要的数据放到新表里面，最后表名改回去；

-- 创建和原表结构一样的临时表
CREATE TABLE TEMP_TABLE LIKE MY_TABLE;

-- 将正常数据复制到临时表
INSERT INTO TEMP_TABLE SELECT * FROM MY_TABLE WHERE group <> 'bad_group';

-- 删除原表（最好用重命名备份）
DROP TABLE MY_TABLE;

-- 将临时表重命名为原表
RENAME TABLE TEMP_TABLE TO MY_TABLE;

定期备份

#!/bin/bash

## 备份文件存放路径
backupdir=/data/mysqlbak	

## 备份文件时间	
time=`date +%Y%m%d-%H%M`		

## 备份所有数据库并打包
mysqldump -h10.*.*.9 -P3306 -uroot -p"密码" --all-databases --single-transaction --default-character-set=utf8 | gzip > $backupdir/mysql$time.sql.gz		

## 删除7天之前的备份文件
find $backupdir -name "mysql*.sql.gz" -type f -mtime +7 -exec rm {} \; > /dev/null 2>&1

crontab -e添加计划任务：

0 0 3 * * * /data/mysqlbak/bakmysql.sh

恢复数据示例

gzip < mysql22020703.sql.gz | mysql -uroot -proot

参考：

Echo

articles: 138 views: