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.
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.
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.
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
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
# 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.
Create database and user
Assuming that 18.104.22.168 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'@'22.214.171.124' IDENTIFIED BY 'db_password'; GRANT SELECT,UPDATE,DELETE,INSERT ON db_name.* TO 'db_user'@'126.96.36.199'; FLUSH PRIVILEGES;
Problems with connection timeout?
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?
"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'@'188.8.131.52' IDENTIFIED WITH mysql_native_password BY 'db_password';
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 184.108.40.206 -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):
Localhost as root:
mysql -u root -p
Or from connecting server:
mysql -u db_user -p -h 220.127.116.11
quit to exit
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
See the process ID and then use:
See current stats
mysqladmin status -p
# TODO: Learn more about SHOW ENGINE INNODB STATUS;
/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.
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
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 field ON table_name(field);
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
-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.
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
mytop -u root -p password
List users and grants
SELECT User, Host FROM mysql.user; SHOW GRANTS FOR 'db_user'@'18.104.22.168';
DROP USER 'db_user'@'22.214.171.124';
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
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
- My Favorite Board Games and How I Play Them
- Switching from Windows PC to Mac and why I switched back
- Creating The Space War - The Card Game of My Dreams
- 24 Characteristics That Geniuses Have in Common
- Canasta - The Great Card Game
- Annual report number 13 + 14: My Success
- Selling my SEO business TodaysWeb
- PDO vs MySQLi speed comparison
- Installing Apache Cassandra with PHP 7 on Ubuntu 16.04