MySQL – a Database we can’t live without

MySQL LogoMySQL 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.

MySQL Replication Troubleshooting

Sometimes things don’t work out with replication. When I first started experimenting with it I thought this was a “setup and forget about it” kind of job. Experience has shown though that you have to regularly triple check and see if things may have broken (despite a good and once working setup). Let’s take a [...]

Read the full article →

How to setup MySQL Master/Slave Replication with existing data

This is a step-by-step guide on how to replicate an existing MySQL server. The server is live and contains data and needs a constant backup companion. Many tutorials focus on how to setup replication when no data is present on the system. That’s an ideal solution if you’re building a new setup, but in case [...]

Read the full article →

How to bulk delete posts in WordPress with MySQL

Usually deleting several posts at once is not a problem thanks to the bulk delete options in WordPress. Those queries however rely on a single delete each, initiated by PHP loop. That’s fine if you’re deleting up to about 100 posts at a time. But it’s not when you have thousands of posts to delete. [...]

Read the full article →

What is the default MySQL password for root in MAMP?

It’s root. This is not the same on a vanilla MySQL install where the root user is not secured by a password by default – however under MAMP it is. So on a fresh MySQL install the credentials are User: root Password: (nothing) Under MAMP (and presumably MAMP Pro) they are User: root Password: root [...]

Read the full article →

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 [...]

Read the full article →

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 [...]

Read the full article →

How to install MySQL on CentOS

This is a step-by-step guide of what you need to do in order to install and setup MySQL on a new server. We’ll prepare a fresh CentOS 6 system (64 bit) for use as a database server. All you need is access to an SSH client and your server root credentials.

Read the full article →

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 [...]

Read the full article →

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 [...]

Read the full article →

Where are the MySQL data files on CentOS?

They’re located in /var/lib/mysql/ Each database has a subfolder here. You’ll also find the MySQL log files here.

Read the full article →

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 [...]

Read the full article →

How to move WordPress from one server to another

It’s not as difficult as one might think, as this article explains: http://codex.wordpress.org/Moving_WordPress If however this means not a lot to you, or you have tried and it’s still not working, you should check out the following topics: How to backup your Database How to restore your Database Hope this helps

Read the full article →