Настройки
Список настроечных параметров и их значения
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;
Оптимизация баз данных
При помощи команды 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'
Эти настройки были найдены на просторах инета, но автора, к сожалению, не помню. Делают эти настройки следующие
Дамп (резервная копия)
Дамп базы данных
$ 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
Как конвертировать MyISAM в InnoDB
Просмотр всех таблиц и их типов
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database' and ENGINE = 'myISAM'
Следущая комманда конвертирует таблицу в InnoDB
ALTER TABLE table1 ENGINE=InnoDB;
Дополнительное чтиво