Is it possible with MySQL to have a database-specific Master Binary Log setup?

Question

Is it possible for me to have a unique binary log per MySQL database on my MySQL server? I've done some googling and can't seem to find any solutions. Surely there's a way!!

Answer 1

Acutally, there is a way, but it takes some work if you really require it.

I already posted something about 6 months ago describing how to set up a Star Topology.

You can use the same concept. Here is the scenario

For this example, you have three databases: members, dbblogs, reports.

Step 01) Setup Binary Logging on the Master

If binary logging in not enabled, you can enable it

[mysqld]
log-bin=mysql-bin

Restart mysql after adding log-bin=mysql-bin to /etc/my.cnf

Step 02) Setup MySQL Instance datadir for Each Specfic Database

mkdir /database_binlogs
mkdir /database_binlogs/members
mkdir /database_binlogs/dbblogs
mkdir /database_binlogs/reports
mkdir /database_binlogs/members/mysql
mkdir /database_binlogs/dbblogs/mysql
mkdir /database_binlogs/reports/mysql
cp /var/lib/mysql/mysql/* /database_binlogs/members/mysql
cp /var/lib/mysql/mysql/* /database_binlogs/dbblogs/mysql
cp /var/lib/mysql/mysql/* /database_binlogs/reports/mysql
chown -R mysql:mysql /database_binlogs

Step 03) Create a MySQL Service for Each Database

Make sure each config file has the following:

/etc/my3307.cnf

[mysqld]
server-id=9993307
datadir=/database_binlogs/members
replicate-do-db=members
log-bin=mysql-bin
log-slave-updates

/etc/my3308.cnf

[mysqld]
server-id=9993308
datadir=/database_binlogs/dbblogs
replicate-do-db=dbblogs
log-bin=mysql-bin
log-slave-updates

/etc/my3309.cnf

[mysqld]
server-id=9993309
datadir=/database_binlogs/reports
replicate-do-db=reports
log-bin=mysql-bin
log-slave-updates

Step 04) Start the three services

• service mysql3307 start
• service mysql3308 start
• service mysql3309 start

Step 05) Perform mysqldump of database schema only from the Master, no data

Do not include mysql schema !!!

mysqldump -uroot -p --master-data=1 --routines --triggers --no-data --databases members dbblogs reports > /root/MySQLSchema.sql
echo "RESET MASTER; START SLAVE;" >> /root/MySQLSchema.sql

Step 06) Edit /root/MySQLSchema.sql to use only the BLACKHOLE storage engine

In vi, run these two replaces in the file /root/MySQLSchema.sql:

• 1,$s/ENGINE=MyISAM/ENGINE=BLACKHOLE/g
• 1,$s/ENGINE=InnoDB/ENGINE=BLACKHOLE/g

Step 07) Create a Replication User on the Master

GRANT SELECT,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'replicant'@'127.0.0.1' IDENTIFIED BY 'replpass';

Step 08) Setup the 3 MySQL Instances as Slaves to Master using

CHANGE MASTER TO
MASTER_HOST='127.0.0.1',
MASTER_PORT=3306,
MASTER_USER='replicant',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1;

Step 09) Load MySQL Schema into the three MySQL Instances

mysql -uroot -p -h127.0.0.1 -P3307 < /root/MySQLSchema.sql
mysql -uroot -p -h127.0.0.1 -P3308 < /root/MySQLSchema.sql
mysql -uroot -p -h127.0.0.1 -P3309 < /root/MySQLSchema.sql

Binary logs are Cleared in the three Instances and Replication is started

From there, Each DB instance will record only its designated database's binlog entries

Give it a Try !!!

UPDATE 2011-10-20 15:37 EDT

Please keep in mind that the three slaves will contain only binlogs and not data.

Answer 2

No. The log_bin= is a global MySQL variable. You can turn logging on or off per database with the binlog-do-db= and binlog-ignore-db= options, but log file location is global per server.

Answer 3

No, but you can filter the binary log afterwards using mysqlbinlog --database=...