Mysql (часть 4). Администрирование percona mysql

Впредыдущей статьешла речь о настройке 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
  1. Запуск mysql со скипом привилегий
mysqld --skip-grant-tables --user=mysql &
или
mysqld_safe --skip-grant-tables &
  1. Логинимся в БД без пароля. Пароль хэшируется с помощью функции 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)
  1. Теперь меняем пароль.
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
  1. Так как данная информация храниться в mysql кэше — нужно его освободить, иначе пароль останется закешированным и дела не будет
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

  1. Теперь останавливаем mysqld, который запускали вручную и запускаем init-скрипт.

    kill pgrep mysql /etc/init.d/mysql start

    • Starting MySQL (Percona Server) database server mysqld

В следующей статье пойдет речь о простой и быстрой настройке mysql кластера, используя Percona XtraDB Cluster

results matching ""

    No results matching ""