Jump to Navigation

MySQL

Trying to contribute

One of the nice things about Open Source programs is that you can squash the bugs that bite you. Of course, not everyone is able to do that, but still. And though there is no obligation to do so, it is nice if you share your patches with the rest of the world.

Sometimes, though, the sharing part just does not work out. I recently encountered two such cases, where I tried to do the right thing but my patches will probably never be used.

MySQL: Clean shutdown

You want to shutdown MySQL as cleanly as possible, perhaps because you want to create backups or want to do an upgrade.

Assumptions

  • You are running MySQL 5.5 or later

Procedure

If possible, stop all incoming connections (for example, from application servers).

On a slave or replication server, stop the I/O thread (which grabs updates from the master) and wait for the SQL thread to process all:

MySQL: Setting up replication

This is a simple guide to setting up replication between MySQL servers.

Assumptions

  • You are using MySQL 5.5 or later;
  • The master server is currently not being used and is not yet configured to use binlogs;
  • The slave server contains a perfect copy of the master server databases.

Procedure

On the master side, make sure the following statements are included in the /etc/my.cnf file:

MySQL: Identify invalid views

Symptoms

When querying a MySQL view, or when dumping a MySQL database, you get an error including the following text:

View ... references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

A MySQL dump stops at that point.

You want to identify those views that return this error through a database query.

Things I did not know about MySQL

Did you know that MySQL can join at most 61 tables in one query?

Did you know that table data is by default stored as latin1 with the  latin1_swedish_ci collation?

Things I did not know but should have - as I encountered both issues the same day.

Pages

Subscribe to RSS - MySQL


by Dr. Radut