1. Binlog log, if there are multiple databases, is the binlog log divided into multiple logs?
2. Where is the path of binlog log storage?
3. Which binlog mode do you use?
1.statement level mode 2.rowlevel mode 3.mixed mode
1. Turn on binglog log
1.1. Modify my.cnf configuration file
Add in the [mysqld] section:
#binlog刷盘策略 sync_binlog=1 #需要备份的数据库 binlog-do-db=hello #不需要备份的数据库 binlog-ignore-db=mysql #启动二进制文件 log-bin=mysql-bin #服务器ID server-id=132
Sync_binlog parameters: 0 : The storage engine does not flush the binlog to the disk, but the file system of the operating system controls the cache flush. 1 : Every time a transaction is submitted, the storage engine calls the sync operation of the file system to refresh the cache. This method is the safest but has lower performance. n : When the submitted log group = n , the storage engine invokes the sync operation of the file system to refresh the cache. sync_binlog=0 or sync_binlog is greater than 1 , the transaction has been committed, but has not been synchronized to disk. Therefore, in the event of a power failure or operating system crash, it is possible that the server has promised not to synchronize some transactions to the binary log. Therefore it is impossible to perform routine procedures to recover these transactions, they will lose the binary log.
1.2. Adjust the binlog log mode
View the log mode of binlog :
show variables like 'binlog_format';
Adjust the log mode of binlog : Three formats of binlog : STATEMENT , ROW , MIXED .
At this time, you need to restart the database to use the configuration file to take effect.
systemctl restart mysqld
1.3. View bin log and relay log logs
Because the binlog log file: mysql-bin.000005 is a binary file, it cannot be opened with vi, etc. At this time, mysql 's built-in mysqlbinlog tool is needed to decode, execute: mysqlbinlog mysql - bin.000005 can convert the binary file into a Read the sql statement.
mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.000058 > binlog
1.4. Use the command to view binlog
show master logs;
Use the show binlog events command to display event information in the log in the form of a list. The format of the show binlog events command:
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
Description: ( . 1 ) the IN 'log_name' : to be queried binlog file name (if this parameter is omitted, the default specified first binlog text element); ( 2 ) FROM pos : Specify which pos starting point to start from (if this parameter is omitted, the calculation starts from the first pos point of the entire file ); ( 3 ) LIMIT [ offset ]: offset (default is 0 ); ( 4 ) row_count : Query the total number of entries (if omitted, all rows will be displayed).
show binlog events in 'mysql-bin.000001';
Switch binlog file: a new log file will be generated.
2. Master-slave replication based on binlog
2.1. Turn off the firewall of the master and slave machines
systemctl stop iptables（需要安装iptables服务） systemctl stop firewalld（默认） systemctl disable firewalld.service（设置开启不启动）
2.2. Main server configuration
To check whether binlog is turned on, you can use the command:
show variables like 'log_bin%';
If log_bin is OFF, it means it is not turned on.
2.3. The host grants backup permissions to the slave
Note: first log in to the MySQL command client
GRANT REPLICATION SLAVE ON *.* TO '从机MySQL用户名'@'从机IP' identified by '从机MySQL密码';
GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' identified by 'root';
Precautions: Generally, the root account is not used . "%" means that all clients can connect. As long as the account and password are correct, the specific client IP can be used here instead, such as 192.168.145.226 , to strengthen security. mysql5.7 has requirements for the strength of the password, it must be composed of letters + numbers + symbols, you can use the following methods to adjust the password strength. Set the minimum digits of the password length
set global validate_password_length=4;
Set password strength level
set global validate_password_policy=0;
The validate_password_policy has the following values:
Refresh permissions :
2.4. Query the status of the master
show master status;
3. Configure from the server
3.1. Modify the my.conf file
3.2. Restart and log in to MySQL to configure the slave server
systemctl restart mysqld
change master to master_host='10.0.200.17', master_port=3306, master_user='root', master_password='root', master_log_file='mysql-bin.000002', master_log_pos=589, MASTER_AUTO_POSITION=0;
note: Do not break in the middle of the statement, master_port is the mysql server port number ( without quotation marks ) , master_user is the database account that performs the synchronization operation, "410" without single quotation marks ( here 410 is the value of position seen in show master status , here the mysql- bin.000001 is the file corresponding value ) .
3.3. Start the copy function from the server
start slave; stop slave; reset slave; show slave status;
3.4. Check the status of the replication function from the server
show slave status \G；
……………………( Parts omitted ) Slave_IO_Running: Yes // This state must be YES Slave_SQL_Running: Yes // This state must be YES ……………………( Parts omitted )
Note: Slave_IO and Slave_SQL process must run properly, that is YES state otherwise is wrong state ( eg: One of NO are belong error ) .