怎么备份mysql数据库
发布网友
发布时间：2022-04-21 19:36
共2个回答
热心网友
时间：2022-04-30 04:58
1、先用工具连接到数据库，如Navicat Premium
2、在要备份的数据库点右键，选择转储sql文件-结构和数据
3、然后选择备份路径和备份文件名，保存即可。
热心网友
时间：2022-04-30 06:16
如果使用脚本备份的话 可以参考如下
准备
mkdir -p /data0/backup
cat > /tmp/myuse.txt<<EOF
[cleint]
user=root
password="Oracle#2017"
EOF
脚本内容：
mysql_backup_tleish.sh
#!/bin/bash#==============================================================================
#TITLE: mysql_backup_tleish.sh
#DESCRIPTION: script for automating the daily mysql backups on development computer
#AUTHOR: tleish
#DATE: 2013-12-20 change at 20180116 by hilodb
#VERSION: 0.4
#USAGE: ./mysql_backup_tleish.sh
#CRON:
# example cron for daily db backup @ 9:15 am
# min hr mday month wday command
# 15 9 * * * /Users/[your user name]/scripts/mysql_backup_tleish.sh
#RESTORE FROM BACKUP
#$ gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]
#==============================================================================
# CUSTOM SETTINGS
#==============================================================================
# directory to put the backup files
BACKUP_DIR=/data0/backup
# MYSQL Parameters
OPTS=" --all-databases --triggers --routines --events "
# Don't backup databases with these names
# Example: starts with mysql (^mysql) or ends with _schema (_schema$)
##在这里添加忽略备份的库
IGNORE_DB="(^mysql|_schema$)"
##如果保持IGNORE_DB为空就是备份所有数据库
#IGNORE_DB=""
# include mysql and mysqlmp binaries for cron bash user
PATH=$PATH:/usr/local/mysql/bin
# Number of days to keep backups
KEEP_BACKUPS_FOR=30 #days
#==============================================================================
# METHODS
#==============================================================================
# YYYY-MM-DD
TIMESTAMP=$(date +%F)
function checkok()
{
if [ $? -ne 0 ]; then
echo "backup database $1 failed,please check script"
exit
fi
}
function check_dir()
{
[ ! -d $BACKUP_DIR ] && mkdir -p $BACKUP_DIR
}
function delete_old_backups()
{
echo "Deleting $BACKUP_DIR/*.sql.gz older than $KEEP_BACKUPS_FOR days"
find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +$KEEP_BACKUPS_FOR -exec rm {} \;
}
function mysql_login() {
local mysql_login=" --defaults-extra-file=/tmp/myuse.txt "
echo $mysql_login
}
function database_list() {
if [ "x$IGNORE_DB" == "x" ];then
local show_databases_sql="SHOW DATABASES"
else
local show_databases_sql="SHOW DATABASES WHERE \`Database\` NOT REGEXP '$IGNORE_DB'"
fi
echo $(mysql $(mysql_login) -e "$show_databases_sql"|awk -F " " '{if (NR!=1) print $1}')
}
function echo_status(){
printf '\r';
printf ' %0.s' {0..100}
printf '\r';
printf "$1"'\r'
}
function backup_database(){
backup_file="$BACKUP_DIR/$TIMESTAMP.$database.sql.gz"
output+="$database => $backup_file\n"
echo_status "...backing up $count of $total databases: $database"
$(mysqlmp $(mysql_login) $OPTS $database | gzip -9 > $backup_file)
checkok $database
}
function backup_databases(){
local databases=$(database_list)
local total=$(echo $databases | wc -w | xargs)
local output=""
local count=1
for database in $databases; do
backup_database
local count=$((count+1))
done
echo -ne $output | column -t
printf "All backed up!\n\n"
}
function hr(){
printf '=%.0s' {1..100}
printf "\n"
}
#==============================================================================
# RUN SCRIPT
#==============================================================================
check_dir
delete_old_backups
hr
backup_databases
hr
运行结果如下
chmod +x mysql_backup_tleish.shbash mysql_backup_tleish.sh
[root@ambaos1 mysqlscript]# ll /data0/backup/
total 12
-rw-r--r-- 1 root root 130 Jan 16 15:33 2018-01-16.demo.sql.gz
-rw-r--r-- 1 root root 130 Jan 16 15:33 2018-01-16.sys.sql.gz
-rw-r--r-- 1 root root 130 Jan 16 15:33 2018-01-16.test.sql.gz
[root@ambaos1 data0]# tree -L 2
.
├── backup --忽略mysql和_schema的
│ ├── 2018-01-16.demo.sql.gz
│ ├── 2018-01-16.sys.sql.gz
│ └── 2018-01-16.test.sql.gz
└── backup_old ---全备的
├── 2018-01-16.demo.sql.gz
├── 2018-01-16.information_schema.sql.gz
├── 2018-01-16.mysql.sql.gz
├── 2018-01-16.performance_schema.sql.gz
├── 2018-01-16.sys.sql.gz
└── 2018-01-16.test.sql.gz
