home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
Migrating the Looker Backend Database to MySQL

By default, Looker uses a HyperSQL in-memory database to store its configuration, users, and other data. On a busy instance, this database can grow to be gigabytes in size, which can lead to performance issues, Java memory pressure, and long startup times.

Looker recommends that you replace the HyperSQL database with a full MySQL database backend when the internal HyperSQL database exceeds 600 MB in size. To check the size of the HyperSQL database, view the size of the looker.script file:

cd looker cd .db ls -lah

If the looker.script file exceeds 600 MB in size, follow the procedures below to migrate to an external MySQL database.

This procedure assumes a deployment in AWS EC2. For local deployments, systems should be sized comparably to the equivalent AWS instances.

Customers updating to Looker 6.6 or later from a version earlier than Looker 6.6 cannot perform the Looker update and migrate to a MySQL backend DB at the same time. If you are updating Looker and migrating to MySQL, we recommend that you complete the Looker update before performing the migration to MySQL.

Provision a MySQL Instance

Provision a MySQL 5.7.x instance to use as the backend. Looker requires MySQL version 5.7.x because earlier versions do not support UTF8mb4 encoding.

In AWS RDS, an instance of class db.m5.large is probably sufficient as a backend for a single Looker instance. Even though the database’s actual usage will likely be in the 5-10 GB range, it’s a good idea to provision 100-150 GB of SSD storage because the provisioned IOPS is based on the amount of storage requested.

Tune MySQL

MySQL’s default max_allowed_packet size for version 5.7 is 4 MB. This setting is too small for database migration and can cause the migration to fail. Set max_allowed_packet to the maximum allowed value of 1073741824:

max_allowed_packet = 1073741824

In addition, set the following default parameters in order to use UTF8mb4, which supports UTF8 character sets. See this article for information about why Looker recommends using UTF8mb4 — not UTF8 — with MySQL.

character_set_client = utf8mb4 character_set_results = utf8mb4 character_set_connection = utf8mb4 character_set_database = utf8mb4 character_set_server = utf8mb4 collation_connection = utf8mb4_general_ci collation_server = utf8mb4_general_ci

On Amazon RDS instances, you apply this setting by creating or modifying a parameter group and editing the appropriate settings. We recommend that you copy the current parameter group and make the changes on the copy, especially if you are sharing parameter groups across several RDS instances. After saving the parameter group, apply it to the RDS instance. A reboot may be required.

Set Your Replica Scheme

Looker relies on functionality that necessitates a mixed or row binlog. If you are hosting your own MySQL instance, set your binlog_format to mixed or row by issuing one of the following commands:

SET GLOBAL binlog_format = 'MIXED';

or

SET GLOBAL binlog_format = 'ROW';

Create a Database Credentials File

Looker needs to know which MySQL database to talk to and which credentials to use. In the Looker directory, create a file named looker-db.yml with the following contents, replacing <DB_hostname>, <DB_username>, <DB_password>, and <DB_name> with values for your database:

dialect: mysql host: <DB_hostname> username: <DB_username> password: <DB_password> database: <DB_name> port: 3306

If your MySQL database requires an SSL connection, add the following line to looker-db.yml:

ssl: true

If you also want to enable verification of the SSL certificate, add the following line to looker-db.yml:

verify_ssl: true

Optionally, to configure MySQL to point to the SSL certificate file, you can add the following parameter to the MySQL JDBC connection string:

trustStore=/path/to/my/truststore.jks

Security recommendation: Follow best-practice security considerations when saving credentials to a file. Ideally, set the looker-db.yml file permissions to 600, owned by the Linux “user” account under which the Looker application is executed. This file should never be checked into a Git repository.

Under Looker’s encryption scheme, all sensitive data in the database is encrypted at rest. Even if someone were to gain access to plaintext database credentials and access the database, Looker encrypts or hashes sensitive data before storing. This applies to passwords, analytics database credentials, query cache, and so on. However, if you do not want to store the cleartext password for this configuration in the looker-db.yml file on disk, you can configure the environment variable LOOKER_DB to contain a list of keys/values for each line in the looker-db.yml file. For example:

export LOOKER_DB="dialect=mysql&host=localhost&username=root&password=&database=looker&port=3306"

Security recommendation: Limit use of your MySQL user account to the IP address used by your Looker server.

Create a Database and User

Create a user and a database on the database instance, replacing <DB_username>, <DB_name>, and <DB_password> with the actual values for the user and database. Also replace <DB_charset> and <DB_collation> with the chosen character set and collation that matches the RDS instance param group settings (for true UTF8 support, we recommend utf8mb4 and utf8mb4_general_ci).

create user <DB_username>; set password for <DB_username> = password ('<DB_password>'); create database <DB_name> default character set <DB_charset> default collate <DB_collation>; grant all on <DB_name>.* to <DB_username>@'%'; grant all on looker_tmp.* to '<DB_username>'@'%';

The looker_tmp database on the last line doesn’t have to actually exist, but the grant statement is needed for internal reporting.

Back Up the .db Directory

Back up the .db directory, which contains the files needed to build the in-memory HyperSQL database, in case you need to restore HyperSQL:

cp -r .db .db-backup tar -zcvf db-backup.tar.gz ./.db-backup

Migrate the Database

Migrating the database to MySQL can take hours on a medium or large instance, especially if the HyperSQL database is 1 GB or more. We recommend that you temporarily upgrade the instance to an db.m5.2xlarge (with 32 GB RAM to allow the 26 GB heap specified below) during the migration, which reduces the time required to ~10 minutes.

  1. On the Looker host:

    cd looker ./looker stop vi looker

  2. In the Looker startup script, make a new second line in the file:

    exit

  3. Stop the instance in the AWS console. Once it stops, change the instance size to db.m5.2xlarge. Then start the instance back up again.

  4. SSH to the host as the Looker user. First make sure Java isn’t running; then run:

    cd looker java -Xms26000m -Xmx26000m -jar looker.jar migrate_internal_data looker-db.yml

    When running the migrate_internal_data step, libcrypt may not be found and a stack trace will appear, starting with this:

    NotImplementedError: getppid unsupported or native support failed to load ppid at org/jruby/RubyProcess.java:752 ppid at org/jruby/RubyProcess.java:749

    If this happens, set the LD_LIBRARY_PATH manually before executing the Java command:

    export LD_LIBRARY_PATH=$HOME/looker/.tmp/:$LD_LIBRARY_PATH

  5. Once that successfully completes, stop the instance from the AWS console.

  6. You can now restore the instance to its original size.

  7. Start the instance again.

Start Looker

  1. Edit the Looker startup script and delete the exit line you added earlier.

  2. Ensure that there are no arguments defined in LOOKERARGS in the startup script. Instead, any arguments should move to the lookerstart.cfg file so that they will not be overwritten by new versions of the startup script. Save and exit the startup script.

  3. Edit lookerstart.cfg. It should look similar to the following:

    LOOKERARGS="-d looker-db.yml"

    If there were any other arguments in the Looker startup script, add them to the lookerstart.cfg file.

  4. Archive the .db directory, if it is not archived already.

    mv .db .db-backup tar -zcvf db-backup.tar.gz ./.db-backup rm -rf ./.db-backup/

  5. Start Looker:

    ./looker start

Verify That Looker Is Using the New Database

If Looker is successfully using the backend MySQL, you should see network connections between the Looker instance and the new database instance. To check this, run the following command on the Looker instance:

netstat -na | grep 3306

You should see some connections to the database instance. Below is a sample output, showing a DB instance at IP address 10.0.3.155:

looker@instance1:~$ netstat -na | grep 3306 tcp6 0 0 10.0.5.131:56583 10.0.3.155:3306 ESTABLISHED tcp6 0 0 10.0.5.131:56506 10.0.3.155:3306 ESTABLISHED tcp6 0 0 10.0.5.131:56582 10.0.3.155:3306 ESTABLISHED tcp6 0 0 10.0.5.131:56508 10.0.3.155:3306 ESTABLISHED

Backing Up Looker

After you migrate to a MySQL backend, Looker’s automated S3 backups will no longer function. We recommend running at least nightly backups of the MySQL database, and nightly file system backups of the Looker working directory. The looker/log/ directory may be excluded from the file system backups. See the Creating Backups documentation page for more information.

Top