{"id":4503,"date":"2018-05-03T14:33:08","date_gmt":"2018-05-03T18:33:08","guid":{"rendered":"http:\/\/wp-p3d2tsa2xh.pairsite.com\/?post_type=ht_kb&p=4503"},"modified":"2020-10-16T14:29:06","modified_gmt":"2020-10-16T18:29:06","slug":"how-to-set-up-automated-database-backups-on-freebsd","status":"publish","type":"ht_kb","link":"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/","title":{"rendered":"How to Set Up Automated Database Backups on FreeBSD"},"content":{"rendered":"

Shared<\/p><\/a>

VPS<\/p><\/a>

Dedicated<\/p><\/a><\/span><\/p>\n

You set up automated database backups on FreeBSD servers. Complete the three processes below to create a recurring database backup on your hosting account. <\/span>
\n<\/a><\/p>\n

Set Up a MySQL Login Path<\/h3>\n

Setting up a MySQL login path will help keep the connection secure. The file is encrypted and any time the file is used, the connection information (usernames, passwords, etc.) will not be exposed. To set up a MySQL login path, follow these instructions:<\/span><\/p>\n

    \n
  1. Log in to your account using SSH. For information about how to do this, see our article, <\/span>How to Connect to Your Account with SSH<\/span><\/a><\/li>\n
  2. Once successfully logged in to your account, enter the following command into the terminal\/command line:
    \n<\/span><\/p>\n
    mysql_config_editor set --login-path=<\/span>DBDescription<\/span> --host=<\/span>DB#.pair.com <\/span>--user=<\/span>DBUser<\/span> --port=3306 --password<\/span><\/pre>\n\n\n\n\n\n
    DBDescription<\/td>\nThis can be whatever description you want. Ideally, this is something you can easily remember and identify the database by.<\/td>\n<\/tr>\n
    DB##.pair.com<\/td>\nThis will be your database server. It will resemble something like this: db12.pair.com<\/i>. You can find the server in your database management interface.<\/td>\n<\/tr>\n
    DBUser<\/td>\nThis is the database user that will be used to login to the database. This is not<\/i> the same as your account username. You can find the server in your database management interface.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n
  3. You will be prompted to enter your database password<\/span><\/li>\n
  4. Next, enter the following command to make sure the login-path data was entered correctly:<\/span>
    \n<\/span><\/p>\n
    mysql_config_editor print --login-path=<\/span>DBDescription<\/span><\/pre>\n
    The output will resemble this: <\/span>
    \n<\/span><\/p>\n
    [<\/span>DBDescription<\/span>]<\/span>\r\nuser = DBUSER<\/span>\r\npassword = *****<\/span>\r\nhost = DB#.pair.com<\/span>\r\nport = 3306<\/span><\/pre>\n<\/li>\n<\/ol>\n
    This will create a <\/span>.mylogin.cnf <\/span><\/i>file in your directory. It will be accessed each time the cron job runs so that you are not prompted for a password each time your database is backed up. <\/span><\/p>\n
    Adding the Shell Script<\/h3>\n
    The next step is to add the shell script. Copy the text below and save it into a file named <\/span>mysqlback.sh<\/span><\/i> on your account. We recommend saving it in a subdirectory. Be sure to customize the script so that it contains the correct information. <\/span><\/p>\n
    #!\/bin\/sh<\/span>\r\n\r\n#Set utility paths<\/span>\r\nMYDUMP=<\/span>\/usr\/local\/bin\/mysqldump\u00a0\r\nBACKUPDIR=\/usr\/home\/<\/span>USERNAME\/PATH\/TO\/BACKUP\/DIRECTORY<\/span>\r\nMYCHMOD=\/bin\/chmod<\/span>\r\nMYGZIP=\/usr\/bin\/gzip<\/span>\r\nMYFIND=\/usr\/bin\/find<\/span>\r\n\r\n\r\n#Install db backup<\/span>\r\n$MYDUMP --login-path=<\/span>DBDESCRIPTION <\/span>DATABASENAME<\/span> --no-tablespaces > $BACKUPDIR\/<\/span>DATABASENAME<\/span>.`date '+%m%d%Y'`.sql<\/span>\r\n\r\n#Lock down permissions on backup file<\/span>\r\n$MYCHMOD 600 $BACKUPDIR\/<\/span>DATABASENAME<\/span>.`date '+%m%d%Y'`.sql<\/span>\r\n\r\n#Compress backup file<\/span>\r\n$MYGZIP $BACKUPDIR\/<\/span>DATABASENAME<\/span>.`date '+%m%d%Y'`.sql<\/span>\r\n\r\n\r\n#If you want to backup a second database at the same time\r\n<\/span>#Make sure you set up a MySQL Login Path for the second database\r\n$MYDUMP --login-path=<\/span>DBDESCRIPTION <\/span>DATABASENAME<\/span> --no-tablespaces > \\<\/span>\r\n$BACKUPDIR\/<\/span>DATABASENAME<\/span>.`date '+%m%d%Y'`.sql<\/span>\r\n\r\n#Lock down permissions on backup file<\/span>\r\n$MYCHMOD 600 $BACKUPDIR\/<\/span>DATABASENAME<\/span>.`date '+%m%d%Y'`.sql<\/span>\r\n\r\n#Compress backup file<\/span>\r\n$MYGZIP $BACKUPDIR\/<\/span>DATABASENAME<\/span>.`date '+%m%d%Y'`.sql<\/span>\r\n\r\n#Clean things up, only keep 8 days (+8d) of backups around<\/span>\r\n$MYFIND $BACKUPDIR -regex \".*[sql.gz]\" -ctime +8d -delete<\/pre>\n\n\n\n\n\n\n
    \/PATH\/TO\/BACKUP\/DIRECTORY<\/td>\nThe file path to the directory where you want the backup to be stored<\/span><\/td>\n<\/tr>\n
    DBDESCRIPTION<\/td>\nThe name of the database. This <\/span>must<\/span><\/i> match the description you created for --login-path<\/span> in the Set Up MySQL Login section<\/a>.<\/span><\/td>\n<\/tr>\n
    USERNAME<\/span><\/td>\nPair Networks account username<\/span><\/td>\n<\/tr>\n
    DATABASENAME<\/span><\/td>\nName of your database. Pair Networks database names are usually comprised of your username and unique database name. So for example, your database name may look like, \"jsmith_mydatabase\"<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Set Up Cron Job<\/h3>\n
    Cron jobs are used to create recurring processes that will run on your server. For more information about creating a cron job, see our article, <\/span>How to Create a Cron Job<\/span><\/a>. <\/span><\/p>\n
    When creating a cron job, you will be asked for a <\/span>Command to Run<\/b>. This command is the command that will start the backup process. In the <\/span>Command to Run<\/b> field, enter this command: <\/span><\/p>\n
    \/usr\/home<\/span>\/USERNAME\/PATH\/TO\/mysqlback.sh<\/span><\/span><\/pre>\n
    You\u2019ll need to replace the following items in the command above:<\/span><\/p>\n\n\n\n\n
    \/PATH\/TO\/mysqlback.sh<\/span><\/td>\nThe file path to the <\/span>mysqlback.sh<\/span><\/i> file (this allows the cron job to locate and run the script for each backup)<\/span><\/td>\n<\/tr>\n
    USERNAME<\/td>\nPair Networks account username<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Save the cron job.<\/span><\/p>\n
    Once you have successfully set up the cron job, <\/span>.mylogin.cnf<\/span><\/i> file, and the script file, your account will begin to backup your database. You can find your backups in the backup directory on your account. <\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
    You set up automated database backups on FreeBSD servers. Complete the three processes below to create a recurring database backup on your hosting account. Set Up a MySQL Login Path Setting up a MySQL login path will help keep the connection secure. The file is encrypted and any time the…<\/p>\n","protected":false},"author":12,"comment_status":"closed","ping_status":"closed","template":"","format":"standard","meta":[],"ht-kb-category":[23],"ht-kb-tag":[210,178,176,177],"yoast_head":"\nHow to Set Up Automated Database Backups on FreeBSD - Knowledge Base - Pair Networks<\/title>\n<meta name=\"description\" content=\"You set up automated database backups on FreeBSD servers. There are three main steps to creating recurring database backup on your hosting account.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Set Up Automated Database Backups on FreeBSD - Knowledge Base - Pair Networks\" \/>\n<meta property=\"og:description\" content=\"You set up automated database backups on FreeBSD servers. There are three main steps to creating recurring database backup on your hosting account.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/\" \/>\n<meta property=\"og:site_name\" content=\"Knowledge Base - Pair Networks\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/pairnetworks\/\" \/>\n<meta property=\"article:modified_time\" content=\"2020-10-16T18:29:06+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:site\" content=\"@pairNetworks\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/\",\"url\":\"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/\",\"name\":\"How to Set Up Automated Database Backups on FreeBSD - Knowledge Base - Pair Networks\",\"isPartOf\":{\"@id\":\"https:\/\/www.pair.com\/support\/kb\/#website\"},\"datePublished\":\"2018-05-03T18:33:08+00:00\",\"dateModified\":\"2020-10-16T18:29:06+00:00\",\"description\":\"You set up automated database backups on FreeBSD servers. There are three main steps to creating recurring database backup on your hosting account.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.pair.com\/support\/kb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Set Up Automated Database Backups on FreeBSD\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.pair.com\/support\/kb\/#website\",\"url\":\"https:\/\/www.pair.com\/support\/kb\/\",\"name\":\"Knowledge Base - Pair Networks\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.pair.com\/support\/kb\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.pair.com\/support\/kb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.pair.com\/support\/kb\/#organization\",\"name\":\"Pair Networks\",\"url\":\"https:\/\/www.pair.com\/support\/kb\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.pair.com\/support\/kb\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.pair.com\/support\/kb\/wp-content\/uploads\/2022\/02\/Gb_TUkUE_400x400.jpeg\",\"contentUrl\":\"https:\/\/www.pair.com\/support\/kb\/wp-content\/uploads\/2022\/02\/Gb_TUkUE_400x400.jpeg\",\"width\":360,\"height\":360,\"caption\":\"Pair Networks\"},\"image\":{\"@id\":\"https:\/\/www.pair.com\/support\/kb\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/pairnetworks\/\",\"https:\/\/twitter.com\/pairNetworks\",\"https:\/\/www.instagram.com\/pairnetworks\/\",\"https:\/\/www.linkedin.com\/company\/2269676\/\",\"https:\/\/www.pinterest.com\/pairnetworksinc\/pins\/\",\"https:\/\/www.youtube.com\/user\/pairnetworks\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Set Up Automated Database Backups on FreeBSD - Knowledge Base - Pair Networks","description":"You set up automated database backups on FreeBSD servers. There are three main steps to creating recurring database backup on your hosting account.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/","og_locale":"en_US","og_type":"article","og_title":"How to Set Up Automated Database Backups on FreeBSD - Knowledge Base - Pair Networks","og_description":"You set up automated database backups on FreeBSD servers. There are three main steps to creating recurring database backup on your hosting account.","og_url":"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/","og_site_name":"Knowledge Base - Pair Networks","article_publisher":"https:\/\/www.facebook.com\/pairnetworks\/","article_modified_time":"2020-10-16T18:29:06+00:00","twitter_card":"summary_large_image","twitter_site":"@pairNetworks","twitter_misc":{"Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/","url":"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/","name":"How to Set Up Automated Database Backups on FreeBSD - Knowledge Base - Pair Networks","isPartOf":{"@id":"https:\/\/www.pair.com\/support\/kb\/#website"},"datePublished":"2018-05-03T18:33:08+00:00","dateModified":"2020-10-16T18:29:06+00:00","description":"You set up automated database backups on FreeBSD servers. There are three main steps to creating recurring database backup on your hosting account.","breadcrumb":{"@id":"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.pair.com\/support\/kb\/how-to-set-up-automated-database-backups-on-freebsd\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pair.com\/support\/kb\/"},{"@type":"ListItem","position":2,"name":"How to Set Up Automated Database Backups on FreeBSD"}]},{"@type":"WebSite","@id":"https:\/\/www.pair.com\/support\/kb\/#website","url":"https:\/\/www.pair.com\/support\/kb\/","name":"Knowledge Base - Pair Networks","description":"","publisher":{"@id":"https:\/\/www.pair.com\/support\/kb\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pair.com\/support\/kb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.pair.com\/support\/kb\/#organization","name":"Pair Networks","url":"https:\/\/www.pair.com\/support\/kb\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.pair.com\/support\/kb\/#\/schema\/logo\/image\/","url":"https:\/\/www.pair.com\/support\/kb\/wp-content\/uploads\/2022\/02\/Gb_TUkUE_400x400.jpeg","contentUrl":"https:\/\/www.pair.com\/support\/kb\/wp-content\/uploads\/2022\/02\/Gb_TUkUE_400x400.jpeg","width":360,"height":360,"caption":"Pair Networks"},"image":{"@id":"https:\/\/www.pair.com\/support\/kb\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/pairnetworks\/","https:\/\/twitter.com\/pairNetworks","https:\/\/www.instagram.com\/pairnetworks\/","https:\/\/www.linkedin.com\/company\/2269676\/","https:\/\/www.pinterest.com\/pairnetworksinc\/pins\/","https:\/\/www.youtube.com\/user\/pairnetworks"]}]}},"_links":{"self":[{"href":"https:\/\/www.pair.com\/support\/kb\/wp-json\/wp\/v2\/ht-kb\/4503"}],"collection":[{"href":"https:\/\/www.pair.com\/support\/kb\/wp-json\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/www.pair.com\/support\/kb\/wp-json\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.pair.com\/support\/kb\/wp-json\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pair.com\/support\/kb\/wp-json\/wp\/v2\/comments?post=4503"}],"version-history":[{"count":21,"href":"https:\/\/www.pair.com\/support\/kb\/wp-json\/wp\/v2\/ht-kb\/4503\/revisions"}],"predecessor-version":[{"id":7793,"href":"https:\/\/www.pair.com\/support\/kb\/wp-json\/wp\/v2\/ht-kb\/4503\/revisions\/7793"}],"wp:attachment":[{"href":"https:\/\/www.pair.com\/support\/kb\/wp-json\/wp\/v2\/media?parent=4503"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/www.pair.com\/support\/kb\/wp-json\/wp\/v2\/ht-kb-category?post=4503"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/www.pair.com\/support\/kb\/wp-json\/wp\/v2\/ht-kb-tag?post=4503"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}