MySQL Tutorials

MySQL and SQL

MySQL

• INTRODUCTION
• TO LOGIN
• BASIC COMMANDS
• OTHER COMMANDS

INTRODUCTION

• MySQL is a key part of LAMP (Linux, Apache, MySQL, PHP / Perl / Python), the fast-growing open source enterprise software stack. More and more companies are using LAMP as an alternative to expensive proprietary software stacks because of its lower cost and freedom from platform lock-in.
• MySQL was originally founded and developed in Sweden by two Swedes and a Finn: David Axmark, Allan Larsson and Michael "Monty" Widenius, who had worked together since the 1980's.

The Number One Open Source Database...

• Thousands of downloads a day
• Millions of active installations
• Provides the date store for the AMP Vertical and other open source stacks
• Available under the GPL license and a commercial license
• Complementary products, such as client administration tools and MySQL Cluster
• MySQL AB founded in 1995
• Now with operations around the world
• Providing consulting, support, training and licensing

…and How it Got That Way

• Free to download and use
• Extremely easy to setup, use and maintain
• Created the “personal” RDBMS trend that is now followed by the large proprietary databases
• Open code base, API and documentation
• Easy for communities to develop language bindings, such as PHP and Java
• New thinking
• Fresh code base, written from scratch to fulfill a specific need
• Defined new parameters of what an RDBMS is, and should do
• Brought the RDBMS to the masses
• MySQL has been belittled as a “SQL enabled file system”
• That’s not necessarily always a bad thing

So What About XYZ?

• Many open source databases are older
• Complexity in setup and maintenance
• Based on some older code that doesn’t provide evolution in storage technology
• New open source databases
• It’s not easy – good storage engines are hard to write
• It’s not easy – finding support and visibility in light of open source giants MySQL and PostgreSQL isn’t easy
• “If it ain’t too broke, don’t fix it”
• MySQL has limitations, but can generally deliver in many situations
• The 80/20 rule

A big win for MySQL has been performance – in most cases

• A big win for MySQL has been performance – in most cases
• eWeek’s Database Benchmark
• MySQL had top performance, including scalable tuning and configuration options
• MySQL excels at rapid queries
• Perfect fit for many applications, especially request/response (web)
• Simpler queries scale better than complex queries
• Mostly read or mostly write concurrency performs best
• Large datasets usually perform well
• MySQL performance restrictions
• Large complex queries can cause SQL optimization problems
• Read/write concurrency can have severe impact
• MySQL needs to be tuned for production

• And There’s Performance

Features from 60,000 Feet

• Multiple storage-engine architecture
• ACID compliant transactions
• Standards based SQL, aiming for SQL-2003
• Syntax based query caching
• Master/Slave replication
• Compile and runtime feature flexibility to conserve resources
• Embedded in hardware
• Extremely high load applications
• Written in C, C++ and ASM
• 80% in C
• Parse tree and optimizer in C++
• String functionality in ASM on some platforms

Languages, Hardware and Operating Systems

• Languages
• C API: libmysql (MySQL AB)
• PHP extensions and PDO (community)
• Connector/JDBC (MySQL AB)
• Connector/.NET (MySQL AB)
• Connector/ODBC (MySQL AB)
• Perl DBD::DBI (community)

• Hardware
• Xeon, Opteron, Itanium, Nacona
• PowerPC, SPARC, RS 6000

• Operating Systems
• Red Hat, SuSE, your Linux here
• FreeBSD, OpenBSD, NetBSD
• Windows, Mac
• Solaris, HPUX, AIX

Under the Hood

• Multithreaded - 1 connection means 1 thread
• Decrease in overhead – increase in performance
• Configurable per-thread parameters
• read_buffer_size - I/O per thread
• read_rnd_buffer_size - for ORDER BY
• sort_buffer_size - rows in memory for ORDER BY and GROUP BY
• tmp_table_size – memory temporary tables for GROUP BY
• Multithreaded – 1 server means 1 process
• Limited addressable space, especially under 32bits
• 64bit platforms are a must for even moderate datasets
• 64bit platforms are a must to take advantage of buffers
• Pay attention to the operating system’s kernel/user memory split in a process

Pluggable Storage Engine Architecture

• Storage engines are the heart of a database
• Provide the data structures and functionality for reliable data persistence and fast data access
• They are very complex and it’s exponentially difficult to design one that fits all use cases (dataset size, query modeling, traffic patterns)
• MySQL can use multiple storage engines simultaneously
• Each table can be implemented using a different storage engine
• An SQL query can operate transparently across storage engines
• Can cause issues with SQL optimization
• Developers can create their own storage engine for a specific use case

PSEA Diagram

• Detailed diagram: http://www.mysql.com/common/images/PSEA_diagram.jpg

Storage Engine: Memory

• RAM based storage engine
• Data is stored only in system memory
• Schema persists on disk
• Very fast
• No disk I/O
• Basic data structures
• Quite limited
• Fixed column widths – no VARCHAR
• Limited indexes available

Storage Engine: MyISAM

• File based storage
• .MYD – table data
• .MYI – index data
• .FRM – table definition (schema)
• Easily maintained
• Architecture-independent data
• Files can be copied across platforms
• Low overhead
• No transactions
• Large grained table level locking
• Excels at mostly-read applications
• One third the memory/disk footprint of transactional engines

• Limited
• Write concurrency
• Potential for corruption with limited recovery (no transactions)
• Limited data dictionary (reduced optimizations)
• Enjoys smaller datasets and simpler queries
• Made MySQL…
• A “SQL enabled file system”
• Belittled as a being a toy
• Number one

Storage Engine: InnoDB

• ACID Compliant
• Atomicity/Consistency/Isolation/Durability
• Full transactional support and multi-versioning
• Read Uncommitted, Read Committed, Repeatable Read, Serializable
• Foreign keys constraints
• Locking and logging
• Row-level and next-key locking
• Consistent non-locking reads
• Commit and rollback segments
• Fault tolerance and table spaces
• Large datasets, raw partitions
• Online backups

• Next generation indexing and data storage
• Clustered and B-tree indexes
• Higher overhead
• Substantial memory/disk footprint
• Administration and maintenance
• Made MySQL…
• Competitive in the enterprise database market
• Ready to break out of commodity RDBMS use
• A target…

Storage Engine: NDB (Cluster)

• Designed to eliminate any single-point-of-failure
• The Five-Nines of MySQL
• Shared-nothing data distribution
• Data redundancy with synchronous replication
• Transparent sub-second fail-over
• Available even with multiple node failures
• Network partitioning and load balancing algorithms
• Hot backup and restore
• Acquisition of Ericsson’s IP and staff
• Implemented as a pluggable storage engine
• Memory resident with disk persistence

Server Roadmap

• Most common: MySQL 3.23.x, 4.0 and 4.1
• Just out: MySQL 5.0
• This year: MySQL 5.1

Features from 30,000 Feet

• Transactions, foreign keys, multi-versioning
• InnoDB included by default since 4.0
• Query cache
• Dynamic configuration variables
• Replication
• Master-slave architectures
• Master-master possible, but with caveats
• Dual threaded replication - One thread to read updates from the master and another to apply them locally
• FULLTEXT indexing enhanced and tuned
• Embedded server, libmysqld
• Sub-queries and derived tables
• Prepared statements, binary client/server protocol
• Text protocol prior to 4.1
• OpenGIS (Geographical data)
• Internationalization, UTF-8
• Character set and collation specified at column level

Features With 5.0

• The enterprise release
• Further standardization on SQL-2003 features and syntax
• Stored Procedures (PSM)
• Views
• Triggers
• Data dictionary
• R-Tree indexes for non-spatial tables
• Server-side cursors
• Precision math
• XA support
• Additional storage engines: Federated and Archive

Concerns Beyond 5.0

• MySQL AB has broken new ground in the industry
• MySQL AB has become a target
• Oracle acquired InnoBase, the makers of the InnoDB storage engine
• MySQL considered an affirmation of their strategy and technology
• MySQL stressed the use of a formally deprecated storage engine, BDB
• Oracle acquired Sleepycat, the makers of the BDB storage engine
• Some powerful friends
• MySQL is closely partnered with SAP
• MySQL/SAP co-develop MaxDB, which has in the past been targeted towards at the SAP market
• Interesting future

• Web Browser (Client)

• Web Server

• PHP

• Web Browser (Client)

• Database Server

• Web Server

• PHP

Installation Summary

• More detailed installation instructions are given on the CD
• Install MySQL in c:\mysql
• MySQL can be installed as a service (Win 2000/XP)
• Can make icons on the desktop for starting and stopping the server.

Command Line Client

• The standard command line client is
• c:\mysql\bin\mysql.exe
• The command line client can be used to send commands and SQL queries to the MySQL server
• There are also GUI clients such as MyCC

• MySQL Server

• Client Program

• Make a request (SQL query)

• Get results

• Client program can be a MySQL command line client, GUI client, or a program written in any language such as C, Perl, PHP, Java that has an interface to the MySQL server.

Connecting to the Server

• Use a command prompt that sets the path to c:\mysql\bin
• The following command connects to the server:
• mysql -u root -p
• you are prompted for the root password.
• you can now send comands and SQL statements to the server

WARNING WARNING

• WARNING
• Always assume that everything is case sensitive, especially table names.
• This is not the case in Windows XP but it is the case in Linux

Entering commands (1)

• Show all the databases
• SHOW DATABASES;

• mysql> SHOW DATABASES; +-------------+ | Database | +-------------+ | bookstore | | employee_db | | mysql | | student_db | | test | | web_db | +-------------+

Entering commands (2)

• Choosing a database and showing its tables
• USE test; SHOW tables;

• mysql> USE test; Database changed mysql> SHOW tables; +----------------+ | Tables_in_test | +----------------+ | books | | name2 | | names | | test | +----------------+ 4 rows in set (0.00 sec) mysql>

Entering commands (3)

• Show the structure of a table
• DESCRIBE names;

• mysql> DESCRIBE names; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | firstName | varchar(20) | | | | | | lastName | varchar(20) | | | | | +-----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql>

Entering commands (4)

• Show the rows of a table (all columns)
• SELECT * FROM names;

• mysql> SELECT * FROM names; +----+-----------+------------+ | id | firstName | lastName | +----+-----------+------------+ | 1 | Fred | Flintstone | | 2 | Barney | Rubble | +----+-----------+------------+ 2 rows in set (0.00 sec) mysql>

Entering commands (5)

• Inserting a new record
• INSERT INTO names (firstName, lastName) VALUES ('Rock','Quarry');
• SELECT * FROM names;

• mysql> INSERT INTO names (firstName, lastName) VALUES ('Ralph', 'Quarry'); Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM names; +----+-----------+------------+ | id | firstName | lastName | +----+-----------+------------+ | 1 | Fred | Flintstone | | 2 | Barney | Rubble | | 3 | Ralph | Quarry | +----+-----------+------------+ 3 rows in set (0.00 sec) mysql>

Entering commands (6)

• Updating a record
• UPDATE names SET lastName = 'Stone' WHERE id=3;
• SELECT * FROM names;

• mysql> UPDATE names SET lastName = 'Stone' WHERE id=3; Query OK, 1 row affected (0.28 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM names; +----+-----------+------------+ | id | firstName | lastName | +----+-----------+------------+ | 1 | Fred | Flintstone | | 2 | Barney | Rubble | | 3 | Ralph | Stone | +----+-----------+------------+ 3 rows in set (0.00 sec) mysql>

Logging output

• The commands you type and their ouput can be logged to a file by using the following command inside the MySQL command line client
• tee log.txt
• Here log.txt is the name of the file

Executing SQL files (1)

• It is usually better to use an editor to write an SQL script and send it to the server.
• A file of SQL commands such as books.sql can be executed by the server by using a command such as
• C:\mysql\bin\mysql -u root -p < books.sql
• This assumes that books.sql is in your current directory. Otherwise the complete path to books.sql must be supplied

Executing SQL files (2)

• A file of SQL commands such as books.sql can also be executed from inside the MySQL client using the source command
• source c:\.....\books.sql
• Here the full path to books.sql should be used.

Documentation

• MySQL comes with a tutorial and complete documentation in a HUGE file:
• c:\mysql\Docs\manual.html
• Table of contents with links:
• c:\mysql\Docs\manual_toc.html
• Use this file to locate the link to the topic you are interested in.

Database concepts (1)

• A relational database management system consists of a number of databases.
• Each database consists of a number of tables.
• Example table

• isbn

• title

• author

• pub

• year

• price

• books table

• rows (records)

• column headings

Some SQL data types (1)

• Each entry in a row has a type specified by the column.
• Numeric data types
• TINYINT, SMALLINT, MEDIUMINT,
• INT, BIGINT
• FLOAT(display_length, decimals)
• DOUBLE(display_length, decimals)
• DECIMAL(display_length, decimals)
• NUMERIC is the same as DECIMAL

Some SQL data types (2)

• Date and time types
• DATE
• format is YYYY-MM-DD
• DATETIME
• format YYYY-MM-DD HH:MM:SS
• TIMESTAMP
• format YYYYMMDDHHMMSS
• TIME
• format HH:MM:SS
• YEAR
• default length is 4

SQL data types (3)

• String types
• CHAR
• fixed length string, e.g., CHAR(20)
• VARCHAR
• variable length string, e.g., VARCHAR(20)
• BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB
• same as TEXT, TINYTEXT ...
• ENUM
• list of items from which value is selected

SQL commands SHOW, USE

• SHOW
• Display databases or tables in current database;
• Example (command line client):
• show databases;
• show tables;
• USE
• Specify which database to use
• Example
• use bookstore;

The CREATE Command (1)

• CREATE creates a database table

• CREATE TABLE table_name ( column_name1 column_type1, column_name2 column_type2, ... column_nameN column_typeN );

• Note: To create a database use the statement CREATE db_name;

The CREATE Command (2)

• Specifying primary keys

• CREATE TABLE table_name ( column_name1 column_type1 NOT NULL DEFAULT '0', column_name2 column_type2, ... column_nameN column_typeN, PRIMARY KEY (column_name1) );

The CREATE Command (3)

• autoincrement primary integer keys

• CREATE TABLE table_name ( column_name1 column_type1 PRIMARY KEY NOT NULL DEFAULT '0' AUTO_INCREMENT, column_name2 column_type2, ... column_nameN column_typeN, );

The CREATE Command (4)

• Can also create UNIQUE keys. They are similar to PRIMARY KEYS but can have NULL values.
• Can also create INDEX fields.

Conditional Creation

• Conditional database creation
• CREATE DATABASE IF NOT EXISTS db_name;
• Conditional table creation
• CREATE TABLE IF NOT EXISTS table_name;

The DROP Command

• To delete databases and tables use the DROP command
• Examples
• DROP DATABASE db_name;
• DROP DATABASE IF EXISTS db_name;
• DROP TABLE table_name;
• DROP TABLE IF EXISTS table_name;

• Note: Don't confuse DROP with DELETE which deletes rows of a table.

The INSERT Command

• Inserting rows into a table

• INSERT INTO table_name ( col_1, col_2, ..., col_N) VALUES ( val_1, val_2, ..., val_N);

• String values are enclosed in single quotes by default but double quotes are also allowed. Literal quotes need to be escaped using \' and \"

The SELECT Command (1)

• Selecting rows from a table
• Simplest form: select all columns
• Select specified columns
• Conditional selection of rows

• SELECT column_list FROM table_name;

• SELECT * FROM table_name;

• SELECT column_list FROM table_name WHERE condition;

The SELECT Command (2)

• Specifying ascending row ordering
• Specifying descending row ordering

• SELECT column_list FROM table_name WHERE condition ORDER by ASC;

• SELECT column_list FROM table_name WHERE condition ORDER by DESC;

The SELECT Command (3)

• There are many other variations of the select command.
• Example: finding the number of records in a table assuming a primary key called id:
• Can also perform searching using the WHERE option

• SELECT COUNT(id) FROM table_name

The UPDATE Command

• Used to modify an existing record
• Conditional update version

• UPDATE table_name SET col_1 = 'new_value1', ..., col_n = 'new_value2';

• UPDATE table_name SET col_1 = 'new_value1', ..., col_n = 'new_value2' WHERE condition;

• studentID

• first_name

• USE test; CREATE TABLE marks ( studentID SMALLINT AUTO_INCREMENT NOT NULL, first_name VARCHAR(20) NOT NULL, last_name VARCHAR(20) NOT NULL, mark SMALLINT DEFAULT 0 NOT NULL, PRIMARY KEY (studentID) );

• marks table

• last_name

• mark

• -- Insert some rows into marks table
• INSERT INTO marks (first_name, last_name, mark) VALUES ('Fred', 'Jones', 78); INSERT INTO marks (first_name, last_name, mark) VALUES ('Bill', 'James', 67); INSERT INTO marks (first_name, last_name, mark) VALUES ('Carol', 'Smith', 82); INSERT INTO marks (first_name, last_name, mark) VALUES ('Bob', 'Duncan', 60); INSERT INTO marks (first_name, last_name, mark) VALUES ('Joan', 'Davis', 86);

Executing The Script

• within MySQL use a command such as
• source c:/.........../marks.sql
• This adds the marks table to the test database

The Marks Table

• Selecting the complete table

• SELECT * FROM marks;

• +-----------+------------+-----------+------+ | studentID | first_name | last_name | mark | +-----------+------------+-----------+------+ | 1 | Fred | Jones | 78 | | 2 | Bill | James | 67 | | 3 | Carol | Smith | 82 | | 4 | Bob | Duncan | 60 | | 5 | Joan | Davis | 86 | +-----------+------------+-----------+------+ 5 rows in set (0.00 sec)

The WHERE Clause (1)

• Select rows according to some criterion

• SELECT * FROM marks WHERE studentID > 1 AND studentID < 5;

• +-----------+------------+-----------+------+ | studentID | first_name | last_name | mark | +-----------+------------+-----------+------+ | 2 | Bill | James | 67 | | 3 | Carol | Smith | 82 | | 4 | Bob | Duncan | 60 | +-----------+------------+-----------+------+ 3 rows in set (0.01 sec)

The WHERE Clause (2)

• Select rows with marks >= 80

• SELECT * FROM marks WHERE mark >= 80;

• +-----------+------------+-----------+------+ | studentID | first_name | last_name | mark | +-----------+------------+-----------+------+ | 3 | Carol | Smith | 82 | | 5 | Joan | Davis | 86 | +-----------+------------+-----------+------+ 2 rows in set (0.00 sec)

The ORDER BY Clause

• Select rows according to some criterion

• SELECT * FROM marks ORDER BY mark DESC;

• +-----------+------------+-----------+------+ | studentID | first_name | last_name | mark | +-----------+------------+-----------+------+ | 5 | Joan | Davis | 86 | | 3 | Carol | Smith | 82 | | 1 | Fred | Jones | 78 | | 2 | Bill | James | 67 | | 4 | Bob | Duncan | 60 | +-----------+------------+-----------+------+ 5 rows in set (0.00 sec)

Searching Using LIKE (1)

• LIKE is used to search a table for values containing a search string:
• There are two wild-card characters used to specifiy patterns:
• _ matches a single character
• % matches zero or more characters
• Can also use NOT LIKE
• Searching is case insensitive

Searching Using LIKE (2)

• Example: last names in marks table that begin with J
• Example: first names that have 3 letters

• SELECT * FROM marks WHERE last_name LIKE 'J%';

• SELECT * FROM marks WHERE first_name LIKE '_ _ _';

Quoting strings

• If a string contains a single quote it must be backquoted (escaped) before it can be used in a query
• Example: find records containing O'Reilly in the last_name field.

• SELECT * FROM marks WHERE last_name = 'O\'Reilly';

Limiting number of rows

• LIMIT can be used to specify the maximum number of rows that are to be returned by a select query. Example
• SELECT * FROM marks LIMIT 3;
• This query will return only the first 3 rows from the marks table
• To return 15 rows beginning at row 5 use
• SELECT * FROM marks LIMIT 4, 15;

• TO LOGIN
• To login (from unix shell) use -h only if needed.the command used to login is
• [mysql dir]/bin/mysql -h hostname -u root -p

• DDL COMMANDS
• DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.
• Examples:
• CREATE, ALTER, DROP statements

CREATE

• Creates objects in the database the queery used to create theobject is
• create database [databasename];
• Example :
• create database [employee];

ALTER

• Alters objects of the database
• ALTER TABLE <table_name>
• ADD <column_name1> <datatype1> <constraint1>
• ALTER TABLE <table_name>
• ALTER COLUMN <column_name1> <datatype1> <constraint1>
• ALTER TABLE <table_name>
• DROP COLUMN <column_name1> <datatype1>

DROP

• Deletes objects of the database
• the syntax for dorp is
• DROP TABLE [ IF EXISTS ] table_name1, table_name2, ....
• Removes the table(s) from the database. The IF EXISTS clause will drop the table only if it exists. If this clause is not present an error is generated if the table does not exist. Any data that was in a dropped table is lost so use with care.

DML COMMANDS

• DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.
• Examples:
• SELECT, UPDATE, INSERT statements

• SELECT
• SELECT [ DISTINCT | ALL ]
• column_expression1, column_expression2, ....
• [ FROM from_clause ]
• [ WHERE where_expression ]
• [ GROUP BY expression1, expression2, .... ]
• [ HAVING having_expression ]
• [ ORDER BY order_column_expr1, order_column_expr2, .... ]
• The SELECT statement is used to form queries for extracting information out of the database. The following example query will return the number, quantity and price of all orders for more than 5 items sorted in descending order by order number. to the output.

• UPDATE
• UPDATE table_name
• SET col_name1 = expression1, col_name2 = expres sion2, ....
• [ WHERE expression ]
• [ LIMIT limit_amount ]
• Updates information in a table. The SET clause is a list of assignments that describe how the columns of the data matched by the WHERE clause are to be updated.

• INSERT
• INSERT INTO table_name [ ( col_name1, col_name2, .... ) ]
• VALUES ( expression1_1, expression1_2, .... ),
• ( expression2_1, expression2_2, .... ), ....
• This is the SQL command to insert records into a table in the database. This statement comes in three forms.

• The Second form of insert command
• INSERT INTO table_name [ ( col_name1, col_name2, .... ) ]
• SELECT ...
• The Third form of insert cmmand
• INSERT INTO table_name
• SET col_name1 = expression1, col_name2 = expression2, ....

TCL COMMSNDS

• TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.
• Examples:
• COMMIT, ROLLBACK statements

• COMMIT AND ROLLBACK
• Saves work done in transactions
• The Syntax
• COMMIT
• ROLLBACK
• Transactional operations for closing a transaction and either committing all the changes made or rolling back and disposing all changes

• DCL COMMANDS
• DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.
• Examples:
• GRANT, REVOKE statements

• GRANT AND REVOKE
• GRANT privileges ON database_object TO ( PUBLIC | user_list )‏
• [ WITH GRANT OPTION ]
• REVOKE [ GRANT OPTION FOR ] privi- leges ON database_object
• FROM ( PUBLIC | user_list )‏
• Grants or revokes types of access on a table or view to a user. When a table or view is created the system gives full grant options to the user that created the object.

OTHER COMMANDS

• SET
• SET variable = expression
• SET AUTO COMMIT ( ON | OFF )‏
• SET TRANSACTION ISOLATION LEVEL ( SERIALIZABLE )‏
• SET SCHEMA schema_name
• Makes a change to the state of the connection. SET AUTO COMMIT is used to switch transaction 'auto commit mode' on or off.

• DESCRIBE
• SYNTAX
• DESCRIBE table_name
• This command provides information about the columns of the table. It shows the column names, the type / size and scale (if applicable) and other useful information.

• SHOW
• SHOW engine_variable
• engine_variable ::= TABLES | SCHEMA | STATUS | CONNECTIONS
• Shows internal information about the database system. SHOW TABLES returns a list of tables in the database. SHOW STATUS returns debugging and statistical information about the internal state of the database engine. SHOW CONNECTIONS returns a snapshot of the current connections on the database. SHOW SCHEMA lists all the schema defined.

• SHUTDOWN
• SYNTAX
• SHUTDOWN
• Shuts down the database. If the database is running as a server the database shuts down cleanly and the process is stopped. If the database is embedded in a Java application it is cleanly put into a shut down state.

Filesystem Check

• The first thing you need to know is that /var/lib/mysql/ibdata1 file NEVER shrinks!
• We opted to enable innodb_file_per_table to bypass ibdata1 file from growing out of control.
• However, the individual *.ibd files will grow, the optimize command will shrink the file, but will lock the table!
• ls –ltr /apps/mysql_data/ > LS_MYSQL_02dec2013.doc
• Also keep track of all the filesystems.
• df –h > DF_H_02dec2013.doc

Topics

Introducing Relational Databases Terminology Managing Databases

Introducing Relational Databases

A relational database manages data in tables. Databases are managed by a relational database management system (RDBMS). An RDBMS supports a database language to create and delete databases and to manage and search data. The database language used in almost all DBMSs is SQL.

Introducing Relational Databases

After creating a database, the most common SQL statements used are INSERT to add data UPDATE to change data DELETE to remove data SELECT to search data A database table may have multiple columns, or attributes, each of which has a name. Tables usually have a primary key, which is one or more values that uniquely identify each row in a table (Figure 3.1.)

Introducing Relational Databases

• Figure 3-1. An example of relational database containing two related tables

Introducing Relational Databases

A database is modeled using entity-relationship (ER) modeling. (Figure 3.2.)

• Figure 3-2. An example of relational model of the winery database

Terminology

Database A repository to store data. Table The part of a database that stores the data. A table has columns or attributes, and the data stored in rows. Attributes The columns in a table. All rows in table entities have the same attributes. For example, a customer table might have the attributes name, address, and city. Each attribute has a data type such as string, integer, or date.

Terminology

Rows The data entries in a table. Rows contain values for each attribute. For example, a row in a customer table might contain the values "Matthew Richardson," "Punt Road," and "Richmond." Rows are also known as records. Relational model A model that uses tables to store data and manage the relationship between tables. Relational database management system A software system that manages data in a database and is based on the relational model.

Terminology

SQL A query language that interacts with a DBMS. SQL is a set of statements to manage databases, tables, and data. Constraints Restrictions or limitations on tables and attributes. For example, a wine can be produced only by one winery, an order for wine can't exist if it isn't associated with a customer, having a name attribute could be mandatory for a customer.

Terminology

Primary key One or more attributes that contain values that uniquely identify each row. For example, a customer table might have the primary key of cust ID. The cust ID attribute is then assigned a unique value for each customer. A primary key is a constraint of most tables. Index A data structure used for fast access to rows in a table. An index is usually built for the primary key of each table and can then be used to quickly find a particular row. Indexes are also defined and built for other attributes when those attributes are frequently used in queries.

Terminology

Entity-relationship modeling A technique used to describe the real-world data in terms of entities, attributes, and relationships. Normalized database A correctly designed database that is created from an ER model. There are different types or levels of normalization, and a third-normal form database is generally regarded as being an acceptably designed relational database.

Managing Databases

The Data Definition Language (DDL) is the set of SQL statements used to manage a database.

Managing Databases

Creating Databases The CREATE DATABASE statement can create a new, empty database without any tables or data. mysql> CREATE DATABASE winestore; mysql> use winestore Example 3.1.

Managing Databases

Creating Tables After issuing the use Database command, you then usually issue commands to create the tables in the database. CREATE TABLE customer ( cust_id int(5) DEFAULT '0' NOT NULL auto_increment, surname varchar(50) NOT NULL, firstname varchar(50) NOT NULL, …… PRIMARY KEY (cust_id), KEY names (surname,firstname) );

Managing Databases

Altering Tables and Indexes Indexes can be added or removed from a table after creation. To add an index to the customer table, you can issue the following statement: ALTER TABLE customer ADD INDEX cities (city); To remove an index from the customer table, use the following statement: ALTER TABLE customer DROP INDEX names;

Managing Databases

Displaying Database Structure with SHOW Details of databases, tables, and indexes can be displayed with the SHOW command. The SHOW command isn't part of the SQL standard and is MySQL-specific. SHOW DATABASES Lists the databases that are accessible by the MySQL DBMS. SHOW TABLES Shows the tables in the database once a database has been selected with the use command.

Managing Databases

SHOW COLUMNS FROM tablename Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table. SHOW INDEX FROM tablename Presents the details of all indexes on the table, including the PRIMARY KEY. SHOW STATUS Reports details of the MySQL DBMS performance and statistics.

Managing Databases

Inserting, Updating, and Deleting Data The Data Manipulation Language (DML) encompasses all SQL statements used for manipulating data. There are four statements that form the DML statement set: SELECT INSERT DELETE UPDATE

Managing Databases

Inserting Data Having created a database and the accompanying tables and indexes, the next step is to insert data. Inserting a row of data into a table can follow two different approaches. First approach: INSERT INTO customer VALUES (NULL,'Marzalla','Dimitria', 'F','Mrs', '171 Titshall Cl','','','St Albans','WA', '7608','Australia','(618)63576028','', 'dimitria@lucaston.com','1969-11-08',35000);

Managing Databases

Second approach: INSERT INTO customer SET surname = 'Marzalla', firstname = 'Dimitria', initial='F', title='Mrs', addressline1='171 Titshall Cl', city='St Albans', state='WA', zipcode='7608', country='Australia', phone='(618)63576028', email='dimitria@lucaston.com', birthdate='1969-11-08', salary=35000;

Managing Databases

The first approach can actually be varied to function in a similar way to the second by including parenthesized attribute names before the VALUES keyword. INSERT INTO customer (surname,city) VALUES ('Smith','Sale');

Managing Databases

Deleting Data There is an important distinction between dropping and deleting in SQL. DROP is used to remove tables or databases. DELETE is used to remove data. DELETE FROM customer; DELETE FROM customer WHERE cust_id = 1;

Managing Databases

Updating Data Data can be updated using a similar syntax to that of the INSERT statement. UPDATE customer SET email = lower(email); UPDATE customer SET title = 'Dr' WHERE cust_id = 7;

Managing Databases

Querying with SQL SELECT The SELECT statement is used to query a database and for all output operations in SQL. SELECT surname, firstname FROM customer; SELECT * FROM region WHERE region_id<=3;

Managing Databases

Sorting and Grouping Output ORDER BY The ORDER BY clause sorts the data after the query has been evaluated. SELECT surname, firstname FROM customer WHERE title='Mr' AND city = 'Portsea' ORDER by surname;

Managing Databases

GROUP BY The GROUP BY clause is different from ORDER BY because it doesn't sort the data for output. Instead, it sorts the data early in the query process, for the purpose of grouping or aggregation. SELECT city, COUNT(*) FROM customer GROUP BY city;

Managing Databases

There are several functions that can be used in aggregation with the GROUP BY clause. Five particularly useful functions are: AVG( ) Finds the average value of a numeric attribute in a set MIN( ) Finds a minimum value of a string or numeric attribute in a set MAX( ) Finds a maximum value of a string or numeric attribute in a set SUM( ) Finds the sum total of a numeric attribute COUNT( ) Counts the number of rows in a set

Managing Databases

HAVING The HAVING clause permits conditional aggregation of data into groups. SELECT city, count(*), max(salary) FROM customer GROUP BY city HAVING count(*) > 10;

Managing Databases

DISTINCT The DISTINCT operator presents only one example of each row from a query. SELECT DISTINCT surname FROM customer;

Managing Databases

Join Queries Cartesian Product A join query is a querying technique that matches rows from two or more tables based on a join condition in a WHERE clause and outputs only those rows that meet the condition. SELECT winery_name, region_name FROM winery, region ORDER BY winery_name, region_name; The query produces all possible combinations of the four region names and 300 wineries in the sample database! In fact, the size of the output can be accurately calculated as the total number of rows in the first table multiplied by the total rows in the second table. In this case, the output is 4 x 300 = 1,200 rows.

Managing Databases

Elementary Natural Joins A cartesian product isn't the join we want. Instead, we want to limit the results to only the sensible rows. SELECT winery_name, region_name FROM winery, region WHERE winery.region_id = region.region_id ORDER BY winery_name;

• MySQL and SQL

Example 3-1

Example 3-1. The complete winestore DDL statements CREATE TABLE wine ( wine_id int(5) DEFAULT '0' NOT NULL auto_increment, wine_name varchar(50) DEFAULT '' NOT NULL, winery_id int(4), type varchar(10) DEFAULT '' NOT NULL, year int(4) DEFAULT '0' NOT NULL, description blob, PRIMARY KEY (wine_id), KEY name (wine_name) KEY winery (winery_id) );

• MySQL and SQL

Example 3-1

Example 3-1. The complete winestore DDL statements CREATE TABLE winery ( winery_id int(4) DEFAULT '0' NOT NULL auto_increment, winery_name varchar(100) DEFAULT '' NOT NULL, region_id int(4), description blob, phone varchar(15), fax varchar(15), PRIMARY KEY (winery_id), KEY name (winery_name) KEY region (region_id) );

• MySQL and SQL

Example 3-1

Example 3-1. The complete winestore DDL statements CREATE TABLE region ( region_id int(4) DEFAULT '0' NOT NULL auto_increment, region_name varchar(100) DEFAULT '' NOT NULL, description blob, map mediumblob, PRIMARY KEY (region_id), KEY region (region_name) );

• MySQL and SQL

Example 3-1

Example 3-1. The complete winestore DDL statements CREATE TABLE customer ( cust_id int(5) NOT NULL auto_increment, surname varchar(50) NOT NULL, firstname varchar(50) NOT NULL, initial char(1), title varchar(10), addressline1 varchar(50) NOT NULL, addressline2 varchar(50), addressline3 varchar(50), city varchar(20) NOT NULL, state varchar(20), zipcode varchar(5), country varchar(20), phone varchar(15), fax varchar(15), email varchar(30) NOT NULL, birth_date date( ), salary int(7), PRIMARY KEY (cust_id), KEY names (surname,firstname) );

• MySQL and SQL

Example 3-1

Example 3-1. The complete winestore DDL statements CREATE TABLE users ( cust_id int(4) DEFAULT '0' NOT NULL, user_name varchar(50) DEFAULT '' NOT NULL, password varchar(15) DEFAULT '' NOT NULL, PRIMARY KEY (user_name), KEY password (password) );

• MySQL and SQL

Example 3-1

Example 3-1. The complete winestore DDL statements CREATE TABLE grape_variety ( variety_id int(3), variety_name varchar(20), PRIMARY KEY (variety_id), KEY var (variety) );

• MySQL and SQL

Example 3-1

Example 3-1. The complete winestore DDL statements CREATE TABLE inventory ( wine_id int(5) DEFAULT '0' NOT NULL, inventory_id int(3) NOT NULL, on_hand int(5) NOT NULL, cost float(5,2) NOT NULL, case_cost float(5,2) NOT NULL, dateadded timestamp(12) DEFAULT NULL, PRIMARY KEY (wine_id,inventory_id) );

• MySQL and SQL

Example 3-1

Example 3-1. The complete winestore DDL statements CREATE TABLE orders ( cust_id int(5) DEFAULT '0' NOT NULL, order_id int(5) DEFAULT '0' NOT NULL, date timestamp(12), discount float(3,1) DEFAULT '0.0', delivery float(4,2) DEFAULT '0.00', note varchar(120), PRIMARY KEY (cust_id,order_no) );

• MySQL and SQL

Example 3-1

Example 3-1. The complete winestore DDL statements CREATE TABLE items ( cust_id int(5) DEFAULT '0' NOT NULL, order_id int(5) DEFAULT '0' NOT NULL, item_id int(3) DEFAULT '1' NOT NULL, wine_id int(4) DEFAULT '0' NOT NULL qty int(3), price float(5,2), date timestamp(12), PRIMARY KEY (cust_id,order_no,item_id) );

• MySQL and SQL

Example 3-1

Example 3-1. The complete winestore DDL statements CREATE TABLE wine_variety ( wine_id int(5) DEFAULT '0' NOT NULL, variety_id int(3) DEFAULT '0' NOT NULL, id int(1) DEFAULT '0' NOT NULL, PRIMARY KEY (wine_id, variety_id) );

• MySQL and SQL

Filesystem Check

• The first thing you need to know is that /var/lib/mysql/ibdata1 file NEVER shrinks!
• We opted to enable innodb_file_per_table to bypass ibdata1 file from growing out of control.
• However, the individual *.ibd files will grow, the optimize command will shrink the file, but will lock the table!
• ls –ltr /apps/mysql_data/ > LS_MYSQL_02dec2013.doc
• Also keep track of all the filesystems.
• df –h > DF_H_02dec2013.doc

Filesystem Check

• The first thing you need to know is that /var/lib/mysql/ibdata1 file NEVER shrinks!
• We opted to enable innodb_file_per_table to bypass ibdata1 file from growing out of control.
• However, the individual *.ibd files will grow, the optimize command will shrink the file, but will lock the table!
• ls –ltr /apps/mysql_data/ > LS_MYSQL_02dec2013.doc
• Also keep track of all the filesystems.
• df –h > DF_H_02dec2013.doc

Check Database Sizes

• Check the size of the databases daily.
• tee DATABASE_SIZES.doc
• select sysdate();
• SELECT table_schema "Data Base Name",sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB"
• FROM information_schema.TABLES
• GROUP BY table_schema;

Verify Database Sizes

Data Base Name	Data Base Size in MB	Free Space in MB
MY_DB	7770.32812500	7.00000000
performance_schema	0.00000000	0.00000000
information_schema	0.00781250	0.00000000
mysql	0.77073860	0.09775925

• 02-Dec-2013

Check the Number of Users

• It’s a good idea to know your users as you are their client. Therefore, get a daily count of users and compare it from the pervious day. Make a note of new users.

QUERY	select count(*) from mysql.user;
COUNT	21

• 03-Dec-2013

Check the Growth of Tables

• It’s a bad idea to do select count(*) of tables, but since this is a new application, knowing the table growth rate out weighs the performance hit.
• mysqlshow -uUSER -p -t radius --count

Check the Global Status

• During peak traffic, checking the global status can help identify variables that are being exceeded.
• echo "show global status\G" | mysql -uUSER -p

Check the Global Variables

• It is good to verify the variables in the my.cnf actually were picked up.
• echo "show global variables;" | mysql -uUSER -p

Show engine innodb status

• For innodb tables, getting a 60 second snapshot of the status can help identify problems.
• mysql> show engine innodb status\Gselect sleep(60); show engine innodb status\G
• *************************** 1. row ***************************
• Type: InnoDB
• Name:
• Status:
• =====================================
• 131206 21:04:28 INNODB MONITOR OUTPUT
• =====================================
• Per second averages calculated from the last 27 seconds
• -----------------
• BACKGROUND THREAD
• -----------------
• srv_master_thread loops: 47785 1_second, 47438 sleeps, 4253 10_second, 17623 background, 17623 flush
• srv_master_thread log flush and writes: 50244
• ----------

Database Table Status

• MySQL gathers Metadata about the status of the Tables.
• mysql> show table status from radius\G
• *************************** 4. row ***************************
• Name: radcheck
• Engine: InnoDB
• Version: 10
• Row_format: Compact
• Rows: 1
• Avg_row_length: 16384
• Data_length: 16384
• Max_data_length: 0
• Index_length: 16384
• Data_free: 0
• Auto_increment: 2
• Create_time: 2013-11-17 01:30:32
• Update_time: NULL
• Check_time: NULL
• Collation: latin1_swedish_ci
• Checksum: NULL
• Create_options:
• Comment:

Database Index Status

• MySQL gathers Metadata about the status of the Tables.
• mysql> show index from radius.radacct\G
• *************************** 1. row ***************************
• Table: radacct
• Non_unique: 0
• Key_name: PRIMARY
• Seq_in_index: 1
• Column_name: radacctid
• Collation: A
• Cardinality: 15293918
• Sub_part: NULL
• Packed: NULL
• Null:
• Index_type: BTREE
• Comment:
• Index_comment:

Identifying Indexes

• To check Indexes
• mysql> show create table radacct\G
• *************************** 1. row ***************************
• Table: radacct
• Create Table: CREATE TABLE `radacct` (
• `radacctid` bigint(21) NOT NULL AUTO_INCREMENT,
• `acctsessionid` varchar(64) NOT NULL DEFAULT '',
• `acctuniqueid` varchar(32) NOT NULL DEFAULT '',
• `username` varchar(64) NOT NULL DEFAULT '',
• `groupname` varchar(64) NOT NULL DEFAULT '',
• `realm` varchar(64) DEFAULT '',
• `nasipaddress` varchar(15) NOT NULL DEFAULT '',
• `nasportid` varchar(15) DEFAULT NULL,
• `nasporttype` varchar(32) DEFAULT NULL,
• `acctstarttime` datetime DEFAULT NULL,
• `acctstoptime` datetime DEFAULT NULL,
• `acctsessiontime` int(12) DEFAULT NULL,
• `acctauthentic` varchar(32) DEFAULT NULL,
• `connectinfo_start` varchar(50) DEFAULT NULL,
• `connectinfo_stop` varchar(50) DEFAULT NULL,
• `acctinputoctets` bigint(20) DEFAULT NULL,
• `acctoutputoctets` bigint(20) DEFAULT NULL,
• `calledstationid` varchar(50) NOT NULL DEFAULT '',
• `callingstationid` varchar(50) NOT NULL DEFAULT '',
• `acctterminatecause` varchar(32) NOT NULL DEFAULT '',
• `servicetype` varchar(32) DEFAULT NULL,
• `framedprotocol` varchar(32) DEFAULT NULL,
• `framedipaddress` varchar(15) NOT NULL DEFAULT '',
• `acctstartdelay` int(12) DEFAULT NULL,
• `acctstopdelay` int(12) DEFAULT NULL,
• `xascendsessionsvrkey` varchar(10) DEFAULT NULL,
• PRIMARY KEY (`radacctid`),
• UNIQUE KEY `acctuniqueid` (`acctuniqueid`),
• KEY `username` (`username`),
• KEY `framedipaddress` (`framedipaddress`),
• KEY `acctsessionid` (`acctsessionid`),
• KEY `acctsessiontime` (`acctsessiontime`),
• KEY `acctstarttime` (`acctstarttime`),
• KEY `acctstoptime` (`acctstoptime`),
• KEY `nasipaddress` (`nasipaddress`)
• ) ENGINE=InnoDB AUTO_INCREMENT=23938867 DEFAULT CHARSET=latin1

Understanding Queries

• mysql> select nasipaddress,count(username),count(distinct username) from radacct group by nasipaddress;
• +---------------+-----------------+--------------------------+
• | nasipaddress | count(username) | count(distinct username) |
• +---------------+-----------------+--------------------------+
• | 10.1.0.10 | 6045699 | 126297 |
• | 10.1.1.10 | 5778471 | 117264 |
• | 10.1.2.10 | 1075127 | 65160 |
• | 10.1.3.10 | 5187412 | 93745 |
• | 10.1.4.10 | 4726818 | 107447 |
• +---------------+-----------------+--------------------------+
• 5 rows in set (38.00 sec)
• mysql> explain select nasipaddress,count(username),count(distinct username) from radacct group by nasipaddress;
• +----+-------------+---------+-------+---------------+--------------+---------+------+----------+-------+
• | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
• +----+-------------+---------+-------+---------------+--------------+---------+------+----------+-------+
• | 1 | SIMPLE | radacct | index | NULL | nasipaddress | 17 | NULL | 15294970 | NULL |
• +----+-------------+---------+-------+---------------+--------------+---------+------+----------+-------+
• 1 row in set (0.00 sec)
• mysql>

Forcing Indexes

• You can force MySQL to use an Index and get a different Explain Plan.
• mysql> explain select nasipaddress,count(username),count(distinct username) from radacct use index (nasipaddress) group by nasipaddress;
• +----+-------------+---------+-------+---------------+--------------+---------+------+----------+-------+
• | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
• +----+-------------+---------+-------+---------------+--------------+---------+------+----------+-------+
• | 1 | SIMPLE | radacct | index | NULL | nasipaddress | 17 | NULL | 15295535 | NULL |
• +----+-------------+---------+-------+---------------+--------------+---------+------+----------+-------+
• 1 row in set (0.00 sec)
• mysql>

Profiling a Query

• mysql> set profiling=1;
• Query OK, 0 rows affected, 1 warning (0.00 sec)
• mysql> select nasipaddress,count(username),count(distinct username) from radacct group by nasipaddress;
• +---------------+-----------------+--------------------------+
• | nasipaddress | count(username) | count(distinct username) |
• +---------------+-----------------+--------------------------+
• | 10.1.0.10 | 6046041 | 126298 |
• | 10.1.1.10 | 5778779 | 117265 |
• | 10.1.2.10 | 1075229 | 65161 |
• | 10.1.3.10 | 5187580 | 93745 |
• | 10.1.4.10 | 4727107 | 107447 |
• +---------------+-----------------+--------------------------+
• 5 rows in set (37.91 sec)

Profiling a Query Continued

• mysql> show profile for query 1;
• +----------------------+-----------+
• | Status | Duration |
• +----------------------+-----------+
• | starting | 0.000054 |
• | checking permissions | 0.000007 |
• | Opening tables | 0.000011 |
• | init | 0.000014 |
• | System lock | 0.000008 |
• | optimizing | 0.000007 |
• | statistics | 0.000019 |
• | preparing | 0.000047 |
• | Sorting result | 0.000005 |
• | executing | 0.000004 |
• | Sending data | 37.915050 |
• | end | 0.000439 |
• | removing tmp table | 0.000013 |
• | end | 0.000007 |
• | query end | 0.000011 |
• | closing tables | 0.000016 |
• | freeing items | 0.000027 |
• | logging slow query | 0.000006 |
• | cleaning up | 0.000015 |
• +----------------------+-----------+
• 19 rows in set, 1 warning (0.00 sec)
• mysql>

Checking Replication

• mysql> show slave status\G
• *************************** 1. row ***************************
• Slave_IO_State: Queueing master event to the relay log
• Master_Host: mysql01-db
• Master_User: repl
• Master_Port: 3307
• Connect_Retry: 60
• Master_Log_File: blog.022416
• Read_Master_Log_Pos: 861798108
• Relay_Log_File: mysql02-relay-bin.002375
• Relay_Log_Pos: 55290803
• Relay_Master_Log_File: blog.022416
• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Replicate_Do_DB:
• Replicate_Ignore_DB:
• Replicate_Do_Table:
• Replicate_Ignore_Table:
• Replicate_Wild_Do_Table:
• Replicate_Wild_Ignore_Table:
• Last_Errno: 0
• Last_Error:
• Skip_Counter: 0
• Exec_Master_Log_Pos: 861797661
• Relay_Log_Space: 189530366
• Until_Condition: None
• Until_Log_File:
• Until_Log_Pos: 0
• Master_SSL_Allowed: No
• Master_SSL_CA_File:
• Master_SSL_CA_Path:
• Master_SSL_Cert:
• Master_SSL_Cipher:
• Master_SSL_Key:
• Seconds_Behind_Master: 0
• Master_SSL_Verify_Server_Cert: No
• Last_IO_Errno: 0
• Last_IO_Error:
• Last_SQL_Errno: 0
• Last_SQL_Error:
• Replicate_Ignore_Server_Ids:
• Master_Server_Id: 991

Repairing Replication

• If Replication Breaks, you MIGHT be able to by-pass the error, otherwise you will have to rebuild Replication from the Master.
• mysql>SET GLOBAL sql_slave_skip_counter = 1;
• mysql>start slave;
• mysql>show slave status\G

Helpful Unix Commands

• Get to know your filesystem (/etc/fstab), eg. ext3 vs ext4.
• fs_spec- desc FS, fs_file – desc mount point, fs_vfstype – desc the type of FS, fs_mntops – desc mount option, fs_freq – desc determines FS dump, fs_passno – used by fsck
• To increase I/O performance change the fs_mntops from the defaults.
• /dev/mapper/osvg-root / ext4 defaults 1 1
• UUID=c96df9ac-787b-4388-9de9-74c292692f9b /boot ext3 defaults 1 2
• /dev/mapper/osvg-opt /opt ext4 defaults 1 2
• /dev/mapper/osvg-var /var ext4 defaults 1 2
• /dev/mapper/appsvg-apps /apps ext4 defaults 1 2
• /dev/sda2 /apps/full_backup ext4 defaults 1 2
• tmpfs /dev/shm tmpfs defaults 0 0
• devpts /dev/pts devpts gid=5,mode=620 0 0
• sysfs /sys sysfs defaults 0 0
• proc /proc proc defaults 0 0
• LABEL=swap swap swap defaults 0 0

ext3 vs ext4

• ext3
• Introduced in 2001 ext3 supports journaling which improves speed. There are three levels of journaling for ext3 ” lowest, medium, highest ” risk check.
• ext4
• With the stable release of ext4 in 2008, this becomes one of the best file system out there.  Transferring speed is really good, but it’s not depending on the file system itself, it also relies on hardware specifications, operating system, Kernel and many more dependencies.

ext3 vs ext4

	Ext3	ext4
Stands For	Third Extended Filesystem	Fourth Extended Filesystem
Original OS	Linux	Linux
Max Volume Size	256TB	1 EB
Max File Size	16GB – 2TB	16TB
Max Filename Length	254 bytes	256 bytes
Journaling	Yes	Yes

IOSTAT -dx

• iostat –dx 2 20
• Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
• sda 0.00 0.01 0.05 0.04 28.98 27.89 668.00 0.00 1.71 0.65 0.01
• dm-0 0.01 83.08 0.03 24.24 2.22 819.96 33.88 0.03 1.36 0.90 2.18
• dm-1 0.00 0.00 0.01 0.00 0.05 0.00 4.06 0.00 24.57 24.56 0.03
• dm-2 0.01 14.16 0.05 4.13 2.20 144.24 35.01 0.08 18.36 1.75 0.73
• dm-3 0.00 0.00 0.01 0.00 0.01 0.00 1.21 0.00 5.21 1.74 0.00
• dm-4 0.00 0.00 0.01 0.00 0.51 0.00 35.39 0.00 4.39 4.02 0.01
• dm-5 0.00 0.00 0.02 107.32 2.19 819.96 7.66 0.19 1.75 0.20 2.17
• dm-6 0.00 0.00 0.00 0.00 0.02 0.00 5.35 0.00 6.55 3.70 0.00
• dm-7 0.00 0.00 0.05 18.29 2.16 144.24 7.98 0.43 23.40 0.40 0.73
• dm-8 0.00 0.00 0.01 0.00 0.49 0.00 40.33 0.00 4.52 3.53 0.00
• dm-9 0.00 0.00 0.03 1.29 1.20 10.36 8.76 0.04 31.10 1.46 0.19
• sdf 0.00 0.00 0.03 2.20 1.12 72.19 32.97 0.01 3.59 0.56 0.12
• sdg 0.00 0.00 0.01 0.00 0.26 0.00 33.39 0.00 3.74 3.74 0.00
• sdh 0.00 0.00 0.01 14.27 1.13 410.12 28.78 0.01 0.98 0.71 1.02
• sdi 0.00 0.00 0.01 0.00 0.05 0.00 5.44 0.00 1.72 1.72 0.00
• dm-10 0.00 0.00 0.00 0.00 0.48 0.00 99.96 0.00 7.45 5.03 0.00
• dm-11 0.00 0.00 0.01 16.55 0.50 132.43 8.03 0.38 23.24 0.37 0.62
• dm-12 0.00 0.00 0.01 0.18 0.40 1.46 9.77 0.00 17.27 8.02 0.15
• dm-13 0.00 0.00 0.01 103.01 2.14 819.95 7.98 0.19 1.82 0.21 2.17
• sdb 0.00 0.00 0.00 1.03 0.03 30.27 29.41 0.00 3.86 0.48 0.05
• sdc 0.00 0.00 0.00 0.00 0.01 0.00 4.85 0.00 3.57 3.57 0.00
• sdd 0.00 0.00 0.00 6.67 0.01 192.74 28.89 0.01 0.84 0.61 0.40
• sde 0.00 0.00 0.00 0.00 0.01 0.00 8.00 0.00 1.66 1.66 0.00

VMSTAT

• vmstat 2 20
• procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
• r b swpd free buff cache si so bi bo in cs us sy id wa st
• 2 0 0 147798752 741408 101980384 0 0 1 16 0 0 0 0 99 0 0
• 0 0 0 147799344 741408 101980400 0 0 0 626 478 1977 0 0 100 0 0
• 0 0 0 147798640 741408 101980400 0 0 0 76 306 218 0 0 100 0 0
• 1 0 0 147799040 741408 101980400 0 0 0 0 145 185 0 0 100 0 0

TOP

• You need to know MySQL pre-allocates Memory at startup.
• top
• top - 22:03:56 up 69 days, 20:02, 1 user, load average: 0.34, 0.26, 0.18
• Tasks: 832 total, 1 running, 831 sleeping, 0 stopped, 0 zombie
• Cpu(s): 0.1%us, 0.4%sy, 0.0%ni, 99.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
• Mem: 264493968k total, 116693676k used, 147800292k free, 741404k buffers
• Swap: 8393944k total, 0k used, 8393944k free, 101977944k cached
• PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
• 13299 root RT 0 458m 70m 42m S 4.3 0.0 982:15.40 corosync
• 3761 william. 20 0 15560 1840 948 R 0.7 0.0 0:00.11 top
• 58946 root 20 0 26216 1772 704 S 0.7 0.0 87:28.83 cmaperfd
• 8163 root 20 0 106m 2460 1208 S 0.3 0.0 80:31.19 scsm
• 15770 mysql 19 -1 22.8g 9.6g 7188 S 0.3 3.8 10178:15 mysqld
• 1 root 20 0 19228 1528 1236 S 0.0 0.0 8:08.60 init

NETSTAT

• Netstat can be used to help Identify who is accessing the database remotely.
• netstat -ntp |grep :3306
• (No info could be read for "-p": geteuid()=1458 but you should be root.)
• tcp 0 0 10.65.105.137:3306 10.71.0.25:33876 ESTABLISHED -
• tcp 0 0 10.65.105.137:3306 10.75.0.24:45161 ESTABLISHED -
• tcp 0 0 10.65.105.137:3306 10.74.0.24:50678 ESTABLISHED -
• tcp 0 0 10.65.105.137:3306 10.72.0.25:56946 ESTABLISHED -
• tcp 0 0 10.65.105.137:3306 10.73.0.25:46129 ESTABLISHED -
• tcp 0 0 10.65.105.137:3306 10.65.103.183:51526 ESTABLISHED -
• tcp 0 0 10.65.105.137:3306 10.73.0.24:58631 ESTABLISHED -
• tcp 0 0 10.65.105.137:3306 10.71.0.24:34191 ESTABLISHED -
• tcp 0 0 10.65.105.137:3306 10.75.0.25:33685 ESTABLISHED -
• tcp 0 0 10.65.105.137:3306 10.74.0.25:41889 ESTABLISHED -
• tcp 0 0 10.65.105.137:3306 10.72.0.24:48392 ESTABLISHED -
• tcp 0 0 10.65.105.137:3306 10.65.103.182:46907 ESTABLISHED -

MySQL Performance Tuning

• The Default MySQL Configuration file is too small for most Applications. There are a handful of parameters that will make your life as a DBA have more time to surf the web instead of doing DBA work. The following changes should give you the most performance boost.

MySQL Configuration File

• The my.cnf file can live where ever you point it to.
• PROD mysqlaaa01.prod:william.mayall[~/scripts]$ locate my.cnf
• /apps/scripts/.my.cnf
• /apps/scripts/my.cnf
• /apps/scripts/copy_env/.my.cnf
• /etc/my.cnf
• /home/william.mayall/.my.cnf
• /home/william.mayall/my.cnf
• /home/william.mayall/my.cnf.05aug2013
• /home/william.mayall/copy_env/.my.cnf
• /usr/my.cnf
• Which my.cnf do you chose?
• PROD mysqlaaa01.prod:william.mayall[~/scripts]$ ps -ef |grep mysql
• root 15365 1 0 Sep30 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/usr/my.cnf --pid-file=/var/run/cluster/mysql/mysql:mysql_conf.pid --bind-address=10.65.105.137
• mysql 15770 15365 10 Sep30 ? 7-01:38:30 /usr/sbin/mysqld --defaults-file=/usr/my.cnf --basedir=/usr --datadir=/apps/mysql_data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --bind-address=10.65.105.137 --log-error=/var/log/mysql/mysql.er --pid-file=/var/run/cluster/mysql/mysql:mysql_conf.pid

MySQL Configuration File

• #cat /usr/my.cnf
• /usr/my.cnf
• [mysqld]
• basedir=/usr
• datadir=/apps/mysql_data
• plugin-dir=/usr/lib64/mysql/plugin
• user=mysql
• log-error=/var/lib/mysql/mysql.err
• pid-file=/var/lib/mysql/mysql.pid
• ignore-db-dir=lost+found
• log-bin=mysqld-bin
• server-id=10
• max_allowed_packet=128M
• max_connections=1000
• expire_logs_days=4
• binlog_cache_size = 8388608
• bulk_insert_buffer_size = 268435456
• innodb_buffer_pool_size = 17179869184
• innodb_flush_log_at_trx_commit = 2
• key_buffer_size = 2147483648
• max_heap_table_size = 33554432
• thread_cache_size = 30
• tmp_table_size = 33554432
• innodb_log_file_size = 134217728
• innodb_file_per_table = 1
• [mysqld_safe]
• basedir=/usr
• datadir=/apps/mysql_data
• pid-file=/var/log/mysql/mysql.pid
• log-error=/var/log/mysql/mysql.er

innodb_flush_log_at_trx_commit

• For I/O Bound performance changing this parameter from 1 to 2 will give you the best performance increase.
• The Default value of 1 means each update transaction commits (or each statement outside of the transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications are OK with this value set to 2 which means do not flush log to the disk, but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not lose more than 1-2 seconds worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. The value set to 2 only causes data loss with full OS crash without battery backed up RAM or Disks.

innodb_log_file_size

• I’ve seen innodb_log_file_size to be the second best performance increaser.
• Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increases recovery times so be careful. I normally use values 64M-512M depending on server size. The current size is 100M for db4, which is fine.

innodb_buffer_pool_size

• Again, the default of 8M is just too small. This is like the SGA for Oracle. Would you create an 8M SGA for a 2GB Oracle database?
• It is best practices to cache the entire database, there is no reason not to IF you have enough RAM.

key_buffer_size

• The key_buffer_size is for MyISAM Tables, including TEMP & Memory Tables.
• Tmp_table_size
• Max_heap_size
• The max_heap_size must be equal to or greater than the tmp_table_size.

max_allowed_packet

• The default of 1MB is too small, genereally make the max_allowed_packet 16MB – 256MB.
• The Slave variables should be greater or equal to the Master’s variables.

table_open_cache

• This is a tricky one. You can actually see a performance hit if you get this wrong!
• Increase it gradually over time, check “SHOW GLOBAL STATUS” check the Opened_tables value, you do NOT want Opened_tables increasing during peak times.
• I suggest setting the value to 128 and go from there. It currently is set to 64 on db4.

query_cache_size

• The Query Cache will put often used queries into cache.
• I noticed the user queries using the NO_CACHE Hint, but enabling cache could be of benefit to Ad-Hoc queries.
• I suggest setting the value to 8M. It is currently disabled in db4.

thread_cache_size

• Thread creation/destructions can be expensive, which happens at each connect/disconnect. I normally set this value to at least 16. If the application has large jumps in the amount of concurrent connections and when I see fast growth of Threads_created variable I set it higher. The goal is not to have threads created in normal operation.

Scripts for Checking Bottlenecks

• Check iostat
• #!/bin/sh
• #./run_iostat.sh > IOSTAT_REPORT.doc &
• x=1
• while [ $x -le 60 ]
• do
• echo "START RUN TIME"
• date
• /usr/bin/iostat -dx 2 20
• x=$(( $x + 1 ))
• echo "END RUN TIME“
• date
• sleep 60
• done

Scripts Continued

• Check vmstat
• #!/bin/sh
• #./run_vmstat.sh > VMSTAT_REPORT.doc &
• x=1
• while [ $x -le 60 ]
• do
• echo "START RUN TIME"
• date
• /usr/bin/vmstat 2 20
• x=$(( $x + 1 ))
• echo "END RUN TIME"
• date
• sleep 60
• done

Scripts Continued

• Show MySQL Status
• #!/bin/sh
• #./run_show_global_status.ksh > GLOBAL_STATUS_REPORT.doc &
• x=1
• while [ $x -le 60 ]
• do
• echo "START RUN TIME"
• date
• echo "show global status\G" | mysql -uUSER –pPASSWD
• x=$(( $x + 1 ))
• echo "END RUN TIME"
• date
• sleep 60
• done

Scripts Continued

• Show MySQL Full Processlist
• #!/bin/sh
• #./run_show_processlistsh > PROCESSLIST_REPORT.doc &
• x=1
• while [ $x -le 60 ]
• do
• echo "START RUN TIME"
• date
• echo "show full processlist;" | mysql -uUSER –pPASSWD
• x=$(( $x + 1 ))
• echo "END RUN TIME“
• date
• sleep 1
• done

Security and Integrity Functions in Database Administration

• IS 257 - Fall 2005

• Review
• MySQL Intro
• Data Integrity
• Security Management
• Backup and Recovery

Security and Integrity Functions in Database Administration

• IS 257 - Fall 2005

• Review
• MySQL Intro
• Data Integrity
• Security Management
• Backup and Recovery

MySQL

• IS 257 - Fall 2005

• The tag-line at http://www.mysql.com is
• The world's most popular open source database
• It is true, it is the most widely used open source database system with users and uses that range from individuals to major corporations and includes…
• Evite
• Friend Finder Network
• Friendster
• Google (not for search though )
• PriceGrabber.com
• Ticketmaster
• Yahoo!
• The US Census bureau
• and many, many others

MySQL myths

• IS 257 - Fall 2005

• The MySQL.com web site contains a list of common myths and misconceptions about MySQL and refutes them:
• MYTH: MySQL is a new, untested database management system
• MYTH: MySQL doesn’t support transactions like other proprietary database engines (it is supposed to be in the version we use here)
• MYTH: MySQL is only for small, departmental, or web-based applications
• MYTH: MySQL doesn’t offer enterprise-class features
• MYTH: MySQL doesn’t have the type of support large corporations need
• MYTH: MySQL isn’t open source any more

MySQL documentation

• IS 257 - Fall 2005

• MySQL is available for download from MySQL.com
• In addition that site has complete online documentation for the MySQL system and for the mysql client program in their ‘Developer Zone’
• The online manuals are quite readable and have lot of examples to help you

MySQL Demo

• IS 257 - Fall 2005

• Since the system wasn’t behaving last week we will look at MySQL online today

Security and Integrity Functions in Database Administration

• IS 257 - Fall 2005

• Data Integrity (review)
• Security Management
• Backup and Recovery

Data Integrity

• IS 257 - Fall 2005

• Intrarecord integrity (enforcing constraints on contents of fields, etc.)
• Referential Integrity (enforcing the validity of references between records in the database)
• Concurrency control (ensuring the validity of database updates in a shared multiuser environment)

Integrity Constraints (review)

• IS 257 - Fall 2005

• The constraints we wish to impose in order to protect the database from becoming inconsistent.
• Five types
• Required data
• attribute domain constraints
• entity integrity
• referential integrity
• enterprise constraints

Required Data

• IS 257 - Fall 2005

• Some attributes must always contain a value -- they cannot have a NULL value
• For example:
• Every employee must have a job title.
• Every diveshop diveitem must have an order number and an item number

Attribute Domain Constraints

• IS 257 - Fall 2005

• Every attribute has a domain, that is a set of values that are legal for it to use
• For example:
• The domain of sex in the employee relation is “M” or “F”
• Domain ranges can be used to validate input to the database

Entity Integrity

• IS 257 - Fall 2005

• The primary key of any entity:
• Must be Unique
• Cannot be NULL

Referential Integrity

• IS 257 - Fall 2005

• A “foreign key” links each occurrence in a relation representing a child entity to the occurrence of the parent entity containing the matching candidate (usually primary) key
• Referential Integrity means that if the foreign key contains a value, that value must refer to an existing occurrence in the parent entity
• For example:
• Since the Order ID in the diveitem relation refers to a particular diveords item, that item must exist for referential integrity to be satisfied.

Referential Integrity

• IS 257 - Fall 2005

• Referential integrity options are declared when tables are defined (in most systems)
• There are many issues having to do with how particular referential integrity constraints are to be implemented to deal with insertions and deletions of data from the parent and child tables.

Insertion rules

• IS 257 - Fall 2005

• A row should not be inserted in the referencing (child) table unless there already exists a matching entry in the referenced table
• Inserting into the parent table should not cause referential integrity problems
• Sometimes a special NULL value may be used to create child entries without a parent or with a “dummy” parent

Deletion rules

• IS 257 - Fall 2005

• A row should not be deleted from the referenced table (parent) if there are matching rows in the referencing table (child)
• Three ways to handle this
• Restrict -- disallow the delete
• Nullify -- reset the foreign keys in the child to some NULL or dummy value
• Cascade -- Delete all rows in the child where there is a foreign key matching the key in the parent row being deleted

Referential Integrity

• IS 257 - Fall 2005

• This can be implemented using external programs that access the database
• newer databases implement executable rules or built-in integrity constraints (e.g. Access and Oracle)

Enterprise Constraints

• IS 257 - Fall 2005

• These are business rule that may affect the database and the data in it
• for example, if a manager is only permitted to manage 10 employees then it would violate an enterprise constraint to manage more

Data and Domain Integrity

• IS 257 - Fall 2005

• This is now increasing handled by the database. In Oracle, for example, when defining a table you can specify:
• CREATE TABLE table-name (
• attr2 attr-type NOT NULL, forbids NULL values
• attrN attr-type CHECK (attrN = UPPER(attrN) verifies that the data meets certain criteria
• attrO attr-type DEFAULT default_value); Supplies default values

Referential Integrity

• IS 257 - Fall 2005

• Ensures that dependent relationships in the data are maintained. In Oracle, for example:
• CREATE TABLE table-name (
• attr1 attr-type PRIMARY KEY,
• attr2 attr-type NOT NULL,
• …, attrM attr-type REFERENCES owner.tablename(attrname) ON DELETE CASCADE, …

Concurrency Control

• IS 257 - Fall 2005

• The goal is to support access by multiple users to the same data, at the same time
• It must assure that the transactions are serializable and that they are isolated
• It is intended to handle several problems in an uncontrolled system
• Specifically:
• Lost updates
• Inconsistent data states during access
• Uncompleted (or committed) changes to data

No Concurrency Control: Lost updates

• IS 257 - Fall 2005

• Read account balance (balance = $1000)
• Withdraw $200 (balance = $800)
• Write account balance (balance = $800)

• Read account balance (balance = $1000)
• Withdraw $300 (balance = $700)
• Write account balance (balance = $700)

• John

• Marsha

• ERROR!

Concurrency Control: Locking

• IS 257 - Fall 2005

• Locking levels
• Database
• Table
• Block or page
• Record
• Field
• Types
• Shared (S locks)
• Exclusive (X locks)

Concurrency Control: Updates with X locking

• IS 257 - Fall 2005

• Lock account balance
• Read account balance (balance = $1000)
• Withdraw $200 (balance = $800)
• Write account balance (balance = $800)
• Unlock account balance

• Read account balance (DENIED)
• Lock account balance
• Read account balance (balance = $800)
• etc...

• John

• Marsha

Concurrency Control: Deadlocks

• IS 257 - Fall 2005

• Place S lock
• Read account balance (balance = $1000)
• Request X lock (denied)
• wait ...

• Place S lock
• Read account balance (balance = $1000)
• Request X lock (denied)
• wait...

• John

• Marsha

• Deadlock!

Concurrency Control

• IS 257 - Fall 2005

• Avoiding deadlocks by maintaining tables of potential deadlocks and “backing out” one side of a conflicting transaction

Transaction Control in ORACLE

• IS 257 - Fall 2005

• Transactions are sequences of SQL statements that ORACLE treats as a unit
• From the user’s point of view a private copy of the database is created for the duration of the transaction
• Transactions are started with SET TRANSACTION, followed by the SQL statements
• Any changes made by the SQL are made permanent by COMMIT
• Part or all of a transaction can be undone using ROLLBACK

Transactions in ORACLE

• IS 257 - Fall 2005

• COMMIT; (I.e., confirm previous transaction)
• SET TRANSACTION READ ONLY;
• SELECT NAME, ADDRESS FROM WORKERS;
• SELECT MANAGER, ADDRESS FROM PLACES;
• COMMIT;
• Freezes the data for the user in both tables before either select retrieves any rows, so that changes that occur concurrently will not show up
• Commits before and after ensure any uncompleted transactions are finish, and then release the frozen data when done

Transactions in ORACLE

• IS 257 - Fall 2005

• Savepoints are places in a transaction that you may ROLLBACK to (called checkpoints in other DBMS)
• SET TRANACTION…;
• SAVEPOINT ALPHA;
• SQL STATEMENTS…
• IF (CONDITION) THEN ROLLBACK TO SAVEPOINT ALPHA;
• SAVEPOINT BETA;
• SQL STATEMENTS…
• IF …;
• COMMIT;

Security and Integrity Functions in Database Administration

• IS 257 - Fall 2005

• Data Integrity
• Security Management
• Backup and Recovery

Database Security

• IS 257 - Fall 2005

• Views or restricted subschemas
• Authorization rules to identify users and the actions they can perform
• User-defined procedures (with rule systems or triggers) to define additional constraints or limitations in using the database
• Encryption to encode sensitive data
• Authentication schemes to positively identify a person attempting to gain access to the database

Views

• IS 257 - Fall 2005

• A subset of the database presented to some set of users
• SQL:
• CREATE VIEW viewname AS SELECT field1, field2, field3,…, FROM table1, table2 WHERE <where clause>;
• Note: “queries” in Access function as views

Restricted Views

• IS 257 - Fall 2005

• Main relation has the form:

Name	C_name	Dept	C_dept	Prof	C_prof	TC
J Smith	S	Dept1	S	Cryptography	TS	TS
M Doe	U	Dept2	S	IT Security	S	S
R Jones	U	Dept3	U	Secretary	U	U

• U = unclassified : S = Secret : TS = Top Secret

Restricted Views

• IS 257 - Fall 2005

NAME	Dept	Prof
J Smith	Dept1	---
M Doe	Dept2	IT Security
R Jones	Dept3	Secretary

NAME	Dept	Prof
M Doe	---	---
R Jones	Dept3	Secretary

• S-view of the data

• U-view of the data

Authorization Rules

• IS 257 - Fall 2005

• Most current DBMS permit the DBA to define “access permissions” on a table by table basis (at least) using the GRANT and REVOKE SQL commands
• Some systems permit finer grained authorization (most use GRANT and REVOKE on variant views

Security and Integrity Functions in Database Administration

• IS 257 - Fall 2005

• Data Integrity
• Security Management
• Backup and Recovery

Database Backup and Recovery

• IS 257 - Fall 2005

• Backup
• Journaling (audit trail)
• Checkpoint facility
• Recovery manager

• Backing Up MySQL Data (Copy Method)

• Database Copy Procedure
• Shut down the MySQL service
• Copy data files to backup location http://localhost/phpmyadmin/
• On Windows:
• C:\Program Files\MySQL\MySQL Server 5.1\data\database_name
• C:\xampp\mysql\data
• On Unix:
• /var/lib/mysql

• Restoring MySQL Data (Copy Method)

• Restoring Database Procedure:
• All files must be replaced in the same directory from which they were backed up.
• Restart database service

• Copy Method Advantages/Disadvantages

• Advantages:
• Fast
• Easy
• Disadvantages
• Not recommended for moving or upgrading databases
• All or nothing data restoration

• mysqldump Backup (Preferred Method)

• Creating mysqldump Backups
• Description: The SQL commands used to create the tables and the data can be redirected to a file. That file can then be used to rebuild the database.
• Syntax:
• mysqldump –u user –p db_name
• mysqldump –u user –p db_name table_name

• Creating mysqldump Backups

• Examples:
• Backup one database:
• mysqldump –u root –p notedb > notedb_backup.sql
• Backup one database table:
• mysqldump –u root –p notedb notecard > notecard_backup.sql

• Creating mysqldump Backup (cont.)

• ma w/o Data:
• mysqlExamples:
• Backup all databases:
• mysqldump –u root –p –-all-databases > full_backup.sql
• Backup Schedump –u root –p -–not-data > schema_only.sql
• Backup Data Only w/o Schema:
• mysqldump –u root –p -–no-create-info > data_only.sql

• mysqldump Advantages/Disadvantages

• Advantages:
• SQL generated can be used to migrate database to a new version or architecture
• Can be used for a partial backup/restore
• Scriptable
• Disadvantages
• Not point and click
• Note: Similar capability is available via phpmyadmin

• Restoring mysqldump Backups

• Restore databases created with --all-databases:
• mysql –u root –p < full_backup.sql
• Restore only one database:
• mysql –u root –p -D notedb < notedb_backup.sql

Disaster Recovery Planning

• IS 257 - Fall 2005

• Testing and
• Training

• Procedures
• Development

• Budget &
• Implement

• Plan
• Maintenance

• Recovery
• Strategies

• Risk
• Analysis

• From Toigo “Disaster Recovery Planning”

Threats to Assets and Functions

• IS 257 - Fall 2005

• Water
• Fire
• Power Failure
• Mechanical breakdown or software failure
• Accidental or deliberate destruction of hardware or software
• By hackers, disgruntled employees, industrial saboteurs, terrorists, or others

Threats

• IS 257 - Fall 2005

• Between 1967 and 1978 fire and water damage accounted for 62% of all data processing disasters in the U.S.
• The water damage was sometimes caused by fighting fires
• More recently improvements in fire suppression (e.g., Halon) for DP centers has meant that water is the primary danger to DP centers

Kinds of Records

• IS 257 - Fall 2005

• Class I: VITAL
• Essential, irreplaceable or necessary to recovery
• Class II: IMPORTANT
• Essential or important, but reproducible with difficulty or at extra expense
• Class III: USEFUL
• Records whose loss would be inconvenient, but which are replaceable
• Class IV: NONESSENTIAL
• Records which upon examination are found to be no longer necessary

Offsite Storage of Data

• IS 257 - Fall 2005

• Early offsite storage facilities were often intended to survive atomic explosions
• PRISM International directory
• PRISM = Professional Records and Information Services Management
• http://www.prismintl.org/
• Mirror sites (Hot sites)