using mysqldump command to backup the data from MySQL Database table

Introduction

In this blog post, we will take a look at the mysqldump command that is shipped as part of the MySQL clients.

Data Backup

We normally deal with backup of data by exporting the data as JSON / CSV or as SQL Files. This is required so that any change that we are working on might have a impact, we can immediately use the backup to fix any unexpected database operation.

When working with JSON / CSV at times, we have to deal with the issues in the data formats. Further as SQL queries, we get the option to rewrite the query, insert them into a local database, perform the required transform or operations and then move to the server.

The MySQL Workbench provides the export option through which we can generate the SQL scripts, but there are issues like the target table names are empty and there is a new insert statement for each row in the table.

Example Command

mysqldump.exe 
--host=servername --port=3306 --default-character-set=utf8 
--user=(db_user_name) -p --protocol=tcp --column-statistics=FALSE --skip-triggers
--set-gtid-purged=OFF --no-create-info --quick --max_allowed_packet=512M --skip-lock-tables 
"db_name" "table_name" --where="column_name='value'" > script_name.sql

As we can see in the above command, we have provided the following arguments

--host -> to provide the server name

--user -> the database connectivity user name

--port -> default to 3306 or custom port if used

--default-character -> we are using utf8, it can be based on the char set defined in the table schema

-p -> to prompt for the server password

--protocol -> by default, we are using the tcp protocol

--skip-triggers -> to skip invocation of triggers

--set-git-purged -> to skip the global transaction identifiers

--no-create -> to not include the create table script as part of the generated sql files

--quick -> for handling large volume of data

--max_allowed_packet -> to set a max packet size, we are using 512 MB

--skip-lock-tables -> to avoid locking table during running the query, default is to lock the table

--where -> adds a filter condition to the generated query so that, we can split into chunks or focus on the required subset of data in the export

I have tried this and it works fine. Hope this helps for anyone in need.

Comments

User Authentication schemes in a Multi-Tenant SaaS Application

User Authentication in Multi-Tenant SaaS Apps Introduction We will cover few scenarios that we can follow to perform the user authentication in a Multi-Tenant SaaS application. Scenario 1 - Global Users Authentication with Tenancy and Tenant forwarding In this scheme, we have the SaaS Provider Authentication gateway that takes care of Authentication of the users by performing the following steps Tenant Identification User Authentication User Authorization Forwarding the user to the tenant application / tenant pages in the SaaS App This demands that the SaaS provider authentication gateway be a scalable microservice that can take care of the load across all tenants. The database partitioning (horizontal or other means) is left upto the SaaS provider Service. Scenario 2 - Global Tenant Identification and User Authentication forwarding In the above scenario, the tenant identification happens on part of the SaaS provider Tenant Identification gateway. Post which, ...

Handling exceptions in the Executor service threads in Java

Introduction This is a continuation post on the exception handling strategies in the threads in Java. For Introduction, please read this post The second post is available here This post addresses the problem statement "How to use the exception handlers in the threads spawned by the Executor Service in Java?" Not all times, we will be using Thread classes to run our threads because we have to manage a lot of the underlying logic for managing threads. There is ExecutorService in Java which comes to the rescue for the above problem. In the previous posts, we have discussed on how to handle the exceptions in plain threads. However, when using executor service, we do not create / manage threads, so how do we handle exception in this case. We have a ThreadFactory as an argument which can be used to customize the way threads are created for use within the ExecutorService . The below snippet of code leverages this feature to illustrate the exception handling, wherein we creat...

Upgrade from http1.1 to http2 for Java spring boot applications hosted in tomcat

In this post, we will list down the tasks to be done for enabling the HTTP 2.0 support in spring boot applications which are hosted in Apache tomcat webserver Application Level Changes Spring boot Application Configuration Changes server.http2.enabled=true In the spring boot application's application.properties file, we have to add the above line so that Spring boot can add the support for http2 Tomcat server configuration In the tomcat web server, we should have SSL enabled before doing the below change. To start with, we have to shutdown the tomcat server instance that is running CD to the directory that has tomcat installed and cd to the bin directory and run the below command sh shutdown.sh We have add the UpgradeProtocol which adds the respective Http2Protocol handler classname to the connector pipeline that enables support for http2.0 <UpgradeProtocol className="org.apache.coyote.http2.Http2Protocol" /> The above UpgradeProtocol can be added to the connec...

SFTP and File Upload in SFTP using C# and Tamir. SShSharp

The right choice of SFTP Server for Windows OS Follow the following steps, 1. Download the server version from here . The application is here 2. Provide the Username, password and root path, i.e. the ftp destination. 3. The screen shot is given below for reference. 4. Now download the CoreFTP client from this link 5. The client settings will be as in this screen shot: 6. Now the code to upload files via SFTP will be as follows. //ip of the local machine and the username and password along with the file to be uploaded via SFTP. FileUploadUsingSftp("172.24.120.87", "ftpserveruser", "123456", @"D:\", @"Web.config"); private static void FileUploadUsingSftp(string FtpAddress, string FtpUserName, string FtpPassword, string FilePath, string FileName) { Sftp sftp = null; try { // Create instance for Sftp to upload given files using given credentials sf...

Download CSV file using JavaScript fetch API

Downloading a CSV File from an API Using JavaScript Fetch API: A Step-by-Step Guide Introduction: Downloading files from an API is a common task in web development. This article walks you through the process of downloading a CSV file from an API using the Fetch API in JavaScript. We'll cover the basics of making API requests and handling file downloads, complete with a sample code snippet. Prerequisites: Ensure you have a basic understanding of JavaScript and web APIs. No additional libraries are required for this tutorial. Step 1: Creating the HTML Structure: Start by creating a simple HTML structure that includes a button to initiate the file download. <!DOCTYPE html> < html lang = "en" > < head > < meta charset = "UTF-8" > < meta name = "viewport" content = "width=device-width, initial-scale=1.0" > < title > CSV File Download </ title > </ head > < body > ...

Software And Architecture

Search This Blog