Среда, 20.09.2017, 01:36
# FreeBSD ГлавнаяРегистрацияВход
Приветствую Вас Гость | RSS
Меню сайта
Категории раздела
Мои статьи [167]
Статистика

Онлайн всего: 1
Гостей: 1
Пользователей: 0
Форма входа
 Каталог статей 
Главная » Статьи » Мои статьи

mysql: полезные команды и настройки

mysql: полезные команды и настройки
Настройки

Список настроечных параметров и их значения

mysqld --verbose --help
Управление пользователями

Список пользователей

mysql> SELECT User,Host FROM mysql.user;
Список прав у пользователя root@localhost

mysql> SHOW GRANTS FOR root@localhost;
Создание нового пользователя

mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'secret';
Добавим выбранные привилегии для всех таблиц БД dbname пользователю 'user'@'localhost'

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX ON dbname.* TO 'user'@'localhost';
Добавим все привилегии для всех таблиц БД dbname пользователю 'user'@'localhost'

mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost';
Удаление прав пользователя 'user'@'localhost' для БД dbname:

mysql> REVOKE ALL ON dbname.* FROM 'user'@'localhost';
Удаление пользователя

mysql> DROP USER user@localhost;
Перезагрузка привилегий

mysql> FLUSH PRIVILEGES;
Новый пароль для root

$ mysqladmin -uroot password 'secret'
Управление базой данных

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

mysql> CREATE DATABASE dbname COLLATE utf8_general_ci;
Создание базы данных из консоли

$ mysqladmin -u root -p create dbname
Удаления базы данных из консоли

mysqladmin -u root -p drop dbname
Maatkit - расширенные возможности MySQL

Adminer - удобная замена phpMyAdmin, всего один php-файл.

Другие sql-команды

Замена в поле определенной подстроки на другую

mysql> UPDATE table SET field=replace(field, 'original string', 'new string');
Вставка данных из одной таблицы в другую

mysql> INSERT INTO table1 (fld1, fld2) SELECT table2.fld1, table2.fld2 FROM table2 WHERE table2.fld2 > 7;
Просмотр структуры таблицы blog_posts

mysql> DESCRIBE blog_posts;
Просмотр sql-запроса на создание структуры таблицы blog_posts

mysql> SHOW CREATE TABLE  blog_posts;
Мониторинг и статистика

Список всех баз данных

mysql> SHOW DATABASES;
Список всех таблиц в выбранной базе данных

mysql> SHOW TABLES;
Статистика по работе сервера

mysql> SHOW GLOBAL STATUS;
Mytop - простое средство мониторинга Mysql
Remote MySQL Performance And Query Monitoring
Install Innotop to Monitor MySQL Server Performance
Install Mtop (MySQL Database Server Monitoring)
Оптимизация баз данных

При помощи команды mysqlcheck можно выполнять проверку, оптимизацию и исправление ошибок.

Поверка на ошибки БД dbname

$ mysqlcheck -p dbname
Восстановление и оптимизация всех БД

$ mysqlcheck -Aor -p
Описание аргументов

-p – использовать пароль

-A – проверять все базы данных

-r – ремонтировать БД

-o – оптимизировать БД

Скрипт простой оптимизации БД, можно добавить в крон для выполнение раз в сутки

mysqlcheck --repair --analyze --optimize --all-databases --auto-repair -u root -pSECRET
Установим mysqltuner, который показывает статистику по работающему mysql-серверу и дает полезные советы по оптимизации.

cd /usr/local/bin
wget http://mysqltuner.pl/mysqltuner.pl
chmod +x mysqltuner.pl
Запустим

/usr/local/bin/mysqltuner.pl
При запуске скрипт спросит логин и пароль для root'a. После корректной авторизации будет выведена статистика и разные советы по правке конфигурационного файла MySQL.

Изменение строки приглашения (prompt)

В ~/.my.cnf добавим

[client]
default-character-set = 'utf8'
pager = 'less -n -i -S'
prompt = '\u@\h [\d] > '
user = 'root'
password = 'secret'
Эти настройки были найдены на просторах инета, но автора, к сожалению, не помню. Делают эти настройки следующие

устанавливаем кодировку по-умолчанию в utf8
используем less для вывода результатов запроса
меняем строку приглашения, \u - пользователь, \h - хост, \d - база данных
указываем логин и пароль для mysql-консоли, удобно при разработке на локальном сервере
Дамп (резервная копия)

Дамп базы данных

$ mysqldump -uroot -p dbname > dump.sql
Дамп выбранных баз

$ mysqldump -uroot -p -B dbname1 dbname2 > dump.sql
Дамп всех баз

$ mysqldump -uroot -p -A > dump.sql
Дамп только структуры, без данных

$ mysqldump -uroot -p --no-data dbname > database.sql
Другие опции

--add-drop-table - добавляет команду DROP TABLE перед каждой командой CREATE TABLE

--add-locks - добавляет команду LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы

--no-create-db, -n - не добавлять команду CREATE DATABASE, которая добавляется при использовании параметров --databases и --all-databases

--no-data, -d - дампить только структуру таблиц

--no-create-info, -t - не создавать команду CREATE TABLE

--skip-comments - не выводить комментарии.

--compact - использовать компактный формат

--create-options - добавляет дополнительную информацию о таблице в команду CREATE TABLE: тип, значение AUTO_INCREMENT и т.д. Не нужные опции можно вырезать с помощью sed.

--extended-insert, -e - применение команды INSERT с многострочным синтаксисом (повышает компактность и быстродействие операторов ввода)

--tables - дампить только таблицы из списка, следующего за этим параметром, разделитель - пробел

Применение дампа

$ mysql -uroot -p dbname1 < dump.sql
Начиная с версии 5.5.30 при дампе возможно получить следующие сообщение

Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
или
mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)
Для исправления этого надо обновить базы и перезапустить MySQL

sudo mysql_upgrade -u root -h localhost -p --verbose
sudo service mysql restart
Изменение кодировка для текстового поля

Список полей для таблицы table с информацией о поле, в том числе и кодировка поля

SHOW FULL COLUMNS FROM table;
Меняем charset для поля field

ALTER TABLE table MODIFY field VARCHAR(255) CHARACTER SET utf8;
Изменение кодировка при импорте с дампа

Определение кодировки файла

file --mime-encoding dump.sql
Конвертирование из кодировки latin1 в utf8

mysqldump --add-drop-table -uroot -p dbname | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql -uroot -p dbname
Восстановление root-пароля

$ service mysqld stop
$ mysqld_safe --skip-grant-tables &
$ mysql

mysql> UPDATE mysql.user SET Password=PASSWORD('secret') WHERE User='root';
mysql> FLUSH PRIVILEGES;

$ service mysqld restart
MySQL + Python

Иногда при компиляция MySQL-python может выскочить такая ошибка configure: error: mysql_config executable not found, это значит, что не установлен пакет libmysqlclient15-dev. Под Ubuntu устанавливается так

sudo apt-get install libmysqlclient15-dev
Перенос директории с данным (data directory)

Останавливаем MySQL

service mysqld stop
Копируем существующею директорию с данными в новое место

mkdir -p /path/new/dir
sudo chown -R mysql:mysql /path/new/dir
sudo cp -R -p /var/lib/mysql /path/new/dir
Укажем в my.cnf путь к новой директории, секция mysqld

# sudo vim /etc/mysql/my.cnf

[mysqld]
datadir=/path/new/dir/mysql
Запускаем MySQL

service mysqld start
Дополнительное чтиво

Стратегия восстановления поврежденной таблицы в MySQL
Настройка и оптимизация MySQL сервера
Постраничная навигация с MySQL при большом количестве записей
MySQL Performance real life Tips and Tricks
MyIsam Vs Innodb Vs Memory – Mysql Storage Engine Comparison

 

http://proft.me/2011/07/19/mysql-poleznye-komandy-i-nastrojki/

Категория: Мои статьи | Добавил: Admin (02.10.2014)
Просмотров: 851 | Комментарии: 1 | Рейтинг: 0.0/0
Всего комментариев: 0
Имя *:
Email *:
Код *:
Поиск
Друзья сайта
  • Официальный блог
  • Сообщество uCoz
  • FAQ по системе
  • Инструкции для uCoz
  • Copyright MyCorp © 2017Сделать бесплатный сайт с uCoz