MySQL is one of the most popular relational database management systems (RDMS) available. While it's an ideal RDMS for both small and large-scale applications, being free and open-source (before Oracle acquired it) made it even more popular and versatile.
If you are running web applications or database applications on linux servers, then most likely you are using mysql or its open source fork, mariadb.
MariaDB is a forked version of MySQL.
MySQL has some auxiliary components with a proprietary license after the Oracle acquisition, but MariaDB maintains a commitment to remain open-source and is also released under the gpl.
There are many free third-party applications that help mysql users interact with their databases and their data very easily. These include phpmyadmin, mysql workbench etc.
MySQL is widely used in a variety of applications, such as web applications, banking applications, data warehousing, ecommerce, and more, due to its reliability and performance. Furthermore, with the large open-source community of active developers, any issues, bugs, or updates ensure continuous improvements to mysql promptly and seamlessly.
Update the system
The first step is to update the system and all installed packages. Keeping your system packages updated is a crucial task for system administration. This ensures we are installing the packages on a system with uptodate packages and necessary security and bug patches installed.
$ sudo apt update
The command will fetch the package information from all configured sources listed in your '/etc/apt/sources.list' file and '/etc/apt/sources.list.d' directory.
Once the local package index of your system is refreshed, you can upgrade the installed packages. This process ensures that each software package on your computer is updated with the latest versions available in the repositories.
$ sudo apt upgrade
Once the packages are fully updated, you can proceed to the installation of mysql server and mysql client.
Install MySQL on Ubuntu 23.04
In recent versions of ubuntu, you don't have to add the repositories by oracle. You can simply use the apt package manager to install the package named 'mysql-server'.
$ sudo apt install mysql-server
$ sudo systemctl start mysql.service
$ sudo systemctl enable mysql.service
$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor >
Active: active (running) since Fri 2023-06-16 19:22:44 UTC; 13h ago
Process: 57025 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre>
Main PID: 57033 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 1116)
Memory: 363.0M
CPU: 5min 34.369s
CGroup: /system.slice/mysql.service
└─57033 /usr/sbin/mysqld
Jun 16 19:22:43 mysqsrv01 systemd[1]: Starting MySQL Community Server...
Jun 16 19:22:44 mysqsrv01 systemd[1]: Started MySQL Community Server.
lines 1-14/14 (END)
Once mysql server is running, it opens the port 3306 for communication by default. This can be verified with the netstat command.
$ sudo netstat -nltp | grep -i mysql tcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN 1387/mysqld tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 1387/mysqld $
It is better to check and install mysql client if that's not installed already.
$ sudo apt install mysql-client
The mysql-client program allows various programming languages to communicate with the mysql server.
You can check the mysql version with the -V options like this:
$ mysql -V mysql Ver 8.0.34-0ubuntu0.23.04.1 for Linux on x86_64 ((Ubuntu)) $
Installing MySQL on Debian 11
Installing MySQL in Debian is a straightforward process. Before starting the installation, make sure you have a user account with sudo privileges and that your system is up-to-date. Here are the steps you need to follow:
Note: You can follow the same steps in Debian.
$ sudo apt update $ sudo apt upgrade
Install wget, if not already installed
$ sudo apt install wget
Unlike Ubuntu, MySQL APT repository is not added to Debian 11 by default. Hence, to add it, run the following command.
wget https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb apt install ./mysql-apt-config_0.8.22-1_all.deb
You will need to select 'OK' twice to install MySQL version 8.0.
Make sure to update/refresh the repos once again after adding the repository.
$ sudo apt update $ sudo apt install mysql-server
After a successful installation, you can use the below command to check the status of MySQL.
$ sudo systemctl status mysql.service
Post-Installation Setup
Secure MySQL installation
After installing MySQL, it's recommended to run the security script provided by MySQL. This will help to tighten a few areas that are vulnerable by default.
Due to an update, after July 2022, you can run the script directly. You need to prepare the MySQL root user to accept connections with a password. (This step is not required in Debian)
$ sudo mysql mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; mysql> exit;
You are now good to proceed with the script to secure MySQL installation.
$ sudo mysql_secure_installation
You can always select the default values if you are not sure about the answers.
Log in to the MySQL console
You can interact with your MySQL server and run SQL statements from the MySQL console. You can perform a wide range of operations, including database creation, user administration, data searching, and more, using a text-based interface. Here are the procedures for using Debian 11 and Ubuntu 23.04 to access the MySQL console:
Access the MySQL Console
By default, you should be able to log in to the MySQL root user without a password, using the 'sudo mysql -u root' command. But in our case, we have configured the MySQL root user to accept passwords. Hence, we have to provide the MySQL root user's password to log in to MySQL.
$ sudo mysql -u root -p Enter password: Your MySQL connection id is 22 Server version: 8.0.33-0ubuntu0.22.04.2 (Ubuntu) Copyright (c) 2000, 2023, 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>
This command is broken down as follows:
- sudo - It is required because only the superuser (root) has permission to log in to MySQL by default.
- mysql - It is the command to start the MySQL console.
- -u root - It specifies that you want to log in as the MySQL root user. The root user has full access to all databases and tables in MySQL.
- -p - It tells MySQL to prompt for a password. When you run the command, it will ask you for the root user's password.
As mentioned earlier, the root user of the system can log in to it without -p option.
Running a few SQL commands
Once you have logged into the MySQL console, you can execute SQL commands. Note that every command needs to end with a ';' (semicolon).
An example to show the available databases is as follows:
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) mysql>EXIT;
In MySQL, keywords like SELECT, INSERT, UPDATE, DELETE, WHERE, and others, are case-insensitive, which means they can be written in any mix of uppercase and lowercase letters and they will function identically.
However, the situation is different for identifiers such as table names, column names, aliases, and so on. Their case sensitivity is determined by two factors: the operating system in use and the setting of the lower_case_table_names system variable.
Creating a New MySQL User and Database
In MySQL, it's often advisable to create separate users for different databases and applications, rather than using the root user for everything. This way, you can assign specific permissions and ensure that each application can only access the data it needs.
Here's how you can create a new user and database in MySQL.
Login to the MySQL console.
$ sudo mysql -u root -p
Once the password is provided, you are logged into the MySQL console.
Assuming the database you want to create is 'mydatabase', you can create it with the below command inside the MySQL console.
mysql> CREATE DATABASE mydatabase; Query OK, 1 row affected (0.01 sec)
After creating the user, we can provide the MySQL user with the necessary rights to carry out daily operations. We are creating a user named "myuser" with the hostname "localhost" for this demonstration. The hostname is significant because it indicates the location from which the MySQL user is permitted to access the MySQL console.
You can choose a password of your own. For this demonstration, the password that we are using is 'mypassword' (without quotes).
mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword'; Query OK, 0 rows affected (0.02 sec)
After the user has been successfully created, we can go ahead and provide this user the necessary permissions. In this example, we are giving full permissions to the database we built in the previous step.
By granting only the necessary permissions, we can prevent any malicious or accidental tampering with the database or tables.
mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> EXIT;
You'll have a new MySQL user with full access to the 'mydatabase' database after completing these instructions. These credentials can be used by your applications to connect to MySQL.
Troubleshooting - Common issues and their solutions
MySQL server doesn't start
The MySQL error logs can be checked if MySQL doesn't start when you do 'sudo systemctl start mysql'. The logs, by default, are stored in the '/var/log/mysql/error.log' in both Ubuntu and Debian.
$ sudo tail -f /var/log/mysql/error.log … … … 2023-06-16T19:22:40.954530Z 7 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.33-0ubuntu0.22.04.2). 2023-06-16T19:22:40.961439Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock 2023-06-16T19:22:42.445125Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.33-0ubuntu0.22.04.2) (Ubuntu). 2023-06-16T19:22:43.962315Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.33-0ubuntu0.22.04.2) starting as process 57033 … … …
This error log will contain specific information about the issue that's preventing it from starting. For example, there might not be enough RAM or disk space. Or another service is already using the MySQL port.
Can't connect to the MySQL Server
In case you can't access the MySQL server, check the MySQL service status.
$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2023-06-17 08:59:15 UTC; 10s ago
Process: 63728 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 63736 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 1116)
Memory: 400.9M
CPU: 1.083s
CGroup: /system.slice/mysql.service
└─63736 /usr/sbin/mysqld
Jun 17 08:59:14 mysqlsrv01 systemd[1]: Starting MySQL Community Server...
Jun 17 08:59:15 mysqlsrv01 systemd[1]: Started MySQL Community Server.
If it's not running, you will have to go through the above step to find out its root cause.
If the service is running but you can't log in to the system, you can try the below steps to troubleshoot further.
Check firewall settings
If you are connecting to the MySQL server remotely, the problem could be the outcome of network restrictions. Therefore, it would be wise to look into the local firewall first.
The UFW (Uncomplicated Firewall) is typically installed but not turned on by default.
However, in a production instance, this can be different.
Again, if you're trying to connect remotely, make sure that your MySQL user is allowed to connect from your current host, and the MySQL server is configured to accept remote connections.
Forgot MySQL root password
If you forget the root password, you'll need to reset it. This involves starting the MySQL server with a special option to bypass password authentication and then setting a new password. Since this is outside the scope of the basic installation procedure, it will not be covered in this guide.
Permission issues
If a MySQL user doesn't have the permissions they need, you can use the 'GRANT' command to adjust their privileges, as explained in the "Creating a New MySQL User and Database" section. If you're unsure of what privileges a user has currently, you can check them with the SHOW GRANTS FOR 'username'@'host' command.
mysql> SHOW GRANTS FOR 'myuser'@'localhost'; +----------------------------------------------------------------+ | Grants for myuser@localhost | +----------------------------------------------------------------+ | GRANT USAGE ON *.* TO `myuser`@`localhost` | | GRANT ALL PRIVILEGES ON `mydatabase`.* TO `myuser`@`localhost` | +----------------------------------------------------------------+ 2 rows in set (0.00 sec)
Error establishing a database connection in your application
This error is common in web applications, usually due to incorrect database credentials in your application's configuration file. Double-check the database name, username, and password. If they are correct, the error may be due to MySQL server not running, or network issues between your application and MySQL server.
Remember, when encountering issues that you can't resolve, you can always refer to the official MySQL documentation or search for the error message online. The MySQL user community is quite active, and there are chances someone has encountered the same issue and found a solution.
Other things
Mysql configuration file - my.cnf
Mysql by default is configured to store the database in the /var/lib/mysql directory. You will need root privileges to view the contents of that directory.
There are sub-directories in /var/lib/mysql for each database that you create in mysql.
mysql --help mysqladmin --help
It would display a large chunk of text with the following lines somewhere in between, indicating the path of the configuration file my.cnf.
... Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf ...
Here are some more commands to locate and list the my.cnf configuration file.
$ mysql --help | grep /my.cnf | xargs ls ls: cannot access '/etc/my.cnf': No such file or directory ls: cannot access '~/.my.cnf': No such file or directory /etc/mysql/my.cnf $
Using the locate command. This one will list out a lot of my.cnf files, even other than the actual ones.
$ locate my.cnf /etc/alternatives/my.cnf /etc/mysql/my.cnf /etc/mysql/my.cnf.fallback /var/lib/dpkg/alternatives/my.cnf $
Conclusion
You should now be able to successfully install MySQL on Ubuntu 23.04 or Debian 11/12, as well as carry out basic tasks like creating a new mysql user and database, and troubleshooting common problems.
For administering your database in a more user-friendly manner, use gui or web based tools like phpmyadmin. Languages like php have strong integration with mysql and most of the applications will work without the need for much configuration.
If you use desktop apps like workbench to manage remote mysql servers then that would require some extra configuration for the firewalls on the server to allow remote administration and mysql will need some extra configuration as well.
