分类： Mysql/postgreSQL
2010-05-18 10:07:43
The effects of this option depend on whether statement-based or row-based replication is in use.这个选项的复制效果取决于是命令复制还是行复制
Statement-based replication.
Tell the slave to restrict replication to statements where
the default database (that is, the one selected by
) is
db_name. To
specify more than one
database, use this option multiple times, once for each
database; however, doing so does not
replicate cross-database statements such as UPDATE
while a different database (or no
database) is selected.命令复制就是告诉从服务器复制约束在默认数据库（使用use命令选择的数据库） 使用命令多次如果要指定多个数据库，some_db.some_table
SET
foo='bar'
To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.
An example of what does not work as you might expect when using statement-based replication: If the slave is started with and you issue the following statements on the master, the statement is not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
The main reason for this “check just the default database” behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table statements or multiple-table statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.命令复制在指定过滤db后只复制默认db是过滤db的语句，行复制则复制所有复制db的语句不管这个db是否是默认的。
Row-based replication.
Tells the slave to restrict replication to database
db_name. Only
tables belonging to
db_name are
changed; the current
database has no effect on this. Suppose that the slave is
started with
and
row-based replication is in effect, and then the following
statements are run on the master:
USE prices;
UPDATE sales.february SET amount=amount+100;
The february table in the
sales database on the slave is
changed in
accordance with the
statement; this occurs whether or not the
statement was issued.
However, issuing the following statements on the master has
no effect on the slave when using row-based replication and
:
USE prices;
UPDATE prices.march SET amount=amount-25;
Even if the statement USE prices
were
changed to USE sales, the
statement's
effects would still not be replicated.
Another important difference in how is handled in statement-based replication as opposed to row-based replication occurs with regard to statements that refer to multiple databases. Suppose the slave is started with , and the following statements are executed on the master:
USE db1;
UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
If you are using statement-based replication, then both tables
are updated on the slave. However, when using row-based
replication, only table1 is
affected on the
slave; since table2 is in a
different
database, table2 on the slave is
not
changed by the .
Now
suppose that, instead of the USE db1
statement, a USE db4 statement
had been
used:
USE db4;
UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
In this case, the
statement would have no effect on the slave when using
statement-based replication. However, if you are using
row-based replication, the
would change
table1 on the slave, but not
table2 — in other words, only
tables
in the database named by
are changed,
and the choice of default database has no effect on this
behavior.
If you need cross-database updates to work, use instead. See .
This option affects replication in the same manner that affects binary logging, and the effects of the replication format on how affects replication behavior are the same as those of the logging format on the behavior of .
Beginning with MySQL 5.1.35, this option has no effect on , , or statements. ()
As with , the effects of this option depend on whether statement-based or row-based replication is in use.
Statement-based replication.
Tells the slave to not replicate any statement where the
default database (that is, the one selected by
) is
db_name.
Row-based replication.
Tells the slave not to update any tables in the database
db_name. The
default database has
no effect.
When using statement-based replication, the following example does not work as you might expect. Suppose that the slave is started with and you issue the following statements on the master:
USE prices;
UPDATE sales.january SET amount=amount+1000;
The
statement
is replicated in such a
case because
applies
only to the default database (determined by the
statement). Because the
sales database was specified
explicitly in
the statement, the statement has not been filtered. However,
when using row-based replication, the
statement's effects
are not propagated to
the slave, and the
slave's copy of the sales.january
table is unchanged; in this instance,
causes all changes made
to tables in the
master's copy of the sales
database to
be ignored by the slave.
To specify more than one database to ignore, use this option multiple times, once for each database. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.
You should not use this option if you are using cross-database updates and you do not want these updates to be replicated. See .
If you need cross-database updates to work, use instead. See .
This option affects replication in the same manner that affects binary logging, and the effects of the replication format on how affects replication behavior are the same as those of the logging format on the behavior of .
Beginning with MySQL 5.1.35, this option has no effect on , , or statements. ()
Tells the slave thread to restrict replication to the specified table. To specify more than one table, use this option multiple times, once for each table. This works for both cross-database updates and default database updates, in contrast to . See .
This option affects only statements that apply to tables. It
does not affect statements that apply only to other database
objects, such as stored routines. To filter statements
operating on stored routines, use one or more of the
--replicate-*-db options.
Tells the slave thread to not replicate any statement that updates the specified table, even if any other tables might be updated by the same statement. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates, in contrast to . See .
This option affects only statements that apply to tables. It
does not affect statements that apply only to other database
objects, such as stored routines. To filter statements
operating on stored routines, use one or more of the
--replicate-*-db options.
Tells the slave to translate the default database (that is,
the one selected by ) to
to_name if it was
from_name on the
master. Only
statements involving tables are affected (not statements such
as ,
, and
), and only if
from_name is the
default database
on the master. This does not work for cross-database updates.
To specify multiple rewrites, use this option multiple times.
The server uses the first one with a
from_name value that
matches. The
database name translation is done before
the --replicate-* rules are
tested.
If you use this option on the command line and the
“>”
character is special to
your command interpreter, quote the option value. For example:
shell>mysqld --replicate-rewrite-db="olddb->newdb"
To be used on slave servers. Usually you should use the default setting of 0, to prevent infinite loops caused by circular replication. If set to 1, the slave does not skip events having its own server ID. Normally, this is useful only in rare configurations. Cannot be set to 1 if is used. By default, the slave I/O thread does not write binary log events to the relay log if they have the slave's server ID (this optimization helps save disk usage). If you want to use , be sure to start the slave with this option before you make the slave read its own events that you want the slave SQL thread to execute.
Tells the slave thread to restrict replication to statements
where any of the updated tables match the specified database
and table name patterns. Patterns can contain the
“%”
and
“_”
wildcard characters, which
have the same meaning as for the
pattern-matching operator.
To specify more than one table, use this option multiple
times, once for each table. This works for cross-database
updates. See .
This option applies to tables, views, and triggers. It does
not apply to stored functions and procedures, or events. To
filter statements operating on the latter objects, use one or
more of the --replicate-*-db
options.
Example:
replicates only updates that use a table where the database
name starts with foo and the
table name
starts with bar.
If the table name pattern is %,
it matches
any table name and the option also applies to database-level
statements (,
, and
). For example,
if you use
,
database-level statements are replicated if the database name
matches the pattern foo%.
To include literal wildcard characters in the database or
table name patterns, escape them with a backslash. For
example, to replicate all tables of a database that is named
my_own%db, but not replicate
tables from
the my1ownAABCdb database, you
should
escape the “_”
and
“%”
characters like this:
.
If you use the option on the command line, you might need to
double the backslashes or quote the option value, depending on
your command interpreter. For example, with the
bash shell, you
would need to type
.
Tells the slave thread not to replicate a statement where any table matches the given wildcard pattern. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates. See .
Example:
does not replicate updates that use a table where the database
name starts with foo and the
table name
starts with bar.
For information about how matching works, see the description of the option. The rules for including literal wildcard characters in the option value are the same as for as well.
