User Guide Getting Started Help Center Documentation Community Training
Looker
  
English
Français
Deutsch
日本語
MySQL on Amazon RDS

Encrypting Network Traffic

Looker strongly recommends encrypting network traffic between the Looker application and your database. Consider one of the options described here.

If you’re interested in using SSL encryption, see the MySQL RDS documentation.

Options

For performance reasons, it is common to use read-only slave databases with Looker. This frees up the production database to perform its primary function without any queries potentially slowing it down.

This document describes how to either:

Modifying an Existing Replica

Users and Security

Change some_password_here to a unique, secure password:

CREATE USER looker;
SET PASSWORD FOR looker = PASSWORD ('some_password_here');
GRANT SELECT ON database_name.* TO looker;

RDS & Temporary Tables

RDS Slaves, by default, have a read-only flag enabled that prevents Looker from writing to temporary tables. Looker never actually changes existing data, but MySQL still requires write access to use temp tables. This can be solved by changing the flag in RDS. From the RDS FAQ:

Q: Can my Read Replicas only accept database read operations?

Read Replicas are designed to serve read traffic. However, there may be use cases where advanced users wish to complete Data Definition Language (DDL) SQL statements against a Read Replica. Examples might include adding a database index to a Read Replica that is used for business reporting, without adding the same index to the corresponding source DB Instance. If you wish to enable operations other than reads for a given Read Replica, you will need to modify the active DB Parameter Group for the Read Replica, setting the read_only parameter to 0.

Please note that if you alter the DB Parameter Group, you will need to restart the Read Replica in order for the changes to take effect.

Create Database and Grant Permissions

Create a temp database (looker_scratch) and give the looker user the rights to write to it. The CREATE TEMPORARY statement in MySQL needs to be associated with a DB for permission purposes.

CREATE SCHEMA looker_scratch;
GRANT
  SELECT,
  INDEX,
  INSERT,
  UPDATE,
  DELETE,
  CREATE,
  DROP,
  ALTER,
  CREATE TEMPORARY TABLES
ON looker_scratch.* to 'looker'@'%';

Creating a New Replica

Please note that AWS RDS can only create Read Replicas with DB Instances that have Backup Retention activated.

Pre-AWS Steps

Step 1

mysql into the database which will be the master:

mysql -h hostname.uid.region.rds.amazonaws.com -P 3306 -u root -p

Step 2

Create a user named looker (replace some_password_here with a unique, secure password):

CREATE USER looker IDENTIFIED BY 'some_password_here';

Step 3

Create a temporary database and grant read privileges to looker for other databases and tables. Looker generally doesn’t write to the looker_scratch database, but uses it to execute CREATE TEMPORARY TABLE commands.

CREATE SCHEMA looker_scratch;
GRANT
  SELECT,
  INDEX,
  INSERT,
  UPDATE,
  DELETE,
  CREATE,
  DROP,
  ALTER,
  CREATE TEMPORARY TABLES
ON looker_scratch.* to 'looker'@'%';

GRANT
  SELECT,
  SHOW DATABASES
ON *.* TO looker;

Step 4

Flush privileges:

FLUSH PRIVILEGES;

Creating the looker user and scratch database may also be done on the slave RDS after it is made writable. However, the steps will need to be repeated each time a new slave instance is created for Looker.

AWS Steps

Step 1

Log in and go to the AWS Dashboard:

Step 2

Go to your RDS Dashboard:

Step 3

Select the DB Instance you wish to specify as the master. Go to Instance Actions and select Create Read Replica:

Step 4

Wait for the dialog to load:

Step 5

Fill out your configuration for the slave:

Step 6

Wait for the master to create the Read Replica. While waiting, you can move on and set up the DB Parameter Group:

Step 7

Go to DB Parameter Groups and click Create DB Parameter Group:

Step 8

Select DB Parameter Group Family, DB Parameter Group Name, and DB Parameter Group Description and then click Yes, then Create:

Step 9

Click on the icon with the magnifying glass for the slave parameter row to edit (shown with orange box), or select the slave parameter group and click Edit Parameters (shown with green boxes):

Step 10

Search for read_only and change the parameter to a 0, then click Save Changes:

Step 11

Wait for the Read Replica creation to finish. Select the slave DB instance and select Modify in the instance action:

Change the Parameter Group to the new slave parameter group. Then check the Apply Immediately box, click Continue, and click Modify DB Instance:

Step 12

Using the MySQL client, connect to slave database instance as looker and test to see if data is accessible.

Feature Support

Looker’s ability to provide some features depends on whether the database dialect can support them.

In the current Looker release, MySQL supports the following Looker features:

Next Steps

After completing the database configuration, you can connect to the database from Looker using these directions.

Top