# Create User
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'];
# Create Database
$ mysqladmin -u <username> -p create <nameOfDatabase>
# Drop/Delete Database
$ mysqladmin -u <username> -p drop <nameOfDatabase>
# Check Process List
$ mysqladmin -u root -p proc
# Check Status at 5 seconds interval
$ mysqladmin -u root -p -i 5 status
# Dump Database
$ mysqldump --opt -u <username> -h <hostname> <nameOfDatabase> -p > /path/to/file
$ mysqldump --opt -u <username> -h <hostname> --all-databases -p > /path/to/file
# Import Database
$ mysql -h <host> -u <username> <nameOfDatabase> -p < /path/to/file
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON <dbname>.* TO <dbuser@localhost> [IDENTIFIED BY '<password>'];
REVOKE ALL ON <dbname> FROM <dbuser@localhost>;
CREATE DATABASE <dbname>;
DROP DATABASE <dbname>;
DROP TABLE <tablename1[, table2, table3...]>;
# To activate new permissions
FLUSH PRIVILEGES;
USE <nameOfDatabase>;
SHOW DATABASES;
# show tables begining with the prefix
SHOW TABLES LIKE 'prefix%';
SELECT * FROM <nameOfTable>;
DESCRIBE <nameOfTable>;
INSERT INTO <table> <username, password, name1, name2, ...> VALUES ('user', password('pass'), 'value1', 'value2' ...);
CREATE TABLE <newtable> AS SELECT DISTINCT <field> FROM <oldtable>;
INSERT INTO <database.table> SELECT * FROM <database.table> WHERE <field> = <value>;
ALTER TABLE <tableOldName> RENAME <tableNewName>;
UPDATE <tableName> SET <field1> = <newValue> [WHERE <field2> = <currentValue>];
Some frequently used MySQL commands for reference...
Submitted by sandip on Mon, 08/30/2004 - 22:33.












dump mysql procedures and functions
Show procedures:
mysql> show procedure status;Show functions:
mysql> show function status;Dump procedures and functions for the respective dbs':
$ mysqldump --opt --routines -uroot -p {DB} > DB.sqlIndexes
Check for indexes:
mysql> SHOW INDEX FROM <tableName> [FROM <nameOfDatabase>]Adding Indexes:
mysql> ALTER TABLE `<tableName>` ADD INDEX (`<field>`);Dropping Indexes:
mysql> ALTER TABLE `<tableName>` DROP INDEX `<field>`;grant and revoke privileges
Additional grants:
mysql> grant lock tables on `<db_name>`.* to '<username>'@'localhost';Removing grants:
mysql> revoke lock tables on `<db_name>`.* from '<username>'@'localhost';Changing MySQL server variables at runtime
Set global variable:
mysql> SET @@global.<variable>=<value>;Set session variable:
mysql> SET @@session.<variable>=<value>;Listing:
mysql> SELECT @@[global|session].<variable>Reference:
Dynamic System Variables
Add additional mysql root user
mysql> GRANT ALL PRIVILEGES ON *.* TO '<user>'@'localhost' IDENTIFIED BY PASSWORD '<password>' WITH GRANT OPTION;Revoke all privileges for a user
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost';Show grants for a particular user
mysql> show grants for 'user'@'localhost';Drop anonymous mysql users
mysql> DROP USER ''@'localhost';Additional MySQL Tuning Tips...
Read it at: Jeremys' Blog
Optimize MySQL Tables
OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.
Remember to do this periodically and after every upgrade.
OPTIMIZE TABLE for MyISAM tables is equivalent of running:
an important distiction
mysqlcheck is meant to be used when mysqld is running, and myisamchk is supposed to be used when mysqld is down only.
mysqlcheck and myisamchk
You're right.
If mysqlcheck does not work for you when repairing tables:
mysqlcheck -r -e <database> <table>Then try stop the mysql server and run:
myisamchk -r <table>.MYIStart up mysql and you may still have to run `mysqlcheck -r` once again if an upgrade is requested.
MySQL Database Repair
For a detailed explaination of checking and repairing Tables refer to the MySQL Manual.
Post new comment