Frequent MySQL/MariaDB operations

database.sh

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