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 [2017/02/28 14: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  ==
Line 221: Line 258:
  
   * https://www.cyberciti.biz/faq/how-to-setup-mariadb-ssl-and-secure-connections-from-clients/   * 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.1488286953.txt.gz ยท Last modified: 2017/02/28 14:02 by tkilla

Page Tools

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki