very large mysqld.log file - safely clear it?

[morrow95]

So today I noticed that the `/var/log/mysqld.log` file has grown to a very large size. The weird thing (to me) is this is the vm that has whm on it and we setup a profile in it to use a remote vm for databases. I guess I am confused why this log is being populated on this vm since all db stuff is handled elsewhere.

Anyways, I modified the my.cnf file to comment out the following line (on the whm vm) :

```#log-error=/var/log/mysqld.log```

From what I can tell this will stop creating the log. Should I need it in the future I can always uncomment it. But, how can I safely clear/empty the existing `mysqld.log` file to free up all that space being used? And, aside from no entries being created anymore are there any other risks as far as operation with it turned off?

Thanks for any information.

 

[cPRex]

Hey there! That would keep new entries from being written after the MySQL service is restarted.

Instead of clearing the log, which you can safely do, I'd recommend looking through it and seeing what was causing so many errors.

You can just run this to clear the log, which essentially just writes blank content to the file:

echo "" /var/log/mysql.log

As for your other post, sometimes the Cloudflare protection is a bit too much with file paths, but this is something that will be resolved when we do the next major Forums update.

 

[morrow95]

Yes, I plan on downloading the file and taking a look before doing anything. Thank you as always - very helpful.

 

[morrow95]

On a side note... any particular reason why this log isn't included in the log rotation settings? Also, still curious why this was being populated on this vm since our entire db service and dbs are on a remote server?

 

[cPRex]

Unless the MySQL service was disabled, it will still be running and logging anything locally that may be on a user's account. It's hard to say without knowing specifically what was logged.

cPanel log rotation handles cPanel system logs and Apache logs, but anything else needs to be manually added. cpanellogd specifically mentions more details about that here:

Log Rotation | cPanel & WHM Documentation

This interface allows you to select Apache log files for the cpanellogd daemon to compress and archive.

docs.cpanel.net

Most systems have /usr/share/mysql/mysql-log-rotate in place, but that would need to be configured manually, outside of the cPanel tools.

If you'd like to see the MySQL log get added to the logrotate service, please open a feature request using the link in my signature and I can get that to the right team.

 

[morrow95]

Thanks I will look into that. As far as the log file goes... I basically have this group of lines repeated throughout the entire log. It started happening over and over earlier this year and the mysqld.log file has grown to about 50gb. Had I not been looking in the file system I wouldn't have known. Make anything out of these? Again, this is on the vm which doesn't even run a db... we have mariadb running on a seperate vm setup as a profile in whm. Other strange thing is I see below 'InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M', but on our actual db server we have 'innodb_buffer_pool_size = 4G' in the cnf file.

'InnoDB: 5.7.40 started; log sequence number 153056497'... if that means the version we are actually running MariaDB 10.5.17 on our db server.

Is there a chance that some old version of mariadb or mysql is running on this vm that we don't even use? We have a separate vm specifically for mariadb.

2022-12-22T21:19:10.532433Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 10000)
2022-12-22T21:19:10.652708Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-12-22T21:19:10.653028Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.40) starting as process 13438 ...
2022-12-22T21:19:10.656476Z 0 [Note] InnoDB: PUNCH HOLE support available
2022-12-22T21:19:10.656511Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-12-22T21:19:10.656515Z 0 [Note] InnoDB: Uses event mutexes
2022-12-22T21:19:10.656519Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2022-12-22T21:19:10.656525Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2022-12-22T21:19:10.656529Z 0 [Note] InnoDB: Using Linux native AIO
2022-12-22T21:19:10.656835Z 0 [Note] InnoDB: Number of pools: 1
2022-12-22T21:19:10.656969Z 0 [Note] InnoDB: Using CPU crc32 instructions
2022-12-22T21:19:10.658723Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2022-12-22T21:19:10.667347Z 0 [Note] InnoDB: Completed initialization of buffer pool
2022-12-22T21:19:10.669514Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2022-12-22T21:19:10.681402Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2022-12-22T21:19:10.692697Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-12-22T21:19:10.692855Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-12-22T21:19:10.708642Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-12-22T21:19:10.709584Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2022-12-22T21:19:10.709598Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2022-12-22T21:19:10.710062Z 0 [Note] InnoDB: Waiting for purge to start
2022-12-22T21:19:10.760254Z 0 [Note] InnoDB: 5.7.40 started; log sequence number 153056497
2022-12-22T21:19:10.760503Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2022-12-22T21:19:10.760722Z 0 [Note] Plugin 'FEDERATED' is disabled.
2022-12-22T21:19:10.760770Z 0 [Note] InnoDB: Buffer pool(s) load completed at 221222 16:19:10
2022-12-22T21:19:10.761258Z 0 [ERROR] unknown variable 'unix_socket=OFF'
2022-12-22T21:19:10.761271Z 0 [ERROR] Aborting

2022-12-22T21:19:10.761302Z 0 [Note] Binlog end
2022-12-22T21:19:10.761363Z 0 [Note] Shutting down plugin 'ngram'
2022-12-22T21:19:10.761369Z 0 [Note] Shutting down plugin 'partition'
2022-12-22T21:19:10.761372Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2022-12-22T21:19:10.761375Z 0 [Note] Shutting down plugin 'ARCHIVE'
2022-12-22T21:19:10.761378Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2022-12-22T21:19:10.761451Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2022-12-22T21:19:10.761456Z 0 [Note] Shutting down plugin 'MyISAM'
2022-12-22T21:19:10.761470Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2022-12-22T21:19:10.761473Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2022-12-22T21:19:10.761475Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2022-12-22T21:19:10.761478Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2022-12-22T21:19:10.761480Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2022-12-22T21:19:10.761482Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2022-12-22T21:19:10.761485Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2022-12-22T21:19:10.761487Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2022-12-22T21:19:10.761489Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2022-12-22T21:19:10.761492Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2022-12-22T21:19:10.761494Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2022-12-22T21:19:10.761496Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2022-12-22T21:19:10.761498Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2022-12-22T21:19:10.761501Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2022-12-22T21:19:10.761503Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2022-12-22T21:19:10.761505Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2022-12-22T21:19:10.761508Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2022-12-22T21:19:10.761510Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2022-12-22T21:19:10.761512Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2022-12-22T21:19:10.761515Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2022-12-22T21:19:10.761517Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2022-12-22T21:19:10.761519Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2022-12-22T21:19:10.761521Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2022-12-22T21:19:10.761524Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2022-12-22T21:19:10.761526Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2022-12-22T21:19:10.761528Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2022-12-22T21:19:10.761531Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2022-12-22T21:19:10.761533Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2022-12-22T21:19:10.761535Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2022-12-22T21:19:10.761537Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2022-12-22T21:19:10.761540Z 0 [Note] Shutting down plugin 'InnoDB'
2022-12-22T21:19:10.761588Z 0 [Note] InnoDB: FTS optimize thread exiting.
2022-12-22T21:19:10.761688Z 0 [Note] InnoDB: Starting shutdown...
2022-12-22T21:19:10.861846Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2022-12-22T21:19:10.862176Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 221222 16:19:10
2022-12-22T21:19:12.368765Z 0 [Note] InnoDB: Shutdown completed; log sequence number 153056516
2022-12-22T21:19:12.371478Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2022-12-22T21:19:12.371495Z 0 [Note] Shutting down plugin 'MEMORY'
2022-12-22T21:19:12.371502Z 0 [Note] Shutting down plugin 'CSV'
2022-12-22T21:19:12.371507Z 0 [Note] Shutting down plugin 'sha256_password'
2022-12-22T21:19:12.371509Z 0 [Note] Shutting down plugin 'mysql_native_password'
2022-12-22T21:19:12.371650Z 0 [Note] Shutting down plugin 'binlog'
2022-12-22T21:19:12.371846Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

 

[cPRex]

This looks like a bad configuration in /etc/my.cnf. Can you post a copy of that configuration file here? They usually aren't very long - maybe 20-30 lines.

 

[morrow95]

This looks like a bad configuration in /etc/my.cnf. Can you post a copy of that configuration file here? They usually aren't very long - maybe 20-30 lines.

This is from the vm that doesn't run our db stuff. I had to attach it as a file as it won't let me post.

 

[cPRex]

I'd check this server first, and confirm it's writing to that log file based on the path in there.

 

[morrow95]

I'd check this server first, and confirm it's writing to that log file based on the path in there.

Yes, that is the log file that was mentioned in it. I tried attaching it as a file, but the forum isn't allowing it and I can't include it in a code block either. I actually commented out that part of the file yesterday to stop the log file from writing :

#changed 10-27-22 to stop huge log files
#log-error=(this is the location of the large log file I originally posted about, but the system won't allow me to list it)

 

[cPRex]

Alright - what about the rest of the settings in the file?

 

[morrow95]

Alright - what about the rest of the settings in the file?

The forum won't allow me to post it in a code block or as an attached file - blocks it. I'm trying with a screenshot instead this time.

 

[cPRex]

Well thee you go - you have the open_files_limit set to 10000, when it seems to be capped at 5000.

Do you happen to have a /usr/my.cnf or /usr/.my.cnf file in place as well with a different limit?

 

[morrow95]

No, have neither of those files.

Okay, but I still don't understand how anything here is 'working' or means anything. We use an entirely different vm for databases setup as a remote db profile in WHM.

I attached a few pictures including one where it shows the 'ib' files on this vm (which shouldn't be doing anything related to db work) have current timestamps like it is being written to. How is that possible when we configured WHM to use a remote db server? We have MariaDB installed on another vm, all our databases are there and working, etc.

Does WHM still use some local/default mysql installation for something (stats or something like that) even though we have this setup to use a remote db server?

 

[cPRex]

What happens when you run "mysqladmin proc status" on the system? It's clear *something* is running locally still, so you'll just need to track down what, and that command will show you the MySQL activity on the machine in real-time.

 

[morrow95]

What happens when you run "mysqladmin proc status" on the system? It's clear *something* is running locally still, so you'll just need to track down what, and that command will show you the MySQL activity on the machine in real-time.

The response shows the username setup for the remote db profile. The one that allows whm to connect and use the remote db on the other vm.

+------------+------------+--------------------+----+---------+------+----------+------------------+----------+
| Id         | User       | Host               | db | Command | Time | State    | Info             | Progress |
+------------+------------+--------------------+----+---------+------+----------+------------------+----------+
| 1161483186 | whm_user   | ---.---.--.-:36596 |    | Query   | 0    | starting | show processlist | 0.000    |
+------------+------------+--------------------+----+---------+------+----------+------------------+----------+
Uptime: 9896774  Threads: 256  Questions: 7322065485  Slow queries: 17781  Opens: 2018  Open tables: 1687  Queries per second avg: 739.843

So, that does kind of make sense, but it doesn't make sense why the cnf files on this vm would affect the settings on the vm where mariadb is installed and used? I do not have open_files_limit set in any of the cnf files on that vm.

 

[cPRex]

At this point I'm not sure what the root cause may be. It would be best to open a ticket with our support team so we can take a look at the machine directly.

 

[morrow95]

Strange. I can ssh into both vms and commands like 'SHOW DATABASES;' correctly show the databases on the vm that has mariadb and our databases - correct sizes and everything. I assume that all has to do with the fact that whm is configured to use that remote vm with the profile we setup in it though. To be honest I didn't know I could anything with the db from cli from the vm that doesn't have them - I've always ssh'ed into the vm with them when I need to do anything from cli. I also checked the settings returned from each :

vm with whm, apache, etc :

mysql> show variables like 'open_%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 32582 |
+------------------+-------+
1 row in set (0.01 sec)

vm with mariadb and dbs :

MariaDB [(none)]> show variables like 'open_%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 32582 |
+------------------+-------+
1 row in set (0.001 sec)

which contradicts this 'open_files_limit' setting problem in the cnf file on the vm with whm.

 

[morrow95]

I finally got around to investigating this more and wanted to update this thread. In my case, mysql 5.7.41 (what was installed when this vm was first setup) was starting up on my vm with WHM installed even though I setup a remote db profile in WHM so all db related activity was done on a completely different vm with mariadb 10.6 installed. Odd to say the least. You would think when a remote db profile is setup it would turn off any mysql/mariadb on the server with WHM on it since you are handing over all db stuff to a remote server/vm - right? I guess it doesn't.

Anyways, I stopped the service then used `systemctl disable mysqld.service` to prevent it from starting back up on restarts, cleared that huge mysqld.log, and cleared out the entries in the my.cnf file for the mysql that shouldn't have been running in the first place. So far so good and everything is working fine and haven't noticed anything strange.

 

[cPRex]

I'm glad you found a good solution!