Файл конфигурации mysql linux

How to Set up MySQL server configuration files

You can specify startup options on the command line when you invoke the server (or client), or in an option file. MySQL client programs look for option files at startup and use appropriate options.

By default, the server uses precompiled values for its configuration variables when it runs. However, if the default values are not suitable for your environment, add runtime options to tell the server to use different values to:

• Specify the locations of important directories and files
• Control which log files the server writes
• Override the server’s built-in values for performance-related variables (that is, to control the maximum number of simultaneous connections and the sizes of buffers and caches)
• Enable or disable precompiled storage engines at server startup

You can specify runtime options when the server is started (to change its configuration and behavior) by using command-line options or an option file, or by using a combination of both. Command-line options take precedence over any settings in an option file.

To find out what options your server supports, execute the following at a shell prompt:

Reasons to Use Option Files

When you invoke the server from the command line, you can specify any of the server options listed with the –help option. However, it is more useful to list them in an option file, for several reasons:

• When you put options in a file, you do not need to specify them on the command line each time you start the server. This is more convenient and less error-prone for complex options, such as those used to configure the InnoDB tablespace.
• If a single option file contains all server options, you can see how the server has been configured at a glance.

MySQL programs can access options from multiple option files. Programs look for each of the standard option files and read any that exist. No error occurs if a given file is not found. To use an option file, create it as a plain text file by using an editor. To create or modify an option file, you must have write permission for it. Client programs need only read access.

Option File Groups

Options in option files are organized into groups, with each group preceded by a [groupname] line that names the group. Typically, the group name is the category or name of the program to which the group of options applies. Examples of groups include:

• [client]: Used for specifying options that apply to all client programs. A common use for the [client] group is to specify connection parameters, because typically connections are made to the same server no matter which client program is used.
• [mysql]and [mysqldump]: Used for specifying options that apply to mysql and mysqldumpclients, respectively. Other client options can also be specified individually.
• [server]: Used for specifying options that apply to both the mysqld and mysqld_safeserver programs
• [mysqld], [mysqld-5.6], and [mysqld_safe]: Used for specifying options to different server versions or startup methods

Writing an Option File

Below is a brief example of groups in an option file:

[client] host = myhost.example.com compress [mysql] show-warnings 

To create or modify an option file, the end user must have write permission for it. The server itself needs only readaccess; it reads option files but does not create or modify them.

To write an option in an option file:

• Use the long option format, as used on the command line, but omit the leading dashes.
• If an option takes a value, spaces are allowed around the equal ( = ) sign. This is not true for options specified on the command line.

In the example shown above, note the following: 1. [client]: Options in this group apply to all standard clients. — host: Specifies the server host name — compress: Directs the client/server protocol to use compression for traffic sent over the network.

2. [mysql]: Options in this group apply onlyto the mysql client. — show-warnings: Tells MySQL to show any current warnings after each statement

3. The mysql client uses options from boththe [client] and [mysql] groups, so it would use all three options shown.

Option File Locations

MySQL server looks for files in standard locations. Standard files are different for Linux and Windows: – In Linux, use the my.cnf file. – In Windows, use the my.ini file.

There is no single my.cnf or my.ini file. The server might read multiple my.cnf or my.ini files from different locations. You can view option file lookup locations, the order in which they are read, and groups with options:

shell> mysql –-help . Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf The following groups are read: mysql client . 

The standard option files are as follows: 1. Linux: The file /etc/my.cnf serves as a global option file used by all users. You can create a user-specific option file named .my.cnf in the user’s home directory. If the MYSQL_HOMEenvironment variable is set, it searches for the $MYSQL_HOME/my.cnf file.

2. Windows:Programs look for option files in the following order: my.ini and my.cnf in the Windows C:\ directory, then the C:\Windows(or C:\WinNT) directory. However, because the Windows installation wizard places the configuration file in the directory C:\Program Files\MySQL\MySQL Server , the server also searches this directory in Windows.

3. MySQL command-line programs search for option files in the MySQL installation directory. To view the locations of options file and the order in which they are read, use the mysql command-line client with the –help option. The following command filters the output of the command:

mysqld --help --verbose 2> /dev/null | grep -A1 "Default options" 

Startup Options in an Option File

To specify server options in an option file, indicate the specific options under the [mysqld] or [server]groups.

1. Logging: You can enable logging for your server by turning on the types of logs required. The following options turn on the general query log, the binary log, and the slow query log:

general_log log-bin slow_query_log 

2. Default Storage Engine: You can specify a default storage engine different from InnoDB by using the –default-storage-engineoption.

3. System Variables: You can customize your server by setting server system variable values. For example, to increase the maximum allowed number of client connections and to increase the number of the InnoDB buffer pools from their defaults, set the following variables:

max_connections=200 innodb_buffer_pool_instances=4 

4. Shared Memory: Not enabled by default on Windows. You can turn on shared memory by using the shared-memory option.

5. Named Pipes: To turn on named-pipe support, use the enable-named-pipe option.

Sample Option Files

Linux: The MySQL installation provides a sample configuration file called my-default.cnf. The my-default.cnf sample option file is in /usr/share/mysql for RPM installations or the share directory under the MySQL installation directory for TAR file installations. The my-default.cnf file is not read by MySQL programs. The install process copies the my-default.cnf sample file to /etc/my.cnf. If /etc/my.cnf already exists, it copies it to /etc/mynew.cnf instead.

Windows: The my-default.ini sample option file and my.ini are located in the MySQL installation directory.

Before you change any of the default options, make sure that you fully understand the effects that the options have on server operation.

• –defaults-file=:Use the option file at the specified location.
• –defaults-extra-file=:Use an additional option file at the specified location.
• –no-defaults:Ignore all option files.

For example, to use only the /etc/my-opts.cnffile and ignore the standard option files, invoke the program like this:

shell> mysql --defaults-file=/etc/my-opts.cnf 

If an option is specified multiple times, either in the same option file or in multiple option files, the option value that occurs last takes precedence. For more information about using option files, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/option-files.html.

Displaying Options from Option Files

You can view which options are used by programs that read the specified option groups by executing the mysql client with the –print-defaultsoption or by using the my_print_defaultsutility. The output consists of options, one per line, in the form that they would be specified on the command line. This output varies according to your option file settings.

shell> my_print_defaults mysql client --user=myusername --password=secret --host=localhost --port=3306 --character-set-server=latin1 

mysql --print-defaults mysql client 

my_print_defaults accepts the following options:

• –help, -?: Display a help message and exit.
• **–config-file=, –defaults-file=, -c **: Read only the given option file.
• **–debug=, -# **: Write a debugging log.
• **–defaults-extra-file=, –extra-file=, -e **: Read this option file after the global option file, but (on Linux) before the user option file.
• **–defaults-group-suffix=, -g **: Read groups with this suffix.
• –no-defaults, -n: Return an empty string.
• –verbose, -v: Verbose mode. Print more information about what the program does.
• –version, -V: Display version information and exit.

Источник

Конфигурация файла my.cnf в MySQL

Файл my.cnf предназначен для настройки следующих параметров при запуске MySQL в командной строке с файлом cnf, тогда нет необходимости добавлять такие параметры в команду при запуске загрузки.

Этот файл my.cnf может быть настраиваемым расположением, или вы можете использовать следующее расположение по умолчанию, пока он находится в расположении по умолчанию, MySQL автоматически распознает его (устанавливается через источник deb или APT, исходное расположение находится в списке ниже):

имя файла	цель
/etc/my.cnf	Глобальные параметры
/etc/mysql/my.cnf	Глобальные параметры
SYSCONFDIR/my.cnf	Глобальные параметры
$MYSQL_HOME/my.cnf	Параметры для конкретного сервера (только сервер)
defaults-extra-file	Указанный файл —defaults-extra-file, если есть
~/.my.cnf	Пользовательские параметры
~/.mylogin.cnf	Параметры пути входа для конкретного пользователя (только на стороне клиента)

Приведенное выше подробное описание может относиться к официальному объяснению: https://dev.mysql.com/doc/refman/5.7/en/option-files.html

Короче говоря, независимо от того, используете ли вы установку исходного кода APT, установку пакета deb или установку пакета двоичного (сжатый пакет tar.gz), вы можете настроить конфигурацию запуска MySQL и настройку с помощью файла my.cnf.

Начиная с версии 5.7.18, двоичный пакет больше не содержит образец файла my-default.cnf, поэтому я извлек образец из версии 5.7.17, но обнаружил, что в нем не слишком много конфигураций. Содержимое my-default.cnf выглядит следующим образом :

Источник

Where is mysql’s configuration file in ubuntu server 18.04

I recently installed MySql Server 8.0.19 on and EC2 (t2.micro) instance running Ubuntu 18.04. I’m trying to find the MySql configuration file that’s like the my.ini on Windows. I’ve looked through almost every file in /etc/mysql/ but none of them are similar to the my.ini that windows uses. Where is this file located on Ubuntu 18.04 and where are the configuration settings read from?

3 Answers 3

Do an update of the locate database and search for my.cnf

sudo locatedb locate my.cnf 

/etc/mysql/my.cnf /home/$USER/.my.cnf 

That is the GENERIC method of finding files on a server.

That does not use a generic configuration. The name depends on the type of instance: my-small.cnf , my-medium.cnf , my-large.cnf . Use that for the locate .

Mind also that EC settings often need to be done from within the console and not on command line!

I’ve looked through /etc/mysql/my.cnf, which finally points to /var/mysql/mysql.conf.d/mysqld.cnf which only has [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log So where is it getting the values for long_query_time etc?

You did not find one of my-small.cnf, my-medium.cnf, my-large.cnf? Please edit the question to include what the type is of the instance. I would start with the EC2 console; that is where I mosttimes see these things happen. It is totally possible that this is done in memory and not on-disk. (we have EC2 instances and have a my-large.cnf but the file is in a virtual directory).

Источник