SwitchingToMySQL

Overview

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

Stop all running Tapper daemons.

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

In this HOWTO 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! Here you delete your existing data.

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

If that fails then you probably prepared your database for correct UTF-8 handling, so better use the next chapter.

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

Careful! Here you delete your existing data.

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

Careful! Here you delete your existing data.

If you don't want to use the shipped SQL init script this is the way to do it completely 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

Done

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.

Restart Tapper

You can now restart the Tapper daemons and maybe re-run some of the HelloWorld examples to re-fill your database.