1、mysqldump备份根据自己的安装环境略作修改，就可以方便备份mysql数据库了,此脚本为 按库备份，很方便。 [root@dbserver ~]# cat backupmysql.sh
#!/bin/sh # backup_mysql_dump.sh: backup mysql databases and keep newest 20 days backup. # # Last updated: 2016-11-09 # ---------------------------------------------------------------------- # This is a free shell script under GNU GPL version 2.0 or above # Copyright (C) 2016 Zhongzl # Feedback/comment/suggestions : http://www.real-blog.com/ # ---------------------------------------------------------------------- # your mysql login information # db_user is mysql username # db_passwd is mysql password # ----------------------------- source /etc/profile db_user="root" #这里填写登录数据库账号 db_passwd='root' #这里填写登录数据库密码 # the directory for story your backup file. backup_dir="/mysqlbackup" #这里填写备份目录 backup_log="/mysqlbackup/log" #这里填写备份日志目录 # date format for backup file (dd-mm-yyyy) date=$(date +"%Y-%m-%d") days_ago=$(date -d"20 days ago" +"%Y-%m-%d") #获取20天前日期 # mysql, mysqldump and some other bin's path MYSQL="mysql" MYSQLDUMP="/usr/local/mysql/bin/mysqldump" MKDIR="/bin/mkdir" GZIP="/bin/gzip" Begin=`date +"%Y年%m月%d日 %H:%M:%S"` # check the directory for store backup is writeable test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0 # the directory for story the newest backup test ! -d "$backup_dir/$date/" && $MKDIR "$backup_dir/$date/" #如果当天备份目录不存在，先创建，再备份 # get all databases 按照库导出全量数据 all_db="$($MYSQL -u $db_user -p$db_passwd -Bse 'show databases')" for db in $all_db do $MYSQLDUMP -u $db_user -p$db_passwd $db | $GZIP -9 > "$backup_dir/$date/$date.$db.gz" done Last=`date +"%Y年%m月%d日 %H:%M:%S"` echo -e "开始:$Begin \n结束:$Last succ" >> $backup_log/bak.log # delete the oldest backup test -d "$backup_dir/$days_ago" && rm -rf "$backup_dir/$days_ago" #删除20天前备份的数据 exit 0;
备份下来为
[root@dbserver 2021-10-18]# pwd /mysqlbackup/2021-10-18 [root@dbserver 2021-10-18]# ll total 260 -rw-r--r--. 1 root root 366 Oct 18 10:16 2021-10-18.information_schema.gz -rw-r--r--. 1 root root 227451 Oct 18 10:16 2021-10-18.mysql.gz -rw-r--r--. 1 root root 367 Oct 18 10:16 2021-10-18.performance_schema.gz -rw-r--r--. 1 root root 21681 Oct 18 10:16 2021-10-18.sys.gz -rw-r--r--. 1 root root 728 Oct 18 10:16 2021-10-18.testdb.gz
恢复testdb数据库示例首先登陆mysql删除testdb
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 5 rows in set (0.00 sec) mysql> drop database testdb; Query OK, 1 row affected (0.13 sec) mysql>
解压备份的testdb
[root@dbserver 2021-10-18]# gzip -d 2021-10-18.testdb.gz [root@dbserver 2021-10-18]# ls 2021-10-18.information_schema.gz 2021-10-18.mysql.gz 2021-10-18.performance_schema.gz 2021-10-18.sys.gz 2021-10-18.testdb
登陆mysql创建数据库testdb
mysql> create database testdb; Query OK, 1 row affected (0.01 sec)
恢复数据库testdb
[root@dbserver 2021-10-18]# mysql -uroot -prootroot testdb < 2021-10-18.testdb mysql: [Warning] Using a password on the command line interface can be insecure.
2.物理拷贝tar我安装的mysql见 http://blog.itpub.net/70004783/viewspace-2794003/
mkdir /backup systemctl stop mysqld tar cvf /backup/mysql20211018.tar /u01 我这里mysql安装路径是/u01/app/ rm -rf /mysql cd / tar xvf /backup/mysql20211018.tar systemctl start mysqld
