- Updated: July 7th, 2020 -
Table Of Contents
- MySQL Homebrew Installation
- Useful MySQL Queries for WordPress
- WordPress CLI
- MySQL Backups (Physical and logical databases)
- 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
- Installing MariaDB Server on macOS Using Homebrew
- macOS 10/15 Catalina Apache Setup: MySQL, Xdebug & More…
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
- Installation Command with homebrew:
brew update && brew install mariadb
- Start MySQL:
- Change MySQL Server Password:
sudo /usr/local/bin/mysql_secure_installationYou’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
however, you can also perform the following queries from phpmyadmin or Sequel Pro to interface with.
Useful MySQL Queries 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 – or vice versa. 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.
UPDATE wp_options SET option_value = replace(option_value, '//localhost-url', '//production-url') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = REPLACE (guid, '//localhost-url', '//production-url');
UPDATE wp_posts SET post_content = REPLACE (post_content, '//localhost-url', '//production-url');
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 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 flushAssociated with flushing the permalink structure Read More
wp db exportExports database to current directory: Read More
wp search-replaceQuick way to search and replace strings in the database: Read More
wp db createCreates a new database from the WordPress installation’s wp-config.php file: Read More
- [[Wordpress & MySQL Configure Development Setup]]
- 13 Useful WordPress SQL Queries You Wish You Knew Earlier for more MySQL query tips.
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.
- Ensure the MySQL server is not running
mysqladmin -u root -p shutdown
- Homebrew installs the database directory at:
- 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
- I use the
tarcommand to handle the recursive backup of the
/usr/local/var/mysqlbecause 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/ .
- Once finished, we can restart MySQL:
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
cdinto the directory where you will export a database dump. This is not mandatory but I find it’s useful when starting out.
- 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
- For compression purposes, I find that it’s best to compress the
.sqlfile separately with
tar -zvcf ~/Desktop/compressed-db.tar.gz ~/Desktop/database-backup.sql
- To export a complete backup of all databases just add the
--all-databasesargument like so:
mysqldump -u root -p –all-databases –single-transaction –quick –skip-lock-tables > ~/Desktop/backup-all-dbs-$(date +%F).sql
Some not-so-common MySQL Errors and Fixes
“Error Code 23/24 Too many open files”
- 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.
launchctl limit maxfiles
sudo launchctl limit maxfiles 64000 524288