Skip to main content

[How-To] Migrate MariaDB Database from local to remote MariaDB Server

Purpose

Show how to migrate MariaDB database from local to remote MariaDB server

Prerequisites

List of prerequisites:

  • Root user or sudo user
  • MariaDB Servers
  • MariaDB Admin user

Instructions

Step 1: Backup your WordPress Database

  1. Local Database Backup:

    • Log in to your current WordPress site's server.
    • Use a tool like mysqldump to create a backup of your local WordPress database. Run the following command:
    • mysqldump -u [username] -p[password] [database_name] > local_backup.sql

    Replace [username], [password], and [database_name] with your MariaDB username, password, and WordPress database name, respectively.

  2. Transfer Backup to Remote Server:

    • Copy the local_backup.sql file to your dedicated MariaDB server. You can use secure copy (SCP) or any other method you prefer.

Step 2: Create a Database on Remote Server

  1. Access Remote MariaDB:

    • Log in to your dedicated MariaDB server.

  2. Create a New Database:

    • In the MariaDB shell, create a new database for your WordPress site:
    • CREATE DATABASE new_database;

  3. Create a Database User:

    • Create a user and grant necessary privileges:
    • CREATE USER 'new_user'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON new_database.* TO 'new_user'@'%'; FLUSH PRIVILEGES;

    Replace 'new_user' and 'password' with your preferred username and password.

Step 3: Import Database to Remote Server

  1. Navigate to the Backup Location:

    • On your dedicated MariaDB server, navigate to the directory where you transferred the local_backup.sql file.

  2. Import Database:

    • Use the following command to import the database:
    • mysql -u [username] -p[password] [database_name] < local_backup.sql

    Replace [username], [password], and [database_name] with the new database username, password, and name.

Step 4: Update WordPress Configuration

  1. Edit wp-config.php:
    • On your WordPress site's server, locate the wp-config.php file.
    • Update the database connection details:
    • define('DB_NAME', 'new_database'); 
define('DB_USER', 'new_user'); 
define('DB_PASSWORD', 'password'); 
define('DB_HOST', 'remote_server_ip');
    Replace 'new_database', 'new_user', 'password', and 'remote_server_ip' with your database name, username, password, and the IP address of your dedicated MariaDB server.

Step 5: Test and Update URLs

  1. Test the Site:

    • Visit your WordPress site to ensure everything is working correctly.

  2. Update URLs (if needed):

    • If your site URLs have changed, update them using a search and replace tool or a plugin like "Better Search Replace."

Step 6: Finalize

  1. Remove Local Database:

    • Once you've confirmed that your site is working well on the remote database, you can remove the local database.

  2. Review and Optimize:

    • Take a moment to review your site and ensure that all functionality is working as expected. Additionally, you may want to optimize your database tables.

By following these steps, you should successfully migrate your WordPress database from a local MariaDB server to a remote one. Always make sure to have backups before making any significant changes to your website.

Back to top