Home FAQ Import your MySQL database to filess

Import your MySQL database to filess

Last updated on Mar 08, 2024

Moving your MySQL database to a new server can be a breeze! This guide will walk you through the process, covering everything from backing up your data to setting it up on the new host.

Before We Begin: Gather Your Credentials

For both the source (old) and destination (new) servers, you'll need the following:

  • Username (USER): The username to access your MySQL database.

  • Password (PASSWORD): The password for the aforementioned username.

  • Host (HOST): The domain name or IP address of the MySQL server.

  • Port (PORT): The port number used by the MySQL server (default is 3306).

  • Database Name (DATABASE): The specific database you want to migrate.

Note: Make sure you have appropriate permissions to access and modify databases on both servers.

Step 1: Backing Up Your Database

A full backup is crucial before any migration. Here's how to create a dump file of your source database using the mysqldump command:

mysqldump -u USER -pPASSWORD HOST:PORT DATABASE > database_backup.sql

  • Replace USER, PASSWORD, HOST, PORT, and DATABASE with your actual credentials.

  • database_backup.sql is the filename you can customize.

Enter your password when prompted. This command will create a file named database_backup.sql containing the entire structure and data of your database.

Step 2: Setting Up the New Host

On your new server, ensure you have a MySQL server installed and running. You'll also need to create a new database with the same name as the one you're migrating.

Here's an example using the MySQL command-line interface:

mysql -u root -p -h NEW_HOST -P NEW_PORT

CREATE DATABASE DATABASE;

GRANT ALL PRIVILEGES ON DATABASE.* TO USER@'HOST' IDENTIFIED BY 'PASSWORD';

FLUSH PRIVILEGES;

exit
  • Replace root, NEW_HOST, NEW_PORT, DATABASE, USER, HOST, and PASSWORD with your new server's credentials.

This creates a new database with the same name and grants privileges to the user you'll be using to import the data.

Step 3: Importing the Backup to the New Host

Now, let's transfer the backup file (database_backup.sql) to your new server. You can use tools like SCP (secure copy) or any FTP client that supports secure transfers.

Once the file is uploaded to the new server, use the mysql command to import the data:

mysql -u USER -pPASSWORD HOST:PORT DATABASE < database_backup.sql
  • Replace the credentials (USER, PASSWORD, HOST, and PORT) with your new server's information.

Enter your password when prompted, and the database will be imported to your new server.

Step 4: Verification (Optional)

You can verify the import by logging into the new server's MySQL console and running:

mysql -u USER -pPASSWORD HOST:PORT DATABASE

SHOW TABLES;

This will list all the tables within the imported database. You can also run queries to check specific data within the tables.

Bonus Tip: Using MySQL Workbench

For a more user-friendly approach, consider using MySQL Workbench, a free graphical tool by MySQL itself. It offers a dedicated migration wizard that can guide you through the process visually.

Remember: This is a general guide, and specific steps might vary depending on your hosting environment and server configuration. Always refer to your hosting provider's documentation if needed.