MySQL: Difference between revisions

From vwiki
Jump to navigation Jump to search
(→‎IP Addresses: Added get-out for binary string gotcha)
(→‎Create Users: Added RO user)
Line 27: Line 27:
To give user privileges to a user coming from a specific host;
To give user privileges to a user coming from a specific host;
<source lang="mysql"> GRANT USAGE on <database>.* to '<user>'@'<host>' identified by '<password>' with grant option; </source>
<source lang="mysql"> GRANT USAGE on <database>.* to '<user>'@'<host>' identified by '<password>' with grant option; </source>
To give a user read-only access;
<source lang="mysql"> GRANT SELECT on <database>.* to '<user>'@'<host>' identified by '<password>';</source>


=== Delete Users ===
=== Delete Users ===

Revision as of 15:27, 3 February 2012

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;

To give a user read-only access; 

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

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, 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
  • Unique - optional, useful to enforce uniqueness in a table and stop duplicate entries.
  • 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) 

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

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

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

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
Retrieved from ‘http://vwiki.co.uk/index.php?title=MySQL&oldid=1698’