MySQL Delete, Backup Tips

Deleting Data from Tables

1. Delete from table_name;

• Deletes data from the table, but does not renew row id values.

2. Truncate

• Deletes both the data and memory spaces, including row id values.

Renewing id values after deletion

Delete from table_name;
alter table table_name auto_increment = 1;

Deleting Specific Rows

delete from table_name where img_url = '';
delete from table_name where img_url is Null;

Tables

Tables containing foreign key fields cannot be deleted with the delete command. MySQL prevents such deletion so that other tables don't end up referencing non-existing tables. In order to force delete in such cases, deactivate foreign key checks, delete, and then reactive the checks.

mysql> SET foreign_key_checks = 0;
mysql> delete from table_name;
mysql> SET foreign_key_checks = 1;

MySQL data backup

[백업 Export]

mysqldump -u mysql_user -p DATABASE_NAME > backup.sql
mysqldump -u root -p freeporkbelly > backup.sql

[백업 Import]

mysql -u mysql_user -p DATABASE < backup.sql