MySQL

From Wiki

Jump to: navigation, search

Contents

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

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:

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

Personal tools