QUIZGUM

Coding Class

Quizgum : DB Backup and Restore

Database backup and restore

Now let's talk about backing up and restoring a database.
If you don't have a database that contains customer information when you're running a real site ...
I'm really frustrated. Backup is required !!
That way, you can overcome the crisis by restoring it right away.
If you are connected to mysql, please disconnect.
And the following command ...

mysqldump -uAccount -pPassword Database name > Backup file name

Then back up the smart database. The file name to back up is smartdump.sql.

mysqldump -uroot -proot smart > smartdump.sql

Enter the above command.

For macOS users, the above command will not run. Enter the following command.

sudo ./mysqldump -uroot -proot smart > smartdump.sql
mysql image

If you see a screen like the above success! You can see the smartdump.sql file by typing ls -al in a terminal.

ls -al

If you open this sql file with an editor, you can see that it contains information.

mysql image

Open the smartdump.sql file with the ATOM editor. Open the ATOM program first and open the smartdump.sql file.

mysql image

Several DB statements are shown.

DROP TABLE IF EXISTS `my_smart_devices`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_smart_devices` (
`num` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`thenumberofcpu` varchar(20) DEFAULT NULL,
`company` varchar(20) DEFAULT NULL,
PRIMARY KEY (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `my_smart_devices`
--

LOCK TABLES `my_smart_devices` WRITE;
/*!40000 ALTER TABLE `my_smart_devices` DISABLE KEYS */;
INSERT INTO `my_smart_devices` VALUES (1,'galaxy nexus','2','samsung'),(2,'galaxy tab','1','samsung'),(3,'iPad2','2','apple');
/*!40000 ALTER TABLE `my_smart_devices` ENABLE KEYS */;
UNLOCK TABLES;

Let's check what each one says.

DROP TABLE IF EXISTS `my_smart_devices`;

The above statement deletes the my_smart_devices table if it exists.

CREATE TABLE `my_smart_devices` (
    `num` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) DEFAULT NULL,
    `thenumberofcpu` varchar(20) DEFAULT NULL,
    `company` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

The above statement is, as you already know, a statement that creates the my_smart_devices table.

LOCK TABLES `my_smart_devices` WRITE;

In the above statement, LOCK TABLES is a statement that locks the my_smart_devices table. The table will not be read or written. However, WRITE is written after the table name. Write is allowed.

INSERT INTO `my_smart_devices` VALUES (1,'galaxy nexus','2','samsung'),(2,'galaxy tab','1','samsung'),(3,'iPad2','2','apple');

The above statement, as you know, enters the data of the table.
If there was a lot of data, the insert would have been longer, and if there was only one data, it would have been shorter:

INSERT INTO `my_smart_devices` VALUES (1,'galaxy nexus','2','samsung');

Now that we have entered, we unlock the table.

UNLOCK TABLES;

Then connect to MySQL, delete the my_smart_devices table in the smart DB and exit MySQL.

mysql image

Now let's restore it.

mysql -uAccount -pPassword Database name < backup file name

mysql -uroot -p비번 smart < smartdump.sql

And if you check, the table is restored.

mysql image

If you want to restore to a database with a different name Create a database with the name you want to create.
For example If you restore smartdump.sql backed up to a database named smart_db Enter the following:

mysql -uroot -p123456 smart_db < smartdump.sql

Then check that the data is entered correctly.

mysql image