How to import a MySQL dump from command line WITH overwrite

Question

i googled a lot and i can't found nothing about it !

[root@someday backups]# mysql -u username_1 -p db_1 < tables_to_import/tables.sql 
ERROR 1050 (42S01) at line 19: Table 'ps_customer' already exists

with mysql -f is the same. i wish simply import that .sql and rewrite that tables, can someone help me ?

p.s. i know that when you export a db you can choose option "DROP TABLE" but if i have a backup, without this declaration ? how can i force ? Thanks

Dimerous

Answer 1

When you do mysqldump add --add-drop-table (like twihoX mentioned). Then do your import as usual. So something like:

mysqldump --add-drop-table -u user -p db_1 > dumpfile.sql

mysql -u user -p db_1 < dumpfile.sql 

Kor

What version of mysql is this for? I am using 5.7.11, and it gives me mysql: [ERROR] unknown option '--add-drop-table' – Pate

Afraid that was a long time ago so I don't recall. I am not using mysql right now to be able to accurately test. – Kor

On your mysql line, you forgot to put the database name after the -p switch. It should be something like: mysql -u user -p mydatabasename < dumpfile.sql – Gwenora

Answer 2

Are you trying to overwrite the entirety of the database? If so, you could manually drop all the tables, and then run your import script. This is easy to do in phpmyadmin. If you're using the CLI, the fastest way would be to use DROP DATABASE databasename and then create database, though I think you'd then have to re-grant privileges for any non-root users.

Another option would be to open up your dump file and add DROP TABLE tablename before each of the CREATE TABLE commands. You could probably do this easily with some clever regex.

Agna

Hi octern ! No, i wish to overwrite ONLY tables contained in tables.sql (in this .sql i exported 6 tables, so i wish to overwrite only these), is it possibile from a logical point of view or maybe am i missing something ? – Dimerous

Then use my second suggestion and add a DROP TABLE command for each CREATE TABLE command in tables.sql. You could write a shell script to do this, but if there are only 6 tables, you should be able to do it by hand in under a minute. – Agna

Answer 3

I'd suggest --add-drop-table option.

Yuan

according to wordpress.stackexchange.com/a/62736/10453 --add-drop-table is an option for exporting the database. Therefore on that basis the assumption would be it would have no effect on import, if the db to import didn't already have drop table sql commands within it. – Dupree

Answer 4

I know this question is a bit old and it's been marked as answered correctly, I'd just like to add this here for those (like me) who didn't use --add-drop-table when exporting.

What you can do is log in to MySQL and drop the tables that you plan to overwrite, then use --force on import.

So login to MySQL

mysql -h HOSTNAME - USERNAME -p

then tell mysql which database you wish to use

mysql> use DATABASE_NAME

drop tables that you want to overwrite

mysql> DROP TABLE my_images;

Then you are ready to import, so log out of mysql and back to where your SQL file was uploaded and run the following command

$ mysql --force -uDB_USER -p DB_NAME < myuploadedfile.sql

This will force MySQL to continue importing any new tables and ignore the 'table already exists error'

Boxcar