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

To give full privileges to a user coming from any location use;

 GRANT ALL PRIVILEGES on <database>.* to '<user>'@'%' identified by '<password>' with grant option;

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

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

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

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. MySQL will do the conversion between INT and dotted quad using the INET_ATON and INET_NTOA functions. For example;

SELECT INET_NTOA(ip) from ips;
INSERT INTO ips SET ip=INET_ATON('10.1.2.3');

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

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);                                         # 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

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)

INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171');

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

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

ORDER BY

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

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, see - 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 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;

SHOW

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

SHOW STATUS;                                           # Shows general info
SHOW CREATE TABLE table;                               # Shows syntax that would be used to recreate a table

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 with the mysql software.

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