Category Archives: MySQL

MySQL: How to find out table stats like rows, space used and last update time

Posted on by

The following MySQL command provides a lot of good information about tables.

show table status 

This statement also displays information about views.

SHOW TABLE STATUS returns the following important fields along with a lot of other intersting data:

Engine
The storage engine for the table. See Chapter 13, Storage Engines.

Rows
The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

Avg_row_length
The average row length.

Data_length
The length of the data file.

More info here

Back up and Restore MySQL database from the command line

Posted on by

Backing up via the command line:

Type the following at the prompt with the appropriate USERNAME and DATABASE name:

mysqldump -u USERNAME -p DATABASE > dump.sql

You will be prompted for your database password and then the DATABASE will be dumped to a plain-text file called dump.sql.

Restoring via the command line:

First drop and recreate the database as needed:

Drop the database

mysqladmin -u USERNAME -p drop DATABASE

Recreate the database

mysqladmin -u USERNAME -p create DATABASE

Import the backup data

mysql -u USERNAME -p DATABASE < dump.sql