0

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

Shaun
  • 1,861
  • 2
  • 23
  • 35
  • 1
    I'm not entirely sure but have you restarted the service or do [FLUSH PRIVILEGES](https://stackoverflow.com/questions/36463966/mysql-when-is-flush-privileges-in-mysql-really-needed) after you created the new user? – FanoFN Jun 25 '21 at 05:42

1 Answers1

1

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]

NcXNaV
  • 1,601
  • 4
  • 12
  • 22
  • Thank you NcXNaV. It still didn't work, but I found some code I'd used years ago, which had the pass included (incl a backwards slash for some reason?), so I tweaked it, plus put the DB at the end as you suggested. The user has select, delete, update, insert, and lock tables privileges set up in phpmyadmin. The code: mysqldump --single-transaction --quick --lock-tables=false -u database_username -p\mybigpasswordwithabackwardsslash database_databasename | gzip -9 > /home/whereIWantIt/database_databasename.sql.gz; It worked at the end of a long, frustrating day, so all it great! Thanks again. – Shaun Jun 26 '21 at 01:50