Category: SQL

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';