Powertools Scheduled Backups

[rwinnick]

New to sqlyog –

Have been regularly running scheduled backups from powertools –

How would I restore a db from these backups –

Would I use the run mysql restore command function under the db heading –

How does this work –

and –

can I use this capability to restore the db on a different server –

Any insights appreciated –

Thanks

Richard

[peterlaursen]

To restore the backup

1) with SQLyog: tools .. import from SQL statements

2) From commandline you can “source” the file

Windows: open commanline client .. connect (“mysql -uuser -p”) and enter “source [path_to]file.sql” on first line

Linux et al: sourcing is all in one line: “./mysql -uuser -p < [path_to]file.sql" .. when the 'mysql' client program is executed from 'current folder'. The .sql file only contains standard SQL statements and can be executed by any client (capable of more statements 'in one shot') no matter on what platform the server runs. 3) “can I use this capability to restore the db on a different server” Yes – provided that MySQL versions are not 'too much different'. The problem is that 'create statements' vary a little. If you have a backup from a 'higher version' that you want to restore to a 'lower version' it can be necessary to edit the file a little in a texteditor. Also with the new features of MySQL version 5 there can be a few syntax issues that must be corrrected because of imcompatible changes in MySQL itself (an example: the DEFINER clause as described here: http://www.webyog.com/faq/35_123_en.html .

Remember to use a text editor and not a word processor if you want/need to edit a backup file (and do it on a copy, of course)

The other issues that you shall be aware of are in particular:

1) the encoding. UTF8 is only supported from MySQL 4.1, so do not use it if you want to import to a lower version.

2) the max-allowed-packet size in MySQL configuration, possible PHP restrictions (with HTTP tunnel) and other network resource issues, read this: http://www.webyog.com/faq/24_101_en.html

3) appropriate selection of 'use' and 'drop' statements. As a general rule: use 'use', drop tables but do not drop the database.

I think you should start practising this a little before you need it seriously! Create a database with a few tables with a few rows of data in each, try the various settings, open the file in a texteditor, read it and understand it! Test your understanding of it by improting to an empty database. Do you get what you expected? Actually it is only around 5 SQL commands for a typical backup file that does not have anything else than plain table data.

[rwinnick]

Hi Peter –

Thanks so much for the insights –

I will take your advise and create a simple db to “fool” with until I understand how this all works –

Thanks again –

Richard

[Author]

Posts