linux 系统下备份 mysql 数据库

mysql-backup

创建 shell script 备份脚本

1
vim mysql-backup.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#!/bin/sh
# This is a mysql datbase backup shell script.

# set mysql info
hostname="localhost"
user="root"
password="my password"

# set database info
database="bak database name"
bakpath="path to backup"
date=$(date +%Y%m%d_%H%M%S)

# backup
mkdir -p $bakpath
mysqldump -h$hostname -u$user -p$password $database | gzip \
> $bakpath/$database_$date_sql.gz

创建定时任务执行 shell script

手动备份

备份一个数据库

1
mysqldump -hhostname -uusername -pmypwd databasename > /path to backup/bakname.sql

备份并压缩

1
mysqldump -hhostname -uusername -pmypwd databasename | gzip > /path to backup/bakname.sql.gz

备份数据库一些表

1
mysqldump -hhostname -uusername -pmypwd databasename table1 table2 table3 > /path to backup/bakname.sql

仅备份数据库结构

1
mysqldump -no-data -databases databasename1 databasename2 databasename3 > /path to backup/bakname.sql

备份所有数据库

1
mysqldump -all-databases > /path to backup/bakname.sql

还原数据库

还原无压缩数据库

1
mysql -hhostname -uuser -pmypwd databasename < /path to backup/bakname.sql

还原压缩数据库

1
gunzip < /path to backup/bakname.sql.gz | mysql -hhostname -uusername -pmypwd databasename

迁移到新的服务器

1
mysqldump -hhostname -uuser -pmypwd databasename | mysql -hnew_hostname -C databasename

链接https://www.jianshu.com/p/b77dfd6d998b