Here is a MySQL backup script which can take table by table backups (individual backup files of each table of each database) in a compressed format. It also provides an automatic rotation of old backup files.\u00a0\u00a0The backup script handles innodb and myisam tables\u00a0separately.<\/p>\n
\u00a0You have to set the following variables prior to running the backup script.<\/p>\n
\u00a0DB_USER<\/p>\n
\u00a0The database user who has access to all databases and its tables. I used “root” for my deployment.<\/p>\n
\u00a0DB_PASS<\/p>\n
\u00a0Password of the above user, prefixed with “-p”. For example if the password is\u00a0Secret<\/span>, then you should write the password as “-pSecret”.<\/p>\n \u00a0BAKUP_LOG<\/p>\n \u00a0File to which the backup log will be written. It should be writable by the user who is running the script.<\/p>\n \u00a0BASE_BAK_FLDR<\/p>\n \u00a0The backup folder. It should be writable by the user who is running the script.<\/p>\n \u00a0RM_FLDR_DAYS<\/p>\n \u00a0Backup rotation period.\u00a0+30<\/span>\u00a0is 30 days.<\/p>\n \n \n For example, if you have taken the backup of “bigdb” on 1st Jan 2007, then the backup will be kept in<\/p>\n \u00a0$BKP_BASE_FLDR\/01-01-2007\/bigdb<\/p>\n \n Following command\/script is an example for restoring a database called\u00a0bigdb <\/span>for which the backup was taken on 1st Jan 2007.<\/p>\n cd \/backup\/01-01-2007\/bigdb;<\/p>\n for table in *; do gunzip -c $table | mysql -u root -pSecret bigdb_new; done.<\/p>\n The above command will iterate through the list of files in the directory and restore all the tables to\u00a0bigdb_new<\/span>\u00a0database. It is assumed that you have created the\u00a0bigdb_new<\/span>\u00a0database prior to running the script.<\/p>\n","protected":false},"excerpt":{"rendered":" Here is a MySQL backup script which can take table by table backups (individual backup files of each table of each database) in a compressed format. It also provides an automatic rotation of old backup files.\u00a0\u00a0The backup script handles innodb and myisam tables\u00a0separately. \u00a0You have to set the following variables prior to running the backup […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[477,91,1,42,51,501],"tags":[631,630,632,133,633],"class_list":["post-896","post","type-post","status-publish","format-standard","hentry","category-backup-2","category-banco-de-dados","category-viazap","category-leitura-recomendada","category-linux-linuxrs","category-shell-script","tag-backup-with-auto-rotation","tag-backup-table-by-table","tag-for-easy-restoration","tag-mysql","tag-of-partialfull-database"],"_links":{"self":[{"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=\/wp\/v2\/posts\/896","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=896"}],"version-history":[{"count":1,"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=\/wp\/v2\/posts\/896\/revisions"}],"predecessor-version":[{"id":897,"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=\/wp\/v2\/posts\/896\/revisions\/897"}],"wp:attachment":[{"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=896"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=896"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.clusterweb.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=896"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}The Backup Script<\/h3>\n
#!\/bin\/bash\r\n# Database Backup script.\r\n# Created By: Mohammed Salih\r\n# Senior System Administrator\r\n# Date: 21\/06\/2007\r\n#\r\n# Database credentials\r\nDB_USER=root\r\n#Please append password in the xxxxx section below, note that there is\r\n# no space between -p and xxxxx\r\nDB_PASS=\"-pxxxxxxx\"\r\n# Get list of Databases except the pid file\r\nDBS_LIST=$(echo \"show databases;\"|mysql -u $DB_USER $DB_PASS -N)\r\n# Log file\r\nBAKUP_LOG=\/backup\/log\/db-backup.log\r\n# Backup Base directory\r\nBASE_BAK_FLDR=\/backup\/db\r\n# Backup rotation period.\r\nRM_FLDR_DAYS=\"+30\"\r\n# From here, only edit if you know what you are doing.\r\nindex=0\r\n# Check if we can connect to the mysql server; otherwise die\r\nif [ ! \"$(id -u -n)\" = \"mysql\" ]; then\r\n echo -e \"Error:: $0 : Only user 'mysql' can run this script\"\r\n exit 100\r\nfi\r\nPING=$(mysqladmin ping -u $DB_USER $DB_PASS 2>\/dev\/null)\r\nif [ \"$PING\" != \"mysqld is alive\" ]; then\r\n echo \"Error:: Unable to connected to MySQL Server, exiting !!\"\r\n exit 101\r\nfi\r\n# Backup process starts here.\r\n# Flush logs prior to the backup.\r\nmysql -u $DB_USER $DB_PASS -e \"FLUSH LOGS\"\r\n# Loop through the DB list and create table level backup,\r\n# applying appropriate option for MyISAM and InnoDB tables.\r\nfor DB in $DBS_LIST; do\r\n DB_BKP_FLDR=$BASE_BAK_FLDR\/$(date +%d-%m-%Y)\/$DB\r\n [ ! -d $DB_BKP_FLDR ] && mkdir -p $DB_BKP_FLDR\r\n # Get the schema of database with the stored procedures.\r\n # This will be the first file in the database backup folder\r\n mysqldump -u $DB_USER $DB_PASS -R -d --single-transaction $DB | \\\r\n gzip -c > $DB_BKP_FLDR\/000-DB_SCHEMA.sql.gz\r\n index=0\r\n #Get the tables and its type. Store it in an array.\r\n table_types=($(mysql -u $DB_USER $DB_PASS -e \"show table status from $DB\" | \\\r\n awk '{ if ($2 == \"MyISAM\" || $2 == \"InnoDB\") print $1,$2}'))\r\n table_type_count=${#table_types[@]}\r\n # Loop through the tables and apply the mysqldump option according to the table type\r\n # The table specific SQL files will not contain any create info for the table schema.\r\n # It will be available in SCHEMA file\r\n while [ \"$index\" -lt \"$table_type_count\" ]; do\r\n START=$(date +%s)\r\n TYPE=${table_types[$index + 1]}\r\n table=${table_types[$index]}\r\n echo -en \"$(date) : backup $DB : $table : $TYPE \"\r\n if [ \"$TYPE\" = \"MyISAM\" ]; then\r\n DUMP_OPT=\"-u $DB_USER $DB_PASS $DB --no-create-info --tables \"\r\n else\r\n DUMP_OPT=\"-u $DB_USER $DB_PASS $DB --no-create-info --single-transaction --tables\"\r\n fi\r\n mysqldump $DUMP_OPT $table |gzip -c > $DB_BKP_FLDR\/$table.sql.gz\r\n index=$(($index + 2))\r\n echo -e \" - Total time : $(($(date +%s) - $START))\\n\"\r\n done\r\ndone\r\n# Rotating old backup. according to the 'RM_FLDR_DAYS'\r\nif [ ! -z \"$RM_FLDR_DAYS\" ]; then\r\n echo -en \"$(date) : removing folder : \"\r\n find $BASE_BAK_FLDR\/ -maxdepth 1 -mtime $RM_FLDR_DAYS -type d -exec rm -rf {} \\;\r\n echo\r\nfi<\/pre>\nThe Backup Location<\/h3>\n
The Restore Script<\/h3>\n