Home / ubuntu / Questions / 1394397
In Process
alebal
Asked: 2022-02-23 14:35:14 +0800 CST

Server migration: mysql

  • 772

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... https://www.alebalweb-blog.com/66-backup-automatico-mysql-su-server-ubuntu.html (Sorry, the description is in italian, but it's almost all code...)

mysql

1 Answers

  1. “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.

    • 1