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

  • 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 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 at 18:33
  • Have you synchronized both servers before starting slaves? – Kondybas Sep 30 at 19:02
  • @Kondybas - yes – iWizard Sep 30 at 19:45
  • 1
    Maybe you want to look for syncronous replication (clustering) – vegatripy Oct 2 at 11:10
1

Solved. Before command CREATE USER you have to put:

use mysql;

and then it is replicated to slave server

  • 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 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 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 at 4:07
  • I am using encrypted bin and ssl. – iWizard Oct 9 at 5:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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