MySQL

MySQL — Show InnoDB Status

0

This morning I had an issue where my transactions weren’t being committed and needed to figure out why. When Googling for a way to see what transactions were conducted, but not active I came across a great ‘Show INNODB Status’ post from MySQL Performance Blog.

If you have the correct admin privileges; log into your MySQL DB server and type the following:

show innodb status

You will be presented a wealth of information, including transaction history (or snapshot if there are many). The aforementioned link will give you a section by section explanation, which proved to be quite helpful in debugging my specific issue.

MySQL Common Queries

0

Came across an awesome site where the author posts queries for a lot of common data retrieval scenarios. Definitely worth a look if hazy on more complex types of queries.

Check it out @ www.artfulsoftware.com

MySQL Add Columns

0

Add a column with no regard for order of entry among other columns:

ALTER TABLE `table_name` ADD COLUMN `column_name` int(11) unsigned not null default 0;

Add a column, but specify that it should be the first column of the table:

ALTER TABLE `table_name` ADD COLUMN `column_name` int(11) unsigned not null default 0 FIRST;

Add a column, but specify it's location other than first:

ALTER TABLE `table_name` ADD COLUMN `column_name` int(11) unsigned not null default 0 AFTER `some_other_column`;

MySQL Tuning Primer Script

0

Suggested from #MySQL on irc.freenode.net, this appears to be a very useful utility for performance tuning your MySQL database. I haven’t used it yet, but plan too on the next project.

MySQL Tuning Primer Script

MySQL – Import data from .sql file

0

The following command will import schema and/or data from a mysqldump file:

mysql -h <host_name> <db_name> -u <username> -p < <file_name>.sql

MySQL – Resetting auto_increment counts

0

There may be times where you need to reset the auto_increment counter of a MySQL table, here’s how:

ALTER TABLE <table_name> AUTO_INCREMENT = 1;

MySQL Database Dump – mysqldump

0

MySQL provides a way to get a snapshot of your schema via mysqldump. Care needs to be taken though, if your tables are large you are better off with a SELECT INTO OUTFILE, or implement replication to a low traffic server because the mysqldump command will lock your tables for as long as it takes to grab the data.

mysqldump -u <user_name> -h <db_host> --opt <database_name> -d --single-transaction > backup.sql

The -d flag tells mysqldump that you don’t want any data, only the schema.

If you are only dumping schema and you wish to remove the auto_increment references from the resulting file, there is a how-to here: http://melikedev.com/2011/06/01/mysql-remove-auto_increment-from-schema-dumps-mysqldump/

Mysql – Granting permissions via Command Line Interface

0

Needed to do this to setup a MySQL server on my local machine to facilitate unit test testing…  I used XAMPP and started the MySQL app. After it starts open your terminal and type the following:

mysql -u root
mysql: GRANT ALL ON *.* TO '<some_user>'@'%' IDENTIFIED BY '<some_password>'
mysql: FLUSH PRIVILEGES;
mysql: EXIT
mysql -u <some_user> -p<some_password>
mysql: exit

You should now be able to connect to your local mysql server from any host using the supplied name and password.

Go to Top