MySQL Migration Steps
Note: This explanation assumes that you’ve connected to your target server, have created a target database and have granted permission for your target server to connect to your source server.
The code
# mysqldump -h [SOURCE_LOCATION] -u[SOURCE_USERNAME] -p[SOURCE_PASSWORD] [SOURCE_DB_NAME] -K | mysql -u[TARGET_USERNAME] -p[TARGET_PASSWORD] [TARGET_DB_NAME]
ex.
# mysqldump -h 123321.tterwre.us-east-1.rds.amazonaws.com -utestuser -ptestpassword testdbname -K | mysql -uanotheruser -panotherpassword anotherdbname
The explanation
Lets break the command into two pieces.
First piece of the MySQL Migration (source database):
mysqldump -h [SOURCE_LOCATION] -u[SOURCE_USERNAME] -p[SOURCE_PASSWORD] [SOURCE_DB_NAME] -K
mysqldump
This initiates MySQL’s database backup program.-h [SOURCE_LOCATION]
Tells the mysqldump program where on the internet the server containing the source database is.-u[SOURCE_USERNAME]
Tells the mysqldump program what username to use when connecting to the source database.-p[SOURCE_PASSWORD]
Tells the mysqldump program what password to use when connecting to the source database.[SOURCE_DB_NAME]
Tells the mysqldump program what particular database to connect to after connecting to the database server.-K
This is optional, but helps to make large data migrations faster. “This makes loading the dump file faster because the indexes are created after all rows are inserted. This option is effective only for nonunique indexes of MyISAM tables.“
In plain English:
Initiate the MySQL backup program and connect to the source database server located at [SOURCE_LOCATION]. Next, connect to the database named [SOURCE_DB_NAME] with [SOURCE_USERNAME] and [SOURCE_PASSWORD], and copy everything in the database.
Second piece of the MySQL Migration (target database):
mysql -u[TARGET_USERNAME] -p[TARGET_PASSWORD] [TARGET_DB_NAME]
mysql
This initiates the mysql program to access the target database (on the same machine you’re typing commands on).-u[TARGET_USERNAME]
Tells the mysql program what username to use when connecting to the target database.-p[TARGET_PASSWORD]
Tells the mysql program what password to use when connecting to the target database.[TARGET_DB_NAME]
Tells the mysql program what database to use when connecting to the target database server (I didn’t specify a location because MySQL will assume you’re using a local database server if you don’t specify a host location).
In plain English:
Connect to [TARGET_DB_NAME] with its server located on my local machine using [TARGET_USERNAME] and [TARGET_PASSWORD].
Piping the two parts of the MySQL Migration together
There’s one last piece that we haven’t talked about. In Linux, the pipe |
is used to give the output from one command to another command. Because the first and second piece of the MySQL Migration command are separated by a |
, together the commands become…
Initiate the MySQL backup program and connect to the source database server located at [SOURCE_LOCATION]. Next, connect to the database named [SOURCE_DB] with [SOURCE_USERNAME] and [SOURCE_PASSWORD], and copy everything in the database. Take the copy made from the source database, and give it to the target database located on the local machine.
That’s all folks. Questions? Hit me in the comments below.