Common MYSQL Commands
Update information in a Table:
update table set email@example.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@'%';
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);
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 runing:
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@'18.104.22.168/255.255.255.192'; grant usage on dbname.* to user@'22.214.171.124/255.255.255.192' with max_user_connections 5; show grants for user@'126.96.36.199/255.255.255.192'; grant usage on dbname.* to user@'188.8.131.52/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.
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
More Information here:
^ Upgrade issue when tailing daemon.logs after upgrading from mysql-5 to mysql-5.1