Difference between revisions of "MySQL"

Jump to navigation Jump to search

MySQL (view source)

Revision as of 10:53, 25 March 2014

4,194 bytes added ,  10:53, 25 March 2014
Removed categories
(→‎SELECT Rows: Updated ORDER BY and JOIN)
(Removed categories)
 
(15 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 237: 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 243: 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 263: 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/2005...2448’

Navigation menu