Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.5Mb
PDF (A4) - 1.5Mb
HTML Download (TGZ) - 289.5Kb
HTML Download (Zip) - 301.7Kb


MySQL Utilities 1.5 Manual  /  ...  /  Create the Associated MySQL Users

8.2.3.1 Create the Associated MySQL Users

Fabric uses four different types of users, each with a different set of required privileges.

Note

The backup and restore users were added in Fabric 1.5.5.

  • Backing store user: stores Fabric specific information, and is only created on the Fabric backing store MySQL server. For additional information, see Section 8.6, “Backing Store”

  • Server user: accesses the managed MySQL servers, and is created on each managed MySQL server.

  • Backup user: executes backup operations, such as mysqldump, and is created on each managed MySQL server.

  • Restore user: executes restore operations that typically use the mysql client, and is created on each managed MySQL server.

Privileges

It is possible to use the same MySQL account for the server user, backup user, and restore user, but in this case the user would have the sum of privileges of the three users. This would result in a very powerful user and is therefore not recommended for production use.

However, for a quick and simple temporary trial, it may be easiest to set the users for all accounts using the same user name and password, such as root.

The Backing Store (Fabric) User

The first thing you must have is a user account on the MySQL server that you plan to use for your backing store. The user account information is stored in the Fabric configuration file.

The backing store database and its associated user are defined under the [storage] using user for the user name and password as the password.

The Fabric user account on the backing store requires the following privileges on the backing store database:

ALTER              - alter some database objects
CREATE             - create most database objects
CREATE VIEW        - create views
DELETE             - delete rows
DROP               - drop most database objects
EVENT              - manage events
REFERENCES         - foreign keys
INDEX              - create indexes
INSERT             - insert rows
SELECT             - select rows
UPDATE             - update rows

Example statements to create this user, to be executed on the backing store MySQL server:

Note

MySQL Fabric creates this database based on fabric.cfg, which in our example is named mysql_fabric. In other words, do not execute CREATE database mysql_fabric; here.

CREATE USER 'fabric_store'@'localhost'
   IDENTIFIED BY 'secret';

GRANT ALTER, CREATE, CREATE VIEW, DELETE, DROP, EVENT,
   INDEX, INSERT, REFERENCES, SELECT, UPDATE ON mysql_fabric.*
   TO 'fabric_store'@'localhost';
Note

The "REFERENCES" privilege is only required when working with MySQL 5.7 and above. MySQL Fabric does not check for this privilege on earlier versions.

For additional information about using and setting up the backing store, see Section 8.6, “Backing Store”.

The Server User

MySQL Fabric uses the server user account to access all MySQL servers that it manages. In other words, this user must be created on all managed MySQL servers.

The server account is defined under the [servers] section using user for the user name and password as the password.

The Fabric server user account on the managed MySQL servers requires the following privileges in global scope:

DELETE             - prune_shard
PROCESS            - list sessions to kill
RELOAD             - RESET SLAVE
REPLICATION CLIENT - SHOW SLAVE STATUS
REPLICATION SLAVE  - SHOW SLAVE HOSTS

The Fabric server user account on the managed MySQL servers requires the following privileges on mysql_fabric.*:

ALTER              - alter some database objects
CREATE             - create most database objects
DELETE             - delete rows
DROP               - drop most database objects
INSERT             - insert rows
SELECT             - select rows
UPDATE             - update rows

Example statements to create the server user, to be executed on each managed MySQL server:

CREATE USER 'fabric_server'@'localhost'
  IDENTIFIED BY 'secret';

GRANT DELETE, PROCESS, RELOAD, REPLICATION CLIENT,
  REPLICATION SLAVE, SELECT, SUPER, TRIGGER ON *.*
  TO 'fabric_server'@'localhost';

GRANT ALTER, CREATE, DELETE, DROP, INSERT, SELECT, UPDATE
   ON mysql_fabric.* TO 'fabric_server'@'localhost';
The Backup User

If you want to use sharding, or clone a MySQL server with the intention to add it to a High-Availability (HA) group, then you must define restore and backup users. Like the server user, these users must be created on all managed servers.

The backup account is defined under the [servers] section using backup_user for the user name and backup_password as the password.

The backup account on the managed MySQL servers requires the following privileges in global scope if mysqldump is used:

EVENT              - show event information
EXECUTE            - show routine information inside views
REFERENCES         - foreign keys
SELECT             - read data
SHOW VIEW          - SHOW CREATE VIEW
TRIGGER            - show trigger information

Example statements to create the backup user, to be executed on each managed MySQL server:

CREATE USER 'fabric_backup'@'localhost'
  IDENTIFIED BY 'secret';

GRANT EVENT, EXECUTE, REFERENCES, SELECT, SHOW VIEW, TRIGGER ON *.*
  TO 'fabric_backup'@'localhost';
Note

The "REFERENCES" privilege is only required when working with MySQL 5.7 and above. MySQL Fabric does not check for this privilege on earlier versions.

The Restore User

If you want to use sharding, or clone a server with the intention to add it to a High-Availability (HA) group, then you must define restore and backup users. Like the server user, these users must be created on all managed servers.

The restore account is defined under the [servers] section using restore_user for the user name and restore_password as the password.

The restore account on the managed MySQL servers requires the following privileges in global scope if mysqldump is used:

ALTER              - ALTER DATABASE
ALTER ROUTINE      - ALTER {PROCEDURE|FUNCTION}
CREATE             - CREATE TABLE
CREATE ROUTINE     - CREATE {PROCEDURE|FUNCTION}
CREATE TABLESPACE  - CREATE TABLESPACE
CREATE VIEW        - CREATE VIEW
DROP               - DROP TABLE (used before CREATE TABLE)
EVENT              - DROP/CREATE EVENT
INSERT             - write data
LOCK TABLES        - LOCK TABLES (--single-transaction)
REFERENCES         - Create tables with foreign keys
SELECT             - LOCK TABLES (--single-transaction)
SUPER              - SET @@SESSION.SQL_LOG_BIN = 0
TRIGGER            - CREATE TRIGGER
Note

Although the "CREATE TABLESPACE" and "REFERENCES" privileges are only required when working with MySQL 5.7 and above, MySQL Fabric still checks for them to help simplify the upgrade process to MySQL 5.7.

Example statements to create the restore user, to be executed on each managed MySQL server:

CREATE USER 'fabric_restore'@'localhost'
  IDENTIFIED BY 'secret';

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TABLESPACE, CREATE VIEW,
  DROP, EVENT, INSERT, LOCK TABLES, REFERENCES, SELECT, SUPER,
  TRIGGER ON *.* TO 'fabric_restore'@'localhost';

User Comments
Sign Up Login You must be logged in to post a comment.