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!!
What would your reasons be behind wanting to do this?– EEAAOct 19, 2011 at 21:08
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
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:
[mysqld] server-id=9993307 datadir=/database_binlogs/members replicate-do-db=members log-bin=mysql-bin log-slave-updates
[mysqld] server-id=9993308 datadir=/database_binlogs/dbblogs replicate-do-db=dbblogs log-bin=mysql-bin log-slave-updates
[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:
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.
No, but you can filter the binary log afterwards using
1Beware though, as filtering will probably not give what you expect ( as stated by dev.mysql.com/doc/refman/5.7/en/… ) While db_name is the default database, statements are output whether they modify tables in db_name or a different database. Unless db_name is selected as the default database, statements are not output, even if they modify tables in db_name. Aug 29, 2017 at 13:37
@Pierre-AlexisdeSolminihac Beware of what? Aug 29, 2017 at 13:38
the caveat @pierre-alexis-de-solminihac was pointing out by linking the relevant section in the docs is: in statement-,row-level or "mixed" based binlogs, you can get different outputs depending on how the queries were issued. Users deciding to use the option should read it first and know the kind of queries their application issues.– wbobDec 19, 2020 at 19:34
log_bin= is a global MySQL variable. You can turn logging on or off per database with the
binlog-ignore-db= options, but log file location is global per server.