MySQL: Difference between revisions

From DWIKI
(44 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]
*[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]]


==Tools==
= Links =
*http://www.adminer.org/
*phpmyadmin
*http://phpminiadmin.sourceforge.net/
===Create a diagram===
*[http://plindenbaum.blogspot.com/2008/10/creating-dia-diagrams-from-mysql-via.html Create a dia diagram]


===Show syntax===
*[https://downloads.mysql.com/archives/community/ Downloads]
http://dev.mysql.com/doc/refman/5.1/en/show.html


===Tuning MySQL===
*[https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/ A Quick Guide to Using the MySQL APT Repository]
*http://www.mysqlperformanceblog.com/
 
*[http://www.warelab.org/blog/?p=100 Optimizing MySQL server variables]
 
*[[mytop]]
 
= 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://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
== Clustering ==
Check out percona galera (and xtrabackup)
==Misc==
=== server went away ===


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


==FAQ==
 
 
 
 
=== stop query ===
 
show processlist
kill <id of query>
 
&nbsp;
 
=== InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes ===
 
(re)move ib_logfile[01]
 
&nbsp;
 
 
=== 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 ===


===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
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
awk �/Table structure for table .test1./,/Table structure for table .test2./{print}� mydumpfile.sql > /tmp/extracted_table.sql
 
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
=== 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 ===


===mysqldump: MySQL server has gone away (2006)===
SHOW ENGINE INNODB STATUS
seems related to max_allowed_packet
[[Image:Example.jpg]]


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


===ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0===
=== error 1146 Table 'performance_schema.session_variables' doesn't exist ===
Could be /etc/hosts.allow
 
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;
 
&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. ===
 
???
 
&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 ===
 
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 =


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


=Tips & Tricks=
==save password==
   mysql_config_editor set --host=localhost --user=root --password
   mysql_config_editor set --host=localhost --user=root --password


==set pager==
(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;
  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;