Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!!

share|improve this question
    
What would your reasons be behind wanting to do this? –  EEAA Oct 19 '11 at 21:08

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.