Home > Uncategorized > Quick Primer: MySQL: Setting up a backup user

Quick Primer: MySQL: Setting up a backup user

With reference to the comments section of MySQL GRANT docs, this post covers creating a user with restricted privileges for backups.

Log on from CLI in linux

mysql -p

Create and grant the user you wish access

mysql> use mysql;
mysql> select password('password_to_be_hashed');
mysql> GRANT SELECT,LOCK TABLES ON *.* TO backuprobot@'192.168.100.%' IDENTIFIED BY PASSWORD '[password hash returned by last command]';
mysql> SELECT user, password FROM user;
mysql> SHOW GRANTS FOR 'backuprobot'@'192.168.100.%';

Using the mysqldump command to dump the contents of the db:

mysqldump -c --routines --triggers -ubackuprobot -ppassword_to_be_hashed --host=192.168.100.10 --result-file="d:\server.sql" --all-databases

Removing the grants and user:

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'backuprobot'@'192.168.100.%';
mysql> DELETE FROM user WHERE user='backuprobot';
mysql> SELECT user, password FROM user;
mysql> FLUSH PRIVILEGES;
mysql> SHOW GRANTS FOR 'backuprobot'@'192.168.100.%';
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: