河北邯郸中考成绩查询网站,成都程序员培训机构,惠州排名推广,网站专题页优化vim /etc/my.cnf.d/mariadb-server.cnf[mysqld]binlog-do-dbdb1 #白名单模式#xff0c;仅允许主服务器上生成db1的二进制日志#xff0c;此选项不支持一行指定多个参数#xff0c;需要每个参数写一行binlog-do-dbdb2重启服务systemctl restart mariadb.service主服务器…vim /etc/my.cnf.d/mariadb-server.cnf[mysqld]binlog-do-dbdb1 #白名单模式仅允许主服务器上生成db1的二进制日志此选项不支持一行指定多个参数需要每个参数写一行binlog-do-dbdb2重启服务systemctl restart mariadb.service主服务器上删除了非db1和db2的一些数据库发现这些二进制日志已无法同步到从节点MariaDB [(none)] show databases;--------------------| Database |--------------------| bdb || db2 || db3 || db5 || db6 || hellodb || information_schema || mysql || performance_schema |--------------------9 rows in set (0.001 sec)MariaDB [(none)] create database db1;Query OK, 1 row affected (3.002 sec)MariaDB [(none)]MariaDB [(none)]MariaDB [(none)]MariaDB [(none)] drop database bdb;Query OK, 0 rows affected (0.001 sec)MariaDB [(none)] drop database db5;Query OK, 0 rows affected (0.000 sec)MariaDB [(none)] drop database db6;Query OK, 0 rows affected (0.000 sec)MariaDB [(none)] show databases;--------------------| Database |--------------------| db1 || db2 || db3 || hellodb || information_schema || mysql || performance_schema |--------------------7 rows in set (0.000 sec)从节点查看数据库发现数据库并未改变且slave正常MariaDB [(none)] show databases;--------------------| Database |--------------------| bdb || db1 || db2 || db3 || db5 || db6 || hellodb || information_schema || mysql || performance_schema |--------------------10 rows in set (0.000 sec)MariaDB [(none)] show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.0.0.211Master_User: repluserMaster_Port: 3306Connect_Retry: 10Master_Log_File: mariadb-bin.000004Read_Master_Log_Pos: 487Relay_Log_File: mariadb-relay-bin.000006Relay_Log_Pos: 788Relay_Master_Log_File: mariadb-bin.000004Slave_IO_Running: Yes #此处显示状态正常Slave_SQL_Running: Yes #此处显示状态正常Replicate_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: 487Relay_Log_Space: 1549Until_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: 211Master_SSL_Crl:Master_SSL_Crlpath:Using_Gtid: NoGtid_IO_Pos:Replicate_Do_Domain_Ids:Replicate_Ignore_Domain_Ids:Parallel_Mode: conservativeSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itSlave_DDL_Groups: 11Slave_Non_Transactional_Groups: 0Slave_Transactional_Groups: 01 row in set (0.000 sec)ERROR: No query specified主服务器上修改db1数据库增加表格MariaDB [db1] create table jinlei (id int,name varchar(20));Query OK, 0 rows affected (0.004 sec)从服务器上查看可以同步该库的表MariaDB [(none)] use db1Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [db1] show tables;---------------| Tables_in_db1 |---------------| jinlei |---------------1 row in set (0.000 sec)主服务器在db3数据库内添加表MariaDB [db1] use db3Database changedMariaDB [db3] create table jinlei (id int,name varchar(20));Query OK, 0 rows affected (0.003 sec)从服务器无法同步db3数据库内的表MariaDB [db1] use db3Database changedMariaDB [db3] show tables;Empty set (0.000 sec)总结在主服务器上配置文件中仅允许记录db1、db2的二进制文件后主服务器上修改其他的数据库则无法同步到从服务器只能在db1或db2上修改数据库才能同步到从服务器。方法2服务器选项从服务器SQL_THREAD在relay log中的事件时仅读取与特定数据库或特定表相关的事件并应用于本地因为二进制日志是在从服务器本地被过滤二进制日志还是通过主服务器发送过来了所以此方法会造成网络及磁盘I/O的浪费从服务器上修改系统变量MariaDB [db3] stop slave;Query OK, 0 rows affected (0.002 sec)MariaDB [db3] set global replicate_do_db‘db1,db2‘;Query OK, 0 rows affected (0.000 sec)主服务器上在db2中增加一个表MariaDB [(none)] use db2Database changedMariaDB [db2] create table jiang (id int,name char(10));Query OK, 0 rows affected (0.003 sec)从服务器上db2内可以同步该表MariaDB [(none)] use db2;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [db2] show tables;---------------| Tables_in_db2 |---------------| jiang |---------------1 row in set (0.000 sec)主服务器在db3中增加一张表MariaDB [db2] use db3Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [db3] create table liang (id int,name char(10));Query OK, 0 rows affected (0.003 sec)从服务器上在db3中无法同步该表MariaDB [db3] show tables;---------------| Tables_in_db3 |---------------| liang || mao || zheng |---------------3 rows in set (0.000 sec)总结该方法相对于方法一更加的灵活。但这两种方法都不支持主服务器跨库操作。2.MYSQL复制过滤器标签tables 变量 参数 har cond replica host thread off本条技术文章来源于互联网如果无意侵犯您的权益请点击此处反馈版权投诉本文系统来源https://www.cnblogs.com/jinlei92131/p/13588971.html
