Database Backup and Restoration Operations

Creating Database and Tables

CREATE DATABASE libraryDB;
USE libraryDB;

CREATE TABLE publications (
    pub_id INT PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    pub_year YEAR NOT NULL
);

CREATE TABLE writers (
    writer_id INT PRIMARY KEY,
    name VARCHAR(20),
    gender CHAR(1)
);

CREATE TABLE writer_publication (
    writer_id INT NOT NULL,
    pub_id INT NOT NULL
);

Inserting Sample Data

INSERT INTO publications VALUES 
    (11078, 'Learning MySQL', 2010),
    (11033, 'Study Html', 2011),
    (11035, 'How to use php', 2003),
    (11072, 'Teach yourself javascript', 2005),
    (11028, 'Learning C++', 2005),
    (11069, 'MySQL professional', 2009),
    (11026, 'Guide to MySQL 5.5', 2008),
    (11041, 'Inside VC++', 2011);

INSERT INTO writers VALUES 
    (1001, 'AuthorX', 'f'),
    (1002, 'AuthorA', 'f'),
    (1003, 'AuthorB', 'm'),
    (1004, 'AuthorC', 'f'),
    (1011, 'AuthorD', 'f'),
    (1012, 'AuthorE', 'm'),
    (1013, 'AuthorF', 'm'),
    (1014, 'AuthorG', 'f'),
    (1015, 'AuthorH', 'f');

INSERT INTO writer_publication VALUES 
    (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),
    (1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);

Backup and Restoration Tasks

1. Backup All Databases

   mysqldump -u root -p123456 --all-databases > full_backup.sql
   

2. Backup Specific Table from a Database

   mysqldump -u root -p123456 libraryDB publications > publications_backup.sql
   

3. Backup Multiple Databases First, create a test database:

   CREATE DATABASE testDB;
   

   Then backup:

   mysqldump -u root -p123456 --databases libraryDB testDB > multi_db_backup.sql
   

4. Restore Table Using mysql Command

   mysql -u root -p123456 libraryDB < publications_backup.sql
   

5. Restore Table Using source Command

   mysql -u root -p123456
   

   Within MySQL:

   USE libraryDB;
   SOURCE publications_backup.sql;
   

Index Management in MySQL

Creating Tables for Index Operations

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(20) NOT NULL,
    category_id INT NOT NULL DEFAULT 0,
    brand_id INT NOT NULL DEFAULT 0,
    product_code CHAR(12) NOT NULL,
    price FLOAT(6,2) NOT NULL DEFAULT 0.00,
    description TEXT
);

CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(20),
    parent_id INT DEFAULT 0
);

Index Oeprations

1. Modify Table Structure Remove description and product_code columns, add a click_count column:

   ALTER TABLE products
   DROP COLUMN description,
   DROP COLUMN product_code,
   ADD COLUMN click_count INT NOT NULL DEFAULT 0;
   

2. Add Unique Index Using ALTER TABLE

   ALTER TABLE products
   ADD UNIQUE INDEX idx_product_name (product_name);
   

3. Add Regular Index Using CREATE INDEX

   CREATE INDEX idx_price ON products(price);
   

4. Create and Remove Index Create index:

   CREATE INDEX idx_click_count ON products(click_count);
   

   Remove using DROP INDEX:

   DROP INDEX idx_click_count ON products;
   

   Remove using ALTER TABLE:

   ALTER TABLE products
   DROP INDEX idx_click_count;