MySQL Operation

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