SwitchingToMySQL

Overview

This is a follow-up to HelloWorld. Prepare that first and then come back here.

The example config ~/.tapper/tapper.cfg already comes with commented out configuration for using MySQL. Here we

1. prepare a MySQL database,

2. modify Tapper config to point to it, and

3. re-run tapper init to initialize the databases with tables

Optionally these instructions show how to set up the database for full UTF-8 use (requiring mysql 5.6+).

Prepare MySQL

Install MySQL

  $ sudo apt-get install mysql-server-5.6 mysql-client-5.6

Optional: Prepare MySQL for UTF-8 needs

Attention: These settings will require you to use the prepared tweaked MySQL create-tables file down below!

Add these lines into your /etc/mysql/my.cnf under the respective [mysqld], [mysql], or [client] group, respectively:

    [client]
    # 2016-03 - [client] - Tapper and BenchmarkAnything with UTF-8
    default-character-set = utf8mb4

    [mysqld]
    # 2016-03 - [mysqld] - Tapper and BenchmarkAnything with UTF-8
    innodb_large_prefix             = on
    innodb_file_per_table           = on
    innodb_file_format              = Barracuda
    character-set-client-handshake  = FALSE
    character-set-server            = utf8mb4
    collation-server                = utf8mb4_unicode_ci
    init-connect                    = 'SET NAMES utf8'
    # random tweaks for higher traffic needs
    innodb_log_buffer_size          = 32M
    innodb_buffer_pool_size         = 512M
    innodb_log_file_size            = 128M
    max_connections                 = 1000
    connect_timeout                 = 300

    [mysql]
    # 2016-03 - [mysql] - Tapper and BenchmarkAnything with UTF-8
    default-character-set = utf8mb4

and restart mysql, e.g., with:

  $ sudo /etc/init.d/mysql restart

Optional: drop existing Tapper database

Careful!

If you want to delete an existing database, then do this:

  $ mysql -u root -p
  $ mysql> drop database testrundb;
  $ mysql> drop database benchmarkanything;

Create Tapper database

  $ mysql -u root -p
  $ mysql> create database if not exists testrundb;
    mysql> grant all on `testrundb`.* to `tapper`@localhost identified by 'verysecret';
    mysql> quit;

Create BenchmarkAnything database

  $ mysql -u root -p
  $ mysql> create database if not exists benchmarkanything;
    mysql> grant all on `benchmarkanything`.* to `benchmarker`@localhost identified by 'secret';
    mysql> quit;

Reconfigure Tapper

Edit ~/.tapper/tapper.cfg in 2 places:

Part 1 - configure testrun database

Find this piece:

database:
  TestrunDB:
    dsn: dbi:SQLite:dbname=...
    #dsn: DBI:mysql:database=testrundb
    #user: tapper
    #password: verysecret

and modify it to look like this:

database:
  TestrunDB:
    #dsn: dbi:SQLite:dbname=...
    dsn: DBI:mysql:database=testrundb
    user: tapper
    password: verysecret

Part 2 - configure benchmark database

Find this piece:

benchmarkanything:
  backend: local
  backends:
    http:
      base_url: http://localhost:7359
  storage:
    backend:
      sql:
        # --- SQLite ---
        dsn: dbi:SQLite:/home/ss5/.tapper/benchmarkanything.sqlite
        # --- mysql (example) ---
        #dsn: DBI:mysql:database=benchmarkanything
        #user: benchmarker
        #password: secret

and modify it to look like this:

benchmarkanything:
  backend: local
  backends:
    http:
      base_url: http://localhost:7359
  storage:
    backend:
      sql:
        # --- SQLite ---
        #dsn: dbi:SQLite:/home/ss5/.tapper/benchmarkanything.sqlite
        # --- mysql (example) ---
        dsn: DBI:mysql:database=benchmarkanything
        user: benchmarker
        password: secret

Re-initialize Tapper with the new databases

Careful! Here you delete your existing data.

  $ tapper init --default
  [... many lines with "SKIP...already exists"]
  REALLY DROP AND RE-CREATE DATABASE TABLES [DBI:mysql:database=testrundb] (y/N)? y
  REALLY DROP AND RE-CREATE DATABASE TABLES [DBI:mysql:database=benchmarkanything] (y/N): y

Optional: use an already prepared tweaked "create table" script for UTF-8

The Tapper::Schema module comes with a separate mysql create table file, you can use that and later you do NOT create the db during tapper init, i.e., answer NO to the testrundb question:

  $ mysql -u tapper -pverysecret testrundb < $(perl -MTapper::Schema -MFile::ShareDir=module_dir -E 'print module_dir("Tapper::Schema")')/extra/testrundb-4.001043-mysql.sql
  $ tapper init --default
  [... many lines with "SKIP...already exists"]
  REALLY DROP AND RE-CREATE DATABASE TABLES [DBI:mysql:database=testrundb] (y/N)? N
  REALLY DROP AND RE-CREATE DATABASE TABLES [DBI:mysql:database=benchmarkanything] (y/N): y

Optional: tweak the "create table" script on your own

• Read TweakingSQL, create your DB like described there and
• create the database with mysql -u tapper -pverysecret testrundb < patched-testrundb.sql
• and do NOT create the db during tapper init, i.e., answer NO to the testrundb question:

  $ tapper init --default
  [... many lines with "SKIP...already exists"]
  REALLY DROP AND RE-CREATE DATABASE TABLES [DBI:mysql:database=testrundb] (y/N)? N
  REALLY DROP AND RE-CREATE DATABASE TABLES [DBI:mysql:database=benchmarkanything] (y/N): y

Clean up

You can now either throw away the benchmarkanything.sqlite and testrundb.sqlite files in ~/.tapper/, or try to migrate their content - which is unfortunately a bit more complicated and not documented here.