{"id":77,"date":"2012-02-15T06:24:00","date_gmt":"2012-02-15T06:24:00","guid":{"rendered":"http:\/\/pheonixsolutions.com\/?p=77"},"modified":"2026-06-25T08:03:28","modified_gmt":"2026-06-25T02:33:28","slug":"script-to-make-a-backup-of-the-current-database-and-restore-db-from-the-available-backup-of-all-the-database-owned-by-a-particular-user-in-cpanel","status":"publish","type":"post","link":"https:\/\/pheonixsolutions.com\/blog\/script-to-make-a-backup-of-the-current-database-and-restore-db-from-the-available-backup-of-all-the-database-owned-by-a-particular-user-in-cpanel\/","title":{"rendered":"Script to Back Up and Restore All Databases Owned by a Particular cPanel User"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Introduction<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In cPanel servers, each user may have multiple MySQL databases associated with their account. During migration, restoration, or troubleshooting, we may need to take a backup of all databases owned by a particular cPanel user and restore them from an available backup.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This can be done using a simple shell script from the command line. The script will identify the databases owned by the specified user, take a backup of the current databases, and then restore the databases from the available backup files.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Prerequisites<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before proceeding, make sure you have the following:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Root SSH access to the server<\/li>\n\n\n\n<li>MySQL or MariaDB service running<\/li>\n\n\n\n<li>The cPanel username<\/li>\n\n\n\n<li>Available database backup files<\/li>\n\n\n\n<li>Sufficient disk space to store database backups<\/li>\n\n\n\n<li>Basic knowledge of Linux commands<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Important Note<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before restoring any database, always take a backup of the existing database. This helps to avoid data loss if the restore process fails or if the wrong backup file is restored.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Also, do not directly copy SQL files into <code>\/var\/lib\/mysql<\/code> for restoration. The proper method is to restore the database using the <code>mysql<\/code> command.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Steps to Back Up and Restore Databases<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Create the Script File<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Create a script file using the following command:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">vi db_backup_restore.sh\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 2: Add the Script<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Replace <code>username<\/code> with the actual cPanel username.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">#!\/bin\/bash\n\nUSER=\"username\"\nBACKUP_DIR=\"\/home\/$USER\/db-backup-$(date +%F-%H-%M)\"\nRESTORE_DIR=\"\/backup\/cpbackup\/monthly\/$USER\/mysql\"\n\nmkdir -p \"$BACKUP_DIR\"\n\necho \"Fetching database list for user: $USER\"\n\nmysql -N -e \"SHOW DATABASES LIKE '${USER}\\_%';\" > \/tmp\/${USER}_dblist.txt\n\necho \"Taking backup of current databases...\"\n\nfor db in $(cat \/tmp\/${USER}_dblist.txt)\ndo\n    echo \"Backing up database: $db\"\n    mysqldump \"$db\" > \"$BACKUP_DIR\/$db.sql\"\ndone\n\necho \"Current database backups saved in: $BACKUP_DIR\"\n\necho \"Restoring databases from available backup...\"\n\nfor db in $(cat \/tmp\/${USER}_dblist.txt)\ndo\n    BACKUP_FILE=\"$RESTORE_DIR\/$db.sql\"\n\n    if [ -f \"$BACKUP_FILE\" ]; then\n        echo \"Restoring database: $db\"\n        mysql \"$db\" &lt; \"$BACKUP_FILE\"\n    else\n        echo \"Backup file not found for database: $db\"\n    fi\ndone\n\necho \"Database backup and restore process completed.\"\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 3: Save and Exit<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Save the file and exit from the editor.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you are using <code>vi<\/code>, press:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">Esc\n:wq\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 4: Give Execute Permission<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Run the following command:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">chmod +x db_backup_restore.sh\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 5: Execute the Script<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Run the script as the root user:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">.\/db_backup_restore.sh\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The script will:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Fetch the list of databases owned by the cPanel user.<\/li>\n\n\n\n<li>Take a backup of the current databases.<\/li>\n\n\n\n<li>Store the backup under the user\u2019s home directory.<\/li>\n\n\n\n<li>Restore the databases from the available backup path.<\/li>\n\n\n\n<li>Show the restore status for each database.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\">Example Backup Location<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The current database backups will be stored under:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">\/home\/username\/db-backup-date-time\/\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Example:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">\/home\/username\/db-backup-2026-06-25-10-30\/\n<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This script is useful when you need to back up and restore all databases owned by a specific cPanel user. It helps reduce manual work and ensures that the current databases are backed up before restoring from an available backup.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Always verify the backup path and database names before running the restore process to avoid accidental data loss.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction In cPanel servers, each user may have multiple MySQL databases associated with their account. During migration, restoration, or troubleshooting, [&hellip;]<\/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-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":"","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-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":"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":""},"mobile":{"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":""}},"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[1019],"tags":[],"class_list":["post-77","post","type-post","status-publish","format-standard","hentry","category-cloud-aws","psol-cat-cloud-aws"],"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/phn2x7-1f","jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/77","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=77"}],"version-history":[{"count":1,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/77\/revisions"}],"predecessor-version":[{"id":10424,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/77\/revisions\/10424"}],"wp:attachment":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=77"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=77"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=77"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}