Skip to main content
added 123 characters in body
Source Link
Ergest Basha
  • 4.5k
  • 3
  • 7
  • 21

If the structure has changed you shouldn't restore from an old dump, you will overwrite the changes.

I suggest use the mysqldump command as follows

sudo mysqldump -u root --complete-insert --no-create-info db_name > test_dump.sql

test_dump.sql now will have the following structure

LOCK TABLES `my_table` WRITE;
/*!40000 ALTER TABLE `my_table` DISABLE KEYS */;
INSERT INTO `my_table` (`id`, `phone_number`, `start_epoch`, `status_code`, `status_name`, `end_epoch`, `cid`, `reg_date`) VALUES (1,'xxxxxxxxx','25/11/2020 09:01:37','120','KO - Irreperibile/Spento Irraggiungibile/fax/segre','25/11/2020 09:01:37','xxxxxxxx','2020-11-26 10:48:42'),(2,'xxxxxxxxxxxx','25/11/2020 09:01:45','106','KO - Gia Cliente','25/11/2020 09:02:50','xxxxxxx','2020-11-26 10:48:42'),(3,'xxxxxxxxxx','25/11/2020 09:03:02','102','KO - Rifiuta Ascolto Offerta','25/11/2020 09:04:03','xxxxxxx','2020-11-26 10:48:42')

Use complete INSERT statements that include column names.

Do not write CREATE TABLE statements that create each dumped table.

Note This option does not exclude statements creating log file groups or tablespaces from mysqldump output; however, you can use the --no-tablespaces option for this purpose.

Note. If the insert statement contains same data (primary key/unique key) , duplicate value... error will be thrown

If the structure has changed you shouldn't restore from an old dump, you will overwrite the changes.

I suggest use the mysqldump command as follows

sudo mysqldump -u root --complete-insert --no-create-info db_name > test_dump.sql

test_dump.sql now will have the following structure

LOCK TABLES `my_table` WRITE;
/*!40000 ALTER TABLE `my_table` DISABLE KEYS */;
INSERT INTO `my_table` (`id`, `phone_number`, `start_epoch`, `status_code`, `status_name`, `end_epoch`, `cid`, `reg_date`) VALUES (1,'xxxxxxxxx','25/11/2020 09:01:37','120','KO - Irreperibile/Spento Irraggiungibile/fax/segre','25/11/2020 09:01:37','xxxxxxxx','2020-11-26 10:48:42'),(2,'xxxxxxxxxxxx','25/11/2020 09:01:45','106','KO - Gia Cliente','25/11/2020 09:02:50','xxxxxxx','2020-11-26 10:48:42'),(3,'xxxxxxxxxx','25/11/2020 09:03:02','102','KO - Rifiuta Ascolto Offerta','25/11/2020 09:04:03','xxxxxxx','2020-11-26 10:48:42')

Use complete INSERT statements that include column names.

Do not write CREATE TABLE statements that create each dumped table.

Note This option does not exclude statements creating log file groups or tablespaces from mysqldump output; however, you can use the --no-tablespaces option for this purpose.

If the structure has changed you shouldn't restore from an old dump, you will overwrite the changes.

I suggest use the mysqldump command as follows

sudo mysqldump -u root --complete-insert --no-create-info db_name > test_dump.sql

test_dump.sql now will have the following structure

LOCK TABLES `my_table` WRITE;
/*!40000 ALTER TABLE `my_table` DISABLE KEYS */;
INSERT INTO `my_table` (`id`, `phone_number`, `start_epoch`, `status_code`, `status_name`, `end_epoch`, `cid`, `reg_date`) VALUES (1,'xxxxxxxxx','25/11/2020 09:01:37','120','KO - Irreperibile/Spento Irraggiungibile/fax/segre','25/11/2020 09:01:37','xxxxxxxx','2020-11-26 10:48:42'),(2,'xxxxxxxxxxxx','25/11/2020 09:01:45','106','KO - Gia Cliente','25/11/2020 09:02:50','xxxxxxx','2020-11-26 10:48:42'),(3,'xxxxxxxxxx','25/11/2020 09:03:02','102','KO - Rifiuta Ascolto Offerta','25/11/2020 09:04:03','xxxxxxx','2020-11-26 10:48:42')

Use complete INSERT statements that include column names.

Do not write CREATE TABLE statements that create each dumped table.

Note This option does not exclude statements creating log file groups or tablespaces from mysqldump output; however, you can use the --no-tablespaces option for this purpose.

Note. If the insert statement contains same data (primary key/unique key) , duplicate value... error will be thrown

Source Link
Ergest Basha
  • 4.5k
  • 3
  • 7
  • 21

If the structure has changed you shouldn't restore from an old dump, you will overwrite the changes.

I suggest use the mysqldump command as follows

sudo mysqldump -u root --complete-insert --no-create-info db_name > test_dump.sql

test_dump.sql now will have the following structure

LOCK TABLES `my_table` WRITE;
/*!40000 ALTER TABLE `my_table` DISABLE KEYS */;
INSERT INTO `my_table` (`id`, `phone_number`, `start_epoch`, `status_code`, `status_name`, `end_epoch`, `cid`, `reg_date`) VALUES (1,'xxxxxxxxx','25/11/2020 09:01:37','120','KO - Irreperibile/Spento Irraggiungibile/fax/segre','25/11/2020 09:01:37','xxxxxxxx','2020-11-26 10:48:42'),(2,'xxxxxxxxxxxx','25/11/2020 09:01:45','106','KO - Gia Cliente','25/11/2020 09:02:50','xxxxxxx','2020-11-26 10:48:42'),(3,'xxxxxxxxxx','25/11/2020 09:03:02','102','KO - Rifiuta Ascolto Offerta','25/11/2020 09:04:03','xxxxxxx','2020-11-26 10:48:42')

Use complete INSERT statements that include column names.

Do not write CREATE TABLE statements that create each dumped table.

Note This option does not exclude statements creating log file groups or tablespaces from mysqldump output; however, you can use the --no-tablespaces option for this purpose.