张槎九江网站建设,淮安网站设计公司,小程序搭建是什么工作,企业网页设计多少钱binlog-format#xff1a;二进制日志的格式#xff0c;有row、statement和mixed几种类型#xff1b;需要注意的是#xff1a;当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW#xff0c;现在MySQL官方认为STATEMENT这个已经不再适合继续使用#xff1b;但mixe…binlog-format二进制日志的格式有row、statement和mixed几种类型需要注意的是当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW现在MySQL官方认为STATEMENT这个已经不再适合继续使用但mixed类型在默认的事务隔离级别下可能会导致主从数据不一致log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host用于启动GTID及满足附属的其它需求master-info-repository和relay-log-info-repository启用此两项可用于实现在崩溃时保证二进制及从服务器安全的功能sync-master-info启用之可确保无信息丢失slave-paralles-workers设定从服务器的SQL线程数根据cpu核数设定0表示关闭多线程复制功能binlog-checksum、master-verify-checksum和slave-sql-verify-checksum启用复制有关的所有校验功能binlog-rows-query-log-events启用之可用于在二进制日志记录事件相关的信息可降低故障排除的复杂度log-bin启用二进制日志这是保证复制功能的基本前提server-id同一个复制拓扑中的所有服务器的id号必须惟一。report-hostThe host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.report-port:The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.master-info-repository:The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info)relay-log-info-repositoryThis option causes the server to log its relay log info to a file or a table.log_slave_updatesWhether updates received by a slave server from a master server should be logged to the slaves own binary log. Binary logging must be enabled on the slave for this variable to have any effect.master服务器配置编辑master的参数文件#GTID parametergtid-modeonenforce-gtid-consistencytrueslave-parallel-workers10binlog-checksumCRC32binlog-formatROWlog-slave-updatestruereport-port3306report-host192.168.56.212master-info-repositoryTABLErelay-log-info-repositoryTABLEsync-master-info1master-verify-checksum1slave-sql-verify-checksum1binlog-rows-query-log_events1重启master的mysql数据库[rootray ~]# /data/3306/mysqld restartStoping MySQL...Warning: Using a password on the command line interface can be insecure.Starting MySQL...mysql show global variables like %gtid%;---------------------------------| Variable_name | Value |---------------------------------| enforce_gtid_consistency | ON || gtid_executed | || gtid_mode | ON | #说明gti功能已启动| gtid_owned | || gtid_purged | |---------------------------------5 rows in set (0.01 sec)创建同步用户mysql GRANT REPLICATION SLAVE ON *.* TO rep% IDENTIFIED BY 123456;Query OK, 0 rows affected (0.78 sec)mysql flush privileges;Query OK, 0 rows affected (0.00 sec)从服务器slave配置my.cnf参数文件配置#GTID parametergtid-modeonenforce-gtid-consistencytrueslave-parallel-workers10binlog-checksumCRC32relay-log /data/3307/logs/relay-logrelay-log-index /data/3307/logs/relay-log.indexbinlog-formatROWlog-slave-updatestruereport-port3307report-host192.168.56.212master-info-repositoryTABLErelay-log-info-repositoryTABLEsync-master-info1sync_relay_log 1sync_relay_log_info 1master-verify-checksum1slave-sql-verify-checksum1binlog-rows-query-log_events1relay_log_recovery ON重启mysql数据库[rootray ~]# /data/3307/mysqld restartStoping MySQL...Warning: Using a password on the command line interface can be insecure.Starting MySQL...mysql show global variables like %gtid%;---------------------------------| Variable_name | Value |---------------------------------| enforce_gtid_consistency | ON || gtid_executed | || gtid_mode | ON || gtid_owned | || gtid_purged | |---------------------------------5 rows in set (0.56 sec)change master tomaster_host192.168.56.212,master_userrep,master_password123456,master_port3306,master_auto_position 1;mysql change master to- master_host192.168.56.212,- master_userrep,- master_password123456,- master_port3306,- master_auto_position 1;Query OK, 0 rows affected, 2 warnings (0.59 sec)mysql start slave;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.56.212Master_User: repMaster_Port: 3306Connect_Retry: 60Master_Log_File: ray-bin.000009Read_Master_Log_Pos: 588Relay_Log_File: relay-log.000003Relay_Log_Pos: 797Relay_Master_Log_File: ray-bin.000009Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 588Relay_Log_Space: 1175Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 97e8847a-ffdf-11e6-87ed-08002736c224Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set: 97e8847a-ffdf-11e6-87ed-08002736c224:1-2Executed_Gtid_Set: 97e8847a-ffdf-11e6-87ed-08002736c224:1-2Auto_Position: 1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1 row in set (0.00 sec)参数master-info-repositoryTABLErelay-log-info-repositoryTABLE把master.info 和relay.info 保存在表中默认是myisam引擎官方建议修改为innodbmysql use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql alter table slave_master_info engineinnodb;Query OK, 0 rows affected (0.29 sec)Records: 0 Duplicates: 0 Warnings: 0mysql alter table slave_relay_log_info engineinnodb;Query OK, 0 rows affected (0.07 sec)Records: 0 Duplicates: 0 Warnings: 0mysql alter table slave_worker_info engineinnodb;Query OK, 0 rows affected (0.13 sec)Records: 0 Duplicates: 0 Warnings: 0忽略过滤表配置文件需要重启replicate-ignore-tabletest.t1在线动态修改无需重启CHANGE REPLICATION FILETER REPLICATE_DO_DB(DB1,DB2);CHANGE REPLICATION FILETER REPLICATE_IGNORE_DB(DB1,DB2);CHANGE REPLICATION FILETER REPLICATE_DO_TABLE(DB1.T1);CHANGE REPLICATION FILETER REPLICATE_IGNORE_TABLE(DB1.T1);CHANGE REPLICATION FILETER REPLICATE_WILD_DO_TABLE(DB1.T%);CHANGE REPLICATION FILETER REPLICATE_WILD_IGNORE_TABLE(DB%.T%);CHANGE REPLICATION FILETER REPLICATE_REWRITE_DB(FROM_DB,TO_DB);
