Skip to content

Instantly share code, notes, and snippets.

@joseluisq

joseluisq/mysql_helpful_commands.md

Last active September 30, 2019 10:01
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save joseluisq/8274fc2fe24e5ba717cbb5547e7e99f8 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save joseluisq/8274fc2fe24e5ba717cbb5547e7e99f8 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. joseluisq revised this gist Jun 5, 2019. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions mysql_helpful_commands.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,8 @@
    # MySQL helpful commands and scripts
    > Some useful commands and scripts for MySQL.
    __Update:__ All these commands and more at https://github.com/joseluisq/awesome-mysql-queries-commands

    ## Import script file from console

    ```sh
  2. joseluisq revised this gist Sep 27, 2017. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion mysql_helpful_commands.md
    View file
    Original file line number Diff line number Diff line change
    @@ -28,7 +28,7 @@ mysqldump -h localhost -u username -p database_name > ./mysql_script.sql
    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)
    or using `--single-transaction` if you got an mysqldump error (because you lack privileges to lock the tables)

    ```sh
    mysqldump -h localhost -u username -p database_name --single-transaction | gzip > ./mysql_script.sql
  3. joseluisq revised this gist Sep 27, 2017. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion mysql_helpful_commands.md
    View file
    Original file line number Diff line number Diff line change
    @@ -28,7 +28,7 @@ mysqldump -h localhost -u username -p database_name > ./mysql_script.sql
    mysqldump -h localhost -u username -p database_name | gzip > ./mysql_script.sql
    ```

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

    ```sh
    mysqldump -h localhost -u username -p database_name --single-transaction | gzip > ./mysql_script.sql
  4. joseluisq revised this gist Sep 27, 2017. 1 changed file with 6 additions and 0 deletions.
    6 changes: 6 additions & 0 deletions mysql_helpful_commands.md
    View file
    Original file line number Diff line number Diff line change
    @@ -28,6 +28,12 @@ mysqldump -h localhost -u username -p database_name > ./mysql_script.sql
    mysqldump -h localhost -u username -p database_name | gzip > ./mysql_script.sql
    ```

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

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

    #### Export tables to SQL file

    ```sh
  5. joseluisq revised this gist Oct 19, 2016. 1 changed file with 5 additions and 5 deletions.
    10 changes: 5 additions & 5 deletions mysql_helpful_commands.md
    View file
    Original file line number Diff line number Diff line change
    @@ -4,7 +4,7 @@
    ## Import script file from console

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

    Or on MySQL shell type:
    @@ -19,23 +19,23 @@ mysql> SOURCE mysql_script.sql
    #### SQL file

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

    #### Export database to SQL + GZIP file

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

    #### Export tables to SQL file

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

    ## Finding duplicated values

    ```sql
    SELECT code, COUNT(code) duplicates FROM client GROUP BY code HAVING duplicates > 1;
    ```
    ```
  6. joseluisq revised this gist Aug 12, 2016. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions mysql_helpful_commands.md
    View file
    Original file line number Diff line number Diff line change
    @@ -34,8 +34,8 @@ $ mysqldump -h localhost -u username -p database_name | gzip > ./mysql_script.sq
    $ mysqldump -h localhost -u username -p database_name table_name1 table_name2 > mydb_tables.sql
    ```

    ## Finding duplicate values
    ## Finding duplicated values

    ```sql
    SELECT code, COUNT(*) duplicates FROM table_name GROUP BY code HAVING duplicates > 1;
    SELECT code, COUNT(code) duplicates FROM client GROUP BY code HAVING duplicates > 1;
    ```
  7. joseluisq created this gist May 23, 2016.
    41 changes: 41 additions & 0 deletions mysql_helpful_commands.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,41 @@
    # MySQL helpful commands and scripts
    > Some useful commands and scripts for MySQL.
    ## Import script file from console

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

    Or on MySQL shell type:

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

    ## Export script file from console

    #### SQL file

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

    #### Export database to SQL + GZIP file

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

    #### Export tables to SQL file

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

    ## Finding duplicate values

    ```sql
    SELECT code, COUNT(*) duplicates FROM table_name GROUP BY code HAVING duplicates > 1;
    ```