mysql sql command line cli dump backup restore tips tricks etc

mysql.md

#MySQL

run mysql cli:

mysql -u [username] -p [databasename]

will prompt for password.

show databases;
use [databasename];
show tables;
show columns from [tablename];

and all the other mysql commands. select, insert, update, joins etc.

#Commands, Tips and Tricks

1. Given unix time, display in format: YYYY-MM-DD HH:MM:SS

   mysql> SELECT FROM_UNIXTIME(123456789);
   
   +--------------------------+
   | FROM_UNIXTIME(123456789) |
   +--------------------------+
   | 1973-11-29 13:33:09      |
   +--------------------------+
   1 row in set (0.00 sec)
   
   mysql> 
   

2. Display results vertically, instead of horizontal, use \G instead of semicolin.

   mysql> SELECT id, username, email FROM users LIMIT 2\G
   *************************** 1. row ***************************
         id: 1
   username: Todd_test
      email: [email protected]
   *************************** 2. row ***************************
         id: 3
   username: Tim_test
      email: [email protected]
   2 rows in set (0.00 sec)
   
   mysql>
   

3. Execute SQL statement file from BASH in one line. Useful if you have to repeat the same statement(s) many times.

   $ mysql -u username -ppassword -h 127.0.0.1 database_name < text_file.sql
   

   The text_file.sql contains any SQL statments that are to be executed on the remote MySQL database server. Depending on the statements, results may not be displayed. For example, a DELETE command will not return anything. Neither will a SELECT statment with no results. If a SELECT statment has results, those will be shown.

4. Backup and Restore DB

   Backup:

   $ mysqldump -u username -ppassword database_name > dumpfilename.sql
   $ gzip -v dumpfilename.sql
   

   If large tables and using InnoDB tables, use the --single-transaction and --quick options for mysqldump. http://stackoverflow.com/questions/5666784/how-can-i-slow-down-a-mysql-dump-as-to-not-affect-current-load-on-the-server

   To dump large tables, you should combine the --single-transaction option with --quick. --quick, -q This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out. http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction

   Restore:

   $ gunzip -v dumpfilename.sql.gz
   $ mysql -u username -ppassword database_name < dumpfilename.sql
   

5. Extras:

   INSERT INTO table (id,Col1,Col2) 
   VALUES 
       (1,1,1),
       (2,2,3),
       (3,9,3),
       (4,10,12)
   ON DUPLICATE KEY UPDATE 
       Col1=VALUES(Col1),
       Col2=VALUES(Col2);

6. dump table schema mysqldump --skip-comments --skip-extended-insert --no-data -uroot -p database > local.sql