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 [2017/02/28 14:02]
tkilla 		linux:databases:mysql [2017/11/22 01:09]
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 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}
 +
  
 ---- ----
Line 152: Line 178:
  
 -- --
 +
 +== 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 260:
  
   * 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.txt ยท Last modified: 2017/11/22 01:10 by tkilla

Page Tools

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki