MySQL backup from command line without a password prompt

Create a file called ~/.my.cnf

Add the following to this file

[mysqldump]
user=myuser
password=mypass

Change permission of this file to 600

Now mysqldump can be run without a password prompt

mysqldump --all-databases > all_databases.sql
Advertisements

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

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

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