Rechercher dans le manuel MySQL
7.2 Database Backup Methods
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 Section 30.2, “MySQL Enterprise Backup Overview”.
Making Backups with mysqldump
The mysqldump program can make backups. It can back up all kinds of tables. (See Section 7.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 7.3.1, “Establishing a Backup Policy”.
Making Backups by Copying Table Files
MyISAM tables can be backed up by copying table files
(*.MYD
, *.MYI
files, and
associated *.sdi
files). To get a consistent
backup, stop the server or lock and flush the relevant tables:
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 Section 13.3.6, “LOCK TABLES and UNLOCK TABLES Syntax”, and Section 13.7.7.3, “FLUSH Syntax”.
You can also create a binary backup simply by copying the 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.)
For an example of this backup method, refer to the export and import example in Section 13.2.5, “IMPORT TABLE Syntax”.
Making Delimited-Text File Backups
To create a text file containing a table's data, you can use
SELECT * INTO OUTFILE
'
. 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
Section 13.2.10, “SELECT Syntax”. This method works for any kind of data
file, but saves only table data, not the table structure.
file_name
' FROM
tbl_name
Another way to create text data files (along with files containing
CREATE TABLE
statements for the
backed up tables) is to use mysqldump with the
--tab
option. See
Section 7.4.3, “Dumping Data in Delimited-Text Format with mysqldump”.
To reload a delimited-text data file, use
LOAD DATA
or
mysqlimport.
Making Incremental Backups by Enabling the Binary Log
MySQL supports incremental backups: You must start the server with
the --log-bin
option to enable
binary logging; see Section 5.4.4, “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 FLUSH LOGS
. 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 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”. The next time you do a
full backup, you should also rotate the binary log using
FLUSH LOGS
or mysqldump
--flush-logs. See Section 4.5.4, “mysqldump — A Database Backup Program”.
Making Backups Using Replication Slaves
If you have performance problems with your master server while making backups, one strategy that can help is to set up replication and perform backups on the slave rather than on the master. See Section 17.3.1, “Using Replication for Backups”.
If you are backing up a slave replication server, you should back
up its master info and relay log info repositories (see
Section 17.2.4, “Replication Relay and Status Logs”) when you back up the slave's
databases, regardless of the backup method you choose. This
information is always needed to resume replication after you
restore the slave's data. If your slave is replicating
LOAD DATA
statements, you should
also back up any SQL_LOAD-*
files that exist
in the directory that the slave uses for this purpose. The slave
needs these files to resume replication of any interrupted
LOAD DATA
operations. The location
of this directory is the value of the
--slave-load-tmpdir
option. If the
server was not started with that option, the directory location is
the value of the tmpdir
system
variable.
Recovering Corrupt Tables
If you have to restore MyISAM
tables that have
become corrupt, try to recover them using
REPAIR TABLE
or myisamchk
-r first. That should work in 99.9% of all cases. If
myisamchk fails, see
Section 7.6, “MyISAM Table Maintenance and Crash Recovery”.
Making Backups Using a File System Snapshot
If you are using a Veritas file system, you can make a backup like this:
From a client program, execute
FLUSH TABLES WITH READ LOCK
.From another shell, execute
mount vxfs snapshot
.From the first client, execute
UNLOCK TABLES
.Copy files from the snapshot.
Unmount the snapshot.
Similar snapshot capabilities may be available in other file systems, such as LVM or ZFS.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-backup-methods.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.