0

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

0

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

  • sqldump could have other problem? what if the file export got UTF-8 problem? the characters got distorted because of the exported files char incompatible. I think the safest is to copy the whole "mysql-5.0.27-win32" folder as I am doing, but there are many files that we don't need since we did not add colums or create new table, we just simply insert some data. And I am not sure 100% which files should I copy. – kiti Jul 13 '14 at 5:37
0

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.

  • mysql 5 or 6 is relly picky, if i did a bit wrong then the whole DB will be locked up or something like that. The 5.0 is very straight forword, you can copy the database file around & it still work ok – kiti Jul 13 '14 at 12:55
  • Not sure what you mean by this exactly. If you shut down the database, yes, you can copy and paste files, but nowadays, people tend to work on the assumption that if you have a deployed app, (a) - it should work 24x7 and (b) you shouldn't require a shutdown to perform a backup. If you can do that with your app, fine, but posters here are trying to help you by pointing out the real problems with older versions (no support, fewer features). – Vérace Jul 13 '14 at 13:03
  • rather than using sqldumb, i am thinking to use GDrive or DropBox to copy the all DB file into hte internet storage, 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? – kiti Jul 13 '14 at 13:59
  • & not sure dropbox solution is feasible. – kiti Jul 13 '14 at 14:00
  • you don't need to stop mysql to copy the mysql file. Besides, if you use sqldumb, then ? will you run the sqldumb all the time? confused? – kiti Jul 14 '14 at 2:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.