{"id":922,"date":"2014-11-01T04:30:06","date_gmt":"2014-11-01T04:30:06","guid":{"rendered":"https:\/\/qbytes.cloud\/?p=922"},"modified":"2014-11-01T04:30:06","modified_gmt":"2014-11-01T04:30:06","slug":"copy-mysql-database","status":"publish","type":"post","link":"https:\/\/www.qbytes.cloud\/index.php\/2014\/11\/01\/copy-mysql-database\/","title":{"rendered":"Export, Copy and Restore a Mysql Database to a remote server"},"content":{"rendered":"<p>Export, Copy and Restore a Mysql Database to a remote server<\/p>\n<p>export database<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n\n# mysqlexport -uroot databasename &gt; databasename.sql\n\n<\/pre>\n<p>Copy to other server<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n\n#scp databasename.sql root@remote.box:\/backup\n\n<\/pre>\n<p>Restore<\/p>\n<p># mysql -u user -p databasename < databasename.sql\n\n[\/bash]\n\nHow do I copy a MySQL database from one computer\/server to another?\n\nOther commands:\n[bash]\n\n# mysqldump db-name | mysql -h remote.box.com db-name\n\n[\/bash]\n\nUse ssh if you don't have direct access to remote mysql server (secure method):\n[bash]\n\n# mysqldump db-name | ssh user@remote.box.com mysql db-name\n\n[\/bash]\n\nOR\n[bash]\n\n# mysqldump -u username -p'password' databasename | ssh user@remote.box mysql -u username -p'password databsename\n\n[\/bash]\n\nYou can just copy table called foo to remote database (and remote mysql server remote.box.com) called bar using same syntax:\n[bash]\n\n# mysqldump db-name foo | ssh user@remote.box.com mysql bar\n\n[\/bash]\n\n\nOR\n[bash]\n\n# mysqldump -u user -p'password' db-name foo | ssh user@remote.box.com mysql -u user -p'password' db-name foo\n[\/bash]\n\nBash Script\n\n[bash]\n\/bin\/bash\nDBUSER=user\nDBPASSWORD=pwd\nDBSNAME=sourceDb\nDBNAME=destinationDb\nDBSERVER=db.example.com\nfCreateTable=\"\"\nfInsertData=\"\"\necho \"Copying database ... (may take a while ...)\"\nDBCONN=\"-h ${DBSERVER} -u ${DBUSER} --password=${DBPASSWORD}\"\necho \"DROP DATABASE IF EXISTS ${DBNAME}\" | mysql ${DBCONN}\necho \"CREATE DATABASE ${DBNAME}\" | mysql ${DBCONN}\nfor TABLE in `echo \"SHOW TABLES\" | mysql $DBCONN $DBSNAME | tail -n +2`; do\n        createTable=`echo \"SHOW CREATE TABLE ${TABLE}\"|mysql -B -r $DBCONN $DBSNAME|tail -n +2|cut -f 2-`\n        fCreateTable=\"${fCreateTable} ; ${createTable}\"\n        insertData=\"INSERT INTO ${DBNAME}.${TABLE} SELECT * FROM ${DBSNAME}.${TABLE}\"\n        fInsertData=\"${fInsertData} ; ${insertData}\"\ndone;\necho \"$fCreateTable ; $fInsertData\" | mysql $DBCONN $DBNAME\n[\/bash]\n\nCopy the \/var\/lib\/mysql folder to the new server. Only if the databases are Mysiam\n\n1. Shut down mysql\n2. rename the current \/var\/liv\/mysql to \/var\/liv\/mysql-bak\n3. copy the old \/var\/liv\/mysql to \/var\/lib\/mysql\n4. Restore file ownership to mysql:mysql for everything in \/var\/lib\/mysql:\n[bash]\n\n# sudo chown -R mysql:mysql \/var\/lib\/mysql\n\n[\/bash]\n5. mysql_upgrade -u root -p\nNotes:\nIt is the mysql root password from the old system!\nThe file \/var\/lib\/mysql\/mysql_upgrade_info needs to be writable.\n6. Restart Mysql\n[bash]\n\n# service restart mysqld\n\n[\/bash]\n\nAlso - check out this - Percona XtraBackup <a href=\"http:\/\/www.percona.com\/software\/percona-xtrabackup\" target=\"_blank\" rel=\"noopener\">http:\/\/www.percona.com\/software\/percona-xtrabackup<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Export, Copy and Restore a Mysql Database to a remote server export database # mysqlexport -uroot databasename &gt; 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 | ... <a title=\"Export, Copy and Restore a Mysql Database to a remote server\" class=\"read-more\" href=\"https:\/\/www.qbytes.cloud\/index.php\/2014\/11\/01\/copy-mysql-database\/\" aria-label=\"Read more about Export, Copy and Restore a Mysql Database to a remote server\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[70],"tags":[],"class_list":["post-922","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/922","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/comments?post=922"}],"version-history":[{"count":0,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/922\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/media?parent=922"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/categories?post=922"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/tags?post=922"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}