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

MySQL & MariaDB Percona Server for MySQL 8.0
1

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
2

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
3

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
4

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
5

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
6

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

Yes, go to the master and CREATE DATABASE myNewDatabase

1 Like
7

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
8

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
9

Could you please suggest a document to follow

1 Like
10

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

1 Like
11

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

1 Like
12

Check this out: Search Percona.com

1 Like
13

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
14

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
15

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

Thanks

1 Like
16

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
17

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
18

Yes, all databases are replicated by default, even the new ones. However, when in my.cnf we define which databases are to be replicated (parameter ‘binlog_do_db’), then creating a new database on the master will not create it on the slave. And this is what the author of this post asked for sure. I have the same problem to solve and I intend to solve it in the next few days.

1 Like
19

Correct! Because setting binlog_do_db overrides the default behavior. Stop using this setting if you want to replicate all databases, current and future.

1 Like