Setting up and Managing a MySQL Server

Disclaimer: I am not a professionally trained sysadmin. What I have learned is by trial and error and study from Google searches. This is more notes for myself rather than a guide or article. I will regularly update it.

Intro

I am using a dedicated MySQL server for Domainstats.com and I have been doing that since the start of the project in july 2015. In recent times I have had considerable problems with the MySQL server and writing this text is also a way for me to organize and find better ways to handle it.

Current Setup

I am using a dedicated server from Hetzner. The current server is:

Dedicated Root Server, EX62-NVMe
Intel® Core™ i9-9900K Octa-Core
64 GB DDR4 RAM
2 x 1 TB NVMe SSD (software RAID 1)
1 Gbit/s bandwidth

It is the third time that I switch to a more powerful server. Domainstats is also storing data on a cluster of 9 dedicated Cassandra servers, a dedicated Redis server and as text files on a dedicated PHP server.

Size of /var/lib/mysql/databasename/ is 65,7 GB
Size of vardump is 9 GB gzipped.
Queries per second avg: 432.941

Before you upgrade to MySQL 8

There are a number of new reserved words such as rank that now has to be escaped as `rank`, see the list here.

Installation

For setup I choose latest Ubuntu as OS.

First install nano, my editor of choise:

apt-get install nano

Change the hostname to mysql:

hostname mysql
nano /etc/hostname
nano /etc/hosts

Updating the timezone to UTC:

dpkg-reconfigure tzdata
None of the above -> UTC

Change password:

passwd

Update and upgrade the system:

apt-get update
apt-get upgrade

Time to install MySQL (I always choose a password with 30+ random generated chars):

apt-get install mysql-server

mysql_secure_installation # (answer Y to all)

Change the configs

nano /etc/mysql/mysql.conf.d/mysqld.cnf

# By default MySQL only listens to localhost. We need to have it open
# Having localost + 1 IP is not possible. We will handle that in the firewall instead
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_bind_address
bind-address = 0.0.0.0

# Begin custom https://www.jimwestergren.com/setting-up-and-managing-a-mysql-server
max_connections = 500

# Skip reverse DNS lookup of clients
skip-name-resolve

# Improve performance of inserts and reduce disk I/O, changes saved to disk each 1 second
# info: https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit = 2

# Improve performance by reducing disk I/O, could be up to 80% of RAM.
# info: https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-diskio.html
# Current set at around 40GB
innodb_buffer_pool_size = 40000000000

# Improve performance by reducing disk I/O
# info https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-diskio.html
innodb_flush_method = O_DIRECT_NO_FSYNC

# Should be increased to 1024 MB according to the table
# Here: https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html
innodb_log_file_size = 1073741824

# All the above now handled supposed automatically with this one
# info https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html
#innodb_dedicated_server = ON
# DID NOT WORK, MYSQL 5.7 COULD NOT START. Perhaps works with MySQL 8?

# Turn off ability to load local files for added security
# info https://www.digitalocean.com/community/tutorials/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps
local-infile=0

Some good info here as well.

Then restart:

/etc/init.d/mysql restart

Create database and user

Assuming that 123.123.123.123 is the server that will connect to this MySQL server.

mysql -u root -p

CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'db_user'@'123.123.123.123' IDENTIFIED BY 'db_password';
GRANT SELECT,UPDATE,DELETE,INSERT,RELOAD,PROCESS ON db_name.* TO 'db_user'@'123.123.123.123';
FLUSH PRIVILEGES;

Why RELOAD and PROCESS? See here.

Problems with connection timeout?

Run:

netstat -tulnp | grep mysqld

If it say tcp6 instead of tcp I think it means that the MySQL server only listens to IPv6 connections. And when you connect with PHP PDO to your IPv6 IP make sure that you use brackets around the IP number, like this:
$this->pdo = new PDO(mysql:host=[IPv6 IP goes here];dbname=... (thanks Paul!)

Are you getting authentication method unknown to the client errors?

This is because MySQL 8.0.11 has changed and is now using caching_sha2_password as default authentication method. See the answers here and more info here:

"When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server's default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used."

Most simple solution is just to run:

ALTER USER 'db_user'@'123.123.123.123' IDENTIFIED WITH mysql_native_password BY 'db_password';

Firewall

Based on this article. We allow SSH, localhost and the connecting server. All else is rejected.

iptables -F # Remove all that exist
iptables -X # Remove all that exist
iptables -A INPUT -p tcp --dport 22 -j ACCEPT # Allow SSH
iptables -A INPUT -p icmp -j ACCEPT # Allow icmp
iptables -A INPUT -s 123.123.123.123 -j ACCEPT # Allow this IP fully, connecting server. Change the example IP
iptables -A INPUT -s 127.0.0.1 -j ACCEPT # Allow localhost.
iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT # Cannot resolve host otherwise
iptables -A OUTPUT -m state --state ESTABLISHED,RELATED -j ACCEPT # Cannot resolve host otherwise
iptables -A INPUT -j REJECT
iptables -A FORWARD -j REJECT
iptables-save > /etc/iptables.rules

Save the firewall:

apt-get install iptables-persistent

Use this to resave changes to iptables if you change it in the future (changes are not changed automatically):

netfilter-persistent save

Login

Localhost as root:

mysql -u root -p

Or from connecting server:

mysql -u db_user -p -h 123.123.123.123

use nameofdatabase;

Write quit to exit

Import database

mysql -u root -p db_name < mydump.sql

Or a big database in gzip format using the pv program to see progress:

apt-get install pv
pv mydump.sql.gz | gunzip | mysql -u root -p db_name

See global variables

SHOW VARIABLES LIKE '%variable_name%';

See queries in real time

mysqladmin -u root -p -i 1 processlist

Stop a hanging slow query

SHOW PROCESSLIST;

See the process ID and then use:

kill 12345;

See current stats

mysqladmin status -p

# TODO: Learn more about
SHOW ENGINE INNODB STATUS;

Starting/stopping/restarting MySQL

/etc/init.d/mysql start
/etc/init.d/mysql stop
/etc/init.d/mysql restart

Improve disk I/O on SSD

fstrim -a -v

See info here.

Backup

mysqldump --single-transaction -u root -p db_name | gzip > /backup/db_name-`date +%Y-%m-%d`.sql.gz

Or a single table:

mysqldump --single-transaction -u root -p db_name table_name | gzip > /backup/table_name-`date +%Y-%m-%d`.sql.gz

Send the backup file to another server:

scp /backup/db_name-sqldump-2020-03-16.sql.gz [email protected]:/backup

Creating Tables

Always use InnoDB as engine and utf8mb4 and utf8mb4_bin. Using utf8_general_ci causing problem with for example MySQL thinking ä = a when comparing.

CREATE TABLE `table_name` (
   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `text` VARCHAR(191) NOT NULL DEFAULT '',
   `small_number` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
   `big_number` INT(10) UNSIGNED NOT NULL DEFAULT 0,
   `medium_big_number` MEDIUMINT(6) UNSIGNED NOT NULL DEFAULT 0,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Create Index

CREATE INDEX field ON table_name(field);

Alter Table

ALTER TABLE table_name ADD field MEDIUMINT(6) UNSIGNED NOT NULL DEFAULT 0 AFTER other_field_name;

See slow queries

For me over 3 seconds is slow, change according to your needs:

SET GLOBAL long_query_time = 3;
SET GLOBAL slow_query_log = 1;

tail -80 /var/lib/mysql/mysql-slow.log

Or:

-i mysqldumpslow /var/lib/mysql/mysql-slow.log

Note! Turn off after use to avoid unnecessary disk work. Some years ago I found that the slow log was 2.8 GB big, don't make the same mistake.

Almost always use VARCHAR for text

VARCHAR has a max limit of 65535 characters, set a proper limit yourself. MEDIUMTEXT has a good name but it fits 16 MB of data which is a lot. Don't do the same mistake as I did by using MEDIUMTEXT instead of high limit VARCHAR for my website builder N.nu resulting in some users copy pasting data like a whole novel in certain fields meant to be small. HTML can sometimes be huge if for example it contains inline base64 images.

Quote from the cPanel Blog:

Use VARCHAR if your data is of variable length and you know it fits into VARCHAR’s 65,535 character limit. In most circumstances, VARCHAR provides better performance, it’s more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.

See MySQL Errors

tail -80 /var/log/mysql/error.log

See size of your databases

cd /var/lib/mysql
du -hx --max-depth 1

Using mytop

mytop -u root -p password

List users and grants

SELECT User, Host FROM mysql.user;
SHOW GRANTS FOR 'db_user'@'123.123.123.123';

Deleting users

DROP USER 'db_user'@'123.123.123.123';

Completely removing MySQL from a server

service mysql stop
apt-get remove --purge mysql*
apt-get purge mysql*
apt-get remove dbconfig-mysql
apt-get autoremove
apt-get autoclean
rm -rf /var/lib/mysql
rm -rf /etc/mysql

More things to study

MySQL Optimization
Slides: 50 Tips for Boosting MySQL Performance

Please don't ask me questions

I will probably not know the answer and I don't want to be responsible for your server setup. Thanks