Dupa instalarea serverului de baze de date MySQL, colectiile de date / bazele de date se gasesc la nivelul unui director (DataDir) a carui cale poate fi determinata prin consultarea variabilei de sistem numita datadir. In cazul instalarii serverului MySQL pe un sistem de operare de tip Debian, directorul de date are adresa /var/lib/mysql.
mysql> SHOW VARIABLES -> WHERE `Variable_name`='datadir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.05 sec)
Directorul de date DataDir contine cate un subdirector pentru fiecare din urmatoarele baze da date:
• mysql: baza de date corespunzatoare sistemului MySQL;
• performance_schema: ofera informatii care permit monitorizarea serverului MySQL;
• sys: ofera vizualizari care au la baza colectiile performance_schema si information_schema, si care pot fi utilizate in depanarea si gestionarea eficienta a resurselor.
In plus, locatia contine fisiere de tip log, fisiere specifice motorului de stocare InnoDB (table space), fisiere corespunzatoare certificatelor SSL, diverse fisiere de configurare (mysqld-auto.cnf).
root@dba:/# ls -lha /var/lib/mysql total 185M drwxr-x--- 6 mysql mysql 4.0K Mar 27 13:12 . drwxr-xr-x 26 root root 4.0K Mar 5 04:19 .. -rw-r----- 1 mysql mysql 56 Mar 5 04:19 auto.cnf -rw-r----- 1 mysql mysql 503 Mar 5 04:19 binlog.000001 -rw-r----- 1 mysql mysql 1.3K Mar 5 04:28 binlog.000002 ... -rw-r----- 1 mysql mysql 272 Mar 27 13:12 binlog.index -rw------- 1 mysql mysql 1.7K Mar 5 04:19 ca-key.pem -rw-r--r-- 1 mysql mysql 1.1K Mar 5 04:19 ca.pem -rw-r--r-- 1 mysql mysql 1.1K Mar 5 04:19 client-cert.pem -rw------- 1 mysql mysql 1.7K Mar 5 04:19 client-key.pem -rw-r----- 1 mysql mysql 3.4K Mar 26 04:40 ib_buffer_pool -rw-r----- 1 mysql mysql 48M Mar 27 13:14 ib_logfile0 -rw-r----- 1 mysql mysql 48M Mar 5 04:19 ib_logfile1 -rw-r----- 1 mysql mysql 12M Mar 27 13:12 ibdata1 -rw-r----- 1 mysql mysql 12M Mar 27 13:12 ibtmp1 drwxr-x--- 2 mysql mysql 4.0K Mar 5 04:19 mysql -rw-r----- 1 mysql mysql 24M Mar 27 13:12 mysql.ibd -rw-r----- 1 mysql mysql 161 Mar 25 17:20 mysqld-auto.cnf drwxr-x--- 2 mysql mysql 4.0K Mar 5 04:19 performance_schema -rw------- 1 mysql mysql 1.7K Mar 5 04:19 private_key.pem -rw-r--r-- 1 mysql mysql 452 Mar 5 04:19 public_key.pem -rw-r--r-- 1 mysql mysql 1.1K Mar 5 04:19 server-cert.pem -rw------- 1 mysql mysql 1.7K Mar 5 04:19 server-key.pem drwxr-x--- 2 mysql mysql 4.0K Mar 5 04:19 sys -rw-r----- 1 mysql mysql 16M Mar 27 13:14 undo_001 -rw-r----- 1 mysql mysql 16M Mar 27 13:14 undo_002
Cresterea numarului de colectii stocate la nivelul serverului, dar si a spatiului ocupat de aceste colectii, pot determina mutarea locatiei corespunzatoare directorului de date. O astfel de operatie implica oprirea serverului MySQL, crearea unui nou director, configurarea drepturilor de la nivelul noii locatii si mutarea continutului din directorul /var/lib/mysql, in noul director.
Oprirea serverului de baze de date MySQL si verificarea statusului acestuia poate fi realizata folosind urmatoarele doua comenzi:
root@dba:/# sudo systemctl stop mysql root@dba:/# sudo systemctl status mysql ● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: inactive (dead) since Sat 2021-03-27 13:41:10 UTC; 5s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 438 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS) Main PID: 438 (code=exited, status=0/SUCCESS) Status: "Server shutdown complete" Mar 27 13:41:10 dba systemd[1]: Stopping MySQL Community Server... Mar 27 13:41:10 dba systemd[1]: mysql.service: Succeeded. Mar 27 13:41:10 dba systemd[1]: Stopped MySQL Community Server.
Pentru noua locatie, mai intai este necesara crearea unui nou director (de exemplu, /tmp/data) si modificarea utilizatorului care detine noua locatie. Trebuie avut in vedere faptul ca directorul de date corespunzator unui sistem de tip MySQL este gestionat prin intermediul utilizatorului cu care rulueaza serverul de baze de date, utilizator care poarta numele mysql.
root@dba:/# sudo mkdir -p /tmp/data root@dba:/# sudo chown -R mysql:mysql /tmp/data
Mutarea efectiva a colectiilor de date in noua locatie se poate realiza cu ajutorul aplicatiei rsync. Aceasta permite transferul rapid al fisierelor si directoarelor intre doua locatii. Daca aplicatie nu este disponibila la nivelul sistemului de operare, mai intai se poate realiza instalarea ei.
root@dba:/# sudo apt-get install rsync Reading package lists... Done Building dependency tree Reading state information... Done The following NEW packages will be installed: rsync 0 upgraded, 1 newly installed, 0 to remove and 4 not upgraded. Need to get 0 B/397 kB of archives. After this operation, 746 kB of additional disk space will be used. Selecting previously unselected package rsync. (Reading database ... 44119 files and directories currently installed.) Preparing to unpack .../rsync_3.1.3-6_amd64.deb ... Unpacking rsync (3.1.3-6) ... Setting up rsync (3.1.3-6) ... Created symlink /etc/systemd/system/multi-user.target.wants/rsync.service → /lib/systemd/system/rsync.service. Processing triggers for man-db (2.8.5-2) ... Processing triggers for systemd (241-7~deb10u7) ...
root@dba:/# sudo rsync -av /var/lib/mysql /tmp/data sending incremental file list mysql/ mysql/auto.cnf mysql/binlog.000001 mysql/binlog.000002 ... mysql/sys/ mysql/sys/sys_config.ibd sent 182,377,044 bytes received 2,955 bytes 121,586,666.00 bytes/sec total size is 182,321,064 speedup is 1.00
Precizarea unei noi locatii pentru directorul de date al serverului MySQL necesita actualizarea valorii de la nivelul variabilei de sistem datadir. O modalitatea de realizarea a acestei operatii o reprezinta introducerea sau actualizarea optiunii de configurare datadir, cu valoarea /tmp/data/mysql/, prin intermediul fisierului de configurare de la nivelul serverului MySQL, /etc/mysql/my.cnf.
root@dba:/# pico /etc/mysql/my.cnf
[mysqld] datadir=/tmp/data/mysql
Dupa reconfigurarea locatiei folosind optiunea de configurare datadir din fisierul de configurare /etc/mysql/my.cnf, o simpla repornire a serverului va determina utilizarea noii locatii a directorului de date. Noua valoare a variabilei de sistem datadir poate fi verificata si dupa stabilirea unei conexiuni la serverul de baze de date.
root@dba:/# sudo systemctl status mysql ● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: active (running) since Sat 2021-03-27 15:30:19 UTC; 13s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 10498 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS) Main PID: 10533 (mysqld) Status: "Server is operational" Tasks: 38 (limit: 4915) Memory: 338.4M CGroup: /system.slice/mysql.service └─10533 /usr/sbin/mysqld Mar 27 15:30:18 dba systemd[1]: Starting MySQL Community Server... Mar 27 15:30:19 dba systemd[1]: Started MySQL Community Server.
mysql> SHOW VARIABLES -> WHERE `variable_name`='datadir'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | datadir | /tmp/data/mysql/ | +---------------+------------------+ 1 row in set (0.07 sec)