fr33.info

User Tools

Site Tools

linux:databases:mysql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
Last revision Both sides next revision
linux:databases:mysql [2015/11/25 21:14]
tkilla 		linux:databases:mysql [2017/11/22 01:09]
tkilla
Line 4: Line 4:
  
 ---- ----
-\\ 
-== mysqlcheck == 
  
 +
 +== 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: 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:
Line 12: Line 19:
    mysqlcheck -A -r --host=${HOST}  --user=root --port=3308 \     mysqlcheck -A -r --host=${HOST}  --user=root --port=3308 \ 
    --password=${PASSWD} | mail -s '[SQL Server] mycheck complete' $EMAILADDI    --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 == == Bug fixes ==
Line 34: Line 46:
  
  
----- +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 == == mysqldump ==
Line 45: Line 80:
  
    mysqldump --opt --host=127.0.0.1 --default-character-set=utf8 --user=root --password=${PWD} $DB > $FILE    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 == == mysql import ==
Line 54: Line 113:
  
    mysql --host=127.0.0.1  -u root --port=$PORT --password=${DBPWD} ${DBNAME} < ${MYSQLDUMP_FILE}    mysql --host=127.0.0.1  -u root --port=$PORT --password=${DBPWD} ${DBNAME} < ${MYSQLDUMP_FILE}
 +
  
 ---- ----
-\\ 
  
 == execute mysql command == == execute mysql command ==
Line 66: Line 125:
  
 ---- ----
-\\ 
- 
  
 == howto reset lost mysql root password == == howto reset lost mysql root password ==
Line 95: Line 152:
 Thanx for the infos to Vivek! Thanx for the infos to Vivek!
 http://www.cyberciti.biz/tips/recover-mysql-root-password.html 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:
 +
 +  * https://www.cyberciti.biz/faq/how-to-setup-mariadb-ssl-and-secure-connections-from-clients/
 +
 +
 +\\
 +
 +**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:
 +  * https://www.percona.com/blog/2012/03/21/troubleshooting-mysql-memory-usage/
 +
 +
 +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/
  
  
linux/databases/mysql.txt · Last modified: 2017/11/22 01:10 by tkilla

Page Tools

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki