Tag Archives: useful mysql commands

Useful MySQL commands

To find MySQL root/admin pass :

cPanel server           : cat /root/.my.cnf ( username : root )
Plesk server             : cat /etc/psa/.psa.shadow ( username : admin )
DirectAdmin server  : cat /usr/local/directadmin/conf/mysql.conf

To login to MySQL :

mysql -u 'username' -p ( will prompt for password )
Password:

To create MySQL dump of a database :

mysqldump -u 'username' -p dbname > database_name.sql ( will prompt for password )

To create MySQL dump of all databases :

mysqldump -u 'username' -p --all-databases > all_databases.sql ( will prompt for password )

To restore all databases from the MySQL dump :

mysql -u username -p < all_databases.sql ( will prompt for password )

To restore a MySQL dump for a database :

mysql -u 'username' -p dbname < database_name.sql ( will prompt for password )

To restore a single database from dump of all databases :

mysql -u 'username' -p --one-database dbname < all_databases.sql ( will prompt for password )

To create MySQL dump of a single table in a database :

mysqldump -u 'username' -p dbname table_name > table_name.sql ( will prompt for password )

To restore the above table from MySQL dump :

mysql -u 'username' -p dbname < /path/to/table_name.sql ( will prompt for password )

One liner to truncate all tables in a db from MySQL :

mysql -Nse 'show tables' DBNAME | while read table;
do mysql -e "truncate table $table" DBNAME; done

One liner to drop all tables in a db from MySQL :

mysql -Nse 'show tables' DBNAME | while read table;
do mysql -e "drop table $table" DBNAME; done