Difference between revisions of "MySQL"

Jump to navigation Jump to search

MySQL (view source)

Revision as of 10:53, 25 March 2014

4,621 bytes added ,  10:53, 25 March 2014
Removed categories
(→‎Create Users: Renamed to "Create Users and Grant/Revoke Privileges" and added Revoke example)
(Removed categories)
 
(16 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.
See the official [http://dev.mysql.com/doc/ MySQL documentation] for further info.


Line 22: Line 24:
== User Accounts ==
== User Accounts ==
=== Create Users and Grant/Revoke Privileges ===
=== 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;
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>
<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;
To give no privileges to a new user coming from a specific host;
Line 90: Line 95:
* '''<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>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
* '''<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 ===
Line 102: Line 127:
<source lang="mysql">
<source lang="mysql">
INSERT INTO ips SET ip=INET_ATON('10.1.2.3');
INSERT INTO ips SET ip=INET_ATON('10.1.2.3');
SELECT INET_NTOA(ip) from ips;
SELECT INET_NTOA(ip) FROM ips;
SELECT INET_NTOA(ip) from ips WHERE INET_NTOA(ip) LIKE '10.1.2.%';
SELECT INET_NTOA(ip) FROM ips WHERE INET_NTOA(ip) LIKE '10.1.2.%';
</source>
</source>
Alternatively, use <code>VARCHAR(15)</code> to store as text.
Alternatively, use <code>VARCHAR(15)</code> to store as text.
Line 109: Line 134:
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  
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">
<source lang="mysql">
SELECT CONVERT(INET_NTOA(ip), CHAR) AS ip from ips;
SELECT CONVERT(INET_NTOA(ip), CHAR) AS ip FROM ips;
</source>
</source>


Line 151: Line 176:


== INSERT / UPDATE Rows ==
== INSERT / UPDATE Rows ==
'''Basic Examples'''
=== Basic Examples ===
<source lang="mysql">
<source lang="mysql">
INSERT INTO hosts (name, ping_ok) VALUES ('ServerA', 1) ON DUPLICATE KEY UPDATE ping_ok=1;
INSERT INTO hosts (name, ping_ok) VALUES ('ServerA', 1) ON DUPLICATE KEY UPDATE ping_ok=1;
Line 158: Line 183:
</source>
</source>


<br>'''INSERT ... SELECT'''<br>
=== 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)
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">INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171');</source>
<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>


<br>'''UPDATE ... SELECT'''<br>
=== UPDATE ... SELECT ===
Here the <code>table</code> table is being updated from the <code>hware</code> table.  
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>   
<source lang="mysql">UPDATE table SET make=(SELECT make FROM hware WHERE id=5) WHERE tid=2;</source>   
Line 170: Line 199:


== SELECT Rows ==
== SELECT Rows ==
'''AS'''
=== 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>
<source lang="mysql"> SELECT INET_NTOA(ip) AS ip, name FROM hosts; </source>


'''ORDER BY'''
=== ORDER BY ===
<source lang="mysql"> SELECT INET_NTOA(ip) AS ip, name FROM hosts ORDER BY ip; </source>
<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...
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...
Line 186: Line 219:
</source>
</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, see - http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html
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 ===
=== COUNT ===
Line 231: Line 266:
SHOW INDEX FROM table;                                # Show tables index/key information
SHOW INDEX FROM table;                                # Show tables index/key information
SHOW CREATE TABLE table;                              # Shows syntax that would be used to recreate a table
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>
</source>


Line 237: Line 307:
CHECK TABLE table;                                    # Checks for table corruption
CHECK TABLE table;                                    # Checks for table corruption
REPAIR TABLE table;                                    # Repairs a corrupted table
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>
</source>


Line 257: Line 362:
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
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>
* <code> mysqldump -u root -p -d sourcedb > sourcedb-schema.sql </code>
[[Category:MySQL]]
Retrieved from ‘https://vwiki.co.uk/index.php?title=Special:MobileDiff/2003...2448’

Navigation menu