MySQL question [ Edited ]

[#1]

Hi there!

I have existing customer running MySQL on our FAS system I’d like to confirm what kind of information are required if SnapCreator can backup and restore MySQL database.

From my old knowledge, MySQL cannot hot-backup databases but things have changed.  If anyone can advice us what we need to ask customer prior to propose SnapCreator it would be very helpful.  They installed MySQL in 2005 that their MySQL version could be old.

Also we haven’t confirmed what kind of storage engine they are using.

Best Regards,

Hideki Masu

[#2]

Hi Masu,

SnapCreator works great with MySQL. This is what we do:

connect to DB

flush tables with read lock

snapshot

unlock tables

The only thing MySQL offers is read-only mode but because SnapCreator / NetApp snapshots are so fast the DB will only be in read-only mode for a second. In the event of an error regardless what, SnapCreator will do an "unlock tables" to make sure the DB is at worst returned to read/write mode asap and automatically.

Also we connect to DB over TCP so we can backup MySQL remotely without our Agent.

Restore is a manual process. SnapCreator can restore data ie: snap restore or snapvault restore but does not do anything with the DB whihc is why we say restore is manual.

If you need anything else let us know.

Keith

[#3]

Hi and welcome to the forums!

Did you look (as an alternative) at snapshot integration via Zmanda?

http://www.netapp.com/us/library/technical-reports/tr-3656.html

Regards,

Radek

[#4]

Thank you  for your answer.

I see.  I understood that SnapCreator will lock the table, not the DB, that if there are thousands of tables exist in one database, it may cause the lag to lock all tables.  In that perspective, what could be the practical or actually experienced number of tables exsits in a database for using SnapCreator?

Regards,

hideki masu

[#5]

Thanks Radek,

Yes, we onced thought to integrate with Zmanda, however they are quite small company far out from Tokyo area and our resellers do not have relationship with them that we've decided it could be troublesome to work with them.

Thanks for your advice anyway.

Hideki Masu

[#6]

Hi Hideki Masu,

Thanks for working on MySQL customer.

From MySQL 6.x.x have the online backup with storage engine co-ordination. Normally we recommend the MySQL enterprise release for the Our customers.The latest enterprise version is 5.1.43,

Please do consider the following for Snapcreator with MySQL module

1. Is the database spread across more than on volume

2. Is the MySQL do require DR solution

3. How frequent the backup schedule

4. Is the customer require client and server module for the backup

5. The database size doesn't matter for snapcreator, any way you can get that details.

As keith suggest, to maintain the consistency we are using read only mode, take the snapshot and release the lock. To Keep the datas in consistency during backup

For Snapcreator the type of storage engine is doesn't mater.

snapcreator for MySQL works very well now.

Rgds,

Karthikeyan.N

[#7]

Keith,

Does this process of going into read-only mode apply to both the MyiSAM and InnoDB engines for MySQL?  Thanks!

-Dave

[#8]

Hi Dave,

I have forwarded this to our MySQL expert (internal) and we will get back to you soon

As for MySQL we do a read lock so that lock applies to all DBs running in the instance, not sure if that answers question. We can additionally put more than 1 DB in backup mode if that is desired.

Keith

[#9]

Hi Hideki Masu,

For the snapcreator we need to consider the following

1. The storage engine is doesn't matter any storage engine OK, Because SC internally using "snap" API

2. For the database level backup keep each database in separate volume

3. For consistent backup, During backup particular database will be in readonly mode for a while.

4. Still the MySQL not have the Hot backup with the current release, but it's in the plan to release later.

5. If the MySQL database installed in 2005, it might be 3.x.x, which can be backup using mysqldump, readonly way.

keith: It will be great to do the multiple database backup at a time, if they are in different volume and different storage controller. This feature will help the MySQL Customer to take all the MySQL databases at time and can restore each one separately without depend on other database files.

Regards

Karthikeyan.N

.