If you have a WordPress website then you know that it is based on PHP and MySQL. Operating a WordPress website doesn’t normally require any interaction with the database itself. The database is self sustaining which means it functions automatically while making modifications through the WordPress backend. There may come a time, however, when you may need to learn how to merge two WordPress databases using MySQL.
Let’s say you have a database backup of your website from last year. This backup contains information that your current website doesn’t have. Now you want to merge, or combine, the database backup from last year with your current database.
Another example could be if you have a database of users from one website and you want to combine that database with another website that has a database of users.
HOW TO MERGE TWO WORDPRESS DATABASES USING MYSQL – PHPMYADMIN
The first step to learn how to merge two WordPress databases using MySQLÂ is to export the database that you plan to merge with your new one. Your hosting provider should have an option to get into phpMyAdmin from cPanel.
When you log into phpMyAdmin you need to select the database that you want to export and then use the “Export” tab. Use the “Quick” export method, use the “SQL” format, and click the “Go” button.
When you have the database downloaded you will need to upload that database using FTP to the location of the website where you want to combine this database with.
HOW TO MERGE TWO WORDPRESS DATABASES USING MYSQLÂ – FTP
Next you need to use an FTP client such as FileZilla to upload your exported database to a directory on the hosting account where your current website is. Your web host should provide you with FTP information such as the domain name, username, password, and port number. This information is required to login via FTP.
You will need an FTP client
Your WordPress site files should be located in the public_html directory. You can upload your database to this directory.
When you have your database uploaded you can exit your FTP session. Feel free to log back in to delete that database file after you have finished learning how to merge two WordPress databases using MySQL. You most likely won’t need that file after you have finished combining your two databases. If you want to keep that there as a backup then that is fine as well.
HOW TO MERGE TWO WORDPRESS DATABASES USING MYSQLÂ – MYSQL COMMANDS
When you have your database uploaded to your public_html directory you will now need to use MySQL to execute some statements that will merge your two databases together. Running MySQL statements requires SSH access and most hosts don’t have it enabled as default. You will have to enable SSH through your host by using cPanel or another admin interface where you can enable SSH.
make sure you backup your original database before continuing
When you have SSH enabled you will need to use a SSH client such as WinSCP or Putty to gain access. When you have gained access via SSH you will need to connect to MySQL from the command line by using the following command:
mysql -u database_username -p
Make sure that you replace “database_username” with the username associated with the database that you are working with. Press “Enter” after you have issued the above command and it will ask to enter your password.
Next you need to issue the following command and press “Enter“:
REPLACE INTO database1.wp_links
Make sure that you replace “database1” with the name of the database that you are keeping and replace “wp_links” with the database table that you are merging. There is no easy way to merge an entire database with another one. You have to merge each table individually. Next you need to issue this command and press “Enter”:
SELECT * FROM database2.wp_links;
Make sure that you replace “database2” with the name of the database that your are copying (merging) over and replace “wp_links” with the database table that you are merging.
You will have to continue this process for every table that you want to merge. When you use “REPLACE INTO”, the database that you are merging over overwrites the existing data while keeping the old data.
So let’s say you have a database with 100 users and you have another database with 200 users. The database with 200 users has 50 users that are already in the database with 100 users. If you merge the database of 200 users with the database of 100 users the resulting database would have 250 users. This is because the 50 users in the 2 databases were duplicates and were overwritten.
When you are finished merging your database tables you can exit MySQL and your SSH session. Examine your website afterwards to ensure everything merged smoothly.
