Connect to MySQL Server to Issue SQL Statements (mysql)

Connect to MySQL server locally

$ mysql -u username -p [app_db]

$ mysql -u dev -p [app_db]

With password in the command line. Note: No space between -p and the password

$ mysql -u dev -pmypassword [app_db]

Connect to a Remote MySQL Server

$ mysql -h mysql_host_name -u dev -p [app_db]

Batch

$ mysql -u dev -p [app_db] < file.sql > result.out

mysql

Switch DB (aka schema)

mysql> USE app_db

Execute a SQL script

mysql> source filename;

Display MySQL rows in vertical format instead of the default horizontal format

mysql> select * from users\G

Limit the update to 1 row

mysql> UPDATE person SET age=3 LIMIT 1;

Set the upper limit on number of rows that MySQL can update/select/join

mysql> SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=100000;

Quit

mysql> quit

Special MySQL Startup Option

mysqld option	Description	Useful for
skip-networking	It disable any remote MySQL client access	Suspending MySQL client access while perform administrative task
skip-grant-tables	Disable any login check and privileges check *	Reset the DB password or recover the database 'mysql'

* To reload and to re-activate the grant tables

mysql> FLUSH PRIVILEGES;

mysqladmin

MySQL Administrator program - mysqladmin

cmd> mysqladmin -u root -p command1 command2 ...

mysqladmin Command	Description
ping	Ping the server
status	Show instance status
variables	mysqld option values
processlist	Show all processes
password "my_newpassword"	Change password
debug	Write debug info to server log
flush-logs	Flush all logs including binary log
shutdown	Shutdown down MySQL instance
kill id1,id2	Kill server thread (Note no space between Ids)
version	Show version
extended-status	Display option values
flush-privileges	Reload the grant table
flush-tables	Flush all tables
start-slave	Start replication on slave
stop-slave	Stop replication on slave

Lockdown MySQL from any DB Read or Write

Many utility tools modify data independent of the DB instance. If some client sessions are modifying the data concurrently, data may be corrupted.

To avoid such data corruption, either

Shutdown the DB instance and allow exclusive access by the tool, or
Lock the table(s) down to prevent other session(s) to write to the DB

Locking table(s)

Lock other sessions from write but not read

Suitable for utilities that only read data like myisamchk --check.

# All tables MUST be locked in one single LOCK TABLE command line
# Flush changes on tbl1 to the disk
mysql> LOCK TABLE tbl1,tbl2 READ;
mysql> FLUSH TABLE tbl1;

Lock other sessions from read & write.

Tools that modify data like myisamchk --recover must block other sessions from read & write

# Read/Write for this session and none for other sessions
# Flush changes on my_table to the disk
mysql> LOCK TABLE my_table WRITE;
mysql> FLUSH TABLE my_table;

Lock all tables and make the tables read only for all sessions

# Closes all open tables and locks all tables with a read lock
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

NOTE: Do NOT quit the mysql session. Quitting a session will automatically un-lock the table

Tool command	Min. Lock required
myisamchk - check	READ LOCK
myisamchk - recover	WRITE LOCK
myisampack	WRITE LOCK
mysqlhotcopy	Not required
mysqlcheck	Not required
mysqldump	Not required

Un-Locking table(s) when the client application is completed

Only 1 table is locked

mysql> UNLOCK TABLE

More than one table is locked

mysql> UNLOCK TABLES

Unlock tables when SET GLOBAL read_only = ON is used

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

mysqlshow

mysqlshow shows DB and tables information

% mysqlshow -u username -p [my_db] [my_table]

myisampack

myisampack generate compressed and read-Only MyISAM tables

myisampack [options] file_name

perror

Display information on specific error code

% perror error_code1 error_code2

mysqlaccess

mysqlaccess checks access privilege

% mysqlaccess [my_host [my_user [db_name]]] [options]

mysql_convert_table_format

Convert table into another storage engine format

% mysql_convert_table_format [options] db_name

Comments:

MySQL Operation