I understand that abstracting action through a CLI can be daunting for some but nothing can match the efficiency and granular control of CLI commands. In my previous attempts to migrate my database, I was having difficulty using MySQL workbench. After some time pondering the issue, I concluded that the Mysql program in the CLI interface installed on the bitnami AMI would have a much leaner process to transfer the database, even though visually it doesn’t offer much to look at.
to start off I would need to backup my fresh database install on a wordpress AMI into a file to retain the bitnami_wordpress schema. The first command is found in the documentation of the bitnami migration guide; however in using this I received an error, the nonworking command the error are below
##command to backup to a local file
mysqldump -u root -p bitnami_wordpress > backup.sql
##command to restore
mysql -u root -p -D bitnami_wordpress < backup.sql
##ERROR:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databa' at line 1
Looking around online, and comparing information I found to the error, It seems like the syntax error is due to the database having a character set defined as utf8, as we can see in the wp-config.php
/** Database Charset to use in creating database tables. */
define( 'DB_CHARSET', 'utf8' );
To get around this I would need to use these commands instead to build and restore
##Build a backup file with a character offset of utf8
mysqldump -u root -p --default-character-set=utf8 --result-file=database1.backup.sql bitnami_wordpress
##restore the database to a remote server to a schema called bitnami_wordpress, I used MySQL workbench to create a blank schema named bitnami_wordpress
mysql -u admin -p -h wordpress.cyoh3jaboyyj.us-east-1.rds.amazonaws.com -D bitnami_wordpress --default_character_set utf8 < database1.backup.sql
This worked very quickly and smoothly, and also the errors I would see from “corrupted” migration from using mysql workbench were nonexistent, this produced a carbon copy of my original site without any differences.
