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}
INSTALL_DATABASE_SERVER_PACKAGES=$(trueorfalse True INSTALL_DATABASE_SERVER_PACKAGES)

register_database mysql

if [[ -z "$MYSQL_SERVICE_NAME" ]]; then
    MYSQL_SERVICE_NAME=mysql
    if is_fedora && ! is_oraclelinux; then
        MYSQL_SERVICE_NAME=mariadb
    elif [[ "$DISTRO" =~ bookworm|bullseye ]]; then
        MYSQL_SERVICE_NAME=mariadb
    fi
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_SERVICE_NAME
    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_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 my_client_conf
    echo_summary "Configuring and starting MySQL"

    if is_ubuntu; then
        my_conf=/etc/mysql/my.cnf
    elif is_oraclelinux; then
        my_conf=/etc/my.cnf
    elif is_fedora; then
        my_conf=/etc/my.cnf
        local cracklib_conf=/etc/my.cnf.d/cracklib_password_check.cnf
        if [ -f "$cracklib_conf" ]; then
            inicomment -sudo "$cracklib_conf" "mariadb" "plugin-load-add"
        fi
    else
        exit_distro_not_supported "mysql configuration"
    fi

    # Set fips mode on
    if is_ubuntu; then
        if is_fips_enabled; then
            my_client_conf=/etc/mysql/mysql.conf.d/mysql.cnf
            iniset -sudo $my_client_conf mysql ssl-fips-mode "on"
            iniset -sudo $my_conf mysqld ssl-fips-mode "on"
        fi
    fi

    # Change bind-address from localhost (127.0.0.1) to any (::)
    iniset -sudo $my_conf mysqld bind-address "$(ipv6_unquote $SERVICE_LISTEN_ADDRESS)"

    # (Re)Start mysql-server
    if is_fedora; then
        # service is not started by default
        start_service $MYSQL_SERVICE_NAME
    elif is_ubuntu; then
        # required since bind-address could have changed above
        restart_service $MYSQL_SERVICE_NAME
    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. We don't do this
    # for Ubuntu 22.04 (jammy) because the authorization model change in
    # version 10.4 of mariadb. See
    # https://mariadb.org/authentication-in-mariadb-10-4/
    if ! (is_ubuntu && [[ "$DISTRO" == "jammy" ]] && [ "$MYSQL_SERVICE_NAME" == "mariadb" ]); then
        sudo mysqladmin -u root password $DATABASE_PASSWORD || true
    fi

    # In case of Mariadb, giving hostname in arguments causes permission
    # problems as it expects connection through socket
    if is_ubuntu && [ "$MYSQL_SERVICE_NAME" == "mariadb" ]; then
        local cmd_args="-uroot -p$DATABASE_PASSWORD "
    else
        local cmd_args="-uroot -p$DATABASE_PASSWORD -h$SERVICE_LOCAL_HOST "
    fi

    # In mariadb e.g. on Ubuntu socket plugin is used for authentication
    # as root so it works only as sudo. To restore old "mysql like" behaviour,
    # we need to change auth plugin for root user
    # TODO(frickler): simplify this logic
    if is_ubuntu && [[ ! "$DISTRO" =~ bookworm|bullseye ]] && [ "$MYSQL_SERVICE_NAME" == "mariadb" ]; then
        if [[ "$DISTRO" == "jammy" ]]; then
            # For Ubuntu 22.04 (jammy) we follow the model outlined in
            # https://mariadb.org/authentication-in-mariadb-10-4/
            sudo mysql -e "ALTER USER $DATABASE_USER@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD('$DATABASE_PASSWORD');"
        else
            sudo mysql $cmd_args -e "UPDATE mysql.user SET plugin='' WHERE user='$DATABASE_USER' AND host='localhost';"
            sudo mysql $cmd_args -e "FLUSH PRIVILEGES;"
        fi
    fi
    if ! (is_ubuntu && [[ "$DISTRO" == "jammy" ]] && [ "$MYSQL_SERVICE_NAME" == "mariadb" ]); then
        # Create DB user if it does not already exist
        sudo mysql $cmd_args -e "CREATE USER IF NOT EXISTS '$DATABASE_USER'@'%' identified by '$DATABASE_PASSWORD';"
        # Update the DB to give user '$DATABASE_USER'@'%' full control of the all databases:
        sudo mysql $cmd_args -e "GRANT ALL PRIVILEGES ON *.* TO '$DATABASE_USER'@'%';"
    fi

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

    # Set default db type to InnoDB
    iniset -sudo $my_conf mysqld sql_mode TRADITIONAL
    iniset -sudo $my_conf mysqld default-storage-engine InnoDB
    iniset -sudo $my_conf mysqld max_connections 1024

    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

    if [[ "$MYSQL_GATHER_PERFORMANCE" == "True" ]]; then
        echo "enabling MySQL performance counting"

        # Install our sqlalchemy plugin
        pip_install ${TOP_DIR}/tools/dbcounter

        # Create our stats database for accounting
        recreate_database stats
        mysql -u $DATABASE_USER -p$DATABASE_PASSWORD -h $MYSQL_HOST -e \
              "CREATE TABLE queries (db VARCHAR(32), op VARCHAR(32),
                count INT, PRIMARY KEY (db, op)) ENGINE MEMORY" stats
    fi

    if [[ "$MYSQL_REDUCE_MEMORY" == "True" ]]; then
        iniset -sudo $my_conf mysqld read_buffer_size 64K
        iniset -sudo $my_conf mysqld innodb_buffer_pool_size 16M
        iniset -sudo $my_conf mysqld thread_stack 192K
        iniset -sudo $my_conf mysqld thread_cache_size 8
        iniset -sudo $my_conf mysqld tmp_table_size 8M
        iniset -sudo $my_conf mysqld sort_buffer_size 8M
        iniset -sudo $my_conf mysqld max_allowed_packet 8M
    fi

    restart_service $MYSQL_SERVICE_NAME
}

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
EOF

        if ! is_ubuntu || [ "$MYSQL_SERVICE_NAME" != "mariadb" ]; then
            echo "host=$MYSQL_HOST" >> $HOME/.my.cnf
        fi
        chmod 0600 $HOME/.my.cnf
    fi
    # Install mysql-server
    if [[ "$INSTALL_DATABASE_SERVER_PACKAGES" == "True" ]]; then
        if is_oraclelinux; then
            install_package mysql-community-server
        elif is_fedora; then
            install_package mariadb-server mariadb-devel mariadb
            sudo systemctl enable $MYSQL_SERVICE_NAME
        elif is_ubuntu; then
            install_package $MYSQL_SERVICE_NAME-server
        else
            exit_distro_not_supported "mysql installation"
        fi
    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
    local plugin

    # NOTE(danms): We don't enable perf on subnodes yet because the
    # plugin is not installed there
    if [[ "$MYSQL_GATHER_PERFORMANCE" == "True" ]]; then
        if is_service_enabled mysql; then
            plugin="&plugin=dbcounter"
        fi
    fi

    echo "$BASE_SQL_CONN/$db?charset=utf8$plugin"
}


# Restore xtrace
$_XTRACE_DB_MYSQL

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