Hey.

Question on database management systems. I do not understand how mysql works. There is a client program - it is a program that is USED. There is a server program - this is a program that PROVIDES. Here is a picture of how the DBMS works in conjunction with PHP. enter image description here

It can be seen from the picture, the browser sends a GET request to the web server - “Give me the script.php document, hold another parameter”. The web server finds this document in the site folder and gives it to the PHP interpreter for processing with the words "Here's a document for you, work it out + hold the server variables that I pulled from the browser's request header ( $_GET, $_SERVER )". The PHP interpreter program finds the REQUEST in the <?php ...?> Script and sends it to the DBMS, which climbs into a specific database folder, takes some data from there and SELLS it back to the PHP interpreter. It turns out that the PHP interpreter is a client. The figure shows that the PHP interpreter and the mysql server are on the same computer - the server. Can they ( PHP interpreter and mysql server) be on different computers? If so, according to what protocol do they "communicate" with each other?

For example, a browser and a web server communicate directly over HTTP . The browser makes a request that looks like the " HTTP header" + "data body (may be empty if the request is sent using the GET method)", the transfer process takes place (I omit the moment exactly how the data is transmitted), and the web server receives it in the same the form. Then the web server responds to the browser.

What program besides the PHP interpreter can be a client of the mysql server?

If I work with mysql DBMS on the command line, then to work you need to go into the mysql monitor program - mysql.exe -uroot -p is entered. Why is login and password entered when starting mysql monitor ? You can also enter a host ( \h ). Is it possible to work with the mysql server when the client is on one computer and the mysql server is on another computer?

How do we catch a mystery log header with the mysql protocol? I want to see how data is exchanged

  • Specify the data to connect. The host will no longer be localhost naturally - iKey
  • dev.mysql.com/doc/internals/en/overview.html - MaxU
  • A standard is not a textbook, but a reference book. therefore it is short and incomprehensible. but also in English - doubly incomprehensible - Dimon

2 answers 2

Can they (PHP interpreter and MySQL server) be on different computers?

Yes! MySQL usually works through a local loop, but it is a network adapter, and nothing prevents MySQL from working through a real network.

Moreover, as the system grows, the removal of the database to a separate machine can be a necessary measure, say, instead of having a single server, having a cluster. Or ... in Docker, for example, it is customary to have a container with a database separate from the application container. And containers interact with each other over the network almost as different machines.

If so, according to what protocol do they "communicate" with each other?

According to the MySQL protocol . It does not have a separate name, since it was formed in MySQL, and although there were other databases that implement it, they position themselves as “compatible with the MySQL protocol”.

What program besides PHP interpreter can be a client of MySQL server?

Any, provided, of course, that it can establish a TCP connection with the database server (namely TCP, not levels higher) and can implement the MySQL protocol.

Of course, there is a whole set of libraries-connectors for different languages ​​that implements this protocol; therefore, it is usually sufficient to connect the library and begin work. And since one of the official connectors libraries is written in C, you can attach it to almost any programming language.

Why is login and password entered when starting mysql monitor?

Because the DB otherwise will not allow access. Well this is a client. And the server requires you to introduce yourself before starting work to determine the privileges of the client.

You can also enter a host ( \h ). Is it possible to work with the mysql server when the client is on one computer and the mysql server is on another computer?

The h key is needed for that. True, you should be able to reach the port on which MySQL is running. How many different MySQL saw, by default they listen only to the local loop and do not accept external connections. For security reasons, of course. This does not interfere with throwing this port through an SSH tunnel, where at that end the connection will be visible, as from a local machine. SSH is relatively secure.

  • thanks for the answer. how to catch a mysql mysql protocol header? I want to see how data is exchanged - Dimon
  • @Dimon need to watch the connection to the database server, obviously. Knowing the address and port. But it is better to first read the general description of the protocol, by reference from the answer it is. - D-side
  • I mean that maybe there are some online databases (where you can write something and take it from there). and a client of some kind — some kind of client program of the mysql server, which I can put on my computer and “listen” to their communication as a wire harrow. - Dimon
  • @Dimon take MySQL Workbench, cling to it, connect to the local MySQL and feel. “Online” is no longer needed, MySQL is already working through the network, even though it’s virtual, within the same computer. - D-side
  • how does it cling to it? - Dimon
  1. Yes, the PHP interpreter and the mysql server can be on different "computers";
  2. In this case, they will communicate via TCP;
  3. Any program in any language can be a mysql client if there is an appropriate driver for the database;
  4. Login and password are required for authorization and verification of relevant access rights;
  5. Yes you can. Often the application server and database are spread to different hosts.
  • thanks for the answer. as far as I know, the tcp protocol is transport. it cuts data into pieces when sending and collects data from pieces when receiving + connection performs. In the tcp headers, all fields are purely "transport" related. I want to touch this "communication" between the client and the mysql server with my hands - to catch these frames with a weirshark and read the headers - Dimon