Skip to main content

Unable to open the log file (mysqld)

While starting the server you may find the below errors sometimes, it's because of unintentionally deleting the MySQL Binary Log files. When starting mysql if the server unable to find the mysql-bin-log files then below errors will arise.

170418 12:15:19 InnoDB: 5.5.30 started; log sequence number 32347593422
/usr/sbin/mysqld: File './mysql-bin.000023' not found (Errcode: 2)
170418 12:15:19 [ERROR] Failed to open log (file './mysql-bin.000023', errno 2)
170418 12:15:19 [ERROR] Could not open log file
170418 12:15:19 [ERROR] Can't init tc log
170418 12:15:19 [ERROR] Aborting

170418 12:15:19  InnoDB: Starting shutdown...
170418 12:15:20  InnoDB: Shutdown completed; log sequence number 32347593422
170418 12:15:20 [Note] /usr/sbin/mysqld: Shutdown complete
Do we have a solution to get rid of this and start MySQL again?

Well, we have two types of workarounds to start MySQL again.

1. Deleting the missed files in binlog .index file
2. Disabling the binlog logging on my.cnf file

1. Deleting the missed files in binlog .index file

$ vi mysql-bin.index


mysql-bin.000016
mysql-bin.000017
mysql-bin.000018
mysql-bin.000019
mysql-bin.000020
mysql-bin.000021
mysql-bin.000022
mysql-bin.000023
Delete the mysql-bin.000023 from the above file and restart the mysqld.

2. Disabling the binlog logging on my.cnf file 

 In this step, open my.cnf and set the value of set_log_bin to 0 and purge all the existing binary logs

$ vi my.cnf

sql_log_bin = 0
Save the above file and restart the mysql service. Once after login to the server purge the bin logs.

$ mysql -u root -p -A -v
Enter Password:

mysql> PURGE BINARY LOGS;
This will delete the existing logs from the server.
After this step, Enable the Binary log again to write the changes to binlog files.

To do that login to MySQL again and do below.

$ mysql -u root -p -A -v
Enter Password:

mysql> SET sql_log_bin = 1;
Once after finishing this, edit the my.cnf file and set the sql_log_bin from 0 to 1and restart the service.

Note: The second procedure is not the recommended way to apply in Production.
Labels:

Comments

Post a Comment

Popular posts from this blog

Warning: Using a password on the command line interface can be insecure

We usually use MySQL login information on shell scripts to load some data into database or to fetch some monitoring information. To login to the database will write login information on Shell Scripts as below #!/bin/sh username = someusername password = somepassword $ mysql -u$username -p$password -A -v -S By doing that so, we will hit a warning in stdout saying: Warning: Using a password on the command line interface can be insecure To avoid the warning in shell script, we need to so some workaround like below: 1. Create a hidden file configuration file > touch /tmp/.mysqllogin.cnf 2. Enter your database credential information on the hidden file: > vi /tmp/.mysqllogin.cnf [client] user=someusername password=somepassword 3. Save the file and try to login to mysql using  --defaults-extra-file  keyword like $ mysql --defaults-extra-file=/tmp/.mysqllogin.cnf -A -v By using this workaround you can avoid the warning.
Post a Comment
Read more

Monitoring MySQL with Percona Monitoring and Management

In this blog post we will see how to monitor MySQL databases using the open source monitoring tool provided by Percona, referred as Percona Monitoring and Management (PMM). About PMM: Percona Monitoring and Management (PMM) is an open-source platform for managing and monitoring MySQL and MongoDB performance. It is developed by Percona in collaboration with experts in the field of managed database services, support and consulting. PMM is a free and open-source solution that you can run in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible. Basic Requirements: 1. Need at least one Linux server to install PMM Client and PMM Server. 2. Need Operating System - root user credentials 3. Default port's(80) have to be open if using to or more servers. 4. A MySQL user is required to capture the Queries in the Database. 5. A Docker se
Post a Comment
Read more