Top Nav

Archive | MySQL

MySQL Error: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted;

Encountered this error when dropping a table on a database that had recently been migrated from 5.0 to 5.5:

The migration used a “mysqldump –all-databases” so it included the “mysql” database. Apparently the “mysql.proc” table changed between the versions. The fix on the new server is to run:

I didn’t figure this out … instead Daniel Tillett gets the credit.


MySQL – Access Denied On Grants

Interesting problem where we got access denied errors when trying to grant privileges on MySQL server:

But we’re logged in as root so it should work. Turns out the problem is the mysql database needs to be upgraded. Try this:

Notice that it lists the individual privileges instead of saying all privileges. Now upgrade the mysql database by running mysql_upgrade:

Next restart mysql server, login and try showing grants again:

Notice now that it shows “ALL PRIVILEGES” and your grant statements will work.




Convert MyISAM Tables to InnoDB

The command to convert a single table from MyISAM to InnoDB looks like:

This is described suscintly by Major.IO:

Kevin van Zonneveld goes further in his blog:

and provides some scripts to convert all tables instead of doing one at a time.

I like the following:

This variation shows the table name before starting the conversion so I can see progress. The query output is pipped right back in to mysql to execute.


MySQL Monitoring With myq_gadgets

Great set of utilities for monitoring MySQL performance:

Here are the steps to install:

The tools will be in the myq_gadgets-master folder.

There’s no installer so you can just start using the tools immediately.

For instructions see the enclosed README.txt.

Also this presentation demonstrates many of the features:


Sync MySQL Slave From Another Slave On Amazon AWS

I was looking for a procedure to setup a new MySQL replication slave on Amazon AWS without having to do a MySQL dump of the master. Ended up using a couple of AWS features to make this an easy process. Let’s assume that we have a master server (master01) and a slave (slave01). We want to create a new slave (slave02). We’ll do this without taking the master offline and without doing a MySQL dump. This is especially important with large databases.

A couple of notes before we start:

  • On AWS we put MySQL storage on it’s own EBS storage volume. We create a new empty volume, partition, add LVM and then format with xfs. This makes it easy to add additional storage in the future.
  • We prefer to use the innodb-file-per-table setting in my.cnf to force MySQL to use a separate file for each InnoDB table. This keeps all the files that belong to a database in a common folder instead of mixing multiple databases in a single file. Also in may help avoid having very large database files.
  • We’re assuming that you’ve already setup the slave02 EC2 instance. You might do this by cloning the server01 instance.

Now here’s my procedure:

1. Login to AWS and go to EC2 -> Volumes. Find the volume holding the MySQL data. If you’ve not already done so we would recommend adding a Name tag to the volume so that you can easily see which server/function it is associated with. For example you might name it “slave01-mysql”. Right click on the volume and select “Create Snapshot”. A dialog will open where you can name the snapshot … something like “slave01-mysql-snap-1” would be appropriate. Depending on the size of the volume, this snapshot will take some time to complete.

One of the greatest features of EBS snapshots is their incremental nature. Here’s how it’s described by Amazon:

Amazon EBS snapshots are incremental backups, meaning that only the blocks on the device that have changed since your last snapshot will be saved. If you have a device with 100 GBs of data, but only 5 GBs of data has changed since your last snapshot, only the 5 additional GBs of snapshot data will be stored back to Amazon S3.

So the first snapshot we take of a volume has to copy the entire volume but additional snapshots only copy changed blocks. We’re going to use this is next step. At this point we have a snapshot of the server01 slave but it is inconsistent and we don’t have the point-in-time log coordinates that we need to initialize a new slave.

2. Login to slave01, stop replication and apply a read lock as follows:

Make sure that the “flush tables …” completes before proceeding. It may take a few seconds or more if there are locked tables that have to be released before the lock can be completed. At this point the slave server is not fully functional. You’ll want to proceed quickly through the next steps.  Until the next step (3) is complete, do not close the terminal with the “flush tables …” as we need to keep the read lock active.

3. Open a second terminal session to slave01, record log coordinates and shutdown the mysql process as follows:

The slave status will look something like:


The columns that we need to record are:



Take note of these values for later use.

The slave01 database is now stopped and we have the log coordinates from right before the shutdown.

4. Next in AWS, start a new snapshot of the slave01-mysql volume. Name it slave01-mysql-snap-2 or something similar. This snapshot will run much faster since it is only copying blocks that changed since the first snapshot.

5. When the second snapshot is complete, start the mysql process on the slave01 server. Check replication and confirm that it comes back in sync with the master. The slave01 is now back to a functional status.

6. In AWS, right click on the slave01-mysql-snap-2 snapshot and select “Create volume from snapshot”. Name the new volume something like slave02-mysql.

Another great feature of EBS snapshots is “lazy loading” as described here:

New volumes created from existing Amazon S3 snapshots load lazily in the background. This means that once a volume is created from a snapshot, there is no need to wait for all of the data to transfer from Amazon S3 to your Amazon EBS volume before your attached instance can start accessing the volume and all of its data. If your instance accesses a piece of data which hasn’t yet been loaded, the volume will immediately download the requested data from Amazon S3, and then will continue loading the rest of the volume’s data in the background.

This means that we can proceed with the new slave setup without waiting for the new volume to be loaded … instead it will load as needed.

7. In AWS, right click on the newly created volume and attach it to the new slave02 instance.

8. Login to the slave02 instance, confirm that mysql is stopped and mount the new volume.

9. Edit /etc/my.conf and add “skip-slave-start” to the “[mysqld]” section.

10. In the mysql data directory (/var/lib/mysql by default), remove the following files:

  • relay logs  (mysqld-relay-bin.*)

11. Start mysql server

12. Set replication config:

13. Now start replication:

It may take a few minutes or more for replication on the new slave to catch up with the master. This is due to the “lazy load” on the volume created from the snapshot and the need to process the logs from the master.

Once the new slave catches up with the master then the task is complete and you have a new slave. This procedure can work with very large databases. There is no downtime on the master for this process. The existing slave is down for only a short period.