10 заметок с тегом

MySQL

Конфигурация MySQL

16 июня 2017, 14:40

Файл конфигурации MySQL my.cnf.
Стандартный путь: /etc/mysql
Установить язык default: UTF-8

Открываем файл на редактирование и пишем:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
MySQL   UTF-8

Детально о JOIN в SQL

23 августа 2016, 15:56

Рассмотрим 7 типов join:

  1. INNER JOIN
  2. LEFT JOIN
  3. RIGHT JOIN
  4. FULL OUTER JOIN (в MySQL UNION)
  5. LEFT JOIN EXCLUDING INNER JOIN
  6. RIGHT JOIN EXCLUDING INNER JOIN
  7. OUTER JOIN EXCLUDING INNER JOIN


    Наглядно посмотрим в чем разница.

А теперь подробно о каждом из них.


Inner JOIN

SELECT <select_list> 
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key



Left JOIN

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key



Right JOIN

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key



Outer JOIN

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key



Left Excluding JOIN

SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL



Right Excluding JOIN

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL



Outer Excluding JOIN

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL



А теперь все это на примере 2 простых таблиц из БД:

TABLE_A
  PK Value
---- ----------
   1 FOX
   2 COP
   3 TAXI
   6 WASHINGTON
   7 DELL
   5 ARIZONA
   4 LINCOLN
  10 LUCENT

TABLE_B
  PK Value
---- ----------
   1 TROT
   2 CAR
   3 CAB
   6 MONUMENT
   7 PC
   8 MICROSOFT
   9 APPLE
  11 SCOTCH

-- INNER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
       B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7

(5 row(s) affected)

-- LEFT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
  10 LUCENT     NULL       NULL

(8 row(s) affected)

-- RIGHT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(8 row(s) affected)

-- OUTER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(11 row(s) affected)

-- LEFT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK
WHERE B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
  10 LUCENT     NULL       NULL
(3 row(s) affected)

-- RIGHT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(3 row(s) affected)

-- OUTER EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(6 row(s) affected)

Конвертация из MyISAM в InnoDB и обратно для таблиц MySQL

26 апреля 2016, 11:46

Конвертация будет проводиться из sql дампа.


Сначала в конфиге mysql проверяем включена ли поддержка InnoDB и другие параметры.


#skip-innodb

innodb_data_file_path = ibdata1:100M:autoextend //минимальный файл 100мб и разрешено авто-увеличение файла


При желании вместо innodb_data_file_path можно использовать innodb_file_per_table, тогда под каждую таблицу будет использоваться свой отдельный файл.


После изменения нужно перезапустить mysql


Итак, процесс конвертации:

mysqldump --opt -u USER -p DBNAME > dbname.sql //создание дампа
sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' dbname.sql > dbname.innodb.sql //MyISAM → InnoDB
sed s/ENGINE=InnoDB/ENGINE=MyISAM/g dbname.sql > dbname.myisam.sql //InnoDB → MyISAM

mysqladmin -u USER -p drop DBNAME //удаляем
mysqladmin -u USER -p create DBNAME //создаем снова
mysql -u USER -p DBNAME < dbname.{innodb|myisam}.sql //заливаем дамп


Готово.

Основы операций с MySQL

25 апреля 2016, 17:28

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

mysql -u root -p
mysql -u root -p dbname
mysql -u root -h localhost -pPASSWORD // подключение с паролем PASSWORD
mysql -u root -h artana.ru -p // удалённый хост artana.ru


Создание дамп баз данных

mysqldump --opt -u root -p dbname > dbname.sql
mysqldump --opt -u root -p dbname mytable > dbname.mytable.sql //отдельной БД mytable

Для всех баз данных — ключ:

--all-databases

По умолчанию хранимые функции и процедуры не добавляться в backup.
нужно добавить ключь

-R, --routines


Управление таблицами в БД MySQL

SHOW DATABASES; //Просмотр
SHOW TABLES; //Просмотр текущей таблицы
USE dbname; //Переключиться
CREATE DATABASE dbname; //Создание
DROP DATABASE dbname; //Удаление
ALTER DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; //Кодировка


Управление привилегиями

GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' IDENTIFIED BY 'password1' WITH GRANT OPTION; //Создание user1 со всеми правами как у root

GRANT ALL ON dbname.* TO 'user'@'localhost' IDENTIFIED BY 'password'; //Создание user с полными правами на БД dbname

GRANT INSERT ON dbname.* TO 'user'@'localhost'; //Права Вставь на бд dbname

REVOKE ALL ON userdb.* FROM 'user'@'localhost'; //Удаление прав на бд

USE mysql;
DELETE FROM USER WHERE USER='user'; //Полное удаление всех прав 

SHOW GRANTS; //Посмотреть список прав пользователя
SHOW GRANTS FOR 'user'@'localhost'; //Посмотреть права для user
SELECT USER, host FROM mysql.user; //Список всех пользователей
FLUSH PRIVILEGES; //Перезагрузка привилегий


Сжатие дампа с помощью bzip2:

bzip2 dbname.sql
mysqldump --opt -u root -p dbname | bzip2 > dbname.sql.bz2 //сжатие на лету


Восстановление БД из дамп файла:

mysql -u root -p dbname < dbname.sql
bzcat dbname.sql.bz2 | mysql -u root -p dbname //если дамп сжат


Перенос БД между серверами MySQL

mysqldump --opt -u root -p dbname | mysql --host=remote-host -u user -p -C dbname //-С указывает режим компрессии данных


Еще использование утилиты mysqladmin

mysqladmin -u root -p create dbname //создание
mysqladmin -u root -p drop dbname //удаление
mysqladmin -u root password PaS$wOrD //задать новый пароль
MySQL

Linux команды для сервера

9 ноября 2015, 11:49

Команды управления для Nginx, MySQL на Debian

systemctl status php5-fpm.service
systemctl status nginx.service
systemctl status mysql.service

/etc/init.d/php5-fpm start
/etc/init.d/php5-fpm stop

/etc/init.d/mysql start
/etc/init.d/mysql stop

/etc/init.d/nginx start
/etc/init.d/nginx stop

nginx -s reload

Команды управления для Apache на CentOS

/etc/init.d/httpd start - запуск
/etc/init.d/httpd stop - стоп
/etc/init.d/httpd status - состояние
/etc/init.d/httpd restart - перезапуск

Команды управления для MySQL

/etc/init.d/mysqld start - запуск
/etc/init.d/mysqld stop - стоп
/etc/init.d/mysqld status - состояние
/etc/init.d/mysqld restart - перезапуск
Apache   CentOS   Debian   Linux   MySQL   nginx
Ctrl + ↓ Ранее