WPKG Blog

Sometimes, you will see MySQL setups where only one database is being replicated. How to add a second database, so that it’s also replicated?

In my.cnf on both servers, add a second database:

replicate-do-db          = first_db
replicate-do-db          = second_db
binlog-do-db            = first_db
binlog-do-db            = second_db

Connect to mysql on both servers and add a new database:


# mysql -p
mysql> CREATE DATABASE second_db;


Now, restart mysql on both servers and verify that replication works (be careful when restarting if there are any writes to the database; i.e. shutdown the secondary master first, then the primary master – depending on your environment):


mysql> use second_db;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> CREATE TABLE example (
-> id INT,
-> data VARCHAR(100)
-> );
Query OK, 0 rows affected (0.13 sec)
mysql> drop table example;
Query OK, 0 rows affected (0.02 sec)
mysql>


After creating/dropping the example table on one server, verify it’s happening on the second server as well:


mysql> show tables;
+---------------------+
| Tables_in_second_db |
+---------------------+
| example |
+---------------------+
1 row in set (0.00 sec)
mysql> show tables;
Empty set (0.01 sec)


Don’t forget to add a user with proper privileges, i.e.:


mysql> CREATE USER someuser IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.23 sec)
mysql> GRANT ALL ON second_db.* TO 'someuser'@'%';
Query OK, 0 rows affected (0.00 sec)