If you are running a website on shared hosting, you often hear from hosting providers that the website’s database is overutilizing. They would ask you to purge MySQL binlogs and even take actions towards this issue, such as suspending hosting accounts until you remove MySQL binlogs.
In today’s post we are going to take a look at what are MySQL binlogs, why they are problems and how to purge MySQL binlogs so that you always bypass the suspension list!
What is MySQL?
There are two types of databases, one is relational, and the other is non-relational. Relational database is the one which stores data in tables (products, orders, posts) that are interlinked and have relationships. It uses SQL, a Structured Query Language, to manage it.
One of the most popular relational database system management system(RDBMS) is MySQL, and it is an open source one. Many well known Content Management Systems (CMS) like WordPress and so on uses MySQL as database. Non CMS based websites can also use MySQL database, that is basically dependent on development choice.
What is MySQL binlogs?
Binlogs is the short word for binary logging or binary logs in SQL. These binary logs hold record of all recent modification to the database. When these logs are generating in an insane amount, that is when it starts to impact the parent server where you are hosting the website.
Why binlogs is problem for shared hosting?
You should not be surprised to know that shared hosting is a type of hosting whose parent server is shared with other accounts. On an average, in one server, hosting companies may host 500 to 600 shared accounts.
When some particular account’s database starts to generate binary logs in huge amount, then that is a subtle issue. Hosting providers map the issue and its possible affect or existing effect on server.
Due to these websites hosted on shared environment, hosting companies audits these accounts on regular basis. They may alert users when they have detected such instances, which often lead to hosting suspension.
It’s an obvious thought that hosting companies would be managing the parent server and may take necessary actions based upon their policies. Hence, one should always read the hosting / product policies before opting out for it.
Steps to purge binlogs-
We knew what are binary logs and how they affect. Now, lets checkout how to go about purging them.
1)Navigate to PHPMyAdmin of your cpanel-
2)Select the DB name and click on Console at bottom-
3)Adjust the console and type command-
SHOW VARIABLES LIKE ‘log_bin%’;
Other command : SHOW BINARY LOGS;
For the second command, you may encounter errors like #1227 access denied, not having privileges from server to perform operation. We will discuss it later in the post, let’s continue with the first command. After typing the command, click on Ctrl + Enter to run the query.
4) Output shows like as shown here-
In this case, the bin logs are off
If the first row shows off means your bin logs are off and from here you can reach back to hosting providers staging the bin logs are off already. If you see it ON, please continue further. If Bin logs are on, they would look like this
5) Purge command
to purge logs until a certain log file, use command
PURGE BINARY LOGS TO ‘log name goes here’;
This command will remove all logs before the shared log name.
PURGE BINARY LOGS TO ‘mysql-bin.000020’;
mysql-bin.000020 is the example log, whereby above commands all logs before mysql-bin.000020 will be purged. You can purge logs by date as well, to purge them by date follow below command.
PURGE BINARY LOGS BEFORE ‘YYYY-MM-DD HH:MM:SS’;
Example
PURGE BINARY LOGS BEFORE ‘2022-05-19 10:30:30’;
It will purge all logs before the date and time mentioned.
Access denied issues-
When attempting to try one of those commands to list or purge will give you errors such as #1227 Access Denied and would talk about not having SUPER privileges for the operation.
This would be due to the shared hosting account you are on has not provided the rights to perform such operations. The reason for this is that such operations may impact the server if performed incorrectly.
In that case, you would be required to export your database and perform these tasks in localhost. When you have purged all bin logs, you can head back to the hosting section and re-import the database.
The very next step after re-importing the database is that you should reach out to your hosting provider and inform them about removal of bin logs in order for them to reinstate hosting plan.
Enable Disable mysql binlogs-
You can opt for disabling bin logs and vice versa, to do this you should command
SET SQL_LOG_BIN=0;
For Enabling it
SET SQL_LOG_BIN=1;
Conclusion
We have seen how to purge binary logs, binary logs are not problems, but in shared hosting they can be. You may want to opt for a server where you have all rights to perform tasks as per will if you do not wish to have hassles on shared hosting. If you have ever encountered this problem with your site, please comment below the hosting provider of it. If you have any concerns about this method or want to share feedback, do contact us here. Bye bye 🙂
