Ubuntu MySQL setup notes

[kingtrw]

I thought I'd post this here as when I first went to test Unimus, I had to delay my initial setup a bit because I didn't want to use a file-level database, but I also wasn't familiar enough with the database setup process to be sure that I knew what I was doing.

I'm still not, but in case it's useful to anyone else, here are my steps for quickly getting a MySQL DB installed on Ubuntu 20.04 for use with Unimus.

Code: Select all

using very good digitalocean guides on  mysql
https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04
https://www.digitalocean.com/community/tutorials/a-basic-mysql-tutorial

# apt-get update
# apt-get install mysql-server

mysqld will log errors to /var/log/mysql/error.log

# /usr/bin/mysql_secure_installation

Validate password component: Y
Strength: 2 (strong)

set root password 

remove anonymous users: Y
disllow root login remotely: Y
remove testdb: Y
reload privilege tables: Y

run 'mysql' as root user so that it autenticates via auth_socket (better alterantive to "mysql - u root -p")

# mysql

> SHOW DATABASES;

> CREATE DATABASE unimus;

> CREATE USER 'unimusdb_user'@'localhost' IDENTIFIED BY '<db_password>';

> GRANT ALL PRIVILEGES ON unimus.* TO 'unimusdb_user'@'localhost';

\q

Then in the Unimus web setup

db: mysql
host: localhost
database name: unimus
database user: unimusdb_user
database password: as above

Don't forget other database best practises such as housekeeping and backup!

Probabably not perfect, but it works!

[Tomas]

Just a few additional notes:

-- The above should work for MySQL and MariaDB just fine. In generally we recommend MariaDB, but DB flavor should really not make much difference in a one-off deploy that will be used for Unimus only.

-- You probably want to change the default memory allocations for buffers. We have seen some BD engines come with really low default buffer values, which can completely tank performance. This can be changed in "my.ini", or files being included by the main config file - depends on how the DB distribution is packaged. Values to change should be:

Code: Select all

query_cache_size = 64M
query_cache_limit = 32K
innodb_buffer_pool_size = 1G

The values above should work if about 2GB of RAM are allotted to the DB engine. If you want to get better performance, increase this numbers at the cost of more RAM usage by the DB.

-- Since we are talking about performance, please don't forget to properly specify RAM allotment for Unimus itself
https://wiki.unimus.net/display/UNPUB/C ... mory+usage

-- Tweaking InnoDB log files is also recommended, especially turning on file-per table. This can save A LOT of disk space on busy DB servers when tables are deleted. Also setting proper log file count and size is useful:

Code: Select all

innodb_file_per_table =  1

innodb_log_files_in_group = 3
innodb_log_file_size = 128M

-- Setting max allowed packet size to a larger than default value is recommended. We saw in quite a few customers large backups failing to save to DB due to this being too low:

Code: Select all

max_allowed_packet = 64M

-- Creating the DB using UTF8 with unicode support is recommended, otherwise non-english characters may not work. This can be done with:

Code: Select all

CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- For existing DBs, migration is a bit tricky and can result in some issues in edge-cases. However, you can try to migrate using:

Code: Select all

ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

SELECT CONCAT("ALTER TABLE `",`TABLE_NAME`,"` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") 
FROM `information_schema`.`TABLES` 
WHERE `TABLE_SCHEMA` = 'database_name';

You then need to migrate all the columns in all the tables:

Code: Select all

SET FOREIGN_KEY_CHECKS=0;

SELECT CONCAT("ALTER TABLE `",`TABLE_NAME`,"` MODIFY `",`COLUMN_NAME`,"` ",COLUMN_TYPE," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ",IF(`IS_NULLABLE`='YES', 'NULL', 'NOT NULL')," ",IF(`COLUMN_DEFAULT` IS NOT NULL, CONCAT(" DEFAULT '", `COLUMN_DEFAULT`, "'"), ''),";") 
FROM `information_schema`.`COLUMNS` 
WHERE `TABLE_SCHEMA` = 'database_name' AND `TABLE_NAME` = 'table_name' AND (`CHARACTER_SET_NAME` IS NOT NULL OR `COLLATION_NAME` IS NOT NULL);

SET FOREIGN_KEY_CHECKS=1;

More info here: https://stackoverflow.com/a/52302695

[kingtrw]

That's really useful, thank you! I'll try the DB conversion, but I may end up rebuilding the machine when we go into production with it anyway.

[kingtrw]

Quick update, as I've finally got around to replacing my proof-of-concept machine with an Ubuntu 20.04 production machine:

Some of the tuning options you suggested have been removed from mysql as per https://dev.mysql.com/doc/refman/8.0/en ... moved.html :

• query_cache_size
• query_cache_limit

innodb-buffer-pool-size defaulted to 128MiB
innodb-file-per-table defaulted to TRUE so I didn't change it
innodb-log-files-in-group defaulted to 2
innodb-log-file-size defaulted to 48 MiB
max-allowed-packet defaulted to 64MiB so I didn't change it

creating database: I used CREATE DATABASE unimus CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; which StackOverflow said was recommended for the 'newer' mysql.

[TDJ211]

Big thank you!!