SQL Trigger

What is Trigger?

It is a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table. 

 Syntax for creating aTRIGGER:

(BEFORE)_tableName_(INSERT|UPDATE|DELETE)

(AFTER)_tableName_(INSERT|UPDATE|DELETE)

The listed below are the restrictions for creating a trigger

      1. It is not allowed to create a trigger for views or temporary table.

      2. It is not allowed to use transaction in a trigger.

      3. Return statements is disallowed in a trigger.

      4. Creating a trigger for a database table causes the query cache invalidated.

      5. All trigger for a database table must have unique name

Mysql Create View

Create View for Department

Create view dept as select dname as ‘department’, count(fname) as ‘number of employees’, avg(salary) as ‘salary’ from department join employee on dno=dnumber group by dname;

 

Create View for Employee

Create view name as select concat (fname,’’,minit,’’,lname) as ‘fullname’, salary as ‘salary’, dname as ‘department’ from employee join department on dnumber=dno;

Create View for Project 

Create view proj as select pname as ‘project’, count(fname) as ‘number of employees’ from project join employee on dnum=dno group by pname;

Replication

What is Replication?

Replication is a way to configure a MySQL database to update on a remote server whenever the local client is updated.Allows you to take one database, make an exact copy of it on another server, and set one of them (the slave) to take all its updates from the other (the master).  Electronic copying data from a database in one computer or server  to a database in another.Allows you to take one database, make an exact copy of it on another server, and set one of them (the slave) to take all its updates from the other (the master).

·         Setting up Replication

In my.ini , search for this line:

#skip-networking

#bind-address = 127.0.0.1

Remove the # to add this on the list.

Setting up Replication :

Configuring Master:

·         In your my.ini file, add this:

            [mysqld]

            log-bin = richard-PC_binary

            binlog-do-db=exampledb        

            server-id=1 

log-bin indicates that the master will use binary update logging, binlog-do-db indicates which database we will use and server-id is a unique number to differentiate each of the master and slave machines. By convention, the master is usually set to 1, and the slaves from 2 upward:

·         In the mysql shell, we will grant a permission to a slave to replicate. The slave user will be replication_user, with a password of replication_pwd:

·         GRANT REPLICATION SLAVE ON *.* TO replication_user IDENTIFIED BY 'replication_pwd';

·         GRANT RELOAD ON *.* TO replication_user IDENTIFIED BY 'replication_pwd';

·         GRANT SUPER ON *.* TO replication_user IDENTIFIED BY 'replication_pwd';

FLUSH privileges. Type this command to complete the setup or update.

·         Then use the database you want to replicate

>use replicationdb;

>show master status;

>quit;

·         master-host = 192.168.4.100

·         master-user = replication_user

·         master_password = replication_pwd

·         server-id = 3

·         master-connect-retry=60

·         replicate-do-db = replication_db

There are two possibilities to get existing tables:

·         make a database dump

·         mysqldump -u root -proot  -- opt replicationdb > C:replicationdb.sql

Transfer this file to your slave server

·         load data from master

If you want to go the LOAD DATA FROM MASTER; 

way then there is nothing you must do right now.

*

·         unlock the tables in replicationdb:

·         mysql -u root –p

·         Enter password:

·         UNLOCK TABLES;

·         quit;

Now the configuration on the master is finished. On to the slave...

Now we will configure the slave:

First create database replicationdb then quit mysql by “quit” command

In the my.ini or my.cfg file, put these lines

Replace the master-host setting with the actual IP address of the master server, Master-user is the user created on the master for replication as well as the master-password. The server_id can be any number, as long as it's not the same as the server_id on the master: 

·         Then quit and copy the sql

·         Then on the mysql shell copy the replicationdb data from the master to the slave

>mysql -u root replicationdb < C:replicationdb.sql

·         Then we restart MySQL

LOAD DATA FROM MASTER

>mysql -u root

>load data from master;

>quit;

You can now use phpMyadmin to see the tables master and slave replicationdb.

·         Next step is to stop the slave to finish up the Slave configuration.

mysql -u root -p
Enter password:
SLAVE STOP;

·         replace the values appropriately:

>CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER=‘replication_user', MASTER_PASSWORD='replication_pwd', MASTER_LOG_FILE=‘allen_binary.000001', MASTER_LOG_POS=106;

·         MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).

·         MASTER_USER is the user we granted replication privileges on the master.

·         MASTER_PASSWORD is the actual password gave to the user on the master.

·         MASTER_LOG_FILE is the file MySQL gave, back when you ran SHOW MASTER STATUS; on the master.

·         MASTER_LOG_POS is the position MySQL gave back when you also ran SHOW MASTER STATUS; on the master.

·         Then start the slave.

>START SLAVE;

And now, be aware that if your master server has already been using binary logging, you'll need to reset the binary log with RESET MASTER so that the slave can start updating from the beginning of the first binary log. Now start the slave server and connect. Once you've connected, check the slave status to see if replication has begun properly:

Master Configuration File Options

·         log-bin=filenameActivates binary logging

log-bin-index=filename

Specifies the name of the binary log index file

Master Configuration File Options

·         sql-bin-update-same

-If set, setting SQL_LOG_BIN to either 1 or 0 will automatically set SQL_ LOG_UPDATE to the same value, and vice versa. SQL_LOG_UPDATE should not be needed any more, so this option is unlikely to be used.

-binlog-do-db=database_name

-Only logs updates to the binary log from the database_name database. All other databases are   ignored.

·         binlog-ignore-db= database_name

-Logs all updates to the binary log except from the database_name database. You can also set the database to ignore on the slave.

Slave Configuration File Options

·         master-host=host

-Specifies the hostname or IP address of the master to which to connect.

-master-user=username

-Specifies the username the slave will connect to the master with.

-master-password=password

-Specifies the password with which the slave will connect to the master.

·         master-port=portnumber

-Specifies the port the master listens on (defaults to the value of MYSQL_ PORT, usually 3306).

·         master-connect-retry= seconds

-MySQL will wait this many seconds before trying to reconnect (default 60).

-master-ssl

-Specifies that replication take place using Secure Sockets Layer (SSL).

·         master-ssl-key=key_name

-If SSL is set to be used (the master-ssl option), this specifies the master SSL key filename.

-master-ssl-cert= certificate_name

-If SSL is set to be used (the master-ssl option), this specifies the master SSL certificate name.

-master-info-file=filename

-Specifies the master information file (default master.info in the data directory), which keeps track of the point in the binary logs that the slave is at in the replication process.

·         report-host

-Specifies the hostname or IP address that the slave will announce itself as to the master

-report-port

-Specifies the port for connecting to the slave reported to the master.

replicate-do-table= db_name.table_name

-Ensures that the slave only replicates the specified table name, from the specified database. You can use this option multiple times to replicate multiple tables.

·         replicate-ignore-table= db_name.table_name

-Tells the slave not to replicate a statement that updates the specified table (even if other tables are also updated by the same statement). You can specify multiple instances of this option.

·         replicate-wild-do-table= db_name.table_name

-Tells the slave to replicate statements only where they match the specified table (similar to the replicate_do_table option), but where the matchtakes into account wildcards. For example, where the table name isdb%.tb%, the match will apply to any database beginning with the letters db, and any table beginning with the letters tb.

·         replicate-wild-ignore-table=db_ name.table_name

-Tells the slave not to replicate a statement that updates the specified table, even if other tables are also updated by the same statement, similar to the replicate-ignore-table option, except that wildcards are taken into account. For example, where the table name is db%.tb% replication will not be performed where the database begins with the letters db, and the table begins with the letters tb). You can specify multiple instances of this option.

·         replicate-ignore-db= database_name

-Tells the slave not to replicate any statement when the current database is database_name. You can use this option multiple times to specify multiple databases to ignore.

·         replicate-do-db= database_name

-Tells the slave thread to replicate a statement only when the database is database_name. You can use this option multiple times to replicate multiple databases.

·         log-slave-updates

Tells the slave to log replicated updates to the binary log. Not set by default. If you plan to use the slave as a master to another slave, you'll need to set this option.

·         replicate-rewrite-db= master_database-> slave_database

-If the database on your slave has a different name to that on the master, you'll need to map the relationship with this option.

·         slave-skip-errors= [err_code1,err_ code2,... | all]

When replication encounters an error, it will stop (since an error usually means the data is inconsistent, and manual steps are needed). This option tells MySQL to continue replicating if the error is one of the listed errors. Error codes are supplied as a number (the same number given in the error log) and separated by a comma. You can also use the all option to cater for any possible errors. You should not normally use this option, as mistaken use of it can lead to your data getting out of sync with the master, with no realistic way of getting it back in sync besides recopying the master data.

·         skip-slave-start

With this option set, replication will not begin when the server starts. You can manually begin it with the SLAVE START command.

·         slave_compressed_protocol=#

If set to 1, then MySQL uses compression to transfer the data between slave and master if both servers support this.

·         slave_net_timeout=#

Determines the number of seconds to wait for more data from the master before a read is aborted.

Slave and Master Commands on mysql

SLAVE START and SLAVE STOP

SHOW SLAVE STATUS

-returns information about the slave, including the important fact whether the slave is connected to the master (Slave_IO_Running), replication is running (Slave_SQL_Running), what binary log is being used (Master_Log_File and Relay_Master_Log_ File), and what position is current in the binary log (Read_Master_Log_Pos and Exec_ master_log_pos).

CHANGE MASTER TO

-This statement is an important one for keeping the replication in sync or starting it off at the right place. The MASTER_LOG_FILE refers to the binary log on the master from which the slave must start replicating, and the MASTER_LOG_POS is the position in that file. (You'll see examples of this later in the chapter.) This statement is also used when the master fails, and you need to change the master to which the slave connects. The full set of CHANGE MASTER TO options is:

CHANGE MASTER TO MASTER_HOST = 'master_hostname', MASTER_USER='replication_username', MASTER_PASSWORD=''replication_user_password', MASTER_PORT='master_port', MASTER_LOG_FILE='master_binary_logfile', MASTER_LOG_POS='master_binary_log_position'

RESET SLAVE

The statement causes the slave to forget its position in the master logs.

LOAD DATA FROM MASTER

-takes a copy of the data on the master and brings it onto the slave. Currently, this is not useful for large datasets or for situations where the master cannot be unavailable for long, as it acquires a global read lock when copying the data. It also updates the value of MASTER_LOG_FILE and MASTER_LOG_POS. Currently it only works with MyISAM tables. This statement is likely to become the standard way of preparing the slave in future, so be sure to read your latest documentation.

Master Replication Commands

The following slides are the master replication commands:

SET SQL_LOG_BIN

RESET MASTER

SHOW MASTER STATUS

PURGE MASTER LOGS TO

SHOW MASTER LOGS

SHOW SLAVE HOSTS

SHOW BINLOG EVENTS

MYSQL Stored Procedures

Introduction to SQL Stored Procedures

DEFINITION:

            A stored procedure is a segment of declarative SQL code, which is stored in the database catalog. A stored procedure can be invoked by a program, a trigger or even another stored procedure.

Stored Procedures Advantages

•      Stored procedure increases performance of application.

•      Stored procedure reduces the traffic between application and database .

•      Stored procedure is reusable and transparent to any application which wants to use it.

•      Stored procedure is secured.

Stored Procedures Disadvantages

•      Stored procedures make the database server high load in both memory and processors.

•      Stored procedure only contains SQL declarative statements.

•      Stored procedure is difficult to debug.

•      Store procedure is not easy to write and maintain.

Getting Started with MySQL Stored Procedures

Creating the first stored procedure

                        Sample Procedure:

                        DELIMITER //

                        CREATE PROCEDURE Showdepartmenr()

                        BEGIN

                        SELECT * FROM department;

                        END //

                        DELIMITER ;

                         

Getting Started with MySQL Stored Procedures(Cont…)

Calling the stored procedure

In order to invoke a stored procedure, we use the following SQL command:

                        CALL STORED_PROCEDURE_NAME();

Sample:

                        CALL Showdepartment();

If you run the command above you will get all employees in the employees database table.

STORED PROCEDURE PARAMETERS

•      Parameters make the stored procedure more flexible and useful. In MySQL, a parameter has one of three modes IN, OUT and INOUT.

•      IN this is the default mode. IN indicates that a parameter can be passed into stored procedures but any modification inside stored procedure does not change parameter.

•      OUT this mode indicates that stored procedure can change this parameter and pass back to the calling program.

•      INOUT obviously this mode is combined of IN and OUT mode; you can pass parameter into stored procedure and get it back with the new value from calling program.

The syntax of defining a parameter in stored procedure is as follows:

•      MODE param_name param_type(param_size)

•       

SAMPLE STORED PROCEDURE WITH PARAMETERS

DELIMITER //

CREATE PROCEDURE GetEmployeeByGender(IN gender varchar(1))

BEGIN

SELECT CONCAT(fname,' ', minit, ' ', lname) as fullname, bdate, address FROM employee WHERE sex = gender;

END //

DELIMITER ;

CALL GetEmployeeByGender(‘M’);

CALL GetEmployeeByGender(‘F’);

HOW TO SHOW STORED PROCEDURES

For a database named dbname, use this query on the INFORMATION_SCHEMA.ROUTINES table:

                        SELECT ROUTINE_TYPE, ROUTINE_NAME
                        FROM INFORMATION_SCHEMA.ROUTINES
                        WHERE ROUTINE_SCHEMA='dbname';

To delete a stored procedure, use the following statement:

DROP PROCEDURE ProcedureName();

Database BackUps

BACKUP AND RESTORE COMMANDS

What is Backup?

The purpose of backup is to recover data from an earlier time.

Syntax:

BACKUP TABLE [table_name] to ‘[path]’;

Example:

mysql> BACKUP TABLE Department TO ‘d:\\backup_folder';

What is Restore?

A restore is performed in order to return data to its original condition if files have become damaged, or to copy or move data to a new location.

Syntax:

RESTORE TABLE [table_name] from ‘[path]’;

Example:

RESTORE TABLE Department FROM ‘d:\\backup_folder';

Other syntax:

To backup using mysqldump:

Mysqldump –u root –p[password] [database_name] > [path];

To restore:

Mysql –u root –p [password] [database_name] < [path];

MYSQLDUMP

The Syntax:

mysql\bin> mysqldump [arguments] > file_name;

•      Dump all database

mysql\bin> mysqldump --all-databases > dump.sql

•      Dump specific databases

mysql\bin> mysqldump -u root --databases db1 db2 db3 > dump.sql

•      Dump single database

mysql\bin> mysqldump -u root --databases clsu > dump.sql

•      OK omit --databases in single database

mysql\bin> mysqldump -u root clsu > dump.sql

•      Dump specific table

mysql\bin > mysqldump -u root clsu college student> dump.sql

mysqldump

•      If dump with --databases  create dump with create database and use

•      If without --databases create dump without create databases and use

•      When reloading dump file specify database name

•      Or specify different name from original

•      If no database exist create first

MYSQLHOTCOPY

•      Runs only on Unix and NetWare

•      Used for bucking up MyISAM and ARCHIVE

•      Must have select privilege, reload privilege, and lock tables privilege

•      shell> mysqlhotcopy db_name [/path/to/new_directory]

•      shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

Backing Innodb

•      Two way using mysqldump and copy the file

•      Too copy file shutdown MySQL server make sure no errors

•      Copy all InnoDB data file (ibdata file and .ibd file) to safe place

•      Copy all .frm file

•      Copy all InnoDB log file

•      Copy you’re my.cnf configuration

Recover Innodb

•      Run Mysql server with binary log on defore taking the buckup

•      To achieve point-in-time recovery you can apply changes from binary log

•      To recover from mysql crash just restart it and the Innodb automatically logs and perform rolls back up to present

Dump all database

a. mysql\bin> mysqldump --all-databases > dump.sql

b. mysql\bin> mysqldump –u root --all-databases > dump.sql

c. mysql\bin> mysqldump --all > dump.sql

Dump specific databases

a. mysql\bin> mysqldump --databases db1 db2 db3 > dump.sql

b. mysql\bin> mysqldump -u root --databases db1 db2 db3 > dump.sql

c. mysql\bin> mysqldump db1 db2 db3 > dump.sql

Dump single database

a. mysql\bin> mysqldump --databases clsu > dump.sql

b. mysql\bin> mysqldump -u root --databases clsu > dump.sql

c. mysql\bin> mysqldump clsu > dump.sql

Which one is correct

a. without --databases no create databases and use

b. with --databases no create databases and use

c .without --databases has create databases and use

What is backing up the mysqlhotcopy

a. ARCHIVE

b. MyISAM

c. MyISAM and ARCHIVE

BACKUP BY DIRECTLY COPYING FILES

Backing up

Backing up databases can also be done by directly copying files.

This can work with MyISAM tables and only applicable in this table.

MyISAM tables

MyISAm tables are stored as files

            .MYD

            .MYI

            .FRM

Lock and Flush

Does not directly lock tables unlike on backup

flush the tables to make sure any unwritten indexes are written to disk

In copying files we need to open two(2) windows:

Window1:

LOCK TABLES table_name READ,table_name READ,…..READ;

FLUSH TABLES  table_name , table_name 2…;

Directly Copying Files:

On Window2:

C:\mydata\data\db>copy table_name.* C:\backup

    Data directory      table to copy      backup path

Example:

After copying, release the table using unlock statement on window1:

Mysql>UNLOCK TABLES;

To test the backup, you're going to drop a table.

BACKUP WITH SELECT INTO AND LOAD DATA INFILE

Select form that enables a query to be written to a file

select ...into :

q  select … into outfile

Writes a selected row to a file

q  select … into dumpfile

q  select … into var_list 

Syntax:

SELECT … INTO OUTFILE ‘file_name’ fields terminated by ‘terminatot’ from [table_name];

 

Load Data Infile

It is use to read the file back to the table

Syntax:

LOAD DATA INFILE [‘file_name’] into table [name_of_table];

If the data inside the data.txt looks like this:

Output:

Solution:

LOAD DATA INFILE [‘file_name’] into table [name_of_table] lines terminated by [‘terminator’];

Output:

Skipping over a prefix:

LOAD DATA INFILE [‘file_name’] into table [name_of_table] fields terminated by [‘terminator’]  lines starting by [‘starting_value’];

Sample file:

Output: