0

I want to migrate a MySQL/MariaDB database from my local box, where I use it for development, to the server that will actually use it in "production".

I'm totally new, so please bear with me. I'm asking for help because I don't even know where/what to look for what I need.

In general, this database, as well as the whole "project", is very simple, and basically serves educational purposes. The database has just one table and access to it has only a user (different than root). After being migrated to the remote server, I'm planning to access it (basically for monitoring) through phpMyAdmin, which will be allowed only to localhost (server's localhost) and thus I'll do that by redirecting traffic through ssh to my localhost.

Both my local box and the server run MariaDB under /var/lib/mysql. My question is: is it as simple as securely copying local database directory to the corresponding directory on the server? If not, how should I proceed?

Note: I only need to copy one table or database.

Note: I am running different versions.

Thanks for your patience.

Rick James
  • 2,463
  • 1
  • 6
  • 13
nullgeppetto
  • 113
  • 5

2 Answers2

1

Copying the entire directory will work if the versions of MariaDB are the same on both machines.

As for "securely copying", that scp is reasonably secure. rsync may have a way built-in. Or the combination should work.

It is not practical to move just one table or just one database. by fiddling with files. Instead, you must use some form of "dump" (mysqldump, etc) to copy just the desired table(s):

source$  mysqldump ... | scp (from stdin to host "dest", file "dump.sql")
dest$  mysql < dump.sql

If it is big, you could throw zip and unzip into the pipes.

The full copy should be done with mysqld not running. The "dump" approach, of course, requires it to be running.

The dump approach avoids virtually all issues with differing versions.

Rick James
  • 2,463
  • 1
  • 6
  • 13
  • Thanks for your response. I'm not sure what do you mean by the entire directory. I was probably misleading above. I was talking about the directory of the specific database (say /var/lib/mysql/testdb), not the entire mysql directory (/var/lib/mysql). Would that cause a problem in case of different MariaDB versions between local and server? – nullgeppetto Sep 29 '20 at 22:48
  • 1
    @nullgeppetto - COPYING ONE TABLE WILL FAIL! I edited your question and my answer. – Rick James Sep 29 '20 at 22:56
1

Theres more to this than just rsync it over.

Unless you have a good reason to do so, do not touch the binary files in /var/lib. MySQL strives to keep binary compatibility and necessary updates as resilient as possible, so its certainly possible with identical versions or upgrades.

But if you can read any SQL at all, this is an excellent moment to verify what exactly you are moving, so use a method that makes that step easy. Create a logical backup using mysqldump in the source machine, review that file for leftovers or mistakes and restore that on the target machine.

anx
  • 8,963
  • 5
  • 24
  • 48