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 [2011/12/03 17:58]
tkilla 		linux:databases:mysql [2017/11/22 01:10] (current)
tkilla
Line 5: Line 5:
 ---- ----
  
-**mysqlcheck** 
  
-Run mysqlcheck to fix SQL index/.. errors once in a while+== Installation ==
  
-this can be used as a weekly cronjob, the output is sent by mail+Set root passwd, remove test db and anoympous users and remove root remote login
  
-   mysqlcheck -A -r --host=${HOST}  --user=root --port=3308 --password=${PASSWD}  | mail -s '[SQL Server] mycheck complete' $EMAILADDI+  /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
  
-\\ 
 ---- ----
  
-**mysqldump** +== Bug fixes == 
-dump a databse to a file. there are many parameters. check 'man mysqldump'+ 
 +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! --opt is important to fix encoding!
Line 24: Line 82:
  
  
-\\+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:
 +
 +  * 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.1322931529.txt.gz · Last modified: 2011/12/03 17:58 by tkilla

Page Tools

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki