使用傾印和還原來將 MySQL 資料庫移轉至適用於 MySQL 的 Azure 資料庫Migrate your MySQL database to Azure Database for MySQL using dump and restore

適用于: 適用於 MySQL 的 Azure 資料庫-單一伺服器 適用於 MySQL 的 Azure 資料庫-彈性伺服器

本文將說明兩個常見方法,讓您可在適用於 MySQL 的 Azure 資料庫中用來備份和還原資料庫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

您也可以參閱 資料庫移轉指南 ,以取得有關將資料庫移轉至適用於 MySQL 的 Azure 資料庫的詳細資訊和使用案例。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:

提示

如果您想要遷移資料庫大小超過 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. 瞭解 如何遷移大型 MySQL 資料庫Learn How to migrate large MySQL databases.

傾印和還原的常見使用案例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.

  • 從內部部署環境或虛擬機器進行遷移 -適用於 MySQL 的 Azure 資料庫不支援還原實體備份,這會將邏輯備份和還原做為唯一的方法。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.

  • 您的備份儲存體從本機冗余移至異地冗余儲存體-適用於 MySQL 的 Azure 資料庫可讓您在伺服器建立期間,為備份設定本機冗余或異地多餘的儲存體。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 -適用於 MySQL 的 Azure 資料庫僅支援 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 引擎格式,然後再移轉至適用於 MySQL 的 Azure 資料庫。If your tables are configured with other storage engines, convert them into the InnoDB engine format before migration to Azure Database for MySQL.

    例如,如果您的 WordPress 或 WebApp 使用 MyISAM 資料表,請先藉由移轉至 InnoDB 格式來轉換這些資料表,然後再還原至適用於 MySQL 的 Azure 資料庫。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 版,則您應該將適用於 MySQL 的 Azure 資料庫設定為執行 5.7 版。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. mysql_upgrade 命令在適用於 MySQL 伺服器的 Azure 資料庫中無法運作,因此並不支援。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,之後再嘗試移轉至適用於 MySQL 的 Azure 資料庫。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.
  • 使用 mysqlpump 中的 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/存放區,並從該處執行還原,這樣應該會比在網際網路上執行還原更快。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.

在目標適用於 MySQL 伺服器的 Azure 資料庫上建立資料庫Create a database on the target Azure Database for MySQL server

在您要移轉資料的目標適用於 MySQL 伺服器的 Azure 資料庫上建立空白資料庫。Create an empty database on the target Azure Database for MySQL server where you want to migrate the data. 使用 MySQL 工作臺或 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.

若要連線,在適用於 MySQL 之 Azure 資料庫的 [概觀] 中找到連線資訊。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 連接字串

準備目標適用於 MySQL 的 Azure 資料庫伺服器,以快速載入資料Preparing the target Azure Database for MySQL server for fast data loads

若要準備目標適用於 MySQL 的 Azure 資料庫伺服器,以更快速地載入資料,則必須變更下列伺服器參數和設定。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. Innodb_buffer_pool_size 只能藉由擴大適用於 MySQL 的 Azure 資料庫伺服器的計算來增加。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.
  • 擴大儲存層 – 適用於 MySQL 的 Azure 資料庫伺服器的 IOPS 會隨著儲存層的成長而逐漸增加。Scale up Storage tier – The IOPs for Azure Database for MySQL server increases progressively with the increase in storage tier. 如需更快速的載入速度,您可以增加儲存層以增加佈建的 IOPS。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 伺服器上使用者名稱為 'testuser' 且無密碼之名為 'testdb' 的資料庫備份到 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 許可權、顯示傾印的視圖、傾印觸發程式的觸發程式,以及如果未使用--single transaction 選項時鎖定資料表。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 來建立資料庫的備份 testdbNow 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]

在此範例中,將資料還原至目標適用於 MySQL 伺服器的 Azure 資料庫上新建立的資料庫。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 ',如果您使用 ' 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:

  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 安裝] 分頁中,按一下 [新增] 以新增適用於 MySQL 伺服器的 Azure 資料庫。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.

已知問題Known Issues

如需已知問題、秘訣與技巧,建議您查看我們的技術社群部落格 (英文)。For known issues, tips and tricks, we recommend you to look at our techcommunity blog.

後續步驟Next steps