Database Backup Options

linux4me2

• May 06, 2020 13:27

i'm looking at the Entire MySQL Directory " Back up all of the databases on the server from the /var/lib/mysql/ directory.

Per Account and Entire MySQL Directory " Perform a comprehensive backup that copies all of the databases for each individual account and all of the databases on the server. Next, it gives a scary warning which I believe refers to option #3: [QUOTE] Warning: Exercise extreme caution if you select this setting. This setting backs up your databases without stopping MySQL". This action causes InnoDB issues in the backup file. The system can"t restore backup files with InnoDB issues.
All my tables are InnoDB, so if I'm correct that the warning refers to option #3, it's not a good way for me to go. That leaves only options #1 and #2. It seems like option #1 will make it easiest to restore a database for an individual account, but there are some databases in /var/lib/mysql/ that aren't associated with user accounts I should probably be backing up as well. What I'm wondering is that if I go with option #2, will the users' databases still be in the /accounts/username.tar.gz files that Backup creates? Does option #2 use mysqldump so that InnoDB issues aren't created?

• 

  GOT

  • May 06, 2020 18:35

  You only want to use Option 1. The only time this causes an issue is if you have manually created a database not through the cPanel account interface because that database would not be tied to a user and would not get backed up. If that is the case then you can use the database map tool in whm to attach a database to a user.

  0
• 

  linux4me2

  • May 06, 2020 18:41

  You only want to use Option 1. The only time this causes an issue is if you have manually created a database not through the cPanel account interface because that database would not be tied to a user and would not get backed up. If that is the case then you can use the database map tool in whm to attach a database to a user.

  
  Thanks! That's what I needed to know. I don't have any databases created outside of cPanel's UI, so I'll go with option #1. It's probably been set on opion #3 since I got this server, and I'm glad I haven't needed a database backup and run into those "InnoDB issues."

  0
• 

  GOT

  • May 06, 2020 18:43

  Well, you DID say: there are some databases in /var/lib/mysql/ that aren't associated with user accounts So, those should be mapped to user accounts if you do have them.

  0
• 

  linux4me2

  • May 06, 2020 19:36

  Well, you DID say: there are some databases in /var/lib/mysql/ that aren't associated with user accounts So, those should be mapped to user accounts if you do have them.

  
  That was misleading. I'm talking about:

  • cphulkd
  • mysql
  • performance_schema

  0
• 

  GOT

  • May 06, 2020 19:41

  Yes you don't need to worry about backing up the system databases.

  0
• 

  linux4me2

  • May 06, 2020 19:45

  Cool. Thanks!

  0
• 

  cPanelLauren

  • May 06, 2020 19:49

  Thanks @GOT for the knowledgeable and helpful response :)

  0
• 

  linux4me2

  • May 06, 2020 20:10

  Thanks @GOT for the knowledgeable and helpful response :)

  
  @cPanelLauren You can mark this one solved if you like.

  0

Please sign in to leave a comment.