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.
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:
- Modify an existing slave database to work with Looker
- Create a new slave read replica database and configure it to work with Looker
Modifying an Existing Replica
Users and Security
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.
mysql into the database which will be the master:
mysql -h hostname.uid.region.rds.amazonaws.com -P 3306 -u root -p
Create a user named
some_password_here with a unique, secure password):
CREATE USER looker IDENTIFIED BY 'some_password_here';
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;
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.
Log in and go to the AWS Dashboard:
Go to your RDS Dashboard:
Select the DB Instance you wish to specify as the master. Go to Instance Actions and select Create Read Replica:
Wait for the dialog to load:
Fill out your configuration for the slave:
Wait for the master to create the Read Replica. While waiting, you can move on and set up the DB Parameter Group:
Go to DB Parameter Groups and click Create DB Parameter Group:
Select DB Parameter Group Family, DB Parameter Group Name, and DB Parameter Group Description and then click Yes, then Create:
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):
Search for read_only and change the parameter to a
0, then click Save Changes:
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:
Using the MySQL client, connect to slave database instance as
looker and test to see if data is accessible.
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:
After completing the database configuration, you can connect to the database from Looker using these directions.