Can I copy the entire /var/lib/mysql folder to a different server? (mysql vs mariadb, different versions)

Question

I used to have a Linux system (LMDE) with MySQL. The system used innodb_file_per_table for most of the databases. (not sure about the version, whatever is the "latest" in LMDE)

I am now on a new system (Manjaro/Arch) with MariaDB.

$ mysql --version
mysql  Ver 15.1 Distrib 10.0.15-MariaDB, for Linux (x86_64) using readline 5.1

The old system had plenty of big databases, that I would really prefer not having to copy with mysqldump. I would much prefer to copy the /var/lib/mysql folder from the old system.

Is this possible?

Solution

Yes, it is possible. (I figured this out while writing the question)

1. Install MariaDB in the new Arch system, verify that it works.
   Do your changes to the /etc/mysql/my.cnf. E.g. innodb_file_per_table.
   (more of this is out of scope for this question)
2. sudo systemctl stop mysqld.
   (You want to stop on both servers, but in my case the other was already off)
3. Rename the /var/lib/mysql folder, e.g. to /var/lib/_mysql.
4. Copy the old /var/lib/mysql folder from the old Debian (LMDE) system.
5. Restore file ownership to mysql:mysql for everything in /var/lib/mysql:
   sudo chown -R mysql:mysql /var/lib/mysql
6. sudo systemctl start mysqld.
   -> Job for mysqld.service failed. See "systemctl status mysqld.service" and "journalctl -xe" for details.
7. sudo systemctl status mysqld.
   -> This shows that the service is running, but some things are going wrong. This is what mysql_upgrade is for.
8. mysql_upgrade -u root -p
   Notes:
   • It is the mysql root password from the old system!
   • I had to remove one leftover folder from /var/lib/mysql due to invalid database name.
   • The file /var/lib/mysql/mysql_upgrade_info needs to be writable.
   • The process can take a while.
9. sudo systemctl restart mysqld.

CAVEAT: Apparently you cannot downgrade to a lower MySQL version. I tried to migrate my databases to MySQL 5.5 (or rather, the corresponding MariaDB), and the server would not start. I had to install MySQL 5.6 instead. (the MariaDB corresponding to MySQL 5.6 is not available on my Linux distro).

Comments

[donquixote]

Funny. Now I need the exact opposite direction, Arch to Mint (based on Ubuntu), and again I am clueless. I get "start: Job failed to start" after replacing the /var/lib/mysql.

[peterh]

It seems impressive, +1. But: simply stopping mysql on both sides, rsync-ing /var/lib/mysql, and then starting isn't enough?

[Richard]

You should also be aware that the server config won't have been copied with this process. While you may wish to accept some of the new defaults, you'll probably want to ensure you also copy any customised variables from /etc/my.cnf as well.

[donquixote]

@Richard: True. In fact I had to do some of this, I just don't remember exactly what I changed. One change was that I enabled innodb_file_per_table.

[donquixote]

@PeterHorvath No, the mysql_upgrade was necessary.

[donquixote]

Just saying, I just did the same from Manjaro back to Mint. It worked, but again it was important to use MySQL 5.6, not MariaDB 5.5.