If SqlBak is installed on your server and a connection is established to your MySQL Server, then you can enable binary logs via one command: Show sudo sqlbak --configure-mysql --connection-id=1 If you have set several connections, then in the –connection-id parameter, you have to specify the connection ID to the database for which you want to enable binary logs. To find the connection ID, use the following command: In this article, we are going to learn about the concept of MySQL Binary Logs and their architecture. I am covering the following details in this article:
An overview of MySQL binary logsThe concept of MySQL binary logs and SQL Server transaction logs are the same. The binary logs contain information about the data changes that occurred on the MySQL instance. The binary logs contain all SQL statements, which update the data (INSERT, UPDATE DELETE) and the DDL statements (Create database object, drop database objects, truncate table) within the database. It contains the time taken to execute a statement that creates and updates the data. The SQL statement that is used to update the data are stored in the EVENT form. The EVENTs describe the global changes that occurred on the MySQL Server during any operations. It helps us to reproduce the change of the global state that had happened on the MySQL Server. The purpose of the MySQL binary logs is the following:
Following are the types of the binary logging:
The MySQL Server logs contain a set of binary logs and an index file. The binary log file contains:
The naming convention of the log file is hostname-bin.NNNNNN. Here NNNN is a sequence. The hostname of my workstation is NISARG-PC; therefore, the naming convention is: Nisarg-pc-bin.000001 If you have set up master-slave replication, then the naming convention of the relay log file will be as following: Nisarg-pc-relay.000001 Let us see some important commands to manage the binary logs. For the demonstration, I have installed MySQL Server 8.0 on my workstation and the operating system that I am using is windows 10. Enabling the binary logsMySQL binary logs can be enabled or disabled by adding or removing the log-bin configuration option. The log-bin parameter is in the configuration files named my.ini. When we install MySQL Server, the configuration file is created in C:\ProgramData\MySQL\MySQL Server 8.0 directory. The directory is hidden, therefore, to view the ProgramData folder in windows explorer you must enable the ‘Show hidden files’ option from the Folder Option. First, let us check the bin-log parameter’s value by querying the metadata table namedinformation_schema.global_variables. Below is the query: mysql> select * from information_schema.global_variables; Output: ERROR 1109 (42S02): Unknown table ‘GLOBAL_VARIABLES’ in information_schema We receive the above error because the information_schema tables are deprecated and removed in MySQL 8.0; therefore, instead of using the system table, we can check the status of the parameter by running the following query mysql> show global variables like ‘log_bin’; Screenshot of query output: As you can see in the above image, the value of the log-bin parameter is OFF, which indicates that binary logging is disabled. Now, to enable binary logging, copy and paste the following line in the my.ini configuration file. log-bin=”[HostName]-bin” In the above lines, replace the [hostname] with your desired name. Restart the MySQL Server by executing the following command in PowerShell. PS C:\WINDOWS\system32> Restart-Service MySQL80 Check the status of binary logging by executing the following query mysql> show global variables like ‘log_bin’; As you can see, the value of the log-bin parameter is ON, which indicates that binary logging is enabled. Disabling binary loggingTo disable the binary logging, add the following lines in the my.ini file. [mysqld] Restart the MySQL services. PS C:\WINDOWS\system32> Restart-Service MySQL80 Run the following query in the MySQL command line: mysql> show global variables like ‘log_bin’; View the binary log locationTo view the default location of the binary location, execute the following query: mysql> show global variables like ‘%log_bin%’; The output of the query: To view the list of the binary logs, run the below command in MySQL command line utility: mysql> show binary logs; Change the default location of the Binary LogsThe MySQL binary logs and index files are saved in the C:\ProgramData\MySQL\MySQL Server 8.0 directory. We can change the default location of the binary logs. To do that, we must follow the below steps. Step 1: Shutdown the MySQL serviceTo shut down the MySQL services, Open control panel open Administrative tools Open Services Locate the MySQL80 from the list of services Right-click on MySQL80 Click on Stop. Step 2: Change the value of log-bin parametersNow, change the value of the log-bin parameter. To do that, open the configuration file named my.ini. The file is in C:\ProgramData\MySQL\MySQL Server 8.0 directory. We want to move it to the D:\Binarylogs. Replace the value of the log-bin parameter, as shown below:
Screenshot of the configuration file: Save the configuration file. Step 3: Copy the binary logs to the new directoryCopy all the binary logs from the C:\ProgramData\MySQL\MySQL Server 8.0 directory to the D:\Binarylogs directory. Step 4: Start the MySQL servicesTo start the MySQL services, Open control panel open Administrative tools Open Services Locate the MySQL80 from the list of services Right-click on MySQL80 Click on Start. Now, let us verify that the location of the binary logs have been updated or not. To do that, execute the following command in the MySQL command-line utility. As you can see in the above image, the location of the binary logs has been changed. SummaryIn this article, we learned about MySQL binary logs. We have learned the following topics.
In the next article, we are going to learn how we can open and interpret the binary log events. To view the binary logs, we can use mysqlbinlog utility. We will be learning more about it in my next articles. How do I view MySQL bin logs?To retrieve a list of all the BinLogs present in your system, make use of the following command: mysql> SHOW BINARY LOGS; This command will display a list of all binary logs present in the system only when the binary log is enabled otherwise, it gives an error.
How to enable MySQL bin logs?Enabling the binary logs
MySQL binary logs can be enabled or disabled by adding or removing the log-bin configuration option. The log-bin parameter is in the configuration files named my. ini. When we install MySQL Server, the configuration file is created in C:\ProgramData\MySQL\MySQL Server 8.0 directory.
What is log bin in MySQL?The --log-bin option value is the base name for the log sequence. The server creates binary log files in sequence by adding a numeric suffix to the base name. If you do not supply the --log-bin option, MySQL uses binlog as the default base name for the binary log files.
How to set Log_bin_trust_function_creators in MySQL?mysql> SET GLOBAL log_bin_trust_function_creators = 1; You can also set this variable by using the --log-bin-trust-function-creators option when starting the server. If binary logging is not enabled, log_bin_trust_function_creators does not apply and SUPER is not required for function creation.
|