mySQL

Server Setup | Security | Performance Tuning | …


Installation

Set root passwd, remove test db and anoympous users and remove root remote login

/usr/bin/mysql_secure_installation
mysqlcheck

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


Bug fixes

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


mysqldump

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

If you get “error 34 too many open files”.. increase open_files_limit in /etc/mysql/my.cnf

open_files_limit = 50000

Restart mysql and check to see current setting:

SHOW VARIABLES LIKE 'open%';

interesting parameters for replication:

–lock-tables - READ LOCK (no writes) for currently dumped database: ensures consitent dump instead of –single-transaction Use this setting with extreme caution when backing non-transactional tables BUT will give you an exact point-in-time snapshot of the data –single-transaction produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes. Those incoming changes do not become part of the dump. That ensures the same point-in-time for all tables. USE IF ALL TABLES ARE INNODB

–opt includes –lock-tables


mysql import

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}

execute mysql command

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;"

howto reset lost mysql root password

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


set collation and character set in my.cnf!
# 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

Convert MyISM to INNODB

.. because it's better :)

a little script to get all required alter table statements

mysql -u root –password=xxxxx -e “SELECT CONCAT('USE ', table_schema, '; ALTER TABLE ', table_name, ' ENGINE=InnoDB;') FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema');” > /root/alter_to_innodb.sql

then execute the file via mysql command: mysql -u … < /root/alter_to_innodb.sql

read this great article: http://mysql.rjweb.org/doc.php/myisam2innodb

SSL Connection

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/

Bugfixes / Troubleshooting

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/

 
Back to top
linux/databases/mysql.txt · Last modified: 2017/11/22 01:10 by tkilla