restoring a MySQL dump on a freshly installed debian/ubuntu system

9 Apr

to restore a dump from a MySQL database created on a debianish Linux system, just feed the dumped SQL to the command-line mysql client like this:

mysql -u root -p < mysql-all-2010-03-28-0515.sql

since the whole content of all MySQL databases are overwritten with that stored in the dump, credentials are affected as well. that’s the reason why debian’s system tools won’t work any more after restoring the old dump, since debian creates a maintenance-user called “debian-sys-maint” during the installation and stores the randomly generated credentials in “/etc/mysql/debian.cnf” so it’s sufficient to just copy the “password” values from the old file into the new one and restart mysql. otherwise, you will run into an error like this:

/etc/mysql/debian-start[3181]3181: Running ‘mysqlcheck’…
/etc/mysql/debian-start[3181]3181: /usr/bin/mysqlcheck: Got error: 1045: Access denied for user ‘debian-sys-maint’@‘localhost’ (using password: YES) when trying to connect
/etc/mysql/debian-start[3181]3181: FATAL ERROR: Upgrade failed