Рубрика: MySQL

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

23.01.2018 от admin·Нет комментариев

Создать в БД пользователя и выдать ему права на чтение:

GRANT SELECT ON `user_read`.* TO `user_read`@`127.0.0.1` IDENTIFIED BY '*********';

1	GRANT SELECT ON `user_read`.* TO `user_read`@`127.0.0.1` IDENTIFIED BY '*********';

Важно выдать права именно на хост 127.0.0.1, а не localhost.

Создать пользователя системы:

# adduser user_read

1	# adduser user_read

Установить его пароль:

# passwd user_read

1	# passwd user_read

Теперь можно подключиться к БД по SSH.

Перенос сайта на WordPress на другой домен

02.08.2017 от admin·Нет комментариев

На старом хостинге

Перейти во временный каталог и создать бекап каталога с сайтом:

$ cd /home/user/tmp
$ tar -C /home/user/example.com/public_html -cvzf site.tar.gz .

1 2	$ cd /home/user/tmp $ tar -C /home/user/example.com/public_html -cvzf site.tar.gz .

Там же создать бекап базы данных и упаковать его:

$ mysqldump -u database_user -p database_name &gt; db.sql
$ gzip db.sql

1 2	$ mysqldump -u database_user -p database_name > db.sql $ gzip db.sql

На новом хостинге

Переходим во временный каталог, подключаемся по SFTP загружаем бекапы каталога и базы данных:

$ cd /home/user/tmp
$ sftp user@old.hosting
> get tmp/site.tar.gz
> get tmp/db.sql.gz

$ cd /home/user/tmp

$ sftp user@old.hosting

> get tmp/site.tar.gz

> get tmp/db.sql.gz

Распаковываем бекап базы и импортируем в новую базу данных

$ gunzip db.sql.gz
$ mysql -u database_user -p database_name &lt; db.sql

1 2	$ gunzip db.sql.gz $ mysql -u database_user -p database_name < db.sql

Переходим в каталог нового сайта (DOCUMENT_ROOT) и распаковываем бекап каталога:

$ cd /home/user/example.com/public_html
$ tar -xvzf /home/user/tmp/site.tar.gz

1 2	$ cd /home/user/example.com/public_html $ tar -xvzf /home/user/tmp/site.tar.gz

Изменить данные для подключения к БД в файле wp-config.php

define('DB_NAME', '******');
define('DB_USER', '******');
define('DB_PASSWORD', '******');
define('DB_HOST', '******');

define('DB_NAME', '******');

define('DB_USER', '******');

define('DB_PASSWORD', '******');

define('DB_HOST', '******');

Скорректировать адрес сайта в БД:

UPDATE wp_options
  SET option_value = REPLACE(option_value, 'http://olddomain.ru', 'http://newdomain.ru')
  WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts
  SET guid = REPLACE(guid, 'http://olddomain.ru','http://newdomain.ru');

UPDATE wp_posts
  SET post_content = REPLACE(post_content, 'http://olddomain.ru', 'http://newdomain.ru');

UPDATE wp_options

SET option_value = REPLACE(option_value, 'http://olddomain.ru', 'http://newdomain.ru')

WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts

SET guid = REPLACE(guid, 'http://olddomain.ru','http://newdomain.ru');

UPDATE wp_posts

SET post_content = REPLACE(post_content, 'http://olddomain.ru', 'http://newdomain.ru');

Очистить папку кеша wp-content/cache.

А вообще можно файлы можно перенести одной строкой:

tar -c * | ssh user@server cd && tar -x

1	tar -c * \| ssh user@server cd && tar -x

Ответы на тесты по MySQL

30.07.2017 от admin·Нет комментариев

Что такое первичный ключ?

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

Что такое mysql_pconnect? Чем он отличается от mysql_connect?

mysql_pconnect устанавливает постоянное соединение с базой данных, которое не закрывается после завершения работы скрипта.

Что такое MyISAM и InnoDB?

Это системы хранения данных в MySQL.

Чем они отличаются?

MyISAM – нет транзакций и внешних ключей, каждая таблица в своем файле, при записи блокируется вся таблица. Если только вставка или чтение, то быстрее InnoDB.

InnoDB — есть транзакции, внешние ключи, больше соответствует реляционной модели. Блокировка на уровне строк. Быстрее при смешанных операциях SELECT, INSERT, UPDATE.

Есть две таблицы: users — таблица с пользователями (users_id, name) orders — таблица с заказами (orders_id, users_id, status)

Выбрать всех пользователей из таблицы users, у которых ВСЕ записи в таблице orders имеют status = 0;
Выбрать всех пользователей из таблицы users, у которых больше 5 записей в таблице orders имеют status = 1;

SELECT u.*
FROM users u
WHERE users_id NOT IN (
  SELECT users_id
  FROM orders o
  WHERE o.status &lt;&gt; 0
)

SELECT u.*

FROM users u

WHERE users_id NOT IN (

SELECT users_id

FROM orders o

WHERE o.status <> 0

)

SELECT u.*
FROM users u
INNER JOIN (
  SELECT users_id, COUNT(*) cnt
  FROM orders o
  WHERE o.status = 1
  GROUP BY users_id
) q ON q.users_id = u.users_id
WHERE
  q.cnt &gt; 5

SELECT u.*

FROM users u

INNER JOIN (

SELECT users_id, COUNT(*) cnt

FROM orders o

WHERE o.status = 1

GROUP BY users_id

) q ON q.users_id = u.users_id

WHERE

q.cnt > 5

Какая разница между LEFT, RIGHT и INNER JOIN

INNER — выбираются только те строки, которые есть одновременно в объединяемых таблицах
LEFT — все из левой, отсутствующие в правой заполняются NULL
RIGHT — все из правой, отсутствующие в левой заполняются NULL

Что делает команда EXPLAIN?

Предоставляет информацию о порядке и методе связывания таблиц.

Как вывести все поля из таблицы my_table

SELECT * FROM my_table

1	SELECT * FROM my_table

Вывести всех производителей за исключением тех, которые находятся в Китае, Таджикистане и России; табл.: manufacture (id, country, count, date_add, tag)

SELECT * 
  FROM manufacture
  WHERE country NOT IN ('Китай', 'Таджикистан', 'Россия')

SELECT *

FROM manufacture

WHERE country NOT IN ('Китай', 'Таджикистан', 'Россия')

Мы не помним как точно пишется “Mercedes“ или “Mersedes“, но нужно из таблицы выбрать автобусы именно этой марки. Как быть? табл.: bus (id, name)

SELECT * 
  FROM bus
  WHERE name LIKE 'Mer_edes'

SELECT *

FROM bus

WHERE name LIKE 'Mer_edes'

Порядок транзакции

SET AUTOCOMMIT=0;
START TRANSACTION;
SELECT * FROM t FOR UPDATE;
UPDATE t SET c=1;
ROLLBACK;
COMMIT;

SET AUTOCOMMIT=0;

START TRANSACTION;

SELECT * FROM t FOR UPDATE;

UPDATE t SET c=1;

ROLLBACK;

COMMIT;

Напишите пример процедуры возвращающей конкатенацию входных параметров (2х входных параметров) типа varchar, с проверкой на наличие значений (not null), и сообщением в качестве возвращаемого значения, в случае если один из параметров не прошел проверку.

CREATE FUNCTION func1(`par1` VARCHAR, IN `par2` VARCHAR) RETURNS VARCHAR
BEGIN
  IF `par1` IS NULL OR `par2` IS NULL THEN
    RETURN 'Ошибка';
  ELSE
    RETURN `par1` || `par2`;
  END IF;
END;

CREATE FUNCTION func1(`par1` VARCHAR, IN `par2` VARCHAR) RETURNS VARCHAR

BEGIN

IF `par1` IS NULL OR `par2` IS NULL THEN

RETURN 'Ошибка';

ELSE

RETURN `par1` || `par2`;

END IF;

END;

Что такое триггер? Возможные применения?

Хранимая процедура, которая вызывается при наступлении определенного события (INSERT, UPDATE, DELETE), до или после (BEFORE или AFTER). Применяются чтобы выполнить дополнительные операции. Например, записать в журнал дату изменения записи.

PDO. Что это?

PHP Data Objects — расширение для универсального доступа к различным БД. Для каждой БД используется родной драйвер, а программный интерфейс одинаков. PDO предоставляет объектно-ориентированный подход для работы с БД.

Создание резервной копии 1С-Битрикс

07.01.2017 от admin·Нет комментариев

Ядро и публичную часть:

$ tar -cvzf site.tar.gz -C ~/www_doc_root/ --exclude="./upload" --exclude=".git" --exclude="./bitrix/managed_cache" --exclude="./bitrix/cache" --exclude="./bitrix/stack_cache" --exclude="./bitrix/backup" .

1	$ tar -cvzf site.tar.gz -C ~/www_doc_root/ --exclude="./upload" --exclude=".git" --exclude="./bitrix/managed_cache" --exclude="./bitrix/cache" --exclude="./bitrix/stack_cache" --exclude="./bitrix/backup" .

Отдельно папку upload:

$ tar -cvzf site_upload.tar.gz -C ~/www_doc_root/ --ignore-failed-read ./upload

1	$ tar -cvzf site_upload.tar.gz -C ~/www_doc_root/ --ignore-failed-read ./upload

Можно в upload пропустить служебный каталог resize_cache и временный tmp:

$ tar -cvzf site_upload.tar.gz -C ~/www_doc_root/ --ignore-failed-read --exclude="upload/resize_cache" --exclude="upload/tmp" ./upload

1	$ tar -cvzf site_upload.tar.gz -C ~/www_doc_root/ --ignore-failed-read --exclude="upload/resize_cache" --exclude="upload/tmp" ./upload

Перед созданием базы данных получить логин и пароль пользователя БД:

$ grep 'DBLogin\|DBPassword' www/bitrix/php_interface/dbconn.php

1	$ grep 'DBLogin\\|DBPassword' www/bitrix/php_interface/dbconn.php

Базу данных:

$ mysqldump -u dbuser -h host -p dbname > sitedb.sql
tar -cvzf sitedb.tar.gz sitedb.sql

1 2	$ mysqldump -u dbuser -h host -p dbname > sitedb.sql tar -cvzf sitedb.tar.gz sitedb.sql

Или одной командой:

$ mysqldump -u dbuser -h host -p dbname | gzip -v > sitedb.sql.gz

1	$ mysqldump -u dbuser -h host -p dbname \| gzip -v > sitedb.sql.gz

Список результатов голосования

03.06.2016 от admin·Нет комментариев

SELECT 
    e.vote_id,
    eq.QUESTION_ID,
    ea.answer_id,
    ea.message,
    u.auth_user_id
FROM
    b_vote_event_question eq
        INNER JOIN
    b_vote_event e ON e.id = eq.event_id
        INNER JOIN
    b_vote_event_answer ea ON ea.event_question_id = eq.id
        INNER JOIN
    b_vote_user u ON u.id = e.vote_user_id
WHERE
    vote_id = 102

SELECT

e.vote_id,

eq.QUESTION_ID,

ea.answer_id,

ea.message,

u.auth_user_id

FROM

b_vote_event_question eq

INNER JOIN

b_vote_event e ON e.id = eq.event_id

INNER JOIN

b_vote_event_answer ea ON ea.event_question_id = eq.id

INNER JOIN

b_vote_user u ON u.id = e.vote_user_id

WHERE

vote_id = 102

Добавить право на бизнес-процесс Битрикса

28.01.2016 от admin·Нет комментариев

SET NAMES 'utf8';
SET CHARACTER SET utf8;

set @bp=158907;
set @user=6324;
set @need_permission = 'read';

set @permission = '';

select s.id
into @workflow
from b_bp_workflow_state s
where
s.document_id= @bp;

select permission
into @permission
from b_bp_workflow_permissions p
where p.workflow_id=@workflow
and p.object_id = concat('user_', cast(@user AS CHAR));

insert into b_bp_workflow_permissions (workflow_id, object_id, permission)
select @workflow, concat('user_', cast(@user AS CHAR(4))), @need_permission
from dual
where @permission != @need_permission;

select *
from b_bp_workflow_permissions p
where p.workflow_id=@workflow
and p.object_id = concat('user_', cast(@user AS CHAR))
order by id;

SET NAMES 'utf8';

SET CHARACTER SET utf8;

set @bp=158907;

set @user=6324;

set @need_permission = 'read';

set @permission = '';

select s.id

into @workflow

from b_bp_workflow_state s

where

s.document_id= @bp;

select permission

into @permission

from b_bp_workflow_permissions p

where p.workflow_id=@workflow

and p.object_id = concat('user_', cast(@user AS CHAR));

insert into b_bp_workflow_permissions (workflow_id, object_id, permission)

select @workflow, concat('user_', cast(@user AS CHAR(4))), @need_permission

from dual

where @permission != @need_permission;

select *

from b_bp_workflow_permissions p

where p.workflow_id=@workflow

and p.object_id = concat('user_', cast(@user AS CHAR))

order by id;

Установка phpMyAdmin на 1С-Битрикс»: Веб-окружение

14.05.2015 от admin·Нет комментариев

Установить:

yum install phpMyAdmin

1	yum install phpMyAdmin

После установки появится файл /etc/httpd/conf.d/phpMyAdmin.conf. Чтобы он применился в конфиге httpd (/etc/httpd/conf/httpd.conf), возможно, потребуется раскомментировать:

Include conf.d/*.conf

1	Include conf.d/*.conf

А некторые конфиги из /etc/httpd/conf.d/ отключить, переименовав из geoip.conf в geoip.conf.disabled

Чтобы статика (css, js) из phpMyAdmin jnlfdfkfcm отдавалась корректно, в конфиг nginx добавить

location ~ ^/phpMyAdmin {
  proxy_pass $proxyserver;
}

location ~ ^/phpMyAdmin {

proxy_pass $proxyserver;

}

Удалить все таблицы из схемы базы данных

05.02.2015 от admin·Нет комментариев

SELECT concat('DROP TABLE IF EXISTS ', table_schema, '.', table_name, ';')
  FROM information_schema.tables
  WHERE table_schema = 'dbtest';

SELECT concat('DROP TABLE IF EXISTS ', table_schema, '.', table_name, ';')

FROM information_schema.tables

WHERE table_schema = 'dbtest';

Вручную создать базу данных MySQL для портала Битрикс

14.10.2014 от admin·Нет комментариев

Создать БД:

CREATE DATABASE `testportal_new`
  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE DATABASE `testportal_new`

DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Создать пользователя и выдать ему права на новую БД можно одной командой:

GRANT ALL ON `testportal_new`.* TO `testportal_new`@`localhost` IDENTIFIED BY '*********';

1	GRANT ALL ON `testportal_new`.* TO `testportal_new`@`localhost` IDENTIFIED BY '*********';

Можно и по отдельности. Сначала создать пользователя:

CREATE USER `testportal_new`@`localhost` IDENTIFIED BY '*********';

1 2	CREATE USER `testportal_new`@`localhost` IDENTIFIED BY '*********';

А затем выдать права пользователю:

GRANT ALL ON `testportal_new`.* TO `testportal_new`@`localhost`;

1 2	GRANT ALL ON `testportal_new`.* TO `testportal_new`@`localhost`;

Сменить пароль можно с помощь оператора SET PASSWORD (***):

SET  PASSWORD  =  PASSWORD('some password')
SET  PASSWORD  FOR  user  =  PASSWORD('some password')

SET PASSWORD = PASSWORD('some password')

SET PASSWORD FOR user = PASSWORD('some password')

Ссылки

Установка MySQL
Сбросить пароль root

Посмотреть структуру таблицы или базы данных

14.10.2014 от admin·Нет комментариев

Структура таблицы b_option:

mysql&gt; describe b_option;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| MODULE_ID   | varchar(50)  | YES  | MUL | NULL    |       |
| NAME        | varchar(50)  | NO   | MUL | NULL    |       |
| VALUE       | text         | YES  |     | NULL    |       |
| DESCRIPTION | varchar(255) | YES  |     | NULL    |       |
| SITE_ID     | char(2)      | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> describe b_option;

+-------------+--------------+------+-----+---------+-------+

+-------------+--------------+------+-----+---------+-------+

+-------------+--------------+------+-----+---------+-------+

5 rows in set (0.01 sec)

Получить SQL-инструкцию для создания таблицы:

mysql&gt; show create table b_option;
CREATE TABLE `b_option` (
  `MODULE_ID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `NAME` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `VALUE` text COLLATE utf8_unicode_ci,
  `DESCRIPTION` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SITE_ID` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
  UNIQUE KEY `ix_option` (`MODULE_ID`,`NAME`,`SITE_ID`),
  KEY `ix_option_name` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

mysql> show create table b_option;

CREATE TABLE `b_option` (

`MODULE_ID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

`NAME` varchar(50) COLLATE utf8_unicode_ci NOT NULL,

`VALUE` text COLLATE utf8_unicode_ci,

`DESCRIPTION` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

`SITE_ID` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,

UNIQUE KEY `ix_option` (`MODULE_ID`,`NAME`,`SITE_ID`),

KEY `ix_option_name` (`NAME`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Получить SQL-инструкцию для создания БД:

mysql&gt; SHOW CREATE DATABASE testdb;
+------------+---------------------------------------------------------------------------------------------+
| Database   | Create Database                                                                             |
+------------+---------------------------------------------------------------------------------------------+
| testdb     | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ |
+------------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE DATABASE testdb;

+------------+---------------------------------------------------------------------------------------------+

| Database | Create Database |

+------------+---------------------------------------------------------------------------------------------+

| testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ |

+------------+---------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

ВВСки

Записки разработчика

Рубрика: MySQL

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

Перенос сайта на WordPress на другой домен

На старом хостинге

На новом хостинге

Ответы на тесты по MySQL

Что такое первичный ключ?

Что такое mysql_pconnect? Чем он отличается от mysql_connect?

Что такое MyISAM и InnoDB?

Чем они отличаются?

Есть две таблицы: users — таблица с пользователями (users_id, name) orders — таблица с заказами (orders_id, users_id, status)

Какая разница между LEFT, RIGHT и INNER JOIN

Что делает команда EXPLAIN?

Как вывести все поля из таблицы my_table

Вывести всех производителей за исключением тех, которые находятся в Китае, Таджикистане и России; табл.: manufacture (id, country, count, date_add, tag)

Мы не помним как точно пишется “Mercedes“ или “Mersedes“, но нужно из таблицы выбрать автобусы именно этой марки. Как быть? табл.: bus (id, name)

Порядок транзакции

Что такое триггер? Возможные применения?

PDO. Что это?

Создание резервной копии 1С-Битрикс

Список результатов голосования

Добавить право на бизнес-процесс Битрикса

Установка phpMyAdmin на 1С-Битрикс»: Веб-окружение

Удалить все таблицы из схемы базы данных

Вручную создать базу данных MySQL для портала Битрикс

Ссылки

Посмотреть структуру таблицы или базы данных