MySQL replication and temporary table

When creating a temporary table, you can use the temporary keyword. For example:

 

 create temporary table tmp_table(name varchar(10) not null,passwd char(6) not null)

 

 

The temporary table is only visible to the current connection. When the connection is closed, it is automatically dropped. This means that you can use the same temporary table name in two different connections without conflict with each other, or use an existing table, but not a temporary table name. (When the temporary table exists, the existing table is hidden. If the temporary table is dropped, the existing table is visible ). You must have

Create temporary table permission.

 

The following are restrictions on temporary tables:

• Temporary tables can only be used in memory, MyISAM, merge, or InnoDB

Temporary tables do not support MySQL cluster (cluster)

• In the same query statement, you can only search for a temporary table once. For example, the following is not available

Mysql>SELECT * FROM temp_table, temp_table AS t2;

Error 1137: Can't reopen table: 'temp _ table'

If you use different aliases to search for a temporary table multiple times in a storage function, or use different statements in the storage function, this error will occur.

• The show tables statement does not list temporary tables.
• You cannot use Rename to rename a temporary table. However, you can replace alter table:

Mysql>ALTER TABLE orig_name RENAME new_name;

Temporary tables are also used in Database Replication. For details, see:Section 16.4.1, "replication features and issues ".

Copying and temporary tables

When using temporary tables, disable slave safely. Temporary tables can be copied. Apart from shutting down slave database services (not just slave threads) and temporary table updates that you have already enabled for replication, the slave database has not been executed yet. If you stop the slave Database Service, these updates are required for the temporary table when the slave database is restarted and are no longer available. To avoid this problem, do not stop the slave service when the temporary table is opened. Use the following steps instead:

• Use the stop slave SQL _thread statement
• Use show status to view the slave_open_temp_tables Value
• If the value is not 0, use start slave SQL _thread; restart the slave Database SQL thread, and then repeat this step later.
• If the value is 0, run the mysqladmin shutdown command to stop the slave.

Temporary table and copy options. By default, all temporary tables are copied, regardless of whether they match--replicate-do-db,--replicate-do-table, Or--replicate-wild-do-table, Temporary tables will be copied. However,--replicate-ignore-tableAnd--replicate-wild-ignore-tableThe two options are used to ignore temporary tables.

If you do not want to copy some temporary tables, use--replicate-wild-ignore-table. For example:--replicate-wild-ignore-table= Foo %. Bar %, which indicates that the slave thread should not copy tables starting with Foo and starting with bar.

The following is the configuration information of someone transferred from the Internet:

Master Configuration:

 

 [Mysqld]
# Master start
# Log output addresses are mainly used for synchronization
Log-bin =/var/log/MySQL/updatelog
# Synchronize Databases
BINLOG-do-DB = CNB
# The host ID cannot be the same as the slave ID
Server-id = 1
# Master end

Slave Configuration:

 

 

Code

 [Mysqld]
# Slave start
# Slave ID, different from the host ID
Server-id = 2
# Host IP address, used by the slave machine to connect to the host
Master-host = 192.168.0.24
# Host port
Master-Port = 3307
# The account you just created to copy host data from the slave machine
Master-user = slave
# The new password for copying host data from the slave
Masters-Password = 123456
# Retry Interval: 10 seconds
Master-connect-retry = 10
# Databases to be synchronized
Replicate-do-DB = CNB
# Enable slave database logs for chained Replication
Log-slave-Updates
# Whether the slave database is read-only. 0 indicates read/write, and 1 indicates read-only.
Read-Only = 1

# Copy only a table
# Replicate-do-table = tablename
# Copy only some tables (matching characters available)
# Replicate-wild-do-table = tablename %
# Only copy a database
# Replicate-do-DB = dbname
# Do not copy a table
# Replicate-ignore-table = tablename
# Do not copy some tables
# Replicate-wild-ignore-table = tablename %
# Do not copy a database
# Replicate-ignore-DB = dbname
# Slave end