{"id":4210,"date":"2023-05-09T15:51:18","date_gmt":"2023-05-09T15:51:18","guid":{"rendered":"https:\/\/blog.osmosys.co\/?p=4210"},"modified":"2024-02-02T04:57:13","modified_gmt":"2024-02-02T04:57:13","slug":"exporting-mysql-data-to-csv-for-local-transfer","status":"publish","type":"post","link":"https:\/\/staging.osmosys.co\/uk\/exporting-mysql-data-to-csv-for-local-transfer\/","title":{"rendered":"Exporting MySQL Data to CSV for Local Transfer"},"content":{"rendered":"<div id=\"bsf_rt_marker\"><\/div>\n<p>If you&#8217;re a developer working with MySQL databases, you might need to export data from your database and transfer it to your local system. In this tutorial, we&#8217;ll show you how to export data from a MySQL database to a CSV file and transfer it to your local system using SSH.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_1 counter-hierarchy ez-toc-counter ez-toc-white ez-toc-container-direction\">\r\n<div class=\"ez-toc-title-container\">\r\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\r\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\r\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/staging.osmosys.co\/uk\/exporting-mysql-data-to-csv-for-local-transfer\/#Step_1_Login_to_the_Server\" >Step 1: Login to the Server<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/staging.osmosys.co\/uk\/exporting-mysql-data-to-csv-for-local-transfer\/#Step_2_Export_Data_to_CSV\" >Step 2: Export Data to CSV<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/staging.osmosys.co\/uk\/exporting-mysql-data-to-csv-for-local-transfer\/#Step_3_Transfer_the_File_to_Local_System\" >Step 3: Transfer the File to Local System<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/staging.osmosys.co\/uk\/exporting-mysql-data-to-csv-for-local-transfer\/#References\" >References<\/a><\/li><\/ul><\/nav><\/div>\r\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Step_1_Login_to_the_Server\"><\/span>Step 1: Login to the Server<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>First, you need to login to the server where your MySQL database is located. Open your terminal and use the following command to log in:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ssh &lt;user&gt;@&lt;ip&gt; -p &lt;port&gt;<\/pre>\n\n\n\n<p>For example:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ssh test_user@172.0.0.2 -p 2505<\/pre>\n\n\n\n<p>Note: There are multiple ways to connect to any server from the terminal. We&#8217;ve used the most simple one in this tutorial.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Step_2_Export_Data_to_CSV\"><\/span>Step 2: Export Data to CSV<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Once you&#8217;ve logged in, you can use the following command to export data from your MySQL database to a CSV file:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">mysql -u &lt;user_name&gt; &lt;database_name&gt; -p -B -e \"&lt;Query&gt;\" | sed \"s\/'\/\\'\/;s\/\\t\/\\\",\\\"\/g;s\/^\/\\\"\/;s\/$\/\\\"\/;s\/\\n\/\/g\" &gt; &lt;~\/path-to-folder\/file-name.csv&gt;<\/pre>\n\n\n\n<p>Here&#8217;s an example:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">mysql -u test-user test_database -p -B -e \"SELECT id, job_id, t_token, t_event_id, t_event_type, t_token, t_submitted_at, e_status_code, e_message, e_response, status, created_on FROM job_logs where status = 'Success' order by created_on desc;\" | sed \"s\/'\/\\'\/;s\/\\t\/\\\",\\\"\/g;s\/^\/\\\"\/;s\/$\/\\\"\/;s\/\\n\/\/g\" &gt; ~\/backup\/logs.csv<\/pre>\n\n\n\n<p>This command exports data from the <code>job_logs<\/code> table with the status &#8220;Success&#8221; and orders it by <code>created_on<\/code> in descending order. The data is exported to a CSV file named <code>logs.csv<\/code> in the <code>backup<\/code> directory. Here is the breakdown of the command used &#8211;<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>mysql<\/code>: This command is used to access MySQL and execute queries. <\/li>\n\n\n\n<li><code>-u &lt;user_name&gt;<\/code>: This parameter specifies the MySQL username used for authentication.<\/li>\n\n\n\n<li><code>&lt;database_name&gt;<\/code>: This parameter specifies the name of the MySQL database to be queried.<\/li>\n\n\n\n<li><code>-p<\/code>: This parameter prompts for a password for the MySQL user specified with <code>-u<\/code>.<\/li>\n\n\n\n<li><code>-B<\/code>: This parameter formats query output into a tabular format without additional formatting.<\/li>\n\n\n\n<li><code>-e \"&lt;Query&gt;\"<\/code>: This parameter specifies the MySQL query to be executed. Replace <code>&lt;Query&gt;<\/code> with the desired query.<\/li>\n\n\n\n<li><code>| sed \"s\/'\/\\'\/;s\/\\t\/\\\",\\\"\/g;s\/^\/\\\"\/;s\/$\/\\\"\/;s\/\\n\/\/g\"<\/code>: This pipeline passes the output of the MySQL command to the <code>sed<\/code> command, which performs several substitutions to convert the output into CSV format. Specifically, it replaces single quotes with escaped single quotes, tabs with commas, and adds quotation marks at the beginning and end of each line.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Step_3_Transfer_the_File_to_Local_System\"><\/span>Step 3: Transfer the File to Local System<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Once you&#8217;ve exported the CSV file, you can use the following command to transfer it to your local system:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">scp -P &lt;port_number&gt; &lt;username&gt;@&lt;ip\/domain&gt;:~&lt;source-file-path&gt; &lt;destination-file-path&gt;<\/pre>\n\n\n\n<p>For example:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">scp -P 2505 test_user@172.0.0.2:~\/backup\/logs.csv .<\/pre>\n\n\n\n<p>This command transfers the <code>logs.csv<\/code> file from the <code>backup<\/code> directory on the server to your current directory on your local system.<\/p>\n\n\n\n<p>That&#8217;s it! You&#8217;ve successfully exported data from a MySQL database to a CSV file and transferred it to your local system using SSH. This process can be used to export and transfer data from any MySQL database to any system that supports SSH.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"References\"><\/span>References<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>OpenSSH documentation: <a href=\"https:\/\/www.openssh.com\/manual.html\" target=\"_blank\" rel=\"noopener\">https:\/\/www.openssh.com\/manual.html<\/a><\/li>\n\n\n\n<li>MySQL documentation: <a href=\"https:\/\/dev.mysql.com\/doc\/\" target=\"_blank\" rel=\"noopener\">https:\/\/dev.mysql.com\/doc\/<\/a><\/li>\n\n\n\n<li>Bash documentation: <a href=\"https:\/\/www.gnu.org\/software\/bash\/manual\/bash.html\" target=\"_blank\" rel=\"noopener\">https:\/\/www.gnu.org\/software\/bash\/manual\/bash.html<\/a><\/li>\n\n\n\n<li>Sed documentation: <a href=\"https:\/\/www.gnu.org\/software\/sed\/manual\/sed.html\" target=\"_blank\" rel=\"noopener\">https:\/\/www.gnu.org\/software\/sed\/manual\/sed.html<\/a><\/li>\n\n\n\n<li>SCP documentation: <a href=\"https:\/\/man.openbsd.org\/scp\" target=\"_blank\" rel=\"noopener\">https:\/\/man.openbsd.org\/scp<\/a><\/li>\n\n\n\n<li>SSH documentation: <a href=\"https:\/\/man.openbsd.org\/ssh\" target=\"_blank\" rel=\"noopener\">https:\/\/man.openbsd.org\/ssh<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>If you&#8217;re a developer working with MySQL databases, you might need to export data from your database and transfer it to your local system. In this tutorial, we&#8217;ll show you how to export data from a MySQL database to a CSV file and transfer it to your local system using SSH. Step 1: Login to [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4224,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","_lmt_disableupdate":"","_lmt_disable":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[47],"tags":[63,83],"class_list":["post-4210","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-web-development","tag-database","tag-mysql"],"modified_by":null,"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/staging.osmosys.co\/uk\/wp-json\/wp\/v2\/posts\/4210","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/staging.osmosys.co\/uk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/staging.osmosys.co\/uk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/staging.osmosys.co\/uk\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/staging.osmosys.co\/uk\/wp-json\/wp\/v2\/comments?post=4210"}],"version-history":[{"count":0,"href":"https:\/\/staging.osmosys.co\/uk\/wp-json\/wp\/v2\/posts\/4210\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/staging.osmosys.co\/uk\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/staging.osmosys.co\/uk\/wp-json\/wp\/v2\/media?parent=4210"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/staging.osmosys.co\/uk\/wp-json\/wp\/v2\/categories?post=4210"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/staging.osmosys.co\/uk\/wp-json\/wp\/v2\/tags?post=4210"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}