This tutorial covers how I migrated a private instance of MySQL from Webfaction to a private instance of MariaDB on Opalstack. If you run into some odd issues or scenarios and have additional suggestions, please let me know and I'm happy to update the op with those variations.
In the following instructions:
port= the port number that the private instance is on. These will most likely be different for the WebFaction/source server and the destination Opalstack server.
user_name = the user name for the private instance. In my case I used 'root'.
db_name = the name of the database you are migrating from the private instance.
Part A: "Dumping" the Database from the WebFaction host server
- Log into the WebFaction source server and start an SSH session.
- Upgrade the version of MySQL (may not be necessary) with the following command:
mysql_upgrade --protocol=tcp -P port -v -u user_name -p
A password prompt will appear. Enter in the password and hit enter.
- Run the MySQL data dump command:
mysqldump --protocol=tcp -P port -u user_name -p db_name > db_name-dump.sql
This will generate the file in whichever directory you are currently in.
- Confirm the data dump by displaying the header information on the dump file:
head -n 5 db_name-dump.sql
- Download the dump file to a folder on your local computer.
Part B: Importing the Database into the Opalstack host server
- Log into the Opalstack destination server and start an SSH session.
- Create a private instance of Maria DB on Opalstack, following steps 1-6 up to the point where you have generated the root user and password. [https://community.opalstack.com/d/91-howto-install-and-run-a-private-mariadb-server-instance-on-opalstack]
Note: I don't know if it's necessary or not, but to avoid any potential issues, I made the password for the root user exactly the same on Opalstack as it was on WebFaction.
- Upload the 'db_name-dump.sql' file to the top directory.
- Log in with mysql credentials:
mysql --protocol=tcp -P port -u user_name -p
- Create the database:
CREATE DATABASE db_name;
Ctrl-D to exit MySQL- Import the Dump File
mysql --protocol=tcp -P port -u user_name -p db_name < db_name-dump.sql
If everything goes right, you won't receive any notifications or error messages. Use whatever tool works for you to verify that the database was fully migrated.
