I give advice to the leadership, real experience to share! mysql-mster-slave-Lock Table Problem!

Reason: A period of time has been to the leadership, the database although one hours back up, but still very dangerous, if the MySQL server suddenly down, or disk failure, not only the MySQL site will be a great threat, and even if the recovery of the database also takes more than 10 minutes, Causes the site to be inaccessible therefore. It is strongly recommended to do a slave; in the end, the leader listened to my opinion. (Although the company about 800 people, but the server is not so much, is small business)

The benefits of doing slave

1) The current database is backed up for one hours, and is offsite locally. Binary logs are turned on. So when the MySQL server does not have the problem, everything is all there is to say. Something's wrong.

2) do slave, so that even if the main server unexpectedly, you can quickly switch to the slave server, reducing the time the site cannot run.

3) The slave backup interval and the master server interval are half an hour apart, which further reduces the risk of data loss, even though the company is not very concurrent.

4) After doing the slave, slave also has its own binary log, so that half an hour of data risk can be remedied. (as long as two units do not fail at the same time)

When the solution is all submitted, the problem comes, the leader wants to start from the new on two servers, so that even if the master and slave environment is set up, the leaders worry about the short time will affect the business, also affect the operation of the site

(It takes time to import the database, if you stop the MySQL service, then the site does not work properly, if you do not stop the site, then a short period of time there will be slightly different data, in case the database is written to do it?)

OK, I gave an opinion, do not stop the current database, but when the master and slave server environment, when the import data, the current Server lock table, prohibit write operations, and then wait for master and slave data synchronization, and master data fully imported, and then switch the database. (and operate after 10 o'clock in the evening after hours)

Solution through!!!

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M01/7E/EA/wKiom1cMq57j0b1UAADVR4q8Baw000.jpg "title=" 1.jpg " alt= "Wkiom1cmq57j0b1uaadvr4q8baw000.jpg"/>

The above command means that the global lock table, all tables are not writable,

Here is the unlock.

Then execute the next lock table.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/7E/EA/wKiom1cMrB_iKrIrAAB_7GbZrVQ574.jpg "title=" 3.jpg " alt= "Wkiom1cmrb_ikriraab_7gbzrvq574.jpg"/> for a write operation

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M02/7E/E7/wKioL1cMrPiDvWiBAADdTlwvqGg158.jpg "title=" 2.jpg " alt= "wkiol1cmrpidvwibaaddtlwvqgg158.jpg"/> Error said that the table has been locked, read-only, can not be written.

Then unlock, and then do the operation!

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M00/7E/E7/wKioL1cMrT7i79jNAAD2zoLjook699.jpg "title=" 4.jpg " alt= "Wkiol1cmrt7i79jnaad2zoljook699.jpg"/>

Ok!!!

To share the knowledge of the lock list.

1.FLUSH TABLES with READ LOCK

This command is a global read lock, and all of the library tables are locked for read-only after the command has been executed. is generally used in the database online backup, this time the database write operations will be blocked, read operation smoothly.

The unlocked statement is also unlock tables.

2.LOCK TABLES tbl_name [as alias] {READ [LOCAL] | [Low_priority] WRITE}

This command is a table-level lock that can be customized to lock a table. Example: Lock tables test read; Does not affect write operations on other tables.

The unlock statement is also unlock tables.

Both statements need to be aware of the characteristics when executing, that is, implicitly committed statements. The unlock tables is implicitly executed when exiting the MySQL terminal. That is, if you want the table lock to take effect, you must always keep the conversation.

 

P.S. MySQL's read lock and Wirte lock

Read-lock: Allows other concurrent read requests, but blocks write requests, which can be read at the same time, but no write is allowed. Also called shared lock

Write-lock: Does not allow other concurrent read and write requests, is exclusive (exclusive). Also called exclusive lock

Then I thought of a small problem. If there are multiple databases, then a different Web site, if the global lock table is not good. But, if a lock table, pro, so much, how to lock Ah! Here to give you a small question, welcome to answer!

If you think this document is helpful to you, then just praise it! Hope to write better articles and share with you!

This article comes from "? Only! "Blog, be sure to keep this provenance http://renzhiyuan.blog.51cto.com/10433137/1763031

I give advice to the leadership, real experience to share! mysql-mster-slave-Lock Table Problem!