We all know that how it is really difficult to manage all the different databases of separate WordPress Installations, and to save time most people create a Back-Up of Home Directory but never notices that keeping a huge back-up file on your shared hosting server is against the terms and policies.
On the other hand taking individual back-ups is not so easy if you are managing Multiple Blogs or Sites. so to over come the issue and helping a little bit out of this situation you can go for this simple process and can feel lighter.
Why should we create websites with single database and how can we create different WordPress websites with single common MySQL Database?
Case 1: Having Limited No. of MySQL Database with the Hosting Plans you own and needs to Run Multiple Sites.
Case 2: Having Unlimited No. of MySQL Database with the Hosting Plans you own but still needs an easy way to manage and Back-up Database.
Requirements: Cpanel Control and Limited Knowledge of C.C.P.E. (cut copy paste edit)
>> Although, most of the people uses Unlimited Hosting Packages that includes Unlimited Space, Unlimited Bandwidth, Unlimited Databases, Unlimited Emails etc… (See Such Plans)
This procedure is useful for the person who is using limited Hosting plans, which gives them limited MySQL Database (usually equals to the no. of sub-domains allowed). Well, if a person is getting such hosting plans then, what is the benefit of creating multiple sites with Single Database and why it is needed to have multiple sites installed when they are providing no. of MySQL Database along with the No. of Add-On Domains?
Note: This Method can be used only for Installing WordPress Manually.
Let’s Start Discussing the Case 1
Well in this case when a person is having a limited number of MySQL Databases and requires a more number of installations than the no. of MySQL Databases provided, then there is a solution to it. With the following steps provided below you can install more than one WordPress installation in a single MySQL Database.
1. Download a fresh copy of WordPress Setup file from WordPress.Org or Click Now or else if you are already running any WordPress Site on same Server that you can just copy the whole folder to the new WordPress Site’s Directory (either a Sub-Domain or a Sub-Directory).
2. Replace the WP-Config.php file (current) with the WP-Config.php (older) file of the same WordPress installation whose MySQL Database needs to be shared.
3. Edit the WP-Config.php file from your Cpanel and make just one change i.e. search for this
/**
* WordPress Database Table prefix.
* You can have multiple installations in one database if you give each a unique
* prefix. Only numbers, letters, and underscores please!
*/
$table_prefix = 'wp_';
And replace {$table_prefix = ‘wp_’;} with {$table_prefix = ‘wp_nn_’;} where nn_ will be the new table prefix i.e. you can replace nn_ with any number or alphabet or any combination but remember to end with an underscore[ _ ].
4. As soon as you made the changes save the WP-Config.php file.
5. Now, Run the New Site URL i.e. the Sub-Domains address or Sub-Directory Address, which will take you to the installation form.
6. Simply add the lo-gin details to be used by Administrator of the WordPress Site and then, re-login the WP Dashboard to manage the New WordPress Site with the Same Shared MySQL Database.
Now, Let’s Discuss the Case 2 i.e. having Un-limited no. of MySQL Databases
The first question that comes in the mind is why we are required to install Multiple WordPress Sites with common MySQL Database although when we have UN-limited no. of MySQL Databases. Well, secret behind this is very simple and you can read out some features that Single MySQL Database will provide…
1. You don’t need to worry about the other Database back –ups when taking a Back-up of one which contains many WordPress Sites installed in it.
2. If you are using WordPress Sites with not much updating regularly and using it as an easy alternative to a Static Website than you must go for Single MySQL Database as it not only reduces the counts of no. of Databases as well as keeps most of the data at one place.
3. Most Important: If you are tensed with Login-in with different accounts and/or memorizing different accounts credentials than you have the option of using same Database with the same Users Login to all Sites installed in one Single MySQL Database.
Frequently Asked Questions (Single Database Installation)MySQL
Question: Which kind of Site can be added – Sub-Domain, Sub Directory or Main Installation?
Answer: Any kind of Site can be merged into Single Database whether a Sub Domain or a Sub Directory or a Main Installation
Question: How many sites can be added to a Single Database?
Answer: You can add as many as you desire but remember to maintain their Table initials different than others.
Question: Can we extract a Single Site from Multiple Database Tables installed in Single Database?
Answer: Yes, you can extract any single WordPress site out of many installed on same Database
Question: Can all sites have same User Login?
Answer: Yes, you can use the same Users Login tables for each additional installation or for selected installations.
Question: Can previous sites be added to this new Database sharing method?
Answer: Yes, you can bring your Old WordPress Installations to this Single MySQL Database Installation.
Question: Can I remove single site from the multiple installations?
Answer: Yes, you can remove single site installation from multiple installations and also its unique Database Tables.
Warnings: Single Database Installation
Keep the WordPress Installation Files safe as same Database configuration is used for all and one mistake can harm all.
If the Database is deleted all sites will stop working immediately. So be careful at the time of Single site removal (must check out the Tables First) or consult first.
Still something is troubling you than feel free to ask and really it will be a pleasure to help you Guys!
Ok, so after wp_ is changed to wp_nn_ the new files names with -nn- appear in the database, how does one import the new mysql into them. There is still only the one database, running import browse to the sql file go only gets one an error message the new won’t go into the old which we didn’t want anyway. How do we get the new data to import into the new files?
Hello Robert,
You will be required to clear the Database tables of nn (auto-generated ) of the new WordPress Installation.
After that you are required to change the Table names from the previous Database (can be done by notepad editor) individually and then upload the database file.
It is showing the error because while uploading the database it can see the old tables already there and due to security it cannot replace them. so you need to remove them manually (as they must be empty) and surely replace the name of the Tables from the old Database file which you need to import…. this will give you old username and login, comments, posts, pages, categories, tags, etc…
If you are only willing to add old Posts than you can do this for taxonomies tables and Posts tables only.
you can leave a message if there still anything else…
After the new nn databases files are created how so I import the new sql to them and not the old one?