Backup DB - Export and Import MySql Database in Linux

How to Export, Import Mysql Database using MySql Dump  and zip it using gzip in linux.

 Export:

 mysqldump --routines -u {user} -p {database} | gzip > {database}.sql.gz

 Import:

 gzip -dc < {database}.sql.gz | mysql -u {user} -p {database}

I hope this command will help! 

MySQL count the number of tables in database

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'your-database-name';

MYSQL : How to change root password

SET PASSWORD FOR root@localhost = PASSWORD('yourpassword');

You can go directly with your phpmyadmin and execute the following query; considering you have no mysql password.

MySQL INSERT SELECT

INSERT INTO table1 (field names) SELECT field name FROM table2;

MySQL - Multiple Update on Select

UPDATE table_1 AS t1, table_2 AS t2
SET
t1.column_name1 = t2.column_name1
t1.column_name2 = t2.column_name2
WHERE t1.column_name = t2.column_name

This mysql statement helps me update my records.