Database and SQL

Useful command to retrieve db info

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.41 sec)

mysql> show variables;

mysql> SHOW VARIABLES WHERE Variable_name = 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES WHERE Variable_name = 'hostname';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| hostname          | Dell  |
+-------------------+-------+
1 row in set (0.00 sec)

Back up whole mysql database

Pattern:
mysqldump -u [db_username]-p[db_password] [database_name] > [target file path]
Example:
mysqldump -u root -p1q2w3e pcms > /Users/Kamol/pcms_db.sql
mysqldump -u root -pMyP@ssw0rd my_db > C:\Kamol/pcms_db.sql

Restore from backup

Pattern:
mysql -u [db_username]-p[db_password] [database_name] < [backup file path]
Example:
mysql -u root -p1q2w3e pcms < /Users/Kamol/pcms_db.sql
mysql -u root -p1q2w3e pcms < C:\Kamol\pcms_db.sql

OSX (or Unix-like) command to generate backup file

Pattern:
[var name]=`date -j +%Y%m%d_%H%M%S` &&mysqldump -u [db_username]-p[db_password] [database_name] > /Users/Kamol/db_backup/db_$[var name].sql
Example:
datevar=`date -j +%Y%m%d_%H%M%S` && mysqldump -u root -p1q2w3e pcms > /Users/Kamol/db_backup/db_$datevar.sql

The command above will create a backup file

Pattern:
/Users/Kamol/db_backup/db_yyyyMMdd_hhmmss.sql
Example:
/Users/Kamol/db_backup/db_20140618_222432.sql