csdnceshi66
必承其重 | 欲带皇冠
采纳率0%
2013-07-16 00:43

如何使用 MySQL 中的命令行导入 SQL 文件?

已采纳

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line.

I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and I tried this command

database_name < file.sql

It is not working I get syntax errors.

  • How can I import this file without a problem?
  • Do I need to create a database first?

转载于:https://stackoverflow.com/questions/17666249/how-to-import-an-sql-file-using-the-command-line-in-mysql

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

30条回答

  • weixin_41568127 ?yb? 8年前

    Try:

    mysql -u username -p database_name < file.sql
    

    Check MySQL Options.

    Note-1: It is better to use the full path of the SQL file file.sql.

    Note-2: Use -R and --triggers to keep the routines and triggers of original database. They are not copied by default.

    点赞 24 评论 复制链接分享
  • csdnceshi62 csdnceshi62 7年前

    Regarding the time taken for importing huge files: most importantly, it takes more time because the default setting of MySQL is autocommit = true. You must set that off before importing your file and then check how import works like a gem.

    You just need to do the following thing:

    mysql> use db_name;
    
    mysql> SET autocommit=0 ; source the_sql_file.sql ; COMMIT ;
    
    点赞 26 评论 复制链接分享
  • csdnceshi70 笑故挽风 8年前

    A common use of mysqldump is for making a backup of an entire database:

    shell> mysqldump db_name > backup-file.sql
    

    You can load the dump file back into the server like this:

    UNIX

    shell> mysql db_name < backup-file.sql
    

    The same in Windows command prompt:

    mysql -p -u [user] [database] < backup-file.sql
    

    PowerShell

    C:\> cmd.exe /c "mysql -u root -p db_name < backup-file.sql"
    

    MySQL command line

    mysql> use db_name;
    mysql> source backup-file.sql;
    
    点赞 20 评论 复制链接分享
  • csdnceshi62 csdnceshi62 4年前

    Similarly to https://stackoverflow.com/a/17666285/1888983
    Key differences for me:

    1. The database has to exist first
    2. No space between -p and the password

    shell> mysql -u root -ppassword #note: no space between -p and password
    mysql> CREATE DATABASE databasename;
    mysql> using databasename;
    mysql> source /path/to/backup.sql
    

    Running fedora 26 with MariaDB.

    点赞 10 评论 复制链接分享
  • csdnceshi67 bug^君 7年前

    Import a database

    1. Go to drive:

      command: d:
      
    2. MySQL login

      command: c:\xampp\mysql\bin\mysql -u root -p
      
    3. It will ask for pwd. Enter it:

      pwd
      
    4. Select the database

      use DbName;
      
    5. Provide the file name

      \.DbName.sql
      
    点赞 10 评论 复制链接分享
  • csdnceshi55 ~Onlooker 6年前

    To import a single database, use the following command.

    mysql -u username -p password dbname < dump.sql
    

    To import multiple database dumps, use the following command.

    mysql -u username -p password < dump.sql
    
    点赞 10 评论 复制链接分享
  • csdnceshi78 程序go 7年前

    Go to the directory where you have the MySQL executable. -u for username and -p to prompt for the password:

    C:\xampp\mysql\bin>mysql -u username -ppassword databasename < C:\file.sql
    
    点赞 10 评论 复制链接分享
  • weixin_41568184 叼花硬汉 4年前

    I'm using Windows 10 with Powershell 5 and I found almost all "unix-like" solutions not working for me.

    > mysql -u[username] [database-name] < my-database.sql
    At line:1 char:31
    + mysql -u[username] [database-name] < my-database.sql
    +                               ~
    The '<' operator is reserved for future use.
        + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : RedirectionNotSupported
    

    I ends up using this command.

    > type my-database.sql | mysql -u[username] -h[localhost] -p [database-name]
    

    and it works perfectly, hopefully it helps.

    Thanks to @Francesco Casula's answer btw.

    点赞 9 评论 复制链接分享
  • csdnceshi62 csdnceshi62 5年前

    I kept running into the problem where the database wasn't created.

    I fixed it like this

    mysql -u root -e "CREATE DATABASE db_name"
    mysql db_name --force < import_script.sql
    
    点赞 9 评论 复制链接分享
  • csdnceshi80 胖鸭 4年前
    mysql -u root -p password -D database_name << import.sql
    

    Use mysql help for details mysql --help

    I think these will be useful options in our context

    [~]$ mysql --help
    mysql  Ver 14.14 Distrib 5.7.20, for osx10.12 (x86_64) using  EditLine wrapper
    Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.                                                                                                                                         
    Usage: mysql [OPTIONS] [database]
      -?, --help          Display this help and exit.
      -I, --help          Synonym for -?
      --bind-address=name IP address to bind to.
      -D, --database=name Database to use.
      --delimiter=name    Delimiter to be used.
      --default-character-set=name Set the default character set.
      -f, --force         Continue even if we get an SQL error.
      -p, --password[=name] Password to use when connecting to server.
      -h, --host=name     Connect to host.
      -P, --port=#        Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).
      --protocol=name     The protocol to use for connection (tcp, socket, pipe,
      -s, --silent        Be more silent. Print results with a tab as separator, each row on new line.
      -v, --verbose       Write more. (-v -v -v gives the table output format).
      -V, --version       Output version information and exit.
      -w, --wait          Wait and retry if connection is down.
    

    what is fun, if we are importing a large database and not having a progress bar. Use Pipe Viewer and see the data transfer through the pipe

    For Mac, brew install pv .For Debian/Ubuntu, apt-get install pv. Others, refer http://www.ivarch.com/programs/pv.shtml

    pv import.sql | mysql -u root -p password -D database_name
    
    1.45GiB 1:50:07 [339.0KiB/s]   [=============>      ] 14% ETA 11:09:36
    1.46GiB 1:50:14 [ 246KiB/s]     [=============>      ] 14% ETA 11:09:15
    1.47GiB 1:53:00 [ 385KiB/s]     [=============>      ] 14% ETA 11:05:36
    
    点赞 9 评论 复制链接分享
  • weixin_41568196 撒拉嘿哟木头 6年前

    For importing multiple SQL files at one time, use this:

    # Unix-based solution
    for i in *.sql;do mysql -u root -pPassword DataBase < $i;done
    

    For simple importing:

    # Unix-based solution
    mysql -u root -pPassword DataBase < data.sql
    

    For WAMP:

    #mysqlVersion replace with your own version
    C:\wamp\bin\mysql\mysqlVersion\bin\mysql.exe -u root -pPassword DataBase < data.sql
    

    For XAMPP:

    C:\xampp\mysql\bin\mysql -u root -pPassword DataBase < data.sql
    
    点赞 9 评论 复制链接分享
  • csdnceshi80 胖鸭 3年前

    While most answers here just mention the simple command

    mysql -u database_user -p [db_name] < database_file.sql

    today it's quite common that databases and tables have utf8-collation where this command is not sufficient. Having utf8-collation in the exported tables it's required to use this command:

    mysql -u database_user -p --default-character-set=utf8 [db_name] < database_file.sql

    Surley this works for other charsets too, how to show the right notation can be seen here:

    https://dev.mysql.com/doc/refman/5.7/en/show-collation.html

    One comment mentioned also that if a database never exists an empty database had to be created first. This might be right in some cases, but depends on the export file. If the exported file includes already the command to create the database then the database never has to be created in a separated step, which even could cause an error on import. So on import it's advisable to have a look first in the file to know which commands are included there, on export it's advisable note the settings, especially if the file is very large and hard to read in an editor.

    There are still more parameters for the command which are listed and explained here:

    https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html

    If you use another database-version consider searching for the corresponding version of the manual too. The mentioned links refer to MySQL version 5.7.

    点赞 9 评论 复制链接分享
  • csdnceshi73 喵-见缝插针 4年前

    If you already have the database use the following to import the dump or the sql file

    mysql -u username -p database_name < file.sql
    

    if you don't you need to create the relevant database(empty) in MySQL, for that first log on to the MySQL console by running the following command in terminal or in cmd

    mysql -u userName -p;
    

    and when prompted provide the password.

    Next create a database and use it

    mysql>create database yourDatabaseName;
    mysql>use yourDatabaseName;
    

    Then import the sql or the dump file to the database from

    mysql> source pathToYourSQLFile;
    

    Note: if your terminal is not in the location where the dump or sql file exists, use the relative path in above.

    点赞 8 评论 复制链接分享
  • weixin_41568196 撒拉嘿哟木头 7年前

    Add the --force option:

    mysql -u username -p database_name --force < file.sql
    
    点赞 8 评论 复制链接分享
  • csdnceshi78 程序go 4年前

    Providing credentials on the command line is not a good idea. The above answers are great, but neglect to mention

    mysql --defaults-extra-file=etc/myhost.cnf database_name < file.sql
    

    Where etc/myhost.cnf is a file that contains host, user, password, and you avoid exposing the password on the command line. Here is a sample,

    [client]
    host=hostname.domainname
    user=dbusername
    password=dbpassword
    
    点赞 7 评论 复制链接分享
  • csdnceshi76 斗士狗 7年前
    mysql --user=[user] --password=[password] [database] < news_ml_all.sql
    
    点赞 7 评论 复制链接分享
  • weixin_41568127 ?yb? 7年前

    Sometimes the port defined as well as the server IP address of that database also matters...

    mysql -u user -p user -h <Server IP> -P<port> (DBNAME) < DB.sql 
    
    点赞 7 评论 复制链接分享
  • csdnceshi58 Didn"t forge 6年前

    You do not need to specify the name of the database on the command line if the .sql file contains CREATE DATABASE IF NOT EXISTS db_name and USE db_name statements.

    Just make sure you are connecting with a user that has the permissions to create the database, if the database mentioned in the .sql file does not exist.

    点赞 6 评论 复制链接分享
  • csdnceshi71 Memor.の 5年前

    For information I just had default root + withoutpassword, it didn't works with all above answers.

    • I created a new user with all privileges and a password. It works.

    • -ppassword WITHOUT SPACE.

    点赞 6 评论 复制链接分享
  • csdnceshi73 喵-见缝插针 7年前

    For backup purposes, make a BAT file and run this BAT file using Task Scheduler. It will take a backup of the database; just copy the following line and paste in Notepad and then save the .bat file, and run it on your system.

    @echo off
    for /f "tokens=1" %%i in ('date /t') do set DATE_DOW=%%i
    for /f "tokens=2" %%i in ('date /t') do set DATE_DAY=%%i
    for /f %%i in ('echo %date_day:/=-%') do set DATE_DAY=%%i
    for /f %%i in ('time /t') do set DATE_TIME=%%i
    for /f %%i in ('echo %date_time::=-%') do set DATE_TIME=%%i
    
    "C:\Program Files\MySQL\mysql server 5.5\bin\mysqldump" -u username -ppassword mysql>C:/%DATE_DAY%_%DATE_TIME%_database.sql
    
    点赞 5 评论 复制链接分享
  • csdnceshi60 ℡Wang Yan 6年前

    The following command works for me from the command line (cmd) on Windows 7 on WAMP.

    d:/wamp/bin/mysql/mysql5.6.17/bin/mysql.exe -u root -p db_name < database.sql
    
    点赞 5 评论 复制链接分享
  • csdnceshi57 perhaps? 5年前

    I think it's worth mentioning that you can also load a gzipped (compressed) file with zcat like shown below:

    zcat database_file.sql.gz | mysql -u username -p -h localhost database_name
    
    点赞 5 评论 复制链接分享
  • csdnceshi59 ℙℕℤℝ 6年前
    1. Open the MySQL command line
    2. Type the path of your mysql bin directory and press Enter
    3. Paste your SQL file inside the bin folder of mysql server.
    4. Create a database in MySQL.
    5. Use that particular database where you want to import the SQL file.
    6. Type source databasefilename.sql and Enter
    7. Your SQL file upload successfully.
    点赞 5 评论 复制链接分享
  • csdnceshi69 YaoRaoLov 8年前

    Go to the directory where you have MySQL.

     c:\mysql\bin\> mysql -u username -p password database_name <
     filename.sql
    

    Also to dump all databases, use the -all-databases option, and no databases’ name needs to be specified anymore.

    mysqldump -u username -ppassword –all-databases > dump.sql
    

    Or you can use some GUI clients like SQLyog to do this.

    点赞 4 评论 复制链接分享
  • csdnceshi68 local-host 7年前

    We can use this command to import SQL from command line:

    mysql -u username -p password db_name < file.sql
    

    For example, if the username is root and password is password. And you have a database name as bank and the SQL file is bank.sql. Then, simply do like this:

    mysql -u root -p password bank < bank.sql
    

    Remember where your SQL file is. If your SQL file is in the Desktop folder/directory then go the desktop directory and enter the command like this:

    ~ ? cd Desktop
    ~/Desktop ? mysql -u root -p password bank < bank.sql
    

    And if your are in the Project directory and your SQL file is in the Desktop directory. If you want to access it from the Project directory then you can do like this:

    ~/Project ? mysql -u root -p password bank < ~/Desktop/bank.sql
    
    点赞 4 评论 复制链接分享
  • csdnceshi69 YaoRaoLov 5年前

    A solution that worked for me is below:

    Use your_database_name;
    SOURCE path_to_db_sql_file_on_your_local;
    
    点赞 3 评论 复制链接分享
  • csdnceshi64 游.程 7年前

    I thought it could be useful for those who are using Mac OS X:

    /Applications/xampp/xamppfiles/bin/mysql -u root -p database < database.sql
    

    Replace xampp with mamp or other web servers.

    点赞 3 评论 复制链接分享
  • csdnceshi71 Memor.の 4年前

    To dump a database into a SQL file use the following command

    mysqldump -u username -p database_name > database_name.sql
    

    To import a SQL file into a database (make sure you are in the same directory as the SQL file or supply the full path to the file)

    mysql u -username -p database_name < database_name.sql
    
    点赞 2 评论 复制链接分享
  • csdnceshi59 ℙℕℤℝ 6年前

    The following steps help to upload file.sql to the MySQL database.

    Step 1: Upload file.sql.zip to any directory and unzip there
    Note: sudo apt-get install unzip : sudo apt-get unzip file.sql.zip
    Step 2: Now navigate to that directory. Example: cd /var/www/html

    Step 3: mysql -u username -p database-name < file.sql
    Enter the password and wait till uploading is completed.

    点赞 2 评论 复制链接分享
  • csdnceshi60 ℡Wang Yan 5年前

    Among all the answers, for the problem above, this is the best one:

     mysql> use db_name;
     mysql> source file_name.sql;
    
    点赞 1 评论 复制链接分享

相关推荐