Date: Wed, 25 Oct 2006 06:49:35 -0700 From: Chris <snagit@cbpratt.prohosting.com> To: FreeBSD Questions <freebsd-questions@freebsd.org> Subject: Re: [OT] a mysql question Message-ID: <4CA20A44-E79D-4975-B99A-8BBDDC9C3684@cbpratt.prohosting.com> In-Reply-To: <a25afc300610241852w2e076ddcmf2d37f3fff67e956@mail.gmail.com> References: <200610241849.50723.freebsd@dfwlp.com> <a25afc300610241852w2e076ddcmf2d37f3fff67e956@mail.gmail.com>
next in thread | previous in thread | raw e-mail | index | archive | help
On Oct 24, 2006, at 6:52 PM, jan gestre wrote: > On 10/25/06, Jonathan Horne <freebsd@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.
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?4CA20A44-E79D-4975-B99A-8BBDDC9C3684>