When needed, snapshot backups of MySQL instance are not that useful. So, I tend to backup my DB with mysqldump. The script below dump all of schemas including system tables, users and privileges. It also drops table if exists and add them with its data. This script saved my days so often.<\/p>\n\n\n\n
#!\/bin\/bash\nsudo mysqldump -A -Y --master-data=2 --add-drop-table --lock-all-tables -uroot -p > mybackup.sql<\/strong><\/code><\/pre>\n\n\n\nOne DB\/Schema can be exported\/backed up as below.<\/p>\n\n\n\n
#!\/bin\/bash\nmysqldump --add-drop-table --databases db-name -u root -p > mybackup.sql<\/strong><\/code><\/pre>\n\n\n\nUpgrading MySQL <\/h2>\n\n\n\n
When you try to import old MySQL dump into the new MySQL, you often can get \u2018mysql.innodb_index_stats\u2019 doesn\u2019t exist in engine<\/em> and more. There are two solutions for that.<\/p>\n\n\n\nFirst of all, you can ignore innodb index and table when dumping. as below.<\/p>\n\n\n\n
#!\/bin\/bash\nsudo mysqldump -A -Y --master-data=2 --add-drop-table --lock-all-tables --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats -uroot -p > mybackup.sql<\/strong><\/code><\/pre>\n\n\n\nOr you can dump the whole instance and import as below. Please note that “-f”<\/strong> is the magic word here.<\/p>\n\n\n\n [sudo] mysql -u root -p -f < sample.sql<\/code><\/pre>\n\n\n\nDB backup senario<\/h2>\n\n\n\n
Dump files are created from log files. So, it is possible to perform full backup and incremental backup by flushing log files as planned. <\/p>\n\n\n\n
The script below performs a full backup every Sunday and it flushes logs and delete master logs to make it sure next backup won’t overlaps with this one. Other days, it just flushes logs only when exporting daily incremental backup. <\/p>\n\n\n\n
#!\/bin\/bash\nif [ \"`date +%A`\" == \"Sunday\" ]; then\n\n\tmysqldump -A -Y --flush-logs --delete-master-logs --master-data=2 --add-drop-table --lock-all-tables -uroot -pPassword > mybackup.sql\n\nelse\n\n today=`date +%A`\n logno=`ls -d \/var\/log\/mysql\/mysql-bin.?????? | sed 's\/^.*\\.\/\/' | sort -g | tail -n 1`\n mysqladmin -uroot -pPassword flush-logs\n mysqlbinlog \/var\/log\/mysql\/mysql-bin.$logno > $today.sql\n\nfi<\/code><\/pre>\n\n\n\nSetting up mint MySQL<\/h2>\n\n\n\n
After installation, you can log in MySQL. It is the most crucial task to set up root user correctly. Otherwise, you won’t be able to connect with DB remotely at all. <\/p>\n\n\n\n
First login<\/p>\n\n\n\n
# This will log you in as user root.\nsudo mysql <\/code><\/pre>\n\n\n\nThen, create root user with password<\/p>\n\n\n\n
CREATE USER 'root'@'localhost' IDENTIFIED BY 'YourPassword';\nCREATE USER 'root'@'%' IDENTIFIED BY 'YourPassword'; <\/code><\/pre>\n\n\n\nMySQL documents say ‘root’@’%’ will create ‘root’@’localhost’ as well. However, it is safer to run both of them. You can give different password to make your credentials more secure.<\/p>\n\n\n\n
Then, Grant privileges<\/p>\n\n\n\n
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;\nGRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;\nFLUSH PRIVILEGES; <\/code><\/pre>\n\n\n\n