mysqldump access denied
When I try to backup using mysqldump from ssh, I run the following command on machine 10.64.1.1. It gives the following error.
mysqldump --user=test -p=password --host=10.64.1.2 --tab=. databasename tablename
mysqldump: Got error: 1045: Access denied for user 'test'@'10.64.1.1' (using开发者_如何学C password: YES)
when trying to connect
However, I can access mysql using the same user and password.
mysql --user=test -p[password]
Current user: test@10.64.1.1
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.91-50-log Percona SQL Server, Revision 73 (GPL)
Protocol version: 10
Connection: 10.64.1.2 via TCP/IP
Updates:
If I do following mysql document: --password[=password]
or -p[password]
.
Since my password contains special symbol @
, Mysql cannot detect user correctly. It complains:
mysqldump: Got error: 1044: Access denied for user 'test'@'%' to database
You have to run CMD.EXE as the Administrator:
Right click on cmd.exe --> Run as Administrator and then type your command:
mysqldump -u[username] -p[password] -h[ip or name] yourdatabasename > c:\sqlfile.sql
It should work fine.
I think that you would have to lose the = when using -p or do it with --password :
--password[=password], -p[password]
The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, you are prompted for one. Specifying a password on the command line should be considered insecure. See Section 6.6, "Keeping Your Password Secure".
I know the topic is very old, but I happened to have the same issue. I figured out that the problem was just the special character used in the password. In fact they must be escaped with the anti slash: --password=123\@456
or using signle quotes --password='123@456'
I had the same problem with a DB user created via Plesk. The user has permissions for a single database. If I specify the database name I get the error mentioned above. If I specify -A
for all databases, the dump works.
In windows, I resolved it by making a directory and then dumping *.sql file in backup directory.
C:\backup
There are no issues with commands :
Remove the [ ] square brackets
mysqldump -u[username] -p[password] -h[ip] yourdb > C:\backup\sqlfile.sql
It worked for me:)
One more possible cause can be the place you are trying to create dump.
Writing mysqldump -u[username] -p yourdb > D:\backup\sqlfile.sql
instead of mysqldump -u[username] -p yourdb > sqlfile.sql
resolved my problem.
There is no =
(equal sign) for the connection options. You want the following.
mysqldump -u test -ppassword -h 10.64.1.2 ...
Check out the docs for more details on the other options.
Heres what worked for me- run cmd line as admin. dont put any password after -p, you are automatically prompted to enter a password and it works.
Trying with this command
mysqldump --user=root -p --host=127.0.0.1 --databases database_name [database_name_2,...] > dump.sql
The problem is that there need additional access to create file in C:/ location so try to use other like for me it resolve by : mysqldump -u root -p cricket classics > C:\Users\habib_s9ayvfl\Desktop\classics.sql
In accordance with this page https://dev.mysql.com/mysqldump-copying-database open your console window (like cmd) and navigate to your mysql installation folder. F.e. c:\mysql and then to the bin folder, so you have c:\mysql\bin
Then type:
mysqldump source_db_name -uuser_name -ppassword > target_database.sql
Be sure that after -u there are no blanks! Same by -p! No blanks! When you're done, type:
mysqladmin create target_database -uuser_name -ppassword
And now you can migrate the data from target_database.sql to your new database:
mysql target_database < target_database.sql -uuser_name -ppassword
It's a very "simple an stupid" way to cloning your database on a same server, into a new one. Also my description here is simple and stupid, but maybe it will be useful for someone someday :-)
精彩评论