How to take a Backup and Import Data in MySQL/SQLite Database

SQLITE

Backup and Import Data in SQLite Database on Windows

Commands for SQLite: (generally used on Windows for light data)

Initiate or Select db:

sqlite3 mydbname.db

To show tables:

.tables

To show DBs:

.databases

To show data:

SELECT * FROM <Table Name>;

To turn ON headers:

.headers on

To turn OFF headers:

.headers off

To show Limit lines e.g. 5-lines in view data:

SELECT * FROM <Table Name> limit 5;

Backup of the existing SQLite database:

sqlite3 <mydatabase name.db>

.backup <mydatabse backup.db>

Import Sqlite Data from an Existing Table from a CSV file:

Save MyFile.CSV in the SQLite folder where DB is installed:

.mode csv

.import c:/sqlite/myfile.csv <table name>

Check the data by viewing the data in the table to make sure.

MYSQL

Backup and Import Data in MySQL Database on Linux

Commands for MySQL: (On Ubuntu Linux)

Get start:

Login to MySQL:

mysql -u <username> -p

Select or change DB:

use<database name>

Show the DB list:

SHOW DATABASES;

Show tables in the selected database:

SHOW TABLES;

View Data:

SELECT * FROM <table name>

Backup of the existing MySQL database:

mysqldump -u administrator -p -d <db name> > mybackup.sql

It creates a DB backup in the working directories.

Open the backup file with vim/nano and be sure the contents are available in the file.

Import MySQL data from an existing table from a CSV file:

Before importing data from CSV to the existing table in DB, use the below steps:

Steps:

First, move/ copy your CSV file to the folder /var/lib/mysql-files and keep the name of the file as the table name.

Permission required:

sudo chmod +777 mysql-files/

Go to the folder:

cd /var/lib/mysql-files

The CSV file at the location “/var/lib/mysql-files” should be the same as the table name where you want to import data. e.g. MYTABLENAME.CSV

If your CSV file has a header row, then delete it. (You may use Nano or Vim.)

The data should start from row 1 itself.

If the file has blank data, set it to NULL by using the below command:

Remove blank data in the CSV file:

sed -i -e 's/""/"\N"/g' MYTABLENAME.csv

mysqlimport --fields-optionally-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --user=<mysql user name> -p <db name> /home/path/filename.csv

Here, You can check the delimiter and line terminating values (which may be optional in some cases).

Verify the data in the database by viewing the data.

An alternate method import can be done by MySQL CLI:

First load Data:

LOAD DATA INFILE '/var/lib/mysql-files/MYTABLE.CSV' INTO TABLE vehicle;

View the data to verify whether it is imported or not. (It might work.).

Some more commands:

I get an error like private file show it by:

SHOW VARIABLES LIKE "secure_file_priv";

It happens in case of file permission issue at “/var/lib/mysql-files”

For local variable:

show variables like "local_infile";

Foreign Key:

SET GLOBAL FOREIGN_KEY_CHECKS=1;

To enable the foreign key “1” to disable “0”. It is generally used if two tables are connected.

To check log:

desc logs;

To find in Log:

select * from logs where <variable e.g. detected_NP> like '%1234 and <variable e.g. Entry_Ts> like '%10:00:00%';

To describe duplicate log:

desc logs_dup;

To update data in the duplicate log:

update logs_dup set <variable e.g. Exit_Ts>='2023-10-27 10:00:00' where <variable e.g. detected_NP>=AA11AA1234 and <Variable e.g. Entry_Ts>='2023-10-26 14:00:50';

To Delete Data in the table:

DELETE FROM <Table Name> WHERE <Variable, e.g. Staff No>='1234’;

To Delete All Data in the Table:

DELETE FROM <Table Name>;

To exit from MySQL CLI:

exit

Export MySQL Data in a CSV File:

To export:

SELECT * FROM <Table Name> INTO OUTFILE 'myexport.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

show variables like "local_infile";

Load data in file "/var/lib/mysql-files/" into table test;

SELECT * FROM <Table Name> INTO OUTFILE '/var/lib/mysql-files/MyExport.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

Also Read: Deploy Cloud Native PostgreSQL on Kubernetes