How is MySQL 5 installed on Linux

ubuntuusers.de

This article has been tested on the following versions of Ubuntu:


Would you like to test the article for another Ubuntu version? Participation in the wiki is always welcome! For this purpose, the instructions for testing articles must be observed.

MySQL 🇩🇪 is a powerful database server application. The company MySQL AB, which is behind the development of the database, was taken over by Sun in 2008 and has been part of this company since Sun was taken over by Oracle. In addition to Linux, MySQL is also available for Windows, MacOS, FreeBSD, Solaris and other operating systems.

MySQL is very often used for web applications together with the web server Apache and PHP.

The MySQL database server is available in a free community version (which is available from Ubuntu sources) and a paid enterprise version, which first contains bug fixes and new functions. Since MySQL became part of Orcale, the differences between the free and the paid version have increased to the displeasure of the community. Details can be found in the Wikipedia article on MySQL.

As an alternative to MySQL, there is the API-compatible MariaDB, which other Linux distributions are already using as a replacement. With Ubuntu - in contrast to many other Linux distributions - MySQL is still installed via the package and not the MariaDB server.

Installation¶

MySQL server¶

MySQL can be installed directly from Ubuntu's package sources. All you need is the package

Package list to copy:

sudo apt-get install mysql-server

Or install with apturl, Link: apt: // mysql-server

installed from the package management [1] become. With Ubuntu 16.04 and 18.04 the database is installed in version 5.7.

PHP¶

If you want to set up a database-based web application with PHP and MySQL (e.g. a CMS or a picture gallery), you still need the package:

Package list to copy:

sudo apt-get install php-mysql

Or install with apturl, Link: apt: // php-mysql

Users and passwords¶

MySQL user¶

It should be noted that the MySQL user administration has nothing to do with the local operating system users of the Linux system. MySQL manages its users itself. If there are overlapping names (which is especially the case with the user), it is important to pay attention to which user is being referred to.

root¶

An account is automatically created when the server is installed. This is used for the administration of the server and is therefore similar in meaning to the Linux root user.

debian-sys-maint¶

Ubuntu creates another user:. With the help of this MySQL user, Ubuntu starts and stops the SQL server and carries out other maintenance tasks, e.g. during updates. The user can therefore also be used for administrative tasks.

Access is password-protected. Ubuntu assigns a preset password that is stored in the /etc/mysql/debian.cnf is stored in plain text. There are two identical sections in each of which the password can be read.

further users¶

For security reasons, just like with operating system users, it is often advisable not to use the existing system accounts for regular work, but to use different MySQL user accounts for different purposes of the database service. If, for example, a configuration file of a php application can be read out due to a security gap, then only the access data of the restricted MySQL user are located there, who then only has access to a single database in the best case. This means that the system tables and all other databases are still protected from access.

Passwords¶

Every MySQL user account should be protected with a password to prevent unauthorized login.

Set / change passwords¶

If a password has not yet been assigned for a user account or an existing password is to be changed, this can be done with the command line tool mysqladmin can be made afterwards.

mysqladmin -u USERNAME -p password DESIRED PASSWORD

The old password is requested in each case. If this was previously empty, you can simply confirm with ⏎.

Special features of the root password¶

As already mentioned, the password for the MySQL root user is normally requested when installing the MySQL server. If, for whatever reason, this does not happen (as is the case for Ubuntu 18.04, for example) or if no password was specified when prompted, the auth_socket authentication plug-in is used for the user 'root' @ 'localhost'. With a root shell, mysql can then be used without a password, because the authorization is controlled by the calling Linux user. However, this means that it is no longer possible to log in as MySQL root outside of a root shell. This means that every call of MySQL command line programs or other connections to the server from outside as MySQL root must take place in the Linux root context.

If you want to set an independent password for the MySQL user root again, you have to use

Log on to the MySQL server in a root shell and enter the following commands at the MySQL prompt:

ALTER USER 'root' @ 'localhost' IDENTIFIED WITH mysql_native_password BY 'NewPassword'; FLUSH privileges; QUIT;

Alternatively, a MySQL command prompt using the debian-sys-maint user can be used to execute the MySQL commands.

By specifying, the MySQL's own password is used again and other Linux users can also access it.

For more ways to reset a forgotten password, see Troubleshooting at the end of the article.

Configuration¶

MySQL is via the file /etc/mysql/mysql.conf.d/mysqld.cnf configured. Usually, however, no changes need to be made. Using an editor [3] you can activate functions or make settings. The most important options are briefly presented here.

Data path¶

MySQL saves the databases in the directory / var / lib / mysql from. If you want to define a different path, you can do this using the option

datadir = / var / lib / mysql

to reach. Please note that the directory should belong to the user or group so that the MySQL server can also write there. More about users and groups can be found in the article Users and Groups.

If the MySQL server cannot be started after this, it is probably due to the profile settings of AppArmor. If the database directory is changed in the configuration file but not adjusted in the AppArmor profile, MySQL can no longer access all databases or parts of existing databases.

This can be remedied by adapting the file /etc/apparmor.d/local/usr.sbin.mysqld. Add the following lines to the end of the file:

/ other_disk / mysql / r, / other_disk / mysql / ** rwk,

Then AppArmor and then MySQL must first be restarted.

MySQL over the network¶

After the installation, the MySQL server is configured in such a way that it can only be reached via localhost - i.e. from the local computer. If the server is to be addressable via the network, the internal IP address of the server can be entered in the configuration file:

# Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure.bind-address = 192.168.0.10

For example, the MySQL server can also be addressed by other computers on the LAN. MySQL can be reached via port 3306 by default. This can also be changed in the configuration file.

If all network interfaces are to be accessed, bind-address must be configured. Alternatively, the entry can also be commented out.

Furthermore, it must be stored in the user rights for each user whether this is allowed to log in via the network.

Service¶

Only a brief glimpse into the use of MySQL is given here. A very extensive documentation is available online in the form of the reference manual 🇬🇧.

Create database¶

To create a new database, first log in as the database administrator:

The following SQL commands are required at the MySQL command prompt. If the execution is successful, you will receive a response in the form:

Query OK, ... rows affected (... sec)
  • Create database (in practice the database name often corresponds to that of the desired application):

    create database if not exists database name;
  • Create a new database user with the password (if the database is to be used as part of a LAMP server):

    create user 'www-data' @ 'localhost' identified by 'secret'; grant usage on *. * to 'www-data' @ 'localhost' identified by 'secret';
  • Allow the user to access the new database:

    grant all privileges on database name. * to 'www-data' @ 'localhost'; flush privileges;
  • Sign out with:

    Alternatively can be used.

Table storage types¶

MySQL knows different types of table storage, the so-called storage engines 🇬🇧. These each have specific advantages and disadvantages. The type of table can be specified when creating it using the option. Different types of memory can be mixed within a database.

InnoDB¶

InnoDB is the standard storage type of MySQL and - unless explicitly stated otherwise - is used by default. It is an almost universally usable type of storage, which offers transaction security and supports foreign keys (so-called "constraints"). If data consistency is very important, InnoDB is usually used as the engine.

MyISAM¶

MyISAM was the standard table type of MySQL up to version 5.5. MyISAM tables are very efficient and fast, especially for large amounts of data with many operations without frequent changes to the data. However, MyISAM tables are not transaction-safe and do not support foreign keys. Another old advantage over InnoDB, the ability to use full-text indexes, has been offset by InnoDBs since version 5.6.

Other table types¶

As mentioned above, MySQL supports a number of other table types, with InnoDB and MyISAM certainly being the most commonly used. Tables based on the "memory" type of storage are also worth mentioning. These are kept exclusively in the main memory (RAM) of the server and are of course correspondingly fast. Memory tables are preferably used for temporary tables or for table copies with frequent access that are only read-only.

Troubleshooting¶

Forgot root password¶

If you have forgotten the password for the MySQL root user, there are several ways to reset it. The following variant is available under Ubuntu and should be used.

By

mysql -u debian-sys-maint -p

log on to the MySQL server as a debian-sys-maint user and then enter the following commands at the MySQL prompt:

ALTER USER 'root' @ 'localhost' IDENTIFIED WITH mysql_native_password BY 'NewPassword'; FLUSH privileges; QUIT;

There are other ways to reset the root password, but these shouldn't be necessary under Ubuntu. If so, you can look it up in the MySQL documentation 🇬🇧.

Reset password for debian-sys-maint¶

Appears when (re) starting the MySQL server

sudo systemctl restart mysql.service

following error message:

* Stopping MySQL database server mysqld [fail] * Starting MySQL database server mysqld [OK] / usr / bin / mysql-admin: connect to server at 'localhost' failed error: 'Access denied for user' debian-sys-maint '@ 'localhost' (using password: YES) '

you have to get the password for the user debian-sys-maint reset. This can be the case if Ubuntu is during an upgrade of the package mysql-server the password for overwrites. In this case you have to log in as a user on the MySQL server and enter the following command sequence at the MySQL command prompt. By entering the password in the file /etc/mysql/debian.cnf replace.

grant all privileges on *. * to 'debian-sys-maint' @ 'localhost' identified by 'newpassword' with grant option; flush privileges; quit;

Data backup¶

There are different strategies how you can carry out (regular) data backups. More information can be found in the sub-article MySQL / Backup.

Graphical interfaces¶

There are a number of graphical user interfaces that facilitate the use of MySQL. More information can be found in the article MySQL / Tools.

Left¶

This revision was created on May 8, 2021 06:35 by frustschieber.
The following keywords were assigned to the article: database, internet, server, network