Difference between revisions of "MySQL"

From DWIKI
m
m
Line 1: Line 1:


== Documentation ==
= Documentation =


*[http://www.mysql.com/ Homepage]  
*[http://www.mysql.com/ Homepage]  
Line 26: Line 26:
  [https://dev.mysql.com/doc/refman/8.0/en/data-types.html https://dev.mysql.com/doc/refman/8.0/en/data-types.html]
  [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 ==
= Tools =


*[http://www.adminer.org/ http://www.adminer.org/]  
*[http://www.adminer.org/ http://www.adminer.org/]  
Line 34: Line 35:
*[https://www.mysqlcalculator.com/ MySQL memory calculator]  
*[https://www.mysqlcalculator.com/ MySQL memory calculator]  


=== Create a diagram ===
== Create a diagram ==


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


=== Show syntax ===
== Show syntax ==


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


=== Tuning MySQL ===
== Tuning MySQL ==


*[http://www.mysqlperformanceblog.com/ http://www.mysqlperformanceblog.com/]  
*[http://www.mysqlperformanceblog.com/ http://www.mysqlperformanceblog.com/]  
Line 64: Line 65:
*[http://www.ibm.com/developerworks/web/library/l-tune-lamp-3.html Tuning MysQL]  
*[http://www.ibm.com/developerworks/web/library/l-tune-lamp-3.html Tuning MysQL]  


===Benchmarking===
== Benchmarking ==
*mysqlslap


== FAQ ==
*mysqlslap


=== Clustering ===
= 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  
Line 79: Line 81:
 
 


 


=== change password ===
== change password ==


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


seems this no longer works on ubuntu/mysql8?
seems this no longer works on ubuntu/mysql8? nor does
nor does
 
  mysqladmin -u root --password
  mysqladmin -u root --password


Line 237: Line 240:
 
 


===privileges for SHOW GLOBAL STATUS===
=== privileges for SHOW GLOBAL STATUS ===
 
  CREATE USER 'zbx_monitor'@'localhost' IDENTIFIED BY '<password>';
  CREATE USER 'zbx_monitor'@'localhost' IDENTIFIED BY '<password>';
  GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'localhost';
  GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'localhost';
Line 252: Line 256:
  WHERE DATA_FREE > 0
  WHERE DATA_FREE > 0
  ORDER BY frag_ratio DESC;
  ORDER BY frag_ratio DESC;
  [[Category:Pages with broken file links]]


== Show processes ==
== Show processes ==

Revision as of 12:10, 17 June 2021

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;

 

Benchmarking

  • mysqlslap

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

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

mysqladmin -u root --password

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]

 

change rights

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.

???

 

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;