Table of Contents

MySQL

Common MYSQL Commands

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';

MYSQL USERS ADD/ REMOVE

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@'%';

MYSQLDUMP

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

Importing a MySQL Dump

mysql -u username -p database_name < file.sql

Setting and Inputting Fixed Variables in MySQL Statements

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

Show colums as Rows

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.

Block all MySQL Users Temporarily

 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;

Determine Logged in users and their Connections to the DB

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

Script to log MySQL users and persistent connections

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

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:

 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.

Set limits for MySQL usage by Users

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

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:

 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