MySQL 5.1 – Fixing Binlog_Do_DB Issue in Master Configuration

MySQLmysql-5.1windows

I have followed all the rules for replication. The steps are
I have changed the my.cnf file. My db name is new_db.

server-id   = 1
log_bin = D:/xampp/mysql/mysql-bin.log
binlog_do_db = new_db

Grant the replication for slave

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
USE new_db;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

After that the result is

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      481 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Binlog_Do_DB column is empty. new_db should be there.

I don't understand what I missed. Please help to solve the problem.

Best Answer

In Windows, you would need to modify the my.ini file and not the my.cnf file

Locate you my.ini file

If MySQL is being run as a service

  1. Enter "services.msc" on the Start menu search box.

  2. Find MySQL service under Name column, for example, MySQL56.

  3. Right click on MySQL service, and select Properties menu.

  4. Look for "Path To Executable" under General tab, and there is your .ini file, for instance, "C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MYSQL56

Otherwise the my.ini file is probably in a sub-directory of D:\xampp\mysql\

Once you've added in the binlog_do_db parameter, be sure to restart the MySQL service.

Edit by RolandoMySQLDBA

You can restart mysql from services.msc window by right clicking and choosing or you can login from DOS CommandLine as Administrator as run

C:\> net stop mysql
C:\> net start mysql

Also, make sure your settings are under the [mysqld] group header in my.ini