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?
Tutorial details | |
---|---|
Difficulty | Easy (rss) |
Root privileges | No |
Requirements | mysql client |
Estimated completion time | 2m |
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.