What does it mean that MySQL is a server application?

[fog37]

TL;DR Summary

Understand what it means that MySQL is a server application

I would like to practice SQL and install MySQL, one of the most popular open source DMBS. MySQL workbench can also to be installed and used as a visual GUI to interact with MySQL which in turns interacts with the database.

My question: is MySQL a server application? If so, what does it mean? I am familiar with the client server model used on the internet where some computers have server operating systems and run server programs while other machines have installed client operating systems (like Windows 10 or 11) and have client applications (Word, Chrome, etc.)

MySQL is called a server application. Does it mean that is can only be run on a machine that has a server operating system (ex: Apache)? I don't think so. Does it simply mean that someone on a different computer could interact with the database hosted on my computer (which has MySQL server installed) as long as they have a MySQL client program?

If I am the only user of the database, I guess I will not need a MySQL client and will be able to interact with MySQL server directly via MySQL workbench...

Thanks!

 

[jedishrfu]

MySql acts as a server. You client program uses JDBC or ODBC to communicate with it. You application will construct custom SQL that is sent to the MySql server to be run against your database and return results to your client application.

Most client apps send SQL select statements to gather customer info to be used in presenting a web page or interactive screen to the customer. Some more advanced apps might create tables, manage table content...

In the past, I've used the H2 Database server (a competitor to MySql) which comes as a single java jar file. When run, it creates several servers one for tcp, one for web and one for something I can'r remember. Java apps use tcp protocol to send and receive dat from the server and the web server handles web page adminstration of the database. H2 database can also be an embedded server meaning an application can be built with H2 embedded. This gives the application favored status with queries that can run roughly 6 times faster than other application that need to use TCP protocol to connect.

MySql has similar features.

 

[PeterDonis]

is MySQL a server application?

Yes. More precisely, the MySQL server is a server application.

If so, what does it mean?

It means that the MySQL server is not an application that you, as a user, run and interact with directly. It is an application that runs in the background and responds to requests from a client, i.e., some other application. The "client" application does not have to be an application that you or any other user runs directly either; it could, for example, be a web server application that makes use of data stored in a MySQL database, and interacts with the database by making requests to a MySQL server. (This is how many, if not most, database-backed web applications are run, for various reasons that are probably beyond the scope of this discussion.)

I am familiar with the client server model used on the internet where some computers have server operating systems and run server programs while other machines have installed client operating systems (like Windows 10 or 11) and have client applications (Word, Chrome, etc.)

The difference between client and server operating systems is pretty small these days; a server running Linux is running the same Linux that many users' desktops or laptops run, just with some configuration differences and a different set of installed programs. Windows servers might be somewhat more differentiated from Windows desktops or laptops, but even there the difference in the OS itself is not very large.

The main way in which a server differs from a client computer is that a server does not have any users interacting with it directly, unless an administrator has to log into do maintenance or fix some problem.

MySQL is called a server application. Does it mean that is can only be run on a machine that has a server operating system (ex: Apache)?

Apache isn't an operating system, it's a web server program. Some servers that run database backed web applications will have a MySQL server running on the same machine as a web server like Apache; but the underlying operating system is most likely going to be Linux.

However, you can run a MySQL server (or, for that matter, an Apache web server) on any machine, including your own desktop. Developers often do this to test applications before deploying them to production servers visible to the Internet.

Does it simply mean that someone on a different computer could interact with the database hosted on my computer (which has MySQL server installed) as long as they have a MySQL client program?

That in itself wouldn't be enough; both computers would also have to be on the same network, such as your home LAN or your office LAN, behind some kind of firewall. You probably don't want your computer visible to the entire Internet (unless you like having various bots and crawlers constantly trying to break into your machine).

If I am the only user of the database, I guess I will not need a MySQL client and will be able to interact with MySQL server directly via MySQL workbench...

Yes, you could do that. I believe that would mean you would be running a MySQL server on your desktop (or laptop) machine. For just doing your own development and testing that's fine.

 

[PeterDonis]

You client program uses JDBC or ODBC to communicate with it.

That's one possibility, but not the only one. A MySQL server (unless it's embedded inside an application, a feature you mention) typically listens on TCP port 3306 and will respond to any client talking over the TCP socket with the correct protocol (which is described in the MySQL documentation). There are a variety of MySQL client libraries out there and most programming languages have some type of MySQL client support.

 

[jedishrfu]

My knowledge is dated. We primarily used JDBC being a java programmer but I know some C/C++ programmers used ODBC instead which had a few features missing from JDBC I think to do with resultsets and being able to rewind and reread them.

 

[PeterDonis]

We primarily used JDBC being a java programmer but I know some C/C++ programmers used ODBC instead which had a few features missing from JDBC I think to do with resultsets and being able to rewind and reread them.

Both JDBC and ODBC are APIs provided by language runtimes or operating system drivers for interacting with databases. But those APIs are not the same as the protocols that the database servers actually speak. There are many different APIs for interacting with databases, provided by different libraries; but if they are interacting with a MySQL database, they all have to translate their API calls into the same MySQL protocol that they speak to the database server. You don't actually have to use an API or a library at all; you could in principle hand-craft MySQL protocol packets yourself and send them over the wire to the server. After all, that's what the libraries or language runtimes or drivers are doing under the hood.

 

[jedishrfu]

Yes, it depends on the application as to what protocol you use. I was involved in one application where we were using a distributed database to store tickertape stock info and basically let the client application route the data to the respective database nodes having some foreknowledge of what data was stored where.

The advantage was faster update times since the database server didn't need to copy the data from node to node. We got a 2x speed up in updates but sacrificed some update status summary.

I didn't want to get into these kinds of details in a thread like this.

 

[Ibix]

A server is so called because it provides a service to other programs (possibly running on other machines). Essentially servers sit on a machine somewhere and listen for requests from other programs and, when they get such a request, reply with the requested data. So, for example, there's a server somewhere that listens to incoming network traffic, waiting for requests in the form https://www.physicsforums.com/threads/something/. When it receives such a request it goes away and looks in its database for posts in the requested thread (possibly by passing some SQL to a separate SQL server in the process) and sends that back to the requesting computer.

That's different to the situation where you write a program that loads data from disk, where your compiled code directly includes binary instructions to seek information on disk. All that functionality is in one program.

The analogy you are meant to make is with serving staff, for example in a restaurant. They provide a way for you to make requests in a standard form (they give you a menu and you make a request), you may be able to customise your request within limits (you could ask for mash instead of chips, but you can't get Chinese from an Indian), and then they go away and fulfill your request. That may mean actually cooking your food themselves, or it may mean them relaying your instructions in some form (numbers on their order pad, for example) to other servers (the kitchen staff) and returning the result. This is distinct from your kitchen, where you can do everything without any communication with other people.

 

[fog37]

Yes. More precisely, the MySQL server is a server application. It means that the MySQL server is not an application that you, as a user, run and interact with directly. It is an application that runs in the background and responds to requests from a client, i.e., some other application. The "client" application does not have to be an application that you or any other user runs directly either; it could, for example, be a web server application that makes use of data stored in a MySQL database, and interacts with the database by making requests to a MySQL server. (This is how many, if not most, database-backed web applications are run, for various reasons that are probably beyond the scope of this discussion.)The difference between client and server operating systems is pretty small these days; a server running Linux is running the same Linux that many users' desktops or laptops run, just with some configuration differences and a different set of installed programs. Windows servers might be somewhat more differentiated from Windows desktops or laptops, but even there the difference in the OS itself is not very large.

The main way in which a server differs from a client computer is that a server does not have any users interacting with it directly, unless an administrator has to log into do maintenance or fix some problem.Apache isn't an operating system, it's a web server program. Some servers that run database backed web applications will have a MySQL server running on the same machine as a web server like Apache; but the underlying operating system is most likely going to be Linux.

However, you can run a MySQL server (or, for that matter, an Apache web server) on any machine, including your own desktop. Developers often do this to test applications before deploying them to production servers visible to the Internet.That in itself wouldn't be enough; both computers would also have to be on the same network, such as your home LAN or your office LAN, behind some kind of firewall. You probably don't want your computer visible to the entire Internet (unless you like having various bots and crawlers constantly trying to break into your machine).Yes, you could do that. I believe that would mean you would be running a MySQL server on your desktop (or laptop) machine. For just doing your own development and testing that's fine.

Thank you! I appreciate the clarifications.
I get that a "server" is an application (generally hosted on a powerful machine but not necessarily) that can handle requests and provide resources to applications installed on other machines or even to applications installed on the same machine as the server. We can have web servers, mail servers, ftp servers, etc.
"Server applications" can run on a machine that is not running a server operating system... I think Jupyter Notebook, which we discussed in the past, is an example of a server application, i.e. a client-server application: both a server app and a client app are installed on the same machine and we use the client (the browser) to access the server app (I think the server app then accesses the Python interpreter...)

Is a server application sometimes called a "local server" or "localhost"?

In regards to databases, I am planning to create a database using MySQL server (and MySQL workbench for ease) on my Windows 10. I will then set up a small LAN (3 Windows machines including mine).
The other two computers would connect to the database on my machine only if they have a MySQL client software installed on them. Can MySQL Workbench work as a MySQL client or would those two machines need to install an actual MySQL client application?

 

[jack action]

At the base, MySQL is just a program that manages databases. It can be used like any program from the command line. Following is an example on my computer (Linux) where:

• I open MariaDB at the "jack" prompt (A program that branched out of MySQL; very similar);
• The program gives a little introduction and presents a "MariaDB [(none)]" prompt;
• I type the MariaDB command "SELECT 2 + 2;"';
• The program responds with a table where the value "2 + 2" is given as "4";
• The MariaDB prompt comes up again, where I choose to exit the program.

But the program is also designed to be used as a server, where it also listens to a TCP port as @DennisN mentioned in post #4. No need for the command line, a client can connect and send directly its requests, and wait for the responses. Whether the client is on your computer or somewhere else on the Internet is just a matter of who you give access to the TCP port in question.

A tool like MySQL Workbench (which is probably a MySQL client, using the TCP port?) will connect directly to MySQL and make the interaction with MySQL a lot easier than with the command line.

 

[anorlunda]

I open MariaDB at the "jack" prompt

Was it necessary to use SUDO?

 

[pbuk]

Thank you! I appreciate the clarifications.

Good, @PeterDonis's posts here are excellent.

I get that a "server" is an application... Jupyter Notebook, which we discussed in the past, is an example of a server application...we use the client (the browser) to access the server app...

Yes all of this is pretty much correct.

Is a server application sometimes called a "local server" or "localhost"?

If a server application is running on the same machine as its client then it can be called a local server, yes (this is the terminal window titled 'Jupyter Notebook' that comes up when you run Jupyter in Windows).

But "localhost" refers to the machine the client is running on, not to any server software that is running on that machine (so pointing your browser at https://localhost/ connects to a secure web server running on the local computer, connecting a MySQL client to localhost:3306 connects to a MySQL server running on the local computer etc.)

In regards to databases, I am planning to create a database using MySQL server (and MySQL workbench for ease) on my Windows 10.

OK.

I will then set up a small LAN (3 Windows machines including mine).

I don't recommend you do that, keep everything on the same machine unless you want to become an expert in network configuration and security. What would be the point of doing this anyway?

Can MySQL Workbench work as a MySQL client

MySQL Workbench is a MySQL client, you wouldn't describe it as anything else.

 

[jack action]

Was it necessary to use SUDO?

I don't use the command line version often, but only the root user has access to it on my personal computer. Maybe I could have logged in with a MariaDB user and password (which if I have, I don't remember).

 

[jtbell]

At the base, MySQL is just a program that manages databases. It can be used like any program from the command line. Following is an example on my computer (Linux) where:

• I open MariaDB at the "jack" prompt (A program that branched out of MySQL; very similar);
• The program gives a little introduction and presents a "MariaDB [(none)]" prompt;
• I type the MariaDB command "SELECT 2 + 2;"';
• The program responds with a table where the value "2 + 2" is given as "4";
• The MariaDB prompt comes up again, where I choose to exit the program.

View attachment 300704​

Are you sure there isn't a server process running in the background, with the mariadb command being a command-line client program? That's how MySQL works on my Linux system. Here's an example similar to yours, where I use the mysql client as user jtbell to connect to the database music:

jtbell@ubuntu:~$ mysql -u jtbell -p music
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from composer where lastname='haydn';
+-----+----------+-----------+-------------+-----------+-----------+
| ID  | lastname | firstname | nationality | birthyear | deathyear |
+-----+----------+-----------+-------------+-----------+-----------+
| 409 | Haydn    | Joseph    | Austria     |      1732 |      1809 |
| 410 | Haydn    | Michael   | Austria     |      1737 |      1806 |
+-----+----------+-----------+-------------+-----------+-----------+
2 rows in set (0.00 sec)

mysql>

Now I open a second terminal window, get a list of all currently-running processes using the command ps -ef, and pipe the output to the grep command to select all lines that contain the string mysql:

jtbell@ubuntu:~$ ps -ef | grep mysql
mysql        968       1  0 12:25 ?        00:00:02 /usr/sbin/mysqld
jtbell      4483    2823  0 12:33 pts/0    00:00:00 mysql -u jtbell -p music
jtbell      4575    4517  0 12:33 pts/1    00:00:00 grep mysql
jtbell@ubuntu:~$

First you see the server program mysqld, running under user mysql. It started up automatically when I booted the Linux system. Then you see the client program mysql running under my user ID. Finally you see a process for the grep command.

 

[jack action]

Yes, I was simply running off the mysql user. I don't think you can use the MySQL "data manager program" without having a "server process" going on in the background (mine was /usr/sbin/mariadbd). My point was that you don't need an external client program to use MySQL.

The text in red in the following quote is what I was doing. But I'm not sure why I had to use sudo and I think it should be investigated, but it's not a high-priority problem right now on my personal computer.

Running mysqld as root​

MariaDB should never normally be run as the system's root user (this is unrelated to the MariaDB root user). If it is, any user with the FILE privilege can create or modify any files on the server as root.

MariaDB will normally return the error Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root! if you attempt to run mysqld as root. If you need to override this restriction for some reason, start mysqld with the user=root option.

Better practice, and the default in most situations, is to use a separate user, exclusively used for MariaDB. In most distributions, this user is called mysql.

 

[pbuk]

The text in red in the following quote is what I was doing.

No it wasn't, you are getting confused between the MariaDB (or MySQL) server daemon mariadbd (or mysqld) and the command line client which you run as mariadb or mysql - note mysql will probably work even if you are using the mariadb client!

There is never a good reason to run the server as root, however in order to do things like CREATE DATABASE on a MySQL/MariaDB install where there is no DB user with those priviliges (and there is no good reason to have a DB user with those privileges) you need to run the client as root.

Edit: live example demonstrating aliasing of mariadb to mysql:

$ mariadb -V
mariadb  Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

$ mysql -V
mysql  Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

$ mysql
ERROR 1045 (28000): Access denied for user 'pbuk'@'localhost' (using password: NO)

$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.3.34-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

 

[jack action]

No it wasn't, you are getting confused between the MariaDB (or MySQL) server daemon mariadbd (or mysqld) and the command line client which you run as mariadb or mysql

You're right, I recheck the ps command with grep mariadb and I also have 2 other processes with the same STIME - sudo mariadb and mariadb - in addition to the server daemon (/usr/sbin/mariadbd). The user is of course root.