mysql中overwrite,如何從命令行WITH overwrite導入MySQL轉儲

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

我google了很多,我找不到任何關於它!

[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 ?

與mysql -f是一樣的。我希望簡單地導入.sql並重寫那些表,有人可以幫助我嗎?

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

附:我知道當你導出數據庫時你可以選擇“DROP TABLE”選項,但如果我有備份,沒有這個聲明?我怎么強迫?謝謝

3 个解决方案

#1

4

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.

您是否試圖覆蓋整個數據庫?如果是這樣,您可以手動刪除所有表,然后運行導入腳本。這在phpmyadmin中很容易做到。如果您正在使用CLI,最快的方法是使用DROP DATABASE databasename然后創建數據庫,但我認為您必須為任何非root用戶重新授予權限。

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.

另一種選擇是打開轉儲文件並在每個CREATE TABLE命令之前添加DROP TABLE tablename。你可以用一些聰明的正則表達式輕松地做到這一點。

#2

21

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

當你做mysqldump時添加--add-drop-table(就像提到的twihoX)。然后像往常一樣進行導入。所以類似於:

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

mysql -u user -p < dumpfile.sql

#3

0

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

我建議--add-drop-table選項。