Thursday, January 20, 2011

TEST: Default Schema

MySQL and it's fork (Percona Server, MariaDB) contains default schema (database) test. Many times, i found it missing from clients mysql-server. It has it's own importance.

1. On master slave replication setup with below configuration parameters.

binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-ignore-db=information_schema

This makes the test schema useful for benchmarking purpose (sysbench, mybench, supersmack) without replicating the benchmark data on slave or new schema can be created if slave benchmark required.

2. On clients server we have to work under restricted conditions and access. In that senario, test schema can be very useful.

3. It's a workspace for users to try and test.

5 comments:

  1. binlog-ignore-db=information_schema
    I don't know what this is for, as it is nonsense.
    Nor does it hurt:)

    ReplyDelete
  2. Never use binlog-ignore-db, it will cause issues with replication. Assume you have a database called production1, then I run the following:

    use production1;
    create table test.a(a1 int);
    drop table test.a;

    there goes your replication..

    ReplyDelete
  3. Sorry, my example was bad.. this is the one you want
    use test;
    create table a(a1 int);
    use production;
    drop table test.a;

    ReplyDelete
  4. Yes, I agree binlog-ignore-db, binlog-do-db can be problematic in many scenario and lead to break replication. replicate-ignore-tab* along with row based replication would solve some of the problem.

    ReplyDelete