MySQL replication and temporary table (temporary table)

Source: Internet
Author: User

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;

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 can be copied. However, the -- replicate-ignore-table and -- replicate-wild-ignore-Table options are used to ignore temporary tables.
If you do not want to copy some temporary tables, use the -- replicate-wild-ignore-Table option. For example, -- replicate-wild-ignore-table = Foo %. Bar % indicates that the slave thread does 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:
[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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.