lib/databases/mysql

#!/bin/bash
#
# lib/databases/mysql
# Functions to control the configuration and operation of the **MySQL** database backend

# Dependencies:
#
# - DATABASE_{HOST,USER,PASSWORD} must be defined

# Save trace setting
_XTRACE_DB_MYSQL=$(set +o | grep xtrace)
set +o xtrace

MYSQL_DRIVER=${MYSQL_DRIVER:-PyMySQL}

register_database mysql

# Linux distros, thank you for being incredibly consistent
MYSQL=mysql
if is_fedora && ! is_oraclelinux; then
    MYSQL=mariadb
fi

# Functions
# ---------

function get_database_type_mysql {
    if [[ "$MYSQL_DRIVER" == "PyMySQL" ]]; then
        echo mysql+pymysql
    else
        echo mysql
    fi
}

# Get rid of everything enough to cleanly change database backends
function cleanup_database_mysql {
    stop_service $MYSQL
    if is_ubuntu; then
        # Get ruthless with mysql
        apt_get purge -y mysql* mariadb*
        sudo rm -rf /var/lib/mysql
        sudo rm -rf /etc/mysql
        return
    elif is_suse || is_oraclelinux; then
        uninstall_package mysql-community-server
        sudo rm -rf /var/lib/mysql
    elif is_fedora; then
        uninstall_package mariadb-server
        sudo rm -rf /var/lib/mysql
    else
        return
    fi
}

function recreate_database_mysql {
    local db=$1
    mysql -u$DATABASE_USER -p$DATABASE_PASSWORD -h$MYSQL_HOST -e "DROP DATABASE IF EXISTS $db;"
    mysql -u$DATABASE_USER -p$DATABASE_PASSWORD -h$MYSQL_HOST -e "CREATE DATABASE $db CHARACTER SET utf8;"
}

function configure_database_mysql {
    local my_conf mysql slow_log
    echo_summary "Configuring and starting MySQL"

    if is_ubuntu; then
        my_conf=/etc/mysql/my.cnf
        mysql=mysql
    elif is_suse || is_oraclelinux; then
        my_conf=/etc/my.cnf
        mysql=mysql
    elif is_fedora; then
        mysql=mariadb
        my_conf=/etc/my.cnf
    else
        exit_distro_not_supported "mysql configuration"
    fi

    # Start mysql-server
    if is_fedora || is_suse; then
        # service is not started by default
        start_service $mysql
    fi

    # Set the root password - only works the first time. For Ubuntu, we already
    # did that with debconf before installing the package, but we still try,
    # because the package might have been installed already.
    sudo mysqladmin -u root password $DATABASE_PASSWORD || true

    # Update the DB to give user '$DATABASE_USER'@'%' full control of the all databases:
    sudo mysql -uroot -p$DATABASE_PASSWORD -h127.0.0.1 -e "GRANT ALL PRIVILEGES ON *.* TO '$DATABASE_USER'@'%' identified by '$DATABASE_PASSWORD';"

    # Now update ``my.cnf`` for some local needs and restart the mysql service

    # Change bind-address from localhost (127.0.0.1) to any (::) and
    # set default db type to InnoDB
    iniset -sudo $my_conf mysqld bind-address "$SERVICE_LISTEN_ADDRESS"
    iniset -sudo $my_conf mysqld sql_mode TRADITIONAL
    iniset -sudo $my_conf mysqld default-storage-engine InnoDB

    # the number of connections has been throttled to 256. In the
    # event that the gate jobs report "Too many connections" it is
    # indicative of a problem that could be the result of one of many
    # things. For more details about debugging this error, refer
    # https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html.
    # Note that the problem may not ONLY be an issue with MySQL
    # connections. If the number of fd's at the OS is too low, you
    # could see errors manifest as MySQL "too many connections".
    iniset -sudo $my_conf mysqld max_connections 256
    iniset -sudo $my_conf mysqld query_cache_type OFF
    iniset -sudo $my_conf mysqld query_cache_size 0

    # Additional settings to put MySQL on a memory diet. These
    # settings are used in conjunction with the cap on max_connections
    # as the total memory used by MySQL can be simply viewed as
    # fixed-allocations + max_connections * variable-allocations. A
    # nifty tool to help with this is
    # http://www.mysqlcalculator.com/. A short description of each of
    # the settings follows.

    # binlog_cache_size, determines the size of cache to hold changes
    # to the binary log during a transaction, for each connection. For
    # more details, refer
    # https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_cache_size
    # When binary logging is enabled, a smaller binlog cache could
    # result in more frequent flushes to the disk and a larger value
    # would result in less flushes to the disk but higher memory
    # usage. This however only has to do with large transactions; if
    # you have a small transaction the binlog cache is necessarily
    # flushed on a transaction commit. This is a per-connection cache.
    iniset -sudo $my_conf mysqld binlog_cache_size 4K

    # binlog_stmt_cache_size determines the size of cache to hold non
    # transactional statements in the binary log. For more details,
    # refer
    # https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_stmt_cache_size
    # This cache holds changes to non-transactional tables (read:
    # MyISAM) or any non-transactional statements which cause
    # modifications to data (truncate is an example). These are
    # written to disk immediately on completion of the statement or
    # when the cache is full. If the cache is too small, you get
    # frequent writes to the disk (flush) and if the cache is too
    # large, it takes up more memory. This is a per-connection cache.
    iniset -sudo $my_conf mysqld binlog_stmt_cache_size 4K

    # bulk_insert_buffer_size for MyISAM tables that use a special
    # cache for insert statements and load statements, this cache is
    # used to optimize writes to the disk. If the value is set to 0,
    # the optimization is disabled. For more details refer
    # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_bulk_insert_buffer_size
    # We set this to 0 which could result in higher disk I/O (I/O on
    # each insert block completion).
    iniset -sudo $my_conf mysqld bulk_insert_buffer_size 0

    # host_cache_size controls a DNS lookup optimization. For more
    # details refer
    # https://dev.mysql.com/doc/refman/5.6/en/host-cache.html
    iniset -sudo $my_conf mysqld host_cache_size 0

    # innodb_buffer_pool_size This is the size of the server wide
    # buffer pool. It is the cache for all data blocks being used by
    # the server and is managed as a LRU chain. Dirty blocks either
    # age off the list or are forced off when the list is
    # full. Setting this to 5MB (default 128MB) reduces the amount of
    # memory used by the server and this will result in more disk I/O
    # in cases where (a) there is considerable write activity that
    # overwhelms the allocated cache, or (b) there is considerable
    # read activity on a data set that exceeds the allocated
    # cache. For more details, refer
    # https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
    iniset -sudo $my_conf mysqld innodb_buffer_pool_size 5M

    # innodb_ft_cache_size and innodb_ft_total_cache_size control the
    # per-connection full text search cache and the server wide
    # maximum full text search cache. We should not be using full text
    # search and the value is set to the minimum allowable. The former
    # is a per-connection cache size and the latter is server
    # wide. For more details, refer
    # https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_ft_cache_size
    # and
    # https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_ft_total_cache_size
    iniset -sudo $my_conf mysqld innodb_ft_cache_size 1600000
    iniset -sudo $my_conf mysqld innodb_ft_total_cache_size 32000000

    # innodb_log_buffer_size This buffer is used to buffer
    # transactions in-memory before writing them to the innodb
    # internal transaction log. Large transactions, or high amounts of
    # concurrency, will cause the system to fill this faster and thus
    # make the system more disk-bound. For more details, refer
    # https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_buffer_size
    iniset -sudo $my_conf mysqld innodb_log_buffer_size 256K

    # innodb_sort_buffer_size, This buffer is used for sorting when
    # InnoDB is creating indexes. Could cause that to be slower, but
    # only if tables are large. This is a per-connection setting. For
    # more details, refer
    # https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_sort_buffer_size
    iniset -sudo $my_conf mysqld innodb_sort_buffer_size 64K

    # join_buffer_size, This buffer makes table and index scans
    # faster. So this setting could make some queries more disk
    # bound. This is a per-connection setting. For more details refer
    # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_join_buffer_size.
    iniset -sudo $my_conf mysqld join_buffer_size 128

    # key_buffer_size defines the index blocks used for MyISAM tables
    # and shared between threads. This is a server wide setting. For
    # more details see
    # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_key_buffer_size
    iniset -sudo $my_conf mysqld key_buffer_size 8

    # max_heap_table_size sets the maximum amount of memory for MEMORY
    # tables (which we don't use). The value is set to 16k, the
    # minimum allowed. For more details, see
    # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_heap_table_size
    iniset -sudo $my_conf mysqld max_heap_table_size 16K

    # net_buffer_length Each client has a buffer for incoming and
    # outgoing data, both start with a size of net_buffer_length and
    # can grow (in steps of 2x) upto a size of max_allowed_packet. For
    # more details see
    # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_net_buffer_length
    iniset -sudo $my_conf mysqld net_buffer_length 1K

    # read_buffer_size, read_rnd_buffer_size are per-thread buffer
    # used for scans on MyISAM tables. It is a per-connection setting
    # and so we set it to the minimum value allowable. Same for
    # read_rnd_buffer_size. For more details refer
    # https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_read_buffer_size
    # and
    # https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_read_rnd_buffer_size
    iniset -sudo $my_conf mysqld read_buffer_size 8200
    iniset -sudo $my_conf mysqld read_rnd_buffer_size 8200

    # sort_buffer_size when a sort is requested, it will be performed
    # in memory in a buffer of this size (allocated per connection)
    # and if the data exceeds this size it will spill to disk. The
    # innodb and myisam variables are used in computing indices for
    # tables using the specified storage engine. Since we don't
    # dynamically reindex (except during upgrade) these values should
    # never be material. Obviously performance of disk based sorts is
    # worse than in memory sorts and therefore a high value here will
    # improve sort performance for large data. For more details,
    # refer:
    # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sort_buffer_size
    # and
    # https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_sort_buffer_size
    # and
    # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_myisam_sort_buffer_size
    iniset -sudo $my_conf mysqld sort_buffer_size 32K
    iniset -sudo $my_conf mysqld innodb_sort_buffer_size 64K
    iniset -sudo $my_conf mysqld myisam_sort_buffer_size 4K

    # thread_cache_size specifies how many internal threads to cache
    # for use with incoming connections. We set this to 0 whic means
    # that each connection will cause a new thread to be created. This
    # could cause connections to take marginally longer on os'es with
    # slow pthread_create calls. For more details, refer
    # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_thread_cache_size
    iniset -sudo $my_conf mysqld thread_cache_size 0

    # thread_stack is the per connection stack size, the minimum is
    # 128k and the default is 192k on 32bit and 256k on 64bit
    # systems. We set this to 192k. Complex queries which require
    # recursion, stored procedures or other memory intensive
    # operations could exhaust this and generate a very characteristic
    # failure ("stack overflow") which is cleanly detected and the
    # query is killed. For more details see
    # https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_thread_stack
    iniset -sudo $my_conf mysqld thread_stack 196608

    # tmp_table_size is the maximum size of an in-memory temporary
    # table. Temporary tables are created by MySQL as part of a
    # multi-step query plan. The actual size of the temp table will be
    # the lesser of tmp_table_size and max_heap_table_size. If a
    # temporary table exceeds this size, it will be spooled to disk
    # using the internal_tmp_disk_storage_engine (default
    # MyISAM). Queries that often generate in-memory temporary tables
    # include queries that have sorts, distinct, or group by
    # operations, also queries that perform IN joins. For more details
    # see
    # https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_tmp_table_size
    iniset -sudo $my_conf mysqld tmp_table_size 1K

    if [[ "$DATABASE_QUERY_LOGGING" == "True" ]]; then
        echo_summary "Enabling MySQL query logging"
        if is_fedora; then
            slow_log=/var/log/mariadb/mariadb-slow.log
        else
            slow_log=/var/log/mysql/mysql-slow.log
        fi
        sudo sed -e '/log.slow.queries/d' \
            -e '/long.query.time/d' \
            -e '/log.queries.not.using.indexes/d' \
            -i $my_conf

        # Turn on slow query log, log all queries (any query taking longer than
        # 0 seconds) and log all non-indexed queries
        iniset -sudo $my_conf mysqld slow-query-log 1
        iniset -sudo $my_conf mysqld slow-query-log-file $slow_log
        iniset -sudo $my_conf mysqld long-query-time 0
        iniset -sudo $my_conf mysqld log-queries-not-using-indexes 1
    fi

    restart_service $mysql
}

function install_database_mysql {
    if is_ubuntu; then
        # Seed configuration with mysql password so that apt-get install doesn't
        # prompt us for a password upon install.
        sudo debconf-set-selections <<MYSQL_PRESEED
mysql-server mysql-server/root_password password $DATABASE_PASSWORD
mysql-server mysql-server/root_password_again password $DATABASE_PASSWORD
mysql-server mysql-server/start_on_boot boolean true
MYSQL_PRESEED
    fi

    # while ``.my.cnf`` is not needed for OpenStack to function, it is useful
    # as it allows you to access the mysql databases via ``mysql nova`` instead
    # of having to specify the username/password each time.
    if [[ ! -e $HOME/.my.cnf ]]; then
        cat <<EOF >$HOME/.my.cnf
[client]
user=$DATABASE_USER
password=$DATABASE_PASSWORD
host=$MYSQL_HOST
EOF
        chmod 0600 $HOME/.my.cnf
    fi
    # Install mysql-server
    if is_suse || is_oraclelinux; then
        if ! is_package_installed mariadb; then
            install_package mysql-community-server
        fi
    elif is_fedora; then
        install_package mariadb-server
        sudo systemctl enable mariadb
    elif is_ubuntu; then
        install_package mysql-server
    else
        exit_distro_not_supported "mysql installation"
    fi
}

function install_database_python_mysql {
    # Install Python client module
    pip_install_gr $MYSQL_DRIVER
    if [[ "$MYSQL_DRIVER" == "MySQL-python" ]]; then
        ADDITIONAL_VENV_PACKAGES+=",MySQL-python"
    elif [[ "$MYSQL_DRIVER" == "PyMySQL" ]]; then
        ADDITIONAL_VENV_PACKAGES+=",PyMySQL"
    fi
}

function database_connection_url_mysql {
    local db=$1
    echo "$BASE_SQL_CONN/$db?charset=utf8"
}


# Restore xtrace
$_XTRACE_DB_MYSQL

# Local variables:
# mode: shell-script
# End: