MySQL maintenance snippets

mysql_maintenance.sh

##########
# EXPORT #
##########

# export db (schema only)
mysqldump -u username -ppassword --no-data --add-drop-database --databases db > db.sql

# export db
mysqldump -u username -ppassword --add-drop-database --databases db > db.sql

# export table
mysqldump -u username -ppassword db table > table.sql

# export table (bulk file)
mysqldump -u username -ppassword db table --fields-terminated-by=, --skip-add-drop-table --skip-add-locks --skip-quote-names --no-create-info -T PATH_TO_EXPORT_FOLDER

##########
# IMPORT #
##########

# import db or table
mysql -u username -ppassword db < file.sql

# disable keys
alter table `table` disable keys;

# import table from bulk file
mysqlimport -u username -ppassword db PATH_TO_FOLDER\table.txt --fields-terminated-by=, -f

# fast index rebuild
mysqlcheck -u username -ppassword -q db table

# enable keys
alter table `table` enable keys;

###############
# PERMISSIONS #
###############

# grant remote access to any host
grant all privileges on db.* to ‘username’@'%' identified by 'password';

# grant remote access to a specific host
grant all privileges on db.* to ‘username’@'IP_ADDRESS' identified by 'password';