MySQL ‐ Migrate Database Between Servers - shaysalomon12/Data-Engineer GitHub Wiki

Migrate Data

On source server

  1. Login to source server MariaM-01 (10.140.49.18) using credentials: root/<linux_root_password>

  2. Run mysqldump to export schema hakaveret_edu_prod from MariaDB:

$ mysqldump -u root -p <database_name> > /root/<database_name>.sql
Password: <mysql_root_password>
  1. Zip the dump file:
$ gzip <database_name>.sql

On target server

  1. Once the mysqldump is done, login to target server

  2. Copy the zipped file, unzip it and clone it with a different name:

# scp root@<source_ip>:/root/<database_name>.sql.gz .
# gunzip <database_name>.sql.gz
# cp <database_name>.sql <database_name>_utf8mb4.sql
  1. Replace CHARSET and add Collation on the cloned file:
$ sed -i 's/DEFAULT CHARSET=utf8/DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci/' <database_name>_utf8mb4.sql
  1. Connect to local MySQL:
# mysql -uroot -p
Password: <mysql_root_password>

  8. Drop database hakaveret_edu_prod if exists and re-create it:

mysql> drop database <database_name>;
mysql> create database <database_name>;
  1. Load dump file into new hakaveret_edu_prod schema:
mysql> use <database_name>;
mysql> source /home/<database_name>.sql
⚠️ **GitHub.com Fallback** ⚠️