Mariadb – How to replicate CREATE USER command

mariadbreplication

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

Best Answer

Solved. Before command CREATE USER you have to put:

use mysql;

and then it is replicated to slave server

Related Solutions

Mysql – Master to Slave to Slave Configuration in MySQL

Based on our chat conversation, here is what was discussed

Server1 is Stand Alone
Server2 is a Master
Server3 is a Slave to Server2

This implies that binary logging is enabled in Server2.

To Make Server1 a Master of Server2, perform the following:

STEP 01 : On Server2, add this to /etc/my.cnf

[mysqld]
log-slave-updates

STEP 02 : On Server3, run STOP SLAVE;

STEP 03 : On Server2, run service mysql restart

STEP 04 : On Server3, run START SLAVE;

STEP 06 : On Server1, add this to /etc/my.cnf

[mysqld]
log-bin=mysql-bin

STEP 07 : On Server1, run service mysql restart

STEP 08 : Set Replication From Server1 to Server2

See Clarification about master slave configuration in mysql

OPTIONAL

Once you have MySQL Replication Going From Server1 to Server2 to Server3, your can properly load all data into all three MySQL Instances by doing the following on Server1:

mysqldump -u... -p... --all-databases --routines --triggers > mysqldata.sql
mysql -u... -p... < mysqldata.sql

This will do three(3) things

Repopulate everything into Server1
MySQL Replication will handle populating Server2 from Server1
MySQL Replication will handle populating Server3 from Server2

Since your data is 50MB in total, this should be execute very quickly.

Mysql – Why is MySQL MariaDB GREATEST(timestamp,NOW()) padding appending results with zeros

The MariaDB doku claims for NOW():

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.

So this might be related to the fact that '2016-04-14 15:06:30' may look like a timestamp, but is actually a Varchar.

Try using timestamp literals:

SELECT GREATEST( TIMESTAMP '2016-04-14 15:06:30', NOW() )

Or functional conversion:

SELECT GREATEST( STR_TO_DATE('2016-04-14 15:06:31','%Y-%m-%d %H:%i:%s'), NOW() )

Best Answer

Related Solutions

Mysql – Master to Slave to Slave Configuration in MySQL

OPTIONAL

Mysql – Why is MySQL MariaDB GREATEST(timestamp,NOW()) padding appending results with zeros

Related Question