Tips for using temporary tables in mysql replication
- 2020-05-13 03:38:12
- OfStack
As we know, temporary tables have the following characteristics:
1. SESSION level, SESSION 1 is automatically DROP when it is broken.
2. It has to do with the default engine. If the default engine is INNODB and you're frantically using temporary tables. So, your IBDATA is going to be infinitely larger.
3. Like disk table 1, write to binlog by default, and passively join rollback plan.
Imagine the following scenario:
The database is corrupt and there is no backup. It just so happens that the binary log is all well preserved and happy. Import the binary log into MYSQL.
There are two ways:
1) mysqlbinlog... *. log | mysql dbname; This use of direct data recovery pipeline, short time, high efficiency. Unfortunately, if you have 1 heap of temporary tables in there, right in the middle of the log, the import fails. (recall the nature of temporary tables.)
2) mysqlbinlog... *. log > result.log;mysql dbname < result. log; This time is long, inefficient, takes up high disk space, and wastes system resources many times. But the import worked.
If you have a large number of temporary table applications, the correct method to use in master-slave replication is: (I talked about this in 2008).
When writing data, of course, this is only the host. Whether you are managing MYSQL in your application or on your own client side, this should be the case:
Open SESSION level replication.
1. SESSION level, SESSION 1 is automatically DROP when it is broken.
2. It has to do with the default engine. If the default engine is INNODB and you're frantically using temporary tables. So, your IBDATA is going to be infinitely larger.
3. Like disk table 1, write to binlog by default, and passively join rollback plan.
Imagine the following scenario:
The database is corrupt and there is no backup. It just so happens that the binary log is all well preserved and happy. Import the binary log into MYSQL.
There are two ways:
1) mysqlbinlog... *. log | mysql dbname; This use of direct data recovery pipeline, short time, high efficiency. Unfortunately, if you have 1 heap of temporary tables in there, right in the middle of the log, the import fails. (recall the nature of temporary tables.)
2) mysqlbinlog... *. log > result.log;mysql dbname < result. log; This time is long, inefficient, takes up high disk space, and wastes system resources many times. But the import worked.
If you have a large number of temporary table applications, the correct method to use in master-slave replication is: (I talked about this in 2008).
When writing data, of course, this is only the host. Whether you are managing MYSQL in your application or on your own client side, this should be the case:
[sql]
SET SQL_LOG_BIN=0;
Turn off the SESSION Level of replication.
update tmp_t1, tmp_t2 set ....;
drop tmp_t1;
drop tmp_t2;
SET SQL_LOG_BIN=1;
Open SESSION level replication.