This post's content Migrating a MySQL database usually requires only few simple steps, but can take quite some time, depending on the amount of data you would like to migrate. The following steps will guide through how to export the MySQL database from the old server, secure it, copy it to the new server, import it successfully and make sure the data is there. Exporting MySQL database to a dump fileOracle provides a utility named mysqldump which allows to easily export the database structure and data to an SQL dump file. Use the following command: mysqldump -u root -p --opt [database name] > [database name].sql Few notes:
Secure the backup fileIn most cases, an organization's data is its most critical asset. Therefore, we do not want database backups laying around in our servers unprotected, as they can mistakenly leak or even worse, get stolen by hackers. Therefore, at the first chance you get, let's compress and encrypt the file and delete the original file. To encrypt the file to a compressed file in Linux operating systems, use this command: zip --encrypt dump.zip db.sql You will be prompted to enter a password before the compression starts. Transfer the backup fileNow that we have an encrypted dump file, let's transfer it over the network to the new server, using SCP: scp /path/to/source-file [email protected]:/path/to/destination-folder/ Import MySQL dump to new serverNow that we have the backup file on the new server, let's decrypt and extract it: unzip -P your-password dump.zip Once the file is imported, remember to delete the dump file both for storage and security reasons. mysql -u root -p newdatabase < /path/to/newdatabase.sql Validate imported data in new serverNow that we have the database imported on the new server, we need a way to make sure that the data is actually there and
we didn't lose anything. SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = 'YOUR_DB_NAME'; In addition, we recommend to check for MIN and MAX records of columns in the tables, to make sure the data itself is valid and not only the amount of data. Another export & import optionWe kept this option to the end, as we do not really recommend working with it. mysqldump -u root -pPassword --all-databases | ssh [email protected]_host.host.com 'cat - | mysql -u root -pPassword' Important Notes
Enjoy your new server! How do I move a database from one server to another?Transfer a Database from one Server to Another Using Backup and Restore Method. Back up the source database. ... . Create a new target database: in Database Explorer, right-click the target connection and select New Database.. Right-click the new database and select Restore. ... . On the Source and Target tab of the wizard:. How do I copy a MySQL database from one server to another in Windows?To copy a MySQL database from a server to another, you use the following steps: Export the database on the source server to a SQL dump file. Copy the SQL dump file to the destination server. Import the SQL dump file to the destination server.
Can I copy MySQL data directory to another server?You have to shut down mysql server (which is not good, if it's a production server) You have to make sure the permission of data (mysql) directory is same as the previous one. You will have to monitor the mysql_error log while starting the second server.
How do I move a MySQL database from one server to another using Workbench?5 Answers. Open MySQL Workbench.. Create the old server's connection (if you haven't it). Create the new server's connection (if you haven't it). Go to Server Administration and click Manage Import / Export.. Select old server.. Select all schemas in Export to Disk tab.. |