MariaDB Installation

yum install mariadb mariadb-server mariadb-libs -y
systemctl start mariadb
netstat -tnlp | grep :3306

MariaDB runs without a default password, allowing direct access via the mysql command.

Database Setup

CREATE DATABASE school DEFAULT CHARACTER SET utf8;

Table Definitions

CREATE TABLE student (
    s_id VARCHAR(20),
    s_name VARCHAR(20) NOT NULL DEFAULT '',
    s_birth VARCHAR(20) NOT NULL DEFAULT '',
    s_sex VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(s_id)
);

CREATE TABLE course (
    c_id VARCHAR(20),
    c_name VARCHAR(20) NOT NULL DEFAULT '',
    t_id VARCHAR(20) NOT NULL,
    PRIMARY KEY(c_id)
);

CREATE TABLE teacher (
    t_id VARCHAR(20),
    t_name VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(t_id)
);

CREATE TABLE score (
    s_id VARCHAR(20),
    c_id VARCHAR(20),
    s_score INT(3),
    PRIMARY KEY(s_id, c_id)
);

Import the schema using:

mysql school < school.sql

MySQL Command-Line Options

Option	Description
-u	Database username
-p	Database password
-h	Server hostname or IP address
-D	Target database name
-N	Suppress column names from output
-B	Use tab characters instead of vertical bars as separators
-e	Execute specified SQL statement
-E	Display results vertically
-H	Output in HTML table format
-X	Output in XML format

Usage Examples

Execute a query against a specific database:

mysql -D school -e "SELECT * FROM student;"

Clean output without headers or separators:

mysql -B -N -D school -e "SELECT * FROM student;"

Vertical display format:

mysql -E -B -N -D school -e "SELECT * FROM student;"

HTML output:

mysql -H -B -N -D school -e "SELECT * FROM student;"

XML export:

mysql -X -B -N -D school -e "SELECT * FROM student;" > result.xml

Shell Script Database Operations

Create a reusable script for executing SQL commands:

#!/bin/bash
#
db_user="appuser"
db_pass="SecurePass123"
db_host="10.11.0.215"
db_name="$1"
query="$2"

mysql -h"$db_host" -u"$db_user" -p"$db_pass" -D"$1" -B -e "$query"

Run queries using the script:

sh db_ops.sh school "SELECT * FROM score"

Insert records:

sh db_ops.sh school "INSERT INTO score VALUES('1020','1002','100');"
sh db_ops.sh school "SELECT * FROM score"

Export results to text file:

sh db_ops.sh school "SELECT * FROM score" > result.txt

Importing Text Files into MySQL

First, create a target table matching the source structure:

CREATE TABLE student1 LIKE student;

Source Data File (tab-separated)

1010    jerry    1991-12-13    male
1011    mike    1991-12-13    female
1012    tracy    1991-12-13    male
1013    kobe    1991-12-13    male
1014    allen    1991-12-13    female
1015    curry    1991-12-13    male
1016    tom    1991-12-13    female

Import Script

#!/bin/bash
#
db_user="appuser"
db_pass="SecurePass123"
db_host="10.11.0.215"

mysql_cmd="mysql -h$db_host -u$db_user -p$db_pass"

cat data.txt | while read sid sname sbirth ssex
do
    $mysql_cmd -e "INSERT INTO school.student1 VALUES('$sid','$sname','$sbirth','$ssex')"
done

Run the import:

sh import_data.sh

Conditional Import with Filteering

To import only records meeting certain criteria:

#!/bin/bash
#
db_user="appuser"
db_pass="SecurePass123"
db_host="10.11.0.215"

mysql_cmd="mysql -h$db_host -u$db_user -p$db_pass"

cat data.txt | while read sid sname sbirth ssex
do
    if [ "$sid" -gt 1014 ]; then
        $mysql_cmd -e "INSERT INTO school.student1 VALUES('$sid','$sname','$sbirth','$ssex')"
    fi
done

Execute and verify:

sh import_data.sh
sh db_ops.sh school "SELECT * FROM student1"

Handling Pipe-Delimited Files

Source data:

2021|hao|1989-12-21|male
2022|zhang|1989-12-21|male
2023|ouyang|1989-12-21|male
2024|li|1989-12-21|female

Import script using custom field separator:

#!/bin/bash
#
db_user="appuser"
db_pass="SecurePass123"
db_host="10.11.0.215"

# Set Input Field Separator for pipe character
IFS="|"

cat data2.txt | while read id name birth sex
do
    mysql -u"$db_user" -p"$db_pass" -h"$db_host" \
          -e "INSERT INTO school.student2 VALUES('$id','$name','$birth','$sex')"
done

Handling Colon-Delimited Files

Source data:

2025:hao:1989-12-21:male
2026:zhang:1989-12-21:male
2027:ouyang:1989-12-21:male
2028:li:1989-12-21:female

Import script:

#!/bin/bash
#
db_user="appuser"
db_pass="SecurePass123"
db_host="10.11.0.215"

IFS=":"

cat data3.txt | while read id name birth sex
do
    mysql -u"$db_user" -p"$db_pass" -h"$db_host" \
          -e "INSERT INTO school.student2 VALUES('$id','$name','$birth','$sex')"
done

Verify the imported data:

sh db_ops.sh school "SELECT * FROM student2"

Key Considerations

• The IFS variable controls field delimiters for read operations
• Use -B flag when exporting to remove vertical bar separators for spreadsheet compatibility
• The LOAD DATA INFILE command offers faster bulk imports, but shell scripting provides filtering flexibility for conditional imports