Some frequently used MySQL commands for reference...

Submitted by sandip on Mon, 08/30/2004 - 22:33

MySQL

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

Bookmark/Search this post with

sandip's blog
Login or register to post comments

loading utf8 csv data file

Submitted by sandip on Wed, 03/18/2015 - 21:50.

Check and make sure that character_set% variable are all set to utf8 first:

mysql> SHOW VARIABLES LIKE 'character_set%';

Set all to utf8 except filesystem and dir.

mysql> set character_set_database='utf8';
mysql> set character_set_server='utf8';

Then import the data and verify:

mysql> LOAD DATA INFILE '/path/to/file.csv' INTO TABLE utf8_table FIELDS TERMINATED BY ';' ESCAPED BY '\\' IGNORE 1 LINES;
mysql> select * from utf8_table limit 10;

turn off binary logging for current session

Submitted by sandip on Thu, 05/30/2013 - 09:04.

Must have SUPER privileges for this to work.

mysql> SET SQL_LOG_BIN = 0;

and all queries on current session will not be sent to the binary log.

To turn binary logging back on:

mysql> SET SQL_LOG_BIN = 1;

Skip Duplicate Replication Errors

Submitted by sandip on Thu, 05/30/2013 - 08:56.

mysql> SET @@GLOBAL.SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

shutdown mysql server

Submitted by sandip on Thu, 01/26/2012 - 12:29.

If sysv init script fail to shutdown mysql server, then try with the mysqladmin command:

mysqladmin shutdown

mysqldump list of tables

Submitted by Anonymous on Mon, 11/14/2011 - 23:37.

This gets a list of tables and pipes to xargs to form a mysqldump command with the table names appended.

mysql --batch --skip-column-names {database_name} -e 'show tables like "wp_%"' | \
xargs mysqldump --opt {database_name} > /tmp/dump.sql

Purging mysql binary logs

Submitted by sandip on Thu, 10/20/2011 - 15:59.

To safely purge binary log files:

1. On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.
2. Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS.
3. Determine the earliest log file among all the slaves. This is the target file.
4. Purge all log files up to but not including the target file.

PURGE BINARY LOGS TO 'mysql-bin.010';

Reference: http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html

Show tables using InnoDB engine

Submitted by sandip on Fri, 08/05/2011 - 15:41.

Show schema and tables using InnoDB engine:

mysql> use information_schema;
mysql> select table_schema, table_name from tables where engine = 'InnoDB';

Total size of all storage engines

Submitted by sandip on Tue, 04/19/2016 - 11:45.

SELECT engine,
        count(*) tables,
        concat(round(sum(table_rows)/1000000,2),'M') rows,
        concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
        concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
        concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
        round(sum(index_length)/sum(data_length),2) idxfrac
        FROM information_schema.TABLES
        GROUP BY engine
        ORDER BY sum(data_length+index_length) DESC LIMIT 10;

Ref: https://www.percona.com/blog/2008/03/17/researching-your-mysql-table-sizes/

show supported / availabe engines

Submitted by Anonymous on Mon, 02/13/2012 - 22:29.

mysql> show engines;

mysql cache query result

Submitted by sandip on Wed, 03/23/2011 - 16:42.

Cache query result:

SELECT SQL_CACHE * FROM table;

Do not cache query result:

SELECT SQL_NO_CACHE * FROM table;

mysqldump with where clause

Submitted by sandip on Mon, 02/07/2011 - 12:23.

Below is example of mysqldump using where clause of a "time_stamp" column:

mysqldump --opt -where="time_stamp > '2010-08-01'" <database> <table> | gzip >database.table.sql.gz

table information with myisamchk

Submitted by sandip on Sun, 01/30/2011 - 02:38.

To get additional information about table.

myisamchk -dvv table.MYI

update password to use new hash

Submitted by sandip on Wed, 10/13/2010 - 16:11.

If using php-5.3.x with mysqlnd support, you would need to update all passwords to use the new longer 41-byte hash.

mysql> set session old_passwords=0;
mysql> select password('oldpassword'); -- to confirm
mysql> set password for 'username'@'localhost' = PASSWORD('oldpassword');

mysqlshow

Submitted by sandip on Tue, 10/12/2010 - 09:47.

Show databases and number of tables:
mysqlshow -v
Show tables and number of columns for a database.
mysqlshow -v <dbname>
Show status of database:
mysqlshow --status -v <dbname>

dump mysql procedures and functions

Submitted by sandip on Thu, 11/19/2009 - 17:58.

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.sql

Indexes

Submitted by wizap on Fri, 03/28/2008 - 15:46.

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

Submitted by wizap on Sat, 02/16/2008 - 19:24.

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

Submitted by sandip on Sun, 07/08/2007 - 02:27.

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

Submitted by sandip on Sat, 07/07/2007 - 20:15.

mysql> GRANT ALL PRIVILEGES ON *.* TO '<user>'@'localhost' IDENTIFIED BY PASSWORD '<password>' WITH GRANT OPTION;

Revoke all privileges for a user

Submitted by sandip on Thu, 06/28/2007 - 16:57.

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost';

Show grants for a particular user

Submitted by sandip on Thu, 06/28/2007 - 16:48.

mysql> show grants for 'user'@'localhost';

Drop anonymous mysql users

Submitted by sandip on Thu, 06/28/2007 - 16:42.

mysql> DROP USER ''@'localhost';

Additional MySQL Tuning Tips...

Submitted by sandip on Fri, 01/28/2005 - 17:34.

Read it at: Jeremys' Blog

Optimize MySQL Tables

Submitted by nepalexpo on Wed, 01/19/2005 - 16:07.

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.

OPTIMIZE TABLE tbl_name[,tbl_name]...

Remember to do this periodically and after every upgrade.

mysqlcheck -o -u root -p --all-databases

OPTIMIZE TABLE for MyISAM tables is equivalent of running:

$ myisamchk --quick --check-only-changed --sort-index --analyze *.MYI

an important distiction

Submitted by Anonymous on Thu, 05/14/2009 - 09:13.

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

Submitted by sandip on Sun, 05/17/2009 - 01:25.

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>.MYI

Start up mysql and you may still have to run `mysqlcheck -r` once again if an upgrade is requested.

check all database tables

Submitted by Anonymous on Sun, 11/07/2010 - 23:46.

To check all database tables first on a running database:

mysqlcheck -c --all-databases

MySQL Database Repair

Submitted by sandip on Wed, 01/12/2005 - 14:19.

  CHECK TABLE <tableName>

  REPAIR TABLE <tableName>
  myisamchk -e *.MYI
  myisamchk -r -q <tableName> (-r -q means `quick recovery mode')

For a detailed explaination of checking and repairing Tables refer to the MySQL Manual.