Migrating User Permissions

Exporting Permissions with clustrix_clone_users

Migration of user accounts and permissions from MySQL is a separate step from data load. The mysqldump --all-databases will dump the mysql database, but Xpand cannot use this data to instantiate users. Instead, use the clustrix_clone_users script, available at /opt/clustrix/bin/. The tool queries a MySQL or Xpand and generates a set of GRANT statements, which can be run to reproduce the set of user accounts and permissions on another cluster (or MySQL instance).

shell> ./clustrix_clone_users -H mysqlhost -u root > user_dump.sql 

shell> mysql -u root -h clx < user_dump.sql 

Note that using GRANT or CREATE USER queries are the only way to create users; it is not possible to write the users table directly in the system database.  

Importing Permissions

clustrix_clone_users utility will query a MySQL (or Xpand) database to dump the users and permissions, generating SQL which can then be imported, as shown in this example:

shell> /clustrix$ ./clustrix_clone_users -H localhost > /tmp/grants.sql
shell> /clustrix$ head /tmp/grants.sql
--
-- Clustrix Users dumpfile ver: 113:82f8694c98db
-- Host: localhost
--
GRANT ALL PRIVILEGES ON *.* TO 'mysql_slave'@'' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'clustrix_ui'@'127.0.0.1' IDENTIFIED BY PASSWORD '*46A23F3EF4B5568CD0D6951239A0345A78DDF61A' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `statd`.* TO 'statd'@'%' IDENTIFIED BY PASSWORD '*58D9255AEB513581F38430D559A1455461E6B74E';
shell> /clustrix$ mysql -h mogwai -u root < /tmp/grants.sql