Difference between revisions of "MySQL"

Jump to navigation Jump to search

MySQL (view source)

Revision as of 10:53, 25 March 2014

17,228 bytes added ,  10:53, 25 March 2014
Removed categories
m (Added "Basic Commands")
(Removed categories)
 
(71 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{Depreciated|category=MySQL}}
See the official [http://dev.mysql.com/doc/ MySQL documentation] for further info.
== Basic Commands ==
== Basic Commands ==


{|cellpadding="4" cellspacing="0" border="1"
{|class="vwikitable"
|- style="background-color:#bbddff;"
|-  
! Command                                          !! Description
! Command                                          !! Description
|-
|-
Line 14: Line 18:
|-
|-
| <code> CREATE DATABASE <db_name>; </code>        || Create a database
| <code> CREATE DATABASE <db_name>; </code>        || Create a database
|-
| <code> DROP DATABASE <db_name>; </code>          || 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...
<source lang="mysql"> FLUSH PRIVILEGES; </source>
To give full privileges (including the ability to alter user accounts) to a new user coming from any location use;
<source lang="mysql"> GRANT ALL PRIVILEGES on <database>.* to '<user>'@'%' identified by '<password>' WITH GRANT OPTION; </source>
To give no privileges to a new user coming from a specific host;
<source lang="mysql"> GRANT USAGE on <database>.* to '<user>'@'<host>' identified by '<password>'; </source>


== User Accounts ==
To give an existing user read-only access;
=== Create Users ===
<source lang="mysql"> GRANT SELECT on <database>.* to '<user>'@'<host>';</source>
To give full privileges to a user coming from any location use;
 
* <code> GRANT ALL PRIVILEGES on <database>.* to '<user>'@'%' identified by '<password>' with grant option; </code>
To give an existing user ''web-user'', read-only access to local database ''webdb'';
<source lang="mysql"> GRANT SELECT ON webdb.* TO 'web-user'@'localhost';</source>
 
To remove/revoke a privilege from an existing user ''web-user'';
<source lang="mysql"> REVOKE SELECT ON webdb.* FROM 'web-user'@'localhost';</source>
 
=== Delete Users ===
To remove an existing user use the following...
<source lang="mysql"> DROP USER '<user>'@'<host>'; </source>
* You need to specify the full <code>user@host</code> entry (or MySQL assumes the wild-card host <code>%</code>)
* Existing users sessions are not dropped, but will be unable to re-establish


=== Display Users ===
=== Display Users ===
To display all configured users;
To display all configured users;
* <code> <nowiki> SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user </nowiki></code>
<source lang="mysql">
SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user;
</source>
Then use the displayed lines to see the detail of each user
Then use the displayed lines to see the detail of each user
=== Change User Password ===
<source lang="mysql">
SET PASSWORD FOR 'user'@'%' = PASSWORD('newpass');
</source>


== Data Types ==
== Data Types ==
=== Numbers ===
<code> BOOL </code> and <code> BOOLEAN </code> are synonyms for <code> TINYINT(1) </code>.
{|class="vwikitable"
! Type              !! Bytes !! Min    !! Max
|-
| <code> TINYINT  </code>          || 1    || -128                  || 127
|-
| <code> TINYINT UNSIGNED </code>  || 1    || 0                    || 255
|-
| <code> SMALLINT </code>          || 2    || -32768                || 32767
|-
| <code> SMALLINT UNSIGNED </code>  || 2    || 0                    || 65535
|-
| <code> MEDIUMINT </code>          || 3    || -8388608              || 8388607
|-
| <code> MEDIUMINT UNSIGNED </code> || 3    || 0                    || 16777215
|-
| <code> INT  </code>              || 4    || -2147483648          || 2147483647
|-
| <code> INT UNSIGNED </code>      || 4    || 0                    || 4294967295
|-
| <code> BIGINT </code>            || 8    || -9223372036854775808  || 9223372036854775807
|-
| <code> BIGINT UNSIGNED </code>    || 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
* '''<code>VARCHAR</code>''' - Variable character length up a prescribed maximum (eg <code>VARCHAR(32)</code>). Max allowed is 65,535 (though in reality this max width is shared across all columns so is less in practice.
* '''<code>CHAR</code>''' - Fixed character length up a prescribed maximum (eg <code>CHAR(32)</code>). Max allowed is 255
=== Dates and Times ===
Date and time values need to specified in the general format of <code>YYYY-MM-DD HH:MM:SS</code> (date or time components should be omitted if required for the table column.  MySQL is relaxed on the usage of delimiters, so <code>YYYY^MM^DD HH-MM-SS</code>, or <code>YYYYMMDDHHMMSS</code> should be fine so long as the overall order of year, month, day, etc is correct and the values are valid.
{|class="vwikitable"
! Type                      !! Min                !! Max                  !! Comments
|-
| <code> DATE </code>      || 1000-01-01          || 9999-12-31
|-
| <code> TIME </code>      || -838:59:59          || 838:59:59
|-
| <code> TIMESTAMP </code>  || 1970-01-01 00:00:01 || 2038-01-19 03:14:07  || Stored as UTC (converted to during INSERT and from during SELECT)
|-
| <code> DATETIME </code>  || 1000-01-01 00:00:00 || 9999-12-31 23:59:59
|-
| <code> YEAR </code>      || 1901                || 2155                ||
|}
When querying data for use in PHP scripts, DATETIME values need to be converted into Unix Timestamp, for example...
<source lang="mysql">SELECT UNIX_TIMESTAMP(datetime) AS datetime FROM table;</source>
=== 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...
<source lang="mysql">INSERT INTO table (col1, col2) VALUES ('data1', NULL); </source>
=== IP Addresses ===
=== IP Addresses ===
IP addresses are most efficiently stored as an UNSIGNED INT, though obviously this isn't particularly human readable.
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 <code> SELECT ... ORDER BY ip </code> 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;
MySQL will do the conversion between INT and dotted quad using the INET_ATON and INET_NTOA functions. For example;
* <code> SELECT INET_NTOA(ip) from ips; </code>
<source lang="mysql">
* <code> INSERT INTO ips SET ip=INET_ATON('10.1.2.3'); </code>
INSERT INTO ips SET ip=INET_ATON('10.1.2.3');
Alternatively, use VARCHAR(15) to store as text.
SELECT INET_NTOA(ip) FROM ips;
SELECT INET_NTOA(ip) FROM ips WHERE INET_NTOA(ip) LIKE '10.1.2.%';
</source>
Alternatively, use <code>VARCHAR(15)</code> to store as text.
 
The data returned by <code> INET_NTOA() </code> 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 <code> [byte] </code> object that is nigh on impossible to convert to a standard string. To force MySQL to return a string wrap the command in <code>CONVERT(x, CHAR)</code>, eg
<source lang="mysql">
SELECT CONVERT(INET_NTOA(ip), CHAR) AS ip FROM ips;
</source>
 
== CREATE / ALTER Tables ==
=== CREATE ===
<source lang="mysql">
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
</source>
 
=== RENAME ===
<source lang="mysql">
RENAME TABLE hware to hardware;
</source>
 
=== ALTER ===
<source lang="mysql">
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
</source>
 
=== DELETE ===
<source lang="mysql">
DELETE FROM hware;                                         # Delete the contents if the hware table
</source>
 
=== 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 <code>AUTO_INCREMENT</code> 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 ===
<source lang="mysql">
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';
</source>
 
=== INSERT ... SELECT ===
Used when you want you want include data from another table in an <code>INSERT</code> statement (eg you want to reference a unique ID in another table)
<source lang="mysql"># 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);</source>
 
=== UPDATE ... SELECT ===
Here the <code>table</code> table is being updated from the <code>hware</code> table.
<source lang="mysql">UPDATE table SET make=(SELECT make FROM hware WHERE id=5) WHERE tid=2;</source> 
 
Alternatively, the example below the <code>plat</code> table is updated from the <code>os</code> table.
<source lang="mysql">UPDATE plat,os SET plat.osid=os.osid WHERE os.os_supplied='Microsoft(R) Windows(R) Server 2003, Standard Edition';</source>
 
== SELECT Rows ==
=== AS ===
'''<code>AS</code>''' renames the column heading for a query result.
<source lang="mysql"> SELECT INET_NTOA(ip) AS ip, name FROM hosts; </source>
 
=== ORDER BY ===
<source lang="mysql">
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)
</source>
 
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 <code>ISNULL(column)</code> for column your sorting by which has NULL values...
<source lang="mysql"> SELECT INET_NTOA(ip) AS ip, name FROM hosts ORDER BY ISNULL(ip), ip; </source>
 
=== JOIN ===
<source lang="mysql">
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
</source>
 
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 ===
<source lang="mysql">
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'
</source>
 
=== DISTINCT ===
Only selects completely distinct rows (ie on all columns), not to be confused with <code>GROUP BY</code> (see below).
<source lang="mysql">
SELECT DISTINCT * FROM hware JOIN notes ON (id); 
</source>
 
=== GROUP BY ===
Select rows, grouped by a particular column (so effectively only shows one row for duplicates on that column)
<source lang="mysql">
SELECT * FROM hware JOIN notes ON (id) GROUP BY model; 
</source>
 
== 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...
<source lang="mysql">
CREATE EVENT do_update_cluster_stats ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE, '23:30:00') DO CALL update_cluster_stats();
</source>
 
You may need to enable the event scheduler
* <code> SET GLOBAL event_scheduler = 1; </code>
* The event_scheduler should now be visible in the process list (<code>show processlist</code>)
 
== Maintenance ==
=== SHOW etc ===
Shows general information about a database, it's tables, columns etc.
<source lang="mysql">
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
</source>
 
==== Database Sizes ====
'''Databases'''
<source lang="mysql">
SELECT table_schema "Database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "Size (MB)" FROM information_schema.tables GROUP BY table_schema;  
</source>
 
'''Top Tables'''
Be sure to update the database name in the <code>FROM</code> line
<source lang="mysql">
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;
</source>
 
Adapted from http://www.mysqlperformanceblog.com/2008/02/04/finding-out-largest-tables-on-mysql-server/
 
=== Export / Backup ===
<source lang="mysql">
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;
</source>
 
=== Corruption ===
<source lang="mysql">
CHECK TABLE table;                                    # Checks for table corruption
REPAIR TABLE table;                                    # Repairs a corrupted table
</source>
 
=== Shrink MyISAM Table ===
<source lang="mysql">
OPTIMIZE TABLE SystemEvents;
</source>
 
=== 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...
<source lang="mysql">
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
</source>
 
=== Restart Replication ===
If replication is completely broken, or the slave no longer has an accurate copy of the master.
 
# Create dump from master
#* <code> mysqldump -u root -p --master-data --all-databases --flush-privileges | gzip -1 > /var/tmp/replication.sql.gz </code>
# Copy to slave
#* EG <code> scp /var/tmp/replication.sql.gz root@10.1.1.55:/var/tmp/. </code>
# Stop slave if still running
#* In MySQL - <code>STOP SLAVE;</code>
# Import into slave
#* <code> zcat /var/tmp/replication.sql.gz | mysql -u root -p</code>
# Start slave
#* In MySQL - <code>START SLAVE;</code>
 
=== Remove Old Binary Logs ===
Make sure that any Slave's are up to date before deleting old binary logs
<source lang="mysql">
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
</source>
 
== 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 <code> mysql </code> and <code> mysqldump </code> 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 (<code>cd</code>) to where MySQL is installed (something like <code>C:\Program Files\MySQL</code>).
 
# Export the database from the source machine (from either Unix shell / Windows command prompt as your sourec machine dictates), eg for database called <code> sourcedb </code>
#* <code> mysqldump -u root -p sourcedb > sourcedb.sql </code>
# Copy the file to your destination database server
# Create an empty database on the destination database server
#* <code> CREATE DATABASE destdb; </code>
# Import the database dump
#* <code> mysql -u root -p destdb < sourcedb.sql </code>
# Give you client server access to the new database
#* <code> GRANT ALL PRIVILEGES on destdb.* to 'destdbuser'@'clientsvr' IDENTIFIED BY 'password'; </code>


[[Category:MySQL]]
=== 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 <code>-d</code> option when creating the dump, so
* <code> mysqldump -u root -p -d sourcedb > sourcedb-schema.sql </code>
Retrieved from ‘https://vwiki.co.uk/index.php?title=Special:MobileDiff/606...2448’

Navigation menu