patternsqlMajor
Backup/Restore Users/Passwords/Privileges
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
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
mysqldump [options] --all-databases > all_databases_dump.sql
mysqldump -u root -p mysql user > user_table_dump.sqlThese 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.sqlselect Host, user, password from user ;
SHOW GRANTS FOR 'user'@'localhost';Context
StackExchange Database Administrators Q#100511, answer score: 26
Revisions (0)
No revisions yet.