Which files in the “MySQL-5.0.27-win32” folder do i need to backup?

Question

Ok, I am using a pretty old MYSQL "mysql-5.0.27-win32" and the file and the structure maybe different from the latest Mysql (mysql 7).

Here is the current structure of of mysql-5.0.27-win32:

In "mysql-5.0.27-win32" folder:
bin
data
Docs
Embedded
examples
include
lib
mysql-test
scripts
share
sql-bench
my-huge.ini
my-innodb-heavy-4G.ini
my-large.ini
my-medium.ini
my-small.ini

In "data" folder:
mysql
mydatabase
...some-other-database that I don't touch...
my-PC.err
my-PC.pid
ib_logfile0 ---> this is a file, not a folder
ib_logfile1 ---> this is a file, not a folder
ibdata1 ---> this is a file, not a folder

the "mydatabase" folder has file such as .frm, .MYD, .MYI for each of its table, 
"mydatabase" also has "db.opt"

Ok, here is the scenarios. I finished developing my WebApp using mysql-5.0.27-win32in my Local Desktop PC. Now it is a time to deploy my app.

To deploy, I copy the whole mysql-5.0.27-win32 folder (which contains the current DB structure + some basic data) into Virtual Server.

So, there will be 2 same folders the original mysql-5.0.27-win32 in the Local PC & the copy mysql-5.0.27-win32 in the Virtual Server. At the beginning, these 2 folders are exactly the same. They have the same DB structures (ie same number of tables & columns in the tables) & same data stored in the tables.

From now on, my website will store user data directly to the copy mysql-5.0.27-win32 folder in Virtual Server (VPS). But if the VPS got shutdown then I will lose all data.

Thus I want to back up daily the "mydatabase" from the copy mysql-5.0.27-win32.

So which file in the "MySQL-5.0.27-win32" folder do i need to backup so that when I put them into the original mysql-5.0.27-win32, then I can have exactly the same info as I have in the copy mysql-5.0.27-win32?

Supposed that we know while data file to copy then: is there any problem for this solution “Backup mysql database by copying the data file even when MYSQL server is running”?

(pls see the subsequent question: "Backup mysql database by copying the data file even when MYSQL server is running" solution! Is there any problem for this backup solution?)

Answer 1

You are using a very old, end-of-life, and unsupported version of MySQL.

There is not exactly a correct answer to your question, because it is impossible in many cases to safely back up or clone a MySQL server that is still running.

The only absolutely safe way to copy data using this method is by stopping the mysqld process, gracefully, and copying all of the files, for all of the databases.

If you are using MyISAM, which, again, you almost certainly should not, it is possible to copy a single database by copying all of the files in that database's named directory, but the tables have to be locked and flushed, or the backup will either not contain the most recent changes, or will be corrupt.

If you are using InnoDB, it is almost impossible to copy only one database from one server by copying files, unless you use the "transportable tablespaces" feature introduced in MySQL 5.6.

The correct answer to the question is "don't do it this way."

The correct solution is to use mysqldump to make the backup, which does not copy files at all. Instead, it creates a file containing the SQL statements necessary to recreate your tables and reinsert the data that was in those tables when the backup was made.

The output of mysqldump is also human-readable, so you can actually examine what's in the backup.

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

Answer 2

OK - you have written

Ok, here is the scenarios. I finished developing my WebApp using mysql-5.0.27-win32in my Local Desktop PC. Now it is a time to deploy my app.

Now, just because you developed on 5.0.27, doesn't mean that you have to deploy on that particular version. Normally (and I stress, normally != always), MySQL is fairly backwards compatible.

My suggestion to you is that you deploy to a more current version (i.e. 5.6.19). I also suggest that when you deploy your app, you make all tables are InnoDB and not MyISAM. MyISAM tables are problematic for backing up (but so are InnoDB on Windows).

You will, though, still have a problem with backups. Xtrabackup, the only open source MySQL hot backup solution isn't (well, not really) available for Windows. I'm guessing that your reference to a "Virtual Server" is to do with Microsoft's proprietary product in this space and not some generic Virtual Server. My other suggestion was going to be to deploy to Linux (if possible).

As others have written, you should really move your level of MySQL to something supported this decade.

[EDIT in response to comments]

You wrote:

since if using sqldumb then u still miss some data if u just backup 1 time a day, but if u use dropbox it will cop instantly. But not sure dropox can recognize when teh file got modified?

You appear not to completely understand what goes on when backing up a database.

If you simply copy the files to Dropbox a few times a day, you'll end up with INCONSISTENT data - the db will still be being updated while being copied leading to the worst of all possible worlds - you believing that you have a backup strategy when, in fact, your backup data is garbage.

You can, of course, halt your server several times a day and copy the files over to Dropbox, but then your app will unavailable at those times - far more than it would have to be using mysqldump.

There are many ways to ensure you have consistent fallback databases in the event that your primary server blows up.

One is to have backups - but they must be consistent. With MyISAM tables, there is only one way to do this mysqldump --lock-tables. If you have InnoDB tables, you must use --single-transaction. If you have a mixture, then use --single-transaction (this is what I've understood from here).

You could look at replication to a standby machine and if you decide to use Unix (i.e. Linux), you have many more options available to you - i.e. LVM snapshots and other goodies. Mydumper is also available under Unix, as is mysqlhotcopy.