How to import a MySQL dump from command line WITH overwrite

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

[<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="394b56564d794a56545c5d5840">[email protected]</a> 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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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

Method 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.

Method 3

I’d suggest –add-drop-table option.

Method 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’


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x