Looker settings for migrating the backend default HyperSQL in memory Database to MySQL

Looker settings for migrating the back-end default HyperSQL in memory Database to MySQL:

Looker uses by default HyperSQL in memory db to store its configuration, users and other data. Over a period of time this might increase in size and can cause performance issues, Java memory pressures and long startup time etc.

Looker recommends to replace the HyperSQL db to MySQL db when internal HyperSQL db exceeds 600MB.

Verify for looker.script file size:

cd looker/.db

ls -lah looker.script

Note: Looker requires MySQL 5.7.x instance to use as the backend.

Tune MySQL:

>mysqld --user=root max_allowed_packet=1073741824

>mysqld --user=root character_set_client = utf8mb4

>mysqld --user=root character_set_results=utf8mb4

>mysqld --user=root character_set_connection=utf8mb4

>mysqld --user=root character_set_database=utf8mb4

>mysqld --user=root character_set_server=utf8mb4

>mysqld --user=root collation_connection=utf8mb4_general_ci

>mysqld --user=root collation_server=utf8mb4_general_ci

In MySQL shell:

SET GLOBAL binlog_format = 'MIXED';

or

SET GLOBAL binlog_format = 'ROW';

Create a Database Credentials File:

cd looker

Create looker-db.yml file and set file permissions to 600 and owned y looker user running application, so looker knows to which MySQL db to talk.

dialect: mysql

host: <DB_host-name>

username: <DB_user-name>

password: <DB_password>

database: <DB_name>

port: 3306

Create a Database and User:

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 'newuser'@'localhost' IDENTIFIED BY 'password';

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>'@'localhost';

grant all on looker_tmp.* to '<DB_username>'@'localhost';

Back Up the .db Directory

cp -r .db .db-backup

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

Migrate the Database

i) On looker host:

cd looker

./looker stop

vi looker

ii) Add second line in the file:

exit

iii) Make sure you have atleast 26G of memory for heap allocation or change accordingly.

cd looker

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

iv) 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

v) reboot system

Starting looker:

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

cd looker

vi looker

Here, remove the line added as "exit"

ii) 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.

iii) 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.

iv) 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/

v) Start Looker:

./looker start

Verification:

i) Verifiy if looker is using MySQL as backend

netstat -an | grep 3306

***

Installing MySQL on Centos

Installing MySQL on Centos:

    From the link (https://dev.mysql.com/downloads/) select suitable package for corresponding operating system. 

Example on CentOS7: mysql80-community-release-el7-3.noarch.rpm

Step1: Install the downloaded package.

> rpm -ivh mysql80-community-release-el7-3.noarch.rpm

This will add two new MySQL yum repositories, and we can now use them to install MySQL server

Verify by looking files in /etc/yum.repos.d/mysql*

Step2: This will update the yum repos.

yum update

Step3: Verify which version is enabled to get installed.

yum repolist all | grep mysql

Step4: Enable or Disable, by default latest is enabled.

yum-config-manager --disable mysql80-community

yum-config-manager --enable mysql57-community

Installing 5.7 in our case.

Verify by: yum repolist enabled | grep mysql

Step5: Installing MySQL community server5.7

yum install mysql-community-server

Verify by, rpm -qa | grep mysql

Step 7: Start deamon

service mysqld start

Verify by service mysqld status

Step8: Get the temporary password for MySQL root user from the logs.

cat /var/log/mysqld.log | grep 'temporary password'

Step9: Login to the shell using the generated root user.

mysql -u root -p

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Abc@123';

Note: By default "MySQL's validate_password(https://dev.mysql.com/doc/refman/8.0/en/validate-password.html)"  plugin is installed, which requires at least one uppercase letter, one lowercase letter, one digit, and one special character, and that the total password length is at least 8 characters.

Step10: Final verification.

mysqladmin -u root -p version

This should return version installed.

***

Fetching results in Elassandra for a date range

 Below is the curl command to fetch results from Elassandra Database for a given date range.

$ curl -XGET -H 'Content-Type: application/json' 'http://x.x.x.x:9200/eltabby_idx/_search?pretty=true&error_trace=true' -d '
{
  "size" : 1,
  "query" : {
    "bool" : {
      "must" : {
        "match" : {"address": "pp.kk@ppk.com"}
      },
    "filter" : {
      "range" : {
        "sent_date" : {
          "gte": "2024-06-11 17:30:46+0000",
          "lte": "2039-06-11 17:30:46+0000"
        }
      }
    }
    }
  }
}'

Activities to do in Oregon

1) Sand dune (www.sanddunesfrontier.com)

2) Torex ATV rental (www.torexatvrentals.com)