What is the best and fastest way to move mysql when changing servers?

Currently I do it through phpmyadmin, I backup the db in the old server, open a new user in the new server, create a new database and upload/restore the backup.

(I use something like this to speed up the database restore with mysql terminal.)

mysql -u username -p database < /var/www/html/mysql_dump/nomedb.sql

A long and tedious operation... can it be done in a better and faster way to copy database and mysql users to the new server?

I have ssh access and server root, ubuntu 20.04 server, apache, php8.1

----------update---------

Just for completeness, I regularly use this script to make mysql backups, and I download everything with rsync to a local disk, so backing up databases is not a problem. The tedious part is restoring them one by one... (Sorry, the description is in italian, but it's almost all code...)

5

1 Answer

“Best” is rather subjective, leading to opinions, but this is generally the method I follow when setting to a new MySQL database:

On the new server …

1. Create all of the databases

   CREATE DATABASE `{dbname}` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
   CREATE DATABASE …
   CREATE DATABASE … 

2. Create all of the user accounts and set privileges:

   CREATE USER '{user}'@'localhost' IDENTIFIED WITH mysql_native_password BY 'superSecretPassword!123';
   GRANT ALL ON `{dbname}`.* TO '{user}'@'localhost';
   CREATE USER …
   GRANT ALL …

On the source server …

1. Export the databases one by one:

   sudo mysqldump {dbname} -n --routines --triggers > dbname.sql

   ^{Note: You do not need to use the command line for this if you prefer phpMyAdmin or another tool.}

   -n ⇢ Suppress the CREATE DATABASE ... IF EXISTS statement

   --routines ⇢ Dump stored routines (functions and procedures)

   --triggers ⇢ Dump triggers for each dumped table

2. Package and compress the database dumps for easier transport:

   sudo tar -czvf dbs.tar.gz *.sql

3. Copy/Send the compressed file to the new server

On the new server …

1. Extract the databases from the compressed file:

   tar -xvf dbs.tar.sql

2. Import the databases:

   sudo mysql dbname < dbname.sql
   sudo mysql dbname …

3. (Optional) Restart MySQL to clear the buffers:

   sudo service mysql restart

4. Enjoy a short break

There are other ways to do this, but I follow this process as it allows me to avoid the problems of overwriting differing versions of the mysql system table (if the new server is not the exact same version) while also ensuring all data, accounts, permissions, and functions/procedures/triggers are in place.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy