MySQL commands and SQL queries to help you manage your MySQL databases and tables.
There is some bad advice going around regarding updating URLs in the WordPress database when migrating your WordPress site from HTTP to HTTPS. There are tons of examples of MySQL queries that do a “find and replace” of the URLs in your WordPress database, and they’ll change the URLs from HTTP to HTTPS. The bad … Read more →
These SQL queries let you change the value for “siteurl” option in the wp_options table in the WordPress database for all sites in a WP Multisite network. So, each line changes the siteurl in the wp_options table for one site in the network. The first line changes it for the main site in your Multisite … Read more →
These are my most often-used MySQL commands and queries. Connect to MySQL on the command line (replace USERNAME and YOURPASSWORD with your own): MySQL Databases List all databases on the command line: Create a database on the command line: Replace database_name, above, with your name of choice. Delete a database: Replace database_name, above, with the … Read more →
These are the terminal commands to reset your MySQL password for your “root” user. This is useful if you forgot your MySQL password on your local LAMP server. This is a fix for errors like these: ERROR 1045: Access denied for user: ‘root@localhost’ (Using password: NO) ERROR 1045: Access denied for user: ‘root@localhost’ (Using password: … Read more →
These are SQL queries to change the home url in the wp_options table in the WordPress database. For a single site, just use the first line. You can also change it for all sites in a Multisite network. This example includes lines to change 8 sites in your multisite network. For each additional site, add … Read more →
This is useful if you created custom menu links for your WordPress menu, in the WordPress admin –> “Appearance” –> “Menus”, and now you need to change the link URLs from “http” to “https” because you are now using an SSL certificate. These are the MySQL statements that let you make those changes directly in … Read more →
These are MySQL statements to bulk delete all WordPress attachments of a specified modified date in the WordPress database. Both steps are needed to remove not only the attachments, but also the postmeta which is attached to the parent post. This is a clean way to get rid of attachments without leaving behind dangling postmeta. … Read more →
Updated to use MySQL transactions, prepared SQL statments, and bound parameters. This lets you insert data from a PHP array into your MySQL database table. It will insert multiple rows into the table, where each row will take values from one array element. Before inserting, you must prepare your data into an array consisting of … Read more →
This is for you if you are using the WordPress Comment Notifier Plugin by Satollo. The purpose of this is to automatically subscribe your existing commentators (which are people who have already made comments to your blog). You want to add these people as subscribers to this Comment Notifier Plugin, or they get left out … Read more →
Insert a field or column right after some existing field in an existing table in a MySQL database. Replace ‘feildname’ with the new field name which you want to add. Replace ‘existingfield’ with your existing field name (the new field will be inserted after this existing field).