Transferring a database between virtual private servers can be accomplished using a SCP (Secure Copy), a method of copying files derived from the SSH Shell. Keep in mind, you will need to know the passwords for both virtual servers. Show
In order to migrate the database, there are two steps: Step One—Perform a MySQL DumpBefore transferring the database file to the new VPS, we first need to back it up on the original virtual server by using the mysqldump command. mysqldump -u root -p --opt [database name] > [database name].sql After the dump is performed, you are ready to transfer the database. Step Two—Copy the DatabaseSCP helps you copy the database. If you used the previous command, you exported your database to your home folder. The SCP command has the following syntax: scp [database name].sql [username]@[servername]:path/to/database/ A sample transfer might look like this: scp newdatabase.sql :~/ After you connect, the database will be transferred to the new virtual private server. Step Three—Import the DatabaseOnce the data has been transferred to the new server, you can import the database into MySQL: mysql -u root -p newdatabase < /path/to/newdatabase.sql With that, your transfer via SCP will be complete. By Etel Sverdlov Want to learn more? Join the DigitalOcean Community!Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest. Sign up 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 transfer data from one database to another in MySQL?In order to migrate the database, there are two steps:. Step One—Perform a MySQL Dump. Before transferring the database file to the new VPS, we first need to back it up on the original virtual server by using the mysqldump command. ... . Step Two—Copy the Database. SCP helps you copy the database. ... . Step Three—Import the Database.. How do I transfer data from one database to another?Right click on the database you want to copy.. 'Tasks' > 'Export Data'. Next, Next.. Choose the database to copy the tables to.. Mark 'Copy data from one or more tables or views'. Choose the tables you want to copy.. Finish.. What is data migration in MySQL?With the MySQL Workbench Migration Wizard, users can convert an existing database to MySQL in minutes rather than hours or days that the same migration would require using traditional, manual methods. The Migration Wizard allows you to easily and quickly migrate databases from various RDBMS products to MySQL.
How do I copy a SQL database from one database to another?On either the source or destination SQL Server instance, launch the Copy Database Wizard in SQL Server Management Studio from Object Explorer and expand Databases.. Move. Move the database to the destination server.. Copy. Copy the database to the destination server.. Source. ... . Status. ... . Refresh.. |