2

I have one server running an older Debian version with MySQL 5.x and a newer Debian server, also running MySQL.

I've created a backup of all databases on the first server like so:

mysqldump -uuser -ppass --all-databases > dump.sql

On the other server, I did a:

mysql -uuser -ppass < dump.sql

At first, everything seemed great. I could browse my databases in phpMyAdmin, but as soon as I tried logging in again, it failed. Turns out, my root password had been overwritten with the one from the older database.

I wanted to reset it, but in order to do so, I would have needed to start mysqld_safe. Which I couldn't because the password for the debian-sys-maint user had been overwritten as well in the database. When I thought all hell had broken loose, I somehow reset both the root and debian-sys-maint passwords to the original values of the new server, and I managed to revert to a clean state.

Since I obviously don't want to go down that road again, here's the question(s):

  • Was I right with my approach of using a complete --all-databases dump?
  • Was there something I needed to do in advance to reading in that dump to prevent this desaster from happening? Or even before creating the dump?

If I'm going about this the wrong way:

  • What is the proper procedure for migrating all databases and their users to another server?

Note that I'm not that experienced with MySQL and server administration at all, so I might be missing something obvious. All the tutorials and how-tos I've found never mention anything like this and just talk about importing the complete dump.

Improve this question
7
  • Just to make sure I understand: you dumped from the old and imported it in the new and expected the new passwords to remain untouched, but they were overwritten?
    – Derek Downey
    Jan 24, 2013 at 14:35
  • I didn't particularly expect the root and Debian admin user passwords to be overwritten, no. The other privileges, yes, but not those that you need to access and administer your MySQL installation in the first place, because… well, you'd lock yourself out. In hindsight, this was a bad idea, but from all I've read before there were no warnings about a situation like this.
    – slhck
    Jan 24, 2013 at 14:38
  • Question: Was the version of MySQL the same on both machines? If not, what was version of MySQL you mysqldump'd, and what was the version of MySQL you loaded ?
    – RolandoMySQLDBA
    Jan 24, 2013 at 15:13
  • It wasn't the same version exactly. 5.0.51a on the old and 5.1.66 on the new one. @rol
    – slhck
    Jan 24, 2013 at 15:16
  • Ouch !!! You just steamrolled the mysql schema. See my post : dba.stackexchange.com/a/31023/877
    – RolandoMySQLDBA
    Jan 24, 2013 at 15:49

1 Answer 1

Reset to default
2

That is a normal procedure for setting up a new server from old database, so there's nothing wrong with this. Probably the reason there is no mention of the passwords getting overwritten is admins know the old passwords so can easily update them on the new server.

One thing you can do is modify the dumpfile to use the new passwords for root and your Debian admin user prior to loading it into the new server.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.