I intend to add to this as I have time.

To display the schema of a table:

mysql> desc tablename;

To restore a database from backup:

mysql -u root -p
mysql> create database databasename;
mysql> use databasename;
mysql> source databasebackupfile;
Note: I back my databases up to a file name of databasename.sql.

To update all records in a column of a table:

mysql> update tablename set columnname=’value‘;

To update one record in a column of a table. Note: othercolumnname needs to be unique to the record.:

mysql> update tablename set columnname=’value‘ where othercolumnname=`somevalue‘;

To create an account:

GRANT ALL PRIVILEGES ON databasename.* TO ‘username‘@’hostname‘ IDENTIFIED BY ‘password‘;
GRANT ALL PRIVILEGES ON databasename.* TO ‘username‘@’localhost’ IDENTIFIED BY ‘password‘;
FLUSH PRIVILEGES;

To make mysqld listen only localhost:

vi /etc/my.cnf
…
bind-address=127.0.0.1
…

To count the number of rows in a table:

SELECT COUNT(*) FROM tablename;

To remove formatting and heading from a SELECT statement and delimit using a TAB character:

mysql mydatabase -N -B -u myuser -p -e “myselect statement;”

How to convert timestamp to date from a SELECT statement:

SELECT column1,DATE_FORMAT(FROM_UNIXTIME(`column2`), ‘%b %e %Y’) AS ‘date_formatted’ FROM tablename;

Here is a simpler example, where timestampcol is the name of date column you want to convert:

SELECT FROM_UNIXTIME(timestampcol) FROM tablename;

How to get the time an hour ago:

SELECT TIME(DATE_SUB(NOW(), INTERVAL 1 HOUR));

To access a MySql database from a script without having the password stored in the script:
Add the information to the .my.cny file in the user’s home directory that the script will run under.

Edit or create the file:

# vi ~/.my.cny

Protect the file:

# chmod 600 ~/.my.cny

Sample file contents:

# cat ~/.my.cny
[client]
user=mysqlusername
password=mysqlpassword
database=defaultdatabase

Then, to use it:

# mysql < mysqlscript > my.output

Display table information:

show table status from databasename;

How to backup one table:

# mysqldump -u mysqlusername -h mysqlhostname -p databasename tablename > tablename.sql

To search or output of a query. This would also apply to a SELECT statement.:
SHOW TABLES LIKE ‘%whatever%’;

To limit the number of rows:
SELECT * FROM mytableLIMIT 100;

To sort output based on a particular column:
SELECT * FROM mytable ORDER BY mycolumn DESC;

To get the size of a databse:
SELECT table_schema “Database Name”, SUM( data_length + index_length)/1024/1024
“Database Size (MB)” FROM information_schema.TABLES where table_schema = ‘mydatabase‘;

To get the size of all the tables in a database:
SELECT table_name “Table”, table_rows “Number of Rows”, round(((data_length + index_length)/1024/1024),2)
“Size of Table” FROM information_schema.TABLES WHERE table_schema = “mydatabase“;

To allow an account to export data to a file:
GRANT FILE ON *.* TO ‘username‘@’hostname‘;