3

I exported all databases of a MySQL server by:

    mysqldump -u root -p --all-databases > /tmp/dbs.sql

Then I copied the file (by scp) on another server, which has the same MySQL version, and imported it with:

    mysql -u root -p < dbs.sql

I can access to MySQL only as root. With other users, I obtain:

    ~$ mysql -u jag -p
    Enter password: 
    ERROR 1045 (28000): Access denied for user 'jag'@'localhost' (using password: YES)

However, selecting all users in mysql.user table, I can see that all user accounts where imported. So, how can I overcome this problem, without resetting all user passwords?

radbyx
  • 9,352
  • 21
  • 84
  • 127
Oscar
  • 457
  • 1
  • 4
  • 14

3 Answers3

3

You need to specify username and password, you can try this:

mysql -u USERNAME -pPASSWORD -h HOSTNAMEORIP DATABASENAME

Note that there is no space between -p parameter and password!

You can check this: http://dev.mysql.com/doc/refman/5.0/en/connecting.html

Kristian Vitozev
  • 5,791
  • 6
  • 36
  • 56
  • thanks for your answer. Unfortunately I obtain the same error. $ mysql -u jag -pmypass -h 127.0.0.1 jag ERROR 1045 (28000): Access denied for user 'jag'@'localhost' (using password: YES) – Oscar Jul 05 '13 at 12:36
  • 1
    Unfortunately I obtain the same error. $ mysql -u jag -pmypass -h 127.0.0.1 jag ERROR 1045 (28000): Access denied for user 'jag'@'localhost' (using password: YES) Maybe it doesn't import correctly passwords? – Oscar Jul 05 '13 at 13:13
  • 4
    I found the problem: mysql should be restarted for applying changes with new (imported) users: $ sudo service mysql restart – Oscar Jul 05 '13 at 13:38
0

After following all the similar answers for this issue, I've solved it in CentOS with this: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

CarlosAS
  • 654
  • 2
  • 10
  • 31
0

please make sure to grant privileges to that user u want to restore with, in this case 'jag'

Alex chai
  • 21
  • 4