Thank you for the response Phil, We do not have the option of adding disks space to the system now. I do have a large /var/spool/bacula directory if that helps?
I don't believe we have any binary log files in this filesystem that's worth purging. It is the bacula directory and ibadta1 file that is chewing up all the space: /var/lib/mysql# ls -larth total 125G drwx------ 2 mysql mysql 4.0K Jul 22 2015 lost@002bfound -rw-r--r-- 1 mysql mysql 0 Feb 10 19:47 debian-10.3.flag drwx------ 2 mysql mysql 4.0K Feb 10 19:47 mysql drwx------ 2 mysql mysql 4.0K Feb 10 19:47 performance_schema -rw-rw---- 1 mysql mysql 428K Feb 11 14:07 ib_buffer_pool -rw-r----- 1 mysql mysql 0 Feb 11 14:56 multi-master.info -rw------- 1 mysql mysql 16 Feb 11 14:56 mysql_upgrade_info drwx------ 6 mysql mysql 4.0K Feb 11 15:29 . -rw-rw---- 1 mysql mysql 24K Feb 11 15:29 tc.log drwxr-xr-x 45 root root 4.0K Feb 17 21:42 .. drwx------ 2 mysql mysql 4.0K Jun 26 23:54 bacula -rw-rw---- 1 mysql mysql 88K Jul 3 23:03 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Jul 3 23:03 aria_log_control -rw-rw---- 1 mysql mysql 4.4G Jul 4 08:06 ibtmp1 -rw-rw---- 1 mysql mysql 120G Jul 4 09:58 ibdata1 -rw-rw---- 1 mysql mysql 48M Jul 4 09:58 ib_logfile0 -rw-rw---- 1 mysql mysql 48M Jul 4 09:58 ib_logfile1 I've checked the /etc/mysql/my.cnf config file and there is no line for innodb_file_per_table which suggests we are not running this? And as per your next step then it would have to be the dump and reload option. If so, is the right process to stop bacula, run the dump and restore and restart bacula? Would doing any purging of old jobs help in this matter? Appreciate the support/advice! Cheers Chaz Chaz Vidal | ICT Infrastructure | Tel: +61-8-8128-4397 | Mob: +61-492-874-982 | chaz.vi...@sahmri.com -----Original Message----- From: Phil Stracchino <ph...@caerllewys.net> Sent: Saturday, 4 July 2020 8:25 AM To: bacula-users@lists.sourceforge.net Subject: EXTERNAL - Re: [Bacula-users] /var/lib/mysql at 100% [External email: Use caution with links and attachments] On 2020-07-03 17:53, Chaz Vidal wrote: > Hi All > > We've filled up the file system for MySQL although the backups appear > to still be running. > > > Are you able to advise if the commands in the catalog maintenance > section of the Bacula manual will still be the recommended way to free > up some space? > > > mysqldump -f --opt bacula > bacula.sql mysql bacula < bacula.sql rm -f > bacula.sql > > I assume that bacula-dir needs to be shutdown duringt his maintenance > period? > > Hoping for some advice, thanks! OK, this is really not a good way to go about it. It's of questionable safety to perform an operation like this with the filesystem 100% full. First: Do you have any binary logs or other logs on that filesystem? Look for anything in /var/lib/mysql that can be flushed or purged. If the MySQL error log is in that directory, which it often is, flush it and rotate it. See if that frees up a little space. Second: Do you have any options for expanding the filesystem? Particularly expanding it on the fly right now before you do anything else (LVM for instance)? If you can expand it on the fly, do that now, before you do anything else. Next order of business, are you using innodb_file_per_table? If you are, and you're able to free up a little bit of working space, use information_schema.tables to get a list of tables sorted by data_length + index_length ascending, and start doing OPTIMIZE TABLE operations, smallest tables first. If you're lucky this will free up enough space to optimize the large tables as well. If there isn't sufficient space, it'll just fail with no loss of data. If you are NOT using file_per_table, or if the above method doesn't free enough space, then you'll have to dump and reload. The above mysqldump instructions are very minimalist though and I would not recommend doing it that way. --opt is on by default anyway, and -f means continue in spite of errors, something you probably DO NOT want to do when dumping a mission-critical database like your backup catalog. Try using mysqldump -qQcER instead. You may optionally add --skip-lock-tables --single-transaction. If you are NOT already using innodb_file_per_table, I strongly advise converting to file_per_table before you reload. If you are converting to file_per_table, you definitely want to dump and reload ALL databases, not just your bacula database, because you cannot shrink the global tablespace (the ibdata1 file), all you can do is delete and reinitialize it, and doing that will require a full reload of everything. -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: +1.603.293.8485 Mobile: +1.603.998.6958 _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
