mysql cluster master-slave replication

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   .

set binlog_format=STATEMENT;

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.

flush logs;

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    , 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

[mysqld] server-id=401326

3.2. Restart and log in to MySQL to configure the slave server

systemctl restart mysqld
change master to master_host='', 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  )  .