Some frequently used MySQL commands for reference...

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

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

loading utf8 csv data file

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

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

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

shutdown mysql server

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

mysqladmin shutdown

mysqldump list of tables

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

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

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

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

mysql> show engines;

mysql cache query result

Cache query result:

SELECT SQL_CACHE * FROM table;

Do not cache query result:

SELECT SQL_NO_CACHE * FROM table;

mysqldump with where clause

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

To get additional information about table.

myisamchk -dvv table.MYI

update password to use new hash

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

  • 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

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

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.

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

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

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

check all database tables

To check all database tables first on a running database:

mysqlcheck -c --all-databases

MySQL Database Repair

  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.

Comment