Maintenance (MySQL) - Revision history

Sstrutt: /* Migrate Databases */ Added "Ignore Foreign Key Checks"

2014-11-26T00:27:15Z

Migrate Databases: Added "Ignore Foreign Key Checks"

← Older revision		Revision as of 00:27, 26 November 2014
Line 114:		Line 114:
	# Give you client server access to the new database		# Give you client server access to the new database
	#* <code> GRANT ALL PRIVILEGES on destdb.* to 'destdbuser'@'clientsvr' IDENTIFIED BY 'password'; </code>		#* <code> GRANT ALL PRIVILEGES on destdb.* to 'destdbuser'@'clientsvr' IDENTIFIED BY 'password'; </code>

			=== Ignore Foreign Key Checks ===
			If you receive an error similar to the following when trying to reimport a dump, its likely that you are falling fowl of foreign key checks which will fail as the table with the foreign key doesn't appear until later in the dump file
			* <code>ERROR 1005 (HY000) at line 14475: Can't create table 'db.table' (errno: 150)</code>

			Before starting the dump, disable foreign key checks
			* <code> mysql -u root -p -e "SET GLOBAL foreign_key_checks=0"</code>
			Then re-enable once all finished
			* <code> mysql -u root -p -e "SET GLOBAL foreign_key_checks=1"</code>

	== Create Blank Copy ==		== Create Blank Copy ==

Sstrutt: /* SHOW etc */ version

2014-05-09T10:52:23Z

SHOW etc: version

← Older revision		Revision as of 10:52, 9 May 2014
Line 3:		Line 3:
	<source lang="mysql">		<source lang="mysql">
	SHOW STATUS; # Shows general info		SHOW STATUS; # Shows general info
			SHOW VARIABLES LIKE "%version%"; # Show running MySQL version
	SHOW ENGINE INNODB STATUS \G # Show InnoDB database status		SHOW ENGINE INNODB STATUS \G # Show InnoDB database status
	SHOW DATABASES; # Show databases		SHOW DATABASES; # Show databases

Sstrutt: /* SHOW etc */ innodb

2014-05-09T09:50:15Z

SHOW etc: innodb

← Older revision		Revision as of 09:50, 9 May 2014
Line 3:		Line 3:
	<source lang="mysql">		<source lang="mysql">
	SHOW STATUS; # Shows general info		SHOW STATUS; # Shows general info
			SHOW ENGINE INNODB STATUS \G # Show InnoDB database status
	SHOW DATABASES; # Show databases		SHOW DATABASES; # Show databases
	SHOW TABLES; # Show tables in the current database		SHOW TABLES; # Show tables in the current database

Sstrutt: /* Export / Backup */ Added Create a Copy of a Table

2014-03-25T11:17:22Z

Export / Backup: Added Create a Copy of a Table

← Older revision		Revision as of 11:17, 25 March 2014
Line 35:		Line 35:
	mysqldump -u root -p dbname > dbname.sql # Basic dump		mysqldump -u root -p dbname > dbname.sql # Basic dump
	mysqldump -u root -p dbname \| dbname.sql.gz # Create a compressed dump		mysqldump -u root -p dbname \| dbname.sql.gz # Create a compressed dump

			# Backup one table only
			mysqldump -u root -p dbname tablename > db_table.sql

	# Exclude certain tables		# Exclude certain tables
Line 44:		Line 47:
	LINES TERMINATED BY '\n' FROM table;		LINES TERMINATED BY '\n' FROM table;
	</source>		</source>

			=== Create a Copy of a Table ===
			# Export the table
			#* EG <code>mysqldump -u root -p database table > /var/tmp/db_table.sql </code>
			# Edit the dump to provide new table name
			#* EG <code> vi /var/tmp/db_table.sql</code>
			#* EG <code>:%s/table/table_new/g</code>
			# Import the new table in
			#* EG <code>mysql -u root -p < /var/tmp/db_table.sql </code>

	== Corruption ==		== Corruption ==

Sstrutt: Initial creation - content from MySQL page (now category)

2014-03-16T22:07:35Z

Initial creation - content from MySQL page (now category)

New page

== SHOW etc ==
Shows general information about a database, it's tables, columns etc.
<source lang="mysql">
SHOW STATUS; # Shows general info
SHOW DATABASES; # Show databases
SHOW TABLES; # Show tables in the current database
DESCRIBE table; # Show column information for a table
SHOW INDEX FROM table; # Show tables index/key information
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>

== Corruption ==
<source lang="mysql">
CHECK TABLE table; # Checks for table corruption
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>

== Migrate Databases ==
There are a number of GUI tools provided to assist in migrating data from one server to another, and these are well worth a look (eg MySQL Migration Toolkit, MySQL Workbench). However I have found that on occasion I can't get these to work (normally due to apparent permissioning issues, despite having full rights), and revert to the old fashioned manual way.

This procedure works for Windows and Unix systems alike, the <code> mysql </code> and <code> mysqldump </code> commands need to be run from the normal Unix shell or Windows command line prompts, not from within the mysql software, though on Windows you'll probably need to change directory (<code>cd</code>) to where MySQL is installed (something like <code>C:\Program Files\MySQL</code>).

# Export the database from the source machine (from either Unix shell / Windows command prompt as your sourec machine dictates), eg for database called <code> sourcedb </code>
#* <code> mysqldump -u root -p sourcedb > sourcedb.sql </code>
# Copy the file to your destination database server
# Create an empty database on the destination database server
#* <code> CREATE DATABASE destdb; </code>
# Import the database dump
#* <code> mysql -u root -p destdb < sourcedb.sql </code>
# Give you client server access to the new database
#* <code> GRANT ALL PRIVILEGES on destdb.* to 'destdbuser'@'clientsvr' IDENTIFIED BY 'password'; </code>

== Create Blank Copy ==
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>

[[Category:MySQL]]