MySQL: Difference between revisions

From DWIKI
mNo edit summary
 
(32 intermediate revisions by the same user not shown)
Line 59: Line 59:
*[http://www.mysqlperformanceblog.com/ http://www.mysqlperformanceblog.com/]  
*[http://www.mysqlperformanceblog.com/ http://www.mysqlperformanceblog.com/]  
*[http://www.warelab.org/blog/?p=100 Optimizing MySQL server variables]  
*[http://www.warelab.org/blog/?p=100 Optimizing MySQL server variables]  
*[https://github.com/BMDan/tuning-primer.sh MySQL Tuning-Primer.sh]
*[[Mytop|mytop]]  
*[[Mytop|mytop]]  
*[[Mysqltuner|mysqltuner]]  
*[[Mysqltuner|mysqltuner]]  
Line 86: Line 87:


 
 
=HOWTO=
==MySQL users and privileges==
===Create user===
create user 'foo'@'localhost' identified by 't0ps3cr37';
flus privilegesh;
===Grant access===
Granting all rights, which should hardly ever be what you should use
grant all privileges on yourdatabase.* to 'you'@'localhost';
===Change password===
alter user 'foo'@'localhost' identified by 's3cr3t5';
seems this no longer works on ubuntu/mysql8? nor does
mysqladmin -u root --password
===Show user privileges===
show grants for 'user'@'localhost';
===Using .mylogin.cnf===
https://dev.mysql.com/doc/refman/8.0/en/mysql-config-editor.html
==Reset auto increment==
ALTER TABLE tablename AUTO_INCREMENT = 1
==Disable binlog==
See [https://dba.stackexchange.com/questions/72770/disable-mysql-binary-logging-with-log-bin-variable Disable MySQL binary logging with log_bin variable]
On mysql8+:
[mysqld]
disable_log_bin
==MySQL processes==
*[https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html SHOW PROCESSLIST]
*[https://mariadb.com/kb/en/show-processlist/ mariadb show processlist] (progress field is only in newer versions)
=== stop query ===
'''ACHTUNG this will trigger massive rollback, reconsider if trying to kill a mass insert/update/delete!'''
show full processlist
kill <id of query>
=MySQL configuration=
==join_buffer_size==
Do not set to crazy high when mysqltuner suggests this. This is a per thread setting!
Check [https://serverfault.com/questions/1099477/how-to-find-joins-performed-without-indexes How to find joins performed without indexes?]
==Procedures==
*[https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html Create procedure]
===Show procedures===
SHOW PROCEDURE STATUS WHERE db = 'your_database_name';


= FAQ =
= FAQ =
==mysqld not starting==
Check https://mariadb.com/kb/en/what-to-do-if-mariadb-doesnt-start/
try
mysqld --help --verbose | grep 'log-error' | tail -1
and
mysqld --help --verbose | grep 'datadir' | tail -1
==Finding mysql configuration files==
===On Ubuntu===
mysqld:
/etc/mysql/mysql.conf.d/mysqld.cnf
or
/etc/mysql/mariadb.conf.d/50-server.cnf
or
ps aux|grep mysqld | grep config-file
==ERROR 1231 (42000): Variable 'slow_query_log_file' can't be set to the value of '/var/log/mysql-slow.log'==
Means the file doesn't exist, create it and chown mysql.mysql ( or similar )
==Accounts==
==Accounts==
https://dev.mysql.com/doc/refman/8.0/en/show-grants.html
https://dev.mysql.com/doc/refman/8.0/en/show-grants.html
Line 94: Line 172:


===Set password to user on auth_socket===
===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';
  alter user 'root'@'localhost' identified with caching_sha2_password by 's3cr37';
  flush privileges;
 
==Server==
===[ERROR] Invalid (old?) table or database name 'lost+found'===
In server config:
  ignore-db-dir=lost+found


== Clustering ==
== Clustering ==
Line 101: Line 184:
Check out percona galera (and xtrabackup)
Check out percona galera (and xtrabackup)


== server went away ==
==Misc==
=== server went away ===


*can be triggered by max_packet_size  
*can be triggered by max_packet_size  
Line 109: Line 193:
&nbsp;
&nbsp;


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


&nbsp;
&nbsp;
Line 130: Line 202:
&nbsp;
&nbsp;


=== change rights ===


=== run query in script ===
=== run query in script ===
Line 210: Line 281:


=== maximum statement execution time exceeded when dumping table ===
=== maximum statement execution time exceeded when dumping table ===
max_execution_time


=== mysqldump: out of memory ===
=== mysqldump: out of memory ===
Line 223: Line 295:
===mysqldump: Dumping 'information_schema' DB content is not supported===
===mysqldump: Dumping 'information_schema' DB content is not supported===
Just don't try to dump it :)
Just don't try to dump it :)
===Warning: /usr/bin/mysqldump: ignoring option '--databases' due to invalid value 'mysql'===
Check your ~/.my.cnf
===Cannot load from mysql.proc. The table is probably corrupted (1728)===
First try
repair table mysql.proc
==mysqldump: Couldn't execute 'FLUSH TABLES'==
New feature, add GRANT RELOAD,PROCESS


== Other stuff ==
== Other stuff ==
Line 237: Line 320:
  purge binary logs to 'mysql-bin.xxxyy';
  purge binary logs to 'mysql-bin.xxxyy';


&nbsp;
&nbsp;


&nbsp;


=== 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 ===
Line 261: Line 340:
== ERROR 1410 (42000): You are not allowed to create a user with GRANT ==
== 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 '%'
First create user:
create user 'foo'@'localhost' identified by 'topsecret';
then
grant all privileges on foobar.* to 'foo'@'localhost';
flush privileges;
 
OR maybe you got wrong host for the user, like 'localhost' instead of '%'


=== show table sizes ===
=== show table sizes ===
Line 303: Line 388:
  ORDER BY frag_ratio DESC;
  ORDER BY frag_ratio DESC;


== Show processes ==


SELECT * from INFORMATION_SCHEMA.PROCESSLIST
 
  show full processlist
==Delete millions of rows==
https://medium.com/@aliasav/how-to-delete-millions-of-rows-from-mysql-a0e37cd1f62
 
 
==Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'==
  alter user 'you'@'localhost identified WITH caching_sha2_password by 'yourpassword';


= Tips & Tricks =
= Tips & Tricks =

Latest revision as of 11:45, 7 February 2024

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

 

HOWTO

MySQL users and privileges

Create user

create user 'foo'@'localhost' identified by 't0ps3cr37';
flus privilegesh;

Grant access

Granting all rights, which should hardly ever be what you should use

grant all privileges on yourdatabase.* to 'you'@'localhost';

Change password

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

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

mysqladmin -u root --password

Show user privileges

show grants for 'user'@'localhost';

Using .mylogin.cnf

https://dev.mysql.com/doc/refman/8.0/en/mysql-config-editor.html


Reset auto increment

ALTER TABLE tablename AUTO_INCREMENT = 1


Disable binlog

See Disable MySQL binary logging with log_bin variable On mysql8+:

[mysqld]
disable_log_bin


MySQL processes

stop query

ACHTUNG this will trigger massive rollback, reconsider if trying to kill a mass insert/update/delete!

show full processlist
kill <id of query>

MySQL configuration

join_buffer_size

Do not set to crazy high when mysqltuner suggests this. This is a per thread setting!

Check How to find joins performed without indexes?


Procedures

Show procedures

SHOW PROCEDURE STATUS WHERE db = 'your_database_name';

FAQ

mysqld not starting

Check https://mariadb.com/kb/en/what-to-do-if-mariadb-doesnt-start/ try

mysqld --help --verbose | grep 'log-error' | tail -1

and

mysqld --help --verbose | grep 'datadir' | tail -1

Finding mysql configuration files

On Ubuntu

mysqld:

/etc/mysql/mysql.conf.d/mysqld.cnf

or

/etc/mysql/mariadb.conf.d/50-server.cnf

or

ps aux|grep mysqld | grep config-file

ERROR 1231 (42000): Variable 'slow_query_log_file' can't be set to the value of '/var/log/mysql-slow.log'

Means the file doesn't exist, create it and chown mysql.mysql ( or similar )

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

Server

[ERROR] Invalid (old?) table or database name 'lost+found'

In server config:

ignore-db-dir=lost+found

Clustering

Check out percona galera (and xtrabackup)

Misc

server went away

  • can be triggered by max_packet_size

 

 


 

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

max_execution_time

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 :)

Warning: /usr/bin/mysqldump: ignoring option '--databases' due to invalid value 'mysql'

Check your ~/.my.cnf


Cannot load from mysql.proc. The table is probably corrupted (1728)

First try

repair table mysql.proc

mysqldump: Couldn't execute 'FLUSH TABLES'

New feature, add GRANT RELOAD,PROCESS

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

First create user:

create user 'foo'@'localhost' identified by 'topsecret';

then

grant all privileges on foobar.* to 'foo'@'localhost';
flush privileges;

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


Delete millions of rows

https://medium.com/@aliasav/how-to-delete-millions-of-rows-from-mysql-a0e37cd1f62


Plugin mysql_native_password reported: mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

alter user 'you'@'localhost identified WITH caching_sha2_password by 'yourpassword';

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;