This section summarizes some general methods for making backups.
Making a Hot Backup with MySQL Enterprise Backup
Customers of MySQL Enterprise Edition can use the MySQL Enterprise Backup product to do physical backups of entire
instances or selected databases, tables, or both. This product includes features for incremental and compressed backups. Backing up the physical database files makes restore much faster than logical techniques such as the mysqldump
command. InnoDB
tables are copied using a
hot backup mechanism. [Ideally, the InnoDB
tables should represent a substantial majority of the data.] Tables from other storage engines are copied using a warm backup mechanism. For an overview of the MySQL Enterprise Backup product, see
MySQL Enterprise Backup Overview.
Making Backups with mysqldump
The mysqldump program can make backups. It can back up all kinds of tables. [See Section 1.4, “Using mysqldump for Backups”.]
For InnoDB
tables, it is possible to perform an online backup that takes no locks on tables using the --single-transaction
option to mysqldump. See Section 1.3.1, “Establishing a Backup
Policy”.
Making Backups by Copying Table Files
For storage engines that represent each table using its own files, tables can be backed up by copying those files. For example, MyISAM
tables are stored as files, so it is easy to do a backup by copying files [*.frm
, *.MYD
, and *.MYI
files]. To get a consistent backup, stop the server or lock and flush the relevant tables:
FLUSH TABLES tbl_list WITH READ LOCK;
You need only a read lock; this enables other clients to continue to query the tables while you are making a copy of the files in the database directory. The flush is needed to ensure that the all active index pages are written to disk before you start the backup. See LOCK TABLES and UNLOCK TABLES Statements, and FLUSH Statement.
You can also create a binary backup
simply by copying all table files, as long as the server isn't updating anything. [But note that table file copying methods do not work if your database contains InnoDB
tables. Also, even if the server is not actively updating data, InnoDB
may still have modified data cached in memory and not flushed to disk.]
Making Delimited-Text File Backups
To create a text file containing a table's data, you can use
mysqldump
2' FROM mysqldump
3. The file is created on the MySQL server host, not the client host. For this statement, the output file cannot already exist because permitting files to be overwritten constitutes a security risk. See SELECT Statement. This method works for any kind of data file, but saves only table
data, not the table structure.
Another way to create text data files [along with files containing mysqldump
4 statements for the backed up tables] is to use mysqldump with the mysqldump
5 option. See
Section 1.4.3, “Dumping Data in Delimited-Text Format with mysqldump”.
To reload a delimited-text data file, use mysqldump
6 or mysqlimport.
Making Incremental Backups by Enabling the Binary Log
MySQL supports incremental backups: You must start the server with the mysqldump
7 option to enable binary logging; see The Binary Log. The binary log files provide you with the information you need to replicate changes to the
database that are made subsequent to the point at which you performed a backup. At the moment you want to make an incremental backup [containing all changes that happened since the last full or incremental backup], you should rotate the binary log by using mysqldump
8. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental
backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained in Section 1.5, “Point-in-Time [Incremental] Recovery”. The next time you do a full backup, you should also rotate the binary log using
mysqldump
8 or mysqldump --flush-logs. See mysqldump — A Database Backup Program.
Making Backups Using Replicas
If you have performance problems with your source server while making backups, one strategy that can help is to set up replication and perform backups on the replica rather than on the source. See Chapter 2, Using Replication for Backups.
If you are backing up a replica server, you should back up its source info and relay log info repositories [see Relay Log and
Replication Metadata Repositories] when you back up the replica's databases, regardless of the backup method you choose. These information files are always needed to resume replication after you restore the replica's data. If your replica is replicating mysqldump
6 statements, you should also back up any InnoDB
1 files that exist in the directory that the replica uses for this purpose. The replica needs these
files to resume replication of any interrupted mysqldump
6 operations. The location of this directory is the value of the InnoDB
3 system variable. If the server was not started with that variable set, the directory location is the value of the
InnoDB
4 system variable.
Phục hồi bảng tham nhũng
Nếu bạn phải khôi phục các bảng MyISAM
đã bị hỏng, hãy cố gắng khôi phục chúng bằng cách sử dụng InnoDB
6 hoặc myisamchk -r trước. Điều đó sẽ hoạt động trong 99,9% của tất cả các trường hợp. Nếu Myisamchk thất bại, hãy xem Phần & NBSP; 1.6, Bảo trì bảng Myisam và phục hồi sự cố.myisamchk -r first. That should work in 99.9% of all cases. If
myisamchk fails, see Section 1.6, “MyISAM Table Maintenance and Crash Recovery”.
Tạo bản sao lưu bằng cách sử dụng ảnh chụp nhanh hệ thống tệp
Nếu bạn đang sử dụng hệ thống tệp Veritas, bạn có thể tạo bản sao lưu như sau:
Từ một chương trình khách hàng, thực thi
InnoDB
7.Từ một vỏ khác, thực thi
InnoDB
8.Từ máy khách đầu tiên, thực thi
InnoDB
9.Sao chép các tập tin từ ảnh chụp nhanh.
Mở ra ảnh chụp nhanh.
Khả năng chụp nhanh tương tự có thể có sẵn trong các hệ thống tệp khác, chẳng hạn như LVM hoặc ZFS.