In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >
Share
Shulou(Shulou.com)06/03 Report--
This article mainly introduces "what are the common MySQL interview questions". In the daily operation, I believe many people have doubts about the common MySQL interview questions. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the questions of "what are the common MySQL interview questions?" Next, please follow the editor to study!
1: is there a backup of your company's database?
A: yes, because a single point of failure is inevitable, our company has masters and subordinates. Interviewer: do you know the difference between master and slave, master and master?
The main and standby machines are: mainframe and standby machine.
The standby machine does not work, that is, it does not provide external services, but silently synchronizes the data of the host, and then waits for one day when the host is dead, it takes its place!
As for switching, there are two main ways:
Manual switching, after learning that the host has hung up, manually cut the standby machine into the host, the disadvantage is slow.
Use keepalived or write your own script for monitoring, and then switch automatically.
The master and slave are the master and slave.
The difference between slave and standby is that it provides external services, generally speaking, master and slave are read and write separated, write requests are assigned to the host, and read requests are assigned to the slave.
The master is that both are mainframes.
Under normal circumstances, there will be no master's structure.
When two write requests reach the same table in the two main databases at the same time, a record will be created at the same time, and the ID of this record is the same, so that one of them will be overwritten after data synchronization, which will cause problems.
2: why do you have to separate reading and writing?
A: read-write separation is the separation of read and write operations from a previous server, sharing some of the pressure on the master library to the slave library.
In essence, it is because the visit volume is too large, the pressure on the main database is too great, and the stand-alone database can not support concurrent read and write.
Then, generally speaking, the number of reads is much higher than that of writes, so the read operations are distributed to the slave library, which is the common separation of read and write.
Another operation of read-write separation is that the main database does not build the index of the query, but builds the index of the query from the library.
Because the index needs to be maintained, for example, if you insert a piece of data, you have to insert not only the clustered index, but also the corresponding secondary index, and the modification is the same.
So after dividing the read operation to the slave database, you can delete the index to be used by the query in the master database to reduce the impact of the write operation on the master library.
3: do you read-write separation in middleware or code encapsulation?
A: code encapsulation.
To put it bluntly, a middle layer is extracted from the code level, and the middle layer is used to achieve read-write separation and database connection.
Is to set up a proxy class, which exposes the normal read-write interface, which encapsulates the logic and points the read operation to the data source of the slave library and the write operation to the data source of the master library.
Advantages: simple, and can be customized according to business changes, at will.
Disadvantages: if the database is down, after the master-slave switch occurs, you have to modify the configuration and restart. If the system is multilingual, you need to implement a middle-tier code for each language and repeat the development.
Middle ware
Generally speaking, it is an independently deployed system, and the interaction between the client and this middleware is through the SQL protocol.
So from the point of view of the client, what is connected is a database, and multi-language differences can also be shielded through SQL protocol interaction.
The disadvantage is that the overall architecture has one more system to maintain, and may become a performance bottleneck, after all, interactions need to be transferred through it.
Common open source database middleware are: official MySQL-Proxy, Atlas, Mycat and so on.
Do you know the master-slave synchronization mechanism of 4:MySQL?
Answer: master-slave synchronization mainly depends on binlog,MySQL asynchronous replication by default. The specific process is as follows:
Main library:
Request for commit transaction received
Update data
Write data to binlog
Respond to the client
Push binlog to slave library
From the library:
The synchronized binlog is written to the relay log by the IThread O thread.
The SQL thread replays the event from the relay log to update the data
Return a response to the main library.
To sum up in one sentence: the transaction committed by the master library will write binlog, which will be pushed by a dump thread to the slave library, and after it is accepted from the library, an I / O thread will write it to the relay log, slowly digest it, and the SQL thread will replay the updated data.
There is a risk of data loss in asynchronous replication. For example, if the data is not synchronized to the slave database, the master database will respond to the client, and then the master database will fail. If the data is promoted from the library to the master database, the data is missing.
Therefore, if there is synchronous replication, the master database needs to copy the binlog to all slave libraries and wait for all slave libraries to respond before giving a response to the client. In this case, the performance is very poor, and synchronous replication is generally not selected.
A semi-synchronous replication has been done after MySQL 5.7. there is a parameter that can be selected to "successfully synchronize a few responses from the library."
For example, if there are a total of three slave libraries and I configure the parameter 1, as long as one slave library responds that the copy is successful, the master library will directly return the response to the client and will not wait for the other two slave libraries.
In this way, the performance is better, and the data reliability is enhanced, and the data will be missing only when both the slave library and the master library are down at the same time.
5: how to deal with master-slave synchronization delay?
A: from the process in the figure above, we can see that delay is inevitable.
If the delay is too large, it is possible for a user to register and then log in to report that the user does not exist.
Because the data is written to the main database, the query slave database may be synchronized in the future, resulting in the user cannot be found.
This is very unfriendly.
There are several common solutions:
Second inquiry. If you can't find the data from the database, go to the main database to check it again and encapsulate it by API, which can be regarded as a strategy, which is relatively simple. However, the pressure of reading has shifted to the main database, and it will be uncomfortable if there are lawbreakers who estimate that they will not be able to find out.
Forces the operation of reading immediately after writing to be transferred to the main library. This kind of code is written dead, such as some operations that are queried immediately after writing, are bound together, and all go to the main library. No, it's too stiff.
Critical business read and write all go to the main library, non-critical or read-write separation. For example, if the user I mentioned above registers, you can read and write the main database, so that there will be no problem of logging in and reporting that the user does not exist, and the frequency of such visits should not be very high, so adjust this kind of interface appropriately depending on the business.
At this point, the study of "what are the common MySQL interview questions" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!
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.
Views: 227
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.