0

I'm replicating database master to master and everything works ok except for mysql.user table.

When I create user in DB-1, that user is not created om DB-2.

I have this on both servers in cnf

binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema
binlog_ignore_db=ssl

binlog_do_db=mysql

replicate_do_db=mysql
replicate_do_table=mysql.user

Status is showing that replication is ok.

SHOW SLAVE STATUS \G

Thank you for help.

EDIT 1: I have also tried to sync all databases:

binlog_do_db=mysql
binlog_do_db=information_schema
binlog_do_db=performance_schema
binlog_do_db=ssl

replicate_do_db=mysql
replicate_do_db=information_schema
replicate_do_db=performance_schema
replicate_do_db=ssl

EDIT 2:

mysql Ver 15.1 Distrib 10.2.26-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Ubuntu 16.04

Improve this question
11
  • User creation involves not only mysql.user table but replicate_do_table directive(s) excludes all tables not mentioned from the replication. It's not a good idea to replicate the mysql database partially.
    – Kondybas
    Sep 30, 2019 at 18:28
  • @Kondybas tried with removing line replicate_do_table=mysql.user, but user is still not created on other server
    – iWizard
    Sep 30, 2019 at 18:33
  • Have you synchronized both servers before starting slaves?
    – Kondybas
    Sep 30, 2019 at 19:02
  • @Kondybas - yes
    – iWizard
    Sep 30, 2019 at 19:45
  • 1
    Maybe you want to look for syncronous replication (clustering)
    – vegatripy
    Oct 2, 2019 at 11:10

1 Answer 1

Reset to default
1

Solved. Before command CREATE USER you have to put:

use mysql;

and then it is replicated to slave server

Improve this answer
4
  • CREATE USER do not need to be prepended with USE mysql as it works with it anyway. I suspect that your internal databases and tables are broken enough and you have to reinit your server to avoid unforeseen consequences. N.B. Information and performance schemas are internal engines that shouldn't be replicated.
    – Kondybas
    Oct 2, 2019 at 13:55
  • @Kondybas - no. fres Ubuntu 16.04 with fresh database. Slave VM cloned and configured. replication works for all dbs except for "mysql" db which does not throw any error. After I put use mysql it works. found solution here fromdual.com/mysql-replication-with-filtering-is-dangerous
    – iWizard
    Oct 2, 2019 at 14:16
  • @iWizard aren't you afraid of revealing password in plain text in binary log files? Are you using SSL? You don't want to send passwords over an unsecured network. Why do you want to replicate mysql database anyway? I prefer to skip all of the system databases including mysql. This way I can replicate (and I have done this before) mysql to mariadb without worrying about their internal database structures.
    – hpaknia
    Oct 9, 2019 at 4:07
  • I am using encrypted bin and ssl.
    – iWizard
    Oct 9, 2019 at 5:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.