Menu

MySQL

Database Permissions – MySQL

Full backup

To perform MySQL full backups, the SqlBak database user needs the following privileges. Run the commands below (replace your_database with the actual database name; repeat the second GRANT line for each database you want to back up).

CREATE USER 'sqlbak'@'localhost' IDENTIFIED BY 'your_password';

GRANT RELOAD, REPLICATION CLIENT, PROCESS ON *.* TO 'sqlbak'@'localhost';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT ON `your_database`.* TO 'sqlbak'@'localhost';
FLUSH PRIVILEGES;

What each privilege is used for (full backup)

  • RELOAD — Required for FLUSH TABLES during backup. mysqldump uses it to get a consistent snapshot.
  • REPLICATION CLIENT — Required for SHOW MASTER STATUS. mysqldump uses it for point-in-time recovery metadata.
  • PROCESS — Required when dumping tablespaces. Allows the backup process to read the process list and tablespace information.
  • SELECT — Read table data for the dump.
  • LOCK TABLES — Lock tables during backup so mysqldump can get a consistent copy.
  • SHOW VIEW — Dump view definitions.
  • EVENT — Required for SHOW EVENTS. Allows dumping scheduled events in the database.

Incremental backup (binary log)

To perform MySQL incremental (binary log) backups, the SqlBak database user also needs the following privilege.

GRANT REPLICATION SLAVE ON *.* TO 'sqlbak'@'localhost';
FLUSH PRIVILEGES;

What this privilege is used for

  • REPLICATION SLAVE — Required to read the binary log. SqlBak uses it when backing up with the Binary log backup type (incremental backups).

Restore

To restore MySQL databases (including restore to a new database that is created during the process), the SqlBak user needs the following privileges. These are granted globally (*.*) because the target database may not exist yet when you configure the user — it is often created as part of the restore.

GRANT CREATE, DROP, ALTER, INSERT, SELECT, INDEX, LOCK TABLES, SHOW VIEW, CREATE VIEW, TRIGGER, EVENT ON *.* TO 'sqlbak'@'localhost';
FLUSH PRIVILEGES;

Why these privileges are granted globally

Restore runs statements such as DROP DATABASE IF EXISTS, CREATE DATABASE, CREATE TABLE, INSERT, and similar. The target database name is chosen at restore time and may be new. You cannot grant privileges on a database that does not exist yet, so the restore user must have the necessary rights on *.*. That way, as soon as the database is created during restore, the user can create objects in it and load data.

What each privilege is used for (restore)

  • CREATE — Create the target database and all objects in it (tables, views, procedures, etc.).
  • DROP — Drop the database or objects when overwriting (e.g. DROP DATABASE IF EXISTS).
  • ALTER — Change table structure during restore (e.g. indexes, columns).
  • INSERT, SELECT — Load and read data.
  • INDEX — Create indexes.
  • LOCK TABLES — Lock tables during restore when required by the restore script.
  • SHOW VIEW, CREATE VIEW — Restore view definitions.
  • TRIGGER — Restore triggers.
  • EVENT — Restore scheduled events.