Unrecognized files in /var/lib/mysql/nagiosxi directory

[bsanjay]

Hello Team,
We can see there are multiple files in directory /var/lib/mysql/nagiosxi that starts with #sql_ . We are not sure about those files as these are not updated since long and this are consuming disk space. Please find below the list of all such files in directory /var/lib/mysql/nagiosxi and advice if this files are required by Nagios or we can delete it from there,

bsanjay@nagiosXI:[/var/lib/mysql/nagiosxi]: ls -lhart
total 29G
-rw-rw---- 1 mysql mysql 65 Jun 28 2017 db.opt
-rw-rw---- 1 mysql mysql 8.7K Jun 28 2017 xi_incidents.frm
-rw-rw---- 1 mysql mysql 96K Jun 28 2017 xi_incidents.ibd
-rw-rw---- 1 mysql mysql 8.6K Oct 15 2018 xi_eventqueue.frm
-rw-rw---- 1 mysql mysql 8.6K Feb 2 2019 #sql-16b5_12a996.frm
-rw-rw---- 1 mysql mysql 8.6K Feb 2 2019 #sql-3ac3_1eb.frm
-rw-rw---- 1 mysql mysql 867M Feb 2 2019 #sql-16b5_12a996.ibd
-rw-rw---- 1 mysql mysql 8.6K Feb 2 2019 #sql-4a91_219.frm
-rw-rw---- 1 mysql mysql 432M Feb 2 2019 #sql-3ac3_1eb.ibd
-rw-rw---- 1 mysql mysql 880K Feb 2 2019 #sql-4a91_219.ibd
-rw-rw---- 1 mysql mysql 8.6K Feb 23 2019 #sql-2392_7ac71.frm
-rw-rw---- 1 mysql mysql 4.3G Feb 23 2019 #sql-2392_7ac71.ibd
-rw-rw---- 1 mysql mysql 8.6K Feb 23 2019 #sql-3d6e_d6.frm
-rw-rw---- 1 mysql mysql 1.7G Feb 23 2019 #sql-3d6e_d6.ibd
-rw-rw---- 1 mysql mysql 8.6K Feb 23 2019 #sql-4409_be.frm
-rw-rw---- 1 mysql mysql 16M Feb 23 2019 #sql-4409_be.ibd
-rw-rw---- 1 mysql mysql 8.6K Feb 23 2019 #sql-1692_133.frm
-rw-rw---- 1 mysql mysql 30M Feb 23 2019 #sql-1692_133.ibd
-rw-rw---- 1 mysql mysql 8.7K Feb 23 2019 #sql-6897_180.frm
-rw-rw---- 1 mysql mysql 192K Feb 23 2019 #sql-6897_180.ibd
-rw-rw---- 1 mysql mysql 8.6K Feb 23 2019 #sql-4f13_17a.frm
-rw-rw---- 1 mysql mysql 3.0M Feb 23 2019 #sql-4f13_17a.ibd
-rw-rw---- 1 mysql mysql 8.6K Feb 23 2019 #sql-1f31_3fd.frm
-rw-rw---- 1 mysql mysql 1.7G Feb 23 2019 #sql-1f31_3fd.ibd
-rw-rw---- 1 mysql mysql 8.6K Feb 23 2019 #sql-4008_18f.frm
-rw-rw---- 1 mysql mysql 442M Feb 23 2019 #sql-4008_18f.ibd
-rw-rw---- 1 mysql mysql 8.6K Feb 23 2019 #sql-5825_11f.frm
-rw-rw---- 1 mysql mysql 829M Feb 23 2019 #sql-5825_11f.ibd
-rw-rw---- 1 mysql mysql 8.6K Feb 23 2019 #sql-4a31_d6.frm
-rw-rw---- 1 mysql mysql 4.0M Feb 23 2019 #sql-4a31_d6.ibd
-rw-rw---- 1 mysql mysql 8.6K Mar 22 00:16 #sql-611e_d2c08.frm
-rw-rw---- 1 mysql mysql 612M Mar 22 00:19 #sql-611e_d2c08.ibd
-rw-rw---- 1 mysql mysql 8.6K Mar 29 00:22 #sql-3e97_e7197.frm
-rw-rw---- 1 mysql mysql 13M Mar 29 00:22 #sql-3e97_e7197.ibd
-rw-rw---- 1 mysql mysql 8.6K Mar 31 10:17 #sql-2b9c_45e62.frm
-rw-rw---- 1 mysql mysql 5.8G Mar 31 10:24 #sql-2b9c_45e62.ibd
-rw-rw---- 1 mysql mysql 8.6K Mar 31 10:27 #sql-97d_173.frm
-rw-rw---- 1 mysql mysql 2.7G Mar 31 10:31 #sql-97d_173.ibd
-rw-rw---- 1 mysql mysql 8.6K Mar 31 10:33 #sql-37a9_9f.frm
-rw-rw---- 1 mysql mysql 1011M Mar 31 10:36 #sql-37a9_9f.ibd
-rw-rw---- 1 mysql mysql 8.6K Mar 31 10:37 #sql-2596_51.frm
-rw-rw---- 1 mysql mysql 17M Mar 31 10:38 #sql-2596_51.ibd
-rw-rw---- 1 mysql mysql 8.6K Apr 5 00:08 #sql-3d03_9a9d9.frm
-rw-rw---- 1 mysql mysql 2.6G Apr 5 00:10 #sql-3d03_9a9d9.ibd
-rw-rw---- 1 mysql mysql 8.6K Apr 5 00:18 #sql-771c_1c7.frm
-rw-rw---- 1 mysql mysql 5.3G Apr 5 00:23 #sql-771c_1c7.ibd
-rw-rw---- 1 mysql mysql 8.7K Apr 5 00:27 #sql-7e3f_fa.frm
-rw-rw---- 1 mysql mysql 464K Apr 5 00:27 #sql-7e3f_fa.ibd
drwxr-xr-x 7 mysql mysql 4.0K May 26 09:05 ..
-rw-rw---- 1 mysql mysql 8.7K Jun 1 13:58 xi_auditlog.frm
-rw-rw---- 1 mysql mysql 9.0K Jun 1 13:58 xi_commands.frm
-rw-rw---- 1 mysql mysql 8.6K Jun 1 13:58 xi_events.frm
-rw-rw---- 1 mysql mysql 8.6K Jun 1 13:58 xi_meta.frm
-rw-rw---- 1 mysql mysql 8.6K Jun 1 13:58 xi_mibs.frm
-rw-rw---- 1 mysql mysql 8.5K Jun 1 13:58 xi_options.frm
-rw-rw---- 1 mysql mysql 8.5K Jun 1 13:58 xi_sysstat.frm
-rw-rw---- 1 mysql mysql 8.6K Jun 1 13:58 xi_usermeta.frm
-rw-rw---- 1 mysql mysql 9.1K Jun 1 13:58 xi_users.frm
-rw-rw---- 1 mysql mysql 8.8K Jun 1 13:58 xi_sessions.frm
-rw-rw---- 1 mysql mysql 8.8K Jun 1 13:58 xi_auth_tokens.frm
-rw-rw---- 1 mysql mysql 9.2K Jun 1 13:58 xi_cmp_trapdata.frm
-rw-rw---- 1 mysql mysql 9.3K Jun 1 13:58 xi_cmp_trapdata_log.frm
drwx------ 2 mysql mysql 4.0K Jun 1 13:58 .
-rw-rw---- 1 mysql mysql 304K Jun 1 13:58 xi_auditlog.ibd
-rw-rw---- 1 mysql mysql 144K Jun 1 14:01 xi_options.ibd
-rw-rw---- 1 mysql mysql 128K Jun 1 14:01 xi_mibs.ibd
-rw-rw---- 1 mysql mysql 32M Jun 1 14:02 xi_usermeta.ibd
-rw-rw---- 1 mysql mysql 256K Jun 1 14:02 xi_users.ibd
-rw-rw---- 1 mysql mysql 112K Jun 1 14:02 xi_sessions.ibd
-rw-rw---- 1 mysql mysql 112K Jun 1 14:02 xi_cmp_trapdata.ibd
-rw-rw---- 1 mysql mysql 112K Jun 1 14:02 xi_auth_tokens.ibd
-rw-rw---- 1 mysql mysql 112K Jun 1 14:02 xi_cmp_trapdata_log.ibd
-rw-rw---- 1 mysql mysql 96K Jun 1 14:02 xi_commands.ibd
-rw-rw---- 1 mysql mysql 187K Jun 1 14:14 xi_eventqueue.MYI
-rw-rw---- 1 mysql mysql 14M Jun 1 14:14 xi_eventqueue.MYD
-rw-rw---- 1 mysql mysql 112K Jun 1 14:14 xi_sysstat.ibd
-rw-rw---- 1 mysql mysql 64M Jun 1 14:14 xi_meta.ibd
-rw-rw---- 1 mysql mysql 10M Jun 1 14:14 xi_events.ibd


Best Regards,
BSanjay

[bsanjay]

Hello Team,
Also we have this 2 files inside /var/lib/mysql directory which is consuming disk space. So, please advice on this files, if they are supposed to be there or we can delete it.

bsanjay@nagiosXI:[~]: ls -l /var/lib/mysql/
-rw-rw---- 1 mysql mysql 1073741824 Jun 1 14:26 ib_logfile0
-rw-rw---- 1 mysql mysql 1073741824 Jun 1 14:26 ib_logfile1

Best Regards,
BSanjay

[jdunitz]

Hello!

Can you post the output of " mysql -u root -p'nagiosxi' nagiosxi -e 'show tables'" ?

Thanks!
--Jeffrey

[bsanjay]

Hello jdunitz,
Please find the output of required comamnd attached,

bsanjay@nagiosXI:[~]: mysql -u root -p'nagiosxi' nagiosxi -e 'show tables'
+---------------------+
| Tables_in_nagiosxi |
+---------------------+
| xi_auditlog |
| xi_auth_tokens |
| xi_cmp_trapdata |
| xi_cmp_trapdata_log |
| xi_commands |
| xi_eventqueue |
| xi_events |
| xi_incidents |
| xi_meta |
| xi_mibs |
| xi_options |
| xi_sessions |
| xi_sysstat |
| xi_usermeta |
| xi_users |
+---------------------+



Best Regards,
Sanjay Batkura

[jdunitz]

Looks like you can do a snapshot, then delete all the old files from a few months ago that you identified.
Most of them are small, but that 867MB one will recover a good bit of space.

--Jeffrey

[bsanjay]

Hello jdunitz,
By saying snapshot are you referring to take backup of this files and then we can delete them i.e. files that starts with hash which are created few months back can be deleted directly ?.

Also, i want some understanding on this files like,
How they got created ?
And why are this files in this location ?

Best Regards,
BSanjay

[ssax]

See here:

https://serverfault.com/questions/36439 ... ata-folder

The only way to reduce those large files is to convert to innodb_file_per_table.

What is the output of this command?

Code: Select all

grep innodb_file_per_table /etc/my.cnf

[bsanjay]

Hello ssax,
From the link you shared what i understood is to drop those tables which starts with #sql DROP TABLE #mysql50##sql-537f_8d5b. But unfortunately those table does not exists in any of the databases. I did also shared the list of tables available inside DB in my previous response.
So the question is, can we execute DROP table query in this case where this tables does not exist in DB ?

Please find output of the requested command attached along with /etc/my.cnf file,


Best Regards,
BSanjay

[ssax]

Try removing this line from your /etc/my.cnf:

Code: Select all

innodb_file_per_table

Yes, try dropping those tables, if it doesn't work, try prepending them with:

Code: Select all

#mysql50#

So:

Code: Select all

mysql -uroot -pnagiosxi -h 127.0.0.1 -e 'DROP TABLE #sql-16b5_12a996;'

If that doesn't work, try with the prepend:

Code: Select all

mysql -uroot -pnagiosxi -h 127.0.0.1 -e 'DROP TABLE #mysql50##sql-16b5_12a996;'

Do not simply delete the files - otherwise you will get so called orphaned tables yielding database engine warnings like these:

InnoDB: in InnoDB data dictionary has tablespace id N,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.

Taken from here: https://serverfault.com/a/364450


Let us know the results.