Бэкап:
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 - восстановление
Краткое введение в MySQL:
СУБД 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 - Большие двоичные объекты (изображения, звуки)
Каждый столбец после своего типа данных содержит и другие спецификаторы:
NOT NULL - Все строки таблицы должны иметь значение в этом атрибуте. Если не указано, поле может быть пустым (NULL)
AUTO_INCREMENT - Специальная возможность MySQL, которую можно задействовать в числовых столбцах.
Если при вставке строк в таблицу оставлять такое поле пустым, MySQL автоматически генерирует уникальное значение идентификатора.
Это значение будет на единицу больше максимального значения, уже существующего в столбце.
В каждой таблице может быть не больше одного такого поля.
Столбцы с AUTO_INCREMENT должны быть проиндексированными
PRIMARY KEY - Столбец является первичным ключом для таблицы.
Данные в этом столбце должны быть уникальными.
MySQL автоматически индексирует этот столбец
UNSIGNED - После целочисленного типа означает, что его значение может быть либо положительным, либо нулевым
COMMENT - Название столбца таблицы
Для изменения ранее записанных в таблицу значений нужно воспользоваться командой UPDATE:
Например, цену всех книг повысили на 10%: UPDATE books SET price = price * 1.1;
Конструкция WHERE ограничит работу UPDATE определенным строками:
Например: UPDATE books SET price = price * 1.05 WHERE price <= 250;
Для удаления строк из базы данных используется оператор DELETE:
Ненужные строки указываются при помощи конструкции WHERE. Например, какие-то книги проданы: DELETE FROM books WHERE quantity = 0;
Если нужно удалить все записи:
TRUNCATE TABLE table_name
Очистка базы данных
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 - бэкап
Восстановление:
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
Подключение к бд:
mysql -uUSERNAME -pPASSWORD DBNAME - подключение к базе DBNAME используя логин:USERNAME и пароль:PASSWORD mysql -uroot -p - подключаемся для управления mysql
Выбор базы данных:
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;
Примеры создания базы данных:
Создание новой базы данных 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-команды INSERT:
Например:
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; - Скопировать данные из одной таблицы в другую
Права доступа и привилегии пользователя:
!!! после изменения \ добавления пользователя не забываем выполнить 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 '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:
Для того чтобы посмотреть, какие права доступа выданы пользователю MySQL, достаточно в консоли прописать команду: SHOW GRANTS FOR 'user'@'localhost';
Посмотреть пользователей mysql можно вот так;
use mysql; select user,password,host from user; select * from user order by host desc;
Как запретить пользователю все операции с базой данных?
Revoke ALL PRIVILEGES on *.* from usernameS@hostname;
Как изменить забытый MySQL административный пароль?
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 - очистка базы данных без удаления базы данных, будут удалены все таблицы.
Удалить базу:
DROP DATABASE dbname; - Удалить базу данных
Как выполнить поиск по регулярному выражению?
Вот например так 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
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; - показать состояние мастера и репликации
