В предыдущей статье шла речь о настройке mysql репликации, теперь поговорим немного об администрировании percona mysq БД. В статье пойдет речь о полезных командам для работы с mysql.
5. Полезные команды
5.1 Резервное копирования и восстановление БД
Дамп одной базы данных.
mysqldump -u root -p companyDB > company_DB.sql
Дамп нескольких баз данных.
mysqldump -u root -p --databases companyDB projectsDB > company_and_projects_DBs.sql
Дамп всех баз данных.
mysqldump -u root -p --all-databases > all_DBs.sql
Дамп одной таблицы.
mysqldump -u root -p companyDB employeesTB > companyDB_employeesTB.sql
Дамп структуры базы данных.
mysqldump -u root -p --no-data companyDB > companyDB_structure.sql
Дамп только триггеров, процедур и событий с gzip-ом.
mysqldump --no-create-info --no-data --triggers --routines --events –u root -p companyDB| gzip > companyDB.sql.gz
Простой скрипт дампа для cron-a.
#!/bin/bash
# Список БД через пробел
dbs="cacti_db inventory_db monitor_db"
# Маленький лог файл для хранение информиции о старте бекапа
echo "`date +%Y-%m-%d` - Start sync backups" >> /tmp/backup.log
for databases in $dbs
do
/usr/bin/mysqldump --max_allowed_packet=512M -uroot -p'your_pass_here' $databases | gzip > /tmp/`date +${databases}.sql.%Y-%m-%d.gz`
if [ "$?" -eq 0 ]
then
/usr/bin/rsync --remove-source-files -a /tmp/`date +${databases}.sql.%Y-%m-%d.gz` mystorage.company.com:/backups/
else
echo "`date +%Y-%m-%d` - FAILED to sync $databases" >> /tmp/backup.log
fi
done
Развернуть дамп существующей базы данных, способ 1.
mysql -u root -p companyDB < companyDB.sql
Развернуть дамп существующей базы данных, способ 2.
mysql> use companyDB; mysql> source companyDB.sql
Развернуть дамп существующей базы данных из архива.
zcat companyDB.sql.gz | mysql -u root -p companyDB
Развернуть дамп несуществующий базы данных, способ 1.
mysql> create database companyDB; mysql> use companyDB; mysql> source companyDB.sql;
Развернуть дамп несуществующий базы данных, способ 2.
mysql -u root -p -e "create database companyDB;" mysql -u root -p companyDB < companyDB.sql
5.2 Переменные, состояние и статус БД
Проверить состояние БД.
mysqladmin -u root -p ping
Узнать версию БД.
mysqladmin -u root -p version или mysql> select version();
Статус БД.
mysqladmin -u root -p status или mysql> status;
Здесь,
- Uptime: сколько секунд работает mysql сервер.
- Threads: суммарное количество подключений к mysql серверу.
- Questions: суммарное количество выполненных запросов за время работы сервера.
- Slow queries: суммарное число запросов, выполненных за время, которое превышает long_query_time.
- Opens: суммарное количество открытых таблиц сервером.
- Flush tables: сколько раз таблица была flushed.
- Open tables: суммарное количество открытых таблиц в базе.
Список переменных статуса mysql.
mysqladmin -u root -p extended-status или mysql -u root -p -e 'show status;'
Список всех системных(глобальных) переменных сервера.
mysqladmin -u root -p variables или mysql> show variables;
Список процессов/запросов в mysql.
mysqladmin -u root -p processlist или mysql> show processlist;
Обновлять каждую секунду список процессов.
mysqladmin -u root -p -i 1 processlist
Список золочёных таблиц.
mysql -u root -p -e 'show open tables WHERE In_use > 0;'
Перезагрузка(запись изменений) привилегий.
mysqladmin -u root -p reload
Очистка (закроет все базы и переоткроет логи).
mysqladmin -u root -p refresh
Потушить сервер.
mysqladmin -u root -p shutdown
Stop/start репликации.
mysqladmin -u root -p stop-slave mysqladmin -u root -p start-slave
5.3 Работа со базами данных и таблицами
Поменять пароль root-а.
mysqladmin -u root -p'old_pass' password 'new_pass'
или
mysql> use mysql;
mysql> update user set password=PASSWORD("newpass") where User='ENTER-USER-NAME-HERE';
Создать БД.
mysqladmin -u root -p create test1db или mysql> create database test1db;
Удалить БД.
mysqladmin -u root -p drop test1db или mysql> drop test1db;
Список flush-команд.
mysqladmin -u root -p flush-hosts mysqladmin -u root -p flush-logs mysqladmin -u root -p flush-privileges mysqladmin -u root -p flush-status mysqladmin -u root -p flush-tables mysqladmin -u root -p flush-threads
Несколько команд в одной.
mysqladmin -u root -p processlist stat
Посмотреть существующие базы.
mysqlshow -u root -p
Посмотреть все таблицы конкретной БД.
mysqlshow -u root -p test1db
… с количеством полей
mysqlshow -v -u root -p test1db
… с количеством полей и строк
mysqlshow -v -v -u root -p test1db
… со структурой таблицы
mysqlshow -u root -p mysql servers
… со структурой таблицы по конкретному полю
mysqlshow -u root -p mysql servers server_name
5.4 Работа с пользователями
Создать нового юзера
mysql> create user 'user1'@'localhost' identified by 'user1pass';
Добавить все права на все базы и все таблицы.
#GRANT [тип прав] ON [название БД].[название таблицы] TO '[имя пользователя]'@'localhost'; mysql> grant ALL PRIVILEGES ON *.* to 'user1'@'localhost'; mysql> flush privileges;
Удаление прав для пользователя.
#REVOKE [тип прав] ON [название БД].[название таблицы] FROM '[имя пользователя]'@'localhost'; mysql> revoke ALL on *.* from 'user1'@'localhost';
Удалить пользователя.
mysql> drop user 'user1'@'localhost';
5.4.1 Сброс пароля root, если забыли.
1. Остановка сервера
# /etc/init.d/mysql stop
2. Запуск mysql со скипом привилегий
mysqld --skip-grant-tables --user=mysql & или mysqld_safe --skip-grant-tables &
3. Логинимся в БД без пароля. Пароль хэшируется с помощью функции PASSWORD(str). Это специальная функция, которая возвращает строку 16-byte и используется системой аутентификации исключительно mysql сервером. Поэтому, используйте MD5 или SHA1 для хранения паролей приложений, которые юзают mysql, но не PASSWORD.
mysql> use mysql; mysql> select Host,User,Password from user; +-----------+------------------+-------------------------------------------+ | Host | User | Password | +-----------+------------------+-------------------------------------------+ | localhost | root | *30657C01D8312B05274147BF51D53702F90CB68C | | ubuntu | root | *30657C01D8312B05274147BF51D53702F90CB68C | | 127.0.0.1 | root | *30657C01D8312B05274147BF51D53702F90CB68C | | ::1 | root | *30657C01D8312B05274147BF51D53702F90CB68C | | localhost | | | | ubuntu | | | | localhost | debian-sys-maint | *90A841D52ED52171F0500125A2460FA4443BCF7F | +-----------+------------------+-------------------------------------------+ 7 rows in set (0.00 sec)
4. Теперь меняем пароль.
mysql> update user set Password=PASSWORD('alex1111') WHERE User='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
5. Так как данная информация храниться в mysql кэше — нужно его освободить, иначе пароль останется закешированным и дела не будет
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
6. Теперь останавливаем mysqld, который запускали вручную и запускаем init-скрипт.
kill `pgrep mysql` /etc/init.d/mysql start * Starting MySQL (Percona Server) database server mysqld
В следующей статье пойдет речь о простой и быстрой настройке mysql кластера, используя Percona XtraDB Cluster