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!!
1
-
What would your reasons be behind wanting to do this?– EEAAOct 19, 2011 at 21:08
Add a comment
|
3 Answers
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 startservice mysql3308 startservice 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/g1,$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.
answered Oct 20, 2011 at 18:32
No, but you can filter the binary log afterwards using mysqlbinlog --database=...
answered Oct 20, 2011 at 0:46
-
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
-
-
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
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.
