Xfer Mysql/MariaDB to another Server
From: https://yodiyil.com/tutorials/how-to-migrate-mysql-database
-between-two-ubuntu-servers/
How to migrate MySQL database between two Ubuntu servers
Thursday, July 23rd, 2020James
We at YodiYil believe in trying new ideas and sharing our learning, data
and
results. We proactively document and share our resources in the form of
tutorials for anyone to use or contribute to.
At some point most of us end up in a situation where we need to migrate
large MySQL database from one Ubuntu server to another without having to
locally download the MySQL data files or running complicated migration
tools.
Our GPS Tracking and IOT services have components that utilize MySQL on
Ubuntu servers and quite some time we require to transfer databases from
one
server to another for upgrades or maintenance.
It is a simple and easy 3 step process.
Step 1 - Backup the Source Database
First create a backup of the database on the source server.
mysqldump -u root -p --opt databasename > databasename.sql
Once the dump is completed and ready, transfer the dump to destination
server
Step 2 - Transfer database dump to destination server
We use SFTP to copy the database file between Ubuntu servers. You can also
use SCP instead.
The steps to connect to destination server using sftp and transferring the
dump file is as following.
Navigate to the directory where the mysqldump file is stored.
sftp user@destinationserver (replace this with your destination servers user
name and IP or domain)
sftp> cd target/directory (navigate to the destination servers directory
where you want to copy the file to)
sftp> put databasename.sql (use the file name to transfer single file, or
the command below to copy all files from source directory)
sftp> put *
Step 3 - Import MySQL database dump
Once the data transfer is complete, import the database dump into the new
MySQL server.
mysql -u root -p databasename < /target/directory/databasename.sql