MySQL and MariaDB power most of the websites I host, from WordPress blogs to custom PHP and ASP.NET applications. This page bundles my MySQL guides: InnoDB tuning, table maintenance and backups, connecting from PHP and .NET, and keeping WordPress and Joomla databases lean. For PHP itself, see my PHP guides.
InnoDB and server performance
- MySQL InnoDB performance improvement: InnoDB buffer pool instances - Updated!: tune
innodb_buffer_pool_sizeand buffer pool instances for heavy InnoDB workloads. - Convert MySQL MyISAM tables to InnoDB: move from the old MyISAM engine to the faster InnoDB.
- MySQL database optimization with indices: speed up lookups with the right indexes.
- Turn off swap: keep Linux from swapping and slowing down your database server.
- How to flush MySQL query cache from time to time: defragment the query cache manually or with a cronjob.
Table maintenance, backups and restore
- Check, repair and optimize MySQL tables with mysqlcheck: check, repair, optimize and analyze tables from the command line.
- Optimize all MySQL tables with PHP/MySQLi multi_query: optimize every table in one statement.
- Restore single MySQL table from a full mysqldump backup file: extract one table from a full
mysqldumpwith Bash. - How to restore a deleted Open-Xchange context?: restore an Open-Xchange context from MySQL backups.
- Calculate MySQL database size with PHP (off the old shelf): calculate database size with PHP or
information_schema.
Connect to MySQL from PHP and .NET
- How to use SSL in PHP Data Objects (PDO) mysql: encrypt your PDO connection with SSL.
- Convert PHP ext/mysql to MySQLi: convert old ext/mysql code to MySQLi, function by function.
- Connect to multiple MySQL servers in a for and foreach loop: check and connect to several servers in a loop.
- Test MySQL database connectivity with MySql.Data.MySqlClient: an ASP.NET test script for MySql.Data.MySqlClient.
- MySQL Connector/ODBC C# test script: test your ODBC connection from C#.
- How to fix System.Collections.Generic.KeyNotFoundException "The given key was not present in the dictionary" Exception with MySQL Connector/NET and utf8mb4 character set: fix the KeyNotFoundException with Connector/NET and utf8mb4.
- MySQL Connector/NET and Entity Framework: let Connector/NET and Entity Framework work together.
- MySQL Connector/NET 6.5 in partial trust: run Connector/NET 6.5 in partial (medium) trust.
Cache MySQL query results
- Cache MySQL query results with PHP WinCache: store query results in RAM with the WinCache user cache.
- MySQL query caching using PHP/Zend_Cache: cache query results to disk with Zend_Cache.
WordPress and Joomla databases
- Wp_options table autoload micro-optimization: keep the autoloaded options in
wp_optionssmall. - Optimize WordPress MySQL tables through Cron, behind the scenes: schedule table optimization with WP-Cron.
- Clean-up spam comments and meta data: delete spam comments and their meta data.
- How to string replace on all WordPress posts in MySQL: bulk edit post content with
REPLACE()andREGEXP_REPLACE(). - Query all WordPress posts in MySQL not having a Yoast SEO meta description: find posts without a Yoast meta description.
- How to Fix Yoast SEO for WordPress DeleteDuplicateIndexables MySQL error: a fix for a slow Yoast DELETE statement on large databases.
- WordPress CMS admin password reset: reset a lost admin password with a MySQL query.
- WordPress HyperDB is an advanced database class that supports replication: replication and partitioning for WordPress.
- Disable Joomla Contacts component (com_contact) in MySQL / phpMyAdmin: disable spam-abused contact forms in the database.
MySQL security
- MySQL sleep() attacks: how
sleep()injection hangs your server, and how to clean it up. - MySQL string comparison functions for MD5 and SHA1 hashes: let MySQL calculate and compare MD5 and SHA1 hashes.