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

backupMySQL

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?)

Best Answer

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

Related Question