Mysql Backups

·         The BACKUP statement creates a copy of the definition and data files of a MyISAM table. The RESTORE statement restores the data.

®     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';

®     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';

·         Directly copying the files. You need to do your own locking. Returning the data files to the data directory restores the data.

·         mysqldump, which creates a text file containing the SQL statements needed to regenerate the tables. Using the file as input to the MySQL daemon restores the data.

                                mysqldump

                                                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

                                                ysqldump -u root --databases 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

·         Using SELECT INTO statements create a text file that can be used to restore the data with the LOAD DATA command or the mysqlimport utility.

·         Using the mysqlhotcopy utility. This is a Perl script that copies the data files to another directory. Returning the data files to the data directory restores the data.

·         mysqldump, which creates a text file containing the SQL statements needed to regenerate the tables. Using the file as input to the MySQL daemon restores the data.

MYSQL Stored Procedures

                A stored procedure is a procedure like a subprogram in a regular computing language that is stored in the database.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 ShowStudents()

                                BEGIN

                                SELECT * FROM students;

                                END //

                                DELIMITER ;

                                 

                Calling the stored procedure

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

                                CALL STORED_PROCEDURE_NAME();

                Sample:

                                CALL ShowStudents();

                If you run the command above you will get all employees in the students 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();

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.

Trigger implementation in MySQL

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

                Some of the 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. 

                 To create the trigger, you can use the following naming convention:

                                Syntax:

                                                (BEFORE | AFTER)_tableName_(INSERT| UPDATE | DELETE)

Replication

Replication means copying the files into other files. Having a backup is better because when your database has crash, you can use your backup to have the files again in your database. You need to have a server, a master and a slave. The master is the main source of the database. The slave needs to connect to the master so that you can have the access in the shared database. Usually the server id of the master is 1, while the server id of the slave is 2 upwards. Any number of slave can connects to the master that writes to the binary log and any update read this binary log. It is useful form of backup. The relationship between the binary log on the master and the master.info file on the slave is the key to keeping replication in synchronize. Replication will fail, if the master binary logs are removed before the slaves have used them.

Setting up the Replication

Step 1: configuring the master

We need to create user.

                CREATE USER hannah IDENTIFIED BY '12345';

                GRANT REPLICATION SLAVE ON *.* TO bogs IDENTIFIED BY '12345';

                GRANT RELOAD ON *.* TO hannah IDENTIFIED BY '12345';

                GRANT SUPER ON *.* TO hannah IDENTIFIED BY '12345';

                FLUSH PRIVILEGES;

                SHOW MASTER STATUS;

                QUIT;

Now we need to edit the my.ini file. Write this code:

                log-bin =hannah-bin.log  -  it is where to start writing a log
                binlog-do-db=my_database  -  this is the syntax of the creating e replication of a specific database
                server-id=1 – this is the id of a master, always 1                

Then start again the mysql.

Step 2: configuring the slave

Edit the my.ini file and put this code:

                server-id=2
                master-host=128.0.0.1
                master-connect-retry=60
                master-user=slave_user
                master-password=slave_password
                replicate-do-db=my_database

And then run this on mysql:

                load data from master; - to load the database from the master

                slave stop;

                change master to master_host='192.168.1.182',

                 master_user=‘hannah',master_password='12345',            

                master_log_file=‘hannah-bin.000001',

                master_log_pos=660;

                slave start;

Master Configuration File Options

·         log-bin=filename

®     Activates binary logging. This option must be present on the master for replication to occur. The filename is optional.

·         log-bin-index=filename

®     Specifies the name of the binary log index file.

·         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. Allother databases are ignored. You can restrict databases from the slave as well.

·         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. Has to be set for replication to begin. Once replication has begun, the master.info data will determine this, and you'll need to use a CHANGE MASTER TO statement to change it.

·         master-user=username

®     Specifies the username the slave will connect to the master with. The user should have REPLICATION SLAVE permission on the master.

·         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

®     If the connection between the master and slave goes down, 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 (for use during a SHOW SLAVE HOSTS statement).

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

·         replicate-ignore-table= db_name.table_name

®     Tells the slave not to replicate a statement that updates the specified table.

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

®     Tells the slave to replicate statements only where they match the specified table.

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

·         replicate-ignore-db= database_name

®     Tells the slave not to replicate any statement when the current database is database_name.

·         replicate-do-db= database_name

®     Tells the slave thread to replicate a statement only when the database is database_name.

·         log-slave-updates

®     Tells the slave to log replicated updates to the binary log.

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

®     This option tells MySQL to continue replicating if the error is one of the listed errors.

·         skip-slave-start

®     With this option set, replication will not begin when the server starts.

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

Replication Commands

·         Slave Commands

                                LOAD DATA FROM MASTER;

                                SLAVE STOP;

                                CHANGE MASTER TO MASTER_HOST='192.168.1.182',  

                                MASTER_USER=‘hannah',MASTER_PASSWORD='12345',

                                MASTER_LOG_FILE=‘hannah_binary.000001',

                                MASTER_LOG_POS=660;

                                SLAVE START;

·         Master Command

             CREATE USER hannah IDENTIFIED BY '12345';

                GRANT REPLICATION SLAVE ON *.* TO hannah IDENTIFIED BY '12345';

                GRANT RELOAD ON *.* TO hannah IDENTIFIED BY '12345';

                GRANT SUPER ON *.* TO bogs IDENTIFIED BY '12345';

                FLUSH PRIVILEGES;

                SHOW MASTER STATUS;

                QUIT;

• SLAVE START and SLAVE STOP start and stop the replication process.
• SHOW SLAVE STATUS returns information about the slave
• The RESET SLAVE 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.

Master replication commands:

• The SET SQL_LOG_BIN = n statement either deactivates binary update logging (if set to 0) or reactivates it (if set to 1). You need the SUPER privilege to run this statement.
• RESET MASTER removes all binary logs and starts numbering again at 001.
• SHOW MASTER STATUS shows the current binary log, the position in the binary log, and whether any databases are being ignored for binary logging.
• PURGE MASTER LOGS TO binary_log_filename removes all logs prior to the specified binary log. Be sure that no slaves still need the binary logs you're about to remove. See the example later in the "Removing Old Binary Logs from the Master and Starting" section.
• SHOW MASTER LOGS shows the list of binary log files available. You'd usually use this before purging the logs.
• SHOW SLAVE HOSTS returns a list of slaves registered with the master (note that by default a slave does not register itself but requires the report-host configuration option to be set).
• The SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ] statement reads statements from the binary logs.

Advance SQL

Logical, arithmetic, comparison, and bit operators

Operators

ž  Operators are the building blocks of complex queries.

1. Logical  operators
2. Arithmetic operators
3. Comparison operators
4. Bit operators

Logical Operators

ž  Logical operators (such as AND and OR) allow you to relate numbers of conditions in various ways.

ž  Reduce to either true (1) or false (0).

Logical Operators

Example:

Arithmetic Operators

•       Arithmetic operators are used to perform basic mathematical operations

Example:

Comparison Operators

•  Comparison operators are used when making comparisons between values. 

Example:

Bit Functions

 Bitwise Functions

• All the operands should be numerals, but they are internally represented as binary.

Example:

ž  Bitwise AND

Example:

ž  Bitwise OR

Performing transactions with BEGIN and COMMIT

These statements provide control over use of transactions:

ž  START TRANSACTION or BEGIN

›      start a new transaction.

›      COMMIT

›      commits the current transaction, making its changes permanent 

›      ROLLBACK

              › rolls back the current transaction, canceling its changes

›      SET autocommit

        › disables or enables the default autocommit mode for the current session 

CREATE TABLE t (f INT) TYPE=InnoDB;

BEGIN;

INSERT INTO t(f) VALUES (1);

SELECT * FROM t;

ROLLBACK;

SELECT * FROM t; 

CREATE TABLE t (f INT) TYPE=InnoDB;

BEGIN;

INSERT INTO t(f) VALUES (1);

SELECT * FROM t;

COMMIT;

ROLLBACK;

SELECT * FROM t;

Table locks

Unlock Tables

Running Mysql in Batch File

The MySQL software also allows you to run queries without 

entering the mysql client itself. 

To do so is to use mysql in what is called batch mode.
Instead of entering queries directly, you can store them in a 

text file and then run the text file through mysql.

Command:
mysql -u username -p databasename < '/path/to/filename.txt‘
Example:
mysql –u root sampledb <c:\query.txt

If you want to save the result of a batch file as its own 

text file, add > '/path/to/output.txt to your code.

The line bin/mysql -u username -p 

< '/path/to/input.txt' > '/path/to/output.txt'

Example:

Batch files can also be run from within the mysql client, if you'd prefer. 

Just log in, select your database, and then type
source/path/to/filename