====== MySQL ======

=====Common MYSQL Commands=====
Update information in a Table:
<code>
 update table set email='email@test.com' where ref_no='x';
</code>

Alter the Structure of a Table:
<code>
 alter table abstract_submit modify surname varchar(250);
 alter table paper_submit add (abstract text);
 alter table paper_submit drop primary key; #dont worry, this will only drop the primary key and leave the data and column intact.
</code>

Some databases may not provide the user with "All Privileges". This line adds the "Alter" permission to the user.:
<code>
 GRANT ALTER ON db.* TO db@localhost IDENTIFIED BY 'db_pass';
</code>

=====MYSQL USERS ADD/ REMOVE=====
<code>
Add/ Create a New User:
 GRANT ALL PRIVILEGES
 ON faim.*
 TO faim@'%'
 IDENTIFIED BY 'pass';
</code>

Delete a User:
<code>
 DELETE FROM USER WHERE USER='name';
 drop user user@localhost;
</code>

Remove a Users Privileges:
<code>
 REVOKE ALL PRIVILEGES
 ON *.* 
 FROM davisc@'%';
</code>

=====MYSQLDUMP=====
Backup MySQL Database:
<code>
 mysqldump -h localhost -u user -p db_name > db_backup.sql
</code>

If you get the following error, you may have to "skip lock tables":
<code>
 mysqldump: Got error: 1044: Access denied for user 'dbuser'@'localhost' to database 'db_name' when using LOCK TABLES
</code>
<code>
 mysqldump -h localhost -u db_user -p --lock-tables=false db > db_backup.sql
 #Another method:
 mysqldump -h localhost -u db_user -p --skip-lock-tables db > db_backup.sql
</code>

===== Importing a MySQL Dump =====
<code>
mysql -u username -p database_name < file.sql
</code>

===== Setting and Inputting Fixed Variables in MySQL Statements =====
<code>
 mysql> set @a=1;
 Query OK, 0 rows affected (0.05 sec)
 
 mysql> insert into t values(@a);
</code>

===== Show colums as Rows =====
The following switch \G will display information in Groups of Rows instead of Columns. Example:
<code>
 mysql> use mysql
 mysql> show table status;
 #Its quite hard to read and has many many columns. Now try:
 mysql> show table status\G
 #no semi-colon needed. All information is shown in Rows.
</code>
====== Block all MySQL Users Temporarily ======
<code>
 SHOW VARIABLES LIKE 'max_connections';
 SET GLOBAL max_connections = 0;
</code>

The above locks all people out from the MySQL server.

**"Although setting the variable to a value of 0 suggests that no connections are allowed, one connection is actually reserved for the root user."**

To see if there are any current connections or processes running:
<code>
 SHOW PROCESSLIST;
</code>

====== Determine Logged in users and their Connections to the DB ======
<code>
 mysqladmin processlist
 #The above assumes that the mysql root password in stored in .my.cnf
</code>

===== Script to log MySQL users and persistent connections =====
<code>
 #!/bin/bash
 mysqladmin processlist | gzip > /var/tmp/mysqlconnlog$(date +%d%m%y_%H%M%S).gz

 #Create a 5min cron job:
 */5 * * * * /root/logmysqlconn.sh
</code>

====== Repair a Crashed MySQL Table ======
In case you stumble across an error like: "Table 'table_name' is marked as crashed and should be repaired" and/or specifically with gallery2 you recieve "ERROR_STORAGE_FAILURE", you'll need to do the following:
<code>
 mysql -u username -p databasename
 check table g2_SessionMap;
 repair table g2_SessionMap;
</code>
If you enable debug in gallery2 ( $gallery->setDebug(true); ), you'll see the above table needing a repair. 

====== Set limits for MySQL usage by Users ======
To limit a user/database from overloading a MySQL server, the following can be done.
<code>
 mysql   #log into mysql as root
 show grants for user@'193.1.99.64/255.255.255.192';
 grant usage on dbname.* to user@'193.1.99.64/255.255.255.192' with max_user_connections 5;
 show grants for user@'193.1.99.64/255.255.255.192';
 grant usage on dbname.* to user@'193.1.99.64/255.255.255.192' with max_user_connections 0;

 grant usage on wikidb.* to wikiuser@'localhost' with max_user_connections 5;
 show grants for wikiuser@'localhost';
 
 grant usage on kartblog.* to kartblog@'localhost' with max_user_connections 5;
 show grants for kartblog@'localhost';
 #Not sure what max_user_connections is ideal, but will try it at 5.
</code>

References:\\
http://dev.mysql.com/doc/refman/5.0/en/user-resources.html\\
http://dev.mysql.com/doc/refman/5.0/en/show-grants.html\\

====== Export a MySQL select query ======
So I was using mysql> via the command line, and I wanted to export a large select query to a text file. Here is how:
<code>
 echo "select distinct pl_title from pagelinks;" | mysql wikidb > mediawiki-complete-page-listing-urls.sql
</code>
Reference: http://www.commandlinefu.com/commands/view/817/export-mysql-query-as-.csv-file

----
More Information here:

http://dev.mysql.com/doc/refman/5.0/en/mysql.html

http://e-mats.org/2010/04/error-1577-hy000-at-line-1-cannot-proceed-because-system-tables-used-by-event-scheduler-were-found-damaged-at-server-start/  -> Upgrade issue when tailing daemon.logs after upgrading from mysql-5 to mysql-5.1