mysql backup

Les fichiers des db se trouvent (ubuntu 8.04)

/var/lib/mysql/

On peut simplement faire une copie des fichiers mais il faut mettre un lock write avant.

C'est ce que fait le script mysqlhotcopy

Syntaxe :

A exécuter comme root

mysqlhotcopy metar_2009 -u meteo -p '' --addtodest /tmp/mysqlbackup

mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

--addtodest

Do not rename target directory (if it exists); merely add files to it.

--dryrun, -n

Report actions without performing them.

--method=command

The method for copying files (cp or scp).

--password=password, -ppassword

The password to use when connecting to the server. Note that the password value is not optional for this option, unlike for other MySQL programs. You can use an option file to avoid giving the password on the command line.

--user=user_name, -u user_name

The MySQL user name to use when connecting to the server.

6.1. Database Backups

This section summarizes some general methods for making backups.

Making Backups by Copying Files

MyISAM tables are stored as files, so it is easy to do a backup by copying files. To get a consistent backup, do aLOCK TABLES on the relevant tables, followed by FLUSH TABLES for the tables. See Section 12.4.5, “LOCK TABLES and UNLOCK TABLES Syntax”, and Section 12.5.6.3, “FLUSH Syntax”. You need only a read lock; this allows other clients to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup.

Making Delimited-Text File Backups

To create a text file containing a table's data, you can use SELECT * INTO OUTFILE 'file_name' FROMtbl_name. The file is created on the MySQL server host, not the client host. For this statement, the output file cannot already exist because allowing files to be overwritten would constitute a security risk. See Section 12.2.8, “SELECT Syntax”. This method works for any kind of data file, but saves only table data, not the table structure.

To reload the output file, use LOAD DATA INFILE or mysqlimport.

Making Backups with mysqldump or mysqlhotcopy

Another technique for backing up a database is to use the mysqldump program or the mysqlhotcopy script.mysqldump is more general because it can back up all kinds of tables. mysqlhotcopy works only with some storage engines. (See Section 4.5.4, “mysqldump — A Database Backup Program”, and Section 4.6.9, “mysqlhotcopy — A Database Backup Program”.)

Create a full backup of your database:

shell> mysqldump --tab=/path/to/some/dir --opt db_name

Or:

shell> mysqlhotcopy db_name /path/to/some/dir

You can also create a binary backup simply by copying all table files (*.frm, *.MYD, and *.MYI files), as long as the server isn't updating anything. The mysqlhotcopy script uses this method. (But note that these methods do not work if your database contains InnoDB tables. InnoDB does not necessarily store table contents in database directories, and mysqlhotcopy works only for MyISAM and ISAM tables.)

For InnoDB tables, it is possible to perform an online backup that takes no locks on tables; see Section 4.5.4, “mysqldump — A Database Backup Program”.