Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

If there are only MyISAM databases, the backup and restoration is possible by simply copying pasting the data folder, regardless of the versions?

But if there are INNODB databases, and we are sure that the source and target versions will be identical, is this copy-and-paste method possible?

It'd be better to avoid typing any command to do the backup because the person who is going to do this never used command-line before.

share|improve this question

4 Answers 4

up vote 9 down vote accepted

If we had to avoid the command line always, we would never have made it to the moon. Either get another astronaut or train harder.

If you are talking about a live database in production, the values could be in flux, so it would be unreliable to do backups of a database, in any database type, by simply copying a bunch of files.

You can get a safe backup of the entire mysql database, including system tables, this way:

mysqldump --all-databases > mysqlbackup.sql

The backup is in the file mysqlbackup.sql - this can be backed up to tape, dvd, etc.

restore is the opposite of this, done with redirecting the backup file into mysql client.

mysql < mysqlbackup.sql

For either of these commands you may need -u for the username having admin rights, and -p to specify the password.

share|improve this answer

If you absolutely must use copy and paste the MySQL services at both ends must be stopped, you must have the same configurations and you must copy all the log files as well as the data folders.

However, as has already been pointed out, that is a poor way to go. It's no big deal to create a script and tell the person who needs to run it what command to type. Assuming that person is clever enough to be able to log on to the machine I would expect him/her to have no trouble running one command.

Another option is to create a master-slave replica and have it keep itself up to date without the user needing to do anything. Of course you will still need to monitor the system to ensure it's working properly but you should be doing that anyway.

share|improve this answer
    
Thank you very much for your information. –  bobo Nov 4 '09 at 4:34
    
+1 to the master/slave replica. After you implement you just sit back and watch the packets flow, and best part is that it will sync automagically. –  Nathan Adams Nov 4 '09 at 4:47

I wouldn't recommend copying and pasting files at all. If the person isn't technical how will you be sure the mysql daemon is properly stopped?

If this is for a non-technical users you really should take the time to build a script that calls mysqldump to properly creates a backup for them.

share|improve this answer

You really should never consider a replication slave to be a good backup solution. For example, what if something happens where a database is dropped or if other harmful SQL is executed on the master? Those statements will just replicate down to your slave and perform the same actions, making it impossible to roll back to a point before the event happened.

Follow labradort's advice and at least use mysqldump.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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