linux mysqldump导入导出数据库

桃扇骨 2022-09-20 15:16 224阅读 0赞

1.导出整个数据库  
mysqldump -u 用户名 -p 数据库名 > 导出的文件名  
mysqldump -u root  -p  lossr > lossr.sql

2.导入数据库  
常用source 命令  
进入mysql数据库控制台，  
如mysql -u root -p

mysql>use 数据库

然后使用source命令，后面参数为脚本文件（如这里用到的.sql）  
mysql>source /root/lossr.net.sql

How do I import data stored in a .sql file created by mysqldump command on Linux or UNIX like operating systems? How do I restore mysql database backup using bash shell based command line option?

<table> 
 <tbody> 
  <tr> 
   <th>Tutorial details</th> 
   <th>&nbsp;</th> 
  </tr> 
  <tr> 
   <td>Difficulty</td> 
   <td><a title="See all Easy Linux / Unix System Administrator Tutorials" href="http://www.cyberciti.biz/faq/tutorial-difficulty-level/easy/" rel="nofollow">Easy</a> (<a title="See all Easy Linux / Unix System Administrator Tutorials via rss feed" href="http://www.cyberciti.biz/faq/tutorial-difficulty-level/easy/feed/" rel="nofollow">rss</a>)</td> 
  </tr> 
  <tr> 
   <td>Root privileges</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td>Requirements</td> 
   <td>mysql client</td> 
  </tr> 
  <tr> 
   <td>Estimated completion time</td> 
   <td>2m</td> 
  </tr> 
 </tbody> 
</table>

The mysqldump command line client is a backup program and it can be used to dump a database (backup database) or a collection of databases for backup or transfer to another SQL server. The dump typically contains SQL statements to create the table, populate it, or both. A common use of mysqldump is for making a backup of an entire database:

mysqldump db_name > backup-db_name.sql
    mysqldump -u userNameHere db_name > backup-db_name.sql
    mysqldump -u userNameHere -p db_name > backup-db_name.sql

You can import such a file using mysql command.

## Syntax ##

The syntax is as follows to import the data created by mysqldump command:

mysql -u {
       DB-USER-NAME} -p {
       DB-NAME} < {
       db.file-name.sql}
    mysql -u {
       DB-USER-NAME} -h {
       MySQL-SERVER-HOST-NAME} -p {
       DB-NAME} < {
       backup.db.file-name.sql}
    mysql -e "source /path/to/backup-db.sql" db_name_here
    mysql -u DB_USER_NAME -p -e "source /path/to/backup-db.sql" db_name_here

### Example: Load the dump file back into the server ###

In this example import (restore) a file called sales.sql for salesdb1 user and sales database, enter:  
`$ mysql -u salesdb1 -p sales < sales.sql`  
If database salesdb1 does not exists, first create it and than import it as follows:  
`$ mysql -u root -p -e 'create database salesdb1;' $ mysql -u salesdb1 -p sales < sales.sql`  
You can also use the following syntax to load the dump file back into the server:

##############################################
    ## DB Name: sales
    ## DB User: salesdb1
    ## DB backup file (import file name): /root/sales.sql 
    ##############################################
    mysql -e "source /root/sales.sql" sales
    mysql -u salesdb1 -p  -e "source /root/sales.sql" sales

Please note that a large size .sql database import may take some time to finish the job. Never store import file in /tmp or import .sql files from /tmp/ folder.