Analyse de la méthode de commande de sauvegarde et de restauration de la bibliothèque Mysql (article long)

Concernant la méthode de sauvegarde et de restauration de la base de données mysql, nous parlons ici d'abord de l'outil de sauvegarde : mysqlhotcopy Utilisez l'outil mysqlhotcopy pour effectuer une sauvegarde rapide Puis restaurez les données et utilisez enfin la commande MySQL ; vous devez exporter la table de base de données. Vous devez lire cet article pour une introduction détaillée.

1. Sauvegarde des données

Sauvegardez régulièrement la base de données pour minimiser les pertes lorsque des situations inattendues se produisent.

1. Utilisez la commande mysqldump pour sauvegarder

mysqldump est un outil de sauvegarde de base de données fourni par MySQL Lorsque la commande mysqldump est exécutée, la base de données est sauvegardée dans un fichier texte qui contient. plusieurs instructions CREATE et INSERT, utilisez ces instructions pour recréer des tables et insérer des données ;

[Utilisez mysqldump pour sauvegarder une seule base de données]

mysqldump -u user -h host -p password dbname>filename.sql

[Utilisez mysqldump pour sauvegarder les données spécifiées ; tables dans la base de données]

mysqldump -u user -h host -p password dbname[tbname,[tbname…]]>filename.sql

[Utilisez mysqldump pour sauvegarder plusieurs bases de données]

mysqldump -u user -h host -p password --databases[dbname,[dbname…]]>filename.sql

Après avoir utilisé le paramètre --databases, vous devez spécifier le nom d'au moins une base de données , et utilisez des espaces pour séparer plusieurs bases de données ;

【Sauvegardez toutes les bases de données du système】

mysqldump -u user -h host -p password --all-databases>filename.sql

Conseil : si vous sauvegardez sur le serveur et que les tables sont toutes MyISAM, vous devriez envisager d'utiliser mysqlhotcopy, car il peut être sauvegardé et restauré plus rapidement ;

2 Copiez directement l'intégralité du répertoire de la base de données

Étant donné que la table MySQL est enregistrée sous forme de fichier, vous pouvez la copier directement. le répertoire de stockage et les fichiers de la base de données MySQL pour la sauvegarde.

Il s'agit d'une méthode de sauvegarde simple, rapide et efficace pour maintenir la cohérence de la sauvegarde, vous devez effectuer l'opération LOCK TABLES sur les tables concernées avant la sauvegarde, puis effectuer FLUSH TABLES sur la table ( assurez-vous que toutes les pages d'index actives sont écrites sur le disque). Cela permet aux autres utilisateurs de continuer à interroger la table pendant que les fichiers du répertoire de la base de données sont copiés.

Cette méthode n'est pas applicable aux tables du moteur de stockage InnoDB. Il est préférable de restaurer les données sauvegardées sur la même version du serveur en utilisant cette méthode. Différentes versions peuvent ne pas être compatibles

3 Utilisez l'outil mysqlhotcopy pour sauvegarder rapidement

mysqlhotcopy ; est un script Perl.

ne peut être exécuté que sur la machine où se trouve le répertoire de la base de données, et ne peut sauvegarder que les tables de type MyISAM et ARCHIVE

2. Restauration des données

1. Commandes MySQL à restaurer

mysql -u username -p [dbname] < filename.sql

Remarque : Si le fichier filename.sql est un fichier créé par l'outil mysqldump et contient une instruction de création de base de données, vous n'avez pas besoin de spécifier le nom de la base de données lors de l'exécution de

Si vous êtes connecté au serveur MySQL, vous pouvez également utiliser la commande source pour importer des fichiers SQL.

source filename

Conseil : Avant d'exécuter la commande source, vous devez utiliser l'instruction use pour sélectionner la base de données. Sinon, des erreurs se produiront pendant le processus de récupération ;

2. Copiez directement dans le répertoire de la base de données

Si la base de données est sauvegardée en copiant le fichier de la base de données, vous pouvez directement copier le fichier sauvegardé. au répertoire de données MySQL pour la restauration.

Lors de la restauration via cette méthode, les numéros de version majeurs de la base de données de sauvegarde et du serveur de base de données à restaurer doivent rester les mêmes. Et cette méthode n'est valable que pour les tables du moteur MyISAM, mais pas pour les tables du moteur InnoDB

Fermez le service MySQL avant d'effectuer la restauration, écrasez le fichier ou le répertoire de sauvegarde avec le répertoire de données MySQL ; et démarrez le service MySQL.

Pour les systèmes d'exploitation Linux/Unix, après avoir copié le fichier, vous devez remplacer l'utilisateur ou le groupe d'utilisateurs du fichier par l'utilisateur et le groupe sur lesquels MySQL est exécuté. Généralement, l'utilisateur est MySQL et le groupe l'est également. mysql;

3. Récupération rapide de mysqlhotcopy

Les fichiers sauvegardés par mysqlhotcopy peuvent également être utilisés pour restaurer la base de données. Lorsque le serveur MySQL cesse de fonctionner, copiez les fichiers de base de données sauvegardés dans le fichier. emplacement où MySQL est stocké (dossier de données MySQL). Redémarrez simplement le service MySQL.

Si vous effectuez cette opération en tant qu'utilisateur root, vous devez spécifier le propriétaire du fichier de base de données

chown -R mysql.mysql /var/lib/mysql/dbname
cp -R /usr/backup/test usr/local/mysql/data

Après avoir exécuté cette instruction, redémarrez le serveur et MySQL sera restauré sur le état de sauvegarde

Astuce : si la base de données à restaurer existe déjà, la récupération ne peut réussir qu'après avoir utilisé l'instruction DROP pour supprimer la base de données existante. De plus, les différentes versions de MySQL doivent être compatibles ; 🎜>

3. Migration de base de données

La migration de base de données consiste à déplacer des données d'un système à un autre. La migration des données se produit pour les raisons suivantes :

1.相同版本的MySQL数据库之间的迁移

相同版本的MySQL数据库之间的迁移就是指在主版本号相同的MySQL数据库之间进行数据库移动。

举例：

将www.abc.com主机上的MySQL数据库全部迁移到www.bcd.com主机上：

mysqldump -h www.abc.com -u root -ppassword dbname | mysql -h www.bcd.com -uroot -ppassword

说明：

mysqldump导入的数据直接通过管道符|，传给mysql命令导入到主机www.bcd.com数据库中，dbname为需要迁移的数据库名称，如果需要迁移全部的数据库，可以使用参数 --all-databases

2.不同版本的MySQL数据库之间的迁移

MySQL服务器升级的时候，需要先停止服务，然后卸载旧版本，并安装新版本MySQL，这种更新方法很简单，如果想保留旧版本中的用户访问控制信息，需要备份MySQL中的mysql数据库，在新版本MySQL安装完成之后，重新读入mysql备份文件中的信息；

旧版本与新版本的字符集不同时，迁移过程需要对默认字符集进行修改，不然可能无法正常显示结果；

对于InnoDB引擎的表，一般只能使用mysqldump工具将数据导出，然后使用mysql命令导入到目标服务器上。

从新版本向旧版本迁移数据的时候，需要特别的小心，最好使用mysqldump命令导出，然后导入目标数据库中；

3.不同数据库之间的迁移

数据库迁移可以使用一些工具，例如在Windows系统下，可以使用MyODBC实现MySQL和SQL Server之间的迁移。

MySQL官方提供的工具MySQL Migration Toolkit也可以实现在不同数据库间进行数据迁移；

4.表的导出和导入

MySQL数据库中的数据可以导出成SQL文本文件、xml文件或者HTML文件。

1.使用SELECT…INTO OUTFILE导出文本文件

MySQL数据库导出数据的时候，允许使用包含导出定义的SELECT语句进行数据导出操作。该文件被创建到服务器主机上，因此必须拥有文件写入权限（FILE权限），才能使用此语法。

语法格式：

SELECT columnlist FORM table WHERE condition INTO OUTFILE ‘filename’ [OPTIONS]

[OPTIONS]选项：

●　FIELDS TERMINATED BY ‘value’

●　FIELDS [OPTIONALLY] ENCLOSED BY ‘value’

●　FIELDS ESCAPED BY ‘value’

●　LINES STARTING BY ‘value’

●　LINES TERMINATED BY ‘value’

说明：filename不能是一个已经存在的文件；

OPTIONS部分语法包括FIELDS部分的语法和LINES子句，其可能的取值有：

FIELDS TERMINATED BY ‘value’：

设置字段之间的分隔字符，可以为单个或者多个字符，默认情况下为制表符‘\t’

FIELDS [OPTIONALLY] ENCLOSED BY ‘value’：

设置字段的包围字符，只能为单个字符，如果使用了OPTIONALLY，则只有CHAR和VERCHAR等字符数据字段被包括；

FIELDS ESCAPED BY ‘value’：

设置如何写入或者读取特殊字符，只能为单个字符，即设置转义字符，默认值为“\”

LINES STARTING BY ‘value’：

设置每行数据开始字符，可以为单个或者多个，默认不使用任何字符

LINES TERMINATED BY ‘value’：

设置每行数据结尾的字符 可以为单个或者多个字符，默认值为‘\n’;

注意：FIELDS和LINES两个子句是可选的，如果同时指定，FIELDS必须位于LINES的前面；

2.使用mysqldump命令导出文本文件

mysqldump工具不仅可以将数据导出为包含CREATE、INSERT的SQL文件，也可以导出为纯文本文件；

mysqldump -T path-u root -p dbname [tables] [OPTIONS]

--OPTIONS选项：

●　--fields-terminated-by=value

●　--fields-enclosed-by=value

●　--fields-optionally-enclosed-by=value

●　--fields-escaped-by=value

●　--lines-terminated-end-by=value

说明：只有指定了T参数才可以导出为纯文本文件；path表示导出数据的目录；tables为指定要导出表的名称；如果不指定，将导出数据库dbname中的所有的表；

[options]取值：

●　--fields-terminated-by=value：

        设置字段之间的分隔字符，可以为单个或者多个字符，默认情况下为制表符‘\t’

●　--fields-enclosed-by=value：

         设置字段的包围字符；

●　--fields-optionally-enclosed-by=value：

         设置字段的包围字符，只能为单个字符，如果使用了OPTIONALLY，则只有CHAR和VERCHAR等字符数据字段被包括；

●　--fields-escaped-by=value：

         控制如何写入或者读取特殊字符，只能为单个字符，及设置转义字符，默认为反斜线“\”;

●　--lines-terminated-end-by=value：

        设置每行数据结尾的字符，可以为单个或者多个字符，默认值为‘\n’

3.使用MySQL命令导出文本文件

mysql是一个功能丰富的工具命令，使用MySQL还可以在命令行模式下执行SQL指令将查询结果导入到文本文件中。相比mysqldump，MySQL工具导出的结果可读性更强。

如果MySQL服务器是一个单独的机器，用户是在一个client上进行操作，用户要把数据导入到client机器上，可以使用mysql -e语句；

使用MySQL导出数据文本文件语句的基本格式如下：

mysql -u root -p --execute=”SELECT语句” dbname > filename.txt

使用MySQL命令还可以指定查询结果的显示格式：

如果某行记录字段很多，可能一行不能完全显示，可以使用--vartical参数，将每条记录分为多行显示；

【将查询结果导出到HTML文件中】

mysql -u root -p --html --execute=”SELECT语句” dbname > filename.html

【将查询结果导出到xml文件中】

mysql -u root -p --xml --execute=”SELECT语句” dbname > filename.xml

4.使用LOAD DATA INFILE方式导入文本文件

LOAD DATA INFILE 语句用于高速的从一个文本文件中读取行，并装入一个表中。文件名称必须为文字字符串。

LOAD DATA INFILE ‘路径+文件名.txt’ INTO TABLE tablename [OPTIONS] [IGNORE number LINES]

 注意：如果导出的.txt文件中指定了一些特殊的字符，因此还原语句中也要指定这些字符，以确保还原之后数据的完整性和正确性；

--OPTIONS选项

●　FIELDS TERMINATED BY ‘value’

●　FIELDS [OPTIONALLY] ENCLOSED BY ‘value’

●　FIELDS ESCAPED BY ‘value’

●　LINES STARTING BY ‘value’

●　LINES TERMINATED BY ‘value’

可以看到LOAD DATA 语句中，关键字INFILE后面的filename文件为导入数据的来源；

tablename表示待导入的数据表名称；

OPTIONS部分语法包括FIELDS部分的语法和LINES子句，其可能的取值有：

FIELDS TERMINATED BY ‘value’：

设置字段之间的分隔字符，可以为单个或者多个字符，默认情况下为制表符‘\t’

FIELDS [OPTIONALLY] ENCLOSED BY ‘value’：

设置字段的包围字符，只能为单个字符，如果使用了OPTIONALLY，则只有CHAR和VERCHAR等字符数据字段被包括；

FIELDS ESCAPED BY ‘value’：

设置如何写入或者读取特殊字符，只能为单个字符，即设置转义字符，默认值为“\”

LINES STARTING BY ‘value’：

设置每行数据开始字符，可以为单个或者多个，默认不使用任何字符

LINES TERMINATED BY ‘value’：

设置每行数据结尾的字符 可以为单个或者多个字符，默认值为‘\n’;

[IGNORE number LINES]

选项表示忽略文件开始处的行数，number表示忽略的行数。执行LOAD DATA语句需要FILE权限；

5.使用mysqlimport命令导入文本文件

使用mysqlimport命令可以导入文本文件，并且不需要登录MySQL客户端。

使用mysqlimport语句需要指定所需的选项、导入的数据库名称以及导入的数据文件的路径和名称。

mysqlimport命令的基本语法如下：

mysqlimport -u root -p dbname filename.txt [OPTIONS]

[options]取值：

●　--fields-terminated-by=value：

         设置字段之间的分隔字符，可以为单个或者多个字符，默认情况下为制表符‘\t’

Quantity　--fields-enclosed-by=value：

　　　　　　　　　

Quantity　--fields-optionally-enclosed-by=value：

Définissez les caractères environnants du champ, qui ne peut être qu'un seul caractère. Si OPTIONALLY est utilisé, seuls les champs de données de caractères tels que CHAR et VERCHAR sont inclus

Quantity　--fields-escaped-by=value :

Contrôlez comment ; pour écrire ou lire des caractères spéciaux. Il ne peut s'agir que d'un seul caractère et définir le caractère d'échappement. La valeur par défaut est la barre oblique inverse "" ; 🎜> Définissez le caractère à la fin de chaque ligne de données, qui peut être un ou plusieurs caractères. La valeur par défaut est 'n'

Quantity　--ignore-lines=n

Ignorer. les n premières lignes du fichier de données ;

Remarque : la commande mysqlimport ne peut pas spécifier le nom de la table de la base de données importée. Le nom de la table de données est déterminé par le nom du fichier importé, c'est-à-dire le nom de la table de données. Le nom du fichier est utilisé comme nom de table. La table doit exister avant d'importer les données.

Recommandations associées :

Commande de sauvegarde et de restauration de la base de données MySQL

Mémo de commande de sauvegarde et de restauration de la base de données Mysql

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!