Difference between revisions of "MySQL"

Jump to navigation Jump to search

MySQL (view source)

Revision as of 10:53, 25 March 2014

3,017 bytes added ,  10:53, 25 March 2014
Removed categories
(→‎Data Types: Added "Dates and Times")
(Removed categories)
 
(14 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 92: Line 97:


=== Dates and Times ===
=== 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 incorrect and the values are valid.
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"
{|class="vwikitable"
Line 171: 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 178: 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 257: 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 263: 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 283: 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/2006...2448’

Navigation menu