MySQL

This page is now depreciated, and is no longer being updated.
The page was becoming too large - all content from this page, and newer updates, can be found via the Category page link below. This page and its contents will not be deleted.
See MySQL

See the official MySQL documentation for further info.

Basic Commands

Command	Description
SHOW DATABASES;	Show databases on server
USE <db_name>;	Use / go into a database
SHOW TABLES;	Show tables in current database
DESCRIBE <tbl_name>;	Show the format of the table
CREATE DATABASE <db_name>;	Create a database
DROP DATABASE <db_name>;	Drop (delete) a database

User Accounts

Create Users and Grant/Revoke Privileges

After making any changes to user privilages, you need to flush them to ensure they're applied...

 FLUSH PRIVILEGES;

To give full privileges (including the ability to alter user accounts) to a new user coming from any location use;

 GRANT ALL PRIVILEGES on <database>.* to '<user>'@'%' identified by '<password>' WITH GRANT OPTION;

To give no privileges to a new user coming from a specific host;

 GRANT USAGE on <database>.* to '<user>'@'<host>' identified by '<password>';

To give an existing user read-only access;

 GRANT SELECT on <database>.* to '<user>'@'<host>';

To give an existing user web-user, read-only access to local database webdb;

 GRANT SELECT ON webdb.* TO 'web-user'@'localhost';

To remove/revoke a privilege from an existing user web-user;

 REVOKE SELECT ON webdb.* FROM 'web-user'@'localhost';

Delete Users

To remove an existing user use the following...

 DROP USER '<user>'@'<host>';

• You need to specify the full user@host entry (or MySQL assumes the wild-card host %)
• Existing users sessions are not dropped, but will be unable to re-establish

Display Users

To display all configured users;

SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user;

Then use the displayed lines to see the detail of each user

Change User Password

SET PASSWORD FOR 'user'@'%' = PASSWORD('newpass');

Data Types

Numbers

BOOL and BOOLEAN are synonyms for TINYINT(1) .

Type	Bytes	Min	Max
TINYINT	1	-128	127
TINYINT UNSIGNED	1	0	255
SMALLINT	2	-32768	32767
SMALLINT UNSIGNED	2	0	65535
MEDIUMINT	3	-8388608	8388607
MEDIUMINT UNSIGNED	3	0	16777215
INT	4	-2147483648	2147483647
INT UNSIGNED	4	0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
BIGINT UNSIGNED	8	0	18446744073709551615

For more info see - http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Strings

There's two main string types

• VARCHAR - Variable character length up a prescribed maximum (eg VARCHAR(32)). Max allowed is 65,535 (though in reality this max width is shared across all columns so is less in practice.
• CHAR - Fixed character length up a prescribed maximum (eg CHAR(32)). Max allowed is 255

Dates and Times

Date and time values need to specified in the general format of YYYY-MM-DD HH:MM:SS (date or time components should be omitted if required for the table column. MySQL is relaxed on the usage of delimiters, so YYYY^MM^DD HH-MM-SS, or YYYYMMDDHHMMSS should be fine so long as the overall order of year, month, day, etc is correct and the values are valid.

Type	Min	Max	Comments
DATE	1000-01-01	9999-12-31
TIME	-838:59:59	838:59:59
TIMESTAMP	1970-01-01 00:00:01	2038-01-19 03:14:07	Stored as UTC (converted to during INSERT and from during SELECT)
DATETIME	1000-01-01 00:00:00	9999-12-31 23:59:59
YEAR	1901	2155

When querying data for use in PHP scripts, DATETIME values need to be converted into Unix Timestamp, for example...

SELECT UNIX_TIMESTAMP(datetime) AS datetime FROM table;

NULL

NULL means "no data", it doesn't mean zero. Therefore 0 <> NULL in an numerical field, and "NULL" <> NULL in a string field.

To set a field to NULL, use NULL without any quotes eg...

INSERT INTO table (col1, col2) VALUES ('data1', NULL);

IP Addresses

IP addresses are most efficiently stored as an UNSIGNED INT, though obviously this isn't particularly human readable, but it is beneficial in as much as that if you use a SELECT ... ORDER BY ip type of statement the IP's will be correctly sorted MySQL will do the conversion between INT and dotted quad using the INET_ATON and INET_NTOA functions. For example;

INSERT INTO ips SET ip=INET_ATON('10.1.2.3');
SELECT INET_NTOA(ip) FROM ips;
SELECT INET_NTOA(ip) FROM ips WHERE INET_NTOA(ip) LIKE '10.1.2.%';

Alternatively, use VARCHAR(15) to store as text.

The data returned by INET_NTOA() is in binary string format, which can occasionally cause problems. If you're passing the data into PowerShell, for example, you end up by a [byte] object that is nigh on impossible to convert to a standard string. To force MySQL to return a string wrap the command in CONVERT(x, CHAR), eg

SELECT CONVERT(INET_NTOA(ip), CHAR) AS ip FROM ips;

CREATE / ALTER Tables

CREATE

CREATE TABLE hware (hid INT AUTO_INCREMENT, make VARCHAR(64), model VARCHAR(64), cpu_num TINYINT UNSIGNED, mem SMALLINT UNSIGNED, sn VARCHAR(64), PRIMARY KEY (hid));
CREATE TABLE notes (nid INT UNSIGNED AUTO_INCREMENT, note VARCHAR(256), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (nid));
CREATE TABLE hware2notes (hid INT, nid INT, PRIMARY KEY (hid, nid));                        # Primary key prevent duplicate rows

RENAME

RENAME TABLE hware to hardware;

ALTER

ALTER TABLE hware ADD UNIQUE KEY hid;                                         # Add additional index/key (using existing column)
ALTER TABLE hware ADD FULLTEXT name (name, description);                      # Add a FULLTEXT key (used for fuzzy searches)
ALTER TABLE hware ADD COLUMN cpu_core TINYINT UNSIGNED AFTER cpu_num;         # Add new column
ALTER TABLE hware MODIFY COLUMN mem INT UNSIGNED;                             # Modify column type
ALTER TABLE hware CHANGE COLUMN cpu_num cpu_sock TINYINT UNSIGNED;            # Change column name
ALTER TABLE hware DROP COLUMN cpu_sock;                                       # Drop (delete/remove) column from table
ALTER TABLE hware DROP KEY hid;                                               # Drop (delete/remove) hid index (not the column)
ALTER TABLE hware DROP PRIMARY KEY;                                           # Drop primary key from table

DELETE

DELETE FROM hware;                                         # Delete the contents if the hware table

Keys / Indexes

There are three different types of indexing available for a table, all of which can be made against one or more columns in your table

• Primary - compulsory, often on an AUTO_INCREMENT id. Must be unique for each row.
• Unique - optional, useful to enforce uniqueness in a table across columns not included in Primary Key
• FullText - optional, used to create a full text index, which you can later do fuzzy searches against.

All keys have a name, if you don't specify one at creation, the name of the first column is used.

INSERT / UPDATE Rows

Basic Examples

INSERT INTO hosts (name, ping_ok) VALUES ('ServerA', 1) ON DUPLICATE KEY UPDATE ping_ok=1;
INSERT IGNORE INTO hware2note (hid, nid);                 # Ignores errors returned if insert would violate primary/unique key duplication
UPDATE hosts SET ping_ok=0, reason='Time Out' WHERE name='ServerA';

INSERT ... SELECT

Used when you want you want include data from another table in an INSERT statement (eg you want to reference a unique ID in another table)

# Basic insert
INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171');
# With 'ON DUPLICATE', VALUES allows the result from the SELECT to re-used
INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171') 
    ON DUPLICATE KEY UPDATE id = VALUES(id);

UPDATE ... SELECT

Here the table table is being updated from the hware table.

UPDATE table SET make=(SELECT make FROM hware WHERE id=5) WHERE tid=2;

Alternatively, the example below the plat table is updated from the os table.

UPDATE plat,os SET plat.osid=os.osid WHERE os.os_supplied='Microsoft(R) Windows(R) Server 2003, Standard Edition';

SELECT Rows

AS

AS renames the column heading for a query result.

 SELECT INET_NTOA(ip) AS ip, name FROM hosts;

ORDER BY

SELECT INET_NTOA(ip) AS ip, name FROM hosts ORDER BY ip;
SELECT INET_NTOA(ip) AS ip, name FROM hosts ORDER BY ip DESC;            # Reverse sorting order (descending)

NULL's can be a bit of pain as they tend to end up at the top, to force then to the bottom insert an additional ISNULL(column) for column your sorting by which has NULL values...

 SELECT INET_NTOA(ip) AS ip, name FROM hosts ORDER BY ISNULL(ip), ip;

JOIN

SELECT make, model FROM hware JOIN hosts ON table.id=hosts.id WHERE hosts.ip=INET_ATON('10.10.255.253');                 # Join with ON
SELECT make, model FROM hware JOIN hosts USING (id) WHERE hosts.ip=INET_ATON('10.10.255.253');                           # Join with USING
SELECT make, model, info FROM hware JOIN hosts USING (id) JOIN notes ON (id) WHERE hosts.ip=INET_ATON('10.10.255.253');  # Multiple join

Different types of join will yield differing results, depending how different rows match up. It can be a bit flummoxing to start with, but its actually fairly simple once you've got the basic idea straight in your head. The LEFT and RIGHT joins allow you control whether or not you want to see NULL's appearing in your results where rows don't always have counterparts in two tables you are JOIN'ing together.

See this site for a very clear and concise walk-through - http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html

COUNT

SELECT COUNT(*) FROM hware WHERE make='IBM';                                                  # Counts number of rows in selection
SELECT make, COUNT(*) AS count FROM hware GROUP BY make;                                      # Counts number of occurrences of 'make'
SELECT COUNT(*) AS total, SUM(IF(model='x336',1,0)) AS x336 FROM hware WHERE make='IBM';      # Additional counts number of rows where model='x336'

DISTINCT

Only selects completely distinct rows (ie on all columns), not to be confused with GROUP BY (see below).

SELECT DISTINCT * FROM hware JOIN notes ON (id);

GROUP BY

Select rows, grouped by a particular column (so effectively only shows one row for duplicates on that column)

SELECT * FROM hware JOIN notes ON (id) GROUP BY model;

Events

Events are scheduled occurrences, either where you're running a simple command, or a stored procedure.

If you need to create a stored procedure, use the MySQL Workbench software (http://dev.mysql.com/downloads/workbench/), its a lot easier than creating via command line.

To create an event...

CREATE EVENT do_update_cluster_stats ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE, '23:30:00') DO CALL update_cluster_stats();

You may need to enable the event scheduler

• SET GLOBAL event_scheduler = 1;
• The event_scheduler should now be visible in the process list (show processlist)

Maintenance

SHOW etc

Shows general information about a database, it's tables, columns etc.

SHOW STATUS;                                           # Shows general info
SHOW DATABASES;                                        # Show databases
SHOW TABLES;                                           # Show tables in the current database
DESCRIBE table;                                        # Show column information for a table
SHOW INDEX FROM table;                                 # Show tables index/key information
SHOW CREATE TABLE table;                               # Shows syntax that would be used to recreate a table

Database Sizes

Databases

SELECT table_schema "Database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "Size (MB)" FROM information_schema.tables GROUP BY table_schema;

Top Tables Be sure to update the database name in the FROM line

SELECT table_name,
       ROUND(table_rows / 1000, 2) AS 'rows (K)',
       ROUND(data_length / ( 1024 * 1024), 2) AS 'data (MB)',
       ROUND(index_length / ( 1024 * 1024), 2) AS 'index (MB)',
       ROUND(( data_length + index_length ) / ( 1024 * 1024), 2) AS 'total (MB)'
FROM   information_schema.TABLES WHERE table_schema = 'databasename'
ORDER  BY data_length + index_length DESC
LIMIT  15;

Adapted from http://www.mysqlperformanceblog.com/2008/02/04/finding-out-largest-tables-on-mysql-server/

Export / Backup

mysqldump -u root -p dbname > dbname.sql              # Basic dump
mysqldump -u root -p dbname | dbname.sql.gz           # Create a compressed dump

# Exclude certain tables
mysqldump -u root -p dbname --ignore-table=dbname.table1 --ignore-table=dbname.table2 > dbname.sql 

# Export a table as CSV
SELECT * INTO OUTFILE '/tmp/table.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' 
LINES TERMINATED BY '\n' FROM table;

Corruption

CHECK TABLE table;                                     # Checks for table corruption
REPAIR TABLE table;                                    # Repairs a corrupted table

Shrink MyISAM Table

OPTIMIZE TABLE SystemEvents;

Skip Replication Errors

Sometimes replication to a Slave will stop due to an error. To skip the error and restart replication, perform the following on the slave...

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

Restart Replication

If replication is completely broken, or the slave no longer has an accurate copy of the master.

1. Create dump from master
   • mysqldump -u root -p --master-data --all-databases --flush-privileges | gzip -1 > /var/tmp/replication.sql.gz
2. Copy to slave
   • EG scp /var/tmp/replication.sql.gz root@10.1.1.55:/var/tmp/.
3. Stop slave if still running
   • In MySQL - STOP SLAVE;
4. Import into slave
   • zcat /var/tmp/replication.sql.gz | mysql -u root -p
5. Start slave
   • In MySQL - START SLAVE;

Remove Old Binary Logs

Make sure that any Slave's are up to date before deleting old binary logs

SHOW MASTER STATUS\g 
SHOW MASTER LOGS;                                      # Get list of logfiles
PURGE MASTER LOGS TO 'mysql-bin.012344';               # Removes all logs up to the one specified

Migrate Databases

There are a number of GUI tools provided to assist in migrating data from one server to another, and these are well worth a look (eg MySQL Migration Toolkit, MySQL Workbench). However I have found that on occasion I can't get these to work (normally due to apparent permissioning issues, despite having full rights), and revert to the old fashioned manual way.

This procedure works for Windows and Unix systems alike, the mysql and mysqldump commands need to be run from the normal Unix shell or Windows command line prompts, not from within the mysql software, though on Windows you'll probably need to change directory (cd) to where MySQL is installed (something like C:\Program Files\MySQL).

1. Export the database from the source machine (from either Unix shell / Windows command prompt as your sourec machine dictates), eg for database called sourcedb
   • mysqldump -u root -p sourcedb > sourcedb.sql
2. Copy the file to your destination database server
3. Create an empty database on the destination database server
   • CREATE DATABASE destdb;
4. Import the database dump
   • mysql -u root -p destdb < sourcedb.sql
5. Give you client server access to the new database
   • GRANT ALL PRIVILEGES on destdb.* to 'destdbuser'@'clientsvr' IDENTIFIED BY 'password';

Create Blank Copy

To create a blank copy of a database (ie with a schema but no data) follow the procedure above but use the -d option when creating the dump, so

• mysqldump -u root -p -d sourcedb > sourcedb-schema.sql