MySQL

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

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

How do I copy a MySQL database from one computer/server to another?

Other commands:


# mysqldump db-name | mysql -h remote.box.com db-name

Use ssh if you don't have direct access to remote mysql server (secure method):


# mysqldump db-name | ssh user@remote.box.com mysql db-name

OR


# mysqldump -u username -p'password' databasename | ssh user@remote.box mysql -u username -p'password databsename

You can just copy table called foo to remote database (and remote mysql server remote.box.com) called bar using same syntax:


# mysqldump db-name foo | ssh user@remote.box.com mysql bar

OR


# mysqldump -u user -p'password' db-name foo | ssh user@remote.box.com mysql -u user -p'password' db-name foo

Bash Script

/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

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:


# sudo chown -R mysql:mysql /var/lib/mysql

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


# service restart mysqld

Also - check out this - Percona XtraBackup http://www.percona.com/software/percona-xtrabackup