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 ;