Replication Features and Issues
1507
care should be taken when replicating transactional tables from the master to nontransactional tables
on the slaves.
Beginning with MySQL 5.0.56, every transaction (including
autocommit
[436]
transactions) is
recorded in the binary log as though it starts with a
BEGIN
statement, and ends with either a
COMMIT
or a
ROLLBACK
statement. However, this does not apply to nontransactional changes; any statements
affecting tables using a nontransactional storage engine such as
MyISAM
are regarded for this purpose
as nontransactional, even when
autocommit
[436]
is enabled. (Bug #26395)
16.4.1.26. Replication and Triggers
Known issue: In MySQL 5.0.17, the syntax for
CREATE TRIGGER
changed to include a
DEFINER
clause for specifying which access privileges to check at trigger invocation time. (See
Section 13.1.11,
“
CREATE TRIGGER
Syntax”
, for more information.) However, if you attempt to replicate from a master
server older than MySQL 5.0.17 to a slave running MySQL 5.0.17 through 5.0.19, replication of
CREATE TRIGGER
statements fails on the slave with a
Definer not fully qualified
error. A
workaround is to create triggers on the master using a version-specific comment embedded in each
CREATE TRIGGER
statement:
CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER ... ;
CREATE TRIGGER
statements written this way will replicate to newer slaves, which pick up the
DEFINER
clause from the comment and execute successfully.
This slave problem is fixed as of MySQL 5.0.20.
16.4.1.27. Replication and Views
Views are always replicated to slaves. Views are filtered by their own name, not by the tables they refer
to. This means that a view can be replicated to the slave even if the view contains a table that would
normally be filtered out by
replication-ignore-table
rules. Care should therefore be taken to
ensure that views do not replicate table data that would normally be filtered for security reasons.
16.4.1.28. Replication and Variables
The
foreign_key_checks
[451]
,
unique_checks
[504]
, and
sql_auto_is_null
[493]
variables
are all replicated.
sql_mode
[495]
is also replicated except for the
NO_DIR_IN_CREATE
[537]
mode. However, when
mysqlbinlog
parses a
SET @@sql_mode = mode
statement, the full
mode
value, including
NO_DIR_IN_CREATE
[537]
, is passed to the receiving server.
The
storage_engine
[497]
system variable is not replicated, regardless of the logging mode; this is
intended to facilitate replication between different storage engines.
The
read_only
[488]
system variable is not replicated. In addition, the enabling this variable has
different effects with regard to temporary tables, table locking, and the
SET PASSWORD
statement in
different MySQL versions.
The
max_heap_table_size
[468]
system variable is not replicated. Increasing the value of this
variable on the master without doing so on the slave can lead eventually to
Table is full
errors
on the slave when trying to execute
INSERT
statements on a
MEMORY
table on the master that is thus
permitted to grow larger than its counterpart on the slave. For more information, see
Section 16.4.1.14,
“Replication and
MEMORY
Tables”
.
Starting from MySQL 5.0.3 (master and slave), replication works even if the master and slave have
different global character set variables. Starting from MySQL 5.0.4 (master and slave), replication
works even if the master and slave have different global time zone variables.
Session variables are not replicated properly when used in statements that update tables. For example,
the following sequence of statements will not insert the same data on the master and the slave:
Содержание 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 ...