Linux run mysql server

Sergey Danielyan

Правильная установка и настройка MySQL в Linux

Эта статья, по замыслу, должна служить пошаговым руководством по корректной настройки MySQL сервера в Linux в общем и в CentOS в частности, начиная от подготовки системы и заканчивая настройкой прав пользователей.
В этот раз текста будет минимум — только команды.

Установка MySQL

Проверяем, установлен ли MySQL сервер

Если установлен, шаги по установке можете пропустить, хотя ознакомиться я все же советую с ними.

Существуют следующие основные пакеты связанные с mysql:

• mysql — клиент mysql
• mysql-server — сервер mysql
• mysql-devel — для разработки и подключения библиотек и хидеров mysql
• mysql-connector-java — JDBC коннектор (используется, например, в EJBCA)

sudo yum install mysql mysql-server mysql-devel mysql-connector-java

Теперь надо установить сервер mysql на запуск в определенные runlevel‘ы (2, 3 и 5):

Если кто забыл соответствие цифрового значения runlevel‘а символьному:

Стартуем демон mysql:

Настройка сервера

Теперь пора настроить сервер. Начнем с пользователей.

Вот состояние таблицы user до начала действий с ней:

mysql -u root
> use mysql
> select host,user from user;

5 rows in set (0.00 sec)
> quit

Как видете, безопасность на уровне плинтуса. Хорошо хоть, что анонимного пользователя нет.

Для настройки базовых вещей в сервере, запустим настройку сервера через mysql_secure_installation. На время этой установки, пароль будет security. Ваш же пароль, как понимаете, должен отличаться.

Запустится скрипт, с запросами на то или иное действие. Вот ответы:

Skip root password for root
Мы еще не устанавливали пароль для root, поэтому при запуске скрипта и запросе пароля для root , просто нажмите Enter .

Install new password for root: security
А вот тут можно установить пароль для root

Do remove an anonymous user
На вопрос о том, удалить ли анонимного пользователя, отвечаем да

Do not disallow remote connections
Не запрещаем коннект к нашему северу с удаленных серверов (если, конечно, эта опция вам нужна, в другом случае, запретите ее)

Do remove a test database
Тестовая база нам не нужна — удаляйте ее

Do reload the privileges
Перегрузим привилегии для их активации

Теперь для всех root пользователей установлен пароль.

Если в ходе этой конфигурации вы не установили пароль для root , можете сделать это так:

SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘security’);
SET PASSWORD FOR ‘root’@’localhost.localdomain’ = PASSWORD(‘security’);
SET PASSWORD FOR ‘root’@’127.0.0.1’ = PASSWORD(‘security’);

UPDATE mysql.user SET Password = PASSWORD(‘security’) WHERE user = ‘root’;

Если же вы не запускали конфигурацию через mysql_secure_installation или не хотите этого делать по каким-то другим причинам, следующие команды удалят any пользователей:

DROP USER »@’localhost’;
DROP USER »@’localhost.localdomain’;

Также, пароль для IPv6 localhost (@::1) можно установить таким образом:

Близится финал нашего действия. Осталось две вещи:

• открыть порты для mysql:

sudo iptables -I INPUT -p tcp —dport 3306 -m state —state NEW,ESTABLISHED -j ACCEPT
sudo iptables -I OUTPUT -p tcp —sport 3306 -m state —state ESTABLISHED -j ACCEPT

выставить кодировку UTF-8 по-умолчанию — файл /etc/my.cnf :

[mysqld]
init_connect=‘SET collation_connection = utf8_unicode_ci’
character-set-server = utf8
collation-server = utf8_unicode_ci

[client]
default-character-set = utf8

Источник

Linux run mysql server

Last updated on: 2019-12-20

Authored by: Jered Heeschen

MySQL is an open-source relational database that is free and widely used. It is a good choice if you know that you need a database but don’t know much about all the available options.

This article describes a basic installation of a MySQL database server on the Ubuntu operating system. You might need to install other packages to let applications use MySQL, like extensions for PHP. Check your application documentation for details.

Install MySQL

Install the MySQL server by using the Ubuntu operating system package manager:

The installer installs MySQL and all dependencies.

If the secure installation utility does not launch automatically after the installation completes, enter the following command:

This utility prompts you to define the mysql root password and other security-related options, including removing remote access to the root user and setting the root password.

Allow remote access

If you have iptables enabled and want to connect to the MySQL database from another machine, you must open a port in your server’s firewall (the default port is 3306). You don’t need to do this if the application that uses MySQL is running on the same server.

Run the following command to allow remote access to the mysql server:

Start the MySQL service

After the installation is complete, you can start the database service by running the following command. If the service is already started, a message informs you that the service is already running:

Launch at reboot

To ensure that the database server launches after a reboot, run the following command:

Configure interfaces

MySQL, by default is no longer bound to ( listening on ) any remotely accessible interfaces. Edit the “bind-address” directive in /etc/mysql/mysql.conf.d/mysqld.cnf:

Restart the mysql service.

Start the mysql shell

There is more than one way to work with a MySQL server, but this article focuses on the most basic and compatible approach, the mysql shell.

At the command prompt, run the following command to launch the mysql shell and enter it as the root user:

When you’re prompted for a password, enter the one that you set at installation time, or if you haven’t set one, press Enter to submit no password.

The following mysql shell prompt should appear:

Set the root password

If you logged in by entering a blank password, or if you want to change the root password that you set, you can create or change the password.

For versions earlier than MySQL 5.7, enter the following command in the mysql shell, replace password with your new password:

For version MySQL 5.7 and later, enter the following command in the mysql shell, replacing password with your new password:

To make the change take effect, reload the stored user information with the following command:

Note: We’re using all-caps for SQL commands. If you type those commands in lowercase, they’ll work. By convention, the commands are written in all-caps to make them stand out from field names and other data that’s being manipulated.

If you need to reset the root password later, see Reset a MySQL root password.

View users

MySQL stores the user information in its own database. The name of the database is mysql. Inside that database the user information is in a table, a dataset, named user. If you want to see what users are set up in the MySQL user table, run the following command:

The following list describes the parts of that command:

• SELECT tells MySQL that you are asking for data.
• User, Host, authentication_string tells MySQL what fields you want it to look in. Fields are categories for the data in a table. In this case, you are looking for the username, the host associated with the username, and the encrypted password entry.
• FROM mysql.user » tells MySQL to get the data from the mysql database and the user table.
• A semicolon (;) ends the command.

Note: All SQL queries end in a semicolon. MySQL does not process a query until you type a semicolon.

User hosts

The following example is the output for the preceding query:

Users are associated with a host, specifically, the host from which they connect. The root user in this example is defined for localhost, for the IP address of localhost, and the hostname of the server. You usually need to set a user for only one host, the one from which you typically connect.

If you’re running your application on the same computer as the MySQL server, the host that it connects to by default is localhost. Any new users that you create must have localhost in their host field.

If your application connects remotely, the host entry that MySQL looks for is the IP address or DNS hostname of the remote computer (the one from which the client is coming).

Anonymous users

In the example output, one entry has a host value but no username or password. That’s an anonymous user. When a client connects with no username specified, it’s trying to connect as an anonymous user.

You usually don’t want any anonymous users, but some MySQL installations include one by default. If you see one, you should either delete the user (refer to the username with empty quotes, like ‘ ‘) or set a password for it.

Create a database

There is a difference between a database server and a database, even though those terms are often used interchangeably. MySQL is a database server, meaning it tracks databases and controls access to them. The database stores the data, and it is the database that applications are trying to access when they interact with MySQL.

Some applications create a database as part of their setup process, but others require you to create a database yourself and tell the application about it.

To create a database, log in to the mysql shell and run the following command, replacing demodb with the name of the database that you want to create:

After the database is created, you can verify its creation by running a query to list all databases. The following example shows the query and example output:

Add a database user

When applications connect to the database using the root user, they usually have more privileges than they need. You can add users that applications can use to connect to the new database. In the following example, a user named demouser is created.

To create a new user, run the following command in the mysql shell:

When you make changes to the user table in the mysql database, tell MySQL to read the changes by flushing the privileges, as follows:

Verify that the user was created by running a SELECT query again:

Grant database user permissions

Right after you create a new user, it has no privileges. The user can log in, but can’t be used to make any database changes.

Give the user full permissions for your new database by running the following command:

Flush the privileges to make the change official by running the following command:

To verify that those privileges are set, run the following command:

MySQL returns the commands needed to reproduce that user’s permissions if you were to rebuild the server. USAGE on \*.\* means the users gets no privileges on anything by default. That command is overridden by the second command, which is the grant you ran for the new database.

Summary

If you’re just creating a database and a user, you are done. The concepts covered here should give you a solid start from which to learn more.

Related articles

Share this information:

©2020 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License

Источник

MySQL

MySQL is a fast, multi-threaded, multi-user, and robust SQL database server. It is intended for mission-critical, heavy-load production systems and mass-deployed software.

Installation

To install MySQL, run the following command from a terminal prompt:

Once the installation is complete, the MySQL server should be started automatically. You can quickly check its current status via systemd:

The network status of the MySQL service can also be checked by running the ss command at the terminal prompt:

When you run this command, you should see something similar to the following:

If the server is not running correctly, you can type the following command to start it:

A good starting point for troubleshooting problems is the systemd journal, which can be accessed at the terminal prompt with this command:

Configuration

You can edit the files in /etc/mysql/ to configure the basic settings – log file, port number, etc. For example, to configure MySQL to listen for connections from network hosts, in the file /etc/mysql/mysql.conf.d/mysqld.cnf , change the bind-address directive to the server’s IP address:

Replace 192.168.0.5 with the appropriate address, which can be determined via ip address show .

After making a configuration change, the MySQL daemon will need to be restarted:

Database Engines

Whilst the default configuration of MySQL provided by the Ubuntu packages is perfectly functional and performs well there are things you may wish to consider before you proceed.

MySQL is designed to allow data to be stored in different ways. These methods are referred to as either database or storage engines. There are two main engines that you’ll be interested in: InnoDB and MyISAM. Storage engines are transparent to the end user. MySQL will handle things differently under the surface, but regardless of which storage engine is in use, you will interact with the database in the same way.

Each engine has its own advantages and disadvantages.

While it is possible, and may be advantageous to mix and match database engines on a table level, doing so reduces the effectiveness of the performance tuning you can do as you’ll be splitting the resources between two engines instead of dedicating them to one.

MyISAM is the older of the two. It can be faster than InnoDB under certain circumstances and favours a read only workload. Some web applications have been tuned around MyISAM (though that’s not to imply that they will slow under InnoDB). MyISAM also supports the FULLTEXT data type, which allows very fast searches of large quantities of text data. However MyISAM is only capable of locking an entire table for writing. This means only one process can update a table at a time. As any application that uses the table scales this may prove to be a hindrance. It also lacks journaling, which makes it harder for data to be recovered after a crash. The following link provides some points for consideration about using MyISAM on a production database.

InnoDB is a more modern database engine, designed to be ACID compliant which guarantees database transactions are processed reliably. Write locking can occur on a row level basis within a table. That means multiple updates can occur on a single table simultaneously. Data caching is also handled in memory within the database engine, allowing caching on a more efficient row level basis rather than file block. To meet ACID compliance all transactions are journaled independently of the main tables. This allows for much more reliable data recovery as data consistency can be checked.

As of MySQL 5.5 InnoDB is the default engine, and is highly recommended over MyISAM unless you have specific need for features unique to the engine.

Advanced configuration

Creating a tuned configuration

There are a number of parameters that can be adjusted within MySQL’s configuration files that will allow you to improve the performance of the server over time.

Many of the parameters can be adjusted with the existing database, however some may affect the data layout and thus need more care to apply.

First, if you have existing data, you will need to carry out a mysqldump and reload:

This will then prompt you for the root password before creating a copy of the data. It is advisable to make sure there are no other users or processes using the database whilst this takes place. Depending on how much data you’ve got in your database, this may take a while. You won’t see anything on the screen during this process.

Once the dump has been completed, shut down MySQL:

It’s also a good idea to backup the original configuration:

Next, make any desired configuration changes.

Then delete and re-initialise the database space and make sure ownership is correct before restarting MySQL:

The final step is re-importation of your data by piping your SQL commands to the database.

For large data imports, the ‘Pipe Viewer’ utility can be useful to track import progress. Ignore any ETA times produced by pv, they’re based on the average time taken to handle each row of the file, but the speed of inserting can vary wildly from row to row with mysqldumps:

Once that is complete all is good to go!

This is not necessary for all my.cnf changes. Most of the variables you may wish to change to improve performance are adjustable even whilst the server is running. As with anything, make sure to have a good backup copy of config files and data before making changes.

MySQL Tuner

MySQL Tuner connects to a running MySQL instance and offer configuration suggestions to optimize the database for your workload. The longer the server has been running, the better the advice mysqltuner can provide. In a production environment, consider waiting for at least 24 hours before running the tool. You can install mysqltuner from the Ubuntu repositories:

Then once its been installed, run:

and wait for its final report. The top section provides general information about the database server, and the bottom section provides tuning suggestions to alter in your my.cnf. Most of these can be altered live on the server without restarting; look through the official MySQL documentation (link in Resources section) for the relevant variables to change in production. The following example is part of a report from a production database showing potential benefits from increasing the query cache:

It goes without saying that performance optimization strategies vary from application to application. So for example, what works best for WordPress might not be the best for Drupal or Joomla. Performance can be dependent on the types of queries, use of indexes, how efficient the database design is and so on. You may find it useful to spend some time searching for database tuning tips based on what applications you’re using. Once you’ve reached the point of diminishing returns from database configuration adjustments, look to the application itself for improvements, or invest in more powerful hardware and/or scaling up the database environment.

Resources

See the MySQL Home Page for more information.

Full documentation is available in both online and offline formats from the MySQL Developers portal

For general SQL information see the O’Reilly books Getting Started with SQL: A Hands-On Approach for Beginners by Thomas Nield as an entry point and SQL in a Nutshell as a quick reference.

The Apache MySQL PHP Ubuntu Wiki page also has useful information.

Источник