{"id":57057,"date":"2020-09-09T12:15:42","date_gmt":"2020-09-09T17:15:42","guid":{"rendered":"https:\/\/blog.cpanel.com\/?p=57057"},"modified":"2020-09-09T12:15:42","modified_gmt":"2020-09-09T17:15:42","slug":"how-to-back-up-and-restore-mysql-databases-in-cpanel","status":"publish","type":"post","link":"https:\/\/devel.www.cpanel.net\/blog\/tips-and-tricks\/how-to-back-up-and-restore-mysql-databases-in-cpanel\/","title":{"rendered":"How to Back Up and Restore MySQL\u00ae Databases in cPanel"},"content":{"rendered":"\n
Data that exist in one place barely exists at all. It\u2019s a hardware failure or mistyped command away from disappearing completely. That’s a scary thought, considering business’ most valuable assets include data. Imagine recreating everything in your database from scratch!<\/p>\n\n\n\n
Regular database backups are essential because that\u2019s where your most important data are stored, whether it\u2019s customer details, email addresses, product catalogs, content, or the hundreds of other types of information businesses need to function. MySQL\u00ae is by far the most widely used open-source database in the world, and that\u2019s why cPanel & WHM makes it so easy to back up and restore MySQL. <\/p>\n\n\n\n
In this article, we take a brief look at what a MySQL backup is and show you how to use cPanel\u2019s MySQL backup and restore tools to keep your data safe. <\/p>\n\n\n\n
MySQL is an open-source relational database management system (RDBMS) used to store, organize, and retrieve information efficiently. cPanel & WHM uses MySQL, and so do many of the most popular content management systems and ecommerce applications, including WordPress\u00ae, Joomla, Drupal, and Magento\u00ae. <\/p>\n\n\n\n
MySQL stores data in tables, which are divided into columns of a predefined data type. Individual records make up the table\u2019s rows, and they have to conform to the types defined by the columns. For example, here is a section of the posts table from a new WordPress site\u2019s database. <\/p>\n\n\n\n How do users get data into and out of MySQL? Unlike a file system, we can\u2019t just edit a file and hit save. We have to talk to the RDBMS in its own language, which is called SQL. To generate the table in the image, we sent the following SQL statement:<\/p>\n\n\n\n SELECT ID,post_date,post_title,post_type,comment_count FROM wp_posts;<\/p>\n\n\n\n As a cPanel user, you won\u2019t have to write SQL because cPanel takes care of that behind the scenes. However, it\u2019s useful to understand what it is because MySQL backups are just a list of SQL statements. <\/p>\n\n\n\n When you run a backup, the software generates the statements needed to recreate the database and saves them to a file. This is often called \u201cexporting\u201d or \u201cdumping\u201d the database. When you restore, MySQL runs those statements to return the database to the state it was in when the backup was made. <\/p>\n\n\n\n Our goal is to dump a database and download the resulting SQL file to our computer, where we can keep it or move it to a more secure location. It is possible to do this from the command line with the \u201cmysql\u201d client, but the cPanel MySQL backup and restore offers an easy-to-use interface.<\/p>\n\n\n\n Either of these methods can be used to back up databases on your server. For this example we are going to use a WordPress installation and a WordPress Database.. A quick warning before we proceed: a WordPress site is composed of a database and<\/em> files and a complete WordPress backup must include both. <\/p>\n\n\n\n This is the most straightforward way to back up MySQL in cPanel, but you can also use the integrated phpMyAdmin administration tool to adjust export settings or back up several databases at once. Here\u2019s how to do that:<\/p>\n\n\n\n Select phpMyAdmin in the Databases<\/em> section of the cPanel main page.<\/p>\n\n\n\n On the left is a list of databases associated with your cPanel account. Click on the one you would like to back up and then on the Export<\/em> tab at the top of the page. <\/p>\n\n\n\n In most circumstances, the Quick<\/em> setting provides the optimal export configuration for MySQL backups, but the Custom<\/em> setting is useful to exclude tables, rename the exported file, or carry out more advanced configuration. If you would like to export several databases simultaneously, open phpMyAdmin and, without selecting a database in the sidebar, click the Export <\/em>tab. The default Quick <\/em>export method will export all available databases. To customize which databases are exported, choose the Custom <\/em>export method, and select the ones you would like to back up.<\/p>\n\n\n\n We\u2019ve seen how easy it is to back up MySQL databases in cPanel, but what if you want to automatically back up a database on a schedule? It\u2019s all too easy to forget, and scheduling ensures that the safety of your data don\u2019t depend on your memory. <\/p>\n\n\n\n To automate a MySQL backup, you can use the cron task scheduler and the command-line \u201cmysqldump\u201d tool. Navigate to the cPanel Cron Jobs<\/em> page, which you\u2019ll find under Advanced<\/em> on the main page. <\/p>\n\n\n\n Use the drop-down boxes to select a time interval, such as once per week. <\/p>\n\n\n\n In the command field, enter the following, replacing the uppercase sections with the appropriate details for your database:<\/p>\n\n\n\n mysqldump -u USERNAME -p PASSWORD database > database_backup.sql<\/p>\n\n\n\n Click the Add New Cron Job<\/em> button, and you\u2019re done. Cron will run the backup at the indicated time interval, saving the SQL file to the location specified at the end of the command (the part after the > symbol). <\/p>\n\n\n\n To learn more about automating tasks with cron jobs, read How To Configure a Cron Job<\/a>.<\/p>\n\n\n\n To restore a database, open the Backup <\/em>interface, which you will find in the Files <\/em>section of the cPanel main page.<\/p>\n\n\n\n Restoring an SQL file overwrites tables and deletes data. If, for example, you published a blog post to your CMS after you last backed up, it will be irreversibly deleted when you restore. Before restoring a backup, make sure it won\u2019t remove data that you want to keep. <\/p>\n\n\n\n In the section titled Restore a MySQL Database Backup<\/em>, click Choose File<\/em> and select an SQL file from your local machine. Click Upload,<\/em> and cPanel will import the file. <\/p>\n\n\n\n For more complex SQL imports or to replicate rather than replace a database, you can use the phpMyAdmin tool described in the previous section. However, the Backup<\/em> page is preferable for standard restores because cPanel handles database prefixes and configuring SQL statements. <\/p>\n\n\n\n It\u2019s hard to overstate the importance of regular MySQL backups. Without a recent backup, your business and its sites are a hardware glitch away from disaster. With cPanel & WHM, you and your users benefit from a simple two-click backup and restore process. <\/p>\n\n\n\n As always, if you have any feedback or comments, please let us know. We are here to help in the best ways we can. You\u2019ll find us on Discord<\/a>, the cPanel forums<\/a>, and Reddit<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":" Data that exist in one place barely exists at all. It\u2019s a hardware failure or mistyped command away from disappearing completely. That’s a scary thought, considering business’ most valuable assets include data. Imagine recreating everything in your database from scratch! Regular database backups are essential because that\u2019s where your most important data are stored, whether […]<\/p>\n","protected":false},"author":77,"featured_media":65505,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[61],"tags":[],"class_list":["post-57057","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tips-and-tricks"],"acf":[],"yoast_head":"\n<\/figure>\n\n\n\n
How to Back Up a MySQL Database in cPanel<\/strong><\/h2>\n\n\n\n
<\/figure>\n\n\n\n
Finally, click on your chosen database to initiate a backup, and cPanel will generate an SQL file and save it to the default download location on your computer.<\/li><\/ul>\n\n\n\n<\/figure>\n\n\n\n
Back Up a Database in cPanel with phpMyAdmin<\/strong><\/h3>\n\n\n\n
<\/figure>\n\n\n\n
<\/figure>\n\n\n\n
<\/figure>\n\n\n\n
Schedule MySQL Backups with Cron in cPanel<\/strong><\/h2>\n\n\n\n
<\/figure>\n\n\n\n
How to Restore a MySQL Database in cPanel<\/strong><\/h2>\n\n\n\n
<\/figure>\n\n\n\n
<\/figure>\n\n\n\n