move mysql to new server

[RabbieBurns]

Ive bought a new server to replace one of the old ones. Im slowly installing all the software, bringing over all the configs etc etc.

 

Not quite sure how to get the entire mysql accross.

 

mysqldump seems to do it database by database - is there any way to export the whole system, users, databases, tables etc in one go, that I can then import directly to the new isntallation on the new server?

[Ric_]

The following will dump all the databases at once to a file called backup.sql:

 

mysqldump -A > backup.sql

 

Simply restroe that into your new instance and job's done!

[contink]

Slight amendment to that to save a lot of standing around or large files if it's huge or over the net...

mysqldump -A > backup.sql | gzip -c > backup.tar.gz

 

That should pipe the output into a tar.gzip file which is a good deal smaller and you just have to extract at the other end and then restore.

 

Makes life a lot easier too if you want a backup on your local machine before you commit.

[RabbieBurns]

cheers

 

How do i re-import it to the new mysql installation?

 

Is it as simple as

mysql < backup.sql

?

[RabbieBurns]

mysqldump -A > backup.sql | gzip -c > backup.tar.gz

 

Is that correct ?

 

-rw-r--r--  1 root root  1740665 Jun 20 15:20 backup.sql
-rw-r--r--  1 root root       20 Jun 20 15:20 backup.tar.gz

 

seems a bit small

[CyberNerd]

Is that correct ?

 

-rw-r--r--  1 root root  1740665 Jun 20 15:20 backup.sql
-rw-r--r--  1 root root       20 Jun 20 15:20 backup.tar.gz

 

seems a bit small

 

it is. try:

mysqldump [options]  | gzip -c > backup.gz

 

After you import the database to the new server you will need to reset the permissions.

 

in mysql

GRANT ALL ON *.* TO 'someuser'@'somehost';

GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

 

MySQL :: MySQL 5.0 Reference Manual :: 12.5.1.3 GRANT Syntax

[RabbieBurns]

excellent thanks, I was wondering why the Joomlas werent able to connect to the database.

 

That was painless