MySQL

From Wiki

Contents 1 Common MYSQL Commands 1.1 MYSQLDUMP 1.2 Setting and Inputting Fixed Variables in MySQL Statements 2 Block all MySQL Users Temporarily 3 Determine Logged in users and their Connections to the DB 3.1 Script to log MySQL users and persistant connections 4 Repair a Crashed MySQL Table 5 Set limits for MySQL usage by Users

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

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

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 runing:

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

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

---

More Information here:

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