[OT] a mysql question

[Chris]

On Oct 24, 2006, at 6:52 PM, jan gestre wrote:

> On 10/25/06, Jonathan Horne <freebsd at dfwlp.com> wrote:
>>
>> i do know how to import a .sql database dump, i was just wondering  
>> if i
>> can do
>> it via a restore job from netbackup.  any opinions i can get, are  
>> greatly
>> appreciated.
>
> to backup a mysql database:
>
> $ mysqldump -u user -p --opt databasename > database.bak.sql
>
> to restore a database from the backup:
>
> $ mysql -u user -p database < database.bak.sql
>
> just make sure user has privileges to do the necessary commands, if  
> not you
> can use the root user of mysql. you can also use a gui tool like  
> phpmyadmin
> for managing mysql.

Just an extra detail. Be sure that you not only do a mysqldump for your
application databases but also dump the "mysql" database so you don't
lose any internal permission records you've created. You or your apps
will have likely created records in that database necessary for when you
attempt to actually use your restored application databases. The  
resulting
dump will need to be edited before you try to recover since your setup
of mysql on the restored server will automatically create the  
structures.
The dump file will duplicate the creation (not good). Get around that by
deleting everything in that dump file except the INSERT statements for
records you've created. They will usually be somewhat obvious, e.g.,
you may have an entry for "daemon" if a webserver cgi application has
been granted access to some database.

The records you will be interested in backing up
are from the database "mysql" and the table called "user". These must
be restored after you restore the application databases in most cases
since they refer to the existence of the application database.