MySQL - The world's most popular open source database

select now(), curdate();

+---------------------+------------+
| now()               | curdate()  |
+---------------------+------------+
| 2018-06-26 12:38:22 | 2018-06-26 |
+---------------------+------------+

select now() - interval 40 day;

+-------------------------+
| now() - interval 40 day |
+-------------------------+
| 2018-05-17 12:39:03     |
+-------------------------+

select curdate() - interval 40 day;

+-----------------------------+
| curdate() - interval 40 day |
+-----------------------------+
| 2018-05-17                  |
+-----------------------------+

select date_sub(curdate(), interval 40 day);

+--------------------------------------+
| date_sub(curdate(), interval 40 day) |
+--------------------------------------+
| 2018-05-17                           |
+--------------------------------------+

1. Stop MySQL :
   systemctl stop mysql
2. Start again MySQL without grant tables :
   mysqld_safe --skip-grant-tables &
   If this outputs :

   2019-11-05T14:19:05.852712Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

   Run :
   mkdir -p /var/run/mysqld && chown mysql:mysql /var/run/mysqld
   and retry (source)
3. Login as root :
   mysql -u root
4. Set new password from MySQL shell (mysql> prompt) :
   1. use mysql;
   2. update user set password=PASSWORD("newRootPassword") where user='root';
      update user set authentication_string=PASSWORD("newRootPassword") where user='root';
   3. check :
      select user, host, authentication_string, PASSWORD("newRootPassword") from user where user='root';

      +------+-----------+-------------------------------------------+-------------------------------------------+
      | user | host      | authentication_string                     | PASSWORD("newRootPassword")               |
      +------+-----------+-------------------------------------------+-------------------------------------------+
      | root | localhost | *C45EE169D5D766B74FE801AB43D8DCB09A61837A | *C45EE169D5D766B74FE801AB43D8DCB09A61837A |
      | root | %         | *C45EE169D5D766B74FE801AB43D8DCB09A61837A | *C45EE169D5D766B74FE801AB43D8DCB09A61837A |
      +------+-----------+-------------------------------------------+-------------------------------------------+
      2 rows in set, 1 warning (0.00 sec)

   4. flush privileges;
   5. quit
5. Stop the mysqld_safe process you just started above :
   pkill -15 mysqld_safe && pgrep mysqld_safe
6. Start MySQL :
   systemctl start mysql
7. Log in as root with your new password :
   mysql -u root -pnewRootPassword

mysql> show master status;
Empty set (0.00 sec)

mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging

• /var/log/mysql.err
• /var/log/mysql/error.log.*
• grep -r log-error /etc/mysql/*
• ls -lt /var/log/mysql/error.log* | head -10

• systemctl status mysql -l
• journalctl -u mysql

systemctl gave some good hints :

Mar 21 01:15:02 mysql mysqld[29666]: FAILFAILFAILFAILFAILFAILFAILFAILFAILFAIL180321	1:15:02 [ERROR] The table 'tableName' is full
Mar 21 01:21:00 mysql mysqld[29666]: 180321	1:21:00 [Warning] mysqld: Disk is full writing '/var/lib/mysql/data/binlogs/mysql-bin.000010' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
Mar 21 01:21:00 mysql mysqld[29666]: 180321	1:21:00 [Warning] mysqld: Retry in 60 secs. Message reprinted in 600 secs
Mar 21 01:22:00 mysql mysqld[29666]: 180321	1:22:00 [Warning] mysqld: Disk is full writing '/var/lib/mysql/data/binlogs/mysql-bin.000010' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
Mar 21 01:22:00 mysql mysqld[29666]: 180321	1:22:00 [Warning] mysqld: Retry in 60 secs. Message reprinted in 600 secs
Mar 21 01:32:00 mysql mysqld[29666]: 180321	1:32:00 [Warning] mysqld: Retry in 60 secs. Message reprinted in 600 secs
Mar 21 01:34:00 mysql mysqld[29666]: 180321	1:34:00 [ERROR] Could not use /var/lib/mysql/data/binlogs/mysql-bin for logging (error 0). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
Mar 21 01:34:00 mysql mysqld[29666]: 180321	1:34:00 [ERROR] Could not open /var/lib/mysql/data/binlogs/mysql-bin.000011 for logging (error 0). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.	Trying to open the next binlog
Mar 21 01:34:00 mysql mysqld[29666]: 180321	1:34:00 [ERROR] mysqld: Error writing file '(null)' (errno: 9 "Bad file descriptor")

1. systemctl restart mysql
2. systemctl status mysql -l

• This happened after upgrading myServer from Jessie (8.x) to Stretch (9.0)
• This also upgraded :
  • PHP to version 7.0
  • MariaDB to version 15.1
• The web application running on myServer also had difficulties connecting to MySQL (MariaDB, actually) :
  Can not connect to MySQL : module mysqli not available
  (or ... not loaded or something like that )

-- Unit mariadb.service has begun starting up.
Dec 12 15:48:34 myServer systemd[3474]: mariadb.service: Failed at step NAMESPACE spawning /usr/bin/install: Too many levels of symbolic links
-- Subject: Process /usr/bin/install could not be executed
-- Defined-By: systemd
-- Support: https://www.debian.org/support
--
-- The process /usr/bin/install could not be executed and failed.

Some hints found on serverfault.com did not help fixing it.

Final words

select user();

+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

When connected to MySQL as root, you're known as root@localhost.

1. as root, on the master server :
   echo 'show master status\G' | mysql -u root | grep -E 'File|Position'

   	File: mysql-bin.000363
   	Position: 55657

2. as root, on the slave server :
   echo 'show slave status\G' | mysql -u root | grep -E ' Master_Log_File|Read_Master_Log_Pos'

   	Master_Log_File: mysql-bin.000363
   	Read_Master_Log_Pos: 55657

3. If fields match :
   • congratulations : both servers are in sync !!!
   • Otherwise :
     • Servers may currently be busy synchronizing (especially if you've just restarted one of them). Re-check in a few seconds.
     • If this fails, I have bad news for you : you're ahead of some replication debug ...

For all tables of a database :

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'dbName';

For a single table :

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_NAME = 'tableName';

General case :

show table status; (very verbose !)

Upon creation :

CREATE USER 'stuart'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON customer.* TO 'stuart'@'localhost' WITH MAX_USER_CONNECTIONS 2;

On an existing account :

GRANT USAGE ON *.* TO 'stuart'@'localhost' WITH MAX_USER_CONNECTIONS 100;

Removing a limit (set value to 0) :

GRANT USAGE ON *.* TO 'stuart'@'localhost' WITH MAX_USER_CONNECTIONS 0;

To reset counts for all accounts :

Just reload the grant tables : flush privileges;

Read the global variable max_user_connections :

select @@global.max_user_connections;

The per-account MAX_USER_CONNECTIONS overrides the global max_user_connections :

• No per-account limit : the global setting max_user_connections rules.
• A per-account MAX_USER_CONNECTIONS setting exists : it overrides the global max_user_connections

Unix timestamp into human-readable date :

+---------------------------+
| FROM_UNIXTIME(1262428560) |
+---------------------------+
| 2010-01-02 11:36:00       |
+---------------------------+

Human-readable date into Unix timestamp :

+---------------------------------------+
| UNIX_TIMESTAMP('2014-01-03 17:21:42') |
+---------------------------------------+
|                            1388766102 |
+---------------------------------------+

srcDbServer='prodServer'; srcDbUser='root'; srcDbPass='password'; srcDbName='prodDbName'; dstDbServer='devServer'; dstDbUser='root'; dstDbPass='password'; dstDbName='devDbName'; regExpNeedle='prod\.example\.com'; regExpReplacement='dev\.example\.com'; mysqldump -h $srcDbServer -u $srcDbUser -p$srcDbPass $srcDbName | sed "s/$regExpNeedle/$regExpReplacement/g" | mysql -h $dstDbServer -u $dstDbUser -p$dstDbPass $dstDbName

... a Bash variable :

+------------+
| fieldName |
+------------+
|      1234 |
+------------+

... CSV :

select password("you can use any string"); select SHA1(UNHEX(SHA1("you can use any string")));

Read from file :

CREATE TABLE tableName(line blob);
LOAD DATA INFILE '/file/to/read' INTO TABLE tableName;
SELECT * FROM tableName;

file='/file/to/read'; dbUser='bob'; dbPass='password'; dbName='testDb'; tableName='testTable'; mysql -u $dbUser -p$dbPass -e "create database $dbName; use $dbName; create table $tableName(line blob); load data infile '$file' into table $tableName; select * from $tableName; drop database $dbName;"

Write to file :

Dump :

Restore :

DEFINER=`wrongUser`@`wrongHost`

DEFINER=`rightUser`@`rightHost`

optimize table myTable;

• DB needs to have some HDD space to save its data; if it doesn't have any space, it reserves some empty space at first.
• When you insert a new row, a piece of that space is used.
• When you run out of free space, a new block is reserved, and so on.
• Now, when you delete some rows, in order to prevent reserving more and more blocks, its space is kept free but never given back to the operating system, so you can use it again later without any need of reserving new blocks.

Optimize all tables of the server :

MySQL 5.1+ (source) :

1. Count MySQL processes per user from the system shell :
   • the one-shot version : dbHost='mysqlServer'; myUser='root'; myPassword='password'; mysql -h $dbHost -u $myUser -p"$myPassword" -e "SELECT user, count(1) as nbProcesses FROM information_schema.processlist GROUP BY user ORDER BY nbProcesses DESC;"
   • the watch version : watch -n 2 -d "dbHost='mysqlServer'; myUser='root'; myPassword='password'; mysql -h $dbHost -u $myUser -p\"$myPassword\" -e \"SELECT user, count(1) AS nbProcesses FROM information_schema.processlist GROUP BY user ORDER BY nbProcesses DESC;\""
2. KILL KILL KILL ! : dbHost='mysqlServer'; evilDbUser='evilUser'; myUser='root'; myPassword='password'; for PID in $(mysql -h $dbHost -u $myUser -p"$myPassword" -e "SELECT id FROM information_schema.processlist WHERE user=\"$evilDbUser\";" | grep -v 'id'); do echo 'KILL : '$PID; mysql -h $dbHost -u $myUser -p"$myPassword" -e "kill $PID;"; done

MySQL (previous versions) :

1. List PIDs from the system shell : myUser='root'; myPassword='password'; myDb='databaseName'; for PID in $(mysql -u $myUser -p"$myPassword" -e 'show processlist;' | grep $myDb | cut -f1); do echo "MySQL PID : $PID"; done
2. KILL KILL KILL ! :
   • local version :

     myUser='root'; myPassword='password'; myDb='databaseName'; for PID in $(mysql -u $myUser -p"$myPassword" -e 'show processlist;' | grep $myDb | cut -f1); do echo 'KILL !'; mysql -u $myUser -p$myPassword -e "kill $PID;"; done

   • network version :

     mysqlHost="mysqlServer"; myUser='root'; myPassword='password'; myDb='databaseName'; for PID in $(mysql -h $mysqlHost -u $myUser -p"$myPassword" -e 'show processlist;' | grep $myDb | cut -f1); do echo 'KILL !'; mysql -h $mysqlHost -u $myUser -p$myPassword -e "kill $PID;"; done

List of users having active processes + number of processes at the time the query is executed :

SELECT
	user,
	COUNT(user) AS nbProcess
FROM
	information_schema.PROCESSLIST
GROUP BY
	user
ORDER BY
	COUNT(user) DESC

List of all MySQL users and their number of active processes at the time the query is executed :

SELECT
	U.user,
	COUNT(PL.user) AS nbProcess
FROM
	mysql.user U
	LEFT JOIN information_schema.PROCESSLIST PL ON PL.user=U.user
GROUP BY
	U.user
ORDER BY
	COUNT(PL.user) DESC

Whole database size (source) :

SELECT
	table_schema AS dbName,
	ROUND( SUM( data_length + index_length ) / 1024 / 1024, 3 ) AS dbSizeMb
FROM
	information_schema.TABLES
WHERE
	table_schema="myDatabase"
GROUP BY
	table_schema;

List of all tables of a database, with their size :

SELECT
	table_name,
	table_rows,
	ROUND(( data_length + index_length ) / 1024 / 1024, 0) AS table_size_MB
FROM
	information_schema.tables
WHERE
	table_schema="dbName"
ORDER BY
	table_size_MB DESC,
	table_rows DESC;

Size and number of rows of a single table :

SELECT
	table_name,
	table_rows,
	ROUND(( data_length + index_length ) / 1024 / 1024, 2) AS table_size_MB
FROM
	information_schema.TABLES
WHERE
	table_schema = "piwik"
	AND table_name = "piwik_log_link_visit_action";

Percona configuration :

Process list (source)

Get MySQL version

INFORMATION_SCHEMA and GRANT (source)

Create a new database

Create a "read-only" account (source) :

Create user accounts

Grants

Create a user account for an application using the old MySQL password encryption method (source) :

Delete a user account

Check a given password matches a MySQL account password :

SELECT
	U.user,
	U.host,
	IF (password('clearPassword')=U.password,'Yes','No') AS 'passwords match ?'
FROM
	mysql.user U
WHERE
	U.user='bob';

Kind of whoami :

+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+

dbNameCurrent='oldDbName'; dbNameNew='newDbName'; dbUser='bob'; dbPassword='password'; stringOk='[OK]'; stringKo='[KO]'; echo -n "Creating new empty DB \"$dbNameNew\" ... "; mysql -u $dbUser -p$dbPassword -e "create database $dbNameNew" && { echo $stringOk; for t in $(mysql -u $dbUser -p$dbPassword -e "show tables from $dbNameCurrent;" | grep -v "Tables_in_$dbNameCurrent"); do echo -n "Renaming table \"${dbNameCurrent}.$t\" into \"${dbNameNew}.$t\" ... "; mysql -u $dbUser -p$dbPassword -e "RENAME TABLE ${dbNameCurrent}.$t TO ${dbNameNew}.$t" && echo $stringOk || echo $stringKo; done; echo -n "Checking whether old DB \"$dbNameCurrent\" can be dropped ... "; [ $(mysql -u $dbUser -p$dbPassword -e "SELECT count(1) as '' FROM information_schema.TABLES WHERE table_schema= '$dbNameCurrent'") -eq 0 ] && { echo $stringOk; echo -n "Removing old DB \"$dbNameCurrent\" ... "; mysql -u $dbUser -p$dbPassword -e "DROP DATABASE $dbNameCurrent" && echo $stringOk || echo $stringKo; } || { echo "$stringKo : Can't drop $dbNameCurrent, not empty database."; } } || echo $stringKo

1. List databases / databases + tables using InnoDB :

   SELECT DISTINCT `table_schema` FROM information_schema.tables WHERE engine = "InnoDB";
   SELECT `table_schema`,`table_name` FROM information_schema.tables WHERE engine = "InnoDB";

2. Back up these databases :
   mysqldump -u admin -ppassword --extended-insert --all-databases --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers > /path/to/allDatabases.sql
   If it fails with :
   mysqldump: Got error: 1449: The user specified as a definer ('bob'@'localhost') does not exist when using LOCK TABLES
   then : grant all on *.* to 'bob'@'localhost identified by 'password';
3. Drop InnoDB tables
4. Stop MySQL server
5. From MySQL's data dir (usually /var/lib/mysql/) : rm ibdata1 ib_logfile0 ib_logfile1
6. Update /etc/mysql/my.cnf :

   innodb_file_per_table = true
   innodb_data_file_path = ibdata1:10M:autoextend

7. Restart MySQL
8. Import DB dumps : mysql -u admin -ppassword < /path/to/allDatabases.sql

Get a system variable :

select @@global.wait_timeout;

Set a system variable :

set @@global.wait_timeout=600;

Update :

Setting up replication

Resynchronizing servers

Is master writing into its binary log ?

Is slave running ?

The SQL thread of slave doesn't start. What can I do ?

The I/O thread of slave doesn't start. What can I do ?

What about some RESET MASTER, RESET SLAVE ?

How to flush master's binary log ?

If slave complains : Incorrect datetime value: 'null' for column ...

1. SET FOREIGN_KEY_CHECKS = 0;
2. do the "delete" job
3. SET FOREIGN_KEY_CHECKS = 1;

Database dumps

Same as above + compression

Options for database dumps

Replicate a local database

Replicate a database from a distant server to the local host

truncate

delete

Create a new table

Lock a table (source) :

To insert the current timestamp while inserting a new record :

To update the current timestamp while updating an existing record :