Update information in a Table:

 update table set email='email@test.com' where ref_no='x';

Alter the Structure of a Table:

 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.

Some databases may not provide the user with “All Privileges”. This line adds the “Alter” permission to the user.:

 GRANT ALTER ON db.* TO db@localhost IDENTIFIED BY 'db_pass';

Add/ Create a New User:
 GRANT ALL PRIVILEGES
 ON faim.*
 TO faim@'%'
 IDENTIFIED BY 'pass';

Delete a User:

 DELETE FROM USER WHERE USER='name';
 drop user user@localhost;

Remove a Users Privileges:

 REVOKE ALL PRIVILEGES
 ON *.* 
 FROM davisc@'%';

Backup MySQL Database:

 mysqldump -h localhost -u user -p db_name > db_backup.sql

If you get the following error, you may have to “skip lock tables”:

 mysqldump: Got error: 1044: Access denied for user 'dbuser'@'localhost' to database 'db_name' when using LOCK TABLES

 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

mysql -u username -p database_name < file.sql

 mysql> set @a=1;
 Query OK, 0 rows affected (0.05 sec)
 
 mysql> insert into t values(@a);

The following switch \G will display information in Groups of Rows instead of Columns. Example:

 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.

 SHOW VARIABLES LIKE 'max_connections';
 SET GLOBAL max_connections = 0;

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:

 SHOW PROCESSLIST;

 mysqladmin processlist
 #The above assumes that the mysql root password in stored in .my.cnf

 #!/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

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:

 mysql -u username -p databasename
 check table g2_SessionMap;
 repair table g2_SessionMap;

If you enable debug in gallery2 ( $gallery→setDebug(true); ), you'll see the above table needing a repair.

To limit a user/database from overloading a MySQL server, the following can be done.

 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.

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

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:

 echo "select distinct pl_title from pagelinks;" | mysql wikidb > mediawiki-complete-page-listing-urls.sql

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