I have a large database dump.sql file I am importing from the command line in linux. The .sql dump creates a database named "database_name". I want to import the database from this .sql file but I want to force it to a database with a different name, as the script currently overwrites "database_name" and "database_name" already exists and has data I can't overwrite.
我有一个大型数据库dump.sql文件，我从linux中的命令行导入。 .sql转储创建名为“database_name”的数据库。我想从这个.sql文件导入数据库，但我想强制它到一个具有不同名称的数据库，因为脚本当前覆盖“database_name”和“database_name”已经存在并且具有我无法覆盖的数据。
Is the best option to find and replace within the .sql file? What is the best method for that since the file is 50mb. I can't simply file_get_contents() on that shiz? Can I?
是.sql文件中查找和替换的最佳选择？由于文件是50mb，最好的方法是什么。我不能简单地在那个shiz上使用file_get_contents（）吗？我可以吗？
Below are the lines I would have to replace in the .sql file:
下面是我必须在.sql文件中替换的行：
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `database_name` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `database_name`;
4 个解决方案
#1
9
When dumping the database with mysqldump, use the option --no-create-db. This will suspress the CREATE DATABASE statement in your dump file.
Then restore the database withmysql -h <host> -u <user> -p <databaseName> < dump.sql
In this way you can restore your data in whatever database you like (But that database has to exist!)
使用mysqldump转储数据库时，请使用选项--no-create-db。这将在您的转储文件中隐藏CREATE DATABASE语句。然后使用mysql -h
#2
7
You have to replace the database_name in the .sql file.
您必须替换.sql文件中的database_name。
You can do that from the shell with sed 's/database_name/new_database_name/' dumpFile.sql > newDumpFile.sql
您可以使用sed的/ database_name / new_database_name /'dumpFile.sql> newDumpFile.sql从shell中执行此操作
this is the correct answer to my question but was provided by 'fab' in the comments of another answer.
这是我的问题的正确答案，但是在另一个答案的评论中由'fab'提供。
NOTE: As @Diego pointed out in the comments, if the string 'database_name' shows up anywhere else in this .sql dump, it will be replaced there as well. You may want to view the contents of the .sql file using less file_name.sql and then be more explicit with your find/replace.
注意：正如@Diego在注释中指出的那样，如果字符串'database_name'显示在此.sql转储中的任何其他位置，它也将在那里被替换。您可能希望使用较少的file_name.sql查看.sql文件的内容，然后使用find / replace更明确地查看.sql文件的内容。
#3
0
The only reliable way is to open the file, find the following line (about 10 to 15 lines from start):
唯一可靠的方法是打开文件，找到以下行（从开始大约10到15行）：
CREATE DATABASE /!32312 IF NOT EXISTS/ old_database_name ...
CREATE DATABASE /！32312 IF NOT EXISTS / old_database_name ...
USE old_database_name;
使用old_database_name;
and replace old_database_name with the new name in both places. I just did this on a 5.9 GB file with vim. It took about 15 sec. to open the file, 3 sec. to edit the line and 30 sec. to save the file. Not sure any other text editor allows you do do this!
并在两个地方用新名称替换old_database_name。我刚用vim的5.9 GB文件做了这个。花了大约15秒。打开文件，3秒。编辑线和30秒。保存文件。不确定任何其他文本编辑器允许您这样做！
#4
0
This is an old question but I was faced with the exact same problem today. Here is how I solved it:
这是一个古老的问题，但我今天面临着同样的问题。这是我解决它的方式：
pv 20171212.dump.bz2 | bunzip2 | sed -e '/^\(CREATE DATABASE\|USE\|.*DROP DATABASE\).*`<old db name>`/d' | mysql <new db name>
pv is just there to show a progress bar. The line could have been written as
pv就是为了显示进度条。该行可以写成
bunzip2 20171212.dump.bz2 | sed -e '/^\(CREATE DATABASE\|USE\|.*DROP DATABASE\).*`<old db name>`/d' | mysql <new db name>
sed removes the lines preventing the database name change without altering any text occurring into the dump file.
sed删除阻止数据库名称更改的行而不更改转储文件中出现的任何文本。
#1
9
When dumping the database with mysqldump, use the option --no-create-db. This will suspress the CREATE DATABASE statement in your dump file.
Then restore the database withmysql -h <host> -u <user> -p <databaseName> < dump.sql
In this way you can restore your data in whatever database you like (But that database has to exist!)
使用mysqldump转储数据库时，请使用选项--no-create-db。这将在您的转储文件中隐藏CREATE DATABASE语句。然后使用mysql -h
#2
7
You have to replace the database_name in the .sql file.
您必须替换.sql文件中的database_name。
You can do that from the shell with sed 's/database_name/new_database_name/' dumpFile.sql > newDumpFile.sql
您可以使用sed的/ database_name / new_database_name /'dumpFile.sql> newDumpFile.sql从shell中执行此操作
this is the correct answer to my question but was provided by 'fab' in the comments of another answer.
这是我的问题的正确答案，但是在另一个答案的评论中由'fab'提供。
NOTE: As @Diego pointed out in the comments, if the string 'database_name' shows up anywhere else in this .sql dump, it will be replaced there as well. You may want to view the contents of the .sql file using less file_name.sql and then be more explicit with your find/replace.
注意：正如@Diego在注释中指出的那样，如果字符串'database_name'显示在此.sql转储中的任何其他位置，它也将在那里被替换。您可能希望使用较少的file_name.sql查看.sql文件的内容，然后使用find / replace更明确地查看.sql文件的内容。
#3
0
The only reliable way is to open the file, find the following line (about 10 to 15 lines from start):
唯一可靠的方法是打开文件，找到以下行（从开始大约10到15行）：
CREATE DATABASE /!32312 IF NOT EXISTS/ old_database_name ...
CREATE DATABASE /！32312 IF NOT EXISTS / old_database_name ...
USE old_database_name;
使用old_database_name;
and replace old_database_name with the new name in both places. I just did this on a 5.9 GB file with vim. It took about 15 sec. to open the file, 3 sec. to edit the line and 30 sec. to save the file. Not sure any other text editor allows you do do this!
并在两个地方用新名称替换old_database_name。我刚用vim的5.9 GB文件做了这个。花了大约15秒。打开文件，3秒。编辑线和30秒。保存文件。不确定任何其他文本编辑器允许您这样做！
#4
0
This is an old question but I was faced with the exact same problem today. Here is how I solved it:
这是一个古老的问题，但我今天面临着同样的问题。这是我解决它的方式：
pv 20171212.dump.bz2 | bunzip2 | sed -e '/^\(CREATE DATABASE\|USE\|.*DROP DATABASE\).*`<old db name>`/d' | mysql <new db name>
pv is just there to show a progress bar. The line could have been written as
pv就是为了显示进度条。该行可以写成
bunzip2 20171212.dump.bz2 | sed -e '/^\(CREATE DATABASE\|USE\|.*DROP DATABASE\).*`<old db name>`/d' | mysql <new db name>
sed removes the lines preventing the database name change without altering any text occurring into the dump file.
sed删除阻止数据库名称更改的行而不更改转储文件中出现的任何文本。