MySQL

From DWIKI
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.

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;