![]() ![]() You can copy complete database without using a particular table name as explained above. $ mysqldump -u root -p database_name table_name > dump.txt This will dump the complete database into dump.txt file. ![]() Run the following command at the source host. If you want to copy tables or databases from one MySQL server to another, then use the mysqldump with database name and table name. Copying Tables or Databases to Another Host This method can be used to implement a database backup strategy. The -all-databases option is available in the MySQL 3.23.12 version. $ mysqldump -u root -p -all-databases > database_dump.txt To back up all the databases available on your host, use the following code. $ mysqldump -u root -p TUTORIALS > database_dump.txt To dump an entire database, don't name any tables after the database as shown in the following code block. To dump multiple tables, name them all followed by the database name argument. UNIQUE KEY AUTHOR_INDEX (tutorial_author) Tutorial_author varchar(40) NOT NULL default '', Tutorial_title varchar(100) NOT NULL default '', Tutorial_id int(11) NOT NULL auto_increment, Table structure for table `tutorials_tbl` This will a create file having content as shown below. $ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt To export a table in SQL format to a file, use the command shown below. $ mysqldump -u root -p -no-create-info \Įxporting Table Contents or Definitions in SQL Format To dump a table as a datafile, you must specify a -tab option that indicates the directory, where you want the MySQL server to write the file.įor example, to dump the tutorials_tbl table from the TUTORIALS database to a file in the /tmp directory, use a command as shown below. It can write the table output either as a Raw Datafile or as a set of INSERT statements that recreate the records in the table. The mysqldump program is used to copy or back up tables and databases. This means that although you will be able to read the file, you may not be able to delete it. Under UNIX, the file is created world readable and is owned by the MySQL server. INTO OUTFILE command will most likely be of no value to you. You should have a login account on the server host or some way to retrieve the file from that host. This prevents MySQL from clobbering files that may be important. You must have the MySQL FILE privilege to execute the SELECT. There is no LOCAL version of the statement analogous to the LOCAL version of LOAD DATA. The output file is created directly by the MySQL server, so the filename should indicate where you want the file to be written on the server host. INTO OUTFILE has the following properties − ![]() > FIELDS TERMINATED BY ',' ENCLOSED BY '"' Mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt' To export the tutorial_tbl table in a CSV format with CRLF-terminated lines, use the following code. You can change the output format using various options to indicate how to quote and delimit columns and records. So, the following statement exports the tutorials_tbl table into /tmp/tutorials.txt as a tab-delimited, linefeed-terminated file. The default output format is the same as it is for the LOAD DATA command. The syntax for this statement combines a regular SELECT command with INTO OUTFILE filename at the end. The simplest way of exporting a table data into a text file is by using the SELECT.INTO OUTFILE statement that exports a query result directly into a file on the server host. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |