MySQL: Difference between revisions

From DWIKI
(29 intermediate revisions by the same user not shown)
Line 1: Line 1:
==Documentation==
*[http://www.mysql.com/ Homepage]
*[http://dev.mysql.com/doc/ Documentation]
*[http://dev.mysql.com/doc/refman/5.1/en/tutorial.html MySQL Tutorial]
*[http://www.nparikh.org/unix/mysql.php MySQL Cheat Sheet]
*[http://www.pantz.org/database/mysql/mysqlcommands.shtml A collection of mysql commands]
*[http://dev.mysql.com/doc/refman/5.0/en/c-api-functions.html MySQL C API Functions]
*[https://haydenjames.io/mysql-query-cache-size-performance/ Avoid This When Tuning MySQL Query Cache for Performance]
*[http://www.tizag.com/mysqlTutorial/mysqljoins.php MySQL joins]
*[http://forge.mysql.com/wiki/Main_Page MySQLForge Wiki]
*[http://www.artfulsoftware.com/infotree/queries.php Common mysql queries]
*http://people.freebsd.org/~kris/scaling/mysql.html
*http://zabbixzone.com/zabbix/mysql-performance-tips-for-zabbix/
*[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]]
[https://serverfault.com/questions/202000/how-find-and-fix-fragmented-mysql-tables Fragmented tables]


==Tools==
= Links =
*http://www.adminer.org/
*phpmyadmin
*http://phpminiadmin.sourceforge.net/
*[https://www.mysqlcalculator.com/ MySQL memory calculator]


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


===Show syntax===
*[https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/ A Quick Guide to Using the MySQL APT Repository]
http://dev.mysql.com/doc/refman/5.1/en/show.html


===Tuning MySQL===
 
*http://www.mysqlperformanceblog.com/
 
*[http://www.warelab.org/blog/?p=100 Optimizing MySQL server variables]
= Documentation =
*[[mytop]]
 
*[[mysqltuner]]
*[http://www.mysql.com/ Homepage]
*[http://dev.mysql.com/doc/ Documentation]
*[http://dev.mysql.com/doc/refman/5.1/en/tutorial.html MySQL Tutorial]
*[http://www.nparikh.org/unix/mysql.php MySQL Cheat Sheet]
*[http://www.pantz.org/database/mysql/mysqlcommands.shtml A collection of mysql commands]
*[http://dev.mysql.com/doc/refman/5.0/en/c-api-functions.html MySQL C API Functions]
*[https://haydenjames.io/mysql-query-cache-size-performance/ Avoid This When Tuning MySQL Query Cache for Performance]
*[http://www.tizag.com/mysqlTutorial/mysqljoins.php MySQL joins]
*[http://forge.mysql.com/wiki/Main_Page MySQLForge Wiki]
*[http://www.artfulsoftware.com/infotree/queries.php Common mysql queries]
*[http://people.freebsd.org/~kris/scaling/mysql.html http://people.freebsd.org/~kris/scaling/mysql.html]
*[http://zabbixzone.com/zabbix/mysql-performance-tips-for-zabbix/ http://zabbixzone.com/zabbix/mysql-performance-tips-for-zabbix/]
*[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://mariadb.com/kb/en/mariadb-error-codes/ Mariadb error codes]
*[https://dev.mysql.com/doc/mysql-errors/8.0/en/client-error-reference.html Client error reference]
 
*[https://serverfault.com/questions/202000/how-find-and-fix-fragmented-mysql-tables Fragmented tables]
 
 
 
== Data types ==
 
[https://dev.mysql.com/doc/refman/8.0/en/data-types.html https://dev.mysql.com/doc/refman/8.0/en/data-types.html]
 
 
 
 
 
= Tools =
 
*[http://www.adminer.org/ Adminer] ('''check out!''' )
*[[Phpmyadmin|phpmyadmin]]
*[http://phpminiadmin.sourceforge.net/ http://phpminiadmin.sourceforge.net/]
*[https://www.mysqlcalculator.com/ MySQL memory calculator]
 
== Create a diagram ==
 
*[http://plindenbaum.blogspot.com/2008/10/creating-dia-diagrams-from-mysql-via.html Create a dia diagram]
 
== 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]
 
== Benchmarking ==
=== mysqlslap ===
*[https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html mysqlslap documentation]
 
= FAQ =
==Accounts==
https://dev.mysql.com/doc/refman/8.0/en/show-grants.html
show grants
revoke
===Set password to user on auth_socket===
This just changes from auth_socket to password
alter user 'root'@'localhost' identified with caching_sha2_password by 's3cr37';
flush privileges;
== change password ==
alter user 'foo'@'localhost' identified by 's3cr3t5';
seems this no longer works on ubuntu/mysql8? nor does
mysqladmin -u root --password




*[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===
==Misc==
*can be triggered by max_packet_size
=== server went away ===
 
*can be triggered by max_packet_size  
 
 
 
 
 
=== stop query ===


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


===run query in script===
&nbsp;
 
=== InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes ===
 
(re)move ib_logfile[01]
 
&nbsp;
 
 
=== 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
 
[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
 
=== high memory usage ===
 
*[https://bobcares.com/blog/fix-mysql-high-memory-usage/ https://bobcares.com/blog/fix-mysql-high-memory-usage/]


===high memory usage===
=== fix logrotate on debian ===
*https://bobcares.com/blog/fix-mysql-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
  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===
=== slow imports ===
  https://support.tigertech.net/mysql-large-inserts
 
  [https://support.tigertech.net/mysql-large-inserts https://support.tigertech.net/mysql-large-inserts]
 
=== speed up import from dump ===


===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


===restore single table from backup===
or
{
 
SHOW ENGINE INNODB STATUS
 
&nbsp;
 
=== 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]
 
&nbsp;


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


== mysqldump ==
=== separate lines in mysqldump ===


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


===Fix privileges===
=== maximum statement execution time exceeded when dumping table ===
 
=== mysqldump: out of memory ===
 
mysqldump -q
 
=== 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: Dumping 'information_schema' DB content is not supported===
Just don't try to dump it :)
 
== Other stuff ==
 
=== Fix privileges ===
 
  mysql_fix_privilege_tables
  mysql_fix_privilege_tables


===mysqldump: out of memory===
&nbsp;
mysqldump -q


=== 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';




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


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


===mysqldump: MySQL server has gone away (2006)===
=== ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails ===
seems related to max_allowed_packet
[[Image:Example.jpg]]


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


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


===ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails===
will probably provide clues, it might well be a matter of reducing load/qps


=== error 1146 Table 'performance_schema.session_variables' doesn't exist ===


===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===
== ERROR 1410 (42000): You are not allowed to create a user with GRANT ==
 
Maybe you got wrong host for the user, like 'localhost' instead of '%'
 
=== 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;
==show database sizes==
SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM
information_schema.tables
GROUP BY table_schema;
=== 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;
=== ERROR 1010 (HY000): Error dropping database (can't rmdir './foo', errno: 39) ===
Files in $datadir/foo mysql doesn't know about
&nbsp;
=== privileges for SHOW GLOBAL STATUS ===
CREATE USER 'zbx_monitor'@'localhost' IDENTIFIED BY '<password>';
GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'localhost';
=== 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 158: Line 301:
  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 09:28, 20 June 2022

Links


Documentation

 

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;

 

 

Benchmarking

mysqlslap

 

FAQ

Accounts

https://dev.mysql.com/doc/refman/8.0/en/show-grants.html

show grants
revoke 

Set password to user on auth_socket

This just changes from auth_socket to password

alter user 'root'@'localhost' identified with caching_sha2_password by 's3cr37';
flush privileges;

change password

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

seems this no longer works on ubuntu/mysql8? nor does

mysqladmin -u root --password


Clustering

Check out percona galera (and xtrabackup)

Misc

server went away

  • can be triggered by max_packet_size

 

 

stop query

show processlist
kill <id of query>

 

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes

(re)move ib_logfile[01]

 


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

 

mysql error codes

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

 

 

mysqldump

separate lines in mysqldump

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

maximum statement execution time exceeded when dumping table

mysqldump: out of memory

mysqldump -q

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

mysqldump: Dumping 'information_schema' DB content is not supported

Just don't try to dump it :)

Other stuff

Fix privileges

mysql_fix_privilege_tables

 

clean up mysql-bin

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


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

Run mysql_upgrade and restart mysqld

ERROR 1410 (42000): You are not allowed to create a user with GRANT

Maybe you got wrong host for the user, like 'localhost' instead of '%'

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;

 

show database sizes

SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM 
information_schema.tables 
GROUP BY table_schema; 

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

???

 

ERROR 1010 (HY000): Error dropping database (can't rmdir './foo', errno: 39)

Files in $datadir/foo mysql doesn't know about

 

privileges for SHOW GLOBAL STATUS

CREATE USER 'zbx_monitor'@'localhost' IDENTIFIED BY '<password>';
GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'localhost';

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;