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

Настройки

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

mysqld --verbose --help

Управление пользователями

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

mysql> SELECT UserHost FROM mysql.user;

Список прав у пользователя [email protected]

mysql> SHOW GRANTS FOR [email protected];

Создание нового пользователя

mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'secret';

Добавим выбранные привилегии для всех таблиц БД dbname пользователю 'user'@'localhost'

mysql> GRANT SELECTINSERTUPDATEDELETECREATEDROPALTERINDEX 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 [email protected];

Перезагрузка привилегий

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;

Оптимизация баз данных

При помощи команды 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/binwget http://mysqltuner.pl/mysqltuner.plchmod +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 --verbosesudo 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 &$ mysqlmysql> 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/dirsudo chown -R mysql:mysql /path/new/dirsudo 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

Дополнительное чтиво


Оригинал статьи http://proft.me/2011/07/19/mysql-poleznye-komandy-i-nastrojki/

0 комментариев

Оставить комментарий