Distributed MySQL Privileges for MySQL Cluster

MySQL Cluster supports distribution of MySQL users and privileges across all SQL nodes in a MySQL Cluster. This support is not enabled by default; you should follow the procedure outlined in this section in order to do so.

[mysql@myhost1 mysqlc]$ mysql -u root -S ../server1-api/mysql.sock

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.24-ndb-7.4.6-cluster-commercial-advanced MySQL Cluster Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE

    ->     FROM INFORMATION_SCHEMA.ROUTINES

    ->     WHERE ROUTINE_NAME LIKE 'mysql_cluster%'

    ->     ORDER BY ROUTINE_TYPE;

Empty set (0.06 sec)

mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES

    -> WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME LIKE '%backup'

    -> ORDER BY ENGINE;

Empty set (0.01 sec)

mysql> source share/ndb_dist_priv.sql

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE

    ->     FROM INFORMATION_SCHEMA.ROUTINES

    ->     WHERE ROUTINE_NAME LIKE 'mysql_cluster%'

    ->     ORDER BY ROUTINE_TYPE;

+---------------------------------------------+----------------+--------------+

| ROUTINE_NAME                                | ROUTINE_SCHEMA | ROUTINE_TYPE |

+---------------------------------------------+----------------+--------------+

| mysql_cluster_privileges_are_distributed    | mysql          | FUNCTION     |

| mysql_cluster_backup_privileges             | mysql          | PROCEDURE    |

| mysql_cluster_move_grant_tables             | mysql          | PROCEDURE    |

| mysql_cluster_move_privileges               | mysql          | PROCEDURE    |

| mysql_cluster_restore_local_privileges      | mysql          | PROCEDURE    |

| mysql_cluster_restore_privileges            | mysql          | PROCEDURE    |

| mysql_cluster_restore_privileges_from_local | mysql          | PROCEDURE    |

+---------------------------------------------+----------------+--------------+

7 rows in set (0.00 sec)

mysql> CALL mysql.mysql_cluster_move_privileges();

2015-06-30 20:49:00 21146 [Note] NDB Binlog: RENAME Event: REPL$mysql/ndb_user_backup

2015-06-30 20:49:02 21146 [Note] NDB Binlog: RENAME Event: REPL$mysql/ndb_db_backup

2015-06-30 20:49:03 21146 [Note] NDB Binlog: RENAME Event: REPL$mysql/ndb_tables_priv_backup

2015-06-30 20:49:04 21146 [Note] NDB Binlog: RENAME Event: REPL$mysql/ndb_columns_priv_backup

2015-06-30 20:49:05 21146 [Note] NDB Binlog: RENAME Event: REPL$mysql/ndb_procs_priv_backup

2015-06-30 20:49:06 21146 [Note] NDB Binlog: RENAME Event: REPL$mysql/ndb_proxies_priv_backup

Query OK, 2 rows affected (15.04 sec)

mysql> SELECT CONCAT(

    ->    'Conversion ',

    ->    IF(mysql.mysql_cluster_privileges_are_distributed(), 'succeeded', 'failed'),

    ->    '.')

    ->    AS Result;

+-----------------------+

| Result                |

+-----------------------+

| Conversion succeeded. |

+-----------------------+

1 row in set (0.01 sec)

mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES

    -> WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME LIKE '%backup'

    -> ORDER BY ENGINE;

+-------------------------+------------+

| TABLE_NAME              | ENGINE     |

+-------------------------+------------+

| db_backup               | MyISAM     |

| user_backup             | MyISAM     |

| columns_priv_backup     | MyISAM     |

| tables_priv_backup      | MyISAM     |

| proxies_priv_backup     | MyISAM     |

| procs_priv_backup       | MyISAM     |

| ndb_columns_priv_backup | ndbcluster |

| ndb_user_backup         | ndbcluster |

| ndb_tables_priv_backup  | ndbcluster |

| ndb_proxies_priv_backup | ndbcluster |

| ndb_procs_priv_backup   | ndbcluster |

| ndb_db_backup           | ndbcluster |

+-------------------------+------------+

12 rows in set (0.00 sec)

[mysql@myhost1 mysqlc]$ mysql -u root -S ../server2-api/mysql.sock

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.6.24-ndb-7.4.6-cluster-commercial-advanced MySQL Cluster Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select table_name,engine from information_schema.tables where table_schema='mysql' and engine='ndbcluster';

+-------------------------+------------+

| table_name              | engine     |

+-------------------------+------------+

| columns_priv            | ndbcluster |

| db                      | ndbcluster |

| ndb_apply_status        | ndbcluster |

| ndb_columns_priv_backup | ndbcluster |

| ndb_db_backup           | ndbcluster |

| ndb_index_stat_head     | ndbcluster |

| ndb_index_stat_sample   | ndbcluster |

| ndb_procs_priv_backup   | ndbcluster |

| ndb_proxies_priv_backup | ndbcluster |

| ndb_tables_priv_backup  | ndbcluster |

| ndb_user_backup         | ndbcluster |

| procs_priv              | ndbcluster |

| proxies_priv            | ndbcluster |

| tables_priv             | ndbcluster |

| user                    | ndbcluster |

+-------------------------+------------+

15 rows in set (0.00 sec)

mysql> quit

Bye