Shulou(Shulou.com)05/31 Report--

This article will explain in detail what matters needing attention from mysqldump synchronization data to production, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. If you need to pour data from one instance into another production instance, you need to pay attention to this: use the following statement dump!

Mysqldump-uroot-p-t-- single-transaction=true-- skip-disable-keys=false-- add-locks=false-- set-gtid-purged=off-B liuwenhe-- tables r > / data/backup/r.sql

Note:

1)-- SET @ @ GLOBAL.GTID_PURGED is not added to the set-gtid-purged=false result file to prevent error reporting, because SET @ @ GLOBAL.GTID_PURGED can only be executed after reset master!

2)-- skip-disable-keys=false closes the operation of closing index keys such as ALTER TABLE `r` DISABLE KEYS, otherwise the index may become invalid online! Affect the busin

3)-- SET @ @ SESSION.SQL_LOG_BIN= 0 is not included in the set-gtid-purged=false result file; that is, you have to write binlog, otherwise you cannot synchronize to the slave library!

4)-- add-locks=false without LOCK TABLES `acct_ loan` WRITE; pay special attention to this. After lock, only this session can operate the table, and this session cannot operate other tables, and other session cannot operate the table. The specific experimental process: (pxc and simple master-slave is the same effect! )

(lab 1 about lock tables r write:

Session 1:

Root@localhost: liuwenhe 18:33:01 > LOCK TABLES r WRITE

Query OK, 0 rows affected (0.00 sec)

Root@localhost: liuwenhe 18:33:03 > insert into r values (1000); # you can manipulate the r table or view it

Query OK, 1 row affected (0.00 sec)

Root@localhost: liuwenhe 18:33:09 > insert into t values (1000); # this session cannot operate t table

ERROR 1100 (HY000): Table 't'was not locked with LOCK TABLES

Root@localhost: liuwenhe 18:33:15 > select count (*) from t; # this session cannot query other tables

ERROR 1100 (HY000): Table 't'was not locked with LOCK TABLES)

Session 2:

Root@localhost: liuwenhe 19:04:33 > select count (*) from r; # query wait

Root@localhost: liuwenhe 18:39:21 > insert into r values (10); # dml wait and find that other session cannot manipulate tables locked by session1!

Root@localhost: liuwenhe 18:41:36 > delete from yy2; # found that session 2 can manipulate other tables

Query OK, 3 rows affected (0.04 sec)

Oot@localhost: liuwenhe 19:05:32 > select count (*) from yy2; found that tables other than r can be read!

Lab 2 about lock table r read

Session 1:

Root@localhost: liuwenhe 18:45:38 > lock table r read

Query OK, 0 rows affected (0.00 sec)

Root@localhost: liuwenhe 18:47:17 > select count (*) from t; # found that tables other than r cannot be read

ERROR 1100 (HY000): Table 't'was not locked with LOCK TABLES

Root@localhost: liuwenhe 18:47:51 > delete from t; # cannot manipulate tables other than r

ERROR 1100 (HY000): Table 't'was not locked with LOCK TABLES

Root@localhost: liuwenhe 18:47:28 > select count (*) from r; # can read r table

+-+

| | count (*) |

+-+

| | 5 |

+-+

1 row in set (0.00 sec)

Root@localhost: liuwenhe 18:47:39 > insert into r values (10); # cannot manipulate r table

ERROR 1099 (HY000): Table 'r'was locked with a READ lock and can't be updated

Session 2:

Root@localhost: liuwenhe 18:53:02 > delete from t limit 1; # session 2 can manipulate t table

Query OK, 1 row affected (0.01sec)

Root@localhost: liuwenhe 18:53:47 > select count (*) from yy2; # session 2 you can view other tables

+-+

| | count (*) |

+-+

| | 0 |

+-+

1 row in set (0.00 sec)

Root@localhost: liuwenhe 18:54:29 > delete from r; # wait, cannot operate table r locked by session1

Root@localhost: liuwenhe 18:55:20 > select * from r; # you can view the session1 lock table!

+-+

| | id |

+-+

| | 10 |

Summary:

Lock tables t write: the current session can read and write this table, but not other tables; other sessions can read and write other tables, but cannot read and write this table

Lock table r read: the current session can only read this table, but cannot manipulate the table, nor can it read or write other tables. Other sessions can read but not write to this table.

About mysqldump synchronization data to production notes are shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.