_mysql Mysql Database backup script code in Ubuntu server

Source: Internet
Author: User
Description:

I'm here to store the MySQL database directory /var/lib/mysql The following pw85 database back into the /home/mysql_data, and Save as Mysqldata_bak_2012_04_11. The tar.gz compressed file format (2012_04_11 refers to the date of the day when the backup is performed), and only the last 7 days of backup is retained.

Implementation steps:

1, create a directory to save the backup file:/home/mysql_data
Cd/home #进入目录
mkdir Mysql_data #创建目录
2. Create backup script file:/home/mysql_data/mysql_databak.sh
Cd/home/mysql_data #进入目录
Touch mysql_databak.sh #创建文件
Nano mysql_databak.sh #编辑文件, enter the following

Copy Code code as follows:

#!/bin/sh
Dump=/usr/bin/mysqldump #mysqldump备份程序执行路径
Out_dir=/home/mysql_data #备份文件存放路径
Linux_user=root #系统用户名
Db_name=pw85 #要备份的数据库名字
Db_user=root #数据库账号 Note: Non-root users--skip-lock-tables with backup parameters, or they may error
db_pass=123456 #数据库密码
Days=7 #DAYS = 7 means to delete a backup 7 days ago, that is, only the last 7 days of backup
CD $OUT _dir #进入备份存放目录
Date= ' Date +%y_%m_%d ' #获取当前系统时间
Out_sql= "$DATE. SQL" #备份数据库的文件名
Tar_sql= "Mysqldata_bak_$date.tar.gz" #最终保存的数据库备份文件名
$DUMP-u$db_user-p$db_pass $DB _name--default-character-set=utf8--opt-q-R--skip-lock-tables> $OUT _sql #备份
TAR-CZF $TAR _sql./$OUT _sql #压缩为. tar.gz format
RM $OUT _sql #删除. SQL Format Backup files
Chown $LINUX _user: $LINUX _user $OUT _dir/$TAR _sql #更改备份数据库文件的所有者
Find $OUT _dir-name "mysqldata_bak*"-type f-mtime + $DAYS-exec rm {} \; #删除7天前的备份文件


(note: {} \; There are spaces in the middle)
###################################################################################### #################
ctrl+o #保存配置
ctrl+x #退出

3, modifying file properties to make it executable

chmod +x/home/ mysql_data/mysql_databak.sh

4, modify/etc/crontab

Nano/etc/crontab #编辑文件, add the following
* * * Root/ home/mysql_data/mysql_databak.sh #表示每天22点45分执行备份
Ctrl+o #保存配置
ctrl+x #退出

5, reboot Crond make setting effective

Service cron Stop #停止
Service cron start #启动
/etc/init.d/cron restart #重启
chkconfig cron on #设为开机启动

span> every day you can see a compressed file like mysqldata_bak_2012_04_11.tar.gz in the/home/mysql_data directory
If you need to recover the file, just unzip the file: Tar- ZXVF mysqldata_bak_2012_04_11.tar.gz
and then import into the database.

The MySQL database backup script is complete under Ubuntu server.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.