Category Archives: MySQL

MySQL is a complex topic – I know enough to get by in the world of WordPress. It’s one of those topics that you never knew you needed until you started diving deeper into storing data.

I like to pronounce it “My Ess Queue Elle”, not “My Sequel”…
and here are some other pointers I’ve picked up along my journey.

How to move very large MySQL Databases

I’ve been shifting some things around and came across several very large WordPress databases for a few projects I was hosting. The largest was 1.3GB or 317MB compressed (!) which obviously poses a problem. Most sites – even those of frequent bloggers – amount to well under 2MB when compressed, but Multi Site or BuddyPress databases can get large quick.

To extract a database from a WordPress site, I’ve always relied on the superb WP DB Backup by Austin Matzko but even that timed out after 170MB.

In this article I’ll talk you through how we can handle this. I’m assuming here that a simple phpMyAdmin export/import isn’t working for you anymore. This is by all means not the only way to go about this task.

Continue reading How to move very large MySQL Databases

How to change your MySQL User Password

Today I had to admit that I did not remember the password for a MySQL user account. Since it’s saved in encrypted form simply reading it out won’t mean I can recover it. A Google search did eventually bring up the correct way of doing it, however it took me quite some time – so here’s how you can do this:

Continue reading How to change your MySQL User Password

How to deploy HyperDB

Ever since I’ve started experimenting with HyperDB I thought that once I’ve got enough servers at my disposal, and if I ever figure out how to setup MySQL replication I’d bring the two together. I believe that day has finally come: HyperDB is now deployed across over 60 sites I’m taking care of.

I want to share with you my configuration and some of the pitfalls I’ve come across – if it helps, great. But mainly these notes are for me to remember how I did it when the next cluster needs to be built.

Continue reading How to deploy HyperDB

How to increase the Upload Limit in phpMyAdmin?

Since phpMyAdmin is written in php, all we need to do is change the file upload limit in the php.ini file. On CentOS and RHEL distributions, this file is located in /etc/php.ini

Find this section:


;;;;;;;;;;;;;;;;
; File Uploads ;
;;;;;;;;;;;;;;;;
; Whether to allow HTTP file uploads.
file_uploads = On
; Temporary directory for HTTP uploaded files (will use system default if not
; specified).
;upload_tmp_dir =
; Maximum allowed size for uploaded files.
upload_max_filesize = 2M

Change the 2M to something bigger, say 100M.

Please note that this is a server wide setting and as such will affect all users on your server.

How to repair MySQL tables from the Command Line

Sometimes your tables crash and your website is unresponsive. You don’t have access to phpMyAdmin to do a repair, because you can’t access Plesk. That sucks!

When you’re out in the field and all that’s at hand is a trusty SSH connection from your mobile, here’s an alternative way to bring your WordPress Sites back to life.

You need the following ingredients:

  • Database Name (I’m using “databasename” here)
  • User Name (I’m using “username” here)
  • Password for the above

All three will be in your wp-admin.php file, use Vi to check if you’ve forgotten them.

Next it’s time to get busy with some MySQL commands I keep forgetting (hence this article):

This will ask you for your password, which you can type but won’t see written out onscreen. Upon success, you’ll be deep in MySQL territory, not the Linux prompt anymore. Next, we’ll select our actual database in use for the site:

Note the importance of the semicolon at the end of the line. Now let’s see which tables are in that database and what they’re called:

show tables;

Excellent! We’re getting there! You’ll see everything that’s in your database, almost like in phpMyAdmin (just less convenient). By default, you should be seeing entries like “wp_options”, “wp_links”, wp_posts” and so forth. The “wp_” is the default prefix for WordPress tables, but depending on your installation this may have changed to something else.

Depending on what else you’re using your database for, there may well be other entries from projects such as PHPList, Drupal, phpBB and so forth. The principles are applicable for every application that’s using MySQL.

You’ve got a whole host of commands at your fingertips now, but the most important ones for a rescue mission like this are probably

The “check table” command seems to repair minor errors, like hosts not having closed connections. It also shows you what’s wrong with a table, or if it’s OK. The “repair” command checks, optimizes and repairs a table – so it’s probably the best one to use.

Unlike phpMyAdmin, you have to issue one command per table; you can’t select all tables and say “repair all” (and if you can, I don’t know about it – in which case, please leave a comment and enlighten us).

For WordPress, that’s 11 tables you need to check – so it’s not a major inconvenience.

Once you’re ready to leave the heady heights of MySQL, type “quit” and you’re back to the command prompt.

Good Luck!

How to restore your database?

The Database is the heart of your WordPress installation. Simply backing up and restoring all your files via FTP is not enough – it only contains the actual WordPress “programme” if you will, your plugins and themes, and all your uploaded media files (like pictures). It does NOT however contain your posts, links and some of the configuration data. That’s where the MySQL Database comes into play.

For any of these articles to make sense, you need to be familiar and have access to phpMyAdmin. Your hosting provider will gladly help you our there.

If you have a fairly small database (up to 5MB), have a look at this:

If you database is significantly larger, you need other means of uploading and importing it. I reccomend the sue of a PHP script, as detailed here:

One sure PHP Script is BigDump:

Obviously, before a RESTORE actually works, you would have had to BACKUP your database (naturally). Perhaps you would like to check out this article to find out how to do this:

http://wordpress-guru.co.uk/archives/how-to-restore-your-databasehow-to-backup-your-database/