HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Backup/Restore Users/Passwords/Privileges

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
privilegespasswordsusersrestorebackup

Problem

I am moving from one server to another and I want to backup all databases + users/privileges/passwords from my MySQL Server. I found to backup a database using mysqldump, but I cannot figure out, how to backup all users and the given privileges. Is there a way to achieve this or do I have to set this newly up on the new server?

Solution

The 'mysql' database contains users/privileges/passwords. So take the dump of mysql database along with other databases

mysqldump [options] --all-databases > all_databases_dump.sql

mysqldump -u root -p mysql user > user_table_dump.sql


These mysql database tables contain grant information

user: User accounts, global privileges, and other non-privilege columns.

db: Database-level privileges.

tables_priv: Table-level privileges.

columns_priv: Column-level privileges.

procs_priv: Stored procedure and function privileges.

After restoring cross check with

select Host, user, password from user ;

SHOW GRANTS FOR 'user'@'localhost';

Code Snippets

mysqldump [options] --all-databases > all_databases_dump.sql

mysqldump -u root -p mysql user > user_table_dump.sql
select Host, user, password from user ;

SHOW GRANTS FOR 'user'@'localhost';

Context

StackExchange Database Administrators Q#100511, answer score: 26

Revisions (0)

No revisions yet.