how to "transfer" mysql database?

[alan123]

I had mysql database installed on Red Hat6, I have another machine running on RH8, mysql already installed, now I want to "transfer" database to this new machine. I did this on RH6:
mysqldump -u access_log -p access_log | gzip -c >db.archive.gz
A zip file "db.archive.gz" was generated, I FTP to RH8, now I try to run these two commands:
1)mysqladmin -u access_log -p create access_log
2)zcat db.archive.gz | mysql -u access_log -p access_log

user "access_log" already created.
When I run command(1), it gives me error message:
------
[root@dev bin]# ./mysqladmin -u access_log -p create access_log
Enter password:
./mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: 'access_log@localhost' (Using password: YES)'
[root@dev bin]#
------

what cause that problem, how can I fix it? thanks.

 

[QatQat]

Try not piping it through Gzip


mysqldump -u access_log -ppassword database_name > script.sql


Then on the new machine run

mysql -u access_log -ppassword < path/script.sql


Bye


Qatqat The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[alan123]

hi, QatQat,

But when I run &quot;mysql -u access_log -ppassword < path/script.sql&quot;, it still shows error:
-------------
[root@dev bin]# ./mysql -u access_log -pacc651 < /usr/local/mysql/bin/archive00
1.sql
ERROR 1045: Access denied for user: 'access_log@localhost' (Using password: YES)
[root@dev bin]#
-------------

 

[QatQat]

It looks like a user rights problem;
login to your MySQL normally and type


grant all
on access_log.*
to access_log@localhost
identified by &quot;acc651&quot;
;



then run



flush privileges;


This should help you


Bye


Qatqat

The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[hvass]

what about copying the complete data dir to the new box in its entirity, this would include the mysql database with all the current permissions

 

[QatQat]

Good thought hvass

if you want to copy everything across and you are using transaction safe tables then you must also copy your .cnf files.


Bye


Qatqat The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[alan123]

Thanks, now it works fine.