Рубрика: sql — mysql — postgres

Конспект: MySQL / mysql / mariadb / tool

Автор записи Автор: Evgeny Yakovlev
Дата записи 2021/06/08

Ссылки:

http://www.mysql.ru/faq/

https://htmlweb.ru/php/mysql.php
https://htmlweb.ru/php/mysql_example.php
https://htmlweb.ru/php/mysql_faq.php

http://www.spravkaweb.ru/

http://www.mysql.ru/faq/

https://htmlweb.ru/php/mysql.php

https://htmlweb.ru/php/mysql_example.php

https://htmlweb.ru/php/mysql_faq.php

http://www.spravkaweb.ru/

Бэкап:

mysqldump --all --add-drop-table [--all-databases] --force [--no-data] [-c] --password=password --user=user [база] [таблицы] > backup_file
-c - формировать в виде полных INSERT. 
--all-databases - бэкап всех баз
--no-data - бэкап только структуры таблиц в базах
[таблицы] - бэкапить только указанные таблицы

mysqldump -u -p dbname > /path/to/file.sql - резервная копия
mysqldump -u root -p -f mydatabase > /home/myname/mydatabasedump.sql - бэкап

mysqldump --all --add-drop-table [--all-databases] --force [--no-data] [-c] --password=password --user=user [база] [таблицы] > backup_file

-c - формировать в виде полных INSERT.

--all-databases - бэкап всех баз

--no-data - бэкап только структуры таблиц в базах

[таблицы] - бэкапить только указанные таблицы

mysqldump -u -p dbname > /path/to/file.sql - резервная копия

mysqldump -u root -p -f mydatabase > /home/myname/mydatabasedump.sql - бэкап

Восстановление:

mysql -u username -p dbname < /path/to/file.sql - восстановление
mysql -u root -p -f mydatabase < /home/myname/mydatabasedump.sql - восстановление

1 2	mysql -u username -p dbname < /path/to/file.sql - восстановление mysql -u root -p -f mydatabase < /home/myname/mydatabasedump.sql - восстановление

Краткое введение в MySQL:

СУБД MySQL - одна из множества баз данных, поддерживаемых в PHP. 
Система MySQL распространяется бесплатно и обладает достаточной мощностью для решения реальных задач.
SQL - это аббревиатура от слов Structured Query Language, что означает структурированный язык запросов. 
Этот язык является стандартным средством для доступа к различным базам данных.
Система MySQL представляет собой сервер, к которому могут подключаться пользователи удаленных компьютеров.
Для работы с базами данных удобно пользоваться средством, входящее в комплект Web-разработчика: Denwer phpMyAdmin. 
Здесь можно создать новую базу данных, создать новую таблицу в выбранной базе данных, 
заполнить таблицу данными, а также добавлять, удалять и редактировать данные.

СУБД MySQL - одна из множества баз данных, поддерживаемых в PHP.

Система MySQL распространяется бесплатно и обладает достаточной мощностью для решения реальных задач.

SQL - это аббревиатура от слов Structured Query Language, что означает структурированный язык запросов.

Этот язык является стандартным средством для доступа к различным базам данных.

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

Для работы с базами данных удобно пользоваться средством, входящее в комплект Web-разработчика: Denwer phpMyAdmin.

Здесь можно создать новую базу данных, создать новую таблицу в выбранной базе данных,

заполнить таблицу данными, а также добавлять, удалять и редактировать данные.

Основные типы данных:

INT -  Целое число
TINYINT - Маленькое целое число (-127 до 128 или от 0 до 255)
FLOAT - Вещественное число с плавающей точкой
DATE - Дата. Отображается в виде ГГГГ-ММ-ДД
TIME - Время. Отображается в виде ЧЧ:ММ:СС
DATETIME - Дата и время. Отображается в виде ГГГГ-ММ-ДДЧЧ:ММ:СС
YEAR[(2|4)] - Год. Можно определить двух- или четырех цифирный формат
CHAR(M) - Строка фиксированной длины М (M<=255)
VARCHAR(M) - Строка произвольной длины до М (M<=255)
TEXT - Длинные текстовые фрагменты (<=65535)
BLOB - Большие двоичные объекты (изображения, звуки)

INT - Целое число

TINYINT - Маленькое целое число (-127 до 128 или от 0 до 255)

FLOAT - Вещественное число с плавающей точкой

DATE - Дата. Отображается в виде ГГГГ-ММ-ДД

TIME - Время. Отображается в виде ЧЧ:ММ:СС

DATETIME - Дата и время. Отображается в виде ГГГГ-ММ-ДДЧЧ:ММ:СС

YEAR[(2|4)] - Год. Можно определить двух- или четырех цифирный формат

CHAR(M) - Строка фиксированной длины М (M<=255)

VARCHAR(M) - Строка произвольной длины до М (M<=255)

TEXT - Длинные текстовые фрагменты (<=65535)

BLOB - Большие двоичные объекты (изображения, звуки)

Каждый столбец после своего типа данных содержит и другие спецификаторы:

NOT NULL - Все строки таблицы должны иметь значение в этом атрибуте. Если не указано, поле может быть пустым (NULL)

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

PRIMARY KEY - Столбец является первичным ключом для таблицы. 
              Данные в этом столбце должны быть уникальными. 
              MySQL автоматически индексирует этот столбец

UNSIGNED - После целочисленного типа означает, что его значение может быть либо положительным, либо нулевым

COMMENT - Название столбца таблицы

NOT NULL - Все строки таблицы должны иметь значение в этом атрибуте. Если не указано, поле может быть пустым (NULL)

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

Если при вставке строк в таблицу оставлять такое поле пустым, MySQL автоматически генерирует уникальное значение идентификатора.

Это значение будет на единицу больше максимального значения, уже существующего в столбце.

В каждой таблице может быть не больше одного такого поля.

Столбцы с AUTO_INCREMENT должны быть проиндексированными

PRIMARY KEY - Столбец является первичным ключом для таблицы.

Данные в этом столбце должны быть уникальными.

MySQL автоматически индексирует этот столбец

UNSIGNED - После целочисленного типа означает, что его значение может быть либо положительным, либо нулевым

COMMENT - Название столбца таблицы

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

Например, цену всех книг повысили на 10%:
UPDATE books SET price = price * 1.1;

1 2	Например, цену всех книг повысили на 10%: UPDATE books SET price = price * 1.1;

Конструкция WHERE ограничит работу UPDATE определенным строками:

Например:
UPDATE books SET price = price * 1.05 WHERE price <= 250;

1 2	Например: UPDATE books SET price = price * 1.05 WHERE price <= 250;

Для удаления строк из базы данных используется оператор DELETE:

Ненужные строки указываются при помощи конструкции WHERE. 
Например, какие-то книги проданы:
DELETE FROM books WHERE quantity = 0;

Ненужные строки указываются при помощи конструкции WHERE.

Например, какие-то книги проданы:

DELETE FROM books WHERE quantity = 0;

Если нужно удалить все записи:

TRUNCATE TABLE table_name

1	TRUNCATE TABLE table_name

Очистка базы данных

mysqldump -uuser -ppassword --add-drop-table --no-data databasename | grep ^DROP | mysql -uuser -ppassword databasename - очистка базы данных без удаления базы данных.

1	mysqldump -uuser -ppassword --add-drop-table --no-data databasename \| grep ^DROP \| mysql -uuser -ppassword databasename - очистка базы данных без удаления базы данных.

Бэкап:

mysqldump --all --add-drop-table [--all-databases] --force [--no-data] [-c] --password=password --user=user [база] [таблицы] > backup_file
-c - формировать в виде полных INSERT. 
--all-databases - бэкап всех баз
--no-data - бэкап только структуры таблиц в базах
[таблицы] - бэкапить только указанные таблицы

mysqldump -u -p dbname > /path/to/file.sql - резервная копия
mysqldump -u root -p -f mydatabase > /home/myname/mydatabasedump.sql - бэкап

mysqldump --all --add-drop-table [--all-databases] --force [--no-data] [-c] --password=password --user=user [база] [таблицы] > backup_file

-c - формировать в виде полных INSERT.

--all-databases - бэкап всех баз

--no-data - бэкап только структуры таблиц в базах

[таблицы] - бэкапить только указанные таблицы

mysqldump -u -p dbname > /path/to/file.sql - резервная копия

mysqldump -u root -p -f mydatabase > /home/myname/mydatabasedump.sql - бэкап

Восстановление:

mysql -u username -p dbname < /path/to/file.sql - восстановление
mysql -u root -p -f mydatabase < /home/myname/mydatabasedump.sql - восстановление

1 2	mysql -u username -p dbname < /path/to/file.sql - восстановление mysql -u root -p -f mydatabase < /home/myname/mydatabasedump.sql - восстановление

Как подготовить к работе mysql после установки? (debian, centos)

Можно воспользоваться скриптом:  
mysql_secure_installation

1 2	Можно воспользоваться скриптом: mysql_secure_installation

Подключение к бд:

mysql -uUSERNAME -pPASSWORD DBNAME - подключение к базе DBNAME используя логин:USERNAME и пароль:PASSWORD

mysql -uroot -p - подключаемся для управления mysql

mysql -uUSERNAME -pPASSWORD DBNAME - подключение к базе DBNAME используя логин:USERNAME и пароль:PASSWORD

mysql -uroot -p - подключаемся для управления mysql

Выбор базы данных:

USE db_name; - выбрать базу для работы

1	USE db_name; - выбрать базу для работы

Перемещение по выбранной базе:

SHOW databases; - вывести список баз данных, к которым пользователь имеет доступ

SHOW tables; - вывести список таблиц, для текущей выбранной базы

DESCRIBE name_table; - посмотреть поля таблицы

!!! Для извлечения данных из таблицы служит оператор SELECT
Он извлекает данные из базы, выбирая строки, которые отвечают заданному критерию поиска. 
Оператор SELECT сопровождает немалое количество опций и вариантов использования.
Символ * означает, что необходимы все поля.

SELECT * FROM books;
SELECT * FROM tables_name; - показать содержимое таблицы tables_name;

Для получения доступа только к некоторому полю следует указать его имя в инструкции SELECT.
SELECT name_colum FROM tables_name; - вывести конкретный столбец из таблицы

Сортировать можно и по нескольким столбцам. 
SELECT author, title, price FROM books;
SELECT name_colum,name_colum2 FROM tables_name; - вывести конкретные столбцы из таблицы

Также можно ограничить вывод с помощью LIMIT
SELECT * FROM tables_name limit 5; - показать содержимое таблицы tables_name и ограничить пятью стоками;
SELECT * FROM users LIMIT 5 OFFSET 5; - посмотреть данные в таблице


Чтобы получить доступ к подмножеству строк в таблице, следует указать критерий выбора, который устанавливает конструкция WHERE:
% Соответствует любому количеству символов, даже нулевых
_ Соответствует ровно одному символу
Например, чтобы выбрать имеющиеся в наличии недорогие книги о PHP, надо составить запрос:
SELECT * FROM books WHERE
	price < 200 AND title LIKE '%PHP%' AND quantity != 0;

Для того, чтобы строки, извлеченные по запросу, перечислялись в определенном порядке, используется конструкция ORDER BY: 
По умолчанию порядок сортировки идет по возрастанию. 
Например:
SELECT * FROM books ORDER BY price;

Изменить порядок сортировки на обратный можно с помощью ключевого слова DESC:
SELECT * FROM books ORDER BY price DESC;

Вместо названий столбцов можно использовать их порядковые номера:
SELECT * FROM books ORDER BY 4, 2, 3;

SHOW databases; - вывести список баз данных, к которым пользователь имеет доступ

SHOW tables; - вывести список таблиц, для текущей выбранной базы

DESCRIBE name_table; - посмотреть поля таблицы

!!! Для извлечения данных из таблицы служит оператор SELECT

Он извлекает данные из базы, выбирая строки, которые отвечают заданному критерию поиска.

Оператор SELECT сопровождает немалое количество опций и вариантов использования.

Символ * означает, что необходимы все поля.

SELECT * FROM books;

SELECT * FROM tables_name; - показать содержимое таблицы tables_name;

Для получения доступа только к некоторому полю следует указать его имя в инструкции SELECT.

SELECT name_colum FROM tables_name; - вывести конкретный столбец из таблицы

Сортировать можно и по нескольким столбцам.

SELECT author, title, price FROM books;

SELECT name_colum,name_colum2 FROM tables_name; - вывести конкретные столбцы из таблицы

Также можно ограничить вывод с помощью LIMIT

SELECT * FROM tables_name limit 5; - показать содержимое таблицы tables_name и ограничить пятью стоками;

SELECT * FROM users LIMIT 5 OFFSET 5; - посмотреть данные в таблице

Чтобы получить доступ к подмножеству строк в таблице, следует указать критерий выбора, который устанавливает конструкция WHERE:

% Соответствует любому количеству символов, даже нулевых

_ Соответствует ровно одному символу

Например, чтобы выбрать имеющиеся в наличии недорогие книги о PHP, надо составить запрос:

SELECT * FROM books WHERE

price < 200 AND title LIKE '%PHP%' AND quantity != 0;

Для того, чтобы строки, извлеченные по запросу, перечислялись в определенном порядке, используется конструкция ORDER BY:

По умолчанию порядок сортировки идет по возрастанию.

Например:

SELECT * FROM books ORDER BY price;

Изменить порядок сортировки на обратный можно с помощью ключевого слова DESC:

SELECT * FROM books ORDER BY price DESC;

Вместо названий столбцов можно использовать их порядковые номера:

SELECT * FROM books ORDER BY 4, 2, 3;

Примеры создания базы данных:

Создание новой базы данных MySQL осуществляется при помощи SQL-команды CREATE DATABASE:
CREATE DATABASE IF NOT EXISTS `base` DEFAULT CHARACTER SET cp1251 COLLATE cp1251_bin


CREATE DATABASE db_name; - создать базу

CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci; - создать базу + установить кодировку и т.д.

Создание новой базы данных MySQL осуществляется при помощи SQL-команды CREATE DATABASE:

CREATE DATABASE IF NOT EXISTS `base` DEFAULT CHARACTER SET cp1251 COLLATE cp1251_bin

CREATE DATABASE db_name; - создать базу

CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci; - создать базу + установить кодировку и т.д.

Пример создания таблицы:

Создание новой таблицы осуществляется при помощи SQL-команды CREATE TABLE

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); - Создать таблицу


Например, таблица books для книжного магазина будет содержать пять полей: 
ISBN, автор, название, цена и количество экземпляров:
CREATE TABLE books (ISBN CHAR(13) NOT NULL,
                    PRIMARY KEY (ISBN),
                    author VARCHAR(30),
                    title VARCHAR(60),
                    price FLOAT(4,2),
                    quantity TINYINT UNSIGNED);

Чтобы избежать сообщения об ошибке, если таблица уже есть необходимо изменить первую строчку, добавив фразу "IF NOT EXISTS":
CREATE TABLE IF NOT EXISTS books ...

Для создания авто обновляемого поля с текущей датой типа TIMESTAMP или DATETIME используйте следующую конструкцию:
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Создание новой таблицы осуществляется при помощи SQL-команды CREATE TABLE

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); - Создать таблицу

Например, таблица books для книжного магазина будет содержать пять полей:

ISBN, автор, название, цена и количество экземпляров:

CREATE TABLE books (ISBN CHAR(13) NOT NULL,

PRIMARY KEY (ISBN),

author VARCHAR(30),

title VARCHAR(60),

price FLOAT(4,2),

quantity TINYINT UNSIGNED);

Чтобы избежать сообщения об ошибке, если таблица уже есть необходимо изменить первую строчку, добавив фразу "IF NOT EXISTS":

CREATE TABLE IF NOT EXISTS books ...

Для создания авто обновляемого поля с текущей датой типа TIMESTAMP или DATETIME используйте следующую конструкцию:

CREATE TABLE t1 (

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

Добавление данных в эту таблицу осуществляется при помощи SQL-команды INSERT:

Например:
INSERT INTO books ( ISBN, author, title, price, quantity )
           VALUES ('5-8459-0184-7', 'Зандстра Мэт',
                   'Освой самостоятельно PHP4 за 24 часа', '129', '5');

Например:

INSERT INTO books ( ISBN, author, title, price, quantity )

VALUES ('5-8459-0184-7', 'Зандстра Мэт',

'Освой самостоятельно PHP4 за 24 часа', '129', '5');

Копирование таблиц:

INSERT INTO comercial_tests (id, place_id, keywords) SELECT id, place_id, keywords FROM commercials; - Скопировать данные из одной таблицы в другую

1	INSERT INTO comercial_tests (id, place_id, keywords) SELECT id, place_id, keywords FROM commercials; - Скопировать данные из одной таблицы в другую

Права доступа и привилегии пользователя:

!!! после изменения \ добавления пользователя не забываем выполнить FLUSH PRIVILEGES;

ALL PRIVILEGES – предоставляет полный доступ к выбранной БД
CREATE – разрешает пользователям создавать новые БД
SELECT – разрешает делать выборку данных
INSERT – позволяет вносить новые записи в таблицы
UPDATE – разрешает менять ранее созданные записи в таблицах
DELETE – разрешает удалять записи из таблиц
DROP – дает возможность удалять записи в БД
GRANT OPTION – позволяет пользователю предоставлять или отзывать права других пользователей

Вот пример синтаксиса, в котором только три типа привилегий предоставляются пользователю:
GRANT SELECT, INSERT, DELETE ON database.* TO 'user'@'localhost';

Если нужно указать несколько прав, то их необходимо прописать через запятую:
GRANT [права], [права] ON *.* TO ‘user’@'localhost’;

Если нужно удалить права у выбранного пользователя:
REVOKE [права] ON [наименование БД].[ наименование таблицы] FROM ‘username’@'localhost’;

Забрать все права:
REVOKE ALL PRIVILEGES ON *.* FROM ‘user @'localhost’;

Полностью удалить пользователя можно командой:
DROP USER ‘user @'localhost’;

Изменить IP для пользователя:
rename user user_name@'10.0.0.100' to user_name@'10.0.0.200';

!!! после изменения \ добавления пользователя не забываем выполнить FLUSH PRIVILEGES;

ALL PRIVILEGES – предоставляет полный доступ к выбранной БД

CREATE – разрешает пользователям создавать новые БД

SELECT – разрешает делать выборку данных

INSERT – позволяет вносить новые записи в таблицы

UPDATE – разрешает менять ранее созданные записи в таблицах

DELETE – разрешает удалять записи из таблиц

DROP – дает возможность удалять записи в БД

GRANT OPTION – позволяет пользователю предоставлять или отзывать права других пользователей

Вот пример синтаксиса, в котором только три типа привилегий предоставляются пользователю:

GRANT SELECT, INSERT, DELETE ON database.* TO 'user'@'localhost';

Если нужно указать несколько прав, то их необходимо прописать через запятую:

GRANT [права], [права] ON *.* TO ‘user’@'localhost’;

Если нужно удалить права у выбранного пользователя:

REVOKE [права] ON [наименование БД].[ наименование таблицы] FROM ‘username’@'localhost’;

Забрать все права:

REVOKE ALL PRIVILEGES ON *.* FROM ‘user @'localhost’;

Полностью удалить пользователя можно командой:

DROP USER ‘user @'localhost’;

Изменить IP для пользователя:

rename user user_name@'10.0.0.100' to user_name@'10.0.0.200';

Создание пользователя:

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password123'; - создать нового пользователя 
GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@'localhost'; - дать права\доступ пользователю  к соответствующей базе
flush privileges; -  перезагрузить таблицы назначения привилегий

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password123'; - создать нового пользователя

GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@'localhost'; - дать права\доступ пользователю к соответствующей базе

flush privileges; - перезагрузить таблицы назначения привилегий

Создание пользователя, не полные права:

CREATE USER 'USER_NAME'@'localhost' IDENTIFIED BY 'SUPER_MEGA_PASSWORD';
GRANT SELECT ON DBNAME.TABLE_NAME_1 TO 'USER_NAME'@'localhost';
GRANT SELECT ON DBNAME.TABLE_NAME_2 TO 'USER_NAME'@'localhost';
flush privileges;

CREATE USER 'USER_NAME'@'localhost' IDENTIFIED BY 'SUPER_MEGA_PASSWORD';

GRANT SELECT ON DBNAME.TABLE_NAME_1 TO 'USER_NAME'@'localhost';

GRANT SELECT ON DBNAME.TABLE_NAME_2 TO 'USER_NAME'@'localhost';

flush privileges;

Смена пароля пользователя в MySQL

Версия mysql 5.7.6 и более современная:
ALTER USER 'имя пользователя'@'localhost' IDENTIFIED BY 'New_Password';
FLUSH PRIVILEGES;

Версия 5.7.5 и древнее:
SET PASSWORD FOR 'имя пользователя'@'localhost' = PASSWORD('New_Password');
FLUSH PRIVILEGES;

Версия mysql 5.7.6 и более современная:

ALTER USER 'имя пользователя'@'localhost' IDENTIFIED BY 'New_Password';

FLUSH PRIVILEGES;

Версия 5.7.5 и древнее:

SET PASSWORD FOR 'имя пользователя'@'localhost' = PASSWORD('New_Password');

FLUSH PRIVILEGES;

Посмотреть \ Показать права доступа пользователя в MySQL:

Для того чтобы посмотреть, какие права доступа выданы пользователю MySQL, достаточно в консоли прописать команду:

SHOW GRANTS FOR 'user'@'localhost';

Для того чтобы посмотреть, какие права доступа выданы пользователю MySQL, достаточно в консоли прописать команду:

SHOW GRANTS FOR 'user'@'localhost';

Посмотреть пользователей mysql можно вот так;

use mysql;
select user,password,host from user;

select * from user order by host desc;

use mysql;

select user,password,host from user;

select * from user order by host desc;

Как запретить пользователю все операции с базой данных?

Revoke ALL PRIVILEGES on *.* from usernameS@hostname;

1	Revoke ALL PRIVILEGES on . from usernameS@hostname;

Как изменить забытый MySQL административный пароль?

1. перезапустить mysqld с опцией --skip-grant-tables
2. mysqladmin -h хост -u пользователь password 'новый пароль'

1 2	1. перезапустить mysqld с опцией --skip-grant-tables 2. mysqladmin -h хост -u пользователь password 'новый пароль'

Очистка базы данных без удаления базы:

mysqldump -uuser -ppassword --add-drop-table --no-data databasename | grep ^DROP | mysql -uuser -ppassword databasename - очистка базы данных без удаления базы данных, будут удалены все таблицы.

1	mysqldump -uuser -ppassword --add-drop-table --no-data databasename \| grep ^DROP \| mysql -uuser -ppassword databasename - очистка базы данных без удаления базы данных, будут удалены все таблицы.

Удалить базу:

DROP DATABASE dbname; - Удалить базу данных

1	DROP DATABASE dbname; - Удалить базу данных

Как выполнить поиск по регулярному выражению?

Вот например так
mysql> select rec_id from rec where rec_id regexp "^1[0]+$" limit 10;
+--------+
| rec_id |
+--------+
| 10     |
| 100    |
| 1000   |
+--------+
Данный запрос выведет все записи в которых ключевое поле это степень десяти. 
Обратите внимание на то что регулярное выражение не имеет ограничителей !

Вот например так

mysql> select rec_id from rec where rec_id regexp "^1[0]+$" limit 10;

+--------+

| rec_id |

+--------+

| 10 |

| 100 |

| 1000 |

+--------+

Данный запрос выведет все записи в которых ключевое поле это степень десяти.

Обратите внимание на то что регулярное выражение не имеет ограничителей !

Запуск произвольной команды из сеанса MySQL под Linux:

Заливаем дамп базы из файла в базу, не выходя из сеанса MySQL:
mysql> \! mysql –uUSERNAME –pPASSWORD DATABASE < DATABSE.sql

Выводим список процессов Linux:
mysql> \! top

Оценим объем логических разделов жесткого диска:
mysql> \! df -h

Заливаем дамп базы из файла в базу, не выходя из сеанса MySQL:

mysql> \! mysql –uUSERNAME –pPASSWORD DATABASE < DATABSE.sql

Выводим список процессов Linux:

mysql> \! top

Оценим объем логических разделов жесткого диска:

mysql> \! df -h

Tools:

Подключение:
mysql -uUSERNAME -pPASSWORD DBNAME - подключение к базе DBNAME используя логин:USERNAME и пароль:PASSWORD
mysql -uroot -p - логинемся для управления mysql

USE db_name; - Выбрать базу для работы

SHOW databases; - Вывести список баз данных, к которым пользователь имеет доступ:

DROP DATABASE dbname; - Удалить базу данных

SHOW tables; - Вывести список таблиц, для текущей выбранной базы:

CREATE DATABASE db_name; - создать базу

CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci; - создать базу + установить кодировку и тд

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password123'; - создать нового пользователя 
GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@'localhost'; - дать права\доступ пользователю  к соответствующей базе
flush privileges; -  перезагрузить таблицы назначения привилегий

rename user user_name@'10.0.0.100' to user_name@'10.0.0.200'; - Изменить IP для пользователя user_name@'10.0.0.100'

 
ALTER USER 'имя пользователя'@'localhost' IDENTIFIED BY 'New_Password'; - Смена пароля пользователя 
FLUSH PRIVILEGES; - Применить привилегии


INSERT INTO comercial_tests (id, place_id, keywords) SELECT id, place_id, keywords FROM commercials; - Скопировать данные из одной таблицы в другую

DESCRIBE pet; - Посмотреть поля таблицы

SELECT * FROM users LIMIT 5 OFFSET 5; - Посмотреть данные в таблице

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); - Создать таблицу

Пользователи и привилегии:
use mysql;
select * form user; - показать все из таблицы user
select user,host from user; - показать всех пользователей из таблицы user
select user,host from user where user like 'admin'; - показать всех пользователей с именем admin
show grants;  - показать привилегии текущего пользователя
show grants for 'user'@'localhost' - показать все привилегии пользователя



show open tables; - показать открытые таблицы
show plugins; - показать используемые плагины 

SHOW MASTER STATUS

show master status; - показать состояние мастера и репликации

Подключение:

mysql -uUSERNAME -pPASSWORD DBNAME - подключение к базе DBNAME используя логин:USERNAME и пароль:PASSWORD

mysql -uroot -p - логинемся для управления mysql

USE db_name; - Выбрать базу для работы

SHOW databases; - Вывести список баз данных, к которым пользователь имеет доступ:

DROP DATABASE dbname; - Удалить базу данных

SHOW tables; - Вывести список таблиц, для текущей выбранной базы:

CREATE DATABASE db_name; - создать базу

CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci; - создать базу + установить кодировку и тд

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password123'; - создать нового пользователя

GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@'localhost'; - дать права\доступ пользователю к соответствующей базе

flush privileges; - перезагрузить таблицы назначения привилегий

rename user user_name@'10.0.0.100' to user_name@'10.0.0.200'; - Изменить IP для пользователя user_name@'10.0.0.100'

ALTER USER 'имя пользователя'@'localhost' IDENTIFIED BY 'New_Password'; - Смена пароля пользователя

FLUSH PRIVILEGES; - Применить привилегии

INSERT INTO comercial_tests (id, place_id, keywords) SELECT id, place_id, keywords FROM commercials; - Скопировать данные из одной таблицы в другую

DESCRIBE pet; - Посмотреть поля таблицы

SELECT * FROM users LIMIT 5 OFFSET 5; - Посмотреть данные в таблице

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); - Создать таблицу

Пользователи и привилегии:

use mysql;

select * form user; - показать все из таблицы user

select user,host from user; - показать всех пользователей из таблицы user

select user,host from user where user like 'admin'; - показать всех пользователей с именем admin

show grants; - показать привилегии текущего пользователя

show grants for 'user'@'localhost' - показать все привилегии пользователя

show open tables; - показать открытые таблицы

show plugins; - показать используемые плагины

SHOW MASTER STATUS

show master status; - показать состояние мастера и репликации

sql - mysql - postgres

mysql \ miradb \ centos 8 \ установка \ настройка

Автор записи Автор: Evgeny Yakovlev
Дата записи 2019/02/20

ссылки

https://mariadb.org/
https://mariadb.com/kb/en/
https://www.youtube.com/watch?v=dZR-vJLm8l8&ab_channel=YuriyLebedev

https://mariadb.org/

https://mariadb.com/kb/en/

https://www.youtube.com/watch?v=dZR-vJLm8l8&ab_channel=YuriyLebedev

Установка

yum update - обновим установленные пакеты
yam serach mariadb - смотрим какие есть пакеты mariadb
yum install mariadb mariadb-server - установим пакет mariadb

#yum install  mariadb-server - можно и так он сам загрузит зависимости mariadb 

systemctl status mariadb - проверяем состояние службы mariadb

systemctl enable mariadb - включаем авто запуск mariadb
systemctl start  mariadb - запускаем службу mariadb
#systemctl enable mariadb --now - запустить и добавить в авто загрузку (вместо команд "systemctl enable mariadb" и "systemctl start  mariadb")

systemctl status mariadb - проверяем состояние службы mariadb
ss -tupln | grep mysqld  - видим что mariadb заработала на порту 3306

systemctl restart mariadb.service - перезапуск демона mariadb в случаи изменения конфигурации может быть полезно

man mysql - документация

yum update - обновим установленные пакеты

yam serach mariadb - смотрим какие есть пакеты mariadb

yum install mariadb mariadb-server - установим пакет mariadb

#yum install mariadb-server - можно и так он сам загрузит зависимости mariadb

systemctl status mariadb - проверяем состояние службы mariadb

systemctl enable mariadb - включаем авто запуск mariadb

systemctl start mariadb - запускаем службу mariadb

#systemctl enable mariadb --now - запустить и добавить в авто загрузку (вместо команд "systemctl enable mariadb" и "systemctl start mariadb")

systemctl status mariadb - проверяем состояние службы mariadb

ss -tupln | grep mysqld - видим что mariadb заработала на порту 3306

systemctl restart mariadb.service - перезапуск демона mariadb в случаи изменения конфигурации может быть полезно

man mysql - документация

Первоначальная настройка mysql_secure_installation

mysql_secure_installation - запустится скрипт начальной настройки mysql

вопросы
Enter current password for root (enter for none): - жмем enter по умолчанию у root_mysql нет пароля 
Set root password? [Y/n] - жмем "y" что бы задать пароль root_mysql
Remove anonymous users? [Y/n] - жмем "y" что бы убрать анонимных пользователей
Disallow root login remotely? [Y/n] - жмем "y" что бы запретить доступ root_mysql по сети
Reload privilege tables now? [Y/n] - жмем "y" что бы перезапустить привилегии

mysql_secure_installation - запустится скрипт начальной настройки mysql

вопросы

Enter current password for root (enter for none): - жмем enter по умолчанию у root_mysql нет пароля

Set root password? [Y/n] - жмем "y" что бы задать пароль root_mysql

Remove anonymous users? [Y/n] - жмем "y" что бы убрать анонимных пользователей

Disallow root login remotely? [Y/n] - жмем "y" что бы запретить доступ root_mysql по сети

Reload privilege tables now? [Y/n] - жмем "y" что бы перезапустить привилегии

my.cnf — доп настройка

nano /etc/my.cnf.d/mariadb-server.cnf

[mysqld]  - основная секция

Популярные директивы:

bind-address=XXX.XXX.XXX.XXX - задать IP на котором будет работать mysql 
skip-networking=1 - включить игнорирование подключений по сети
port=X - сменить порт по умолчанию(3306) на X

nano /etc/my.cnf.d/mariadb-server.cnf

[mysqld] - основная секция

Популярные директивы:

bind-address=XXX.XXX.XXX.XXX - задать IP на котором будет работать mysql

skip-networking=1 - включить игнорирование подключений по сети

port=X - сменить порт по умолчанию(3306) на X

Пример работы с базой данных и таблицами в консоле:

mysql -u root -p 

show databases; - показать доступные базы данных  (information_schema, mysql, performance_schema - базы данных по умолчанию)
crate database demodb5; - создать базу данных demodb5

use demodb5; - выбрать базу данных, сменить базу данных
show tables; - показать таблицы в выбранной базе данных

create table demotab(col01 int primary key,col02 nvarchar(50)); - создать таблицу demotab, два столбца, первый столбец col01 из цыфер, второй столбец col02 до 50 символов
create table demo(nl nvarchar(15), n2 nvarchar(20)); - создать таблицу demo, первый столбец 15 символов, второй столбец 20 символов

describe demotab; - показать логическую структуру таблицы demotab
describe demo; - показать логическую структуру таблицы demotab


insert into demotab values (1, 'Chto to tut aga'), (2, 'И сюда что то я записал' ); - добавим две записи в таблицу demodb
insert into demo values ('Алексей', 'Иванов');

select * from demotab; - показать содержимое таблицы demotab
select * from demo; - показать содержимое таблицы demo


mysqldump -uuser -ppassword --add-drop-table --no-data databasename | grep ^DROP | mysql -uuser -ppassword databasename - очистка базы данных без удаления базы данных.

mysql -u root -p

show databases; - показать доступные базы данных (information_schema, mysql, performance_schema - базы данных по умолчанию)

crate database demodb5; - создать базу данных demodb5

use demodb5; - выбрать базу данных, сменить базу данных

show tables; - показать таблицы в выбранной базе данных

create table demotab(col01 int primary key,col02 nvarchar(50)); - создать таблицу demotab, два столбца, первый столбец col01 из цыфер, второй столбец col02 до 50 символов

create table demo(nl nvarchar(15), n2 nvarchar(20)); - создать таблицу demo, первый столбец 15 символов, второй столбец 20 символов

describe demotab; - показать логическую структуру таблицы demotab

describe demo; - показать логическую структуру таблицы demotab

insert into demotab values (1, 'Chto to tut aga'), (2, 'И сюда что то я записал' ); - добавим две записи в таблицу demodb

insert into demo values ('Алексей', 'Иванов');

select * from demotab; - показать содержимое таблицы demotab

select * from demo; - показать содержимое таблицы demo

mysqldump -uuser -ppassword --add-drop-table --no-data databasename | grep ^DROP | mysql -uuser -ppassword databasename - очистка базы данных без удаления базы данных.

sql - mysql - postgres

MySQL fails on: mysql “ERROR 1524 (HY000): Plugin ‘auth_socket’ is not loaded”

Автор записи Автор: Evgeny Yakovlev
Дата записи 2019/01/12

Ссылки

https://ru.stackoverflow.com/questions/552936/%D0%9E%D1%88%D0%B8%D0%B1%D0%BA%D0%B0-%D0%B2-phpmyadmin-access-denied-for-user
https://stackoverflow.com/questions/37879448/mysql-fails-on-mysql-error-1524-hy000-plugin-auth-socket-is-not-loaded
https://toster.ru/q/389804
https://toster.ru/q/448897
http://qaru.site/questions/32547/mysql-fails-on-mysql-error-1524-hy000-plugin-authsocket-is-not-loaded

https://ru.stackoverflow.com/questions/552936/%D0%9E%D1%88%D0%B8%D0%B1%D0%BA%D0%B0-%D0%B2-phpmyadmin-access-denied-for-user

https://stackoverflow.com/questions/37879448/mysql-fails-on-mysql-error-1524-hy000-plugin-auth-socket-is-not-loaded

https://toster.ru/q/389804

https://toster.ru/q/448897

http://qaru.site/questions/32547/mysql-fails-on-mysql-error-1524-hy000-plugin-authsocket-is-not-loaded

Решение:

1. first, run these bash commands
sudo /etc/init.d/mysql stop # stop mysql service
sudo mysqld_safe --skip-grant-tables & # start mysql without password
# enter -> go
mysql -uroot # connect to mysql

2. then run mysql commands => copy paste this to cli manually
use mysql; # use mysql table
update user set authentication_string=PASSWORD("") where User='root'; # update password to nothing
update user set plugin="mysql_native_password" where User='root'; # set password resolving to default mechanism for root user

flush privileges;
quit;

3. run more bash commands
sudo /etc/init.d/mysql stop 
sudo /etc/init.d/mysql start # reset mysql
# try login to database, just press enter at password prompt because your password is now blank
mysql -u root -p

1. first, run these bash commands

sudo /etc/init.d/mysql stop # stop mysql service

sudo mysqld_safe --skip-grant-tables & # start mysql without password

# enter -> go

mysql -uroot # connect to mysql

2. then run mysql commands => copy paste this to cli manually

use mysql; # use mysql table

update user set authentication_string=PASSWORD("") where User='root'; # update password to nothing

update user set plugin="mysql_native_password" where User='root'; # set password resolving to default mechanism for root user

flush privileges;

quit;

3. run more bash commands

sudo /etc/init.d/mysql stop

sudo /etc/init.d/mysql start # reset mysql

# try login to database, just press enter at password prompt because your password is now blank

mysql -u root -p

sql - mysql - postgres

MySQL Сброс пароля пользователя root

Автор записи Автор: Evgeny Yakovlev
Дата записи 2019/01/08

Сброс пароля root mysql

0.Найдите конфигурационный файл my.cnf и добавьте в секцию [mysqld] директиву skip-grant-tables, которая отключит проверку прав доступа
тут nano nano /etc/mysql/mariadb.conf.d/50-server.cnf
тут nano /etc/mysql/my.cnf
--------------------------
[mysqld]
...
skip-grant-tables
--------------------------

2. После чего перезагрузите сервер. 

3. Теперь вы можете входить MySQL без пароля, войдите и переустановите пароль
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password'); 

4. После этого следует убрать директиву skip-grant-tables из my.cnf и перезапустить сервер.

0.Найдите конфигурационный файл my.cnf и добавьте в секцию [mysqld] директиву skip-grant-tables, которая отключит проверку прав доступа

тут nano nano /etc/mysql/mariadb.conf.d/50-server.cnf

тут nano /etc/mysql/my.cnf

--------------------------

[mysqld]

...

skip-grant-tables

--------------------------

2. После чего перезагрузите сервер.

3. Теперь вы можете входить MySQL без пароля, войдите и переустановите пароль

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

4. После этого следует убрать директиву skip-grant-tables из my.cnf и перезапустить сервер.

0. /etc/init.d/mysql stop
1. mysqld_safe --skip-grant-tables &
2. mysql -uroot
3. UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root'; - где newpwd - новый пароль.
4. FLUSH PRIVILEGES;
5. /etc/init.d/mysql stop
6. /etc/init.d/mysql start

0. /etc/init.d/mysql stop

1. mysqld_safe --skip-grant-tables &

2. mysql -uroot

3. UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root'; - где newpwd - новый пароль.

4. FLUSH PRIVILEGES;

5. /etc/init.d/mysql stop

6. /etc/init.d/mysql start

Для этого необходимо выполнить всего несколько шагов:

1. Останавливаем mysql.
service mysqld stop
2. Запускаем mysql следующей командой:
/usr/bin/mysqld_safe --skip-grant-tables --user=root &
3. Запускаем клиента mysql
mysql -u root
4. Выполняем sql запрос
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
где newpwd - новый пароль.
5. Применяем изменения
FLUSH PRIVILEGES;
6. Выходим из mysql клиента выполнив в нем команду exit
7. Перегружаем mysql сервер.
service mysqld restart

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

1. Останавливаем mysql.

service mysqld stop

2. Запускаем mysql следующей командой:

/usr/bin/mysqld_safe --skip-grant-tables --user=root &

3. Запускаем клиента mysql

mysql -u root

4. Выполняем sql запрос

UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';

где newpwd - новый пароль.

5. Применяем изменения

FLUSH PRIVILEGES;

6. Выходим из mysql клиента выполнив в нем команду exit

7. Перегружаем mysql сервер.

service mysqld restart

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

Сброс пароля для debian-sys-maint:

0. killall mysqld
1. mysqld_safe --skip-grant-table
2. Жмем ctrl+z
3. bg
4. mysql -u root
5. UPDATE mysql.user SET Password = PASSWORD( 'новый пароль' ) WHERE user.Host = 'localhost' AND user.User = 'debian-sys-maint';
6. exit
7. fg
8. ctrl+c
9. /etc/init.d/mysql start

0. killall mysqld

1. mysqld_safe --skip-grant-table

2. Жмем ctrl+z

3. bg

4. mysql -u root

5. UPDATE mysql.user SET Password = PASSWORD( 'новый пароль' ) WHERE user.Host = 'localhost' AND user.User = 'debian-sys-maint';

6. exit

7. fg

8. ctrl+c

9. /etc/init.d/mysql start

Посмотреть пароль:

cat /etc/mysql/debian.cnf

1	cat /etc/mysql/debian.cnf

sql - mysql - postgres

MySQL — ошибка Checking for corrupt, not cleanly closed and upgrade needing tables

Автор записи Автор: Evgeny Yakovlev
Дата записи 2018/12/30

Данное сообщение не является ошибкой:

MySQL - ошибка Checking for corrupt, not cleanly closed and upgrade needing tables

1	MySQL - ошибка Checking for corrupt, not cleanly closed and upgrade needing tables

Такой строкой скрипт запуска MySQL рассказывает. что он:

Проверяет наличие поврежденный таблиц
Проверяет наличие не закрытых таблиц
Проверяет наличие не обновленных таблиц, если вы обновили версию MySQL.
принудительно проверить вcе таблицы для спокойствия
mysqlcheck --check-upgrade --all-databases --auto-repair -u root -p
mysql_upgrade --force -u root -p

Проверяет наличие поврежденный таблиц

Проверяет наличие не закрытых таблиц

Проверяет наличие не обновленных таблиц, если вы обновили версию MySQL.

принудительно проверить вcе таблицы для спокойствия

mysqlcheck --check-upgrade --all-databases --auto-repair -u root -p

mysql_upgrade --force -u root -p

sql - mysql - postgres

PostgreSQL \ psql \ pg_dump \ pg_restore \ install

Автор записи Автор: Evgeny Yakovlev
Дата записи 2018/12/25

Установка postgres debian:

apt update
apt install postgresql postgresql-contrib

apt show postgresql


https://postgrespro.ru/docs/postgresql/9.6/app-pgrestore

apt update

apt install postgresql postgresql-contrib

apt show postgresql

https://postgrespro.ru/docs/postgresql/9.6/app-pgrestore

psql:

Все команды запускаются под пользователем postgres (postgresql-суперпользователь $ su - postgres) 
sudo su - postgres - входим пользователем под  postgres

psql - Можно передавать аргументы или использовать консоль.

Передача аргументов psql:

psql --help - помощь

psql -l - список баз данных

psql -d dbname - подключение к БД dbname

psql -f file.sql - выполнение команд из файла file.sql

psql -U postgres -d dbname -c "CREATE TABLE test(some_id serial PRIMARY KEY, some_text text);" - выполнение команды в базе dbname

psql -d dbname -H -c "SELECT * FROM test" -o test.html - вывод результата запроса в html-файл


Режим консоли psql:

psql - входим в консоль

\? - помощь по сокращённым командам 

\help - помощь по командам sql

\h - помощь по SQL-командам

\h create database - просмотр синтаксиса создания базы данных

\conninfo - проверка соединения

\c dbname - подсоединение к БД dbname

\l - список баз данных

\l+ - просмотр существующих баз данных c более детальным выводом(размер, описание баз данных)

\d table - структура таблицы table

\du - список всех пользователей и их привилегий

\dt - список всех таблиц

\dt+ - список всех таблиц с описанием

\dt *s* - список всех таблиц, содержащих s в имени

\df+ - просмотр списка доступных функций

\i FILE - выполнить команды из файла FILE

\o FILE - сохранить результат запроса в файл FILE

\a - переключение между режимами вывода: с/без выравнивания

\timing - включить отображение времени выполнения запроса

Все команды запускаются под пользователем postgres (postgresql-суперпользователь $ su - postgres)

sudo su - postgres - входим пользователем под postgres

psql - Можно передавать аргументы или использовать консоль.

Передача аргументов psql:

psql --help - помощь

psql -l - список баз данных

psql -d dbname - подключение к БД dbname

psql -f file.sql - выполнение команд из файла file.sql

psql -U postgres -d dbname -c "CREATE TABLE test(some_id serial PRIMARY KEY, some_text text);" - выполнение команды в базе dbname

psql -d dbname -H -c "SELECT * FROM test" -o test.html - вывод результата запроса в html-файл

Режим консоли psql:

psql - входим в консоль

\? - помощь по сокращённым командам

\help - помощь по командам sql

\h - помощь по SQL-командам

\h create database - просмотр синтаксиса создания базы данных

\conninfo - проверка соединения

\c dbname - подсоединение к БД dbname

\l - список баз данных

\l+ - просмотр существующих баз данных c более детальным выводом(размер, описание баз данных)

\d table - структура таблицы table

\du - список всех пользователей и их привилегий

\dt - список всех таблиц

\dt+ - список всех таблиц с описанием

\dt *s* - список всех таблиц, содержащих s в имени

\df+ - просмотр списка доступных функций

\i FILE - выполнить команды из файла FILE

\o FILE - сохранить результат запроса в файл FILE

\a - переключение между режимами вывода: с/без выравнивания

\timing - включить отображение времени выполнения запроса

Посмотреть размер таблицы:

sudo su - postgres - входим пользователем под  postgres

psql - входим в psql

psql -l - список баз данных

psql -d dbname - подключение к БД dbname

SELECT pg_size_pretty (pg_indexes_size('name_table')); - смотрим размер таблицы name_table

sudo su - postgres - входим пользователем под postgres

psql - входим в psql

psql -l - список баз данных

psql -d dbname - подключение к БД dbname

SELECT pg_size_pretty (pg_indexes_size('name_table')); - смотрим размер таблицы name_table

Просмотр списка и путей к конфигурационным файлам:

psql > SELECT name, setting FROM pg_settings WHERE category = 'File Locations';
# или список всех конфигурационных параметров
psql > show all;

psql > SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

# или список всех конфигурационных параметров

psql > show all;

Список активных соединений с информацией о:

pid процесса, выполняющегося запроса, пользователя, базы данных

psql > SELECT * FROM pg_stat_activity;

pid процесса, выполняющегося запроса, пользователя, базы данных

psql > SELECT * FROM pg_stat_activity;

Как получить последние 10 записей:

Если id индексируется, это будет очень эффективно. Естественно, это также может быть timestamp.
select * from NAMETABLE order by id desc limit 10;

SELECT * FROM your_table ORDER BY your_timestamp DESC LIMIT 10;

Если id индексируется, это будет очень эффективно. Естественно, это также может быть timestamp.

select * from NAMETABLE order by id desc limit 10;

SELECT * FROM your_table ORDER BY your_timestamp DESC LIMIT 10;

Создание индексов:

# primary key
psql > ALTER TABLE tableName ADD PRIMARY KEY (id);

# unique index
psql > CREATE UNIQUE INDEX indexName ON tableName (columnNames);

# primary key

psql > ALTER TABLE tableName ADD PRIMARY KEY (id);

# unique index

psql > CREATE UNIQUE INDEX indexName ON tableName (columnNames);

Настройка подключения к базе:

postgresql.conf
---------------
#listen_addresses = 'localhost' 
listen_addresses = '*' # разрешить подключение для всех
---------------
!!! после изменений  перезапустить postgres

pg_hba.conf
-----------
host all all 0.0.0.0/0 password # разрешения подключения для всех
-----------

postgresql.conf

---------------

#listen_addresses = 'localhost'

listen_addresses = '*' # разрешить подключение для всех

---------------

!!! после изменений перезапустить postgres

pg_hba.conf

-----------

host all all 0.0.0.0/0 password # разрешения подключения для всех

-----------

pg_dump — список наиболее часто используемых опций:

-h host - хост, если не указан то используется localhost или значение из переменной окружения PGHOST.

-p port - порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.

-u - пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.

-a, --data-only - дамп только данных, по-умолчанию сохраняются данные и схема.

-b - включать в дамп большие объекты (blog'и).

-s, --schema-only - дамп только схемы.

-C, --create - добавляет команду для создания БД.

-c - добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).

-O - не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).

-F, --format {c|t|p} - выходной формат дампа, custom, tar, или plain text.

-t, --table=TABLE - указываем определенную таблицу для дампа.

-v, --verbose - вывод подробной информации.

-D, --attribute-inserts - дамп используя команду INSERT с списком имен свойств.

-h host - хост, если не указан то используется localhost или значение из переменной окружения PGHOST.

-p port - порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.

-u - пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.

-a, --data-only - дамп только данных, по-умолчанию сохраняются данные и схема.

-b - включать в дамп большие объекты (blog'и).

-s, --schema-only - дамп только схемы.

-C, --create - добавляет команду для создания БД.

-c - добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).

-O - не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).

-F, --format {c|t|p} - выходной формат дампа, custom, tar, или plain text.

-t, --table=TABLE - указываем определенную таблицу для дампа.

-v, --verbose - вывод подробной информации.

-D, --attribute-inserts - дамп используя команду INSERT с списком имен свойств.

Бекап \ архивирования таблиц:

В PostgreSQL есть две утилиты для бекапа pg_dump и pg_dumpall. 
pg_dump - используется для бекапа одной базы 

pg_dump mydatabase > mydatabase.sql - бекап базы данных с именем mydatabase

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb - создание бекапа базы mydb, в сжатом виде
 
pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb - создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb - создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

pg_dump -a -t table_name -f file_name database_name - Дамп данных только одной таблицы. 
                                                      Если нужно создать резервную копию нескольких таблиц, то имена этих таблиц перечисляются с помощью ключа -t для каждой таблицы.

pg_dump -h localhost  -O -F p -c -U postgres mydb | gzip -c > mydb.gz - создание резервной копии с сжатием в gz

В PostgreSQL есть две утилиты для бекапа pg_dump и pg_dumpall.

pg_dump - используется для бекапа одной базы

pg_dump mydatabase > mydatabase.sql - бекап базы данных с именем mydatabase

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb - создание бекапа базы mydb, в сжатом виде

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb - создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb - создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

pg_dump -a -t table_name -f file_name database_name - Дамп данных только одной таблицы.

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

pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz - создание резервной копии с сжатием в gz

Бекап всех баз данных используя команду pg_dumpall:

pg_dumpall - для бекапа всех баз и сервера в целом (необходимо запускать под postgresql-суперпользователем)
pg_dumpall > all.sql

1 2	pg_dumpall - для бекапа всех баз и сервера в целом (необходимо запускать под postgresql-суперпользователем) pg_dumpall > all.sql

Проверка бекапа:

grep "^[\]connect" all.sql
\connect db1
\connect db2

grep "^[\]connect" all.sql

\connect db1

\connect db2

В PostgreSQL есть две утилиты для восстановления базы из бекапа:

psql - восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
pg_restore - восстановление сжатых бекапов (tar);

psql -h localhost -U someuser -d dbname -f mydb.sql - восстановление всего бекапа с игнорированием ошибок

psql -h localhost -U someuser --set ON_ERROR_STOP=on -f mydb.sql - восстановление всего бекапа с остановкой на первой ошибке

Для восстановления из tar-архива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить

pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup

psql - восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);

pg_restore - восстановление сжатых бекапов (tar);

psql -h localhost -U someuser -d dbname -f mydb.sql - восстановление всего бекапа с игнорированием ошибок

psql -h localhost -U someuser --set ON_ERROR_STOP=on -f mydb.sql - восстановление всего бекапа с остановкой на первой ошибке

Для восстановления из tar-архива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить

pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup

Восстановление резервной копии БД, сжатой gz:

gunzip mydb.gz
psql -U postgres -d mydb -f mydb

!!! Начиная с версии 9.2 можно восстановить только структуру таблиц с помощью опции --section

# создаем БД
CREATE DATABASE mydb2;

# восстанавливаем
pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup

Обслуживание таблицы
VACUUM ANALYZE table;
REINDEX DATABASE dbName;
REINDEX TABLE tabName;

gunzip mydb.gz

psql -U postgres -d mydb -f mydb

!!! Начиная с версии 9.2 можно восстановить только структуру таблиц с помощью опции --section

# создаем БД

CREATE DATABASE mydb2;

# восстанавливаем

pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup

Обслуживание таблицы

VACUUM ANALYZE table;

REINDEX DATABASE dbName;

REINDEX TABLE tabName;

Перенос директории с данным (data directory):

!!! Сначала узнать текущий путь

# способ 1
$ su - postgres
$ psql
psql > SHOW data_directory;

# способ 2
$ ps ax | grep 'postgres -D'

Создадим новую директорию, назначим пользователя и инициализируем
mkdir -p /pathto/postgresql/data
chown -R postgres:postgres /pathto/postgresql

su - postgres
initdb -D /pathto/postgresql/data
Теперь надо подправить файл с сервисом, который стартует postgresql

# под arch linux
sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service
Environment=PGROOT=/pathto/postgresql/
PIDFile=/pathto/postgresql/data/postmaster.pid

!!! Сначала узнать текущий путь

# способ 1

$ su - postgres

$ psql

psql > SHOW data_directory;

# способ 2

$ ps ax | grep 'postgres -D'

Создадим новую директорию, назначим пользователя и инициализируем

mkdir -p /pathto/postgresql/data

chown -R postgres:postgres /pathto/postgresql

su - postgres

initdb -D /pathto/postgresql/data

Теперь надо подправить файл с сервисом, который стартует postgresql

# под arch linux

sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service

Environment=PGROOT=/pathto/postgresql/

PIDFile=/pathto/postgresql/data/postmaster.pid

Очищение таблицы

!!! Очищение таблицы tablename и обнуление счетчика с ID
!!! CASCADE нужен на случай если tablename связана с другой таблицей

TRUNCATE TABLE tablename RESTART IDENTITY CASCADE;

!!! Очищение таблицы tablename и обнуление счетчика с ID

!!! CASCADE нужен на случай если tablename связана с другой таблицей

TRUNCATE TABLE tablename RESTART IDENTITY CASCADE;

Удаление NULL у поля:

ALTER TABLE movies ALTER COLUMN year DROP NOT NULL;

1	ALTER TABLE movies ALTER COLUMN year DROP NOT NULL;

Утилита pgcli

pgcli - утилита командной строки с авто-дополнениям и подсветкой синтаксиса

Установка
pip install pgcli

Запуск
pgcli -U postgres -W dbname

pgcli - утилита командной строки с авто-дополнениям и подсветкой синтаксиса

Установка

pip install pgcli

Запуск

pgcli -U postgres -W dbname

Создание пользователя myuser с паролем ‘123’:

CREATE USER myuser WITH PASSWORD '123';

1	CREATE USER myuser WITH PASSWORD '123';

Создание базы данных mytestdb с владельцем myuser:

CREATE DATABASE mytestdb OWNER = myuser;

1	CREATE DATABASE mytestdb OWNER = myuser;

Предоставление пользователю myuser всех прав доступа к базе mytestdb:

GRANT ALL PRIVILEGES ON database mytestdb TO myuser;

1	GRANT ALL PRIVILEGES ON database mytestdb TO myuser;

Удаление базы данных mytestdb:

DROP DATABASE mytestdb;

1	DROP DATABASE mytestdb;

Удаление пользователя myuser:

DROP USER myuser;

1	DROP USER myuser;

Установка пароля для пользователя postgres:

passwd postgres - задаем пароль
su postgres - подключаемся пользователем postgres
psql - входим клиент postgres для управления db

Для смены пароля уже в самом клиенте набираем
alter user postgres with password 'ПАРОЛЬ';

passwd postgres - задаем пароль

su postgres - подключаемся пользователем postgres

psql - входим клиент postgres для управления db

Для смены пароля уже в самом клиенте набираем

alter user postgres with password 'ПАРОЛЬ';

Просмотр активных/текущих процессов:

SELECT * from pg_stat_activity;

1	SELECT * from pg_stat_activity;

Обнуление/усечение таблицы:

truncate tablename;

1	truncate tablename;

Просмотр размера базы данных:

SELECT pg_size_pretty( pg_database_size( 'databasename' ) );

1	SELECT pg_size_pretty( pg_database_size( 'databasename' ) );

Просмотр размера таблицы:

SELECT pg_size_pretty( pg_total_relation_size( 'tablename' ) );

1	SELECT pg_size_pretty( pg_total_relation_size( 'tablename' ) );

Просмотр размера таблицы без индексов:

SELECT pg_size_pretty( pg_relation_size( 'tablename' ) );

1	SELECT pg_size_pretty( pg_relation_size( 'tablename' ) );

Просмотр запущенной версии PostgreSQL:

SELECT version();

1	SELECT version();

Репликация \ replication

Входим:
su - postgres
psql

Состояние репликации:
master
SELECT * FROM pg_stat_replication;

slave
select * from pg_stat_wal_receiver;



Слоты репликации:
master
-- создание слота для репликации
SELECT pg_create_physical_replication_slot('<SLOT_NAME>');
-- удаление слота для репликации
SELECT pg_drop_replication_slot('<SLOT_NAME>');
-- слоты репликации
SELECT * FROM pg_replication_slots;

Входим:

su - postgres

psql

Состояние репликации:

master

SELECT * FROM pg_stat_replication;

slave

select * from pg_stat_wal_receiver;

Слоты репликации:

master

-- создание слота для репликации

SELECT pg_create_physical_replication_slot('<SLOT_NAME>');

-- удаление слота для репликации

SELECT pg_drop_replication_slot('<SLOT_NAME>');

-- слоты репликации

SELECT * FROM pg_replication_slots;