Working with mysql CLI

Print Friendly

Login

mysql -u username -p

Show databases;

show databases;

Use database;

use db_name;

List tables;

show tables;

Show table fields

describe table_name;

OR

show columns from table_name;

Select fields from table

select column1,column2 from table_name;

Update record

update table_name set column='test1' where column='test2';

Delete record

delete from table_name where column='test';

Backup database (not logged in to mysql)

mysqldump -u username -ppassword db_name > db_backup_file.sql

Backup certain tables from database

mysqldump -u username -ppassword db_name table_name1 table_name2 > db_backup_file.sql

Backup more than one database

mysqldump -u username -ppassword --databases db_name1 db_name2 > db_backup_file.sql

Backup all databases

mysqldump -u username -ppassword --all-databases > db_backup_file.sql

Restore database (if database does not exist)

mysql -u username -p password db_name < db_backup_file.sql

Restore database (existing database)

mysqlimport -u username -p password db_name < db_backup_file.sql

Show Database size

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" 
FROM information_schema.TABLES GROUP BY table_schema ;
This entry was posted in Web. Bookmark the permalink.

Leave a Reply

Your email address will not be published.