Replication

Replication is a way to configure a MySQL database to update on a remote server whenever the local client is updated.

Replication 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).

Replication is the frequent electronic copying data from a database in one computer or server to a database in another so that all users share the same level of information.

Replication 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/Configuring Master:

In your my.ini file put these lines:

            [mysqld]

            log-bin = mcvin_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:

•  Now 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';
• 
  
• You can also type this 

mysql>FLUSH privileges

Once you have given the desired privileges for your user, you will need to FLUSH privileges in order to complete the setup and to make the new settings work.

Then use the database you want to replicate

mysql>use replicationdb;

mysql>show master status;

• This command will show something like this:

o   show master status;

• This information will be needed later on the slave.
• Then leave the MySQL shell:

mysql>quit;

Setting Up/Configuring Slave:

·         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 and data from replicationdb from master to slave

·         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.

Slave Replication Commands

¨  start and stop the replication process, respectively.

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).

Triggers and Stored Procedure

TRIGGERS

SQL trigger is an SQL statements or a set of SQL statements which is stored to be activated or fired when an event associating with a database table occurs.

Implementation

• While trigger is implemented in MySQL has all features in standard SQL but there are some restrictions you should be aware.

Restrictions:

• It is not allowed to create a trigger for views or temporary table.
• It is not allowed to use transaction in a trigger.
• Return statement is disallowed in a trigger.
• Creating a trigger for a database table causes the query cache invalidated.

*Query cache allows you to store the result of query and corresponding select statement.

In the next time, when the same select statement comes to the database server, the database server will use the result which stored in the memory instead of parsing and executing the query again.

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

*It is allowed that triggers for different tables having the same name but it is recommended that trigger should have unique name in a specific database. 

MYSQL Stored Procedures

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.

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

• 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 BACKUP and RESTORE commands

BACKUP

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

Syntax:

BACKUP TABLE [table_name] TO ‘[path]’;
Example:
BACKUP TABLE College TO ‘D:\\New’;

RESTORE

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 College FROM ‘D:\\New';

Other syntax:

To backup using mysqldump:

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

Mysqldump –u root clsu > D:\\New;

To restore:

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

SELECT INTO

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

Types of select ...into:

v  select … into outfile

o   Writes a selected row to a file

v  select … into dumpfile

o   writes a single row to a file

v  select … into var_list

o   selects column values and into variables

LOAD DATA INFILE

It is use to read the file back to the table

mysqldump

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

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

o   When reloading dump file specify database name

o   Or specify different name from original

o   If no database exist create first

mysqldump examples:

mysql\bin> mysqldump [arguments] > file_name

v  Dump all database

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

v  Dump specific databases

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

v  Dump single database

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

Example:
mysqldump -u root --databases clsu > D:\\New\clsu.sql

v  OK omit --databases in single database

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

v  Dump specific table

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

Example:
mysqldump -u root clsu college > college.sql

mysqlhotcopy

v  Runs only on Unix and NetWare

v  Used for bucking up MyISAM and ARCHIVE

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

Backing Innodb

v  Two way using mysqldump and copy the file

o   Too copy file shutdown MySQL server make sure no errors

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

o   Copy all .frm file

o   Copy all InnoDB log file

o   Copy you’re my.cnf configuration

Recover Innodb

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

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

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