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.