Replication Features and Issues
1504
4. When the value is 0, issue a
mysqladmin shutdown
command to stop the slave.
Temporary tables and replication options.
By default, all temporary tables are replicated; this
happens whether or not there are any matching
--replicate-do-db
[1457]
,
--replicate-
do-table
[1458]
, or
--replicate-wild-do-table
[1459]
options in effect. However, the
--
replicate-ignore-table
[1458]
and
--replicate-wild-ignore-table
[1460]
options are
honored for temporary tables.
A recommended practice when using replication is to designate a prefix for exclusive use in naming
temporary tables that you do not want replicated, then employ a matching
--replicate-wild-
ignore-table
[1460]
option. For example, you might give all such tables names beginning with
norep
(such as
norepmytable
,
norepyourtable
, and so on), then use
--replicate-wild-
ignore-table=norep%
[1460]
to prevent the replication of these tables.
16.4.1.16. Replication of the
mysql
System Database
User privileges are replicated only if the
mysql
database is replicated. That is, the
GRANT
,
REVOKE
,
SET PASSWORD
,
CREATE USER
, and
DROP USER
statements take effect on the slave only if the
replication setup includes the
mysql
database.
See also
Section 16.4.1.17, “Replication and User Privileges”
.
16.4.1.17. Replication and User Privileges
User privileges are replicated only if the
mysql
database is replicated. That is, the
GRANT
,
REVOKE
,
SET PASSWORD
,
CREATE USER
, and
DROP USER
statements take effect on the slave only if the
replication setup includes the
mysql
database.
If you are replicating all databases, but do not want statements that affect user privileges to be
replicated, set up the slave not to replicate the
mysql
database, using the
--replicate-wild-
ignore-table=mysql.%
[1460]
option. The slave recognizes that privilege-related SQL statements
have no effect, and thus it does not execute those statements.
See
Section 16.4.1.16, “Replication of the
mysql
System Database”
, for more information.
16.4.1.18. Replication and the Query Optimizer
It is possible for the data on the master and slave to become different if a statement is written in such
a way that the data modification is nondeterministic; that is, left up the query optimizer. (In general, this
is not a good practice, even outside of replication.) Examples of nondeterministic statements include
DELETE
or
UPDATE
statements that use
LIMIT
with no
ORDER BY
clause; see
Section 16.4.1.9,
“Replication and
LIMIT
”
, for a detailed discussion of these.
Also see
Section C.5.8, “Known Issues in MySQL”
.
16.4.1.19. Replication and Reserved Words
You can encounter problems when you attempt to replicate from an older master to a newer slave and
you make use of identifiers on the master that are reserved words in the newer MySQL version running
on the slave. An example of this is using a table column named
current_user
on a 4.0 master
that is replicating to a 4.1 or higher slave because
CURRENT_USER
is a reserved word beginning in
MySQL 4.1. Replication can fail in such cases with Error 1064
You have an error in your SQL
syntax...
, even if a database or table named using the reserved word or a table having a column
named using the reserved word is excluded from replication. This is due to the fact that each SQL
event must be parsed by the slave prior to execution, so that the slave knows which database object or
objects would be affected; only after the event is parsed can the slave apply any filtering rules defined
by
--replicate-do-db
[1457]
,
--replicate-do-table
[1458]
,
--replicate-ignore-
db
[1457]
, and
--replicate-ignore-table
[1458]
.
To work around the problem of database, table, or column names on the master which would be
regarded as reserved words by the slave, do one of the following:
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...