 |
 |
Hi Matt, Good work - I'm glad things went well. I'd be interested to know: a) how database became corrupt. b) whether disaster recovery procedure was easy and well defined. c) anything we can do to stop a repeat. Suggestion: Let's add this to the Storage Meeting agenda and go over it. Cheers, Steve On 2016-03-02 16:53, Matt Williams wrote: > Hi both, > > Thanks a lot for your help with this. In the end, wiping the database > and re-importing did the trick. > > Cheers, > Matt > > On 26 February 2016 at 10:46, Sam Skipsey > <[log in to unmask]> wrote: >> hi Matt, >> >> I think Terry is assuming that you only have one database backed up? >> The >> problem is that you have a full backup, which includes many databases >> (all >> of the DPM databases, for example). >> I don't think Terry's method works for restoring from such a backup >> (see, ie >> http://stackoverflow.com/questions/2342356/import-single-database-from-all-databases-dump >> for discussion of how to chop out individual databases from a dump). >> >> Sam >> >> On Fri, 26 Feb 2016 at 09:57 Matt Williams >> <[log in to unmask]> wrote: >>> >>> Hi Terry, >>> >>> That really useful. Thank you. One question though, should the >>> command >>> to restore the database (using zcat pipe etc) read: >>> >>> # zcat mysql-dump-$theTime.sql.gz | mysql -u root mysql_new >>> >>> i.e. with the last argument being "mysql_new" rather than "mysql"? >>> I'm >>> guessing that's the name of the database to write into? >>> >>> Cheers, >>> Matt >>> >>> On 25 February 2016 at 14:24, Terry Froy <[log in to unmask]> wrote: >>> > On Thu, 2016-02-25 at 13:48 +0000, Matt Williams wrote: >>> > >>> >> Hi Terry, >>> >> >>> >> It's a Perl script which effectively boils down to: >>> >> >>> >> /usr/bin/mysqldump --user=$mysql_user --password=$mysql_pw --opt >>> >> --all-databases | gzip -c > mysql-dump-$theTime.sql.gz >>> >> >>> >> So, yes. >>> > >>> > Okay, >>> > >>> > This is how *I* would do this. >>> > >>> > root@mysql:~# /etc/rc.d/init.d/mysql stop >>> > >>> > At this point, take a full copy of /var/lib/mysql (or wherever your >>> > MySQL datadir points) just in case this all goes horribly wrong. >>> > >>> > Start up the database via the mysqld_safe command: >>> > >>> > root@mysql:~# /usr/bin/mysqld_safe --skip-grant-tables >>> > >>> > This will start the MySQL daemon but will bypass any security you have; >>> > so, your MySQL server should be adequately firewalled from the outside >>> > world while you do this. >>> > >>> > Once it is running, create a new database: >>> > >>> > root@mysql:~# mysql -u root 'create database mysql_new' >>> > >>> > ... then import your backup of the 'mysql' database: >>> > >>> > root@mysql:~# zcat mysql-dump-$theTime.sql.gz | mysql -u root mysql >>> > >>> > Shut down your MySQL database server process. >>> > >>> > Then, do: >>> > >>> > root@mysql:~# cd /var/lib/mysql >>> > root@mysql:/var/lib/mysql# mv mysql mysql_old >>> > root@mysql:/var/lib/mysql# mv mysql_new mysql >>> > >>> > At this point, restart your MySQL server. >>> > >>> > root@mysql:~# /etc/rc.d/init.d/mysql start >>> > >>> > Once complete, normal service should be resumed. >>> > >>> > Regards, >>> > Terry >>> > -- >>> > Terry Froy >>> > Cluster Systems Manager, Particle Physics >>> > Queen Mary, University of London >>> > Tel: +44 (0)207 882 6560 >>> > E-mail: [log in to unmask]