MySQL - The world's most popular open source database

How to compute dates in queries ?

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

How to recover the MySQL root password ?

  1. Stop MySQL / MariaDB : systemctl stop mysql
  2. Start again without grant tables : mysqld_safe --skip-grant-tables &
  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';
    3. flush privileges;
    4. quit
  5. Stop the mysqld_safe process you just started above : pkill -15 mysqld_safe && pgrep mysqld_safe
  6. Start MySQL : systemctl start mysql

mysqldump

show master status; returns Empty set (0.00 sec)

Situation :

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

Details :

Try :
mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging
This is getting weird, because I'm in the context of a master-slave replication, and binary logging IS enabled. Something unexpected may have happened...
Make the logs talk : But also :

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

Solution :

As adviced in the log above, let's restart MySQL :
  1. systemctl restart mysql
  2. systemctl status mysql -l

Situation :

Details :

systemd logs say :
-- 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.

Solution :

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

The Too many levels of symbolic links message disappeared after I removed a hack in my setup where /root actually symlinked to /home/root (allowing to have all my scripts on the /home partitions and survive re-installs )
Can't find link / hint that led me here

Final words

Here are the answers to some questions that arose during the debug (only related by the time-frame) :
  • the mysqli issue was fixed with :
    apt install php7.0-mysql
  • the PHP configuration is (as per phpinfo();) :
    Configuration File (php.ini) Path		/etc/php/7.0/cgi
    Loaded Configuration File			/etc/php/7.0/cgi/php.ini
    Scan this dir for additional .ini files		/etc/php/7.0/cgi/conf.d

How to get the current user name ?

select user();

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

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

How to make sure the master and slave servers are effectively synchronized ?

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

Which storage engine is used : MyISAM or InnoDB ?

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

How to set / update the root password ?

set update
from the system shell mysqladmin -u root password newMysqlRootPassword
Works only when the root account has no password at all
to do
from the MySQL shell to do update mysql.user set password=password('newMysqlRootPassword') where user='root';
Don't forget to flush privileges; to reload the grant tables in memory

How to set user quotas on the number of connections ?

By default, MySQL accepts up to max_user_connections connections, as defined in /etc/my.cnf.
It is possible to define per-account limits with MAX_USER_CONNECTIONS :
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 :

How to convert between Unix timestamps and human-readable dates ?

Unix timestamp into human-readable date :

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

Human-readable date into Unix timestamp :

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

How to dump and restore a WordPress MySQL database to a different environment ?

The MySQL data of a WordPress site is special in that it has the website name "hardcoded" : www.example.com in internal hyperlinks. Moving a database to a different environment implies to search/replace the environment name :

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

How to extract MySQL data from the shell into ...

... a Bash variable :

With awk :
value=$(mysql -h host -u bob -ppassword -e "[your MySQL query here];" | awk '/[0-9]+/ {print $1}')
The Awk part of the command converts :
+------------+
| fieldName |
+------------+
|      1234 |
+------------+
into 1234. As is, this command only supports numeric values.
With tr and cut :
value=$(mysql -h host -u bob -ppassword -e "[your MySQL query here];" | tr '\n' '+' | cut -d '+' -f 2)
With a count(1) query (and possibly due to an obsolete Bash version), this helped convert the count(1)\n1234 into 1234. No idea where the ASCII-art table borders are gone

... CSV :

  • host='192.168.0.228'; user='bob'; password='secret'; csvFile='/path/to/myFile.csv'; mysql -h $host -u $user -p$password -e "SELECT * FROM myDb.myTable;" | sed 's/\t/;/g' | bzip2 > $csvFile.bz2
    The sed part of the command converts the TAB into ;
  • host='192.168.0.228'; user='bob'; password='secret'; csvFile='/path/to/myFile.csv'; mysql -h $host -u $user -p$password -e "SELECT * FROM myDb.myTable INTO OUTFILE '$csvFile' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'"
    $csvFile :
    • is local to the DB server
    • is written to the filesystem by the user executing the MySQL process : write restrictions may apply.
    this example is still experimental

What is the hashing algorithm implemented in MySQL's PASSWORD() ?

Just try this :

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

Read and write files through MySQL

Read from file :

From the MySQL shell :
CREATE TABLE tableName(line blob);
LOAD DATA INFILE '/file/to/read' INTO TABLE tableName;
SELECT * FROM tableName;
From the system shell :
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;"
This requires :
  • the FILE privilege for the MySQL user
  • /file/to/read to be readable by everybody

Write to file :

SELECT "data to write comes here..." INTO OUTFILE "/path/to/file";

This requires the directory /path/to/ to be writable by everybody

MySQL dump / restore views

Dump :

mysqlHost='host'; mysqlUser='root'; mysqlPass='password'; mysqlDb='dbName'; mysql -h $mysqlHost -u $mysqlUser -p$mysqlPass INFORMATION_SCHEMA --skip-column-names --batch -e "select table_name from tables where table_type = 'VIEW' and table_schema = '$mysqlDb'" | xargs mysqldump -h $mysqlHost -u $mysqlUser -p$mysqlPass $mysqlDb > views.sql

Restore :

Import the generated views.sql file exactly as you'd do with a table dump.

If the views are exported from a server to be imported on another, the views definers may not match, resulting in a MySQL 1449 error. To fix this, edit views.sql and replace all occurrences of :

DEFINER=`wrongUser`@`wrongHost`
with
DEFINER=`rightUser`@`rightHost`

How to reclaim disk space after massive DELETE from a table ?

optimize table myTable;

DBs don't grow in size unnecessarily, but for performance issues space is not freed either. If you delete records, the freed space is not given back to the operating system. Instead, it's kept as an empty space for the DB to re-use afterwards. This is because : Trying to optimize an InnoDB table will display Table does not support optimize, doing recreate + analyze instead (details).

Optimize all tables of the server :

dbUser='root'; dbPass='password'; for dbName in $(mysql -u $dbUser -p$dbPass -e "select distinct table_schema as '' from information_schema.tables where table_schema not in ('mysql', 'performance_schema', 'information_schema');"); do for tableName in $(mysql -u $dbUser -p$dbPass -e "select table_name as '' from information_schema.tables where table_schema='$dbName';"); do echo "OPTIMIZING $dbName.$tableName ..."; mysql -u $dbUser -p$dbPass $dbName -e "optimize table $tableName;"; done; done

Mass-killing of MySQL connections

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

How to count MySQL processes per user ?

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

How to get database / tables size ?

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;

MariaDB :

  • dislikes having a space between SUM and (
  • has TABLE_SCHEMA uppercase

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;

If you're just looking for the biggest table(s), try this : dbName='myDb; ls -Shl "/var/lib/mysql/$dbName" | head -20

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

MySQL tips

Percona configuration :

Percona comes with no /etc/mysql/my.cnf, but examples are available in /usr/share/mysql/my*.cnf

Process list (source)

  • show full processlist;
  • select * from information_schema.processlist;
  • select * from information_schema.processlist where STATE='Copying to tmp table';

Get MySQL version

select version();

INFORMATION_SCHEMA and GRANT (source)

grant select on information_schema.* to 'bob'@'%';
ERROR 1044 (42000): Access denied for user 'root'@'10.0.16.%' to database 'information_schema'
This error message is misleading since you never need to grant any privilege on INFORMATION_SCHEMA : each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases, users who have insufficient privileges will see NULL. (source).

Create a new database

create database databaseName;

The engine, charset and collation are table parameters, so no need to worry here (?)

MySQL administration : user

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

This can be achieved by granting only the select privilege :
GRANT SELECT ON dbName.* TO 'bob'@'10.0.%.%';

Create user accounts

  • remote root account : grant all on *.* to 'root'@'host' identified by 'password';
  • single database user (notice the backticks) : grant all on dbName.* to `bob`@`host` identified by 'password';
To use wildcards in host names, the host name can be declared as 192.168.0.%, or 192.168.%.% (details).

Grants

grant process on *.* to 'bob'@'host';

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

This is a two-step action :
  1. Create the user account without password :
    grant all on dbName.* to 'bob'@'host';
  2. Set the password with the old encryption method :
    set password for 'bob'@'host'=old_password('password');

Delete a user account

  1. As we can not delete user accounts that still have valid rights, we must 1st revoke these rights :
    1. list existing rights : show grants for 'bob'@'host';
    2. revoke all, grant option from 'bob'@'host';
    3. revoke all on *.* from 'bob'@'host';
    • If this outputs : ERROR 1141 (42000): There is no such grant defined for user 'bob' on host 'host', make sure the specified host name matches with the hostname defined for the user and in the grants.
    • Don't forget to flush privileges; after every grant or revoke
  2. delete the account : drop user 'bob'@'host';

revoke all, grant option from 'bob'@'host'; revoke all on *.* from 'bob'@'host'; flush privileges; drop user 'bob'@'host';

mysqlUserName='Bob'; mysqlUserHost='192.168.0.42'; mysql -u root -ppassword -e "revoke all, grant option from '$mysqlUserName'@'$mysqlUserHost'; revoke all on *.* from '$mysqlUserName'@'$mysqlUserHost'; flush privileges; drop user '$mysqlUserName'@'$mysqlUserHost';"

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 :

select user(), current_user();
+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+

How to rename a MySQL database ?

On the MySQL host, run :

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

/var/lib/mysql/ibdata1 is getting huge

  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

MySQL system variables

It is possible to alter MySQL system variables from the MySQL shell. System variables remain changed until the restart of the server.
Get a system variable :
select @@global.wait_timeout;
Set a system variable :
set @@global.wait_timeout=600;

How to set up a MySQL master-slave replication ?

Update :

Information below may be outdated / obsolete. Use at your own risk !

Setting up replication

This procedure was applied to 4.1.20 MySQL servers. Please refer to MySQL documentation for commands on up-to-date versions.
  1. We have 2 servers : master and slave
  2. on the master, enable binary logging. You can do this by editing /etc/my.cnf, browse to the [mysqld] section and add :
    log-bin=/data/mysql/binlog_masterServer
    server-id=1
    This way, the binary log is enabled at each MySQL restart, independently of other options.
    • If you want to have a look at the contents of the log, just type (from the system shell, not the MySQL shell) :
      mysqlbinlog /data/mysql/binlog_masterServer.000001
    • Flushing ALL binary log files can be done (in the MySQL shell) with :
      RESET MASTER
      New empty log files are then re-created (more about the binary log)
  3. Declare which databases are to be replicated :
    binlog-do-db=test_db
    binlog-do-db and binlog-ignore-db respectively white-list or black-list the database to replicate. None is better than the other, it's just a matter of building the shortest list.
  4. Still in /etc/my.cnf, define the server-id into the [mysqld] section and restart mysqld to load these settings. (details about MySQL option files)
  5. On the master, create an account with the REPLICATION SLAVE privilege so that the slave can connect to the master (details).
    grant replication slave on *.* to 'slave'@'10.40.103.10' identified by 'passwordForSlaveUserOnMaster';
  6. On the master, flush all the tables and block write statements, from the MySQL shell :
    FLUSH TABLES WITH READ LOCK;
  7. Now, you can backup the master database and copy it to the slave server, because the master-slave replication only performs incremental updates based on a common starting point.
  8. Re-enable the write activity on the master :
    UNLOCK TABLES;
  9. on slave, edit the configuration file (here : E:\MySQL\MySQL Server 4.1\my.ini) and in the [mysqld] section, define the slave-id and master settings :
    server-id=2
    master-host=172.20.20.45
    master-user=slave
    master-password=passwordForSlaveUserOnMaster
    Details :
    (to be confirmed) If no reference to which logfile to use and no logfile position is found, a "default" logfile is assumed, starting at the beginning.
  10. start the slave server (that was supposed to be stopped while receiving the database dump)
  11. start the slave threads (in the MySQL shell) :
    START SLAVE;
  12. ...
  13. For both :
    SHOW PROCESSLIST;
    You should see 1 master thread and 2 slave threads.

Resynchronizing servers

  1. stop slave; to stop receiving the master binlog
  2. reset slave; to delete the binlog that is stored on the slave
  3. reset master; to delete the master binlog and forget about unnecessary queries as we're making a new snapshot
  4. on the master : lock the db to be re-synchronized
  5. on the master, archive the db : tar -cjf my_archive.tar.bz2 file_1 file_2 ... file_n (-j asks tar to use the bzip2 compression algorithm)
  6. on the master, unlock the db
  7. copy the archive to the slave
  8. on the slave : lock the db to be re-synchronized
  9. on the slave, un-archive the db : tar -xjf my_archive.tbz (.tar.bz2 or .tbz has no real importance)
  10. on the slave, unlock the db
  11. start slave; to get the db changes that occurred during the operation, and listen to new updates

Debug MySQL master-slave replication

Is master writing into its binary log ?

On master :
In the MySQL shell :
show master status;
In the system shell :
echo 'show master status\G' | mysql -u root

If Position is > 0, this means the binary log is active.

Is slave running ?

On slave :
In the MySQL shell :
  • show slave status;
  • show slave status\G
In the system shell :
echo 'show slave status\G' | mysql -u root | grep -E 'Slave.*Running'

You should see both Slave_IO_Running and Slave_SQL_Running displaying YES.

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

On slave :
In the MySQL shell :
show slave status\G
In the system shell :
echo 'show slave status\G' | mysql -u root | grep Last_Error

and have a look at the Last_Error returned field. Looks like when an error is found by a slave in any binlog (i.e. a missing table), the processing of the binlog stops.

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

In the MySQL shell of slave, type :
  1. STOP SLAVE;
  2. RESET SLAVE;
  3. START SLAVE;
  4. then show slave status\G to make sure Slave_IO_Running says YES.

What about some RESET MASTER, RESET SLAVE ?

For some reason (?), sometimes data updates don't go through to the slave. To fix this, follow these steps :
  1. RESET MASTER;
  2. STOP SLAVE;
  3. RESET SLAVE;
  4. START SLAVE;

How to flush master's binary log ?

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

This error occurred while performing master-slave replication between a MySQL 4.1.20 master and a MySQL 5.0.45 slave, where a table field was defined as datetime and trying to be inserted a NULL value. Apparently, this is no big trouble for MySQL 4.x as this error never came up before, while replicating the same table.
To fix it :

  1. Edit the slave configuration file : /etc/my.cnf on Linux or x:\install_path\MySQL5\my.ini on Windows
  2. Search the [mysqld] section, and the sql-mode directive within that section
  3. Remove the option STRICT_TRANS_TABLES
  4. Add the option ALLOW_INVALID_DATES
  5. save, exit and restart the slave server

How to disable foreign key check

Deleting some records from InnoDB tables sometimes conflicts with keys constraints. To workaround this :
  1. SET FOREIGN_KEY_CHECKS = 0;
  2. do the "delete" job
  3. SET FOREIGN_KEY_CHECKS = 1;

MySQL dump / restore database

Database dumps

All these commands have to be executed on the db server, in the system shell (not the MySQL shell). Specifying the password in the command line is optional and just saves typing it repeatedly.
  • dump a full database : mysqldump -u root -ppassword dbName > dbName.sql
  • dump one or more tables : mysqldump -u root -ppassword dbName table_1 table_2 table_n > dump.sql
  • restore a full database : mysql -u root -ppassword dbName < dbName.sql
  • restore a single table : mysql -u root -ppassword dbName < table_name.sql

See also :

Same as above + compression

dump + compress a full database (source)
  • with gzip : mysqldump -u root -ppassword dbName | gzip > dbName.sql.gz
  • with bzip2 : mysqldump -u root -ppassword dbName | bzip2 > dbName.sql.bz2
Restore a compressed dump
gunzip < dbName.sql.gz | mysql -u root -ppassword dbName

Options for database dumps

mysql -u bob -ppassword --opt --databases dbName > dbName.sql

Option Usage
-B --databases specify that following arguments are database names, not table names. Additionally, this adds a USE dbName; statement to the dump.
Using this directive with mysqldump will prevent importing the dump into a database having a different name.
-C --compress Compress all information sent between the client and the server if both support compression
-d --no-data Dump only the create table statements and not the table contents.
-l --lock-tables lock all tables of a database before exporting it. Thus, the only locked tables are those of the database currently being exported
--add-drop-table add a "DROP" statement before any "CREATE TABLE"
--add-locks to let "INSERT" queries run faster, lock the table before inserting data, and unlock it once done
--extended-insert use the multi-line "INSERT" method (?)
--opt similar to --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. This is enabled by default.
--quick push the result directly to the output device without buffering
--single-transaction The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.
To dump large tables, combine the --single-transaction option with the --quick.

Replicate a local database

  1. mysql -u root -ppassword -e 'create database newDbName;'
  2. mysqldump -u root -ppassword sourceDbName > ./sourceDbName.sql
  3. mysql -u root -ppassword newDbName < ./sourceDbName.sql
  4. rm ./sourceDbName.sql

Replicate a database from a distant server to the local host

  • This doesn't work to cross-replicate databases on a single MySQL server
  • This must be run on the server that will receive data
  • Mind the --databases option if remoteDbName != localDbName

The idea is to make a "basic" database dump with mysqldump on a remote host, but instead of storing it into a file, we pipe it into a "mysql import" command :

mysqldump -h remoteDbServer -u remoteDbUser -premoteDbPassword remoteDbName | mysql -u localDbUser -plocalDbPassword -C localDbName

  • remoteDbUser requires the LOCK TABLES privilege to perform the dump.
  • localDbUser requires the INSERT privilege to import the dump.

How to delete everything from a MySQL table ?

truncate

truncate tableName

  • Deletes all existing records of the table tableName
  • Can't be used with where clause
  • Actually performs a drop table + create table
  • Is not transaction-safe : no rollback is possible
  • Doesn't return the number of rows deleted when queried
  • Is inspired by Oracle's truncate

delete

delete from tableName where [SQL condition]

  • Delete records of table tableName matching a condition : can be used with where clause
  • Creates a log for rollback
  • Deletes table contents row by row when executed
  • Is slow
  • Returns the number of rows deleted when queried

MySQL administration : table

Create a new table

create table my_table (my_id int(10) auto_increment primary key, something varchar(50));

Lock a table (source) :

A table can be locked either in READ or WRITE mode :
  • READ : the table becomes read-only for everybody, including the thread performing the lock.
  • WRITE : if a thread obtains a WRITE lock on a table, only the thread holding the lock can read an write to the table. Other threads are blocked from reading or writing the table until the lock has been released.
Example :
  • lock tables my_table read;
  • unlock tables;

How to automatically insert the current timestamp in a MySQL table while inserting/editing a record ?

To insert the current timestamp while inserting a new record :

Alter the table so that the column that holds the timestamp has CURRENT_TIMESTAMP as default value.

To update the current timestamp while updating an existing record :

Alter the table so that the column that holds the timestamp has ON UPDATE CURRENT_TIMESTAMP as default value.

MySQL error log filled up with : /usr/libexec/mysqld: Can't open file: 'bayes_token.MYI' (errno: 145)

Situation :

The same error message is repeated several times per second into the error log (/var/log/mysqld.log), which is growing huge.

Details :

To know more about a MySQL error code, just type (in the system shell) :
perror 145
Which returns :
MySQL error code 145: Table was marked as crashed and should be repaired

Solution :

The table bayes_token must be repaired. To do so, in the MySQL shell or within a client, run :
repair table maillog.bayes_token;