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

ссылки

MariaDB Foundation

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

Установка

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" что бы перезапустить привилегии

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 

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

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 - очистка базы данных без удаления базы данных.

Ссылки

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 

Сброс пароля 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. /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 изменен.

Сброс пароля для 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

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

cat /etc/mysql/debian.cnf

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

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

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

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 - включить отображение времени выполнения запроса

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

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;

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

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;

SELECT * FROM "User" LIMIT 10;
SELECT * FROM "Permission" LIMIT 10;

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

# 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 # разрешения подключения для всех
-----------

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 с списком имен свойств.

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

В 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

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

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

Восстановление резервной копии БД, сжатой 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;

Перенос директории с данным (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

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

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

TRUNCATE TABLE tablename RESTART IDENTITY CASCADE;

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

ALTER TABLE movies ALTER COLUMN year DROP NOT NULL;

Утилита pgcli

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

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

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

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

CREATE USER myuser WITH PASSWORD '123';

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

CREATE DATABASE mytestdb OWNER = myuser;

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

GRANT ALL PRIVILEGES ON database mytestdb TO myuser;

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

DROP DATABASE mytestdb;

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

DROP USER myuser;

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

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

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

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

SELECT * from pg_stat_activity;

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

truncate tablename;

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

SELECT pg_size_pretty( pg_database_size( 'databasename' ) );

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

SELECT pg_size_pretty( pg_total_relation_size( 'tablename' ) );

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

SELECT pg_size_pretty( pg_relation_size( 'tablename' ) );

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

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('');
-- удаление слота для репликации
SELECT pg_drop_replication_slot('');
-- слоты репликации
SELECT * FROM pg_replication_slots;