开发者

How to Import Multi-Database MySQLdump with 'DROP TABLE IF EXISTS'?

开发者 https://www.devze.com 2023-04-11 22:32 出处:网络
I want to copy a bunch of DB\'s from server1 to server2 and I want to drop any DB\'s on server2 that conflict with the imported DB\'s, then import the DB\'s.

I want to copy a bunch of DB's from server1 to server2 and I want to drop any DB's on server2 that conflict with the imported DB's, then import the DB's.

I'm using this to import:

mysql -u root -p[password] < a_bunch_of_dbs.sql

I used phpMyAdmin to perform several exports but they are totally unusable.

The problem is that phpMyAdmin will either put:

  DROP DATABASE `database1`;

(it results in error #1008 - Can't drop database 'database1'; database doesn't exist)

OR:

  CREATE DATABASE `database1`;

(it results in error #1007 - Can't create database 'database1'; database exists)

But NOT:

  DROP DATABASE IF EXISTS `database`;
  CREATE DATABASE IF NOT EXISTS `database1`;

(obviously the "IF NOT EXISTS" is redundant but I liked it there for reassurance)

So, to solve this I either need some command line options OR I need to find out where phpmyadmin creates the query strings and add a "IF EXI开发者_开发知识库STS" to the DROP TABLE option and that would solve everything.

Any ideas? Thank you.


Take advantage of MySQL Backup and Restore tools in dbForge Studio for MySQL. Turn the option 'Include IF EXISTS in DROP statement' on and you will get script like this:

--
-- Definition for database database1
--
DROP DATABASE IF EXISTS database1;
CREATE DATABASE database1
  CHARACTER SET latin1
  COLLATE latin1_swedish_ci;

-- 
-- Set default database
--

USE database1;

--
-- Definition for table dept
--
CREATE TABLE dept (
  id INT(11) NOT NULL AUTO_INCREMENT,
  dept_name VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 2
AVG_ROW_LENGTH = 16384
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

Command line is supported.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号