This is an old revision of the document!
Server Setup | Security | Performance Tuning | …
Set root passwd, remove test db and anoympous users and remove root remote login
/usr/bin/mysql_secure_installation
Run mysqlcheck to fix SQL index/.. errors and clean the caches once in a while. this can be used as a weekly cronjob, the output is sent by mail:
mysqlcheck -A -r --host=${HOST} --user=root --port=3308 \ --password=${PASSWD} | mail -s '[SQL Server] mycheck complete' $EMAILADDI
In case the server crashed, mysql suggests an offline check, while server is stopped:
myisamchk --silent --force */*.MYI
* https://dev.mysql.com/doc/refman/5.7/en/crashing.html
If you receive an error like this while dumping: “Column count of mysql.proc is wrong. Expected 20, found 16.”
run:
mysql_upgrade -u root --password=PASSWD
If you receive this error: “Table './DB_NAME/TABLE_NAME' is marked as crashed and should be repaired”
run this in mysql console:
USE DB_NAME REPAIR TABLE TABLE_NAME;
or repair all dbs - some say, the server should be stopped (it does not take long, if few tables are crashed):
mysqlcheck --repair --all-databases -u root --password=PASSWD
If the server crashs during import:
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query creating database xxx .: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
this indicates crashed INNODB tables.
Various ways to repair include setting:
innodb_force_recovery=4
Then restart, dump and reimport..read the docs.. A good article about corrupted innodb: http://www.softwareprojects.com/resources/programming/t-how-to-fix-mysql-database-myisam-innodb-1634.html
If you get annoyed, because nothing helps: kill everything:
apt-get remove --purge mysql-server
remove /etc/mysql/ and /var/lib/mysql
apt-get install mysql-server
Then reset the passwd and retry
dump a database to a .sql file. there are many parameters. check 'man mysqldump'
–opt is important to fix encoding!
mysqldump --opt --host=127.0.0.1 --default-character-set=utf8 --user=root --password=${PWD} $DB > $FILE
works by piping an sql file into the 'mysql' command:
mysql --host=127.0.0.1 -u root --port=$PORT --password=${DBPWD} ${DBNAME} < ${MYSQLDUMP_FILE}
any sql statement can be executed in commandline and scripts by using '-e' param:
mysql --host=127.0.0.1 -u $DBUSER --password=${DBPWD} --port=$PORT \ -e "CREATE DATABASE ${DBNAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;"
there is a good chance to find the pass in root's commands history ;)
cat /root/.mysql_history|more cat /root/.bash_history|more
if you are not lucky, you can put the mysql-server in recovery mode:
/etc/init.d/mysql stop mysqld_safe --skip-grant-tables & # start server wo/ permission checks.. mysql -u root # the SQL console is accessible without passwd..
# change the pass in SQL: mysql> use mysql; mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root'; mysql> flush privileges; mysql> quit
# restart in normal mode - with the fresh pass: /etc/init.d/mysql stop /etc/init.d/mysql start
Thanx for the infos to Vivek! http://www.cyberciti.biz/tips/recover-mysql-root-password.html
# UTF-8 should be used instead of Latin1. Obviously. # NOTE "utf8" in MySQL is NOT full UTF-8: http://mathiasbynens.be/notes/mysql-utf8mb4 [client] default-character-set = utf8mb4 [mysqld] character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci [mysql] default-character-set = utf8mb4 utf84mb suggested (15), but utf8 for compatibility:
mysql> SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'collation%';
* https://stackoverflow.com/questions/3513773/change-mysql-default-character-set-to-utf-8-in-my-cnf
–
This setup does not require all users to use ssl, you need to update your mysql.users with REQUIRE_SSL!
First generate all certs. You need to adjust days and set some hostnames (seems unimportant here)
Create a certificate authority:
openssl genrsa 4096 > ca-key.pem openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem
Create server certificate:
openssl req -newkey rsa:4096 -days 3600 -nodes -keyout server-key.pem -out server-req.pem openssl rsa -in server-key.pem -out server-key.pem openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Create client certificate:
openssl req -newkey rsa:4096 -days 3600 -nodes -keyout client-key.pem -out client-req.pem openssl rsa -in client-key.pem -out client-key.pem openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Check the certs:
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
Copy to mysql dir:
cp *.pem /etc/mysql/ssl/
Edit /etc/mysql/my.cnf:
[client] #... # SSL: ssl-ca=/etc/mariadb/ssl/ca-cert.pem [mysqld]: # ... # SSL: ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key.pem
Restart mysqld:
/etc/init.d/mysql restart
Check SSL-usage in mysql console:
show global variables like '%ssl%';
and in shell:
mysql -u root -ppass -sss -e '\s' | grep SSL
If you setup replication, check the other wiki page and the following tutorial. Spoiler: you have to grant replication with “…REQUIRE SSL;”
* Excellent tutorial on replication and SSL: https://www.ulyaoth.net/resources/tutorial-install-wordpress-with-mariadb-in-ssl-replication.40/
After Upgrade to 10.1 SSL connection does not work anymore. Client cannot login and repication is broken:
ERROR 2026 (HY000): SSL connection error: error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed
Regenerating certs fixes the prob!
Used this tutorial to create new certs:
OOM - out of memory crashs
.. is the most common reason for crashs
check the logs - usually syslog - and
dmesg
if you find oom messages like:
[755193.018154] Out of memory: Kill process 3328 (mysqld) score 6 or sacrifice child [755193.018224] Killed process 3328 (mysqld) total-vm:3650212kB, anon-rss:395124kB, file-rss:14684kB, shmem-rss:0kB
the mysql process was killed. mysql_safe in debian usually respawns it immediatly (no need for restarts by monit)
watch memory usage:
watch -n 30 "ps aux | grep /sbin/mysql[d]"
the most important parameter in my.cnf is this (but there are many others..):
innodb_buffer_pool_size = 2G
OOM troubleshooting infos:
If you suspect RAM problems and you cannot run memtest on a remote server, try this trick:
fallocate -l 24G largefile # server RAM - used ram or better all the ram in rescue mode md5sum largefile; md5sum largefile; md5sum largefile;md5sum largefile; md5sum largefile; md5sum largefile
This uses up all RAM, because the file is read into memory. the md5 sum should be always the same.
found this trick here: https://blog.tausys.de/2014/10/05/root-server-einfacher-speichertest/