Тонкости работы MySQL
1. Часто используемые команды для MySQL
1.1 Управление пользователями
1.2 Управление базой данных
1.3 Мониторинг и статистика
1.4 Оптимизация баз данных
1.5 Дамп (резервная копия)
1.6 Восстановление root-пароля
2. Работа с MySQL из командной строки
3, Пару способов как получить доступ к СУБД MySQL когда забыл пароль от root’a
4. Скрипт резервного копирование (backup) баз MySQL
1. Часто используемые команды для MySQL
1.1 Управление пользователями
Список пользователей:
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';
Удалить пользователя ‘user’
mysql> USE mysql;
mysql> DELETE FROM user WHERE User='user';
Перезагрузка привилегий:
mysql> FLUSH PRIVILEGES;
Новый пароль для root:
$ mysqladmin -uroot password 'secret'
1.2 Управление базой данных
Создание базы данных:
mysql> CREATE DATABASE dbname COLLATE utf8_general_ci;
Создание базы данных из консоли
$ mysqladmin -u root -p create dbname
Удаления базы данных из консоли:
mysqladmin -u root -p drop dbname
1.3 Мониторинг и статистика
Список всех баз данных:
mysql> SHOW DATABASES;
Список всех таблиц в выбранной базе данных:
mysql> SHOW TABLES;
Статистика по работе сервера:
mysql> SHOW GLOBAL STATUS;
так же можно воспользоваться утилитой mytop — мониторинг процессов Mysql в реальном времени
1.4 Оптимизация баз данных
При помощи команды
mysqlcheck
можно выполнять проверку, оптимизацию и исправление ошибок.
Поверка на ошибки БД dbname:
$ mysqlcheck -p dbname
Восстановление и оптимизация всех БД:
$ mysqlcheck -Aor -p
Описание аргументов
-p – использовать пароль
-A – проверять все базы данных
-r – ремонтировать БД
-o – оптимизировать БД
Скрипт простой оптимизации БД, можно добавить в крон для выполнение раз в сутки:
mysqlcheck --repair --analyze --optimize --all-databases --auto-repair -u root -pSECRET
1.5 Дамп (резервная копия)
Дамп базы данных:
$ 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
Изменение кодировка при импорте с дампа
Определение кодировки файла:
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
1.6 Восстановление 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
2. Работа с MySQL из командной строки
Будем считать что все у нас уже установлено и теперь приступаем непосредственно к работе.
Подключаемся:
ceval@srv:~$ mysql -pПАРОЛЬ
-p, —password=[password] — Пароль пользователя, для соединения с сервером MySQL. Не должно быть пробела между -p и паролем.
-u, —user=[user]- Имя пользователя для соединения с сервером MySQL. Необязательно, по умолчанию используется такое же, как ваш логин.Используеться если логин отличается от имени пользователя для соединения с сервером MySQL.
$ mysql -u ceval_ -pПАРОЛЬ
Если мы ввели свой пароль правильно, то увидим такие строки и приглашения:
Welcome to the MySQL monitor. Commands end with ; or \g.
Server version: 5.0.45-Debian
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>
Создаем нужную нам базу, с кодировкой cp1251, указанную кодировку можно заменить на требуемую:
CREATE DATABASE db_name DEFAULT CHARACTER SET cp1251 DEFAULT COLLATE cp1251_general_ci;
Удаления базы:
DROP DATABASE db_name;
Смена базы
USE `mydatabase`;
Удаления таблицы из базы
DROP TABLE `db_table`;
Еще немного:
->\g show databases; // смотрим какие базы есть
->\g create database sputnik; // создаем новую базу
->\g show databases; // проверяем создалась ли она
->\q // Выходим
В разных версиях MySQL могут быть разные команды (4 и 5) в данном случае \g — дает команду серверу выполниьт скрипт SQL
Загрузка, сохранение базы данных MySQL (backup, restore) работа с mysql ubuntu
Загрузить базу c сохраненными данными (если делалось сохранение данных). Они сохранены например в файле baza.sql
mysql
->\g show databases;
->\u baza;
->\. /home/backup/baza.sql
->\q
/home/backup/baza.sql — путь к файлу бекапа
Сохранить базу с данными в файл baza.sql
mysqldump baza > /home/backup/baza.sql;
База хранится: /var/lib/mysql/baza - для удаления всей базы данных достаточно удалить данный каталог.
Можно осуществлять различными способами:
— подключиться по ssh и работать через командную строку;
— использовать phpmyadmin;
— использовать пакет mysql-admin — это графическая утилита администрирования MySQL (GUI tool for intuitive MySQL administration
MySQL)
Теперь устанавливаем себе пакет mysql-admin на машину
sudo aptitude install mysql-admin
Дальше идем на удаленную машину и правим конфиг [b]my.cnf[/b]
$ sudo nano /etc/mysql/my.cnf
Для начало можно проверить:
$ netstat -an | grep 3306
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
Как видно из вывода пока MySQL слушает на localhoste, а нам нужно, чтобы MySQL слушал на всех интерфейсах, а не только на localhost, поэтому находим и редактируем такую запись:
[...]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
[...]
и что б MySQL слушал на всех интерфейсах, закомментируем эту строчку:
[...]
#bind-address = 127.0.0.1:
[...]
Рестанем MySQL:
$ sudo /etc/init.d/mysql restart
И посмотрем снова вывод команды:
$ netstat -an | grep 3306
Вывод должен выглядеть следующим образом:
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
и теперь можно запускать mysql-admin для удаленной работы с MySQL — Alt+F2 — mysql-admin
или
$ mysql-admin
3, Пару способов как получить доступ к СУБД MySQL когда забыл пароль от root’a
3.1 Cпособ сбрасываем и задаем новый пароль для root’a
Останавливаем сервис:
# /etc/init.d/mysql stop
и перезапускаем его вручную с опцией игнорирования привелегий:
# mysqld_safe –skip-grant-tables &
Задаем новый пароль:
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(”НОВЫЙ ПАРОЛЬ”) where User=’root’;
mysql> flush privileges;
mysql> quit
И перезапускаем MySQL в нормальном режиме:
# /etc/init.d/mysql restart
3.2 Способ заходим под системным пользывателем, берем файл debian.cnf есть в /etc/mysql/.
# cat debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = qvBXYgFm9t5vg9hx
socket = /var/run/mysqld/mysqld.sock
Так же можно сменить этот пароль на другой
# mysqladmin -u debian-sys-maint -p -h localhost password 'blahblahblah'
Enter password: qvBXYgFm9t5vg9hx
4. Резервное копирование (backup) баз MySQL
Попался мне на просторах интернета один из скриптов для создания бэкапов баз данных, в чем его плюс — создает отдельный архив под каждую базу данных, в отличии от стандартного mysqldump с ключем —all-databases который пишет все базы в один файл для меня получается не совсем правильный бэкап, т.е мне это ни подходит. По мне лучше каждая база -отдельный файл.
В любом случаи желательно сделать пользователя от которого будем делать бекапы, из прав дадим ему права на просмотр баз,SELECT и Lock Tables, я приведу пример как создать из командной строки:
$ mysql -pПАРОЛЬ
-p, —password=[password] – Пароль пользователя, для соединения с сервером MySQL. Не должно быть пробела между -p и паролем.
-u, —user=[user] — Имя пользователя для соединения с сервером MySQL. Необязательно, по умолчанию используется такое же, как ваш логин.Используется если логин отличается от имени пользователя для соединения с сервером MySQL.
$ mysql> CREATE USER ‘back’@'localhost’ IDENTIFIED BY ’123456′;
Query OK, 0 rows affected (0.02 sec)
$ mysql> GRANT SELECT , LOCK TABLES ON * . * TO ‘back’@'localhost’ IDENTIFIED BY ’123456′;
Query OK, 0 rows affected (0.00 sec)
для примера пользователь back и его пароль 123456 (mysql backup ubuntu)
И для общего развития предложу один из вариантов с использованием mysqldump, может кому пригодиться:
$ mysqldump -uback -p123456 --all-databases | gzip -c > `date "+%Y-%m-%d"`.bz2
все базы данных и сжатие данных на ходу
Теперь сам найденный скрипт:
#!/bin/bash
mkdir ~/mysql_backup/`date +%e-%B`
cd ~/mysql_backup/`date +%e-%B`
for i in `mysql -u %USER% -p%PASSWORD% -e’show databases;’ | grep -v information_schema | grep -v Database`; do mysqldump -u %USER% -p %PASSWORD% $i > `date +%Y-%m-%d`-$i; bzip2 -9 `date +%Y-%m-%d`-$i;done
так же не забываем поменять %USER% на пользователя, которого вы создали для бэкапов, и %PASSWORD% на его пароль.
Так же можно использовать довольно интересный и бесплатный скрипт – “Sypex Dumper”
Теперь для полноты автоматизации можно один из понравившихся вариантов добавить в cron.
$ crontab -e
# m h dom mon dow command
01 00 * * * sh ~/backup
mysql backup ubuntu запуск каждый день в одну минуту первого.