About Mokhtar Ebrahim

MySQL On Linux (Beginners Tutorial)

In this post, we will cover many aspects of MySQL on Linux. First of all, how to install it, how to perform basic CRUD operations, how to import & export data, play with the MySQL engine itself such as setting the root user password and much more.

MySQL is one of the most popular relational databases engines in the world. It has earned its fame for being open source and quite stable.

It is also compatible with most known programming languages. Of course, it’s possible to install it and use it on most Linux distributions that exist, for example, Ubuntu and CentOS. Let’s get started.

Install MySQL on Linux Ubuntu and CentOS

The first step to use MySQL on Linux is obviously to install it in our system.

Ubuntu and CentOS are two of the most used Linux distributions. In the case of Ubuntu, it is quite popular among novices who come from other operating systems like Windows.

Recently, Canonical; the company behind the development of Ubuntu has been profiling Ubuntu to be used on servers.

On the other hand, CentOS was born as a clone of Red Hat Enterprise Linux and it has always been used in the servers area as its main objective.

So if you use Ubuntu, you can install MySQL by typing this command in a terminal session:

1

$ sudo apt install mysql-server

Then, after typing the user’s password, it will start the download and subsequent installation.

On the other hand, CentOS does not include MySQL by default in its repositories. However, it is easy to add MySQL repository to install it. First, switch to the root user and then, add the repository.

You can edit its content using a text editor such as nano or vim. In the case of Ubuntu, most of the configurations are in /etc/mysql/conf.d/

Remember that if you are going to edit it, but it is recommended to create a backup of the file first.

Start MySQL & enable on startup

Now MySQL is installed, but still not running. So how to start MySQL?

Since previous versions of Linux, systemd is in charge of managing the system services. As we may already know, MySQL is a system service.

To start running the MySQL service, type the following command in the terminal:

1

$ sudo systemctl start mysql

If you are using CentOS, use this command as the root user:

1

$ systemctl start mysqld

As we can see, no output. That means the service started successfully.

If you reboot the system, you will notice that MySQL is not running. So we need to enable it to run on system startup.

To do this, use the systemctl command but add the enable option:

1

$ sudo systemctl enable mysql

In the case you are using CentOS:

1

$ systemctl enable mysqld

Now if you reboot the system, you will note that MySQL is running. Let’s see how to ensure that.

Check if MySQL is running

You can check if MySQL is running or not using the systemctl command but adding the status option:

1

$ sudo systemctl status mysql

For CentOS, the command should run as root user and as follows:

1

$ systemctl status mysqld

The command generates a screen output with a green signal indicating that the service is running.

Otherwise, it appears in gray indicating that it is inactive or stopped.

Check which port MySQL is running

An important part of managing MySQL service is knowing which port the instance is running on. By default, the port used by MySQL is 3306, however, it is always advisable to check before starting work.

To check which port is used by MySQL on Linux, you can use one of these two commands:

1

$ lsof -n | grep'mysql.*TCP'

Or, you can use this one too:

1

$ netstat -tlpn | grepmysql

Anyway, it is pretty useful to know which port is used by MySQL in order to open that port in case you are using a firewall like iptables or CSF firewall.

Restart MySQL

If you make any changes to MySQL settings they will not become effective until you reboot MySQL or restart the MySQL service.

To restart MySQL just use the following command:

1

$ sudo systemctl restart mysql

For CentOS use this one as the root user:

1

$ systemctl restart mysqld

If the command does not generate any screen output, it means that the service was successfully restarted.

Set root password for MySQL

The MySQL root user is the one who has all the privileges over all databases. So it is a must to define a very strong password for this user.

Some of the operations that the root user can do are to create new users. As well as to manage the permissions of these.

To do this, we will use the mysql_secure_installation script where we can not only define the password of the root user, but we can also add other configurations.

So, in Ubuntu run the following command to use the mysql_secure_installation script.

1

nbsp; sudo mysql_secure_installation

On CentOS, the command is the same but run it as the root user.

1

$ mysql_secure_installation

There the first question will be about to activate the Validate Password plugin. It is a good idea to enable it. Then, choose the level of a password validation policy.

Next, we can set the root user password:

After that, the plugin will evaluate the strength of the password. Type Y to continue.

Then, you will be asked for other configuration questions that you have to answer. The answers depend on each particular case.

Congratulations! The root password has been correctly defined.

Check MySQL version

MySQL is an application with quite active development. Besides this, the large Linux distributions constantly send security patches. So it is good to know which version of MySQL is installed.

On the other hand, on the internet, they often make publications about discovered security vulnerabilities and which versions are affected.

By knowing which version we have, we will be able to know if we have any vulnerabilities and take precautions.

To do this, simply run the following command:

1

$ mysql --version

There you can see the current version that is installed. So that you can update it or keep it according to our needs.

Create a user on MySQL

Working with MySQL is a delicate matter, not because of the complexity of the service itself, but because of the security policies, we have to implement to protect the data.

A basic security policy is to create specific users for each application or database.

To do this, you have to first access the MySQL shell as the root user:

1

$ sudo mysql -u root -p

On CentOS:

1

$ mysql -u root -p

When you run the command, you will be asked for the MySQL root user password. Then, you will be able to use MySQL commands.

To create a new user run the following sentence:

1

> CREATE USER 'user'@'localhost'IDENTIFIED BY 'password';

Replace ‘user’ with the username of your choice. ‘localhost’ is the host where you will create the user. And in ‘password’ enter the password for this new user. Similarly, it is recommended that it be a strong password.

Grant all privileges

The newly created user does not have any privilege on any database. In fact, you would not be able to access the MySQL shell either. Then, the root user has to grant certain privileges to this new user.

There are several privileges and their assignment will depend on the server administrator. Some of them are:

ALL PRIVILEGES: Assigns all permissions on the chosen database.

CREATE: This privilege allows you to create new tables or databases.

DROP: In this case, it allows you to delete tables and databases.

DELETE: It allows deleting records from the tables.

INSERT: With this privilege, you can create records in the tables.

SELECT: This privilege is required to read the records from the tables.

UPDATE: It allows you to update the records of a table.

GRANT OPTION: With this privilege, the user can remove privileges from certain users.

When running the command, you will be prompted for the MySQL root user password.

Also, remember that this command must be run by the root user of the system or use sudo. Finally, it is possible to choose the path of the output file.

Backup database

MySQL has a tool called mysqldump that allows you to make backups of the databases in a MySQL instance. It is a very useful and agile tool.

Making a backup is similar to exporting a database as we have seen before. However, it is also possible with this tool to make a backup of all existing databases.

For it, it is enough to run the following command as a root user or with sudo:

1

$ sudo mysqldump -u root -p --all-databases > alldatabases.sql

Make MySQL case insensitive

When developing independent applications, the names of the tables are usually a problem. By default, MySQL is case sensitive in table names.

Therefore, in some cases, it is required to revert this behavior.

To make this change, it is necessary to add a directive in the MySQL configuration file. First, stop the MySQL service.

1

$ sudo systemctl stop mysql

If we use CentOS, the command is as follows:

1

$ systemctl stop mysqld

After this, edit the following specific configuration file. So open it with root user permissions:

On Ubuntu:

1

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

If we use CentOS, the path will be different. The path will be /etc/my.cnf.

Then, under the section [mysqld] add the following directive:

1

lower_case_table_names = 1

Then save the changes and close the file.

Next, we need to start the MySQL service again.

1

$ sudo systemctl start mysql

Or on CentOS:

1

$ systemctl start mysqld

And that is enough. You can create tables with upper and lower case and MySQL will treat them the same way.

Where are MySQL logs on Linux?

Log files are sequential records of all events related to a particular program or system service.

It is very important to check them from time to time in search for any abnormal behaviors in the execution of the program. Also, when the MySQL service fails or cannot start, the error is recorded directly in the log.

On Ubuntu, the MySQL logs are saved in /var/log/mysql/error.log.

In the case of CentOS, the file is located in /var/log/mysqld.log.

To review the contents of the file you can use the tail command. For example:

1

$ tail /var/log/mysql/error.log

Or, if you use CentOS:

1

$ tail /var/log/mysqld.log

By running it, you will be able to see everything that has happened with the service.

Also, you can use -f option with the tail command to continuously see the latest updates for the log records.

1

$ tail -f /var/log/mysqld.log

Uninstall MySQL

In case you want to uninstall MySQL from Linux, just invoke the package manager of each distribution. So it is not complicated.

In the case of Ubuntu, we use the APT package manager. To uninstall it, the following command is enough:

1

$ sudo apt remove mysql

On CentOS, the package manager is YUM, so as the root user, use the following command:

1

$ yum remove mysql-community-server

Conclusion

MySQL is a very popular relational database. Its power & ease of use as we saw makes it the favorite engine for a large number of developers. However, in this post, we have covered some basics of MySQL on Linux.

Newsletter

Join them now to gain exclusive access to the latest news in the sysadmin and OS developers world, as well as insights about IoT, embedded programming, operating systems, scripting languages and other related technologies.

Join Us

With 1,240,600 monthly unique visitors and over 500 authors we are placed among the top OS resources and development sites around. Constantly being on the lookout for partners; we encourage you to join us. So If you have a blog with unique and interesting content then you should check out our SCG partners program. You can also be a guest writer for System Code Geeks and hone your writing skills!