mysql 转储_使用转储和还原进行迁移 - Azure Database for MySQL | Microsoft Docs

您现在访问的是微软AZURE全球版技术文档网站，若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站，请访问 https://docs.azure.cn.

使用转储和还原将 MySQL 数据库迁移到 Azure Database for MySQLMigrate your MySQL database to Azure Database for MySQL using dump and restore

10/30/2020

本文内容

适用于：

Azure Database for MySQL-单服务器

Azure Database for MySQL-灵活服务器

本文介绍了在 Azure Database for MySQL 中备份和还原数据库的两种常见方式This article explains two common ways to back up and restore databases in your Azure Database for MySQL

从命令行转储和还原(使用 mysqldump)Dump and restore from the command-line (using mysqldump)

使用 PHPMyAdmin 转储和还原Dump and restore using PHPMyAdmin

有关将数据库迁移到 Azure Database for MySQL 的详细信息和用例，请参阅 数据库迁移指南 。You can also refer to Database Migration Guide for detailed information and use cases about migrating databases to Azure Database for MySQL. 本指南提供的指导旨在将 MySQL 迁移成功规划和执行到 Azure。This guide provides guidance that will lead the successful planning and execution of a MySQL migration to Azure.

准备阶段Before you begin

若要逐步执行本操作方法指南，需要具备以下条件：To step through this how-to guide, you need to have:

已在计算机上安装 mysqldump 命令行实用程序。mysqldump command-line utility installed on a machine.

MySQL Workbench 或其他用于执行转储和还原命令的第三方 MySQL 工具。MySQL Workbench or another third-party MySQL tool to do dump and restore commands.

提示

如果希望迁移数据库大小超过 1 TB 的大型数据库，则可能需要考虑使用支持并行导出和导入的社区工具(如 mydumper/myloader)。If you are looking to migrate large databases with database sizes more than 1 TBs, you may want to consider using community tools like mydumper/myloader which supports parallel export and import.

转储和还原的常见用例Common use-cases for dump and restore

最常见的用例包括：Most common use-cases are:

从其他托管服务提供商移动 -大多数托管服务提供商可能由于安全原因无法提供对物理存储文件的访问权限，因此，逻辑备份和还原是迁移的唯一选择。Moving from other managed service provider - Most managed service provider may not provide access to the physical storage file for security reasons so logical backup and restore is the only option to migrate.

从本地环境或虚拟机进行迁移 -Azure Database for MySQL 不支持还原物理备份，这会使逻辑备份和还原成为唯一的方法。Migrating from on-premises environment or Virtual machine - Azure Database for MySQL doesn't support restore of physical backups which makes logical backup and restore as the ONLY approach.

将 备份存储从本地冗余迁移到异地冗余存储 Azure Database for MySQL 允许在服务器创建过程中将本地冗余存储或异地冗余存储配置为仅允许进行备份。Moving your backup storage from locally redundant to geo-redundant storage - Azure Database for MySQL allows configuring locally redundant or geo-redundant storage for backup is only allowed during server create. 预配服务器以后，不能更改备份存储冗余选项。Once the server is provisioned, you cannot change the backup storage redundancy option. 若要将备份存储从本地冗余存储移到异地冗余存储，只需要转储和还原选项。In order to move your backup storage from locally redundant storage to geo-redundant storage, dump and restore is the ONLY option.

从备用存储引擎迁移到 InnoDB -Azure Database for MySQL 仅支持 InnoDB 存储引擎，因此不支持备用存储引擎。Migrating from alternative storage engines to InnoDB - Azure Database for MySQL supports only InnoDB Storage engine, and therefore does not support alternative storage engines. 如果表配置了其他存储引擎，请确保先将它们转换为 InnoDB 引擎格式，再迁移到 Azure Database for MySQL。If your tables are configured with other storage engines, convert them into the InnoDB engine format before migration to Azure Database for MySQL.

例如，如果有使用 MyISAM 表的 WordPress 或 WebApp，在将这些表还原到 Azure Database for MySQL 之前，首先通过将这些表迁移到 InnoDB 格式的方式转换格式。For example, if you have a WordPress or WebApp using the MyISAM tables, first convert those tables by migrating into InnoDB format before restoring to Azure Database for MySQL. 使用子句 ENGINE=InnoDB 设置创建新表时所用的引擎，然后在还原之前将数据传输到兼容表中。Use the clause ENGINE=InnoDB to set the engine used when creating a new table, then transfer the data into the compatible table before the restore.

INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns

重要

若要避免任何兼容性问题，请确保转储数据库时，源和目标系统上所使用的 MySQL 版本相同。To avoid any compatibility issues, ensure the same version of MySQL is used on the source and destination systems when dumping databases. 例如，如果现有 MySQL 服务器版本是 5.7，那么应迁移到配置为运行版本 5.7 的 Azure Database for MySQL 中。For example, if your existing MySQL server is version 5.7, then you should migrate to Azure Database for MySQL configured to run version 5.7. 在 Azure Database for MySQL 中，mysql_upgrade 命令不起作用，也不受支持。The mysql_upgrade command does not function in an Azure Database for MySQL server, and is not supported.

如果需要跨 MySQL 版本进行升级，应先将低版本数据库转储或导出到自己环境中更高版本的 MySQL 中。If you need to upgrade across MySQL versions, first dump or export your lower version database into a higher version of MySQL in your own environment. 然后运行 mysql_upgrade再尝试迁移到 Azure Database for MySQL 中。Then run mysql_upgrade, before attempting migration into an Azure Database for MySQL.

性能注意事项Performance considerations

若要优化性能，请在转储大型数据库时留意这些注意事项：To optimize performance, take notice of these considerations when dumping large databases:

转储数据库时，请使用 mysqldump 中的 exclude-triggers 选项。Use the exclude-triggers option in mysqldump when dumping databases. 从转储文件中排除触发器，避免在还原数据期间触发触发器命令。Exclude triggers from dump files to avoid the trigger commands firing during the data restore.

使用 single-transaction 选项，将事务隔离模式设置为 REPEATABLE READ 并在转储数据之前将 START TRANSACTION SQL 语句发送到服务器。Use the single-transaction option to set the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. 在单个事务中转储多个表会在还原过程中占用一些额外的存储空间。Dumping many tables within a single transaction causes some extra storage to be consumed during restore. 选项 single-transaction 和 lock-tables 互斥，因为 LOCK TABLES 导致所有挂起的事务均为隐式提交。The single-transaction option and the lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly. 若要转储大型表，请结合使用选项 single-transaction 和 quick。To dump large tables, combine the single-transaction option with the quick option.

使用其中包含多个 VALUE 列表的 extended-insert 多行语法。Use the extended-insert multiple-row syntax that includes several VALUE lists. 这可使转储文件较小并在重新加载文件时加快插入。This results in a smaller dump file and speeds up inserts when the file is reloaded.

转储数据库时，使用 mysqldump 中的 order-by-primary 选项，以便按主键顺序编写数据脚本。Use the order-by-primary option in mysqldump when dumping databases, so that the data is scripted in primary key order.

转储数据时，使用 mysqldump 中的 disable-keys 选项，以便在加载前，禁用外键约束。Use the disable-keys option in mysqldump when dumping data, to disable foreign key constraints before load. 禁用外键检查可提高性能。Disabling foreign key checks provides performance gains. 启用约束并在加载后验证数据，确保引用完整性。Enable the constraints and verify the data after the load to ensure referential integrity.

适当时使用已分区表。Use partitioned tables when appropriate.

并行加载数据。Load data in parallel. 避免太多将导致达到资源限制的并行度，并通过使用 Azure 门户中提供的指标监视资源。Avoid too much parallelism that would cause you to hit a resource limit, and monitor resources using the metrics available in the Azure portal.

转储数据库时，使用 mysqlpump 中的 defer-table-indexes 选项，以便在加载表数据后创建索引。Use the defer-table-indexes option in mysqlpump when dumping databases, so that index creation happens after tables data is loaded.

使用 mysqlump 中的 skip-definer 选项可以省略视图和存储过程的 CREATE 语句中的 definer 和 SQL SECURITY 子句。Use the skip-definer option in mysqlpump to omit definer and SQL SECURITY clauses from the create statements for views and stored procedures. 重新加载转储文件时，它会创建使用默认 DEFINER 和 SQL SECURITY 值的对象。When you reload the dump file, it creates objects that use the default DEFINER and SQL SECURITY values.

将备份文件复制到 Azure blob/存储，并在其中执行还原，这应该比通过 Internet 执行还原要快得多。Copy the backup files to an Azure blob/store and perform the restore from there, which should be a lot faster than performing the restore across the Internet.

在 Azure Database for MySQL 目标服务器上创建数据库Create a database on the target Azure Database for MySQL server

在要迁移数据的 Azure Database for MySQL 目标服务器上创建一个空数据库。Create an empty database on the target Azure Database for MySQL server where you want to migrate the data. 使用 MySQL Workbench 或 mysql.exe 等工具创建数据库。Use a tool such as MySQL Workbench or mysql.exe to create the database. 数据库名称可与包含转储数据的数据库名称相同，或可以创建一个不同名称的数据库。The database can have the same name as the database that is contained the dumped data or you can create a database with a different name.

若要获取连接，请在 Azure Database for MySQL 的“概述”中找到连接信息。To get connected, locate the connection information in the Overview of your Azure Database for MySQL.

将连接信息添加到 MySQL Workbench。Add the connection information into your MySQL Workbench.

准备目标 Azure Database for MySQL 服务器以实现快速数据加载Preparing the target Azure Database for MySQL server for fast data loads

若要准备目标 Azure Database for MySQL 服务器以实现快速数据加载，需要更改以下服务器参数和配置。To prepare the target Azure Database for MySQL server for faster data loads, the following server parameters and configuration needs to be changed.

max_allowed_packet – 设置为 1073741824(即 1 GB)，以防止由于长行而引起的溢出问题。max_allowed_packet – set to 1073741824 (i.e. 1GB) to prevent any overflow issue due to long rows.

slow_query_log – 设置为“关闭”以关闭慢速查询日志。slow_query_log – set to OFF to turn off the slow query log. 这将消除数据加载过程中由慢速查询日志记录导致的开销。This will eliminate the overhead caused by slow query logging during data loads.

query_store_capture_mode –设置为 "无" 以关闭查询存储。query_store_capture_mode – set to NONE to turn off the Query Store. 这将消除由查询存储的采样活动导致的开销。This will eliminate the overhead caused by sampling activities by Query Store.

innodb_buffer_pool_size – 在迁移期间从门户的定价层纵向扩展服务器到 32 vCore 内存优化 SKU，以增大 innodb_buffer_pool_size。innodb_buffer_pool_size – Scale up the server to 32 vCore Memory Optimized SKU from the Pricing tier of the portal during migration to increase the innodb_buffer_pool_size. 只能通过纵向扩展 Azure Database for MySQL 服务器的计算来增大 innodb_buffer_pool_size。Innodb_buffer_pool_size can only be increased by scaling up compute for Azure Database for MySQL server.

innodb_io_capacity 和 innodb_io_capacity_max - 从 Azure 门户中的服务器参数更改为 9000，以提高 IO 利用率，从而优化迁移速度。innodb_io_capacity & innodb_io_capacity_max - Change to 9000 from the Server parameters in Azure portal to improve the IO utilization to optimize for migration speed.

innodb_write_io_threads 和 innodb_write_io_threads - 从 Azure 门户中的服务器参数更改为 4 以加快迁移速度。innodb_write_io_threads & innodb_write_io_threads - Change to 4 from the Server parameters in Azure portal to improve the speed of migration.

纵向扩展存储层 – 随着存储层的增加，Azure Database for MySQL 服务器的 IOP 会逐渐增加。Scale up Storage tier – The IOPs for Azure Database for MySQL server increases progressively with the increase in storage tier. 为了更快地加载，可能需要增加存储层以增加预配的 IOP。For faster loads, you may want to increase the storage tier to increase the IOPs provisioned. 请记住，存储只能纵向扩展，而不能横向扩展。Please do remember the storage can only be scaled up, not down.

迁移完成后，可以将服务器参数和计算层配置还原为以前的值。Once the migration is completed, you can revert back the server parameters and compute tier configuration to its previous values.

使用 mysqldump 实用工具进行转储和还原Dump and restore using mysqldump utility

使用 mysqldump 从命令行创建备份文件Create a backup file from the command-line using mysqldump

若要备份本地服务器或虚拟机中的现有 MySQL 数据库，请运行以下命令：To back up an existing MySQL database on the local on-premises server or in a virtual machine, run the following command:

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

需要提供的参数包括：The parameters to provide are:

[uname] 数据库用户名[uname] Your database username

[pass] 数据库密码(请注意，-p 和密码之间没有空格)[pass] The password for your database (note there is no space between -p and the password)

[dbname] 数据库名称[dbname] The name of your database

[backupfile.sql] 数据库备份的文件名[backupfile.sql] The filename for your database backup

[--opt] mysqldump 选项[--opt] The mysqldump option

例如，若要将 MySQL 服务器上名为“testdb”的数据库(用户名为“testuser”且无密码)备份到文件 testdb_backup.sql，请使用以下命令。For example, to back up a database named 'testdb' on your MySQL server with the username 'testuser' and with no password to a file testdb_backup.sql, use the following command. 该命令将 testdb 数据库备份到名为 testdb_backup.sql 的文件中，该文件包含重新创建数据库所需的所有 SQL 语句。The command backs up the testdb database into a file called testdb_backup.sql, which contains all the SQL statements needed to re-create the database. 确保用户名“testuser”至少对转储表具有 SELECT 特权，对转储视图具 有 SHOW VIEW 权限，对转储触发器至少具有 TRIGGER 权限，如果未使用 --single transaction 选项，则至少具有 LOCK TABLES 权限。Make sure that the username 'testuser' has at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used.

GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'testuser'@'hostname' IDENTIFIED BY 'password';

现在，运行 mysqldump 以创建 testdb 数据库的备份Now run mysqldump to create the backup of testdb database

$ mysqldump -u root -p testdb > testdb_backup.sql

若要在数据库中选择特定的表进行备份，列出表名，用空格隔开。To select specific tables in your database to back up, list the table names separated by spaces. 例如，若仅从“testdb”备份 table1 和 table2 两个表，请遵循此示例：For example, to back up only table1 and table2 tables from the 'testdb', follow this example:

$ mysqldump -u root -p testdb table1 table2 > testdb_tables_backup.sql

若要一次性备份多个数据库，请使用 --database 切换并列出数据库名(用空格隔开)。To back up more than one database at once, use the --database switch and list the database names separated by spaces.

$ mysqldump -u root -p --databases testdb1 testdb3 testdb5 > testdb135_backup.sql

使用命令行或 MySQL Workbench 还原 MySQL 数据库Restore your MySQL database using command-line or MySQL Workbench

创建目标数据库后，可以使用 mysql 命令或 MySQL Workbench 将数据从转储文件还原到新创建的特定数据库。Once you have created the target database, you can use the mysql command or MySQL Workbench to restore the data into the specific newly created database from the dump file.

mysql -h [hostname] -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

在此示例中，将数据还原到在 Azure Database for MySQL 目标服务器上新创建的数据库中。In this example, restore the data into the newly created database on the target Azure Database for MySQL server.

下面是一个示例，说明如何将此 mysql 用于单一服务器：Here is an example for how to use this mysql for Single Server :

$ mysql -h mydemoserver.mysql.database.azure.com -u myadmin@mydemoserver -p testdb < testdb_backup.sql

下面是有关如何使用此 mysql 实现 灵活的服务器 的示例：Here is an example for how to use this mysql for Flexible Server :

$ mysql -h mydemoserver.mysql.database.azure.com -u myadmin -p testdb < testdb_backup.sql

使用 PHPMyAdmin 转储和还原Dump and restore using PHPMyAdmin

按照以下步骤使用 PHPMyadmin 转储并还原数据库。Follow these steps to dump and restore a database using PHPMyadmin.

备注

对于单一服务器，用户名必须采用以下格式： ' '， username@servername 但对于灵活服务器，只需使用 "用户名"，如果使用 " username@servername " 灵活的服务器，连接将失败。For single server, the username must be in this format , 'username@servername' but for flexible server you can just use 'username' If you use 'username@servername' for flexible server, the connection will fail.

使用 PHPMyadmin 进行导出Export with PHPMyadmin

若要导出，可以使用可能已安装在本地环境中的常用工具 phpMyAdmin。To export, you can use the common tool phpMyAdmin, which you may already have installed locally in your environment. 使用 PHPMyAdmin 导出 MySQL 数据库：To export your MySQL database using PHPMyAdmin:

打开 phpMyAdmin。Open phpMyAdmin.

选择数据库。Select your database. 单击左侧列表中的数据库名称。Click the database name in the list on the left.

单击“导出”链接。Click the Export link. 这将显示一个新页面，可查看数据库转储情况。A new page appears to view the dump of database.

在“导出”区域中，单击“全选”链接，选择数据库中的表。In the Export area, click the Select All link to choose the tables in your database.

在 SQL 选项区域中，单击适当的选项。In the SQL options area, click the appropriate options.

单击“另存为文件”选项及相应的压缩选项，然后单击“执行”按钮。Click the Save as file option and the corresponding compression option and then click the Go button. 将出现一个对话框，提示在本地保存该文件。A dialog box should appear prompting you to save the file locally.

使用 PHPMyAdmin 进行导入Import using PHPMyAdmin

导入数据库的方法与导出类似。Importing your database is similar to exporting. 执行以下操作：Do the following actions:

打开 phpMyAdmin。Open phpMyAdmin.

在 phpMyAdmin 设置页中，单击“添加”可添加 Azure Database for MySQL 服务器。In the phpMyAdmin setup page, click Add to add your Azure Database for MySQL server. 提供连接详细信息和登录信息。Provide the connection details and login information.

创建适当命名的数据库，并在屏幕左侧选中该数据库。Create an appropriately named database and select it on the left of the screen. 若要重写现有数据库，请单击数据库名称，选中所有表名称旁边的复选框，再选择“删除”以删除现有表。To rewrite the existing database, click the database name, select all the check boxes beside the table names, and select Drop to delete the existing tables.

单击“SQL”链接，显示可在其中键入 SQL 命令或上传 SQL 文件的页面。Click the SQL link to show the page where you can type in SQL commands, or upload your SQL file.

使用“浏览”按钮查找数据库文件。Use the browse button to find the database file.

单击“执行”按钮，导出备份、执行 SQL 命令并重新创建数据库。Click the Go button to export the backup, execute the SQL commands, and re-create your database.

已知问题Known Issues

有关已知问题、提示和技巧，我们建议你查看技术社区博客。For known issues, tips and tricks, we recommend you to look at our techcommunity blog.

后续步骤Next steps

若要详细了解如何将数据库迁移到 Azure Database for MySQL，请参阅数据库迁移指南。For more information about migrating databases to Azure Database for MySQL, see the Database Migration Guide.

如果希望迁移数据库大小超过 1 TB 的大型数据库，则可能需要考虑使用支持并行导出和导入的社区工具(如 mydumper/myloader)。If you are looking to migrate large databases with database sizes more than 1 TBs, you may want to consider using community tools like mydumper/myloader which supports parallel export and import.