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

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

本文介绍了在 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

准备阶段Before you begin

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

如果希望迁移数据库大小超过 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. 并行转储和还原有助于大幅缩短大型数据库的迁移时间。Parallel dump and restore can help significantly reduce migration time for large databases.

使用常用工具Use common tools

使用常用的实用程序和工具(例如 MySQL Workbench 或 mysqldump)进行远程连接,并将数据还原到 Azure Database for MySQL 中。Use common utilities and tools such as MySQL Workbench or mysqldump to remotely connect and restore data into Azure Database for MySQL. 在具有 Internet 连接的客户端计算机上使用此类工具连接到 Azure Database for MySQL。Use such tools on your client machine with an internet connection to connect to the Azure Database for MySQL. 使用具有 SSL 加密的连接是最安全的做法,另请参阅在 Azure Database for MySQL 中配置 SSL 连接Use an SSL encrypted connection for best security practices, see also Configure SSL connectivity in Azure Database for MySQL. 迁移到 Azure Database for MySQL 时,无需将转储文件移到任何特殊的云位置。You do not need to move the dump files to any special cloud location when migrating to Azure Database for MySQL.

转储和还原的常见用途Common uses for dump and restore

在几个常见方案中,可使用 mysqldump、mysqlpump 等 MySQL 实用程序以将数据库转储和加载到 Azure MySQL 数据库。You may use MySQL utilities such as mysqldump and mysqlpump to dump and load databases into an Azure MySQL Database in several common scenarios. 在其他方案中,可改用导入和导出方法。In other scenarios, you may use the Import and Export approach instead.

  • 迁移整个数据库时请使用数据库转储。Use database dumps when you are migrating the entire database. 此建议适用于移动大量 MySQL 数据,或者要最小化实时站点或应用程序的服务中断的情况。This recommendation holds when moving a large amount of MySQL data, or when you want to minimize service interruption for live sites or applications.

  • 将数据加载到 Azure Database for MySQL 时,请确保数据库中的所有表都使用 InnoDB 存储引擎。Make sure all tables in the database use the InnoDB storage engine when loading data into Azure Database for MySQL. Azure Database for MySQL 仅支持 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-transactionlock-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-transactionquickTo 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.

使用 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 -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 

在 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 等工具创建数据库。Use a tool such as MySQL Workbench 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.

在 Azure 门户中找到连接信息

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

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(即 1GB),以防止由于长行而引起的溢出问题。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.

使用命令行或 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 -h mydemoserver.mysql.database.chinacloudapi.cn -u myadmin@mydemoserver -p testdb < testdb_backup.sql

使用 PHPMyAdmin 进行导出Export using 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:

  1. 打开 phpMyAdmin。Open phpMyAdmin.
  2. 选择数据库。Select your database. 单击左侧列表中的数据库名称。Click the database name in the list on the left.
  3. 单击“导出”链接。Click the Export link. 这将显示一个新页面,可查看数据库转储情况。A new page appears to view the dump of database.
  4. 在“导出”区域中,单击“全选”链接,选择数据库中的表。In the Export area, click the Select All link to choose the tables in your database.
  5. 在 SQL 选项区域中,单击适当的选项。In the SQL options area, click the appropriate options.
  6. 单击“另存为文件”选项及相应的压缩选项,然后单击“执行”按钮。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:

  1. 打开 phpMyAdmin。Open phpMyAdmin.
  2. 在 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.
  3. 创建适当命名的数据库,并在屏幕左侧选中该数据库。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.
  4. 单击“SQL”链接,显示可在其中键入 SQL 命令或上传 SQL 文件的页面。Click the SQL link to show the page where you can type in SQL commands, or upload your SQL file.
  5. 使用“浏览”按钮查找数据库文件。Use the browse button to find the database file.
  6. 单击“执行”按钮,导出备份、执行 SQL 命令并重新创建数据库。Click the Go button to export the backup, execute the SQL commands, and re-create your database.

后续步骤Next steps