This post identifies the minimum privileges required by a user in MySQL to take a backup of a database. The mysqldump backup tool takes a read consistent view of the database. As a result, it needs to lock the tables and select the data from each table in a database. As a result, SELECT and LOCK TABLES privileges are required.
Here is an example below. We are logged in with root user and we will create a separate user in this database, “demo” in our case, that will allows us to perform the backups:
First we create the user
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1049 Server version: 5.0.45 SUSE MySQL RPM Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from mysql.user where user = 'backup'; Empty set (0.00 sec) mysql> grant select, lock tables, show view on demo.* to backup@localhost identified by 'test'; Query OK, 0 rows affected (0.00 sec) mysql> select * from mysql.user where user = 'backup'; +-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | +-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+ | localhost | backup | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | +-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+ 1 row in set (0.00 sec) mysql> quit Bye
Now we run the backup:
$ mysqldump -u backup -ptest demo > demo.sql $ more demo.sql -- MySQL dump 10.11 -- -- Host: localhost Database: demo -- ------------------------------------------------------ -- Server version 5.0.45 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `Test` -- DROP TABLE IF EXISTS `Test`; CREATE TABLE `Test` ( `id` int(11) NOT NULL auto_increment, `name` varchar(10) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `Test` -- LOCK TABLES `Test` WRITE; /*!40000 ALTER TABLE `Test` DISABLE KEYS */; /*!40000 ALTER TABLE `Test` ENABLE KEYS */; UNLOCK TABLES; -- -- Temporary table structure for view `Test_view` -- DROP TABLE IF EXISTS `Test_view`; /*!50001 DROP VIEW IF EXISTS `Test_view`*/; /*!50001 CREATE TABLE `Test_view` ( `a` int(11), `b` int(11) ) */;