Some useful commands and scripts for MySQL.

mysql_helpful_commands.md

MySQL helpful commands and scripts

Some useful commands and scripts for MySQL.

Import script file from console

mysql -h host -u username -p password --default_character_set utf8 database_name < mysql_script.sql

Or on MySQL shell type:

mysql> USE DATABASE mydatabase_name;
mysql> SOURCE mysql_script.sql

Export script file from console

SQL file

mysqldump -h localhost -u username -p database_name > ./mysql_script.sql

Export database to SQL + GZIP file

mysqldump -h localhost -u username -p database_name | gzip > ./mysql_script.sql

or using --single-transaction if you got an mysqldump error (because you lack privileges to lock the tables)

mysqldump -h localhost -u username -p database_name --single-transaction | gzip > ./mysql_script.sql

Export tables to SQL file

mysqldump -h localhost -u username -p database_name table_name1 table_name2 > mydb_tables.sql

Finding duplicated values

SELECT code, COUNT(code) duplicates FROM client GROUP BY code HAVING duplicates > 1;