| #!/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: |