From owner-freebsd-questions@FreeBSD.ORG Wed Oct 25 13:50:24 2006 Return-Path: X-Original-To: freebsd-questions@freebsd.org Delivered-To: freebsd-questions@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id F258B16A4E2 for ; Wed, 25 Oct 2006 13:50:24 +0000 (UTC) (envelope-from snagit@cbpratt.prohosting.com) Received: from n126.sc0.cp.net (smtpout1077.sc0.he.tucows.com [64.97.144.77]) by mx1.FreeBSD.org (Postfix) with ESMTP id B24DA43D46 for ; Wed, 25 Oct 2006 13:50:24 +0000 (GMT) (envelope-from snagit@cbpratt.prohosting.com) Received: from [192.168.1.100] (67.47.213.85) by n126.sc0.cp.net (7.2.069.1) (authenticated as eagletree@hughes.net) id 453F51F5000061A5 for freebsd-questions@freebsd.org; Wed, 25 Oct 2006 13:49:45 +0000 Mime-Version: 1.0 (Apple Message framework v752.2) In-Reply-To: References: <200610241849.50723.freebsd@dfwlp.com> Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <4CA20A44-E79D-4975-B99A-8BBDDC9C3684@cbpratt.prohosting.com> Content-Transfer-Encoding: 7bit From: Chris Date: Wed, 25 Oct 2006 06:49:35 -0700 To: FreeBSD Questions X-Mailer: Apple Mail (2.752.2) Subject: Re: [OT] a mysql question X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 25 Oct 2006 13:50:25 -0000 On Oct 24, 2006, at 6:52 PM, jan gestre wrote: > On 10/25/06, Jonathan Horne 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.