MySQL Archives

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 setup MySQL Master/Slave Replication with existing data

by

mysqlThis 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 you’ve got a server that already has data present then here’s how to accomplish the this:

  1. setup your existing MySQL server (with data) as a Master
  2. export all your databases and user accounts
  3. create a slave and import all your data
  4. start replication

I’ve done this several times and always forgot to take some notes – until today. Without further ado, let’s replicate MySQL.

Read more

How to bulk delete posts in WordPress with MySQL

by

mysqlUsually 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. I’ve come across installations with hundreds of thousands of posts which have often been created automatically – and there comes the time when you need to clean things up and prune that massive database.

Deleting 100.000 posts is impossible from within WordPress: it puts a huge load on your server, it takes forever, and besides WordPress will time out after about 200 posts.

The solution: a dedicated SQL query.

Sounds scary I know – let’s go through it step by step in this article.

Read more

How to move very large MySQL Databases

by

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.

Read more

How to change your MySQL User Password

by

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:

Read more

How to deploy HyperDB

by

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.

Read more

Pages: 1 2 3

How to increase the Upload Limit in phpMyAdmin?

by

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 more