|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 the official MySQL documentation for further info.
- 1 Basic Commands
- 2 User Accounts
- 3 Data Types
- 4 CREATE / ALTER Tables
- 5 INSERT / UPDATE Rows
- 6 SELECT Rows
- 7 Events
- 8 Maintenance
- 9 Migrate Databases
||Show databases on server|
||Use / go into a database|
||Show tables in current database|
||Show the format of the table|
||Create a database|
||Drop (delete) a database|
Create Users and Grant/Revoke Privileges
After making any changes to user privilages, you need to flush them to ensure they're applied...
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';
To remove an existing user use the following...
DROP USER '<user>'@'<host>';
- You need to specify the full
user@hostentry (or MySQL assumes the wild-card host
- Existing users sessions are not dropped, but will be unable to re-establish
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');
BOOLEAN are synonyms for
For more info see - http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
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.
||1970-01-01 00:00:01||2038-01-19 03:14:07||Stored as UTC (converted to during INSERT and from during SELECT)|
||1000-01-01 00:00:00||9999-12-31 23:59:59|
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 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 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.%';
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 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 TABLE hware to hardware;
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 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_INCREMENTid. 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
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('188.8.131.52'); # 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('184.108.40.206') ON DUPLICATE KEY UPDATE id = VALUES(id);
UPDATE ... SELECT
table table is being updated from the
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
UPDATE plat,os SET plat.osid=os.osid WHERE os.os_supplied='Microsoft(R) Windows(R) Server 2003, Standard Edition';
AS renames the column heading for a query result.
SELECT INET_NTOA(ip) AS ip, name FROM hosts;
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;
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
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'
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);
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 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 (
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
SELECT table_schema "Database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "Size (MB)" FROM information_schema.tables GROUP BY table_schema;
Be sure to update the database name in the
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;
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;
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;
If replication is completely broken, or the slave no longer has an accurate copy of the master.
- Create dump from master
mysqldump -u root -p --master-data --all-databases --flush-privileges | gzip -1 > /var/tmp/replication.sql.gz
- Copy to slave
scp /var/tmp/replication.sql.gz email@example.com:/var/tmp/.
- Stop slave if still running
- In MySQL -
- In MySQL -
- Import into slave
zcat /var/tmp/replication.sql.gz | mysql -u root -p
- Start slave
- In MySQL -
- In MySQL -
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
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
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
- Export the database from the source machine (from either Unix shell / Windows command prompt as your sourec machine dictates), eg for database called
mysqldump -u root -p sourcedb > sourcedb.sql
- Copy the file to your destination database server
- Create an empty database on the destination database server
CREATE DATABASE destdb;
- Import the database dump
mysql -u root -p destdb < sourcedb.sql
- 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