MySQL

To start MySQL:
/usr/local/bin/mysqld_safe &
or
/usr/local/bin/mysqld_safe --user=mysql &

To set the root password:
/usr/local/bin/mysqladmin -u root password 'new-password'
or
/usr/local/bin/mysqladmin -u root -h hostname password 'new-password'

To reset the root password:
How to Reset the Root Password
Note: Windoze instructions are first. Unix instructions are further down the page.

To create a new user:
First, log in as root.
$ mysql -u root -p
mysql> grant all privileges on db_name.tbl_name to someuser@localhost
    -> identified by 'somepassword';
or
mysql> grant all privileges on db_name.tbl_name to someuser@"%"
    -> identified by 'somepassword';

To delete a user:
Note: You must be root to do this.
From MySQL 4.1.1 up, use DROP USER

Before MySQL 4.1.1:
mysql> select user, host from mysql.user;
+-------+--------------+
| user  | host         |
+-------+--------------+
| root  | mydomain.org |
| joe_s | localhost    |
| root  | localhost    |
| dude  | localhost    |
+-------+--------------+

mysql> revoke all privileges on *.* from dude@localhost;

mysql> revoke grant option on *.* from dude@localhost;

mysql> delete from mysql.user
    -> where user='dude' and host='localhost';

mysql> flush privileges;

mysql> select user, host from mysql.user;
+-------+--------------+
| user  | host         |
+-------+--------------+
| root  | mydomain.org |
| joe_s | localhost    |
| root  | localhost    |
+-------+--------------+

To create a database:
mysql> create database databasename;
To create a table:
mysql> create table sometable (col1 varchar(20), col2 varchar(25),
    -> col3 date);
or
mysql> create table sometable (col1 varchar(20), col2 varchar(25));
Note that date doesn't have a definable length therefore
there is only one closing parenthesis on that line. When the line ends
with a data type with a definable length, there are two closing
parenthesis, one for the column length and one for the end of the table
definition.

To see info on an existing table:
mysql> show columns from sometable;
or
mysql> describe sometable;
or
mysql> show create table sometable\G;

To load data from a file into a table:
mysql> load data infile 'fullpathandfilename' into table sometable;
note: Use the ' characters in this command.
note: Use lines terminated by '\r\n'; when using Windows files on *nix databases.
note: When exporting data from Excel, make sure there is a \N in all cells that don't have data.
note: If you get an ERROR 13: Can't get stat of...
login with 'mysql -u user -p --local-infile=1' and then use
mysql> load data local infile...

To view warnings:
mysql> show warnings;

To load one line of data into a table:
mysql> insert into sometable values
    -> ('val1','val2','1963-10-01');
or
mysql> insert into sometable (name, date_added) values ('joe', now());

To change data:
mysql> update tablename set columnname = 'new data'
    -> where columnname= 'criteria';

To delete a row:
mysql> delete from tablename where columnname = 'criteria';

To delete all rows from a table:
mysql> delete from tablename;

To search with a text file from within mysql:
mysql> source filename;
or
mysql> \. filename;

To log to a file from within mysql:
mysql> tee outputfile
When done:
mysql> notee

To find rows that exits in table A that don't exist in table B:
mysql> select A.item from A left join B on A.item=B.item where B.item is null;

To get the sum of a collumn:
mysql> select sum(column_name) from table_name;

To get the sum of values in a row:
mysql> select id, sum(col_3+col_5) from table_name group by id:

To count the number of rows in a table:
mysql> select count(*) from tablename;

To check the version:
mysql> select version();

To check the current date:
mysql> select curdate();

To check the current date and time:
mysql> select now();

To check for the presence of ssl:
mysql> show variables like 'have_openssl';

To backup and restore a database:
$ mysqldump -u user -p db_name > backup_file.sql
$ mysql -u user -p db_name < backup_file.sql

To reset the auto_increment to 1:
mysql> alter table tablename auto_increment = 1;

To re-create an index:
mysql> alter table tablename drop index indexname;
mysql> alter table tablename add fulltext indexname (col1,col2,...);