MySQL

From DWIKI
Revision as of 17:15, 8 November 2021 by Tony (talk | contribs)
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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


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

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 SQL=SHOW VARIABLES LIKE "collation_database"

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;

 

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;