Categories
Wordpress Development

MySQL Command Line Basics

One of the most underrated things I’ve learned through my Wordpress journey is how to understand, work with, and manipulate MySQL databases to facilitate a wide range of useful actions. When working in a localhost environment, MySQL is especially useful for migrating databases over to a public server.

Table Of Contents

  1. MySQL Homebrew Installation
  2. MySQL Backups (Physical and logical databases)
  3. MySQL Useful Commands for WordPress
  4. WordPress CLI
  5. Some not-so-common MySQL Errors and Fixes

Getting good with MySQL is like getting good with anything, it takes time and patience. With the vastness of information about MySQL it can get pretty heady pretty quickly. It can even be hard to know where to begin. This guide aims to introduce you to MySQL basics through the command line interface (cli), in the context of WordPress and based in real world examples. I will focus on using Homebrew with the OS X for a localhost development stack but the information can be utilized across other environments.

If you’re looking for further information on a do-it-yourself solution for rolling your own development stack (Apache, MySQL, PHP, etc) check out this great Grav blog article

MySQL Homebrew Installation

Recommended Resource(s):

Let’s first walk through a basic homebrew installation of the open source relational database MariaDB Server. It’s a replacement for the MySQL installation on the Mac and allows us to update and upgrade with ease. (If you don’t have Homebrew installed, you can get started with Part 1 of the Homebrew installation guide from Grav’s article).

MariaDB Server is available as a Homebrew “bottle”, a pre-compiled package. This means you can install it without having to build from source yourself. This saves time – Installing MariaDB Server on macOS Using Homebrew

  1. Installation Command with homebrew: brew update && brew install mariadb
  2. Start MySQL: mysql.server start
  3. Change MySQL Server Password: sudo /usr/local/bin/mysql_secure_installation You’ll need to answer the questions that are pertinent to your environmental setup. That’s it, you should be able to have mysql start up and shut down accordingly for your development environment.

When you are ready you can now login to your mysql cli with:

mysql -u root

MySQL Backups (Physical and logical databases)

There are two different types of backups which have both are useful for different situations.

1. Physical backup

The physical backup solution is handy when you are in a situation where you cannot actually run your MySQL server to access the databases to export individually or if the host system is inaccessible. You can still access the database(s) to copy out directly.

  1. Ensure the MySQL server is not running mysqladmin -u root -p shutdown
  2. Homebrew installs the database directory at:
    /usr/local/var/mysql
  3. Create a directory to house the physical backup location. This guide will use the ~/Desktop for demonstration purposes but you should alter this to meet your needs.
    mkdir -p ~/Desktop/mariadb-bak
  4. I use the tar command to handle the recursive backup of the /usr/local/var/mysql because I can compress it at the same time with gzip. That command structure will look like this on the Mac:
    tar -zvcf ~/Desktop/[backup-name]-$(date +%F).tar.gz -C /usr/local/var/mysql/ .
  5. Once finished, we can restart MySQL:
    mysql.server start

Breaking down the earlier tar command, we’re basically stating we want to create a compressed zip file from a specified directory and we want to run it in verbose mode so that we can see what is going on while it is creating the zip file. We are also adding a date stamp to the zip file for organizational purposes.

Make sure to understand the syntax as that very last . ensures you get everything in the specified directory including any hidden files.

2. Logical Backups

This is the usual method I use for my database backups. It ensures that I have copies of specific databases at certain points (e.g. just before a round of updates) that work like a snapshot for each WordPress installation. We’ll be using the mysqldump command from the cli to export a single database and learn how to dump all databases to understand a little more about the syntax. This does not require us to log into the mysql cli, and instead is its own command bundled in with the MySQL installation.

The basic structure will look like this:
mysqldump -u [username] -p [databaseName] > [filename]-$(date +%F).sql

  1. cd into the directory where you will export a database dump. This is not mandatory but I find it’s useful when starting out.
  2. Here’s a sample command to export a given database to your desktop without compression:
    mysqldump -u root -p your_database_name --single-transaction --quick --skip-lock-tables > ~/Desktop/backup-db-$(date +%F).sql
  3. For compression purposes, I find that it’s best to compress the .sql file separately with tar like so:
    tar -zvcf ~/Desktop/compressed-db.tar.gz ~/Desktop/database-backup.sql
  4. To export a complete backup of all databases just add the --all-databases argument like so:
    mysqldump -u root -p –all-databases –single-transaction –quick –skip-lock-tables > ~/Desktop/backup-all-dbs-$(date +%F).sql

MySQL Useful Commands for WordPress

Swapping Out Localhost URLs for Production Site URLs

The next 4 commands are for going through and replacing one url with another. Typically this would come about when you are migrating your web site to a production server. Each site is different and has differing needs but I find these are handy to have around when you need them. This effectively goes through the different tables of your database looking for anything that matches a specific url scheme (e.g. https://my-site.test) and replaces it with the production site’s url scheme (e.g. https://production-site.com). This goes through your db options, your posts/pages as well as the postmeta fields.

  1. UPDATE wp_options SET option_value = replace(option_value, '//localhost-url', '//production-url') WHERE option_name = 'home' OR option_name = 'siteurl';
  2. UPDATE wp_posts SET guid = REPLACE (guid, '//localhost-url', '//production-url');
  3. UPDATE wp_posts SET post_content = REPLACE (post_content, '//localhost-url', '//production-url');
  4. UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, '//localhost-url','//production-url');

Note that the above works wonderfully in testing although you will still need to login and (currently) manually update your permalinks and activate the correct child theme since it won’t technically exist.

Conversely, use the wp-cli tool (see more below) and flush the permalinks with wp rewrite flush and make sure your .htaccess file is set accordingly for rewrite rules to the correct URLs.

WordPress CLI

WordPress cli is also very helpful with many cli commands for handling WordPress specifically. In fact, the WordPress cli also includes the option for wp db which is great for simple activities like exporting a database. For a full list of the WP-CLI check out the WP-CLI Commands

  • wp rewrite flush Associated with flushing the permalink structure Read More
  • wp db export Exports database to current directory: Read More
  • wp search-replace Quick way to search and replace strings in the database: Read More
  • wp db create Creates a new database from the WordPress installation’s wp-config.php file: Read More

Further Reading:

Some not-so-common MySQL Errors and Fixes

“Error Code 23/24 Too many open files”

  1. Be sure to check out: Increase Open File Descriptor Limits from Become the Solution where the author points out the below temporary fix, however also integrates the more permanent solution, Alternatively, I’ve got a real world issue you can find here [[Troubleshooting bacwtt.dev to bacwtt.test]] as well. For a temp solution you can find out how much your current maximum open files limit in your cli and then increase your soft and hard limits respectively on OS X. This will change the maximum open files to 64000 with a maximum hard limit of 524288. This won’t take effect until you log out or reboot your Mac.
  2. launchctl limit maxfiles
  3. sudo launchctl limit maxfiles 64000 524288

Leave a Reply

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