Skip to content

Instantly share code, notes, and snippets.

@amishakov

amishakov/database.sh

Created October 28, 2023 11:32
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save amishakov/a7d37477f876e75a17e523cd4d5733c6 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save amishakov/a7d37477f876e75a17e523cd4d5733c6 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @fevangelou fevangelou created this gist Sep 4, 2019.
    120 changes: 120 additions & 0 deletions database.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,120 @@
    MySQL Operations
    --------------------------------------
    In this document:
    - Export/Import DB
    - Export/Import Table from DB
    - mysqladmin
    - Repair DB
    --------------------------------------


    ########################
    ### Export/Import DB ###
    ########################

    * Note: Use mysql or mysqldump commands with "--verbose" flag to see progress (not recommended)

    // Export
    mysqldump -u'user' -p'pass' db_name > db_name.sql

    // Import
    mysql -u'user' -p'pass' db_name < db_name.sql

    // Import using specific collation
    mysql -u'user' -p'pass' db_name -p --default-character-set=utf8 db_name < db_name.sql

    // Export ALL databases & users
    mysqldump -u'user' -p'pass' --all-databases > all_databases.sql

    // Import ALL databases & users
    mysql -u'user' -p'pass' < all_databases.sql


    --- GZIP (preferred) ---

    // Export (gzip)
    mysqldump -u'user' -p'pass' db_name | gzip -9 > db_name.sql.gz

    // Import (gzip)
    gunzip < db_name.sql.gz | mysql -u'user' -p'pass' db_name

    // Export (gzip) ALL databases & users
    mysqldump -u'user' -p'pass' --all-databases | gzip > all_databases.sql.gz

    // Import (gzip) ALL databases & users
    gunzip < all_databases.sql.gz | mysql -u'user' -p'pass'


    ###################################
    ### Export/Import Table from DB ###
    ###################################

    // Export (gzip)
    mysqldump -u'user' -p'pass' db_name table_name | gzip > table_name.sql.gz

    // Import (gzip)
    gunzip < table_name.sql.gz | mysql -u'user' -p'pass' db_name


    ##################
    ### mysqladmin ###
    ##################

    // Show active processes
    $ mysqladmin processlist

    // Change root password with mysqladmin
    Make sure your password is correct, if you need it you can change it with this:
    $ mysqladmin -u root -p password

    When it asks for a password, enter the OLD password and then it will ask you to enter a new password, then confirm. Finished.

    OR
    $ mysqladmin -u root -p 'OLD' password 'NEW'


    ##########################
    ### Repair/Optimize DB ###
    ##########################

    // METHOD 0 - mysqlcheck (preferred)
    mysqlcheck --all-databases -r #repair
    mysqlcheck --all-databases -a #analyze
    mysqlcheck --all-databases -o #optimize


    // METHOD 1 - mysqlcheck
    Login to shell, and put
    $ mysqlcheck -r your_database
    when complete, then do
    $ mysqlcheck -o your_database

    OR just
    $ mysqlcheck -op your_database

    To optimize all tables that are fragmented, you could run this command:
    $ mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

    Quicker command to repair/optimize all databases:
    $ mysqlcheck -rop -u user_name –all-databases


    // METHOD 2 - MYISAMCHK
    myisamchk /var/lib/mysql/DATABASENAME/*.MYI - to check for corruptions
    myisamchk -r /var/lib/mysql/DATABASENAME/*.MYI to repair db

    myisamchk -r /var/lib/mysql/*/*.MYI to repair ALL dbs

    To force repair crashed tables
    myisamchk -of /var/lib/mysql/DATABASENAME/*.MYI
    myisamchk -rof /var/lib/mysql/DATABASENAME/*.MYI

    myisamchk --force --fast --update-state --key_buffer_size=512M --sort_buffer_size=512M --read_buffer_size=4M --write_buffer_size=4M /var/lib/mysql/DATABASENAME/*.MYI

    myisamchk --force --fast --update-state --key_buffer_size=512M --sort_buffer_size=512M --read_buffer_size=4M --write_buffer_size=4M *.MYI

    myisamchk --force --update-state --key_buffer_size=512M --sort_buffer_size=512M --read_buffer_size=4M --write_buffer_size=4M *.MYI

    myisamchk --force --update-state --key_buffer_size=64M --sort_buffer_size=16M --read_buffer_size=4M --write_buffer_size=4M *.MYI

    myisamchk --force --update-state --key_buffer_size=64M --sort_buffer_size=16M --read_buffer_size=4M --write_buffer_size=4M /var/lib/mysql/*/*.MYI