Which username and password does mysqldump expect?

I’m trying to make a copy of my website’s database, so that I can download it and import it into wamp for local testing.

Here is what I’m entering in Putty:

mysqldump -u my_database_username -p dataname_db.sql --single-transaction --quick --lock-tables=false > dataname_db_local-$(date +%F).sql && gzip dataname_db_local.sql

No matter what combo of user and pass I use, I get this error

Got error: 1044: "Access denied for user to database when selecting the database

It wants the MySQL user that has full privileges to that database, right? ie the same credentials as what I use to connect to the database in a new MySQLi() command in php, right?

I read that sometimes passwords with special characters aren’t allowed, so I made a new user, full priveleges, for that database with a plain alphanumeric pass, but it’s still not accepted.

I then thought maybe it wants the same username and pass as what I use to connect to my server via Putty, but that didn’t work. Neither did -u root with the server password.

Can someone please clarify exactly which username it wants?

Thank you

Answer

Yes, you are right, mysqldump requires exactly the same username and password as what you use to connect to the database in a new MySQLi() command in php.

Make sure your account has Lock_Table privilege.

If it still didn’t work, try to pass the –-single-transaction option to mysqldump:

mysqldump --single-transaction -u db_username -p DBNAME > backup.sql

Notice that there is a syntax problem, you should select your DB at last of mysqldump statement:

mysqldump [options] db_name [tbl_name ...] > filename.sql

Reference: [1] , [2]