Export, Copy and Restore a Mysql Database to a remote server

export database

# mysqlexport -uroot databasename > databasename.sql

Copy to other server

#scp databasename.sql root@remote.box:/backup

Restore

# mysql -u user -p databasename < databasename.sql [/bash] How do I copy a MySQL database from one computer/server to another? Other commands: [bash] # mysqldump db-name | mysql -h remote.box.com db-name [/bash] Use ssh if you don't have direct access to remote mysql server (secure method): [bash] # mysqldump db-name | ssh user@remote.box.com mysql db-name [/bash] OR [bash] # mysqldump -u username -p'password' databasename | ssh user@remote.box mysql -u username -p'password databsename [/bash] You can just copy table called foo to remote database (and remote mysql server remote.box.com) called bar using same syntax: [bash] # mysqldump db-name foo | ssh user@remote.box.com mysql bar [/bash] OR [bash] # mysqldump -u user -p'password' db-name foo | ssh user@remote.box.com mysql -u user -p'password' db-name foo [/bash] Bash Script [bash] /bin/bash DBUSER=user DBPASSWORD=pwd DBSNAME=sourceDb DBNAME=destinationDb DBSERVER=db.example.com fCreateTable="" fInsertData="" echo "Copying database ... (may take a while ...)" DBCONN="-h ${DBSERVER} -u ${DBUSER} --password=${DBPASSWORD}" echo "DROP DATABASE IF EXISTS ${DBNAME}" | mysql ${DBCONN} echo "CREATE DATABASE ${DBNAME}" | mysql ${DBCONN} for TABLE in `echo "SHOW TABLES" | mysql $DBCONN $DBSNAME | tail -n +2`; do createTable=`echo "SHOW CREATE TABLE ${TABLE}"|mysql -B -r $DBCONN $DBSNAME|tail -n +2|cut -f 2-` fCreateTable="${fCreateTable} ; ${createTable}" insertData="INSERT INTO ${DBNAME}.${TABLE} SELECT * FROM ${DBSNAME}.${TABLE}" fInsertData="${fInsertData} ; ${insertData}" done; echo "$fCreateTable ; $fInsertData" | mysql $DBCONN $DBNAME [/bash] Copy the /var/lib/mysql folder to the new server. Only if the databases are Mysiam 1. Shut down mysql 2. rename the current /var/liv/mysql to /var/liv/mysql-bak 3. copy the old /var/liv/mysql to /var/lib/mysql 4. Restore file ownership to mysql:mysql for everything in /var/lib/mysql: [bash] # sudo chown -R mysql:mysql /var/lib/mysql [/bash] 5. mysql_upgrade -u root -p Notes: It is the mysql root password from the old system! The file /var/lib/mysql/mysql_upgrade_info needs to be writable. 6. Restart Mysql [bash] # service restart mysqld [/bash] Also - check out this - Percona XtraBackup http://www.percona.com/software/percona-xtrabackup