Backup bazy MySQL

Jako że baza jest bazą „darmową” nie posiada zbytnio rozbudowanych narzędzi do backupowania i odzyskiwania danych. Nie znajdziemy tu backupu przyrostowego i możliwości odtworzenia na punkt w czasie.

Samo wykonanie backupu bazy jest dość proste. Służy do tego zewnętrzne narzędzie MYSQLDUMP.
Wykonujemy backup bazy. Podajemy opcję -d i nazwę bazy oraz usera z któego to wykonujemy:

[root@localhost ~]# mysqldump -d testowadb1 -u root -p

Enter password:
-- MySQL dump 10.16  Distrib 10.1.22-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: testowadb1
-- ------------------------------------------------------
-- Server version       10.1.22-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `testtable1`
--

DROP TABLE IF EXISTS `testtable1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `testtable1` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-04-04 22:23:47

Zostanie utworzony skrypt pozwalający na odzyskanie bazy(tabel). Ale uwaga !!! Należy jednak zwróci uwagę że powyższy skrypt tworzy tylko strukturę bazy (tworząc puste tabele) i nie wypełnia jej danymi.

BACKUP DANYCH BAZY

Podczas uruchamiania MySQLDumpa należy użyć opcji -u -p podłączając się na konkretnego użytkownika gdyż inaczej otrzymamy komunikat o braku uprawnień do bazy

mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect

Jeśli baza istnieje i jest pusta (stworzona za pomocą powyższego skryptu) musimy wykonać polecenie:

[root@localhost ~]# mysqldump -u root -p --no-create-info testowadb1 > t1_nocreate.sql

Enter password:


-- MySQL dump 10.16  Distrib 10.1.22-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: testowadb1
-- ------------------------------------------------------
-- Server version       10.1.22-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping data for table `testtable1`
--

LOCK TABLES `testtable1` WRITE;
/*!40000 ALTER TABLE `testtable1` DISABLE KEYS */;
INSERT INTO `testtable1` VALUES (1,'Jan','Kowalski','jan.kowalski@mail.pl'),(3,'Anna','Nowak','anna.nowak@mail.pl'),(4,'Katarzyna','Polska','katarzyna.polska@mail.pl'),(5,'Robert','Barski','robert.barski@mail.pl'),(6,'Andrzej','Dulski','andrzej.dulski@mail.pl'),(7,'Agnieszka','Frankowska','agnieszka.frankowska@mail.pl'),(8,'Hubert','Goglewski','hubert.goglewski@mail.pl');
/*!40000 ALTER TABLE `testtable1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-04-04 22:25:50

PEŁNY BACKUP BAZY

Natomiast jeśli chcemy wykonać pełny backup bazy ze skryptami tworzącymi tabele oraz napełnieniem danymi wydajemy polecenie bez opcji -d (tak jak to było w pierwszym poleceniu u góry). Polecenie przekierowujemy do pliku z którego potem będziemy odtwarzać bazę.
Uwaga!!! Wcześniej trzeba zalogować się do bazy i za pomocą CREATE DATABASE stworzyć bazę.

[root@localhost ~]# mysqldump -u root -p testowadb1 > backup_bazy.sql

-- MySQL dump 10.16  Distrib 10.1.22-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: testowadb1
-- ------------------------------------------------------
-- Server version       10.1.22-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `testtable1`
--

DROP TABLE IF EXISTS `testtable1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `testtable1` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `testtable1`
--

LOCK TABLES `testtable1` WRITE;
/*!40000 ALTER TABLE `testtable1` DISABLE KEYS */;
INSERT INTO `testtable1` VALUES (1,'Jan','Kowalski','jan.kowalski@mail.pl'),(3,'Anna','Nowak','anna.nowak@mail.pl'),(4,'Katarzyna','Polska','katarzyna.polska@mail.pl'),(5,'Robert','Barski','robert.barski@mail.pl'),(6,'Andrzej','Dulski','andrzej.dulski@mail.pl'),(7,'Agnieszka','Frankowska','agnieszka.frankowska@mail.pl'),(8,'Hubert','Goglewski','hubert.goglewski@mail.pl');
/*!40000 ALTER TABLE `testtable1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-04-04 22:26:58

Jak widać w skrypcie tabele w bazie testowadb1 czyli w naszym przypadku TESTTABLE1 najpierw są usuwane a następnie tworzone i napełniane.

PRZYWRACANIE BAZY Z BACKUPU – PRZYKŁAD

Teraz przykładowo usuniemy tabelę TESTTABLE1:

MariaDB [testowa]> drop table testtable1;

Query OK, 0 rows affected (0.01 sec)

MariaDB [testowa]> select * from testtable1;

ERROR 1146 (42S02): Table 'testowa.testtable1' doesn't exist

Aby odtworzyć tabelę (bazę) wydajemy polecenie:

[root@localhost ~]# mysql -u root -p testowadb1 < backup_bazy.sql;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [testowadb1]> select * from testtable1;

+----+-----------+------------+------------------------------+
| id | firstname | lastname   | email                        |
+----+-----------+------------+------------------------------+
|  1 | Jan       | Kowalski   | jan.kowalski@mail.pl         |
|  3 | Anna      | Nowak      | anna.nowak@mail.pl           |
|  4 | Katarzyna | Polska     | katarzyna.polska@mail.pl     |
|  5 | Robert    | Barski     | robert.barski@mail.pl        |
|  6 | Andrzej   | Dulski     | andrzej.dulski@mail.pl       |
|  7 | Agnieszka | Frankowska | agnieszka.frankowska@mail.pl |
|  8 | Hubert    | Goglewski  | hubert.goglewski@mail.pl     |
+----+-----------+------------+------------------------------+
7 rows in set (0.00 sec)

BACKUP POJEDYNCZEJ TABELI

Można też wykonać backup jednej tabeli. W tym celu wykonujemy:

[root@localhost mysql]# mysqldump -d testowadb1 -u root -p --lock-tables testtable1 > /var/lib/mysql/db1_table1.sql

Enter password:

[root@localhost mysql]# less db1_table1.sql

-- MySQL dump 10.16  Distrib 10.1.22-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: testowadb1
-- ------------------------------------------------------
-- Server version       10.1.22-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `testtable1`
--

DROP TABLE IF EXISTS `testtable1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `testtable1` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-11-12 14:58:08

Tak jak w przypadku bazy tu też tylko zabezpieczana jest struktura tabeli. Aby wykonać pełny backup tabeli wykonujemy:

[root@localhost mysql]# mysqldump -u root -p testowadb1 --lock-tables testtable1 > /var/lib/mysql/db1_table2.sql

Enter password:

[root@localhost mysql]# less db1_table1.sql

-- MySQL dump 10.16  Distrib 10.1.22-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: testowadb1
-- ------------------------------------------------------
-- Server version       10.1.22-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `testtable1`
--

DROP TABLE IF EXISTS `testtable1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `testtable1` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `testtable1`
--

DROP TABLE IF EXISTS `testtable1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `testtable1` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `testtable1`
--

LOCK TABLES `testtable1` WRITE;
/*!40000 ALTER TABLE `testtable1` DISABLE KEYS */;
INSERT INTO `testtable1` VALUES (1,'Jan','Kowalski','jan.kowalski@mail.pl'),(3,'Anna','Nowak','anna.nowak@mail.pl'),(4,'Katarzyna','Polska','katarzyna.polska@mail.pl'),(5,'Robert','Barski','robert.barski@mail.pl'),(6,'Andrzej','Dulski','andrzej.dulski@mail.pl'),(7,'Agnieszka','Frankowska','agnieszka.frankowska@mail.pl'),(8,'Hubert','Goglewski','hubert.goglewski@mail.pl');
/*!40000 ALTER TABLE `testtable1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-11-12 15:01:06

Ten wpis został opublikowany w kategorii MySQL (MariaDB) i oznaczony tagami . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz