MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL TIMESTAMPDIFF in MySQL MySQL Syntax Checker Sudo MySQL Secure Installation

MySQL Export
2023-12-30 16:44:30

MySQL Export

What is MYSQL?

MySQL is a freely available database management system that is distributed, supported, and developed by Oracle Incorporation (INC). Tables and views are the two ways that data is stored in the relational database MySQL. Tables, stored procedures, functions, triggers, views, indexes, and even cursors are among the many database objects that it supports.

Any laptop or desktop computer may easily run the MySQL database server, even with a variety of servers or web apps installed. The MySQL database server is built to handle sizable databases that could hold information from numerous organizations. MySQL database servers support numerous web APIs and a broad range of functionalities. It functions incredibly effectively and safely and accesses a range of online databases. It guarantees constant connectivity to devices and servers.

Why do we need to export a MySQL database?

For backup and restore purposes, it is beneficial to export the MySQL database. Through the use of the backup, the restoration procedure aids in data recovery in an emergency. The export feature provides additional assistance with data movement across servers.

The MySQL database can be exported so that incremental backups, which are backups that consist of modifications made to the preceding backup, partial backups (which might be backups of specific tables or schemas), and compressed backups (which take up to 80% much less space), may be created. The statistics are also exportable to HTML, CSV, XML, and JSON forms.

Approaches for Exporting a MySQL Database

Data can be exported from a MySQL database using one of two primary methods:

  • Table Data Export
  • Result Data Export

Table Data Export

JSON and CSV file types can be exported from MySQL with the help of the table data export wizard. Column selections, datatype selections, encodings, and even separators can all be used when exporting data, and it can be done locally or remotely on servers. You can export the data from the table using the MySQL Workbench tool.

  • To export a table, open the MySQL database wizard and choose the desired table. Choose the table data export wizard option by right-clicking on the table name.
  • You can export a single column or the entire table by selecting it in the table data export tab.
  • Now specify the file format you want to use and the precise location where you want the data exported. Users have a choice between JSON and CSV.
  • To begin the procedure and to keep an eye on your progress at the same time, choose the next option.

Use the SELECT...INTO OUTFILE command to export a query result straight into a file on the server host. This is the simplest method of exporting table data into a text file.

Exporting Data with the SELECT ... INTO OUTFILE Statement

This statement's syntax consists of a normal SELECT query with an INTO OUTFILE filename at the end. The output format, by default, is the same as the one used with the LOAD DATA command. Thus, the tutorials_tbl table is exported as a tab-delimited, linefeed-terminated file into /tmp/tutorials.txt by the following statement.

mysql> SELECT * FROM tutorials_tbl
 -> INTO OUTFILE '/tmp/tutorials.txt';

With a variety of settings, you may modify the output format to specify how to quote and delimit records and columns. To export the tutorial_tbl table in a CSV file with lines that are terminated with CRLF, utilize the code provided.

mysql> SELECT * FROM password INTO OUTFILE '/tmp/tutorials.txt'
 -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
 -> LINES TERMINATED BY '\r\n';

The following are the characteristics of the SELECT... INTO OUTFILE:

  • Since the MySQL server directly generates the output file, the filename should specify the location on the server host where you wish the file to be written. The statement does not exist in a local form comparable to the local version of LOAD DATA.
  • To run the SELECT... INTO query, you need to have the MySQL FILE privilege.
  • The output file can't be pre-existing. This stops potentially significant files from being crushed by MySQL.
  • It is expected that you possess a login account on the server host or a method to obtain the file from that host. If not, you won't be able to benefit from the SELECT... INTO OUTFILE statement.
  • The file is generated with world-readable permissions under UNIX and is owned by the MySQL server. This implies that even while you can view the file, it might not be possible for you to remove it.

Exporting Tables as Raw Data

Tables and databases can be copied or backed up using the mysqldump utility. The table output can be written as a series of INSERT statements that reconstruct the records in the database or as a Raw Datafile.

In order to export a table as a data file, you need to give the location where you want the file to be written by the MySQL server with the --tab option.

For instance, execute the command as follows to export the tutorials_tbl table from the TUTORIALS database to a file in the /tmp directory.

$ mysqldump -u root -p --no-create-info \
 --tab=/tmp tutorials tutorials_tbl
password ******

Exporting Table Contents or Definitions in SQL Format

The following command can be used to export a table to a file in SQL format.

$ mysqldump -u root -p root tutorials_tbl > dump.txt
password ******

As seen below, this will generate a file with the content.

-- MySQL dump 8.23
--
-- Host: localhost Database: TUTORIALS
---------------------------------------------------------
-- Server version 3.23.58
--
-- Table structure for table `tutorials_tbl`
--
CREATE TABLE tutorials_tbl (
 tutorial_id int(15) NOT NULL auto_increment,
 tutorial_title varchar(150) NOT NULL default '',
 tutorial_author varchar(50) NOT NULL default '',
 submission_date date default NULL,
 PRIMARY KEY (tutorial_id),
 UNIQUE KEY AUTHOR_INDEX (tutorial_author)
) TYPE = MyISAM;
--
-- Dumping data for table `tutorials_tbl`
--
INSERT INTO tutorials_tbl
 VALUES (1,'MYSQL Tutorial','Hari','2023-11-14');
INSERT INTO tutorials_tbl
 VALUES (2,'Learn Java','Sanjeev','2023-11-10');
INSERT INTO tutorials_tbl
 VALUES (3,'Learn Python','John','2023-11-30');

Use the database name parameter after each table name if you want to dump several tables. The following code block illustrates how to dump a whole database: don't name any tables after the database.

$ mysqldump -u root -p root > database_dump.txt
password ******

Use this code to create a backup of every database that is reachable for your server.

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

MySQL 3.23.12 includes support for the --all-databases option. Put a database backup plan into practice this way.

Copying Tables or Databases to another Host

Use mysqldump with the database name and table name if you wish to copy tables or databases between MySQL servers.

Execute the subsequent command on the host of origin. The whole database will be dumped into the dump.txt file as a result.

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

As previously mentioned, you can replicate the entire database without utilizing a specific table name.

Now, run the following command to access the ftp dump.txt file on a different host. Please ensure that database_name has been created on the target server before executing this operation.

$ mysql -u root -p database_name < dump.txt
password *****

An alternative method to accomplish this without utilizing a middle file is to transmit the mysqldump output to the remote MySQL server over the network directly. Use the command that follows if you are able to establish a connection to both servers from the host hosting the source database (be sure you have access to both servers).

$ mysqldump -u root -p database_name \
 | mysql -h other-host.com database_name

The first portion of the mysqldump command establishes a connection to the local server and then outputs the dump to the pipe. The remaining portion of the command accesses the remote MySQL server on other-host.com. Sending each statement to the other-host.com server reads the pipe for input.

Result Data Export

This method allows you to export the result set that you get from running a query in the JSON, CSV, HTML, and XML file formats.

  • Use a MySQL database to execute the query. An output in a grid format will result from this.
  • After choosing the query's output, select Export/Import. Now specify the format in which you wish to export the data as well as the file name.

Methods to Export a MySQL Database

Exporting data from a MySQL database can be done in several ways:

  • Using phpMyAdmin
  • Using MySQL Export Database Command Line
  • Using MySQL Workbench

Using phpMyAdmin

It is an application designed to control web servers. You may run MySQL queries, modify collations, optimize the queries, and carry out several MySQL tasks using this program. One of the programs that is most frequently utilized to export MySQL databases is phpMyAdmin.

The phpMyAdmin utility can be used to execute a MySQL data export in the manner described below:

  • Logging In To cPanel
  • Exporting Using cPanel

Logging In To cPanel

  • Choose the phpMyAdmin option from the area of the database after logging into cPanel.
  • Click the Export tab after selecting the database that needs to be exported.

Exporting Using cPanel

  • You can choose to export a specific column or the complete table from the export tab. Use the select all option to choose every entry in the database.
  • After choosing the columns you want, select the reduction method to zipped and click the save as file option. As a result, the file size will be smaller, and the export process will go much more smoothly.
  • To export the file to your system, click the go button now.

Using MySQL Export Database Command Line

Exporting a MySQL database using the command line is a useful but somewhat complicated method. Large and small databases can both be used. The mysqldump program requires users to write some special code in order to export data.

The Unix-based command line interface known as SSH (Secure Shell) offers a safe method of gaining access to both local and distant systems. Despite being compatible with Windows and MAC OS, SSH uses commands from the Linux operating system. Because they encrypt passwords and employ certificates for server and client machine authentication, these commands are extremely secure.

A MySQL Export Database Command Line can be executed by following these steps:

  • Logging in to the Server
  • Using the Command Prompt
  • Exporting the Database

Logging in to the Server

The command to log in using SSH is as follows:

ssh root @ouripaddress

Maintaining the username root makes it easier to read because you have to log in as the root user. Press enter to continue after making sure you have entered the right IP address.

ssh root 123.123.123.123 # Here 123.123.123.123 is an example IP address.

In order to connect, enter the password now.

Using the Command Prompt

The directory to which the user has permission to write can be found by opening the command prompt and using the cd command as follows:

cd /hp/hhh/MYSQLDB.com/tables # Here, you need to specify the path after cd.

Exporting the Database

To export the database, use the mysqldump program. The following is how the command is used:

$ mysqldump -u USER_NAME -p DB_NAME >file_name.format

In order to export the records, the person must provide the report name and format they want to make use of.

Advantages of Using MySQL Export Database Command Line Method:

  • The MySQL Export Database Command Line executes incredibly quickly.
  • It is entirely dependable when SSH (Secure Shell) is used.
  • Without sacrificing speed, the MySQL Export Database Command Line technique functions flawlessly on databases of any size.
  • It is not necessary to have access to a remote web server.

Limitations of Using MySQL Export Database Command Line Method:

  • You need to have access to SSH.
  • When the mysqldump tool is used with large databases, it frequently locks people out for extended periods of time.
  • For those who are not experienced with coding, writing the code for the MySQL Export Database Command Line on a regular basis can take time and effort.
  • Real-world applications of the MySQL Export Database Command Line method necessitate the deployment of a sizable staff in order to create and manage these pipelines and guarantee reliable, daily operations.

Using MySQL Workbench

Here, we will use MySQL Workbench to export databases, including tables. Please enter the username's password into the workbench and open it.

  • Step 1: Clicking the server in the Menu bar is the first step. Click the Data Export option, as displayed on this screen, when a popup window displays. Data export settings and options will be displayed in a new window.
MySQL EXPORT
  • Step 2: Select the database you desire. Additionally, matching tables will be shown in the window's left area. Selecting the mytestdb database will be our next step. For the database to be included in the Export file, we can also select several database checkboxes. We can choose more than one table similarly.
MySQL EXPORT
  • Step 3: Once the database has been chosen, along with every table, navigate to the drop-down menu and pick an option from the list.
    • Dump Data and Structure: Both the data rows and the table structure will be saved.
    • Dump Data Only: The tables' inserted rows will be the only ones saved.
    • Dump Structure Only: Only the table structure—which consists of our defined data types and database columns—will be saved.
  • Step 4: There are two radio buttons in the Export option; they are described below.
    • Export to Dump Project Folder: All tables will be saved as distinct SQL files in a single folder upon export to the "Dump Project Folder." When importing or restoring the export file table by table, it will be helpful.
    • Export to Self-Contained File: One SQL file, including all of the databases and tables, will be produced. If you want to use a single SQL file to import all the databases, tables, and data rows, this is a viable solution. We are able to choose the export route of our choosing.
  • Step 5: The progress meter and log will appear when you click the Start Export button. We may now check the export files within our system's Document folder.
MySQL EXPORT