Chan Chen Coding...

导航

统计

文章分类

文章档案

最新评论

MySQL Auto Backup

Below is the script example to backup mysql database in command line:-
$ mysqldump -h localhost -u username -p database_name > backup_db.sql
If your mysql database is very big, you might want to compress your sql file.
Just use the mysql backup command below and pipe the output to gzip,
then you will get the output as gzip file.
$ mysqldump -u username -h localhost -p database_name | gzip -9 > backup_db.sql.gz
If you want to extract the .gz file, use the command below:-
$ gunzip backup_db.sql.gz
Type the following command to import sql data file:
$ mysql -u username -p -h localhost DATA-BASE-NAME < data.sql
In this example, import 'data.sql' file into 'blog' database using vivek as username:
$ mysql -u vivek -p -h localhost blog < data.sql
If you have a dedicated database server, replace localhost hostname with with actual server name or IP address as follows:
$ mysql -u username -p -h 202.54.1.10 databasename < data.sql
OR use hostname such as mysql.cyberciti.biz
$ mysql -u username -p -h mysql.cyberciti.biz database-name < data.sql
If you do not know the database name or database name is included in sql dump you can try out something as follows:
$ mysql -u username -p -h 202.54.1.10 < data.sql
To auto backup database, please read 10 ways to Automatically & Manually Backup MySQL Database , here is one example
[root@sdc-d1-pangaea-devops1 ~]# vi /etc/crontab
SHELL
=/bin/bash
PATH
=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO
=root
HOME
=/

# For details see man 
4 crontabs

# Example of job definition:
# .
---------------- minute (0 - 59)
|  .------------- hour (0 - 23)
|  |  .---------- day of month (1 - 31)
|  |  |  .------- month (1 - 12) OR jan,feb,mar,apr 
|  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
|  |  |  |  |
*  *  *  *  * user-name command to be executed
59 23 * * tue root mysqldump -u root -proot123 jira > /export/home/web/dbbak/jira/jira_$(date +\%Y\%m\%d).sql
This script will generate an backup file at 23:59 every Tue.
Note
The cron does not regonize `date +%Y%m%d`, so use $(date +\%Y\%m\%d) instead of it.


-----------------------------------------------------
Silence, the way to avoid many problems;
Smile, the way to solve many problems;

posted on 2012-03-21 17:43 Chan Chen 阅读(281) 评论(0)  编辑  收藏 所属分类: DB


只有注册用户登录后才能发表评论。


网站导航: