Encrypting network traffic
Looker strongly recommends encrypting network traffic between the Looker application and your database. Consider one of the options described on the Enabling secure database access documentation page.
If you’re interested in using SSL encryption, see the MySQL RDS documentation.
Options
For performance reasons, it is common to use read-only replica databases — also called read replicas — 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 replica database to work with Looker
- Create a new read replica database and configure it to work with Looker
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 and temporary tables
RDS read replicas, 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 replica only accept database read operations?
Read replicas are designed to serve read traffic. However, there may be use cases where advanced users want 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 database instance. If you want 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
.
If you alter the DB Parameter Group, you will need to restart the read replica for the changes to take effect.
Create database and grant permissions
Create a temp database 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.
You can specify the name of the temp database in the Temp Database field when creating your database connection. If you don’t specify a temp database name, Looker generates a scratch database named
looker_tmp
. The following commands uselooker_tmp
, but if you specified a different temp database name, use your temp database name instead oflooker_tmp
.
CREATE SCHEMA looker_tmp;
GRANT
SELECT,
INDEX,
INSERT,
UPDATE,
DELETE,
CREATE,
DROP,
ALTER,
CREATE TEMPORARY TABLES
ON looker_tmp.* TO 'looker'@'%';
Creating a new replica
Note that AWS RDS can only create read replicas with database instances that have Backup Retention activated.
Pre-AWS steps
Step 1
mysql
into the database that will be the primary database instance:
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_tmp
database but uses it to execute CREATE TEMPORARY TABLE
commands.
You can specify the name of the temp database in the Temp Database field when creating your database connection. If you don’t specify a temp database name, Looker generates a scratch database named
looker_tmp
. The following commands uselooker_tmp
, but if you specified a different temp database name, use your temp database name instead oflooker_tmp
.
CREATE SCHEMA looker_tmp;
GRANT
SELECT,
INDEX,
INSERT,
UPDATE,
DELETE,
CREATE,
DROP,
ALTER,
CREATE TEMPORARY TABLES
ON looker_tmp.* 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 RDS replica after it is made writable. However, the steps will need to be repeated each time Looker connects to a new replica database.
AWS steps
Step 1
Log in and go to the AWS dashboard. Select RDS under Database:
Step 2
On your RDS dashboard, select the database instance you want to specify as the primary database. Click Instance Actions and select Create Read Replica:
Step 3
In the Create Read Replica DB Instance pop-up, configure the read replica database and click Yes, Create Read Replica:
Step 4
While waiting for the read replica database to be created, you can set up the DB Parameter Group, which will contain the engine configuration values that are applied to the read replica database instance. Click the DB Parameter Groups page in the sidebar and then click the Create DB Parameter Group button:
Step 5
Select the DB Parameter Group Family and DB Parameter Group Name, and enter a DB Parameter Group Description. Then click Yes, Create.
Step 6
Click the magnifying glass icon on the row containing the read replica database parameter group or select that row and click Edit Parameters.
Step 7
In the Filters field under Parameters, search for “read_only”. Change the parameter value to a 0
. Then click Save Changes.
Step 8
When the read replica creation has finished, select it from the list of database instances, click the Instance Action button, and select Modify in the instance action.
Step 9
In the Modify DB Instance pop-up, change the DB instance Parameter Group to the new read replica parameter group. Then check the Apply Immediately box, click Continue, and click Modify DB Instance.
Step 10
Using the MySQL client, connect to the read replica database instance as looker
and test to see if data is accessible.
Feature support
For Looker to support some features, your database dialect must also support them.
In Looker 22.6, MySQL supports the following Looker features:
Next steps
After completing the database configuration, you can connect to the database from Looker using these directions.