For this “cheat-sheet” we will assume that we have an existing database ‘database1‘ that is currently being replicated in a master-slave server setup. To add another database ‘database2‘ to the existing master-slave replication setup, follow this guide:

Create the new database on the master server:

CREATE DATABASE database2;

It it most likely you gave already granted permissions for your replication user, as they are GLOBAL permissions, however if you should wish to create a new replication user, here is the command to do so:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_user'@'slave_server_IP';
FLUSH PRIVILEGES;

Export the new database on the master server:

mysqldump -u root -p --databases database2 --master-data=1 --single-transaction > database2.sql

Transfer the ‘database2.sql‘ file from the master server to the slave server using scp or rsync over the existing SSH tunnel:

scp -P ssh_tunnel_port database2.sql  remote_user@localhost:/path/to/destination/

Import the new database on the slave server:

mysql -u root -p < /path/to/destination/database2.sql

Edit the ‘my.cnf‘ configuration file on the slave server to include the new database in the replication setup:

# Add this line to the [mysqld] section
replicate-do-db=database2

Restart the MariaDB service on the slave server:

sudo systemctl restart mariadb

Check the status of the slave server to ensure it’s replicating both databases correctly:

SHOW SLAVE STATUS\G

Ensure that both ‘database1‘ and ‘database2‘ are replicating correctly by checking the ‘Slave_IO_Running‘ and ‘Slave_SQL_Running‘ values in the output. They should both be set to ‘Yes’. If not, investigate the error messages and take appropriate action to resolve the issues.

If the master server is being actively updated, you can still set up replication without stopping all activity. The key is to use the --single-transaction option with mysqldump when exporting the new database, which ensures a consistent snapshot of the data.

Here’s the command you’ll use:

mysqldump -u root -p --databases database2 --master-data=1 --single-transaction > database2.sql

The --single-transaction option starts a transaction before running the dump, so all data in the output will be consistent up to a single point in time. This way, you don’t need to lock the tables or stop updates on the master server during the export process. Note that the --single-transaction option only works with transactional storage engines like InnoDB; it won’t work for non-transactional engines like MyISAM.

Know that --single-transaction doesn’t lock the entire database during the dump, there might be a small performance impact due to the increased load on the master server. It’s a good idea to monitor the server’s performance during the process and, if needed, schedule the replication setup during periods of low activity.