Dave Edwards has offered me to write this week's Log Buffer, and I couldn't help but jump at the opportunity. I'll dive straight into it.
OracleI'll start with Oracle, the dust of the Sun acquisition has settled, so maybe it's time to return our attention to the regular issues.

Lets start with Hemant Chitale's Common Error series and his Some Common Errors - 2 - NOLOGGING as a Hint explaining what to expect from NOLOGGING. Kamran Agayev offers us an insight into Hemant's personality with his Exclusive Interview with Hemant K Chitale. My favorite quote is:

Do you refer to the documentation? And how often does …

The mysqldump filter was created with this need in mind. It allows you to remove all DEFINER clauses and eventually replacing them with a better one.
For example:

mysqldump --no-data sakila | dump_filter --delete > …

Last night at the Boston MySQL User Group I presented on how to get a consistent snapshot to build a slave, how to use mk-table-checksum to check for differences between masters and slaves on an ongoing basis, and how to use tools such as mk-table-sync and mysqldump to sync the data if there are any discrepancies.

The slides are online at http://technocation.org/files/doc/slave_sync.pdf.

The video can be watched on youtube at http://www.youtube.com/watch?v=Un0wqYKmbWY or directly in your browser with the embedded player below:

MySQL backup soon becomes an important matter when the database is used in production. The pain-point comes from the fact that while backuping the database is not available to respond to client requests anymore. With mysqldump - the standard tool for performing MySQL backups - and a large database the operation can go over many tenth of minutes if not hours. If I am running my business on line this is simply not acceptable.

The classical approach to workaround this problem is to take advantage of MySQL replication. I set up a master/slave configuration where the slave acts as copy of the master. Then, when needed, I run mysqldump on the slave without any service interruption on the master.

But ZFS snapshosts bring a new straightforward approach that avoids the pain and the complexity of a master/slave replication.

Snapshots are a key feature of ZFS that allows me to save a copy of …

Restoring a single database from a full dump is pretty easy, using the mysql command line client’s --one-database option:

mysql> mysql -u root -p --one-database db_to_restore < fulldump.sql

But what if you don’t want to restore the database, you just want to extract it out of the dump file? Well, that happens to be easy as well, thanks to the magic of sed:

shell> sed -n '/^-- Current Database: `test`/,/^-- Current Database: `/p' fulldump.sql > test.sql

You just need to change “test” to be the name of the database you want extracted.

Sometimes, you want to backup individual databases in MySQL to move to a different server. This part is easy using mysqldump:

shell> mysqldump -u root -p --databases db1 db2 ... > backup.sql

The problem is, what happens when you want to backup the permissions associated with these databases? Well, here are a few queries to help you out.

-- Grab the users with global permissions, 
-- with permissions to the databases you want, 
-- and tables/stored procedures in it.
mysql> SELECT u.* INTO OUTFILE '/tmp/user.txt'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
        LINES TERMINATED BY '\n'
        FROM
                mysql.user u
        WHERE
                u.Select_priv='Y'
        UNION
        SELECT u.*
        FROM
                mysql.user u,
                mysql.db d
        WHERE
                d.Db IN('db1', 'db2', ...) AND
                d.User = u.user
        UNION
        SELECT u.* …

After I wrote about things you need to know about MySQL backups, a customer contacted me and asked me what I know about SecoBackup for MySQL. I see it has a very low cost and Percona has Amazon accounts for testing purposes, so I quickly downloaded s3sql_2.2.0.1-2.01_i386.deb, installed it, configured it, and gave it a whirl.

Since I just want to see what it does to take a backup, I started up a sandbox running from /tmp/12345 and configured it to backup msandbox:msandbox@127.0.0.1 (I shut down my main mysqld on my laptop to make sure it can’t connect to the default instance).

Then I configured a backup set and tried to take a backup. Right away I saw it isn’t full featured enough. It doesn’t let you specify a port to connect to. This …

This is actually old news, but I never thought to file a bug report (until now) or say anything to anyone about it. If you use mysqldump to dump and restore a MySQL table that has INSERT triggers, you can get different data in your restored database than you had when you dumped. [...]

Large databases, long mysqldump times, long waits for globally locked tables. These problems basically never go away when you rely on mysqldump with –all-databases or a list of databases, as it dumps schemas serially. I’m not going to explain serial vs parallel processing here since that’s a larger topic. Suffice to say that in these days of multi-core / multi-cpu servers we only make use of one processor’s core when we serially export databases using mysqldump. So, I have a new script that attempts to alleviate those issues and now I need testers to provide feedback/improvements.

In order to keep some sanity when dealing with hundreds of database servers, the script takes care of the following:

1. low global locking time requirements: solved by parallel tasks / forked processes
2. backup file checking: with mysqldump files; it checks for “–Dump completed” at the end of the sql file …

After a period of inactivity I was hacking back on a Drupal project, I had taken a mysql dump from a production platform and imported into my local dev setup, just to have some realistic data.

All of a sudden some forms started failing with the following error:


user warning: There is no 'user'@'nonlocalhost registered query: insert into blah (stuff,morestuff) values (x,y) in /var/vhost/drupal-tree/includes/database.mysql.inc on line 172.


My Drupal data connection was correct and working for selects etc.. only a limited set of inserts failed.

After some debugging I realised that the error was not Drupal related, running the same query on my MySQL console gave the same error.


ERROR 1449 (HY000): There is no 'user'@'nonlocalhost' registered


The error came from a trigger on the table I was inserting data into that had been created on the …