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
linux:databases:mysql [2016/08/23 16:02]
tkilla 		linux:databases:mysql [2017/11/22 01:10] (current)
tkilla
Line 80: 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
  
 ---- ----
Line 88: Line 111:
  
    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}
 +
  
 ---- ----
Line 152: Line 176:
  
 -- --
 +
 +== 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  == == 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) First generate all certs. You need to adjust days and set some hostnames (seems unimportant here)
Line 180: Line 219:
 Edit /etc/mysql/my.cnf: Edit /etc/mysql/my.cnf:
  
-[client] +  [client] 
-#... +  #... 
-# SSL: +  # SSL: 
-ssl-ca=/etc/mariadb/ssl/ca-cert.pem +  ssl-ca=/etc/mariadb/ssl/ca-cert.pem 
- +   
-[mysqld]: +  [mysqld]: 
-# ... +  # ... 
-# SSL: +  # SSL: 
-ssl-ca=/etc/mysql/ssl/ca-cert.pem +  ssl-ca=/etc/mysql/ssl/ca-cert.pem 
-ssl-cert=/etc/mysql/ssl/server-cert.pem +  ssl-cert=/etc/mysql/ssl/server-cert.pem 
-ssl-key=/etc/mysql/ssl/server-key.pem+  ssl-key=/etc/mysql/ssl/server-key.pem
  
 Restart mysqld: Restart mysqld:
Line 207: Line 246:
  
 * Excellent tutorial on replication and SSL: https://www.ulyaoth.net/resources/tutorial-install-wordpress-with-mariadb-in-ssl-replication.40/ * 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.1471960954.txt.gz · Last modified: 2016/08/23 16:02 by tkilla

Page Tools

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki