Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 '13 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 '13 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 '13 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 '13 at 15:16
    
Ouch !!! You just steamrolled the mysql schema. See my post : dba.stackexchange.com/a/31023/877 –  RolandoMySQLDBA Jan 24 '13 at 15:49
show 2 more comments

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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