注意:先要检测mysqldump命令是否生效
可以使用命令mysqldump -uroot -p test > /backup/mysqldump/test.db测试下,如果提示mysqldump命令找不到
可以使用以下方法:
[root@localhost bin]# whereis mysql
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/share/mysql /home/mysql/bin/mysql /usr/share/man/man1/mysql.1.gz
[root@localhost bin]# ln -fs /home/mysql/bin/mysqldump /usr/bin
这个是因为/usr/bin目录下缺失mysqldump 导致,只需要一下方法建立软链接,即可以解决:
一、定时备份
参考的链接:https://blog.csdn.net/qq_37798548/article/details/83306718
(1)创建文件夹
mkdir mysqlbak
(2)新建备份脚本
vim /home/mysqlbak/mysql_bak.sh
(3)在脚本里填入以下内容
mysql_user="root" #MySQL备份用户 mysql_password="root" #MySQL备份用户的密码 mysql_host="127.0.0.1" #ip地址 mysql_port="3306" #端口 mysql_charset="utf8" #MySQL编码 backup_db_arr=("test_pj") #要备份的数据库名称,多个用空格分开隔开 如("db1" "db2" "db3") backup_location=/home/mysqlbak #备份数据存放位置,末尾请不要带"/",此项可以保持默认,程序会自动创建文件夹 expire_backup_delete="ON" #是否开启过期备份删除 ON为开启 OFF为关闭 expire_days=3 #过期时间天数 默认为3天,此项只有在expire_backup_delete开启时有效 backup_time=`date +%Y%m%d%H%M` #定义备份详细时间 backup_Ymd=`date +%Y-%m-%d` #定义备份目录中的年月日时间 backup_3ago=`date -d '3 days ago' +%Y-%m-%d` #3天之前的日期 backup_dir=$backup_location/$backup_Ymd #备份文件夹全路径 welcome_msg="Welcome to use MySQL backup tools!" #欢迎语 mysql_ps=`ps -ef |grep mysql |wc -l` mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l` if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then echo "ERROR:MySQL is not running! backup stop!" exit else echo $welcome_msg fi mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password <<end use mysql; select host,user from user where user='root' and host='127.0.0.1'; exit end flag=`echo $?` if [ $flag != "0" ]; then echo "ERROR:Can't connect mysql server! backup stop!" exit else echo "MySQL connect ok! Please wait......" # 判断有没有定义备份的数据库,如果定义则开始备份,否则退出备份 if [ "$backup_db_arr" != "" ];then #dbnames=$(cut -d ',' -f1-5 $backup_database) #echo "arr is (${backup_db_arr[@]})" for dbname in ${backup_db_arr[@]} do echo "database $dbname backup start..." `mkdir -p $backup_dir` `/home/mysql/mysql/bin/mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --default-character-set=$mysql_charset | gzip > $backup_dir/$dbname-$backup_time.sql.gz` flag=`echo $?` if [ $flag == "0" ];then echo "database $dbname success backup to $backup_dir/$dbname-$backup_time.sql.gz" else echo "database $dbname backup fail!" fi done else echo "ERROR:No database to backup! backup stop" exit fi # 如果开启了删除过期备份,则进行删除操作 if [ "$expire_backup_delete" == "ON" -a "$backup_location" != "" ];then #`find $backup_location/ -type d -o -type f -ctime +$expire_days -exec rm -rf {} \;` `find $backup_location/ -type d -mtime +$expire_days | xargs rm -rf` echo "Expired backup data delete complete!" fi echo "All database backup success! Thank you!" exit fi |
(4)脚本赋予执行权限
修改mysql_bak.sh脚本属性,赋予执行权限
chmod 600 /home/mysqlbak/mysql_bak.sh
chmod +x /home/mysqlbak/mysql_bak.sh
(5)定时执行备份脚本
crontab -e加入如下内容:
00 03 * * * /home/mysqlbak/mysql_bak.sh
# 每天凌晨3点执行一次 (00 03 * * * /home/mysqlbak/mysql_bak.sh)
(6)启动定时任务
/bin/systemctl start crond.service
/bin/systemctl stop crond.service
/bin/systemctl restart crond.service
这个命令在red hat当中常用,有的linux发行版本中没有这个命令.
$ service crond start //启动服务
$ service crond stop //关闭服务
$ service crond restart //重启服务
(7)查看备份结果
备份数据存储在路径为:/home/mysqlbak/
(8)解压备份的文件
压缩gz:
gzip fileName
解压gz:
gzip -d fileName
备份的文件使用gzip –d xxx进行解压,xxx代表文件的名称
二、还原
(1)先登录
mysql -u root -p
passwd:
(2)有外键关联的情况下可能会报错:登录
mysql> set @@global.foreign_key_checks = 0;
在导入完毕后
mysql> set @@global.foreign_key_checks = 1;
mysql>use dbname;#dbname数据库名称
mysql>source /home/sql_data/test.sql #格式为source .sql文件存储路径