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
