log_bin variable must be set to ON.binlog_format variable must be set to ROW.gtid_mode parameter can only be set to ON or OFF. It is recommended to set gtid_mode to ON. Setting it to OFF generates an alarm, while setting it to ON_PERMISSIVE or OFF_PERMISSIVE causes an error.server_id parameter must be manually set to a value other than 0.do_db and ignore_db.log_slave_updates variable must be set to ON.log_bin controls the binlog option. You need to enable the binlog feature to record all database table structures and data change logs. my.cnf configuration file for the source database by referring to the following content.my.cnf configuration file is /etc/my.cnf. The actual path in your environment may vary.log_bin = MYSQL_BINbinlog_format = ROWserver_id = 2 // It is recommended to set this parameter to an integer greater than 1. This is only an example.binlog_row_image = FULLexpire_logs_days = 3 // Modify the retention period for binlogs. It is recommended to set this to 3 days or more.
[$Mysql_Dir]/bin/mysqladmin -u root -p shutdown[$Mysql_Dir]/bin/safe_mysqld &
show variables like '%log_bin%';
mysql> show variables like '%log_bin%';+------------------+-------+| Variable_name | Value |+------------------+-------+| log_bin | ON |+------------------+-------+1 row in set (0.00 sec)
binlog_format refers to the recording modes for binlogs. The following three modes are available:STATEMENT: Every SQL statement that modifies data is recorded in the master's binlog. During replication, the replica executes the same SQL statements that were originally executed on the master. This mode can reduce the volume of binlog data. However, it causes the replica to incorrectly replicate certain specific functions.ROW: The binlog will log the modification of each data row, and the same data is modified on the replica. This mode guarantees the correct master-to-replica replication but increases the binlog size.MIXED: This mode is a combination of the previous two. MySQL determines the appropriate logging format for each specific SQL statement executed, selecting one from STATEMENT and ROW.binlog_format parameter must be set to ROW. If such an error occurs, see the following instructions to fix it.binlog_format.set global binlog_format = ROW;
show variables like '%binlog_format%';
mysql> show variables like '%binlog_format%';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW |+---------------+-------+1 row in set (0.00 sec)
gtid_mode to ON or OFF. It is recommended to set gtid_mode to ON. Otherwise, an alarm is generated during verification.gtid_mode are as follows. When modifying the value of gtid_mode, you must change it step by step through the following four values. For example, to change it from OFF to ON, follow the modification sequence for gtid_mode: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON.OFF: All new transactions on the master and all transactions on the replica must be anonymous transactions.OFF_PERMISSIVE: New transactions on the master are anonymous transactions. However, transactions on the replica can be a mixture of anonymous and GTID transactions or anonymous transactions, but they cannot be only GTID transactions.ON_PERMISSIVE: New transactions on the master are GTID transactions. Transactions on the replica can be either anonymous or GTID transactions.ON: New transactions on the master and transactions on the replica must be GTID transactions.gtid_mode = OFF_PERMISSIVE on both sides of the master-replica replication structure.
For MySQL versions earlier than 5.7.6, you need to modify the my.cnf configuration file and restart the database for the setting to take effect. For MySQL 5.7.6 and later, you can modify it through a global command without restarting the database, but you need to reset all business connections.set global gtid_mode = OFF_PERMISSIVE;
gtid_mode = ON_PERMISSIVE on both sides of the master-replica replication structure.set global gtid_mode = ON_PERMISSIVE;
0 indicates that they are consumed.show variables like '%ONGOING_ANONYMOUS_TRANSACTION_COUNT%';
mysql> show variables like '%ONGOING_ANONYMOUS_TRANSACTION_COUNT%';+-------------------------------------+-------+| Variable_name | Value |+-------------------------------------+-------+| Ongoing_anonymous_transaction_count | 0 |+-------------------------------------+-------+1 row in set (0.00 sec)
gtid_mode = ON on both sides of the master-replica replication structure.set global gtid_mode = ON;
my.cnf file and restart the database for the initial values to take effect.my.cnf configuration file is /etc/my.cnf. The actual path in your environment may vary.gtid_mode = onenforce_gtid_consistency = on
server_id parameter must be set manually and cannot be 0. The default value of this parameter is 1, but the configuration may not be correct even if the queried parameter value is 1. You need to manually set it.server_id parameter.set global server_id = 2; // It is recommended to set this parameter to an integer greater than 1. This is only an example.
show global variables like '%server_id%';
mysql> show global variables like '%server_id%';+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id | 2 |+---------------+-------+1 row in set (0.00 sec)
binlog_do_db: records binlogs only for specified databases. By default, all logs are recorded.binlog_ignore_db: does not record logs for specified databases.my.cnf configuration file for the source database to remove the do_db and ignore_db settings.my.cnf configuration file is /etc/my.cnf. The actual path in your environment may vary.[$Mysql_Dir]/bin/mysqladmin -u root -p shutdown[$Mysql_Dir]/bin/safe_mysqld &
show master status;
mysql> show master status;+---------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------+| binlog.000011 | 154 | | | |+---------------+----------+--------------+------------------+-------------------+
log-bin parameter is enabled for a replica, data operations on the replica can be recorded in its binlog. However, data replication from the master to the replica is not recorded in the binlog. Therefore, if the replica acts as a master for other replicas, you need to enable the log_slave_updates parameter. my.cnf configuration file for the source database.my.cnf configuration file is /etc/my.cnf. The actual path in your environment may vary.log_slave_updates = ON
[$Mysql_Dir]/bin/mysqladmin -u root -p shutdown[$Mysql_Dir]/bin/safe_mysqld &
show variables like '%log_slave_updates%';
mysql> show variables like '%log_slave_updates%';+-------------------+-------+| Variable_name | Value |+-------------------+-------+| log_slave_updates | ON |+-------------------+-------+1 row in set (0.00 sec)
Apakah halaman ini membantu?
Anda juga dapat Menghubungi Penjualan atau Mengirimkan Tiket untuk meminta bantuan.
masukan