Mysql, command import/export table structure or data, mysql Export
In the command line, mysql DATA export has a very useful command mysqldump. Its parameters include the handler, which can be viewed as follows:
Mysqldump
Most commonly used:
Mysqldump-uroot-pmysql databasefoo table1 table2> foo. SQL
In this way, table1 and table2 of database databasefoo can be imported into foo in SQL format. in SQL, the-uroot parameter indicates that the user name used to access the database is root. If there is a password, the-p parameter must be added.
C: \ Users \ jack> mysqldump-uroot-pmysql sva_rec date_drv> e: \ date_drv. SQL
Mysql DATA import is also quite convenient, such:
Mysql-uroot databasefoo <foo. SQL
In this way, all data of foo. SQL can be imported into database databasefoo.
1. Export the entire database
Mysqldump-u username-p Password Database Name> exported file name
C: \ Users \ jack> mysqldump-uroot-pmysql sva_rec> e: \ sva_rec. SQL
2. Export a table, including the table structure and data
Mysqldump-u user name-p Password Database Name Table Name> exported file name
C: \ Users \ jack> mysqldump-uroot-pmysql sva_rec date_rec_drv> e: \ date_rec_drv. SQL
3. Export a database structure
C: \ Users \ jack> mysqldump-uroot-pmysql-d sva_rec> e: \ sva_rec. SQL
4. Export a table with only the table structure
Mysqldump-u username-p password-d database name Table Name> exported file name
C: \ Users \ jack> mysqldump-uroot-pmysql-d sva_rec date_rec_drv> e: \ date_rec_drv. SQL
5. Import the database
Common source commands
Go to the mysql Database Console,
For example, mysql-u root-p
Mysql> use Database
Then run the source command. The following parameter is the script file (for example,. SQL used here)
Mysql> source d: wcnc_db. SQL
====================
To export
If it's an entire DB, then:
$ mysqldump -u [uname] -p[pass] db_name > db_backup.sql
If it's all DBs, then:
$ mysqldump -u [uname] -p[pass] --all-databases > all_db_backup.sql
If it's specific tables within a DB, then:
$ mysqldump -u [uname] -p[pass] db_name table1 table2 > table_backup.sql
You can even go as far as auto-compressing the output using gzip (if your DB is very big ):
$ mysqldump -u [uname] -p[pass] db_name | gzip > db_backup.sql.gz
If you want to do this remotely and you have the access to the server in question, then the following wocould work (presuming the MySQL server is on port 3306 ):
$ mysqldump -P 3306 -h [ip_address] -u [uname] -p[pass] db_name > db_backup.sql
To import
Type the following command to import SQL data file:
$ mysql -u username -p -h localhost DATA-BASE-NAME < data.sql
In this example, import 'data. SQL 'file into 'blog' database usingSathishAs username:
$ mysql -u sat -p -h localhost blog < data.sql
If you have a dedicated database server, replace localhost hostname with actual server name or IP address as follows:
$ mysql -u username -p -h 202.54.1.10 databasename < data.sql
OR use hostname such as mysql. cyberciti. biz
$ mysql -u username -p -h mysql.cyberciti.biz database-name < data.sql
If you do not know the database name or database name is wrongly ded in SQL dump you can try out something as follows:
$ mysql -u username -p -h 202.54.1.10 < data.sql
Refer: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
If you want a GUI tool then you cocould probably use SQLyog