MySQL and Linux commands

Where are the MySQL binaries stored? Find the location? : which mysql

 

Find the version of MySQL: mysql -V

 

Start the MySQL server: /usr/bin/mysqld_safe --user=mysql & or /etc/init.d/mysql start

 

Validate MySQL process has started: ps -afe | grep mysqld

 

Validate MySQL server is up and running: mysql> SHOW VARIABLES LIKE "%version%";

 

Find the columns of the table in MySQL: mysql> SHOW COLUMNS FROM dept;

 

Find the table related information: SHOW TABLE STATUS LIKE 'dept' \G

 

Display all tables: Mysql> show tables

 

Display all databases: Myql> show databases

 

Display tables from the non current database: Mysql> SHOW TABLES FROM database_name;

 

List the filed of the tables

mySQL> describe tableName

 

Backup/Dump all databases

[mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

 

Backup/Dump particular database

[mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

 

Backup/Dump a table from a database

[mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

 

Restore database (or database table) from backup

[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql