Tuesday, September 27, 2011

MySQL Maintenance

My main focus is Microsoft SQL on Windows.  I was playing with MySQL and want to share MySQL equivalents to common functions in MS SQL.

Back up all MySQL databases:
$ mysqldump --user=root --password=whatever --result-file=backup_file.sql --all-databases

Look into the "lock-tables" option.
Also look into mysqlhotcopy
Also look into the log-bin option to get something like transaction logs.

For data integrity checks, look into mysqlcheck and myisamchk (is there something similar for Innodb?)

For optimization:
Index defrag - mysqlcheck --optimize -A -u username -ppassword

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.