How can I restore the MySQL root user’s full privileges?

Данный документ поможет пользователю в настройке и использовании MySQL.

I am trying to execute query in mysql.

SET GLOBAL log_bin_trust_function_creators =1; 

SQL query:
SET GLOBAL log_bin_trust_function_creators =1
MySQL said:
#1227 — Access denied; you need the SUPER privilege for this operation

I want to know that how do i assign SUPER privileges to any database

asked Aug 14, 2012 at 4:58

Manish Malviya

2 gold badges13 silver badges19 bronze badges

If you want to do it through Console, do like this:

 mysql> GRANT SUPER ON *.* TO user@'localhost' IDENTIFIED BY 'password';

After executing above code, end it with:

mysql> FLUSH PRIVILEGES;

You should do in on *.* because SUPER is not the privilege that applies just to one database, it’s global.

6 gold badges47 silver badges84 bronze badges

answered Aug 14, 2012 at 5:02

You can see the privileges here.

answered Aug 14, 2012 at 5:29

5 gold badges29 silver badges44 bronze badges

2 gold badges12 silver badges30 bronze badges

answered Sep 14, 2017 at 17:15

ON $.$ TO-> $=* doesnt show when you put two with a dot between them.

and the reverse one, removing grant:

checked on vagrant should be working in any mysql

answered Sep 27, 2015 at 13:52

On Centos 5 I was getting all sorts of errors trying to make changes to some variable values from the MySQL shell, after having logged in with the proper uid and pw (with root access). The error that I was getting was something like this:

mysql> -- Set some variable value, for example
mysql> SET GLOBAL general_log='ON';
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

OS-Shell> sudo mysql # no DB uid, no DB pw

Kindly note that I did not provide the DB uid and password

mysql> show variables;
mysql> -- edit the variable of interest to the desired value, for example
mysql> SET GLOBAL general_log='ON';

It worked like a charm

answered Jul 16, 2018 at 3:14

1 gold badge12 silver badges31 bronze badges

Select-используется для извлечения информации из таблиц.Select можно также использовать не имея разрешения на доступ к какой-либо БД,например,как калькулятор.

Сервер выдаст результат.Пример использования Select:

mysql>select fname,lname,address from customers where city='New York';

Insert-используется для вставки информации в таблицу.Например:

mysql>insert into data2 (first_col,third_col,sixth_col) values ('Bob','Smith','bob@fdrt.net');

Update-позволяет обновлять информацию в таблицах.Например:

mysql>update numbers set field3='129867' where field5='567483';

Delete-используется для удаления информации из таблиц.

mysql>delete from indexes where u_id='2201';

Alter-используется для модификации таблиц:переименования таблицы,добавления колонок,изменения колонок,удаления колонок.Примеры:

mysql>alter table wind rename newwind;
mysql>alter table newwind add master_con varchar(20);
mysql>alter table newwind change retail wholesale varchar(60);
mysql>alter table newwind drop field021;

Create-позволяет создавать базы данных и таблицы в них.Примеры:

mysql>mysqladmin -u john -ptest create mydb //Создает пустую БД.
mysql>use mydb;
Database changed.
create table monitoring (
id int(5) not null auto_increment,
first_name varchar(15) not null,
last_name varchar(20) not null,
address varchar(80) not null,
primary key (id),
key(last_name));

Drop-ипользуется для удаления таблиц и баз данных.

Grant-позволяет пользователю предоставлять другим пользователям привилегии,которыми он сам обладает.Два пользователя с различными привилегиями и привилегий GRANT могут комбинировать свои разрешения.

Index-позволяет пользователю создавать и удалять индексы.

1. Stop MySQL service
2. Run mysqld_safe --skip-grant-tables &
3. Type mysql -u root -p and press enter.
4. Enter your password
5. At the mysql command line enter: use mysql;

Then execute this query:

insert into `user` (`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`)
values('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0','0');

then restart the mysqld

EDIT: October 6, 2018

In case anyone else needs this answer, I tried it today using innodb_version 5.6.36-82.0 and 10.1.24-MariaDB and it works if you REMOVE THE BACKTICKS (no single quotes either, just remove them):

insert into user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections)
values('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0','0');

Основные утилиты MySQL.

В состав дистрибутива MySQL входят следующие утилиты:

• mysqld
• mysql
• mysqladmin
• mysqlaccess
• mysqlshow
• mysqldump
• isamchk
• isamlog
• safe_mysqld(для Unix-версий)

Используется для проверки привилегий пользователя для доступа к конкретной базе данных.
Общий синтаксис:

Полезная утилита для проверки прав доступа пользователя,если он получает сообщение Access denied,при попытке соединиться с базой данных.

Используется,чтобы показать,с какими базами данных работает сервер,какие таблицы содержит каждая БД
и какие колонки есть в каждой таблице.Синтаксис:

Mysqlshow может использовать следующие параметры:

Если ввести mysqlshow без аргументов,будут показаны все базы данных,если указать имя БД,
будут показаны все таблицы в ней.

Используется для для создания дампа содержания базы данных (резервной копии).Можно записать содержимое
базы данных в файл.Синтаксис:

Mysqldump может использовать следующие параметры:

С помощью этой утилиты можно осуществлять восстановление таблиц,управление таблицами и сбор
статистики.Синтаксис:

Isamchk используется для проверки непротиворечивости таблиц и устранения проблем,которые это может вызвать.
Если пользователь часто удаляет и добавляет данные в таблицах,эта утилита будет очень полезной.
Isamchk может использовать следующие опции:

При использовании опции -r нужно остановить mysqld.Перед началом работы нужно перейти
в каталог,где находится таблица,нуждающаяся в проверке или ремонте.

Журнал транзакций,используется с опцией —log-isam=file_name демона mysqld.В файл записываются
все изменения,происходящие в базе данных.Может использоваться для восстановления базы данных.
Если есть резервная копия базы данных до того,как был сгенерирован isam-журнал,то информацию можно легко восстановить.
Если нет копии,то нужно использовать все журналы с начала работы базы данных.

Cкрипт для запуска демона mysqld в Unix-версиях.Выполняется при начальной загрузке системы.
Для автоматического запуска нужно добавить запись в один из файлов инициализации системы.

Access denied for user 'root'@'localhost' (using password: YES)

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Access denied for user 'root'@'localhost' (using password: NO)

mysql -u root -p

sudo mysql

ALTER USER 'root'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'your_new_password';

FLUSH PRIVILEGES;

SELECT user, plugin
FROM mysql.user

exit;

mysql -u root -p

/etc/my.cnf
/etc/mysql/my.cnf

[mysqld]
skip-grant-tables

mysql -u root -p

FLUSH PRIVILEGES;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

mysql -u root -p

4.3.4 Задание изначальных привилегий MySQL

После установки MySQL изначальные привилегии доступа задаются при помощи
`scripts/mysql_install_db'. See section 2.3.1 Обзор быстрой установки. Скрипт mysql_install_db запускает
сервер mysqld, а затем инициализирует таблицы предоставления привилегий со
следующим набором привилегий:

• В качестве суперпользователя создается MySQL root который может делать
  все, что угодно. Соединения должны устанавливаться с локального
  компьютера.

  Примечание: Изначально пароль root пуст, поэтому кто
  угодно может подсоединиться в качестве root без пароля и получить все
  привилегии.

• Создается анонимный пользователь, который может выполнять любые
  операции над базами данных с именами test или начинающимися с test_.
  Соединения должны устанавливаться с локального компьютера. Это
  означает, что любой локальный пользователь может подключиться без
  пароля и будет воспринят сервером как анонимный пользователь.

• Остальные привилегии запрещены. Например, обычный пользователь не
  может использовать команды mysqladmin shutdown или mysqladmin
processlist.

Примечание: В Windows принятые по умолчанию привилегии отличаются от
указанных. See section 2.6.2.3 Работа MySQL в среде Windows.

Поскольку сразу после установки программа совершенно не защищена, первым
делом необходимо задать пароль для пользователя MySQL root. Это можно
сделать следующим образом (обратите внимание, что пароль указывается при
помощи функции PASSWORD()):

shell> mysql -u root mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');

Опытные пользователи могут работать непосредственно с таблицами назначения
привилегий:

shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password') -> WHERE user='root';
mysql> FLUSH PRIVILEGES;

shell> mysqladmin -u root password new_password

Изменять пароли других пользователей могут только пользователи с правом
записи/обновления базы данных mysql. Все обычные пользователи (не
анонимные) могут модифицировать только свой собственный пароль при помощи
указанных выше команд или команды SET PASSWORD=PASSWORD('new_password').

После того, как был задан пароль root, этот пароль необходимо будет
вводить, подсоединяясь к серверу как root.

Можно оставить пароль root пустым, тогда не придется его указывать во
время проведения дополнительных установок и тестирования. Тем не менее,
обязательно укажите его, прежде чем использовать сервер для любой реальной
работы.

Ознакомьтесь со скриптом `scripts/mysql_install_db', чтобы увидеть, как
задавать привилегии по умолчанию. Данный скрипт можно использовать как
основу для добавления других пользователей.

Если необходимо, чтобы изначальные привилегии отличались от указанных
выше, можно изменить базу mysql_install_db еще до ее запуска.

Чтобы полностью заново создать таблицы предоставления привилегий, удалите
все файлы с расширениями `.frm', `.MYI' и `.MYD' в каталоге, где находится база
данных mysql (это каталог с именем mysql в каталоге базы данных, который
выводится на экран при запуске команды mysqld --help). Затем запустите
скрипт mysql_install_db (возможно, после добавления в него необходимых
привилегий).

Примечание: в более старых, чем 3.22.10, версиях MySQL файлы с расширением
`.frm' удалять не следует. Если же случайно они были удалены, их следует
восстановить, скопировав из дистрибутива MySQL до запуска
mysql_install_db.

Начало работы с MySQL

MySQL — это популярный сервер баз данных, используемый в разных приложениях. SQL означает язык структурированных запросов — (S)tructured (Q)uery (L)anguage, который MySQL использует для коммуникации с другими программами. Сверх того, MySQL имеет свои собственные расширенные функции SQL для того чтобы обеспечить пользователям дополнительный функционал. В этом документе мы рассмотрим как провести первоначальную установку MySQL, настроить базы данных и таблицы, и создать новых пользователей. Давайте начнем с установки.

Сначала убедитесь что MySQL установлен на вашу систему. В случае если вам требуется определенная функциональность MySQL, убедитесь, что установлены необходимые USE-флаги, так как они помогут в тонкой настройке инсталляции.

По завершении установки, вы увидите следующее уведомление:

Сообщение einfo MySQL

You might want to run:
"emerge --config =dev-db/mysql-[version]"
if this is a new install.

Так как это новая установка, мы запустим эту команду. Вам надо нажать ENTER по запросу во время конфигурации базы данных MySQL. В процессе конфигурации устанавливается основная база данных MySQL, которая содержит служебную информацию, такую как базы данных, таблицы, пользователи, разрешения и т.д. В процессе конфигурации рекомендуется чтобы вы изменили свой пароль root так быстро, как это возможно. Мы определенно это сделаем, иначе кто-нибудь сможет волей случая появиться и взломать сервер MySQL, настроенный по умолчанию.

 * MySQL DATADIR is /var/lib/mysql * Press ENTER to create the mysql database and set proper * permissions on it, or Control-C to abort now... Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, issue the following commands to start the server and change the applicable passwords: /etc/init.d/mysql start /usr/bin/mysqladmin -u root -h pegasos password 'new-password' /usr/bin/mysqladmin -u root password 'new-password' Depending on your configuration, a -p option may be needed in the last command. See the manual for more details.

Заметка
Если предыдущая команда не выполнится из-за того, что имя хоста установлено в localhost, измените его на другое имя, например gentoo. Обновите файл и перезапустите .

Некоторая нехарактерная для ebuild-файлов информация MySQL удалена отсюда, чтобы содержать этот документ настолько последовательным, насколько возможно.

Важно
Начиная с mysql-4.0.24-r2, пароли вводятся во время этапа конфигурации, что делает пароль root более надежным.

Сценарий конфигурации уже вывел команды, которые нам нужно запустить, чтобы настроить наш пароль, поэтому нам сейчас надо их выполнить.

Если вы используете OpenRC, выполните данную команду:

root /etc/init.d/mysql start

 * Re-caching dependency info (mtimes differ)... * Starting mysqld (/etc/mysql/my.cnf) ... [ ok ]

Если вы используете systemd, вместо этого используйте следующую команду:

root systemctl restart mysqld.service

With >=dev-db/mariadb-10.1.18, use:

root systemctl restart mariadb.service

После этого установите пароль root:

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.25
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Параметр -u указывает пользователя, который будет выполнять вход. Параметр -h указывает хост. Обычно это будет localhost, если только вы не настраиваете удаленный сервер. И, наконец, -p сообщает клиенту mysql что вы будете вводить пароль для доступа к базе данных. Обратите внимание на приглашение mysql>. Это то место, где вы будете вводить все ваши команды. Теперь, когда мы в командной строке mysql в качестве пользователя root, мы можем начать настраивать нашу базу данных.

Важно
Установка mysql по умолчанию приемлема для систем разработки. Для более безопасных значений по умолчанию можно запустить /usr/bin/mysql_secure_installation

     
 Важно
A host of ‘localhost’ does not mean DNS localhost (127.0.0.1) to MySQL. Instead, it refers to the UNIX socket connection and not TCP/IP.
Предоставление Привилегий Командой GRANT
Let’s have a closer look at this somewhat simplified format of the GRANT command.
 Синтаксис команды GRANT
        
Во-первых, мы имеем привилегии, которые мы хотим назначить. Исходя из того что мы выучили на данный момент, имеются несколько привилегий, которые вы можете установить:
ALL — Дает полный контроль базы данных со всеми привилегиями
CREATE — Позволяет пользователям создавать таблицы
SELECT — Позволяет пользователям делать запросы к таблицам
INSERT — Позволяет пользователям вставлять данные в таблицу
SHOW DATABASES — Позволяет пользователям просматривать список баз данных
USAGE — Пользователь не имеет привилегий
GRANT OPTION — Позволяет пользователям предоставлять привилегии
 Заметка
Если вы запустили MySQL чтобы передавать данные веб-приложению, CREATE, SELECT, INSERT (обсуждалось здесь же), DELETE и UPDATE (для получения дальнейшей информации посмотрите раздел Справочного Руководства MySQL — Синтаксис GRANT и REVOKE) — это единственные разрешения, которые вам, скорее всего, понадобятся. Большинство делает ошибку, предоставляя все разрешения, когда в этом нет действительной необходимости. Сверьтесь с разработчиками приложения, чтобы посмотреть, не вызовут ли такие разрешения проблемы в общей работе.
Теперь, когда мы настроили наших пользователей, давайте их протестируем. Сначала, мы завершим работу mysql, введя quit в командной строке:
Теперь мы снова в консоли. Сейчас, когда пользователи созданы, давайте продолжим и посмотрим что они могут делать.
Проверка Пользовательских Разрешений
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.25
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Теперь мы должны протестировать пользовательские ограничения. Давайте переключимся к базе данных gentoo:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
Давайте попробуем сделать что-нибудь, что мы, как предполагается, не должны. Мы попытаемся создать таблицу.
mysql>CREATE TABLE test (test VARCHAR(20), foobar VARCHAR(2));
ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'
Как видите, эта функция не сработала, так как наш пользователь не имеет соответствующих прав доступа. Однако, одной из привилегий, которую мы предоставили, является выражение SELECT. Давайте попробуем:
mysql>SELECT * FROM developers;
+-------------+-----------------------+----------------+
| name | email | job |
+-------------+-----------------------+----------------+
| Joe Smith | joesmith@gentoo.org | toolchain |
| John Doe | johndoe@gentoo.org | portage |
| Chris White | chriswhite@gentoo.org | documentation |
| Sam Smith | samsmith@gentoo.org | amd64 |
| Jane Doe | NULL | Outsourced job |
+-------------+-----------------------+----------------+
5 rows in set (0.00 sec)
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.25
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mysql>CREATE DATABASE gentoo2;
ERROR 1044: Access denied for user: 'admin@localhost' to database 'gentoo2'
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> INSERT INTO developers VALUES('Bob Simmons', 'bobsimmons@gentoo.org', 'python');
Query OK, 1 row affected (0.08 sec)
Удаление Прав Доступа Пользователя Командой REVOKE
Команда REVOKE позволяет нам запретить доступ пользователю. Мы можем запретить или все права на доступ, или определенные права. В действительности, формат весьма схож с командой GRANT .
 Синтаксис REVOKE
     
Здесь, параметры объясняются в разделе команды GRANT . Однако же, в этом разделе, мы собираемся запретить пользователю доступ полностью. Давайте предположим, мы обнаружили, что учетная запись guest вызывает некоторые проблемы с безопасностью. Мы решаем отозвать все привилегии. Мы заходим под учетной записью root и делаем то, что необходимо.
Query OK, 0 rows affected (0.00 sec)
 Заметка
В данном случае, права доступа пользователя достаточно просты, поэтому их удаление из базы данных не является проблемой. Однако, в более общем случае, вы, скорее всего, использовали бы *.* вместо gentoo.* для того чтобы удалить права доступа пользователя ко всем другим базам данных.
Давайте теперь выйдем и попытаемся зайти в качестве пользователя guest.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.25
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Хотя мы и были способны выполнить вход, наши права доступа к gentoo теперь исчезли.
ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'
И наш проблемный пользователь больше не может получить доступ к базе данных gentoo. Пожалуйста, заметьте, что пользователь все еще может выполнить вход. Это потому что он остается в основной базе данных MySQL. Давайте рассмотрим как полностью удалить учетную запись командой DELETE и взглянем на таблицу пользователей MySQL.
Удаление Учетных Записей с Использованием DELETE
Таблица пользователей MySQL является перечислением всех пользователей и информации о них. Убедитесь, что вы выполнили вход под root. Затем продолжайте и используйте основную базу данных MySQL.
Query OK, 1 row affected (0.07 sec)
Enter password:
ERROR 1045: Access denied for user: 'guest@localhost' (Using password: YES)
Наш пользователь успешно удален!
В то время как это руководство сфокусировано, в основном, на настройке MySQL из командной строки, доступно несколько альтернатив с графическим интерфейсом:
Это завершает вводное руководство к MySQL. Я надеюсь это дало вам лучшее понимание основ MySQL и настройки базы данных.
Работа с таблицами в MySQL
В структуру MySQL входят базы данных, таблицы, записи, и поля. Базы данных объединяют таблицы, таблицы объединяют записи, записи объединяют поля, которые содержат действительную информацию. Такая структура позволяет пользователям выбирать как они хотят обращаться к своей информации. На данный момент, мы разобрались с базами данных, теперь давайте поработаем с таблицами. Во-первых, таблицы могут быть перечислены, подобно базам данных, с использованием команды SHOW TABLES. Сейчас, в базе данных gentoo не имеется таблиц, как и показывает нам следующая команда:
Empty set (0.00 sec)
Это означает нам надо создать какие-либо таблицы. Чтобы это сделать, мы используем команду CREATE TABLE. Однако, эта команда достаточно отличается от простой команды CREATE DATABASE тем что принимает список аргументов. Формат команды следует ниже:
 Синтаксис CREATE TABLE
    
table_name — это имя таблицы, которую мы хотим создать. В данном случае, давайте создадим таблицу с именем developers . Эта таблица будет хранить имя разработчика, email адрес и его должность.
field_name будет хранить имя поля. В этом случае мы имеем три требуемых имени: имя разработчика, email адрес, и должность.
field_data_type — это то, какой тип информации будет сохранен. Различные доступные форматы могут быть найдены по адресу MySQL Column Types Page . Для наших целей, мы будем использовать тип данных VARCHAR для всех наших полей. VARCHAR — это один из простейших типов данных, когда дело касается работы со строками.
size — это то, как много данных будет хранить одно поле. В нашем случае, мы будем использовать 128. Это означает, что поле будет иметь данные типа VARCHAR, которые занимают 128 байт. В настоящий момент, вы можете спокойно думать об этом как о 128 символах, хотя существует более техническое объяснение, которое вам предоставит сайт, указанный выше. Теперь, когда мы знаем как мы создадим таблицу, давайте это сделаем.
mysql>CREATE TABLE developers ( name VARCHAR(128), email VARCHAR(128), job VARCHAR(128));
Query OK, 0 rows affected (0.11 sec)
Похоже, наша таблица была успешно создана. Давайте проверим это командой SHOW TABLES:
+------------------+
| Tables_in_gentoo |
+------------------+
| developers |
+------------------+
1 row in set (0.00 sec)
Да, наша таблица существует. Однако, она, по-видимому, не имеет никакой информации о типах полей, которые мы создали. Для этого, мы используем команду DESCRIBE (или, кратко, DESC), которая принимает имя таблицы в качестве своих аргументов. Давайте посмотрим что она нам даст для нашей таблицы developers.
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(128) | YES | | NULL | |
| email | varchar(128) | YES | | NULL | |
| job | varchar(128) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Показаны разные поля и их типы. Также показано несколько дополнительных атрибутов, которые находятся за рамками данного руководства. Пожалуйста, обратитесь за подробностями к Справочному Руководству MySQL . Теперь у нас есть таблица для работы с ней. Давайте продолжим и заполним ее.
Заполнение Базы Данных MySQL
Мы заполним таблицу (или добавим в нее данные), используя команду INSERT. Подобно команде CREATE TABLE, она также имеет особый формат:
 Синтаксис INSERT
        
Эта команда используется для вставки записи в таблицу. table содержит таблицу MySQL, в которую мы хотим ввести информацию. Имя таблицы может сопровождаться списком столбцов для вставки данных, VALUES() хранит значения, которые вы хотите вставить в таблицу. Вы можете опустить список столбцов, если вы вставляете значение в каждый из них и если Вы пишете значения в том же порядке, в котором определены столбцы. В данном случае, мы хотим вставить данные в таблицу developers. Давайте добавим примеры записей:
Query OK, 1 row affected (0.06 sec)
## (Если вы не знаете порядок столбцов в таблице или хотите вставить неполную запись)
mysql> INSERT INTO developers (job, name) VALUES('outsourced', 'Jane Doe');
Query OK, 1 row affected (0.01 sec)
В соответствии с полученным результатом, кажется, запись была вставлена правильно. Что если мы хотим ввести больше, чем просто одну запись? Это тот случай, когда команда LOAD DATA вступает в действие. Она загружает записи из файла, разделенного символами табуляции. Давайте это попробуем, отредактировав файл в домашнем каталоге пользователя и добавив в него записи. Мы назовем этот файл . Здесь приведен пример:
John Doe	johndoe@gentoo.org	portage
Chris White	chriswhite@gentoo.org	documentation
Sam Smith	samsmith@gentoo.org	amd64
 Важно
Убедитесь, что вы знаете, с какими данными вы будете работать. Очень небезопасно использовать LOAD DATA, когда вы не уверены насчет содержимого файла!
Команда LOAD DATA имеет в каком-то смысле пространное определение, но здесь мы используем ее самую простую форму.
 Синтаксис LOAD DATA
       
 — это каталог и имя файла, которые будут использоваться. table — это имя нашей таблицы. В этом случае, наш файл — , а имя таблицы — developers.
mysql>LOAD DATA LOCAL INFILE '~/records.txt' INTO TABLE developers;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
 Важно
Если вы обнаружили какое-либо странное поведение, убедитесь, что поля разделены символами табуляции. Если вы вставите информацию в ваш исходный файл из другого источника, это может конвертировать символы табуляции в пробелы.
Сработало хорошо. Однако, это просто вставляет записи, и не дает вам какого-либо контроля над MySQL. Множество веб-приложений используют скрипты sql для того чтобы настроить MySQL быстро и легко. Если вы хотите использовать скрипты sql, вам нужно запустить mysql в пакетном режиме, или использовать файл в качестве источника команд. Здесь приведен пример запуска mysql в пакетном режиме:
Как и в случае с LOAD DATA, убедитесь, что вы можете сказать что делает файл . Невозможность этого может скомпрометировать вашу базу данных! Другим способом выполнения этого является использование команды source. Эта команда запустит команды из файла sql, находясь в интерактивном режиме mysql. Здесь показано как использовать sql file в качестве источника команд:
Если вы видите, что веб-приложение требует запуск sql файла, две команды выше могут быть использованы для выполнения данной задачи. Мы настроили нашу таблицу, как же нам проверить наши поля? Мы выполним это поиском в нашей таблице с помощью запросов.
Запросы к Таблицам MySQL
Запросы являются одной из основных функций любой базы данных SQL. Они помогают превратить данные в таблицах во что-то полезное. Большинство запросов выполняются командой SELECT . Команда SELECT — довольно сложна, и мы рассмотрим только три основных формы этой команды в данном документе.
 Виды команды SELECT
                          
Давайте-ка быстро рассмотрим первую форму команды. Она относительно проста и дает общее представление о вашей таблице. Мы продолжим и запустим ее, чтобы посмотреть какие данные у нас есть в настоящий момент.
mysql>SELECT * FROM developers;
+-------------+-----------------------+----------------+
| name | email | job |
+-------------+-----------------------+----------------+
| Joe Smith | joesmith@gentoo.org | toolchain |
| John Doe | johndoe@gentoo.org | portage |
| Chris White | chriswhite@gentoo.org | documentation |
| Sam Smith | samsmith@gentoo.org | amd64 |
| Jane Doe | NULL | Outsourced job |
+-------------+-----------------------+----------------+
5 rows in set (0.00 sec)
Мы видим как данные, которые мы добавили с INSERT, так и вставленные LOAD DATA. Теперь, давайте предположим что мы просто хотим посмотреть запись для Chris White. Мы можем сделать это с помощью второй формы команды select, как показано ниже.
mysql>SELECT * FROM developers WHERE name = 'Chris White';
+-------------+-----------------------+---------------+
| name | email | job |
+-------------+-----------------------+---------------+
| Chris White | chriswhite@gentoo.org | documentation |
+-------------+-----------------------+---------------+
1 row in set (0.08 sec)
Как предполагалось, выбрана отдельная запись, которую мы искали. Теперь, предположим, мы только хотели узнать должность и email адрес данной персоны, но не ее имя. Мы можем это выполнить с помощью третьей формы SELECT , как здесь и показано.
mysql>SELECT email,job FROM developers WHERE name = 'Chris White';
+-----------------------+---------------+
| email | job |
+-----------------------+---------------+
| chriswhite@gentoo.org | documentation |
+-----------------------+---------------+
1 row in set (0.04 sec)
Этот способ выбора намного легче в управлении, особенно с большими объемами информации, как мы увидим позже. А сейчас, будучи mysql пользователем root, мы обладаем неограниченными разрешениями делать с базой данных MySQL то, что мы захотим. В среде выполнения сервера, наличие пользователя с такими привилегиями может вызвать немало проблем. Для того, чтобы контролировать кто и что может делать с базами данных, мы установим привилегии.
Система привилегий и безопасность в MySQL.
База данных mysql и таблицы привилегий.
Итак,вы успешно вошли в базу данных mysql,которая используется для администрирования сервера.Что же здесь находится?А находятся здесь 5 таблиц,которые ничем не отличаются от других таблий баз данных,за исключением того,что эти таблицы используются для предоставления доступа к базам данных и таблицам в них пользователям.Рассмотрим каждую из них.
Введите следующую команду,которая покажет таблицы в базе данных mysql:

 
mysql>show tables;
Кратко рассмотрим функции каждой из таблиц:
User
 Определяет,разрешено ли пользователю,пытающемуся подключиться к серверу делать это.Содержит имя пользователя,пароль а также привилегии.Если ввести команду show columns from user;,то получим следующее:
Изначально эта таблица содержит пользователя root с паролем,который вы установили и именем хоста ‘%’.По умолчанию root может входить с любого хоста,имеет все привилегии и доступ ко всем базам данных.Также в таблице содержится запись для пользователя ‘%’,которую нужно сразу же удалить,так как она предоставляет доступ любому пользователю.
Для добавления нового пользователя выполните следующие действия:
Пояснения:
Db
 Определяет к каким базам данных каким пользователям и с каких хостов разрешен доступ.В этой таблице можно предоставлять каждому пользователю доступ к базам данных и назначать привилегии.
 Eсли выполнить команду show columns from db; получим следующее:
По умолчанию,все привилегии установлены в ‘N’.Например,предоставим юзеру john доступ к базе данных library и дадим ему привилегии select,insert и update(описание основных команд mysql будет дано в отдельном разделе,сейчас моя цель-показать,как работают таблицы привилегий).
Host
Таблица host используется для расширения диапазона доступа в таблице db.К примеру,если доступ к какой-либо базе данных должен быть предоставлен более чем одному хосту,тогда следует оставить пустой колонку host в таблице db,и внести в таблицу host необходимые имена хостов.
 Выполним команду show columns from host;
Как видно из таблицы,здесь также можно задавать привилегии для доступа к базе данных.Они обычно редко используются без необходимости.Все привилегии доступа нужно задавать в таблице db для каждого пользователя,а в таблице host только перечислить имена хостов.Сервер читает все таблицы,проверяет имя пользователя,пароль,имя хоста,имя базы данных,привилегии.Если в таблице db привилегии select,insert установлены в ‘Y’,а в таблице host в ‘N’,то в итоге юзер все равно получит ‘Y’.Чтобы не вносить путаницы,лучше назначать привилегии в таблице db.
Эти 3 таблицы являются основными.В новых версиях MySQL,начиная с 3.22 добавлены еще 2 таблицы- tables_priv и columns_priv,которые позволяют задать права доступа к определенной таблице в базе данных и даже к определенной колонке.Они работают подобно таблице db,только ссылаются на таблицы и колонки.
 Также,начиная с версии 3.22 можно использовать команду GRANT для предоставления доступа к базам данных,таблицам и колонкам таблиц,что избавляет от необходимости вручную модифицировать таблицы db,tables_priv и columns_priv.Команда GRANT будет подробно рассмотрена в следующих разделах.
Инсталляция и первый запуск MySQL.
Если вы работаете на Unix-платформе(в частности я буду приводить примеры для Linux,которые немногим отличаются для других Unix-платформ),нужно выполнить следующие шаги:
Распаковывать исходники нужно в каталог /usr/local,так как в настроечных скриптах указывается именно эта директория и создать симлинк для каталога mysql-это делается,потому что в тех же скриптах указывается этот каталог.Нужно подредактировать скрипты mysql_install_db и safe_mysqld применительно к вашей системе,для того,чтобы они начали работать,в частности исправить путь для указания местоположения mysqld и баз данных.
 Для запуска демона mysqld нужно перейти в каталог /usr/local/mysql/bin и запустить скрипт safe_mysql в фоновом режиме.Если вы не получили никаких сообщений,это уже хорошо-значит,все прошло успешно.Если же появилось сообщение об ошибке,значит что-то не в порядке со скриптами.Можно настроить запуск сервера mysql во время загрузки компьютера.Для этого нужно поместить запуск safe_mysql в один из файлов инициализации системы(они разные в зависимости от версии Linux).
 Для Linux Slakware в файл /etc/rc.d/rc.local нужно добавить:
Что касается Windows,поклонником которого я являюсь,то здесь установка проходит практически без проблем.Распаковываете zip-архив,запускаете exe-файл,а дальше все работает само.Устанавливается обычно в каталог c:mysql.
 В Windows NT MySQL устанавливается в виде службы и можно указать способ запуска-ручной или автоматический (Start-Settings-Control Panel-Services-MySQL-Startup).
Управление сервером обычно осуществляется из командной строки.Насчет Linux я уже писал,а в Windows 95/98 откройте сеанс DOS и выполните следующие команды:
cd mysqlbin
mysqlbin>mysqld --standalone
Эта команда запустит демон mysql в фоновом режиме.В Windows 95/98 не предусмотрен запуск mysqld в виде службы.Отсутствие сообщений при этом -хороший знак,значит все в порядке.
При запуске mysqld можно указывать следующие опции:
Примечание.Команды mysqld и mysql имеют еще некоторые опции,но в данный момент они особого интереса не представляют.
mysqlbin>mysql -u root mysql
Welcome to the MySQL monitor.Commands end with ; or g.
Your MySQL connection id is 1 to server version: 3.23.19
Type 'help' for help.
mysql>
 Если вы это получили,значит вы успешно вошли в базу данных mysql,которая используется для администрирования сервера.В ней содержатся 5 таблиц,называемых таблицами привилегий.Система привилегий будет подробно рассмотрена в следующих разделах,а пока выполните следующее:
mysqlbinmysql -u root -p mysql
Enter password:*******
Запросы к базе данных и команда Select.
Я не ставлю целью углубляться в изучение языка SQL,об этом вы можете прочитать в любом руководстве по SQL Server,MySQL в основном поддерживает все основные команды стандарта ANSI 92,но команда Select заслуживает того,чтобы посвятить ей отдельную главу.
 Команда Select используется для запросов к базе данных с целью извлечения из нее информации.Синтаксис команды следующий:
SELECT [STRAIGHT_JOIN] [DISTINCT | ALL] select_expression,...
[FROM tables... [WHERE where_definition] [GROUP BY column,...]
[ORDER BY column [ASC | DESC], ...] HAVING full_where_definition
[LIMIT [offset,] rows] [PROCEDURE procedure_name]]
[INTO OUTFILE 'file_name'... ]
Как видно из вышеприведенного,вместе с командой Select используются ключевые слова,использование которых очень влияет на ответ сервера.Рассмотрим каждое из них.
Ну вот вкратце и все.MySQL поддерживает почти все основные команды SQL Server,так что более подробно
 о команде SELECT вы можете прочитать в любом учебнике по языку SQL.
Настройка Базы Данных
Создание Базы Данных
Мы вошли и приглашение mysql показано на экране. Сначала, давайте рассмотрим базы данных, которые у нас имеются в настоящий момент. Чтобы это сделать, мы используем команду SHOW DATABASES.
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.09 sec)
 Важно
Пожалуйста, запомните что команды MySQL должны оканчиваться точкой с запятой — ;
Вопреки тому факту, что тестовая база данных уже создана, мы собираемся создать нашу собственную. Базы данных создаются с использованием команды CREATE DATABASE. Мы создадим одну из них под названием gentoo.
mysql>CREATE DATABASE gentoo;
Query OK, 1 row affected (0.08 sec)
Ответ позволяет нам узнать, что команда была выполнена без ошибок. В этом случае, одна строка была изменена. Это является отсылкой к основной базе данных mysql, которая содержит список всех баз данных. Вам не нужно сильно беспокоиться о второстепенных деталях. Последнее число является характеристикой того, насколько быстро был выполнен запрос. Мы можем проверить, что база данных была создана, запустив команду SHOW DATABASES снова.
+----------+
| Database |
+----------+
| gentoo |
| mysql |
| test |
+----------+
3 rows in set (0.00 sec)
В самом деле, наша база данных была создана. Для того чтобы работать с созданием таблиц для нашей новой базы данных gentoo, нам надо выбрать ее в качестве текущей базы данных. Чтобы это сделать, мы используем команду USE. Команда USE принимает имя базы данных, которую вы хотите использовать в качестве текущей. Другой возможностью является ее установка в командной строке после параметра -D. Давайте продолжим и переключимся к базе данных gentoo.
Database changed
Сейчас, текущей базой данных является созданная нами ранее база данных gentoo. Теперь, когда мы ей пользуемся, мы можем начать создавать таблицы и заполнять их информацией.
Создание и модификация баз данных и таблиц в MySQL.
Создание баз данных
Типы данных в MySQL
Создание и модификация таблиц
Создание баз данных.

 
mysqlbin>mysqladmin -u root -p create data1
Enter password:******
Database "data1" created.
mysqlbin>
По умолчанию,root имеет доступ ко всем базам данных и таблицам.Перейти в созданную базу данных можно,используя команду mysql.

 
mysqlbin>mysql -u root -p data1
Enter password:******
Welcome to MySQL monitor.
Или,находясь в другой базе данных,например в mysql ввести команду:

 
mysql>use data1
Database changed.
Теперь можно создавать таблицы и вводить информацию.
Специальные пользователи MySQL.
monty
Следует заметить,что следует внести данные как для localhost,так и для ‘%’,то есть любого хоста в сети.
admin
dummy
Пользователь,который может входить на сервер без пароля ,но только с локального хоста.Все
 глобальные привилегии устанавливаются в ‘N’ кроме «USAGE»,что позволяет dummy создавать пользователей без привилегий.Права доступа к базам данных должны быть заданы root’ом.
Типы данных в MySQL.
Прежде чем создавать таблицы,необходимо ознакомиться с тем,какие типы данных поддерживает MySQL.Все они представлены в нижеследующей таблице:
Создание и модификация таблиц.
Cоздадим таблицу customers в БД data1:

 
mysql>use data1
Database changed.
mysql>create table customers (
emp_id int(4) not null auto_increment,
emp_name varchar(10) not null,
emp_lname varchar(15) not null,
address varchar(60) not null,
phone int(10),
primary key(emp_id));
Мы создали пустую таблицу.Вводить данные в нее можно несколькими способами:
 а)вручную,используя команду insert into;
 б)загрузить данные из текстового файла,что является более предпочтительным,особенно если нужно ввести несколько тысяч записей.Синтаксис этой команды будет описан позже.
 в)использовать утилиту mysqlimport также для загрузки данных из текстового файла.
Пример ввода данных вручную:

 
mysql>insert into customers (emp_id,emp_name,emp_lname,address,phone)
-->values ('1001','John','Walker','New York','1236458794');
или
mysql>insert into customers values ('1001','John','Walker','New York','1236458794');
//работает и то,и то.
Что касается auto_increment в столбце emp_id,это означает,что числовое значение этого столбца будет автоматически увеличиваться на единицу с каждой новой записью.То есть,если мы ввели значение 1001,то следующее будет 1002 и т.д.Значение в такой столбец вводится один раз для задания точки отсчета,а дальше сервер будет сам подставлять нужные значения.
Синтаксис команды LOAD DATA INFILE.
DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 't']
[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '' ]]
[LINES TERMINATED BY 'n']
[IGNORE number LINES]
[(col_name,...)]
Предположим,существует некоторый текстовый файл 123.txt,содержащий 2000 записей,которые нужно внести в таблицу.Нужно создать таблицу,имеющую такую же структуру и такое же число полей,как и файл(а также подходящие типы данных).Предположим,что поля в файле разделены запятыми.Кроме того,файл должен находиться в нужной базе данных.
 Вводим следующую команду:

 LOAD DATA INFILE ‘123.txt’ into table customers fields terminated by ‘,’;
Это все.Данные из файла помещаются в таблицу.
Для модификации таблиц и данных в них используются команды update и alter table.
 Рассмотрим их действие на примере таблицы customers:
Пример действия команды alter table:
mysql>alter table customers add last_accessed date;
mysql>insert into customers (last_accessed) values ('20000512') where
-->emp_id='1003';
mysql>alter table customers rename customers buyers;
mysql>alter table buyers change phone telephone int(11);
После выполнения этих команд таблица примет следующий вид:
Можете проверить это сами 🙂
Изменение данных в таблицах производится с помощью команды update.Для примера возьмем ту же таблицу buyers.

 
mysql>update buyers set address='Seattle' where emp_lname='Smith';
Для модификации таблиц используются также команды Drop и Delete.
Delete-удаляет строку из таблицы.Например,если ввести команду
 delete from buyers where emp_id=’1002′
будет удалена строка для Bill Smith.
Drop-если используется в Alter table удаляет колонку из таблицы.
Команда Drop также используется для удаления таблиц и баз данных.Пользоваться ею следует осторожно,так как любое удаление необратимо,восстановить данные можно только из резервной копии.
Общие проблемы и их разрешение.
Это очень важный шаг и необходимо быть внимательным.Единственный выход при этом в Windows-деинсталлировать сервер и установить по новой,благо еще ничего не настроено.
Если такую проблему испытывает пользователь,вероятно причина в неправильной настройке таблиц привилегий,в первую очередь та же установка пароля,или записи в таблицах db и host неверны.Рассмотрим это на простом примере:
select * from data2.authors;
Предположим,что jerry временно работает в другом отделе,который находится в другом домене,и когда он пытается получить доступ к базе данных,он получает Access denied,поскольку компьютерам этого домена не разрешен доступ к базе данных.В данном случае нужно поступить следующим образом:очистить поле Host в таблице Db,а в таблицу Host занести следующие данные:
В MySQL версий 3.22 и выше работает очень хорошая команда GRANT,с помощью которой можно предоставлять пользователям доступ к базам данных и таблицам без модификации таблиц привилегий вручную.
С помощью этой команды можно указывать привилегии на доступ даже к определенным столбцам таблицы:
Применение этой команды автоматически помещает данные в таблицы Db,Tables_priv и Column_priv,что избавляет от необходимости их ручной модификации.
Если нужно отнять у пользователя какую-либо привилегию,используйте команду REVOKE.