Facebook tells how it makes its MySQL cluster ‘almost run itself’

Facebook has one of the largest, if not the largest, MySQL installations in the world, and has created a tool to keep that system online with as little human intervention as possible. It’s called MySQL Pool Scanner and, Facebook’s Shlomo Priymak wrote in a post on Monday describing it, it’s designed to automate ”nearly everything a conventional MySQL ...

MySQL Backup and Maintenance

Anyone operating a MySQL database server should have a sound maintenance and backup plan to either eliminate or significantly reduce the risk of data corruption or complete loss of data.  This article is the result of a recent event in which I nearly lost all my data.   MySQL can be effectively maintained using the following ...

Removing old session data form MySQL database

PHP Web applications that use sessions can save data to session files and/or to a database. Session data typically has an expiration timestamp. When session data is saved to a database, the session data is not automatically removed from the database when the expriation time is exceeded. What are some ways to remove this stale ...

Reset lost root password in MySQL

This is a handy procedure that can be used to reset the root password for the MySQL account. I had to use this procedure on a failed installation. It appeared that I had a problem with the bin/install_db command, and it failed to pre-populate the mysql.user table with the correct initial permissions and grants. Without ...

MySQL IF() usage

This article discusses the usage of the MySQL IF() function to display the status of a BOOLEAN or TINYINT(1) column. While working on a tool to track a migration, I needed a means to show the migration status of a collection of tasks. If all the tasks had been migrated, then I needed to summarize ...

MySQL table marked as crashed and should be repaired

If you are getting the following error message: Table ‘jos151_session’ is marked as crashed and should be repaired this means mysql service was terminated while this particular table was open to write some data into it. After mysql start/restart some data is in inconsistent state. To fix the problem you need to log into mysql, ...

How to find the largest tables in MySQL

The largest tables are often the most promising candidates for optimization. Here is a query to list top 5 largest tables in the database: mysql> SELECT TABLE_SCHEMA,    -> TABLE_NAME,    -> (INDEX_LENGTH+DATA_LENGTH)/(1024*1024) AS SIZE_MB,    -> TABLE_ROWS    -> FROM INFORMATION_SCHEMA.TABLES    -> WHERE TABLE_SCHEMA NOT IN ("mysql", "information_schema")    -> ORDER BY SIZE_MB DESC LIMIT 10; +-----------------+----------------------------+----------+------------+ | ...

MySQL Replication

The native replication built into MySQL is a powerful tool to keep an identical copy or copies of a database across multiple servers. MySQL replication is useful if you want to have an exact copy of a live database for backup or application purposes. In the event of a total failure of the primary master ...

How to repair a MySQL database

Occasionally, due to errors or bugs in the MySQL database tables, one can come across various unknown issues. Whenever a database table gets corrupted, the most important thing is to determine the issue behind it. In this tutorial, we will provide step by step explanation on how to repair MySQL database. Let’s get started, there ...

Disk space for MySQL databases is shown as 0MB

Often, when logging into cPanel, databases are shown with zero sizes. To fix this issue, ssh to your server and open cPanel config: # vi /var/cpanel/cpanel.config Find and change this variable from 0 to 1: # disk_usage_include_sqldbs=1 Restart cPanel: # /etc/init.d/cpanel restart Now, databases are included in the used disk size calculations and the correct ...