Mysql Binary Log Configuration and Setup Tutorial. Binary log in mysql is one of the very useful feature. Basically binary log contains all sql query which change database. For example insert , update, delete data and alter and drop table. In my post Mysql Binary Log Tutorial I will explain about the binary log configuration, its uses and all setting attribut of mysql binary log like location , format, expiration of log etc. What is Mysql Binary Log: Mysql binary log is a special kind of log which contains information about your database changes along with the moment the change was being made. Binary log may contains the query execution time. It never log select statement or any statement which is not making any kind of database changes. Binary log generated in some system hex format. You can process binary log using mysql utility mysqlbinlog. Using binlog you can convert binary log in like sql file. Mysql community named this log as binary log because it generate individual number file which contains event(or the things which make change in the db). Binary Logging can be configured for two reason in mysql. Enable binary logging. A binary log cache is allocated for each client if the. Current my.cnf configuration. How to configure or setup and enable binary log for. What is Mysql Binary Log: Mysql binary log is a special kind of. So please set your mysql binary log. If you want to setup incremental backup of your database. You can read my article of. Master send event in the binary log on the master server and slave read the log . Base name is optional field. If you will not provide the. Setup replication with MySQL 5.6 Server on CentOS 6/Redhat. If you are running your My. SQL in XAMPP or you are running any pre- compiled Mysql you can configure or enable. In my case I have configured in mysql of my XAMPP server. Following is the step to configure binary log in My. SQL server of XAMPP. Go to your XAMPP installation directory location and open mysql/bin/my. In my case I have created it in. ![]() MySQL Backup and Restore; Contents. Configuring MariaDB with my.cnf. Home; Open Questions; MariaDB; MariaDB Enterprise; MariaDB MaxScale. MariaDB Binary Packages Upgrading MariaDB. In my case binary log is generated in folder location C: /xampp/mysql/data/binlog. File Name are prefix with bin- log. This can be a security enhancement,# if all processes that need to connect to mysqld run on the same host.# All interaction with mysqld must be made via Unix sockets or named pipes.# Note that using this option without enabling named pipes on Windows# (via the . If not then you need to troubleshoot for the reason. Above method is not only the exclusive for setting up or enabling the binary log in XAMPP server but also you can set binary log using above method on any pre- compiled distribution or if you want to setup bin log using your my. The thing you need to know is location of your my. It will work for Linux and Windows distribution. Once you did the above step then you have passed first step only. Because by above method you are setting up or enabling binary logging of your whole database server. There are lots of option available in the binary logging. For example rotation of your binary log , adding and removing database, expiration of binary log. I will explain all these point in the next section of this tutorial. Setting up Binary log index file name. If you have carefully analyzed my above folder location of the binary logging. You can found that it has two file(bin- log. After roration or expiration or size limit. It is stores information about Mysql binary logging. ![]() You can open this index file in notepad and you can found that it has name with location of your all active(for active and expired I have explained in the later section of my tutorial) binary logs. You can set location of your mysql binary log index file. By default mysql creates it in the same directory where your binary logs getting stored. However if you want to change the location of your index file you can change it by setting option. You can set index by starting your mysql with. Following is the step by step approach to set index filename of binary log. Locate and open your mysql configuration file my. Add following linelog- bin- index=. Your file will be generated. In my case it has generated with name abc. How to add or remove database from. Then you are creating Mysql binary log of all database. You can add and remove database by using option variable. You can use both variable either by command line on your configuration file format. If you have not used option binlog- do- db then by default all database will be included in your binary log. But if you will specify any database name with binlog- do- db then the only specified. So for example if you want to create binary log of only 2 database(test. And restart your mysql server. Now only test. 1 and test. Rest of the database is not participated in the binary logging from your mysql server. Like binlog- do- db to add database in your mysql binary log you have option to remove database from your mysql binary log. If you want particular database to not participate in the binary log then you can use binlog- ignore- db option. You cant set this option by both command line and mysql configuration file. In general practice this option rarely required. But if you are by default enabled all db binary logging so sometime you need this option if you specifically want to remove any database from your binary logging. Both option will impact your slave replication. So if you have configured slave server for your database then first analyze the impact on your save if you will ignore or add. So you need to rotate the binary log. If you will rotate your binary log. You can rotate your mysql binary log by specifying option. If specified limit will be exceeded then binary log will be rotated. Or in other term it will generate new file for the binary logging. So if your Mysql binary log is at size 0. GB and your table is being updated then writing into 0. GB file take time. So your query will take time. So please set your mysql binary log rotation. So after first working day of every month your binary log are not useful(in case of master slave replication it may be required). So ideally you should delete your binary log after specific time of interval. In mysql we term it expire your log instead deleting. You can expired your Mysql binary log using setting expire. So here you can specify after how many day your log will be expired. If you have any question regarding binary log setup please post in comment section.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |