17.5.14 Distributed MySQL Privileges for MySQL Cluster

MySQL Cluster NDB 7.2 introduces support for distributing 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.

Normally, each MySQL server's user privilege tables in the
mysql database must use the
MyISAM storage engine, which means
that a user account and its associated privileges created on one
SQL node are not available on the cluster's other SQL nodes.
In MySQL Cluster NDB 7.2 and later, an SQL file
ndb_dist_priv.sql is provided with the MySQL
Cluster distribution. This file can be found in the
share/mysql directory in the MySQL
installation directory.

The first step in enabling distributed privileges is to load this
script into a MySQL Server that functions as an SQL node (which we
refer to after this as the target SQL node
or MySQL Server). You can do this by executing the following
command from the system shell on the target SQL node after
changing to its MySQL installation directory (where
options stands for any additional
options needed to connect to this SQL node):

shell> mysql options -uroot < share/mysql/ndb_dist_priv.sql

Importing ndb_dist_priv.sql creates a number
of stored routines (six stored procedures and one stored function)
in the mysql database on the target SQL node.
After connecting to the SQL node in the mysql
client (as the MySQL root user), you can verify
that these were created as shown here:

The stored procedure named
mysql_cluster_move_privileges creates backup
copies of the existing privilege tables, then converts them to
NDB.

mysql_cluster_move_privileges performs the
backup and conversion in two steps. The first step is to call
mysql_cluster_backup_privileges, which creates
two sets of copies in the mysql database:

A set of local copies that use the
MyISAM storage engine. Their
names are generated by adding the suffix
_backup to the original privilege table
names.

A set of distributed copies that use the
NDBCLUSTER storage engine. These
tables are named by prefixing ndb_ and
appending _backup to the names of the
original tables.

After the copies are created,
mysql_cluster_move_privileges invokes
mysql_cluster_move_grant_tables, which contains
the ALTER TABLE ...
ENGINE = NDB statements that convert the mysql system
tables to NDB.

Normally, you should not invoke either
mysql_cluster_backup_privileges or
mysql_cluster_move_grant_tables manually; these
stored procedures are intended only for use by
mysql_cluster_move_privileges.

Although the original privilege tables are backed up
automatically, it is always a good idea to create backups manually
of the existing privilege tables on all affected SQL nodes before
proceeding. You can do this using mysqldump in
a manner similar to what is shown here:

Depending on the number of rows in the privilege tables, this
procedure may take some time to execute. If some of the privilege
tables are empty, you may see one or more No data -
zero rows fetched, selected, or processed warnings
when mysql_cluster_move_privileges returns. In
such cases, the warnings may be safely ignored. To verify that the
conversion was successful, you can use the stored function
mysql_cluster_privileges_are_distributed as
shown here:

Once the conversion to distributed privileges has been made, any
time a MySQL user account is created, dropped, or has its
privileges updated on any SQL node, the changes take effect
immediately on all other MySQL servers attached to the cluster.
Once privileges are distributed, any new MySQL Servers that
connect to the cluster automatically participate in the
distribution.

Note

For clients connected to SQL nodes at the time that
mysql_cluster_move_privileges is executed,
you may need to execute
FLUSH
PRIVILEGES on those SQL nodes, or to disconnect and
then reconnect the clients, in order for those clients to be
able to see the changes in privileges.

All MySQL user privileges are distributed across all connected
MySQL Servers. This includes any privileges associated with views
and stored routines, even though distribution of views and stored
routines themselves is not currently supported.

In the event that an SQL node becomes disconnected from the
cluster while mysql_cluster_move_privileges is
running, you must drop its privilege tables after reconnecting to
the cluster, using a statement such as
DROP TABLE IF EXISTS
mysql.user mysql.db mysql.tables_priv mysql.columns_priv
mysql.procs_priv. This causes the SQL node to use the
shared privilege tables rather than its own local versions of
them. This is not needed when connecting a new SQL node to the
cluster for the first time.

In the event of an initial restart of the entire cluster (all data
nodes shut down, then started again with
--initial), the shared privilege
tables are lost. If this happens, you can restore them using the
original target SQL node either from the backups made by
mysql_cluster_move_privileges or from a dump
file created with mysqldump. If you need to use
a new MySQL Server to perform the restoration, you should start it
with --skip-grant-tables when
connecting to the cluster for the first time; after this, you can
restore the privilege tables locally, then distribute them again
using mysql_cluster_move_privileges. After
restoring and distributing the tables, you should restart this
MySQL Server without the
--skip-grant-tables option.

You can restore the SQL node's local privileges using either
of two procedures.
mysql_cluster_restore_privileges works as
follows:

If copies of the mysql.ndb_*_backup tables
are available, attempt to restore the system tables from
these.

Otherwise, attempt to restore the system tables from the local
backups named *_backup (without the
ndb_ prefix).

The other procedure, named
mysql_cluster_restore_local_privileges,
restores the system tables from the local backups only, without
checking the ndb_* backups.

The system tables re-created by
mysql_cluster_restore_privileges or
mysql_cluster_restore_local_privileges use the
MySQL server default storage engine; they are not shared or
distributed in any way, and do not use MySQL Cluster's
NDB storage engine.

The additional stored procedure
mysql_cluster_restore_privileges_from_local is
intended for the use of
mysql_cluster_restore_privileges and
mysql_cluster_restore_local_privileges. It
should not be invoked directly.

Important

Applications that access MySQL Cluster data directly, including
NDB API and ClusterJ applications, are not subject to the MySQL
privilege system. This means that, once you have distributed the
grant tables, they can be freely accessed by such applications,
just as they can any other NDB
tables. In particular, you should keep in mind that
NDB API and ClusterJ applications can read and write
user names, host names, password hashes, and any other contents
of the distributed grant tables without any
restrictions.