Galera cluster and mysql restore issue

[trupti mali]

Hi, I am facing a strange issue here. I have setup a galera cluster with 3 nodes. As long as I am creating database/tables/ adding data directly to any of the galera nodes, it works fine. But when I import a mysqldump created sql from another database -using command like

mysql -uroot -p testDB<testDB.sql

I face a strange issue. If I run import on primary node of the galera, i acn see data is imported properly on primary node. But when I try to verify the same on one of the secondary nodes, I realize that suddenly i am not allowed in using my old credentials. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

On more investigation, i realize that mysql.user table has been emptied on secondary node.

MariaDB [mysql]> select * from mysql.user; Empty set (0.00 sec) (Ofcourse I had to start this node's mysql usng skip grant tables for this)

Can you please guide me what could be the issue here?

[Philip Stoev]

Hello,

Please check the mysqldump file for any statements that wipe out and
recreate and populate the mysql.user table using an INSERT statement. If the
mysql.user table on your installation is MyISAM (which can be checked using
SHOW CREATE TABLE), then those INSERTS produced by mysqldump will not be
replicated to the second node as updates to MyISAM tables are not replicated
by Galera by default (but the statement that wipes the table clean will be).

There are two ways out of this situation:

- instruct mysqldump to not dump and restore the mysql database so that the
original contents of all tables in that database are preserved and not
overwritten;
- enable wsrep_replicate_myisam MySQL option so that the INSERTS on the
MyISAM tables are replicated. You will then need to issue FLUSH PRIVILEGES
or restart the node for the newly-added records to the mysql.user table to
become active users;
- use CREATE USER to recreate the users rather than direct INSERTs into the
MyISAM table.

Philip Stoev

--
You received this message because you are subscribed to the Google Groups
"codership" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to codership-tea...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[James Wang]

Since which version does Galera support wsrep_replicate_myisam  please?

Good to know.  Thanks

[Philip Stoev]

The option has been there for a while but is experimental and limited and
should be used strictly for emergency maintenance such as MySQL user
management and fixing issues with rows in the the mysql database.

Using it for general-purpose operations with MyISAM tables is not
recommended.

Philip Stoev