Exporting a MySQL database using mysqldump

You can run the mysqldump utility directly against your MySQL database, using whatever options you require. However, if you're exporting to import the data into a Cloud SQL database, then use the mysqldump utility with the following flags:

• --databases Specify an explicit list of databases to export. This list must not contain the system databases ( sys , mysql , performance_schema , and information_schema ).
• --hex-blob If your database contains any binary fields, then you must use this flag to ensure that your binary fields are imported correctly.
• --single-transaction Starts a transaction before running. Rather than lock the entire database, this lets mysqldump read the database in the current state, making for a consistent data dump.
• --routines To include stored procedures and functions.

• When using mysqldump version 8 or later to export MySQL databases versions earlier than 8:
  --column-statistics=0

  This flag removes the COLUMN_STATISTICS table from the database export to avoid the Unknown table 'COLUMN_STATISTICS' in information_schema (1109) error. For more information, see Diagnose issues .

It's also recommended to use the following flags:

• --no-autocommit
• --default-character-set=utf8mb4
• --master-data

From a machine with network connectivity to your MySQL server, run the following command:

   
mysqldump  
 \ 
  
-h  
 [ 
SOURCE_ADDR ] 
  
-P  
 [ 
SOURCE_PORT ] 
  
-u  
 [ 
USERNAME ] 
  
-p  
 \ 
  
--databases  
 [ 
DBS ] 
  
 \ 
  
--hex-blob  
 \ 
  
--no-autocommit  
 \ 
  
--default-character-set = 
utf8mb4  
 \ 
  
--master-data = 
 1 
  
 \ 
  
--single-transaction  
 \ 
  
--routines  
 \ 
  
 | 
  
gzip  
 \ 
  
 | 
  
gcloud  
storage  
cp  
-  
gs:// [ 
BUCKET_NAME ] 
/ [ 
DUMP_FILENAME ] 
.gz 

If the source of the migration is a Relational Database Service (RDS) for MySQL:

• The master-data property isn't supported.
• If your source database server supports GTID, then use the --set-gtid-purged=on property; otherwise, don't use this property.
• If you're using a manual dump to migrate your data, then perform the migration with GTID enabled.

This command might look like the following example:

   
mysqldump  
 \ 
  
-h  
 [ 
SOURCE_ADDR ] 
  
-P  
 [ 
SOURCE_PORT ] 
  
-u  
 [ 
USERNAME ] 
  
-p  
 \ 
  
--databases  
 [ 
DBS ] 
  
 \ 
  
--hex-blob  
 \ 
  
--no-autocommit  
 \ 
  
--default-character-set = 
utf8mb4  
 \ 
  
--set-gtid-purged = 
on  
 \ 
  
--single-transaction  
 \ 
  
--routines  
 \ 
  
 | 
  
gzip  
 \ 
  
 | 
  
gcloud  
storage  
cp  
-  
gs:// [ 
BUCKET_NAME ] 
/ [ 
DUMP_FILENAME ] 
.gz 

Also, you should configure RDS instances to retain binlogs longer. This command might look like the following example:

   
 # Sets the retention period to one week. 
  
call  
mysql.rds_set_configuration ( 
 'binlog retention hours' 
,  
 168 
 ) 
 ; 
 

Replace [PROPERTIES_IN_BRACKETS] with the following values: