BG Tech Blog

Играейки си с една мъничка SQL заявка (само 38 реда) и тествайки скоростта й на MySQL 5.7 намерих нещо интересно 🙂

За да направят версия 5.7 SQL99 compliant, разработчиците на MySQL са променили начина на работа на GROUP BY.

Подробно описание от човек, гледащ в бъдещето, което всъщност вече е тук -> http://rpbouman.blogspot.co.uk/2014/09/mysql-575-group-by-respects-functional.html

Що е то SQL99? -> https://en.wikipedia.org/wiki/SQL:1999

От време не време се налага да се занимавам с изчисляване на разстояния от точка А до точка В. Останах приятно изненадан от поддържката на GIS (https://en.wikipedia.org/wiki/Geographic_information_system) в MySQL 5.7.

Тъй като все още не съм намерил време да го тествам, ето линк към една доста добре написана статия с достатъчно информация и тестове за добър старт на деня -> https://www.percona.com/blog/2016/02/03/new-gis-features-in-mysql-5-7/

При опит да сменим часовата зона на MySQL сървъра:

SET time_zone = 'Europe/London';

Се получи следната грешка:

SQLSTATE[HY000]: General error: 1298 Unknown or incorrect time zone: "Europe/London"

Как да оправим грешката? Импортираме всички timezones в MySQL сървъра 🙂

От конзолата пишем:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Където:
-u root е потребителското име за root достъп до MySQL сървъра.

Лесно и бързо, с точно една команда 🙂

mysql> SELECT @@global.time_zone, @@session.time_zone;

Ето резултата:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.07 sec)

Работейки по превода система, съдържаща в себе си хиляди фрази, трябваше да измисля начин, по който да показвам колко думи има във всяка фраза и съответно колко общо думи има във всеки отделен проект.
За някои от езиците се плаща на външни преводачи, така че според броя думи се определя и сумата, която те ще получат.

Последователно изпълняваме няколко заявки:

Заявка 1:

DELIMITER //

Заявка 2:

CREATE FUNCTION wordcount(str TEXT)
       RETURNS INT
       DETERMINISTIC
       SQL SECURITY INVOKER
       NO SQL
  BEGIN
    DECLARE wordCnt, idx, maxIdx INT DEFAULT 0;
    DECLARE currChar, prevChar BOOL DEFAULT 0;
    SET maxIdx=char_length(str);
    WHILE idx < maxIdx DO
        SET currChar=SUBSTRING(str, idx, 1) RLIKE '[[:alnum:]]';
        IF NOT prevChar AND currChar THEN
            SET wordCnt=wordCnt+1;
        END IF;
        SET prevChar=currChar;
        SET idx=idx+1;
    END WHILE;
    RETURN wordCnt;
  END//

Заявка 3:

DELIMITER ;

Ето резултата:

mysql> SELECT sentence, wordcount(sentence) as "Words" from test;
+------------------------------+-------+
| sentence                     | Words |
+------------------------------+-------+
| Hello World                  |     2 |
| Hello World                  |     2 |
| Mary had a little lamb       |     5 |
| Her fleece was white as snow |     6 |
| Everywhere that mary went    |     4 |
| Umm, sheep followed her      |     4 |
+------------------------------+-------+
6 rows in set (0.02 sec)

Можете да прочетете коментарите тук ако имате нужда от повече информация.

“Repair by sorting” is MyISAM’s way of updating the indexes after a bulk insert. When inserting Millions of rows of data into a MyISAM table, it is normal for the index updates to take longer than the actual inserts.

copy/paste @ mysql

Защо да използваме репликация на базата данни?

Има много и различни доводи, поради които хората използват репликацията (MySQL replication). Всеки сам трябва да отговори на този въпрос, като обърне внимание на нуждите на конкретният проект, в който ще се използва тази практика. Няма универсален отговор.
При нас ще го използваме най-вече за да намалим натоварването на нашия master сървър.

Как става всичко това?

За начало ни трябват 2 MySQL сървъра. За опитната постановка и двата сървъра са с версия 5.4.3-beta

master – това е сървъра, който е главен за replication cluster-a и от който всички останали ще взимат данните
slave – това е сървъра, който пази огледално копие на съдържанието на master сървъра – бази данни, таблици, записи

Настройки на master сървъра

За начало трябва да пуснем сървъра да слуша на външното IP на машината като закоментираме следния ред в my.cnf (обикновено се намира в /etc/mysql/my.cnf):

#skip-networking
#bind-address            = 127.0.0.1

Също там (в my.cnf) добавяме информация за самата репликация:

log-bin = /var/log/mysql/mysql-master-bin.log # log, в който ще се записва случващото се с MySQL сървъра (базите данни) на master сървъра
binlog-do-db=exampledb # име на базата данни, която ще репликираме. Ако махнете този ред ще се репликират всички бази
server-id=1 # задължително добавяме ID на сървъра. Изисква се от конфигурацията.

Следващата стъпка е да рестартираме master MySQL сървъра:

/etc/init.d/mysql restart

След рестарта се логваме в MySQL конзолата и даваме права на slave сървъра да чете от нашият нов master сървър:

GRANT REPLICATION SLAVE ON *.* TO 'slaveUser'@'%' IDENTIFIED BY '<password>'; (заменете <password> с парола по ваш избор)
FLUSH PRIVILEGES;

Настройки на slave сървъра

За тези настройки има много информация в Интернет. Голяма част от нея е deprecated, тоест вече е остаряла и не е съвместима с направените промени по текущия MySQL сървър.

Отваряме my.cnf на slave сървъра и правим следните промени:

server-id=2 # задължително го добавяме. Различно от server-id на master сървъра
replicate-do-db=exampledb # коя база данни ще репликираме тук, тоест коя ще взимаме от master и ще записваме тук на slave сървъра
 
# следващите данни са масово разпространени в Интернет, но не са нужни в този файл.
master-host=0.0.0.0
master-user=slaveУser
master-password=secret-password-here

И двата сървъра вече са стартирани или ако не са, трябва да ги стартирате 🙂

Сега имаме работещ master и работещ slave сървър, но това не е всичко. Трябва да кажем на slave сървъра къде се намира нашият master и кои негови бази данни да чете.

За целта се логваме в MySQL конзолата на slave сървъра и изпълняваме следните команди:

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='0.0.0.0', MASTER_USER='slaveUser', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='mysql-master-bin.000003', MASTER_LOG_POS=0, MASTER_PORT=3306;

MASTER_HOST='0.0.0.0' # IP на master сървъра
MASTER_USER='slaveUser' # потребителското име за slave сървъра, което избрахме по-горе
MASTER_PASSWORD='<password>' # паролата, която избрахме по-горе
MASTER_LOG_FILE='mysql-master-bin.000003' # това се взима от следващата описана команда
MASTER_LOG_POS=0 # тук е добре да оставим 0 и съответно на slave машината да нямаме създадена базата данни, която искаме да репликираме
MASTER_PORT=3306 # порт, на който слуша master сървъра

За да намерим MASTER_LOG_FILE изпълняваме следната команда на master сървъра

mysql> show master status;
+-------------------------+-----------+--------------+------------------+
| File                    | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+-----------+--------------+------------------+
| mysql-master-bin.000006 | 272737959 |              |                  |
+-------------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
 
mysql>

Съответно нашият MASTER_LOG_FILE се казва mysql-master-bin.000006

След като сме определили файла и сме изпълнили заявката за потребител, парола и порт на slave сървъра, пак на него изпълняваме командата за стартиране на slave:

mysql> START SLAVE;

От тук нататък би трябвало да имате изцяло работещи и реплекиращи master и slave сървъри.

За пълна информация можете да погледнете документацията на MySQL за MySQL replication