Manually Backup or Migrate a WordPress Database Using Command Line

Migrating wordpress data can be a headache since all the data is tucked away into database. These are some helpful command line commands I use to make the process a bit sweeter. The environment I am running for my development site is using Docker on a windows machine. Docker utilizes windows subsystem linux so I already have that setup on my system so most of the commands are for linux. Lets be honest, when it comes to the command line I much prefer linux so WSL comes in clutch. First I need to access the mysql container I have running in docker. If you happen to have the same need see my tutorial on that Accessing A Docker Container from a CLI that doesn’t suck.

Quick Guide

  1. Save the Database to a file

    • mysqldump -u root -p database_name > new_file_name.sql
  2. Find and replace to new file

    • sed 's/oldstring/newstring/g'  new_file_name.sql > new_file_name_edited.sql
  3. Recovering Database from a file

    • mysql -u root -p database_name < new_file_name.sql

 

1. Saving the Database to a file

Now that we are in our CLI in the environment with mysql all we need to do is dump the wordpress database to a sql file. The command is simply:

mysqldump -u root -p database_name > new_file_name.sql

the breakdown:

  • mysqldump the command to dump database data somewhere.
  • -u root is the user you are accessing the database as. -u being the flag for that argument and root is the username I am accessing my database with.
  • -p signals that a password needs to be entered. When the command is submitted it will prompt you for a password. Note that when entering the password nothing is displayed for security purposes. If you so choose you can include the password in the command, so if my password was “password” then I would put -ppassword Note that there is not a space between the flag -p and the value password.
  • database_name this is the name of the database that you wish to use. Obviously change this to whatever the name of your database is.
  • this arrow indicates that the information from the database is going to the file.
  • new_file_name.sql any arbitrary name you want to give the file that will be created, but you might want to keep the .sql part. A path can also preceed the name so the file is created in a desired directory.

Source Reference:

If you don’t remember your mysql username and such info and your trying to dump a wordpress database, you can find the database credentials it in the wp_config.php file.

Since my environment is in a container I want to dump that file to a shared directory within my Docker container so I can access it from outside of the container if I need to. I setup my container to share the directory usr/local/tmp. Considering this I would us:

mysqldump -u root -p database_name > usr/local/tmp/new_file_name.sql

Now we have our database saved to an sql file. Keep it somewhere safe to have as a backup for a rainy day.

2. Find and replace to new file

now we want to run a find and replace operating on our file. this could be to replace an old domain name or update http to https or any number of things you can imagine to replace.

sed 's/oldstring/newstring/g'  new_file_name.sql > new_file_name_edited.sql

the breakdown:

  • sed a stream editor used to perform basic text transformations on an input stream.
  • ‘s/oldstring/newstring/g’  this is the script telling sed how to process out text stream. this could be some_file.sed or a ‘quoted string’ like we did here. Lets break down the script we use: s/  is the substitute command followed by oldstring/ that we want replaced by newstring/ followed by the g flag to globally apply the substitutions, aka all instances instead of just the first.
  • new_file_name.sql this is the input file for the stream of text the edit will be applied to.
  • > new_file_name_edited.sql this is the output file that the steam will be written to.

Just for the sake of completeness, the -i argument could be used to insert the changes to the input document without the need for creating a whole new file. I prefer the above method so I retain the original copy as a backup if need be, but if you desired to do a find and replace directly onto the source file then it would look like:

sed -i 's/oldstring/newstring/g'  new_file_name.sql

Source Reference:

3. Recovering Database from a file

This is basically just a reversal of the dump we did in step one. We run the new_file_name.sql file we previously created on the mysql server that we wish to populate our data to.

mysql -u root -p database_name < new_file_name.sql

the breakdown:

  • See the breakdown for step 1 as it’s virtually the same noting the 2 differences
  • mysql the mysql command is used instead of mysqldump.
  • < the arrow points the other direction indicating the info is going from the file to the database

Source Reference:

Leave a Reply

Your email address will not be published. Required fields are marked *