How to add new database to existing master slave replication in mysql 8

I have an existing percona mysql 8.0.26 master-slave configuration in place.
How do I add a new database to be replicated to the slave.
Are there any steps or has anyone created. Please share.

1 Like

On the source MySQL, simply create the database CREATE DATABASE IF NOT EXISTS myDatabase and that will replicate to the replica and be executed. Make sure replication is indeed running by SHOW REPLICA STATUS\G on the replica.

1 Like

I have a percona master-slave 8.0.26
The solution that you say, not sure will work.
Will it need xtrabackup, parameter changes and restore the backup?

1 Like

You asked how to add a new database which will be replicated to the slave. This question implies that you already have replication setup. If you already have replication configured, then you don’t need xtrabackup. You would have already used xtrabackup to create the slave in the first place. Once replication is running, creating a database is simple SQL as I explained above.

1 Like

Aha, probably I put the wrong question then.
Yes agreed I have a replication setup, but now I want to replicate a new database to the slave.
It would be nice if you can help me to get the exact steps for this case.

1 Like

This means that you already have a working master and slave.

Yes, go to the master and CREATE DATABASE myNewDatabase

1 Like

I created new database on the master, but it doesn’t show on the slave.
I updated my.cnf on the slave with replicate-do-db = test.
And restarted the slave mysql service.
But I can’t see the database on slave.
Instead I see an error on slave.
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘d0305618-e25d-11ec-aa88-005056ab077e:4’ at master log mysql-bin.000003, end_log_pos 857.

1 Like

You do not need this. Replication, by default, replicates everything. There is nothing you need to add to my.cnf.

THIS is your problem. Replication was not configured correctly to start with. Right now, your replication is broken. You need to resolve this issue and get replication running properly. Then your database will show up. I suggest you wipe the slave and start over.

1 Like

Could you please suggest a document to follow

1 Like

Could you please suggest a document to built master-slave replication

1 Like

I am trying to build a GTID based master slave replication.

1 Like

Check this out: Search Percona.com

1 Like

Thanks, I have configured now master-slave replication.
And this way I am replicating all the databases to the slave.
However it seems, I have to read about replicate_do_db for adding specific databases to slave and how to add new one to this specific list.
Thanks again.

1 Like

No! As I said above everything replicates by default! You do NOT need to add this parameter each time you make a new database. When you CREATE DATABASE foo that database will AUTOMATICALLY be replicated by default.

replicate_do_db is a parameter to use when you want to restrict which databases are replicated which is a very very advanced way to run MySQL replication.

1 Like

Hi
@matthewb I was also expecting same answer. Just expected reply.

Thanks

1 Like

When you mention everything replicates by default, does that include mysql, sys, information_schema, and performance_schema? I’ve used the replicate_do_db replication filter in the past since it wasn’t clear “what” was going to be replicated on the slave/replica.

1 Like

When you mention everything replicates by default, does that include mysql, sys, information_schema, and performance_schema?

EVERYTHING REPLICATES BY DEFAULT! EVERYTHING! E-V-E-R-Y-T-H-I-N-G!

Every database, every table, every column… everything replicates by default!

1 Like