- Mysql binary log is a special kind of logging facility provided by MySql by which we can record the log of the database changes statement along with the moment when statement get executed.
- We can log all insert, update and delete statement of the database in the mysql binary log.
What is MySQL binary log?
As we have already discus above that this is the special kind of log provided by mysql which record the log of database changes.
It store data in binary format, that is why name of the log is binary log. It store data in binary format because it is being used by mysqlbinlog utility to restore data from binary log. Basically it record the insert , update and delete query executed on the database in the binary log format along with the time. This is the reason it is being used to recover the database.
Why MySQL binary log?
- To get incremental backup of the database: As mysql binary logging record all changes happen to the specific database table, it is used to get incremental backup of the database. Because if we have full backup of till 10th November and we have set up the binary log then we can get the further changes made in the database. So it is very good option to record the database changes.
- To do the database recovery in case of data loss: As it store the change made on the database, we can do the database recovery by mysql binary log.
- To configure mysql database replication: We can use mysql binary log to replicate database on the remote server.
Setup and Use of Binary log:
- Stop the MySQL services:
# service mysqld stop
- Edit the mysql configuration files (/etc/my.cnf) and add below lines:
log-bin = /var/lib/mysql/<application-name>-mysql-bin.log
expire_logs_days = 2
# binary logging format – mixed recommended
The logs will go to the mysql data directory and will have the mysql-bin prefix if we use “log-bin=mysql-bin” otherwise we can mentioned the location as we have done in above file.”expire_logs_days” system variable to expire binary log files automatically after a given number of days.
- Start the MySQL service
# service mysqld start
- 80If you have enabled binary logging for the point-in-time recovery (or using replication in your environment) option and forgot to purge it then it may eat up memory in no time, so to “purge” binary logs from production server follow the steps(depending upon your environment) List the current binary logs…
- 63To learn more about what is binary log and how to setup Click here MySQL binary log : mysqlbinlog utility mysqldump – MySQL Database Backup and restore program Purge Binary log MySQL Master-Master-Slave-Slave Replication Database Recover from MySQL binary log: Binary logs store all the queries (in STATEMENT format) or…
- 50To enable the query log, put this in /etc/my.cnf in the [mysqld] section log = /path/to/query.log #works for mysql < 5.1.29 Also, to enable it from MySQL console SET general_log = 1; Remember that this logfile can grow very big on a busy server. update: With mysql 5.1.29+ , the log option is deprecated. To specify…
- 47What is database? Database is an organized collection of information about an entity having controlled redundancy and serves multiple applications. DBMS (database management system) is an application software that is developed to create and manipulate the data in database. A query language can easily access a data in a database.…