CODEWITHSUNDEEP
mysql
user984003
user984003

Reputation: 29597

mysqldump is not saving user password

I am using mysqldump to save my database and now want to reconstruct it completely, including the database, the users, and the user permissions.

mysqldump --databases my_db mysql -u root -p > mysqldump.sql

This generates the sql to create the database and it creates the user in the following table:

INSERT INTO db VALUES ('localhost','my_db','my_user','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

However, the password for the user does not appear anywhere in the file and it's not clear to me how I can use the user to access the DB once I have recreated the DB.

I cannot use the --all-databases option because there is another DB with tons of data and I do not want that in the file.

Upvotes: 0

Views: 218

Answers (3)

Zafar Malik
Zafar Malik

Reputation: 6854

Whenever you assign privilege to any user then entry goes as per below-

If you are assigning privileges on all dbs like . then entry will go only in user table.

If you are assigning privileges db wise like mydb.* then one entry will go in user table which contains all privilegs with 'N' permission & keep user password there and 2nd entry in db table which contains appropriate rights.

mysql db contains user privileges as per below-

global rights (all db) with user anme and password : user table db wise rights in : db table table wise rights in : tables_priv table column wise rights in : columns_priv table.

So you are checking db table, which contains db wise rights but not password as password is stored only in user table.

Upvotes: 0

Norbert
Norbert

Reputation: 6084

You are looking at the incorrect table in your dump: Look at the table:

CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL Etc

The INSERT statement after that contains your passwords

Upvotes: 1

Rasika
Rasika

Reputation: 1998

The user name and password are not part of the database, but are part of the special mysql database. If you want to replicate the user accounts, take a dump of the mysql database and restore it on the other end and then use flush privileges command to refresh the user table in the DB.

Upvotes: 0

Related Questions