-
-
Save clevertonh/42e9aa2fc7a59528b2e0e871838c8a5a to your computer and use it in GitHub Desktop.
Handy mysql commands and tips
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/sh | |
| set -e | |
| set -u | |
| # I'm just getting acquainted with mysql so cut me some slack ;) | |
| # check if tables are myisam or innodb (all the ones I inherited seem to be myisam, drat) | |
| SHOW TABLE STATUS FROM `database`; | |
| ## optimize tables | |
| # source: http://stackoverflow.com/questions/5474662/mysql-optimize-all-tables | |
| # source2: http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/ | |
| mysqlcheck -o <db_schema_name> | |
| # -or- (for all databases | |
| mysqlcheck -o --all-databases | |
| mysqlcheck -o <db_name> -u<username> -p | |
| ## dump data and import again | |
| # sourcE: http://stackoverflow.com/questions/5475200/mysql-restoring-a-database-via-mysqldump-does-it-overwrite-the-different-desti?rq=1 | |
| # handy options: add-drop-table and add-drop-database | |
| # export | |
| mysqldump -uuser -ppassword SourceDatabase > file.sql | |
| # import | |
| mysql -u USER -p DATABASE < FILE.SQL | |
| ## mysql big deletes | |
| # source: http://mysql.rjweb.org/doc.php/deletebig | |
| # use paritions or delete in chunks... | |
| # testing the sublime plugin |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment