====================	MySQL
====================	MySQL Access Control
mysql> GRANT <privileges> ON <what>
    -> TO <user> [IDENTIFIED BY "<password>"]
    -> [WITH GRANT OPTION];
<privileges> is a comma-separated list of the privileges you wish to grant. The privileges you can specify can be sorted into three groups:

Database/Table/Column privileges:
ALTER: Modify existing tables (e.g. add/remove columns) and indexes.
CREATE: Create new databases and tables.
DELETE: Delete table entries.
DROP: Delete tables and/or databases.
INDEX: Create and/or delete indexes.
INSERT: Add new table entries.
SELECT: View/search table entries.
UPDATE: Modify existing table entries.
Global administrative privileges:
FILE: Read and write files on the MySQL server.
PROCESS: View and/or kill server threads belonging to other users.
RELOAD: Reload the access control tables, flush the logs, etc.
SHUTDOWN: Shut down the MySQL server.
Special privileges:
ALL: Allowed to do anything (like root).
USAGE: Only allowed to log in -- nothing else.
====================	Creating a Database
mysql> CREATE DATABASE jokes;
--------------------------------
create a user named dbmanager that can connect from server.host.net with
password managedb and has full access to the database named db only (including
the ability to grant access to that database to other users)

mysql> GRANT ALL ON db.*
    -> TO dbmanager@server.host.net
    -> IDENTIFIED BY "managedb"
    -> WITH GRANT OPTION;

change that user's password to funkychicken
mysql> GRANT USAGE ON *.*
    -> TO dbmanager@server.host.net
    -> IDENTIFIED BY "funkychicken";
mysql> flush privileges;
====================	MySQL Locked Out?
% kill <pid>
mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD("newpassword")
    -> WHERE User="root";
mysqladmin flush-privileges
====================	MySQL 
mysql -h <hostname> -u <username> -p
mysql> SHOW DATABASES;
mysql> DROP DATABASE test;	delet DB test
mysql> DROP DATABASE\c	ignore the command you had begun typing
mysql> USE jokes;		use DB jokes
====================	Creating A Table
mysql> CREATE TABLE <table name> (
    ->  <column 1 name> <col. 1 type> <col. 1 details>,
    ->  <column 2 name> <col. 2 type> <col. 2 details>,
    ->  ...
    -> );

mysql> CREATE TABLE Jokes (
    ->  ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  JokeText TEXT,
    ->  JokeDate DATE NOT NULL
    -> );

mysql> SHOW TABLES;
+-----------------+
| Tables in jokes |
+-----------------+
| Jokes           |
+-----------------+
1 row in set

mysql> DESCRIBE Jokes;
+----------+---------+------+-----+------------+- -
| Field    | Type    | Null | Key | Default    | ...
+----------+---------+------+-----+------------+- -
| ID       | int(11) |      | PRI | 0          | ...
| JokeText | text    | YES  |     | NULL       |
| JokeDate | date    |      |     | 0000-00-00 |
+----------+---------+------+-----+------------+- -
3 rows in set

mysql> DROP TABLE <tableName>;		delete a table
====================	Inserting Data into a Table
mysql> INSERT INTO <table name> SET
    ->  columnName1 = value1,
    ->  columnName2 = value2,
    ->  ...
    -> ;

mysql> INSERT INTO <table name>
    -> (columnName1, columnName2, ...)
    -> VALUES (value1, value2, ...);

mysql> INSERT INTO Jokes SET
    -> JokeText = "Why did the chicken cross the road? To get to the other side!",
    -> JokeDate = "2000-04-01";

mysql> INSERT INTO Jokes
    -> (JokeText, JokeDate) VALUES (
    -> "Why did the chicken cross the road? To get to the other side!",
    -> "2000-04-01"
    -> );

====================	Viewing Stored Data
mysql> SELECT * FROM Jokes;	list everything stored in the Jokes table
mysql> SELECT ID, JokeDate FROM Jokes;	
--------------------	see only the first 20 characters of the JokeText column
mysql> SELECT ID, LEFT(JokeText,20), JokeDate FROM Jokes;
+----+----------------------+------------+
| ID | LEFT(JokeText,20)    | JokeDate   |
+----+----------------------+------------+
|  1 | Why did the chicken  | 2000-04-01 |
+----+----------------------+------------+
1 row in set (0.05 sec)
mysql> SELECT COUNT(*) FROM Jokes;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.06 sec)

mysql> SELECT COUNT(*) FROM Jokes
    -> WHERE JokeDate >= "2000-01-01";

mysql> SELECT JokeText FROM Jokes
    -> WHERE JokeText LIKE "%chicken%";

mysql> SELECT JokeText FROM Jokes WHERE
    -> JokeText LIKE "%knock knock%" AND
    -> JokeDate >= "2000-04-01" AND
    -> JokeDate < "2000-05-01";

====================	Modifying Stored Data
mysql> UPDATE <tableName> SET
    -> <col_name>=<new_value>, ...
        -> WHERE <where clause>;

mysql> UPDATE Jokes SET JokeDate="1990-04-01" WHERE ID=1;
mysql> UPDATE Jokes SET JokeDate="1990-04-01"
    -> WHERE JokeText LIKE "%chicken%";

====================	Troubleshooting
mysql -S /var/lib/mysql/mysql.sock
====================	Deleting Stored Data
mysql> DELETE FROM <tableName> WHERE <where clause>;
mysql> DELETE FROM Jokes WHERE JokeText LIKE "%chicken%";

mysql> DELETE FROM Jokes;	empty the Jokes table in one fell swoop

====================	Database Backups using mysqldump
mysqldump -h myhost -u root -pmypass dbname > dbname_backup.sql
mysqldump --all-databases -u -p | ssh @ 'cat > backupfile'

====================	Restore
mysqladmin -h myhost -u root -pmypass create dbname
mysql -h myhost -u root -pmypass dbname < dbname_backup.sql

====================	Incremental Backups using Update Logs
safe-mysqld --log-update=update
--------------------
my.cnf		config file
[mysqld]
log-update=/var/mysql/backup/update

====================	Encrypt passwords
update users set password=(encrypt('newpass')) where uid=611;

====================	Clean old updates
#! /bin/sh

find //mysql/backup`/ -name "update.[0-9]*" -type f -mtime +6 | xargs rm -f
/usr/local/mysql/bin/mysqladmin -u root -ppassword flush-logs

====================	Checking and Repairing MySQL Data Files
myisamchk /usr/local/mysql/var/dbName/tblName
myisamchk /usr/local/mysql/var/dbName/tblName.MYI
myisamchk --extend-check /path/to/tblName
-------------------	three repair methods
myisamchk --recover --quick /path/to/tblName
myisamchk --recover /path/to/tblName
myisamchk --safe-recover /path/to/tblName

====================	Recover from lost root password
1. Kill the mysqld that may be running (not with -9)
kill `cat /var/lib/mysql/hostname.pid`

2. Restart MySQL in safe mode:
/usr/bin/mysqld_safe --skip-grant-tables&

3. Connect to MySQL:

/usr/bin/mysql

4. Use the mysql database:
use mysql;

5. Run the update command putting your new password where the ....'s are. Yes
   this is all one command:

update user set password = password('.......') where user = 'root' and host='localhost';

6. Flush the privileges so everything will take:

flush privileges;

7. Then quit mysql:
\q;

8. Stop mysql from command line with:
/etc/init.d/mysql stop
/etc/init.d/mysql start

====================