Mysql, command import/export table structure or data, mysql Export

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