====================	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.
SHOW PROCESSLIST; View current connections

====================	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

====================	
=====================================	Live backups - replication
http://www.onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html
======================	master server
GRANT REPLICATION SLAVE, REPLICATION CLIENT
    ON *.*
    TO 'replicant'@'slave_host'
    IDENTIFIED BY 'my_pwd';

----------------------	my.cnf
[mysqld]
server-id = 1
log-bin = /var/log/mysql/bin.log

======================	slave server
server-id = 2

master-host = mastersite.com
master-port = 3306
master-user = replicant
master-password = my_pwd

log-bin = /var/log/mysql/bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log

relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
======================	starting replication
mysqldump 

======================	MySQL tuning
set-variable = max_connections = 60
set-variable = max_user_connections = 60
set-variable = max_allowed_packet=1M (sanity check to stop runaway queries)
set-variable = max_connect_errors=999999
set-variable = table_cache=1200

======================	MySQL + Open Office
install isql, libmyodbc

===== prompt mysql (\u@\h)>
[mysql] prompt=mysql \\d>
mysql -u root -p --no-beep
----- aliases
alias usrcount="mysql -u appadmin -p myapplication -e \"select count(id) from users\"
----- vertical format
SELECT * from users WHERE id=1\G
----- redirect to CSV file
SELECT * FROM users INTO OUTFILE '/home/jason/users.csv' FIELDS TERMINATED BY '\t\' ->LINES TERMINATED BY '\n';

Inserting Batch Data

Suppose you're writing an e-commerce application that will be used to sell a wide variety of products. It makes sense to categorize these products; therefore, you'll be using a table named categories to manage the category names:

mysql test>create table categories (
   ->id integer not null auto_increment,
   ->name varchar(35) not null,
   ->primary key(id));

During breaks from writing the application, you've been adding to a textfile (categories.txt containing a list of categories. This file looks something like this:

Candy
Fruit
Coffee
Tea
Pop
Vegetables

The time has come to add these categories to the categories table. You could do
so manually, but this is time-consuming and error prone. The sane solution is to
use MySQL's LOAD DATA INFILE command:

LOAD DATA INFILE '/home/jason/categories.txt' INTO TABLE categories ->LINES TERMINATED BY '\n'

If your input file has multiple items per row, you can tell MySQL to insert each
into a separate column by delimiting them with a tab and using FIELDS TERMINATED
BY '\t':

mysql>LOAD DATA INFILE '/home/jason/categories.txt' INTO TABLE categories ->FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
===== 
MySQL: Five Dials to Set
By Sean Hull

MySQL can be installed and up and running in a matter of minutes with RPMs or even quicker with tools like xampp. However, there are so many dials to turn, and options to set that even experienced DBAs can feel overwhelmed.

With that in mind, we'd like to focus your attention on ten areas that will put you on solid footing.
1. Connections

Connections are typically coming from a web server, as many MySQL databases back internet websites. Here are some of the parameters related to connections, and how to set them.

max_connections
This should be at minimum the sum of the maximum number of apache connections allowed from all your web servers. Keep in mind that each connection uses session memory (see below).

max_packet_allowed
The maximum packet size is typically the size of your largest dataset you'll need to return in one chunk. If you're using mysqldump remote, it may need to be larger.

aborted_connects
Check this system status counter, and be sure it is not increasing. If it is, your clients are getting errors connecting.

thread_cache_size
Incoming connections create a new thread in mysql each time they are opened. Since opening and closing connections in mysql is very cheap (resource-wise) and fast, persistent connections aren't as typical as they are with other databases such as Oracle. However, having threads pre-created does save some time, and that's what the mysql thread cache is for.

Keep an eye on threads_created and if it is increasing, make your thread cache bigger. It doesn't cost much in terms of memory to have 25, 50 or 100 thread_cache_size.
2. Query Cache

Caching queries in mysql includes both the parsed query plan, as well as the return dataset. If the underlying table data or structure changes, that will invalidate the entry in the query cache.

query_cache_min_res_unit
Blocks in the query cache are allocated in chunks of this size. Calculate the average size of your queries with the formula below, and set this variable according to that. MySQL will then use query cache memory more efficiently, caching more queries and wasting less memory.

query_cache_size
This sets the total size of the query cache.

query_cache_limit
This tells mysql to discard queries larger than this size. The theory goes that large queries run infrequently, such as a batch job doing a large report, so those results shouldn't clog up with query cache.

qcache hit ratio = qcache_hits / (qcache_hits + com_select)
Find these variables with:

SQL> show status like 'qcache%';
SQL> show status like 'com_%';

average query size = (query_cache_size - qcache_free_memory)/qcache_queries_in_cache
query_cache_size you can get with:

SQL> show variables like 'query%';
qcache_* status variables you can get with:
SQL> show status like 'qcache%';

3. temp tables

Memory is extremely fast, so when it comes to sorting, we want to keep the entire result set in memory while we're working on it. We can do this by tuning queries to make that set smaller, *OR* by setting these variables larger.

tmp_table_size
max_heap_table_size
Whenever you create temp tables in MySQL it will use the minimum of these two variables as the cutoff, beyond which it will build the temp table on disk. Keep in mind that there are many many sessions, all fighting for limited resources, so better to tune queries than just go setting this too high. Also, keep in mind tables with BLOB or TEXT will go straight to disk.
4. session memory

Each session in MySQL will use it's own memory for work it needs to do. This is memory that directly contributes to your SQL queries, so you want to make it large enough to satisfy those needs. However, you have to balance that with the number of concurrent sessions in your database at one time. What makes this a little bit of a black art here is that MySQL allocates these buffers as needed. So you can't just add them up and multiply by the number of sessions. That estimate will be much higher than MySQL typically uses. Best thing to do is to startup MySQL, connect all of your sessions, then keep and eye on the VIRT column of a top session. The number for the mysqld row usually stays pretty steady. That's your real-world total memory usage. Subtract all your static MySQL memory areas and you have the total session memory in the real world. Then divide by the number of sessions to get the average.

read_buffer_size
Caches blocks from sequential scans. This buffer is across storage engines, not just MyISAM tables.

sort_buffer_size
Any session that performs a sort allocates this buffer. Again beware setting it to a large value system-wide. Better to leave it at 1M or 2M, and then set it within a session, to a higher value for a specific query.

join_buffer_size
Any session that performs a join allocates this buffer. Set it to 1M or 2M and then modify it on a per-session basis as needed.

read_rnd_buffer_size
Used for sorting, and ORDER BY operations. You're better off setting this to 1M, and setting it as a session variable for queries that can use a larger value.
5. slow query log

The slow query log is a really useful feature of MySQL. As queries tend to be the often overlooked, yet most important part of a performing database, this is an important facility to have enabled.

log_slow_queries
Set this in the my.cnf file to turn it on. MySQL will default to putting the file in your datadir with a name hostname-slow.log but you can also give it a name when you specify this option.

long_query_time
This defaults to 10 seconds. I suggest leaving this alone. You can set the value dynamically to 1 second to turn it on, and if the database gets restarted, the log will be off by default. As of 5.1.21 and for folks who installed a Google patch, this option can be set in microseconds. This is a REALLY great feature, because once you wipeout all the queries 1 second and over, finer tuning becomes possible. This will help you eliminate problem SQL long before it becomes a bigger problem.

log_queries_not_using_indexes
This option is a good one to enable as well. It actually logs any queries that return all rows.

http://www.databasejournal.com/features/mysql/article.php/3824086/MySQL-Five-Dials-to-Set.htm
===== tuning
SHOW PROCESSLIST;
SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS;