How to import a MySQL dump from command line WITH

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

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 > dumpfile.sql

mysql -u user -p < dumpfile.sql 

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.

Answer 3

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