How do you move MySQL database to different server?
 

I'm moving my biker forum to a new server, and dont know how to backup the current database, move it, and set it up on the new machine so i dont lose any of the users or posts on the board.

Can anyone give me a quick step by step to do this?

server1>mysqldump --user=root --pass=pass --opt database > db.backup.sql
server1>sftp db.backup.sql server2.com

create the empty database on server 2

server2>mysql -u root -ppass database < db.backup.sql


i'm sure there's easier ways, but that's one of them in a nutshell

thanks man!

ok the importing does not work

anyone else know how to import the database on FreeBSD?

That should be pretty flawless. Do you get an error ? Does it just not create the tables ? Is the mysql version different ? You may need a compatibility switch.

actually this works:
mysql -u admin -ppassword -D dbase < db.dbase.sql

check the vBulletin documentation, it's described in detail how to move it.

P.S. I suggest you redirect the old server's traffic to the new IP immediately, before the DNS resolves to the new server.

Use phpMyAdmin to dump the db to an sql file, then import it on the new server.

http://sourceforge.net/projects/phpmyadmin/

Easy as pie.

(approx)

cd /var/db/mysql/$dbname
tar cf $dbname.tar $dbname
scp $dbname.tar root@$targethost:/$targdir
ssh -l root $targethost
cd /var/db/mysql
tar xzvf <targdir>/<dbname>.tar
chown -R mysql:mysql <dbname>

If you need help drop me an icq.