Google Cloud SQL - Backup prod and restore on dev instance

To test your code in cloud sql you need a realistic data load. The best way to get this is the backup and restore your production database. Note that you need to make sure you anonymize the data sufficiently immediately after the restore to protect your customers identities.

Go to google api console. https://code.google.com/apis/console/ you need to be logged in to your google account for this.

Find the name of the storage bucket in cloud storage or create a new one.

Go back into Cloud sql console and export data.

Give the name of the file as date-prod: june-17-2013-prod

Back up will take a while.

Once it is completed download the file to your local machine.

Decompress it, its in gzip format.

This outputs both the mysql db and the required prod db. Use the following shell commands (bash) to get rid of the mysql stuff:

grep -in ‘create database’ your_file_name

The output gives you the line numbers of the creates (that's what the n is for, the i ignores the case of the search pattern).

Then use

tail -n +line_number_of_create_db your_file_name >cut.txt

The new file will just contain the parts you are interested in.

use wc -l to verify that the line counts are what you expect.

Use vim to edit the db name and the use statement to something new.

Add this at the top of the file:

SET FOREIGN_KEY_CHECKS = 0;

You will need to edit the mysql defaults file. You can find its location in the properties of the mysqld service in windows:

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.5\my.ini" MySQL

You may need to grant yourself permissions on this file so that you can save it.

Add this line under the [mysql] section:

max_allowed_packet=64M

and the same under the [mysqld] section:

max_allowed_packet=64M

Restart mysqld service locally.

Execute your cut.txt file like this:

mysql -u root -p instancename <cut.txt