Transferring ALL MySQL Data
A few weeks back, I switched the server OS. From Ubuntu 12.04 to Debian 7 for multiple reasons.
- To get to know Debian OS in general
- To get rid of frequent updates from Ubuntu flavors
- To use PHP 5.4, without any modifications.
- To use PHP 5.5, using DotDeb, sometime in the future.
- To use MariaDB in a fresh install
- To see how migrations work from one OS to another OS
- To see how migrations work from one server to another server
- To prepare for migrations for clients!
- To test drive websites using PHP 5.4 or 5.5.
- To see, if there is any savings in memory in Debian.
Anyway, I setup another server, transferred all the files and the databases. Everything worked after the migrations.
Then, after a few weeks, I noticed that the memory constantly touches its limit. So, I tried to optimize MariaDB. After changing a few things in my.cnf, I typed ‘service mysql restart’. It just failed. Looking at the syslog, I noticed that ‘debian-sys-maint’ can not restart the server. OMG!
The issue happened when I dumped all the databases and the corresponding users from the old server. When I imported everything to the new server, the password for ‘debian-sys-maint’ was also reset.
So, the solution is to reset it back to what’s local password. To do that, get the password from /etc/mysql/debian.cnf file, log into MariaDB server as root, then type the following…
GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'the_local_password';
