Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I am currently trying to find a way to automatically restore a dump.sql file to another server weekly. I haven't found the code to do this yet in either mysql or command prompt but I am looking. The main problem I can see is that the server creates two database backups each day one for each database and the name changes each day because we store the date in the name.

Is it possible to set up some code that restores the newest file for each database once a month?

I have done a lot of research but I keep finding guides on how to create the dump files or how to upload a single dump file to a new database but can't find anything on this.

share|improve this question

migrated from stackoverflow.com Sep 21 '11 at 8:36

This question came from our site for professional and enthusiast programmers.

    
Use Full Question. +1 –  Sagotharan Aug 2 '11 at 11:32
1  
Are you asking how to identify the two newest files? If so, we would need to know the naming convention specifics of the files, and what shell you're using (if it's not bash). Also, is it safe to assume that the files are created with mysqldump, specifying a single database on the command line? This makes a difference as to the contents of the SQL script itself. –  Doug Kress Sep 10 '11 at 16:22

4 Answers 4

You can try SQLyog's SQL Scheduler and Reporting Tool which allows you to schedule execution of query(s). It has a Job Agent that allows you to generate, format and send personalized mails with results returned from a query. You can specify an external file(dump.sql in your case) or enter query(s) that you want to execute using this tool. There is an option Save & Schedule job using Windows Scheduler if you need the job to be launched automatically according to a certain schedule.

share|improve this answer

Basically what I would do is write a bash script that runs once a day/week/month.

Here's some logic (that I would do)

Inside the directory where the backups are located you can grab the latest version of each backup you want by editing the UNIQUEPARTHERE

ls -lthr | grep UNIQUEPARTHERE | tail -1 | cut -f9 -d ' '

For example:

$FILE = ls -lthr | grep DATABASE_BACKUP1 | tail -1 | cut -f9 -d ' '

The above command will show the latest backup of any file that contains DATABASE_BACKUP1 in the filename.

After that it's just about running a mysql command on a remove server:

mysql --user=mysql_username --password=your_password remote_database_name < $FILE --host=IP_OF_REMOTE_SERVER_GOES_HERE

Of course this is a rough idea of how I would handle it based on the assumptions that you use bash and the backup is created using mysqldump ;)

Good luck :)

share|improve this answer

bash file is nice idea

Another way is to make backup into specific directory Export database:

mysql>mysqldump -h hostname -u username -p database_name > backup_db.sql

I am putting it into my webserver directory and can accessed as http://xxxxx/backup_db.sql

Into another server

Simply use wget http://xxxxx/backup_db.sql and import into db via

mysql> source /home/om/Download/dbname.sql

You can automate whole process into crontab

share|improve this answer

If you're using unix/linux:

You could use CRON to execute scheduled task (each week in your case)

Then use mysql command line in your script used by CRON to import the database to another server.

To determine the newest file

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.