MySQL
Links
Documentation
- Homepage
- Documentation
- MySQL Tutorial
- MySQL Cheat Sheet
- A collection of mysql commands
- MySQL C API Functions
- Avoid This When Tuning MySQL Query Cache for Performance
- MySQL joins
- MySQLForge Wiki
- Common mysql queries
- http://people.freebsd.org/~kris/scaling/mysql.html
- http://zabbixzone.com/zabbix/mysql-performance-tips-for-zabbix/
- Migratie mysql database without downtime
- 10 things to monitor on your mysql server
- MySQL Replication
- Mariadb error codes
- Client error reference
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
- http://www.mysqlperformanceblog.com/
- Optimizing MySQL server variables
- MySQL Tuning-Primer.sh
- mytop
- mysqltuner
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 recovery
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
Make user super admin
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
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
- 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 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
mysqldump: Couldn't execute 'SHOW FIELDS FROM `table1`':
View 'foo.bar' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
??
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;
SELECT table_name,(DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS sizeMb FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB' having sizeMb > 10;
SELECT table_name,(DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS sizeMb FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB' having sizeMb > 10;
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';
ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded
Try
INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
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;