MySQL - Revision history

Sstrutt: Removed categories

2014-03-25T10:53:41Z

Removed categories

← Older revision		Revision as of 10:53, 25 March 2014
Line 362:		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]]~~

2014-03-16T22:14:26Z

Depreciated page

← Older revision		Revision as of 22:14, 16 March 2014
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.

2014-02-28T14:35:20Z

Maintenance: Added "Restart Replication"

@@ Line 319: / Line 319: @@
 START SLAVE;
 </source>
 === Remove Old Binary Logs ===

2014-02-21T00:21:14Z

Maintenance: Added "Skip Replication Errors"

← Older revision		Revision as of 00:21, 21 February 2014
Line 310:		Line 310:
	<source lang="mysql">		<source lang="mysql">
	OPTIMIZE TABLE SystemEvents;		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>		</source>

2014-02-04T09:07:21Z

Remove Old Binary Logs: Updated

← Older revision		Revision as of 09:07, 4 February 2014
Line 315:		Line 315:
	Make sure that any Slave's are up to date before deleting old binary logs		Make sure that any Slave's are up to date before deleting old binary logs
	<source lang="mysql">		<source lang="mysql">
	SHOW MASTER STATUS\g # Get ~~current logfile name~~		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		PURGE MASTER LOGS TO 'mysql-bin.012344'; # Removes all logs up to the one specified
	</source>		</source>

2014-01-31T14:43:22Z

Maintenance: Added Remove Old Binary Logs

← Older revision		Revision as of 14:43, 31 January 2014
Line 310:		Line 310:
	<source lang="mysql">		<source lang="mysql">
	OPTIMIZE TABLE SystemEvents;		OPTIMIZE TABLE SystemEvents;
			</source>

			=== 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 # Get current logfile name
			PURGE MASTER LOGS TO 'mysql-bin.012344'; # Removes all logs up to the one specified
	</source>		</source>

2014-01-21T15:34:05Z

Export / Backup: Added export as CSV

← Older revision		Revision as of 15:34, 21 January 2014
Line 294:		Line 294:
	# Exclude certain tables		# Exclude certain tables
	mysqldump -u root -p dbname --ignore-table=dbname.table1 --ignore-table=dbname.table2 > dbname.sql		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>

2014-01-15T11:19:53Z

Maintenance: Added "Export / Backup"

← Older revision		Revision as of 11:19, 15 January 2014
Line 286:		Line 286:

	Adapted from http://www.mysqlperformanceblog.com/2008/02/04/finding-out-largest-tables-on-mysql-server/		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
			</source>

	=== Corruption ===		=== Corruption ===

2014-01-10T12:28:18Z

Database Sizes: Added Top Tables

@@ Line 267: / Line 267: @@
 ==== Database Sizes ====
 <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>
 === Corruption ===

2013-10-22T22:13:27Z

SHOW etc: Added "Database Sizes"

← Older revision		Revision as of 22:13, 22 October 2013
Line 264:		Line 264:
	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 ====
			<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>		</source>