MySQL Backup and Restore Commands

Backup Commands

How to Backup MySQL Database?

To backup of MySQL database/databases, the database must exist in MySQL Instance and ID which is using to backup, must have access to it.
The format of the command would be:

# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]


The parameters of the said command as follows.


    [username] : A valid MySQL username.

    [password] : A valid MySQL password for the user.

    [database_name] : A valid Database name you want to take backup.

    [dump_file.sql] : The name of backup dump file you want to generate.

How to Backup a Single MySQL Database?

To take a backup of single database, use the command as follows. The command will dump database [testdb] structure with data on to a single dump file called testdb.sql.

# mysqldump -uroot -p testdb > testdb_file.sql

How to Backup Multiple MySQL Databases?

If you want to take backup of multiple databases, run the following command. The following example command takes a backup of databases [testdb, sysdb] structure and data in to a single file called testdb_sysdb.sql.

# mysqldump -uroot -p --databases testdb sysdb > testdb_sysdb.sql

How to Backup All MySQL Databases?

If you want to take backup of all databases, then use the following command with option –all-database. The following command takes the backup of all databases with their structure and data into a file called all-databases.sql.

# mysqldump -u root -p --all-databases > all-databases.sql

How to Backup MySQL Database Structure Only?

If you only want the backup of database structure without data, then use the option –no-data in the command. The below command exports database [testdb] Structure into a file testdb_structure.sql.

# mysqldump -u root -p --no-data testdb > testdb_structure.sql

How to Backup MySQL Database Data Only?

To backup database Data only without structure, then use the option –no-create-info with the command. This command takes the database [testdb] Data into a file testdb_data.sql.

# mysqldump -u root -p --no-create-db --no-create-info testdb > testdb_data.sql

How to Backup Single Table of Database?

With the below command you can take backup of single table or certain tables of your database. For example, the following command only take backup of wp_posts table from the database testdb.

# mysqldump -u root -p testdb tbl_test > testdb_test.sql

How to Backup Multiple Tables of Database?
If you want to take backup of multiple or certain tables from the database, then separate each table with space.

# mysqldump -u root -p testdb tbl_test tbl_emp > testdb_tbl.sql

How to Backup Remote MySQL Database?

The below command takes the backup of remote server [192.10.92.6] database [testdb] into a local server.

# mysqldump -h 192.10.92.6 -u root -p testdb > testdb.sql

Restore Commands

How to Restore MySQL Database?

In the above tutorial we have seen the how to take the backup of databases, tables, structures and data only, now we will see how to restore them using following format.

# # mysql -u [username] –p[password] [database_name] < [dump_file.sql]

How to Restore Single MySQL Database?

To restore a database, you must create an empty database on the target machine and restore the database using msyql command. For example the following command will restore the testdb.sql file to the testdb database.

# mysql -u root -p testdb < testdb.sql

If you want to restore a database that already exist on targeted machine, then you will need to use the mysqlimport command.

# mysqlimport -u root -p testdb < testdb.sql

In the same way you can also restore database tables, structures and data.

SQL Server Delete Duplicate Rows from Table

In this post, I will demonstrate techniques to delete duplicate rows from SQL Server database table.

One day I was working on one of the freelancing project and found duplicates records in some of client tables.
I have found the work around and created solution to delete duplicate rows.
Let’s create sample table and insert data into it:

CREATE TABLE tbl_DuplicateData
( 
 ID INTEGER PRIMARY KEY
 ,Name VARCHAR(150)
)
GO
 
INSERT INTO tbl_DuplicateData VALUES 
((1,'Roy'),(2,'Ashish'),(3,'Tarun'),
(4,'Rahul'),(5,'Kapoor'),
(6,'Nadun'),(7,'Roy'),(8,'Rahul'),(9,'Kapoor'),
(10,'Rahul'),(11,'Roy'))
GO

There can be multiple solution and different way to get out of this issue:
1st solution is using WITH CTE:

;WITH cte_FindDuplicateRows AS 
(
    SELECT 
       Name
       ,ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) RowNumber
    FROM  tbl_DuplicateData
)
DELETE FROM cte_FindDuplicateRows
WHERE RowNumber > 1
GO

Another way is using SELF JOIN:

DELETE FROM A
FROM tbl_DuplicateData AS A
INNER JOIN tbl_DuplicateData AS B
  ON A.Name = B.Name AND A.ID > B.ID
GO

Check the table after deleting duplicate rows:

SELECT *FROM tbl_DuplicateData ORDER BY ID

As per requirement you can choose any of the solution and before executing please must check your query plan and choose best solution to delete duplicate rows.