November 26th, 2012, 02:02 PM
MySQL 5.5 or 5.6 - Question about Backup and Recovery
We are currently using Community version 5.0.51a and are thinking about going to MySQL 5.5 or MySQL 5.6 but I have a question about the ability to backup the database and recover it in using the higher version under a Multi Tenant evironment.
Currently every customer has their own MySQL database and within each database we have 8 different schemas. Now as we all know, Community version MySQL is free, and if one upgrades to MySQL 5.5 or 5.6 then one has to pay a Fee to Oracle for support. So we want to upgrade, and since we want access to many of the new abilities/functions...we will have to become Multi Tenant based. In other words we will have many customers within the same database, thus each customer will have there own set of those 8 different schemas each and thus if we had 12 customers per database that would mean 8 x 12 = 96 schemas.
So under community version of MySQL 5.0.51a we take a complete dump of the total database, and thus if we wanted to restore that customer we could simply restore everything. So what happens under a Multi Tenant case where we have 12 customers sharring the same database?
Under Oracle I would simple use RMAN, or take a total Export of the database using Data Pump. Then if I had a issue I could selectively Import using either RMAN or Data Pump utility to selectively import only the needed schemas that were damaged or whatever. Now you can not selectively restore individual schemas using Community version MySQL 5.0.51a...can you do that with either MySQL 5.5 or 5.6?
Further having many customers say (12) within a single database with (8) schemas each at a total of (96) schemas, the only way I know currently to restore the lost data would be to stand up another hardware to support the single 12 tenant database and then to export the lost schemas out of the new database and then drop and recreate and import the data schema back into the old database. Is this still the only way or is there a better or another way to do this in the higher MySQL 5.5 (GA) or MySQL 5.6 (Beta)?
Last edited by ByGoneYrs; November 26th, 2012 at 02:04 PM.
November 26th, 2012, 07:09 PM
In MySQL databases and schemas are the same thing, and you can't create a schema within a database. Do you actually mean that you have a separate database server instance for each client?
It wouldn't surprise me if the license has changed between 5.0 and 5.5, but 5.5 is still available for free. Yes, if you want official support you will have to pay, but that is true of all software, including 5.0.
November 27th, 2012, 07:21 AM
No my question is if in a MySQL database if you have it set up as Multi-Tenant, and there are multiple Schemas within that database, and you take a backup of the whole database and only want to restore just one database schema...can you do that Without setup a totally other database first and then copy the needed data from that new other database into the old one? Now in Oracle, you can take a export of the total database and just import from the .dmp file just the schema or maybe just a table back into the primary database at very little problems. There is no need to bring up another database to then copy the data from one database to another. So my question is in versions 5.5 or 5.6 have they fixed this?
BTW yes I know that we will have to pay for support and that is not a issue, BUT at this point I want to know if what I am asking is possible?
November 28th, 2012, 08:35 AM
I'm still not sure what you mean by "multi schemas within a database", but mysqldump does allow you to specify that you only want to dump specific databases or specific tables. However, that decision happens at export-time.
When you're importing a dump created by mysqldump there is no way that I know of to perform a partial import of the dump - ie: importing only certain databases or tables from the dump. I've never heard of any plans to add this feature.
There might be third party tools that let you perform a full export and partial import, but I'm not aware of any off the top of my head. (There are a lot of third party MySQL management tools).
November 28th, 2012, 09:16 AM
I am talking about Multi-Tenant, thus each customer of my company has 8 seperate schemas that relate to 8 different sets of code and are seperate fuctions and/or abilities/services. Thus each seperate schema has it's own sets of tables, indexes, views and etc....So if I make the MySQL database multi-tenant, that means each customer will have their own set of the 8 different schemas they us uniquely named to ID just the ones that belong to that specific customer and if I were to have 12 of these customer also in the same MySQL instance...then that means that it would be 8 schemas per customer/tenant and then multiply that by 12.
So it looks like the way to do what I want is to set up a Master - Master replication. Master 1 at the primary production site and Master 2 at the DR site. Then do a end of business day sync up from Master 1 to Master 2, then do a OS level SnapShot, then do a backup of Master 2 and put to tape, then do another Snapshot and keep 7+1 days of Snapshots online in our HP 3PAR storage. Thus we can if we loose the Primary site, we can roll everyone over to the DR Master 2 DBs, and if something happens and data is lost we can simply restore one of those snap shots or Tape backups to another VM and do a export/copy/restore of the lost or effected data.
If this was Oracle db instead of MySQL we could use RMAN and/or DataPump Export/Inport utility to do things alot better, but there is always multiple ways to do anything.
November 29th, 2012, 08:31 AM
If your secondary site is only for disaster recovery then I assume it won't be receiving any writes; you should look at master-slave replication instead. A master-slave configuration is less complicated to set up and maintain.