Setting up the environment

To complete this tutorial, you need to set up your computing environment withthe following:

• A MySQL instance on Compute Engine (namedmysql-prod) to representyour production database server.
• An additional disk (namedmysql-prod-data) that's attached to yourproduction server for storing your production database.
• A copy of theEmployees database imported intomysql-prod to simulate the production database that you wantto clone.
• A MySQL instance on Compute Engine (namedmysql-test) to representyour testing database server. You clone your database onto this server.

The following diagram illustrates this architecture.

Create the production VM instance

To simulate a production environment, you set up a Compute Engine VMinstance running MySQL on Debian Linux.

The VM instance for this tutorial uses two disks: a 50 GB disk for the OS anduser accounts, and a 100 GB disk for database storage.

In Compute Engine, using separate disks offers noperformance benefits. Disk performance is determined by the total storage capacity of alldisks attached to an instance and by the total number of vCPUs on your VM instance.Therefore, the database and log file can reside on the same disk.

1. Open Cloud Shell.

   Open Cloud Shell

2. Set your preferred zone:

   ZONE=us-east1-bREGION=us-east1gcloudconfigsetcompute/zone"${ZONE}"

3. Create a Compute Engine instance:

   gcloudcomputeinstancescreatemysql-prod\--machine-type=n1-standard-2\--scopes=cloud-platform\--boot-disk-size=50GB\--boot-disk-device-name=mysql-prod\--create-disk="mode=rw,size=100,type=pd-standard,name=mysql-prod-data,device-name=mysql-prod-data"

   This command grants the instance full access to Google Cloud APIs,creates a 100 GB secondary disk, and attaches the disk to the instance.Ignore the disk performance warning because you don't need high performancefor this tutorial.

Set up the additional disk

The second disk attached to the production instance is for storing yourproduction database. This disk is blank, so you need to partition, format, andmount it.

1. In the Google Cloud console, go to theVM instances page.

   Go to the VM instances page

2. Make sure a green check markcheck is displayed next tothe name of yourmysql-prod instance, indicating that the instance isready.

3. Click theSSH button next to themysql-prod instance. The browseropens a terminal connection to the instance.

4. In the terminal window, display a list of disks attached to your instance:

   lsblk

   The output is the following:

   NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINTsda      8:0    0   50G  0 disk└─sda1   8:1    0   50G  0 part /sdb      8:16   0  100G  0 disk

   The disk namedsdb (100 GB) is your data disk.

5. Format thesdb disk and create a single partition with an ext4 file system:

   sudomkfs.ext4-m0-F-Elazy_itable_init=0,lazy_journal_init=0,discard\/dev/sdb

6. Create the MySQL data directory to be the mount point for the datadisk:

   sudomkdir-p/var/lib/mysql

7. To automatically mount the disk at the mount point you created, add an entryto the/etc/fstab file:

   echo"UUID=`sudo blkid -s UUID -o value /dev/sdb` /var/lib/mysql ext4 discard,defaults,nofail 0 2"\|sudotee-a/etc/fstab

8. Mount the disk:

   sudomount-av

9. Remove all files from the data disk so that it's free to be used by MySQLas a data directory:

   sudorm-rf/var/lib/mysql/*

Install the MySQL server

You need to download and install MySQL Community Edition. The MySQLdata directory is created on the additional disk.

1. In the SSH session connected tomysql-prod, download and install theMySQL configuration package:

   wgethttp://repo.mysql.com/mysql-apt-config_0.8.13-1_all.debsudodpkg-imysql-apt-config_0.8.13-1_all.deb

2. When you're prompted, select theMySQL Server & Cluster option, andthen selectmysql-5.7.

3. In the list, select theOk option to complete the configuration of thepackage.

4. Refresh the repository cache and install the mysql-community packages:

   sudoapt-getupdatesudoapt-getinstall-ymysql-community-servermysql-community-client

5. When you're warned that the data directory already exists, selectOk.

6. When you're prompted to provide a root password, create and enter apassword. Note the password or store it temporarily in a safe place.

Download and install the sample database

1. In the SSH session connected to themysql-prod instance, install git:

   sudoapt-getinstall-ygit

2. Clone theGitHub repository containing theEmployees database scripts:

   gitclonehttps://github.com/datacharmer/test_db.git

3. Change directory to the directory for theEmployees database script:

   cdtest_db

4. Run theEmployees database creation script:

   mysql-uroot-p-q <employees.sql

   When you're prompted, enter the root password that you created earlier.

5. To verify the sample database is functional, you can run a query that countsthe number of rows in theemployees table:

   mysql-uroot-p-e"select count(*) from employees.employees;"

   When you're prompted, enter the root password you that you created earlier.

   The output is the following:

   +----------+| count(*) |+----------+|   300024 |+----------+

Create the test VM instance

In this section, you create a MySQL VM instance namedmysql-test as thedestination for the cloned database. The configuration of this instance isidentical to the production instance. However, you don't create a second datadisk; instead, you attach the data disk later in this tutorial.

1. Open Cloud Shell.

   Open Cloud Shell

2. Create the test MySQL instance:

   gcloudcomputeinstancescreatemysql-test\--machine-type=n1-standard-2\--scopes=cloud-platform\--boot-disk-size=50GB\--boot-disk-device-name=mysql-test

   You can ignore the disk performance warning because you don't need highperformance for this tutorial.

Install the MySQL server on the test VM instance

You also need to download and install MySQL Community Edition ontothemysql-test VM instance.

1. In the SSH session connected tomysql-test, download and install theMySQL configuration package:

   wgethttp://repo.mysql.com/mysql-apt-config_0.8.13-1_all.debsudodpkg-imysql-apt-config_0.8.13-1_all.deb

2. When you're prompted, select theMySQL Server & Cluster option, andthen selectmysql-5.7.

3. In the list, select theOk option to complete the configuration of thepackage.

4. Refresh the repository cache and install the mysql-community packages:

   sudoapt-getupdatesudoapt-getinstall-ymysql-community-servermysql-community-client

5. When you're prompted to provide a root password, create and enter apassword. Note the password or store it temporarily in a safe place.

Cloning the database using Compute Engine disk snapshots

One way to clone a MySQL database running on Compute Engine is tostore the database on a separate data disk and use persistent disk snapshots tocreate a clone of that disk.

Persistent disk snapshots let you get a point-in-time copy of on-disk data. Scheduling disk snapshots isone way to automatically back up your data.

In this section of the tutorial, you do the following:

• Take a snapshot of the production server's data disk.
• Create a new disk from the snapshot.
• Mount the new disk onto the test server.
• Restart the MySQL server on the test instance so that the server uses thenew disk as a data disk.

The following diagram shows how a database is cloned by using disk snapshots.

Create the disk snapshot

1. Open Cloud Shell.

   Open Cloud Shell

2. Create a snapshot of your data disk in the same zone as the VM instance:

   gcloudcomputediskssnapshotmysql-prod-data\--snapshot-names=mysql-prod-data-snapshot\--zone="${ZONE}"

   After a few minutes, your snapshot is created.

Attach the disk snapshot to the test instance

You need to create a new data disk from the snapshot you created and then attachit to themysql-test instance.

1. Open Cloud Shell.

   Open Cloud Shell

2. Create a new persistent disk by using the snapshot of the production diskfor its contents:

   gcloudbetacomputediskscreatemysql-test-data\--size=100GB\--source-snapshot=mysql-prod-data-snapshot\--zone="${ZONE}"

3. Attach the new disk to yourmysql-test instance with read-writepermissions:

   gcloudcomputeinstancesattach-diskmysql-test\--disk=mysql-test-data--mode=rw

Mount the new data disk in Linux

To use the cloned data disk as the MySQL data directory, you need to stop theMySQL instance and mount the disk.

1. In the SSH session connected tomysql-test, stop the MySQL service:

   sudoservicemysqlstop

2. In the terminal window, display a list of disks attached to your instance:

   lsblk

   The output is the following:

   NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINTsda      8:0    0   50G  0 disk└─sda1   8:1    0   50G  0 part /sdb      8:16   0  100G  0 disk

   The disk namedsdb (100 GB) is your data disk.

3. Mount the MySQL data disk onto the MySQL data directory:

   sudomount-odiscard,defaults/dev/sdb/var/lib/mysql

   Mounting this disk hides any MySQL configuration files and tablespaces,replacing them with the contents of the disk.

   With this command, the disk is temporarily mounted and is not remounted onsystem boot. If you want to mount the disk on system boot, create anfstabentry. For more information, seeSet up the additional disk earlier in this tutorial.

   Note: For this tutorial, themysql-prod andmysql-test instances usethe same Unix user ID for themysql user. Different user IDs might beused in a production environment. If the user IDs differ, you need to changethe owner tomysql on all the files and directories in the data disk byrunning the following command:chown -hR mysql.mysql /var/lib/mysql

Start MySQL in the test instance

1. In the SSH session connected tomysql-test, start the MySQL service:

   sudoservicemysqlstart

2. To verify that the cloned database is functional, run a querythat counts the number of rows in theemployees table:

   mysql-uroot-p-e"select count(*) from employees.employees;"

   When you're prompted, enter the root password of themysql-prod databaseserver. The production instance root password is required because the entireMySQL data directory is a clone of the data directory of themysql-prodinstance, so all the databases, database users, and their passwords arecopied.

   +----------+| count(*) |+----------+|   300024 |+----------+

   The number of rows is the same as on themysql-prod instance.

Now that you have seen how to clone a database using persistent disk snapshots,you might want to try cloning a database by using export and import. Tocomplete the tutorial for this second approach, you must unmount the cloneddisk.

Unmount the cloned disk

To unmount the cloned disk that you created by using disk snapshots, perform thefollowing steps:

1. In the SSH session connected to yourmysql-test instance, stop the MySQLservice:

   sudoservicemysqlstop

2. Unmount the cloned data disk from the MySQL data directory:

   sudoumount/var/lib/mysql

3. Restart the MySQL service:

   sudoservicemysqlstart

Cloning using export and import

A second method of cloning a MySQL database running onCompute Engine is to use MySQL's built-in export (usingmysqldump) andimport. With this approach, you transfer the export file by usingCloud Storage.

This section of the tutorial uses resources that you created in theCloning the database using Compute Engine disk snapshots section of this tutorial. If you didn't complete that section, you must do sobefore continuing.

In this section of the tutorial, you do the following:

• Create a Cloud Storage bucket.
• Export the database on the production instance, writing it toCloud Storage.
• Import the export file into the test instance, reading it fromCloud Storage.

The following diagram shows how a database is cloned by transferring an exportusing Cloud Storage.

Because systems outside of Google Cloud can be given access toCloud Storage, you can use this approach to clone databases fromexternal MySQL instances.

Create a Cloud Storage bucket

You need to create a Cloud Storage bucket that stores the export fileswhile you transfer them from themysql-prod instance to themysql-testinstance.

1. Open Cloud Shell.

   Open Cloud Shell

2. Create a Cloud Storage bucket in the same region as your VMinstances:

   gcloudstoragebucketscreate"gs://$(gcloudconfigget-valueproject)-bucket"--location="${REGION}"

Export the database

In your production environment, you might already make backups usingmysqldumpexport files. You can use these backups as a base for cloning your database.

In this tutorial, you make a new export file by usingmysqldump, which doesn'timpact any existing full or incremental backup schedules.

• In the SSH session connected to themysql-prod instance, export theEmployees database, streaming it into a Cloud Storageobject in the bucket that you created earlier:

  mysqldump--user=root-p--default-character-set=utf8mb4--add-drop-database--verbose--hex_blob\--databasesemployees|\gcloudstoragecp-"gs://$(gcloudconfigget-valueproject)-bucket/employees-dump.sql"

  When you're prompted, enter the root password of themysql-prod database server.

  You use theutf8mb4 character set in the export to avoid any characterencoding issues.

  The--add-drop-database option is used so thatDROP DATABASE andCREATEDATABASE statements are included in the export.

Import the exported file

1. In the SSH session connected to themysql-test instance, stream theexported file from your Cloud Storage bucket into themysqlcommand-line application:

   gcloudstoragecat"gs://$(gcloudconfigget-valueproject)-bucket/employees-dump.sql"|\mysql--user=root-p--default-character-set=utf8mb4

   When you're prompted, enter the root password of themysql-test database server.

   You use theutf8mb4 character set in the import to avoid any characterencoding issues.

2. To verify that the cloned database is functional, run a querythat counts the number of rows in theemployees table:

   mysql-uroot-p-e"select count(*) from employees.employees;"

   When you're prompted, enter the root password of themysql-test database server.

   +----------+| count(*) |+----------+|   300024 |+----------+

   The number of rows is the same as on themysql-prod instance.

Using Cloud SQL as the cloning destination

If your destination database is hosted on Cloud SQL, and the origindatabase is on Compute Engine, then the only supported mechanism for cloning isby exporting the database to Cloud Storage, and then importing thedatabase into Cloud SQL.

Asexplained in the documentation for Cloud SQL,Cloud SQL can only import the exported file when it does not contain anytriggers, stored procedures, views, or functions.

If your database relies on any of these elements, you must exclude them from theexport by using the--skip-triggers and--ignore-table [VIEW_NAME] command-linearguments, and then manually recreate them after importing.

Create a Cloud SQL for MySQL instance

1. Open Cloud Shell.

   Open Cloud Shell

2. Create a Cloud SQL for MySQL instance running the same databaseversion as yourmysql-prod instance:

   gcloudsqlinstancescreatemysql-cloudsql\--tier=db-n1-standard-2--region=${REGION}--database-versionMYSQL_5_7

   After a few minutes, your Cloud SQL database is created.

3. Reset the root user password to a known value:

   gcloudsqlusersset-passwordroot\--host=%--instance=mysql-cloudsql--prompt-for-password

   When you're prompted to provide a root password, create and enter apassword. Note the password or store it temporarily in a safe place.

Export the database

To export the database in a format suitable for importing intoCloud SQL, you need to exclude any views in the database.

1. In the SSH session connected to themysql-prod instance, set an environmentvariable containing a set of command-line arguments for themysqldumpcommand so that it ignores the views in theEmployees database:

   DATABASE_NAME=employeesIGNORE_TABLES_ARGS="`mysql -u root -p -s -s -e \"    SELECT CONCAT('--ignore-table${DATABASE_NAME}.',TABLE_NAME)    FROM information_schema.TABLES    WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA = '${DATABASE_NAME}';    \"`"

   When you're prompted, enter the root password of themysql-prod database server.

2. View the variable contents to verify that they were set correctly:

   echo"${IGNORE_TABLES_ARGS}"

   --ignore-table employees.current_dept_emp--ignore-table employees.dept_emp_latest_date

3. Export theEmployees database, excluding triggers and views, streaming itdirectly into a Cloud Storage object in the bucket that you createdearlier:

   mysqldump--user=root-p--default-character-set=utf8mb4--add-drop-database--verbose\--hex-blob--skip-triggers--set-gtid-purged=OFF\$IGNORE_TABLES_ARGS\--databasesemployees|\gcloudstoragecp-"gs://$(gcloudconfigget-valueproject)-bucket/employees-cloudsql-import.sql"

   When you're prompted, enter the root password of themysql-prod database server.

Update object permissions

The correct permissions need to be set on both the Cloud Storage bucketand the export object so that the Cloud SQL service account is able to read them.These permissions are set automatically when you use the Google Cloud console toimport the object, or they can be set by usinggcloud commands.

1. Open Cloud Shell.

   Open Cloud Shell

2. Set an environment variable containing the address of the service account of yourCloud SQL instance:

   CLOUDSQL_SA="$(gcloudsqlinstancesdescribemysql-cloudsql--format='get(serviceAccountEmailAddress)')"

3. Add the service account to the bucket Identity and Access Management (IAM) policy as areader and writer:

   gcloudstoragebucketsadd-iam-policy-binding"gs://$(gcloudconfigget-valueproject)-bucket/"\--member=user:"${CLOUDSQL_SA}"--role=roles/storage.objectUser

Import the exported database

1. Open Cloud Shell.

   Open Cloud Shell

2. Import the exported file into your Cloud SQL instance:

   gcloudsqlimportsqlmysql-cloudsql\"gs://$(gcloudconfigget-valueproject)-bucket/employees-cloudsql-import.sql"

   When prompted, entery.

3. To verify that the cloned database is functional, run a querythat counts the number of rows in theemployees table:

   echo"select count(*) from employees.employees;"|\gcloudsqlconnectmysql-cloudsql--user=root

   When prompted, enter the root password of themysql-cloudsql database server.

   The output is the following:

   Connecting to database with SQL user [root].Enter password:count(*)300024

   The number of rows is the same as on themysql-prod instance.

   Note: The imported database doesn't contain anytriggers, stored procedures, views, or functions from the original database.If these are required, you must recreate them manually.

Additional information for production systems

The following categories provide additional best practices for your productionsystems.

Using disk snapshots

For physical backups (such as disk snapshots), the MySQL documentationrecommends that you pause writes to the database before you take a snapshot. You do this byusing theFLUSH TABLES WITH READ LOCK command. When the snapshot is complete, you can useUNLOCK TABLESto restart writes.

For databases that use InnoDB tables, we recommend that you take the snapshotdirectly without first executing theFLUSH TABLES WITH READ LOCK command. Thisallows the database to stay running without any ill effects, but the snapshotmight be in an inconsistent state. However, if this occurs, the InnoDB enginecan rebuild the tables to a consistent state when the clone starts up.

For databases that use MyISAM tables, executing theFLUSH TABLES WITH READ LOCKcommand blocks all writes to the tables, making your database read-onlyuntil you run theUNLOCK TABLES command.

If you take a snapshotwithout first flushing and locking the tables, there isa risk that the newly cloned database will contain inconsistent data, or will becorrupted.

Therefore, to get a consistent snapshot on databases using MyISAM tables, werecommend that you runFLUSH TABLES WITH READ LOCK on a read replica andtake a snapshot of that replica so that the performance of the primary (master)database is not affected.

Using the mysqldump command

In order to create an export file that's consistent with the source database,themysqldump command locks all the tables during the export operation. Thismeans that writes to the database are blocked while the database is being exported.

We therefore recommend that you run themysqldump command against a readreplica of the primary database so that the primary is not blocked.