This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Last revision Both sides next revision | ||
linux:databases:mysql [2011/12/03 18:01] 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 | ||
+ | |||
+ | / | ||
+ | |||
+ | |||
+ | == 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 11: | Line 19: | ||
| | ||
| | ||
+ | |||
+ | In case the server crashed, mysql suggests an offline check, while server is stopped: | ||
+ | |||
+ | myisamchk --silent --force */ | ||
+ | |||
+ | * https:// | ||
---- | ---- | ||
- | \\ | ||
- | **mysqldump** | + | == Bug fixes == |
- | dump a databse | + | |
+ | If you receive an error like this while dumping: " | ||
+ | |||
+ | run: | ||
+ | mysql_upgrade -u root --password=PASSWD | ||
+ | |||
+ | |||
+ | If you receive this error: "Table ' | ||
+ | |||
+ | 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 | ||
+ | ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/ | ||
+ | |||
+ | 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:// | ||
+ | |||
+ | If you get annoyed, because nothing helps: kill everything: | ||
+ | |||
+ | apt-get remove --purge mysql-server | ||
+ | |||
+ | remove /etc/mysql/ and / | ||
+ | |||
+ | apt-get install mysql-server | ||
+ | |||
+ | Then reset the passwd and retry | ||
+ | |||
+ | ---- | ||
+ | |||
+ | == mysqldump | ||
+ | |||
+ | dump a database | ||
--opt is important to fix encoding! | --opt is important to fix encoding! | ||
| | ||
+ | |||
+ | |||
+ | If you get "error 34 too many open files" | ||
+ | |||
+ | open_files_limit = 50000 | ||
+ | |||
+ | |||
+ | Restart mysql and check to see current setting: | ||
+ | SHOW VARIABLES LIKE ' | ||
+ | |||
+ | |||
+ | |||
+ | interesting parameters for replication: | ||
+ | |||
+ | --lock-tables - READ LOCK (no writes) for currently dumped database: ensures consitent dump | ||
+ | instead of | ||
+ | --single-transaction | ||
+ | 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 --host=127.0.0.1 | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | == execute mysql command == | ||
+ | |||
+ | any sql statement can be executed in commandline and scripts by using ' | ||
+ | |||
+ | mysql --host=127.0.0.1 | ||
+ | -e " | ||
+ | |||
+ | ---- | ||
+ | |||
+ | == howto reset lost mysql root password == | ||
+ | |||
+ | there is a good chance to find the pass in root's commands history ;) | ||
+ | cat / | ||
+ | cat / | ||
+ | |||
+ | |||
+ | if you are not lucky, you can put the mysql-server in recovery mode: | ||
+ | |||
+ | / | ||
+ | | ||
+ | mysql -u root # the SQL console is accessible without passwd.. | ||
+ | |||
+ | # change the pass in SQL: | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | |||
+ | # restart in normal mode - with the fresh pass: | ||
+ | / | ||
+ | / | ||
+ | |||
+ | |||
+ | Thanx for the infos to Vivek! | ||
+ | http:// | ||
+ | |||
+ | ---- | ||
+ | |||
+ | == set collation and character set in my.cnf! | ||
+ | |||
+ | # UTF-8 should be used instead of Latin1. Obviously. | ||
+ | # NOTE " | ||
+ | | ||
+ | [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 ' | ||
+ | |||
+ | * https:// | ||
+ | |||
+ | -- | ||
+ | |||
+ | == Convert MyISM to INNODB | ||
+ | |||
+ | .. because it's better :) | ||
+ | |||
+ | a little script to get all required alter table statements | ||
+ | |||
+ | mysql -u root --password=xxxxx -e " | ||
+ | |||
+ | then execute the file via mysql command: mysql -u ... < / | ||
+ | |||
+ | read this great article: http:// | ||
+ | |||
+ | |||
+ | == 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 / | ||
+ | |||
+ | |||
+ | Edit / | ||
+ | |||
+ | [client] | ||
+ | #... | ||
+ | # SSL: | ||
+ | ssl-ca=/ | ||
+ | | ||
+ | [mysqld]: | ||
+ | # ... | ||
+ | # SSL: | ||
+ | ssl-ca=/ | ||
+ | ssl-cert=/ | ||
+ | ssl-key=/ | ||
+ | |||
+ | Restart mysqld: | ||
+ | / | ||
+ | |||
+ | Check SSL-usage in mysql console: | ||
+ | show global variables like ' | ||
+ | |||
+ | and in shell: | ||
+ | mysql -u root -ppass -sss -e ' | ||
+ | |||
+ | |||
+ | If you setup replication, | ||
+ | |||
+ | |||
+ | |||
+ | * Excellent tutorial on replication and SSL: https:// | ||
+ | |||
+ | == 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: | ||
+ | |||
+ | Regenerating certs fixes the prob! | ||
+ | |||
+ | Used this tutorial to create new certs: | ||
+ | |||
+ | * https:// | ||
+ | |||
+ | |||
\\ | \\ | ||
+ | |||
+ | **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: | ||
+ | |||
+ | the mysql process was killed. | ||
+ | |||
+ | watch memory usage: | ||
+ | |||
+ | watch -n 30 "ps aux | grep / | ||
+ | |||
+ | |||
+ | the most important parameter in my.cnf is this (but there are many others..): | ||
+ | |||
+ | innodb_buffer_pool_size = 2G | ||
+ | |||
+ | |||
+ | OOM troubleshooting infos: | ||
+ | * https:// | ||
+ | |||
+ | |||
+ | If you suspect RAM problems and you cannot run memtest on a remote server, try this trick: | ||
+ | |||
+ | fallocate -l 24G 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:// | ||
+ | |||