MySQL: Difference between revisions

From DWIKI
mNo edit summary
Line 1: Line 1:
==Documentation==
 
*[http://www.mysql.com/ Homepage]
== Documentation ==
*[http://dev.mysql.com/doc/ Documentation]
 
*[http://dev.mysql.com/doc/refman/5.1/en/tutorial.html MySQL Tutorial]
*[http://www.mysql.com/ Homepage]  
*[http://www.nparikh.org/unix/mysql.php MySQL Cheat Sheet]
*[http://dev.mysql.com/doc/ Documentation]  
*[http://www.pantz.org/database/mysql/mysqlcommands.shtml A collection of mysql commands]
*[http://dev.mysql.com/doc/refman/5.1/en/tutorial.html MySQL Tutorial]  
*[http://dev.mysql.com/doc/refman/5.0/en/c-api-functions.html MySQL C API Functions]
*[http://www.nparikh.org/unix/mysql.php MySQL Cheat Sheet]  
*[https://haydenjames.io/mysql-query-cache-size-performance/ Avoid This When Tuning MySQL Query Cache for Performance]
*[http://www.pantz.org/database/mysql/mysqlcommands.shtml A collection of mysql commands]  
*[http://www.tizag.com/mysqlTutorial/mysqljoins.php MySQL joins]
*[http://dev.mysql.com/doc/refman/5.0/en/c-api-functions.html MySQL C API Functions]  
*[http://forge.mysql.com/wiki/Main_Page MySQLForge Wiki]
*[https://haydenjames.io/mysql-query-cache-size-performance/ Avoid This When Tuning MySQL Query Cache for Performance]  
*[http://www.artfulsoftware.com/infotree/queries.php Common mysql queries]
*[http://www.tizag.com/mysqlTutorial/mysqljoins.php MySQL joins]  
*http://people.freebsd.org/~kris/scaling/mysql.html
*[http://forge.mysql.com/wiki/Main_Page MySQLForge Wiki]  
*http://zabbixzone.com/zabbix/mysql-performance-tips-for-zabbix/
*[http://www.artfulsoftware.com/infotree/queries.php Common mysql queries]  
*[http://techblog.procurios.nl/k/n618/news/view/56429/14863/how-to-migrate-mysql-databases-without-downtime.html Migratie mysql database without downtime]
*[http://people.freebsd.org/~kris/scaling/mysql.html http://people.freebsd.org/~kris/scaling/mysql.html]
*[https://blog.monyog.com/top-10-things-to-monitor-on-your-mysql-server/ 10 things to monitor on your mysql server]
*[http://zabbixzone.com/zabbix/mysql-performance-tips-for-zabbix/ http://zabbixzone.com/zabbix/mysql-performance-tips-for-zabbix/]
*[[MySQL Replication]]
*[http://techblog.procurios.nl/k/n618/news/view/56429/14863/how-to-migrate-mysql-databases-without-downtime.html Migratie mysql database without downtime]  
*[https://blog.monyog.com/top-10-things-to-monitor-on-your-mysql-server/ 10 things to monitor on your mysql server]  
*[[MySQL_Replication|MySQL Replication]]  
 
[https://serverfault.com/questions/202000/how-find-and-fix-fragmented-mysql-tables Fragmented tables]
[https://serverfault.com/questions/202000/how-find-and-fix-fragmented-mysql-tables Fragmented tables]


 


==Data types==
== Data types ==
https://dev.mysql.com/doc/refman/8.0/en/data-types.html


==Tools==
[https://dev.mysql.com/doc/refman/8.0/en/data-types.html https://dev.mysql.com/doc/refman/8.0/en/data-types.html]
*http://www.adminer.org/
*phpmyadmin
*http://phpminiadmin.sourceforge.net/
*[https://www.mysqlcalculator.com/ MySQL memory calculator]


===Create a diagram===
== Tools ==
*[http://plindenbaum.blogspot.com/2008/10/creating-dia-diagrams-from-mysql-via.html Create a dia diagram]


===Show syntax===
*[http://www.adminer.org/ http://www.adminer.org/]
http://dev.mysql.com/doc/refman/5.1/en/show.html
*phpmyadmin
*[http://phpminiadmin.sourceforge.net/ http://phpminiadmin.sourceforge.net/]
*[https://www.mysqlcalculator.com/ MySQL memory calculator]


===Tuning MySQL===
=== Create a diagram ===
*http://www.mysqlperformanceblog.com/
 
*[http://www.warelab.org/blog/?p=100 Optimizing MySQL server variables]
*[http://plindenbaum.blogspot.com/2008/10/creating-dia-diagrams-from-mysql-via.html Create a dia diagram]
*[[mytop]]
 
*[[mysqltuner]]
=== Show syntax ===
 
[http://dev.mysql.com/doc/refman/5.1/en/show.html http://dev.mysql.com/doc/refman/5.1/en/show.html]
 
=== Tuning MySQL ===
 
*[http://www.mysqlperformanceblog.com/ http://www.mysqlperformanceblog.com/]
*[http://www.warelab.org/blog/?p=100 Optimizing MySQL server variables]  
*[[Mytop|mytop]]  
*[[Mysqltuner|mysqltuner]]  


In my.cnf check out:
In my.cnf check out:
  log_slow_queries
  log_slow_queries


On prompt check out stuff like:


On prompt check out stuff like:
  SHOW STATUS LIKE '%qcache%';
  SHOW STATUS LIKE '%qcache%';
  SHOW GLOBAL STATUS;
  SHOW GLOBAL STATUS;
  SHOW VARIABLES;
  SHOW VARIABLES;


 
*[http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html 5.1.5 Status Variables]
*[http://www.ibm.com/developerworks/web/library/l-tune-lamp-3.html Tuning MysQL]
== FAQ ==


*[http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html 5.1.5 Status Variables]
=== Clustering ===
*[http://www.ibm.com/developerworks/web/library/l-tune-lamp-3.html Tuning MysQL]


==FAQ==
===Clustering===
Check out percona galera (and xtrabackup)
Check out percona galera (and xtrabackup)


===server went away===
=== server went away ===
*can be triggered by max_packet_size
 
*can be triggered by max_packet_size  
 
 


=== change password ===


==change password==
  alter user 'foo'@'localhost' identified by 's3cr3t5';
  alter user 'foo'@'localhost' identified by 's3cr3t5';


===stop query===
=== stop query ===
 
  show processlist
  show processlist
  kill <id of query>
  kill <id of query>


===run query in script===
=== run query in script ===
 
  RES=`mysql -qsBe "select foo from fu.bar where tap ='beer'" -N`
  RES=`mysql -qsBe "select foo from fu.bar where tap ='beer'" -N`


===where did mysql_config_editor go?===
=== where did mysql_config_editor go? ===
https://mariadb.com/resources/blog/mysql-56-security-through-complacency explains how no security is better than some security


===high memory usage===
[https://mariadb.com/resources/blog/mysql-56-security-through-complacency https://mariadb.com/resources/blog/mysql-56-security-through-complacency] explains how no security is better than some security
*https://bobcares.com/blog/fix-mysql-high-memory-usage/
 
=== high memory usage ===
 
*[https://bobcares.com/blog/fix-mysql-high-memory-usage/ https://bobcares.com/blog/fix-mysql-high-memory-usage/]
 
=== fix logrotate on debian ===


===fix logrotate on debian===
  echo "SET PASSWORD FOR 'debian-sys-maint'@'localhost' = PASSWORD('`grep password /etc/mysql/debian.cnf | head -n 1 | awk -F= '{ gsub(/[ \t]+/, "", $2); print $2 }'`');" | mysql -u root -p
  echo "SET PASSWORD FOR 'debian-sys-maint'@'localhost' = PASSWORD('`grep password /etc/mysql/debian.cnf | head -n 1 | awk -F= '{ gsub(/[ \t]+/, "", $2); print $2 }'`');" | mysql -u root -p
===insert select===
 
=== insert select ===
 
   insert into bar(c,d) select concat ("foo ",w) as  c, concat("bar ",w) as d from foo;
   insert into bar(c,d) select concat ("foo ",w) as  c, concat("bar ",w) as d from foo;


&nbsp;
=== slow imports ===
[https://support.tigertech.net/mysql-large-inserts https://support.tigertech.net/mysql-large-inserts]


===slow imports===
=== speed up import from dump ===
https://support.tigertech.net/mysql-large-inserts


===speed up import from dump===
  SET autocommit=0;
  SET autocommit=0;
  SET unique_checks=0;
  SET unique_checks=0;
  SET foreign_key_checks=0;
  SET foreign_key_checks=0;
and after import
and after import
  COMMIT;
  COMMIT;
  SET autocommit=1;
  SET autocommit=1;
  SET unique_checks=1;
  SET unique_checks=1;
  SET foreign_key_checks=1;
  SET foreign_key_checks=1;
and turn them back on
and turn them back on


and try mysqldump --extended-insert (this seems to be default now, and part of --opt)
and try mysqldump --extended-insert (this seems to be default now, and part of --opt)


===pagination in CLI===
=== pagination in CLI ===
 
  show tables;\P less
  show tables;\P less


===show mysql configuration===
=== show mysql configuration ===
 
  show variables
  show variables


&nbsp;
=== show performance ===


===show performance===
  show innodb status
  show innodb status
or
or
  SHOW ENGINE INNODB STATUS
  SHOW ENGINE INNODB STATUS


===restore single table from backup===
=== restore single table from backup ===
 
{
{


===mysql error codes===
=== mysql error codes ===
http://dev.mysql.com/doc/refman/5.1/en/error-messages-client.html


[http://dev.mysql.com/doc/refman/5.1/en/error-messages-client.html http://dev.mysql.com/doc/refman/5.1/en/error-messages-client.html]
&nbsp;
=== separate lines in mysqldump ===


===separate lines in mysqldump===
  --extended-insert=FALSE --complete-insert=TRUE  
  --extended-insert=FALSE --complete-insert=TRUE  


===Fix privileges===
=== Fix privileges ===
 
  mysql_fix_privilege_tables
  mysql_fix_privilege_tables


===mysqldump: out of memory===
=== mysqldump: out of memory ===
 
  mysqldump -q
  mysqldump -q


&nbsp;
=== clean up mysql-bin ===


===clean up mysql-bin===
  purge logs;
  purge logs;
  purge binary logs to 'mysql-bin.xxxyy';
  purge binary logs to 'mysql-bin.xxxyy';


&nbsp;
=== mysqldump: Got error: 1040: Too many connections when trying to connect ===


===mysqldump: Got error: 1040: Too many connections when trying to connect===
=== mysqldump: MySQL server has gone away (2006) ===


seems related to max_allowed_packet [[File:Example.jpg|RTENOTITLE]]


===mysqldump: MySQL server has gone away (2006)===
&nbsp;
seems related to max_allowed_packet
[[Image:Example.jpg]]


=== ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0 ===


===ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0===
Could be /etc/hosts.allow
Could be /etc/hosts.allow


===ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails===
=== ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails ===


=== ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ===


===ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction===
  SHOW ENGINE INNODB STATUS
  SHOW ENGINE INNODB STATUS
will probably provide clues, it might well be a matter of reducing load/qps
will probably provide clues, it might well be a matter of reducing load/qps


===error 1146 Table 'performance_schema.session_variables' doesn't exist SQL=SHOW VARIABLES LIKE "collation_database"===
=== error 1146 Table 'performance_schema.session_variables' doesn't exist SQL=SHOW VARIABLES LIKE "collation_database" ===
 
Run mysql_upgrade and restart mysqld
Run mysql_upgrade and restart mysqld


===show table sizes===
=== show table sizes ===
 
  SELECT table_schema as `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`  FROM information_schema.TABLES  ORDER BY (data_length + index_length) DESC;
  SELECT table_schema as `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`  FROM information_schema.TABLES  ORDER BY (data_length + index_length) DESC;


&nbsp;
=== InnoDB: page_cleaner: 1000ms intended loop took 16955ms. The settings might not be optimal. ===


===InnoDB: page_cleaner: 1000ms intended loop took 16955ms. The settings might not be optimal.===
  ???
  ???


&nbsp;
=== Check fragmentation ===


===Check fragmentation===
  select ENGINE,
  select ENGINE,
   concat(TABLE_SCHEMA, '.', TABLE_NAME) as table_name,
   concat(TABLE_SCHEMA, '.', TABLE_NAME) as table_name,
Line 172: Line 224:
  ORDER BY frag_ratio DESC;
  ORDER BY frag_ratio DESC;


==Show processes==
== Show processes ==
 
  SELECT * from INFORMATION_SCHEMA.PROCESSLIST
  SELECT * from INFORMATION_SCHEMA.PROCESSLIST
  show full processlist
  show full processlist


=Tips & Tricks=
= Tips & Tricks =
==save password==
 
== save password ==
 
   mysql_config_editor set --host=localhost --user=root --password
   mysql_config_editor set --host=localhost --user=root --password
(due to serious crappiness you might have to quote the password)
(due to serious crappiness you might have to quote the password)


On newer versions this no longer works, and you have to fall back to using ~/.my.cnf
On newer versions this no longer works, and you have to fall back to using ~/.my.cnf
  [client]
  [client]
  user=root
  user=root
  password=s3cr3t
  password=s3cr3t


==set pager==
== set pager ==
 
  pager less;
  pager less;
and to disable it again:
and to disable it again:
  nopager;
  nopager;
  [[Category:Pages with broken file links]]

Revision as of 14:22, 26 November 2020

Documentation

Fragmented tables

 

Data types

https://dev.mysql.com/doc/refman/8.0/en/data-types.html

Tools

Create a diagram

Show syntax

http://dev.mysql.com/doc/refman/5.1/en/show.html

Tuning MySQL

In my.cnf check out:

log_slow_queries

On prompt check out stuff like:

SHOW STATUS LIKE '%qcache%';
SHOW GLOBAL STATUS;
SHOW VARIABLES;

 

FAQ

Clustering

Check out percona galera (and xtrabackup)

server went away

  • can be triggered by max_packet_size

 

change password

alter user 'foo'@'localhost' identified by 's3cr3t5';

stop query

show processlist
kill <id of query>

run query in script

RES=`mysql -qsBe "select foo from fu.bar where tap ='beer'" -N`

where did mysql_config_editor go?

https://mariadb.com/resources/blog/mysql-56-security-through-complacency explains how no security is better than some security

high memory usage

fix logrotate on debian

echo "SET PASSWORD FOR 'debian-sys-maint'@'localhost' = PASSWORD('`grep password /etc/mysql/debian.cnf | head -n 1 | awk -F= '{ gsub(/[ \t]+/, "", $2); print $2 }'`');" | mysql -u root -p

insert select

 insert into bar(c,d) select concat ("foo ",w) as  c, concat("bar ",w) as d from foo;

 

slow imports

https://support.tigertech.net/mysql-large-inserts

speed up import from dump

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

and after import

COMMIT;
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;

and turn them back on

and try mysqldump --extended-insert (this seems to be default now, and part of --opt)

pagination in CLI

show tables;\P less

show mysql configuration

show variables

 

show performance

show innodb status

or

SHOW ENGINE INNODB STATUS

restore single table from backup

{

mysql error codes

http://dev.mysql.com/doc/refman/5.1/en/error-messages-client.html

 

separate lines in mysqldump

--extended-insert=FALSE --complete-insert=TRUE 

Fix privileges

mysql_fix_privilege_tables

mysqldump: out of memory

mysqldump -q

 

clean up mysql-bin

purge logs;
purge binary logs to 'mysql-bin.xxxyy';

 

mysqldump: Got error: 1040: Too many connections when trying to connect

mysqldump: MySQL server has gone away (2006)

seems related to max_allowed_packet RTENOTITLE

 

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Could be /etc/hosts.allow

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

SHOW ENGINE INNODB STATUS

will probably provide clues, it might well be a matter of reducing load/qps

error 1146 Table 'performance_schema.session_variables' doesn't exist SQL=SHOW VARIABLES LIKE "collation_database"

Run mysql_upgrade and restart mysqld

show table sizes

SELECT table_schema as `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`  FROM information_schema.TABLES  ORDER BY (data_length + index_length) DESC;

 

InnoDB: page_cleaner: 1000ms intended loop took 16955ms. The settings might not be optimal.

???

 

Check fragmentation

select ENGINE,
 concat(TABLE_SCHEMA, '.', TABLE_NAME) as table_name,
 round(DATA_LENGTH/1024/1024, 2) as data_length,
 round(INDEX_LENGTH/1024/1024, 2) as index_length,
 round(DATA_FREE/1024/1024, 2) as data_free,
 (data_free/(index_length+data_length)) as frag_ratio
FROM information_schema.tables
WHERE DATA_FREE > 0
ORDER BY frag_ratio DESC;

Show processes

SELECT * from INFORMATION_SCHEMA.PROCESSLIST
show full processlist

Tips & Tricks

save password

 mysql_config_editor set --host=localhost --user=root --password

(due to serious crappiness you might have to quote the password)

On newer versions this no longer works, and you have to fall back to using ~/.my.cnf

[client]
user=root
password=s3cr3t

set pager

pager less;

and to disable it again:

nopager;