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]
|