Difference between revisions of "MySQL"

Jump to navigation Jump to search

MySQL (view source)

Revision as of 10:53, 25 March 2014

2,993 bytes added ,  10:53, 25 March 2014
Removed categories
m (Added Applications category)
(Removed categories)
 
(12 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 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]]
[[Category:Applications]]
Retrieved from ‘https://vwiki.co.uk/index.php?title=Special:MobileDiff/2015...2448’

Navigation menu