Category Archive Базы данных

Автор:human

Настройка mysql,percona и информация по командам

Создание базы данных и пользователя и наделение их правами

Обновим права

Как слить данных для тестировщиков — например 100 записей

Дать все права root

GRANT ALL PRIVILEGES ON *.* TO root@’%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;

Как импортировать все таблицы sql в базу

Выполнить Mysql запрос из bash

Как сменить пароль mysql

Свой пароль можно поменять через:

Пароль определенного пользователя можно поменять через:

тоже самое делают:

или

или mysqladmin -u логин password пароль

Инструкция по сбросу пароль root  Mysql.

Останавливаем MySQL:

Запускаем в режиме без проверки прав доступа:
/usr/sbin/mysqld —skip-grant-tables &

Выбираем активную БД:

Обновляем пароль:
update user set Password=PASSWORD(‘новый пароль’) WHERE User=’root’;

Завершаем работу mysqld:

Запускаем MySQL

Бэкап данных с помощью p

ercona xtrabackup

 

Архивация данных и восстановление одной таблицы из бд

Ниже представленный скрипт позволяет из архива полного дампа вытащить одну таблицу.

Небольшое пояснение по скрипту. В качестве параметров он принимает имя базы данных и имя таблицы, которую необходимо восстановить.

Имя архива состоит из имени базы с расширением sql.gz

Пример выполнения скрипта

Из архива базы database_name извлечется таблица table_name

После этого, если необходимо, можно восстановить данную таблицу в базу.

Убить большое количество запросов в базу

Как убить большое количество запрсов в базу данных? Всё просто. Например запросы, которые длятся больше 10 секунды и с пользователем user_remote

Смотрим количество процессов(необязательно);

Выполняем запрос из файла.

Mysql slow log включить

Добавим строку

Проверить значения переменных можно следующим образом:

Mysql очистка bilog

Просто установим количество дней для хранения бинлогов и очистим их.

Включаем slave compressed protocol

Бэкап данных со slave с жатием в gz

mysqldump -u root -pPassword -R —master-data=2 —max-allowed-packet=1024M -B dbname | gzip > dbname.sql.gz

Пропуск ошибок репликации

Mysql тюнинг репликации

Устанавливаем параметры количества потоков репликации. Ставим в зависимости от реплицируемых баз данных — по 1 на базу, если у вас 10 баз — ставьте 10.

Смотрим

Избавляемся от лагов mysql

1) sync_relay_log = 0
2) sync_relay_log_info = 0
3) sync_master_info = 0
Смотрим значения

Все делаем через

Переключение slave->master в случае отказа master

В случае отказа основного (master) сервера СУБД, необходимо вручную или автоматически скриптом переключить кластер на другой master-сервер СУБД. Для этого обычно slave-сервер, хранящий последние реплицированные данные, переводят в режим основного.

Общая схема этой процедуры такова:

Закрываем доступ клиентов к веб-приложению
Если используется двухуровневая конфигурация (фронтэнд nginx — бэкэнд apache и т.п.), рекомендуется на фронтэнде отключить доступ к бэкэнду (веб-приложению) и отдавать при обращении клиентов к кластеру информационную страницу о регламентных работах.

Останавливаем на всех slave-серверах поток получения обновлений бинарного лога с основного (master) сервера:

Ждем, пока от потока выполнения команд slave (SQL_THREAD) не появится сообщение «Has read all relay log; waiting for the slave I/O thread to update it», говорящее о том, что slave-сервер выполнил все команды из relay-лога в своей базе. Сразу останавливать slave командой STOP SLAVE не рекомендуется, т.к. не все SQL-команды могут быть выполнены из relay-лога (по причине отставания и т.п.), а при переключении slave на новый мастер, relay-лог будет очищен и, возможно, потеряется часть «непроигранных» данных.

Подготовка нового master-сервера

Убеждаемся, что на slave-сервере, который мы хотим сделать master-сервером, бинарный лог ведется и не логируются запросы из master:

Полностью останавливаем slave — потоки чтения бинарного лога и выполнения SQL-команд:

Команда RESET MASTER необходима для очистки бинарного лога нового master, иначе, если в бинарном логе будут записи (устаревшие и т.п.), они проиграются на подключаемых к нему slave-серверах. Такое возможно, если сервер был master с включенным бинарным логом, потом стал slave и перестал использовать бинарный лог, потом снова переводится в режим master.

Итак, новый master подготовлен, у него очищен бинарный лог, и он готов обрабатывать запросы.

Переключение slave-серверов на новый master-сервер

На всех slave-серверах выполняем:

Настройка репликации mysql через скрипт

Добавим содержимое в скрипт

Делаем его исполняемым и запускаем

Естественно указываем свои ип адреса мастера и раба.

Обязательно настраиваем бинарные логи на мастере.
Добавим в /etc/my.cnf на мастере.
[mysqld]
log-bin=mysql-bin
Рестартим mysql.
не забываем установим server-id на мастере и слейве — 1 и 2.
На мастере /etc/my.cnf

На слейве /etc/my.cnf

P.S. Как изменить формат mysql лога bin.

Mysql работаем с бинлогами

Например:

В данном случае будет обработан файл mysql-bin.000038 (из текущей директории), вывод оправится в out.sql, будут выведены только команды, относящиеся к изменению базы с именем db_name. Параметром -s мы запретили вывод дополнительной служебной информации.
Более полезный пример:

Mysql error ib_logfile0 is of different size

Что делать, если во время перезапуска Mysql показывает подобную ошибку:

# Цифры могут быть другими

Это значит, что с момента последней перезагрузки в файле my.cnf был изменен такой параметр:

Как видно из ошибки, фактический размер ib_logfile файлов составляет 5Мб, хотя в настройках указан размер в 256Мб. Существует два варианта решения этой проблемы.

1. Изменить размер лог файлов на правильный

В нашем случае необходимо изменить настройки в my.cnf на:

После этого повторно запустить Mysql.

2. Обновить размер файлов

Для того, чтобы Mysql заработал с новой настройкой, необходимо сначала остановить Mysql:

После этого удалить все текущие бинлоги:

# Нужно удалить оба файла: ib_logfile0 и ib_logfile1

И снова запустить Mysql, он сам пересоздаст эти файлы:

Восстановление таблиц mysql

Автор:human

Percona XtraDB установка, настройка и миграция

Percona XtraDB установка, настройка и миграция

1.Требования

  • Мимум 3 ноды, так как если отпадет одна нода и подключиться назад 2-я нода заблокируется и будет скидывать реплику на восстановленную ноду, т.е. останется активна только одна нода
  • Только Linux, только хардкор!!
  • Перкона работает только с MySQL InnoDB движком;
  • Перед установкой вам необходимо удалить все существующие пакеты Mysql mysql-client, mysql-server, postfix...
  • Ip адреса node1 10.1.9.162, node2 10.1.9.164, node3 10.1.9.164, node 4 10.1.9.165 Будем использовать 4 ноды.

2 Installation Procedure — процедура установки

Perkona это всего лишь модифицированная Mysql для возможности мультимастер репликации.

Итак, приступим к установке. Кстати, официальная документация по установке на debian системы на оф.сайте. Заранее убедитесь что у Вас не установлено никаких пакетов mysql в системе:

2.1.Установка percona 5.6 на debian 8 jessie

Во время устаноки устанавливаем пароли для Mysql

3. Настройка НОД

Открываем порты на всех нодах. Для этого я устанавливаю пакет.

Проверяем наличие пакета NTP для того, чтобы время на серверах было синхронно настроено
apt-get install ntp -y
Добавляем строку server ntp.mobatime.ru

3.1 Настройка ноды 1(стартовой)

Подправим конфиг /etc/mysql/my.cnf

Мой конфиг на нагруженную систему. Обратите внимание ври первом запуске первой ноды список всех нод с параметром

пустой. Это нужно для инициализации кластера. Данное значение

мы поменяем на

после запуска других нод кластера и сделаем

ЭТО ОЧЕНЬ ВАЖНО!!!
Итак просмотрим весь конфиг.

Создадим каталог для mysql — логов,bin и др(так как это у нас указано в конфиге).

Создадим пользователя и дадим ему пароль(в mysql ), назначим права;

или так

Идем далее. Добавим экстра функции

Так же необходимо отключить скрипт автозапуска проверки таблиц в debian(НА ВСЕХ НОДАХ)

touch /etc/mysql/NODEBIANSTART

 

3.2 Нода 2

Редактируем /etc/mysql/my.cnf. Он очень похож на первый, различия в том, что

указан сразу с адресами всех нод!!! и в параметре

указан ip нашей 2-ой ноды. В параметре

xtradb будет 2, так как это наша вторая нода. В других нодах параметры меняем аналогичным образом(wsrep_node_address=равен ip ноды,wsrep_node_name=xtradb- равен порядковуму номеру ноды)

Сейв.

Так же создадим каталог для mysql — логов,bin и др(так как это у нас указано в конфиге).

5.3 Percona Database Configuration

These steps configure the database section of the gerrit config file and must be followed once you have completed the installation of the Percona XtraDB cluster with Gerrit.

Procedure

When installing Gerrit with Percona XTRADB using an ‘n-nodes’ configuration, you need to:

  1. Create the reviewdb database only on one node (the other nodes will replicate this).
  2. Install vanilla gerrit on that node or on a node that connects to that database node.
  3. Proceed with the standard installation of GerritMS.
  4. Usually in a GerritMS-Percona configuration, each Gerrit node connects to an individual Percona XtraDB node, sitting maybe on the same host as Gerrit. So in the gerrit.config property file, in the dabase section, you will find localhost as the hostname to connect to.
  5. Then, if you want, you can maximise the database access speed from Gerrit to Percona XtraDB by using connection pooling. For this you need to:
    • edit the etc/gerrit.config file and
    • add or replace this piece of configuration in the database section:

      Depending on the load of the machine you can raise or lower the poolLimit or the poolMinIdle properties. Just keep in mind that, since, as usual, the default max number of connections for a MySQL server database is 151, you need to raise that number if you need to set the poolLimit to a value close or higher than 150. If you need to raise the max number of connection to MySQL (Percona) server, the you have to modify the my.cnf file and add something like:
  6. The last step is to modify the Git MultiSite configuration file (/opt/wandisco/git-multisite/replicator/properties/application.properties) for each node that will access a local master Percona database. Replace the following properties, or add them to the bottom of the file:

Note: Since Percona XtraDB cluster is based on MySQL server, the configuration is the same as the one for MySQL server.

5.4 Миграция с Mysql на Percona XtraDB

Требования

  • Нам надо будет создать дамп базы данных с помощью утилиты mysqldump
  • Вам надо учесть один момент, если ваша база данных не и использует InnoDB, то вам вначале необходимо выполнить еще один шаг, который трансформирует вашу базу данных из «ENGINE=MyISAM»‘ в  «ENGINE=InnoDB».

Процедура миграции

  1.  Делаем дамп базы данных:
  2.  Если вам необходимо модифицировать дамп базы данных, то сделайте backup до этого.
  3.  Установите и настройте Percona XtraDb(до этого удалив mysql)
    • Импортируйте старую базу данных на наш кластер Percona XtraDB
    •  На другие ноды автоматически произойдет репликация, и вам не надо будет производить процедуру импорта на каждую из них.

6. Решение проблем PERCONA CLUSTER:

Проблема1 WSREP_SST: [INFO] Waiting for SST streaming to complete! При подключении ноды донора и старте mysql

Решение. Удаляем папку с .sst —

sudo rm -r /var/lib/mysql/.sst

sudo /etc/init.d/mysql start

P.S. Может понадоиться повторный запуск, пока нода реплика не догонит донора.

Проблема2  MySQL PID not found, pid_file detected/guessed: /var/run/mysqld/mysqld.pid

Решение Создаем Pid файл и даем ему права mysql

touch /var/run/mysqld/mysqld.pid && chown mysql:mysql /var/run/mysqld/mysqld.pid

sudo /etc/init.d/mysql star

Проблема3 WSREP: SST failed: 1 (Operation not permitted)

Решение. Я решил данную проблему переустановкой percona cluster и

Первое. Я изменил в конфиге следующие строки:

Затем сделал старт.
Подождал пока запустится.
Затем вернул всё обратно и подождал пока нода догонит реплики(надо время). Всё — можно пользовать.

Оф доки Percona по миграции https://www.percona.com/doc/percona-server/5.6/upgrading_guide_55_56.html

Как ускорить импорт данных

Установка и настройка proxysql — балансировщика записи-чтения для percona xtradb cluster

Запуск proxysql

/usr/bin/proxysql-admin —proxysql-user=root —proxysql-password=GtnhCnjksgbyVbybcnh —proxysql-port=6032 —proxysql-host=10.1.9.205 —cluster-user=sstuser —cluster-password=Cbrt205 —cluster-port=3306 —cluster-host=10.1.9.205 —galera-check-interval=1000 —enable —mode=’loadbal’ —adduser

Автор:human

Установка и настройка mysqlcluster на centos7

MySQL Cluster is designed to provide a MySQL compatible database with high availability and low latency. The MySQL Cluster technology is implemented through the NDB (Network DataBase) and NDBCLUSTER storage engines and provides shared-nothing clustering and auto-sharding for MySQL database systems. In the shared-nothing architecture, each of nodes has its own memory and disk, the use of shared storage such as NFS, SANs is not recommended and supported.

To implement a MySQL Cluster, we have to install three types of nodes. Each node type will be installed on it’s own server. The components are:

1. Management Node — NDB_MGMD/MGM
The Cluster management server is used to manage the other node of the cluster. We can create and configure new nodes, restart, delete, or backup nodes on the cluster from the management node.

2. Data Nodes — NDBD/NDB
This is the layer where the process of synchronizing and data replication between nodes happens.

3. SQL Nodes — MySQLD/API
The interface servers that are used by the applications to connect to the database cluster.

In this tutorial, I will guide you trough the installation and configuration of a MySQL Cluster with centOS 7. We will configure the management node, two data nodes, and two SQL nodes.

Prerequisites

  • The OS is CentOS 7 — 64bit.
  • 5 CentOS servers or virtual machines. I will use the hostnames and IP addresses as shown below:
    • Management Node
      db1 = 192.168.1.120
    • Data Nodes
      db2 = 192.168.1.121
      db3 = 192.168.1.122
    • SQL Nodes
      db4 = 192.168.1.123
      db5 = 192.168.1.124

Step 1 — Setup Management Node

The first step is to create the «Management Node» with CentOS 7 db1 and IP 192.168.1.120. Make sure you are logged into the db1 server as root user.

A. Download the MySQL Cluster software

I’ll download it from the MySQL site with wget. I’m using the «Red Hat Enterprise Linux 7 / Oracle Linux 7 (x86, 64-bit), RPM Bundle » here which is compatible with CentOS 7. Then extract the tar file.

cd ~
wget https://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar
tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

Install MySQL Cluster package rpm.

B. Install and Remove Packages

Before you install the rpm package for MySQL Cluster, you need to install perl-Data-Dumper that is required by the MySQL-Cluster server. And you need to remove mariadb-libs before we can install MySQL Cluster.

yum -y install perl-Data-Dumper
yum -y remove mariadb-libs

C. Install MySQL Cluster

Install MySQL Cluster package with these rpm commands:

cd ~
rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

Make sure there is no error.

D. Configure MySQL Cluster

Create a new directory for the configuration files. I will use the «/var/lib/mysql-cluster» directory.

mkdir -p /var/lib/mysql-cluster

Then create new configuration file for the cluster management named «config.ini» in the mysql-cluster directory.

cd /var/lib/mysql-cluster
vi config.ini

Paste the configuration below:

Save the file and exit.

E. Start the Management Node

Next start the management node with the command below:

ndb_mgmd —config-file=/var/lib/mysql-cluster/config.ini

The result should be similar to this:

MySQL Cluster Management Server mysql-5.6.28 ndb-7.4.10
2016-03-22 19:26:08 [MgmtSrvr] INFO     — The default config directory ‘/usr/mysql-cluster’ does not exist. Trying to create it…
2016-03-22 19:26:08 [MgmtSrvr] INFO     — Successfully created config directory

The management node is started, now you can use command «ndb_mgm» to monitor the node:

ndb_mgm
show

Check cluster status.

You can see the management node has been started with: mysql-6.6 and ndb-7.4.

Step 2 — Setup the MySQL Cluster Data Nodes

We will use 2 CentOS servers for the Data Nodes.

  1. db2 = 192.168.1.121
  2. db3 = 192.168.1.122

A. Login as root user and download the MySQL Cluster software

Login to the db2 server with ssh:

ssh root@192.168.1.121

Then download the MySQL Cluster package and extract it:

cd ~
wget https://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar
tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

B. Install and Remove Packages

Install perl-Data-Dumper and remove the mariadb-libs:

yum -y install perl-Data-Dumper
yum -y remove mariadb-libs

C. Install MySQL Cluster

Now we can install the MySQL Cluster packages for the Data Nodes with these rpm commands:

cd ~
rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

Make sure there is no error.

D. Configure Data Node

Create a new configuration file in the /etc directory with the vi editor:

vi /etc/my.cnf

Paste configuration below:

Save the file and exit.

Then create the new directory for the database data that we defined in the management node config file «config.ini».

mkdir -p /var/lib/mysql-cluster

Now start the data node/ndbd:

ndbd

results:

2016-03-22 19:35:56 [ndbd] INFO     — Angel connected to ‘192.168.1.120:1186’
2016-03-22 19:35:56 [ndbd] INFO     — Angel allocated nodeid: 2

MySQL cluster node is online.

Data Node db2 connected to the management node ip 192.168.1.120.

E. Redo step 2.A — 2.D on db3 server.

As we have 2 data nodes, please redo the steps 2.A — 2.D on our second data node.

Step 3 — Setup SQL Node

This is step contains the setup for the SQL Node that provides the application access to the database. We use 2 CentOS servers for the SQL Nodes:

  1. db4 = 192.168.1.123
  2. db5 = 192.168.1.124

A. Log in and Download MySQL Cluster

Login to the db4 server as root user:

ssh root@192.168.1.123

And download MySQL Cluster package:

cd ~
wget https://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar
tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

B. Install and Remove Packages

Install perl-Data-Dumper and remove the mariadb-libs that conflict with MySQL Cluster.

yum -y install perl-Data-Dumper
yum -y remove mariadb-libs

C. Install MySQL Cluster

Install the MySQL Cluster server, client and shared package with the rpm commands below:

cd ~
rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

D. Configure the SQL Node

Create a new my.cnf file in the /etc directory:

vi /etc/my.cnf

And paste configuration below:

Save the file and exit the editor.

Start the SQL Node by starting the MySQL server:

service mysql start

E. Redo step 3.A — 3.D on db5 server.

Please redo the steps 3.A — 3.D on the second SQL server (db5).

Step 4 — Monitor the Cluster

To see the cluster status, we have to log into the management node db1.

ssh root@192.168.1.120

We can use the ndb_mgm command to see the cluster status:

ndb_mgm
ndb_mgm> show

Check the NDB clsuter state.

Another useful command is:

ndb_mgm -e «all status»
ndb_mgm -e «all report memory»

Step 5 — Testing the Cluster

To perform a test on our new MySQL Cluster, we have to login to the SQL Nodes db4 or db5 servers.

Login to the db4 server:

ssh root@192.168.1.123

Change the default MySQL password that stored in «.mysql_secret» file in root directory:

cd ~
cat .mysql_secret

this is my sample:

# The random password set for the root user at Tue Mar 22 19:44:07 2016 (local time): qna3AwbJMuOnw23T

Now change the password with command below:

mysql_secure_installation

Type your old mysql password and then type the new one, press enter to confirm all.

If all is done, you can login to the MySQL shell with your password:

mysql -u root -p

After you logged in, create a new root user with host «@«, so we will be able to access the MySQL from outside.

CREATE USER ‘root’@’%’ IDENTIFIED BY ‘aqwe123’;

Replace aqwe123 with your own secure password! Now you can see the new root user with host «@» on the MySQL user list:

select user, host, password from mysql.user;

And grant the new root user read and write access from the remote node:

GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY PASSWORD ‘*94CC7BF027327993D738E11…(Encrypted PASSWORD)’ WITH GRANT OPTION;

Grant priveliges.

Now try to create a new database from db4 server and you will see the database on db5 too.

This is just a sample result for testing the cluster data replication.

All nodes are online.

The MySQL Cluster has been setup successfully on CentOS 7 with 5 server nodes.

Conclusion

MySQL Cluster is a technology that provides High Availability and Redundancy for MySQL databases. It uses NDB or NDBCLUSTER as the storage engine and provides shared-nothing clustering and auto-sharding for MySQL databases.  To implement the cluster, we need 3 components: Management Node(MGM), Data Nodes (NDB) and SQL Nodes (API). Each of node must have its own memory and disk. It is not recommended to use network storage such as NFS. To install MySQL Cluster on a CentOS 7 minimal system, we have to remove the mariadb-libs package, mariadb-libs conflict with MySQL-Cluster-server and you have to install the perl-Data-Dumper package, it’s needed by MySQL-Cluster-server. A MySQL Cluster is easy to install and configure on multiple CentOS servers.

Автор:human

Отказоустойчивые решения mysql — обзор

Отказоустойчивые решения mysql — обзор


1 вариант — pacemakerhearbeatdrbdMysq

2 вариант — Master-Master Manager

3 вариант — MHA (Master High Availibility Manager and Toold for MySQL)

4 вариант — Gtid (global Transaction ID).jpg

5 вариант — Mysql Cluster

Galera Cluster for MySQL vs MySQL (NDB) Cluster: A High Level Comparison