3

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.

Scott Pack
  • 14,907
  • 10
  • 53
  • 83
  • Use Full Question. +1 –  Aug 02 '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 Answers4

2

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 :)

Kenny
  • 143
  • 2
  • 15
1

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.

Ashwin A
  • 151
  • 1
1

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

Fred
  • 111
  • 2
1

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

Romi
  • 11
  • 1