Database Replication

     Replication is a way to configure a MySQL database to update on a remote server whenever the local client is updated. It allows you to make an exact copy of a database to another server, and set one of them (the slave) to take all its updates from the other (the master). It is use to create a read-only database of the master database. It can also be use as a backup of the master database.

SETTING UP REPLICATION
     To set up a replication you will need a database for the master and a database for the slave. You will need to change the configuration file(my.ini / my.cnf) of both servers. 

     In the master you will need to enable the binary logging because the slave reads the binary log of the master in order to replicate the database of the master. For the configuration file of the master you will need to put these:

  log-bin = binary_log_name

  server-id=1

     The server-id is used to act as an ip address for the databases. The commands to use in the mysql of the master are the following:

  CREATE USER bogs IDENTIFIED BY '12345';

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

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

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

  FLUSH PRIVILEGES;

  SHOW MASTER STATUS;

  QUIT;

     The bogs is the user created and the 12345 is the password that is also created together with the user.  The FLUSH PRIVELEGES statement is called to refresh the changes made in the privileges granted to the user bogs. The SHOW MASTER STATUS is used to know the current position and what log file is currently being used by the master. The information output from SHOW MASTER STATUS is to be used for the CHANGE statement for the slave.

     For the slave configuration file you will need to put these:
  server-id=2
  master-host=192.168.1.181
  master-user=bogs
  master-password=12345
  master-connect-retry=60

     The server id must be different from the server id of the master. The master host is the ip address of the master server. The master user is the user created by the master for the slaves. The master password is the password created by the master for the slaves. The master connect retry is the number of seconds the slave will wait before it cancels its request for connecting to the master.
     The commands to use in the mysql of the master are the following:
  LOAD DATA FROM MASTER;
  SLAVE STOP;
  CHANGE MASTER TO MASTER_HOST='192.168.1.182',
  MASTER_USER='bogs',MASTER_PASSWORD='12345',
  MASTER_LOG_FILE='binary_log_name.000001', 
  MASTER_LOG_POS=660;
  SLAVE START;

MASTER AND SLAVE CONFIGURATION
     There are different configuration files available for the master and slave. These are the configuration for the master configuration file(my.ini/my.cnf for the master):

      Here are the configuration files for the slave:

Stored Procedures and Triggers

TRIGGERS

     Triggers are SQL statements or a set of SQL statements which is stored to be activated when an event associating with a database table occurs. The events can be one of following:

     Delete - the trigger is activated if something is deleted from the table.

     Insert - the trigger is activated if something is inserted into the table .

     Update - the trigger is activated if the table is updated.

To create a trigger you can use the 'CREATE TRIGGER' statement.  Here is the syntax:

  CREATE TRIGGER trigger_name BEFORE [table event] FOR EACH ROW [sql statements];

     For better use of triggers you can use the DELIMETER function to enable adding of sequence of mysql events.
 

STORED PROCEDURES

     Stored procedures are blocks of code stored on the server and will normally carry out a series of SQL statements. They are segments of declarative SQL code, which and can be invoked by a program, a trigger or even another stored procedure.
     To create a stored procedure you will need to use the 'CREATE PROCEDURE' statement. For better creation of a stored procedure, you can also usee the DELIMETER function to enable adding of sequence of mysql statements. Here is the syntax:
  DELIMETER //
  CREATE PROCEDURE procedurename()
  BEGIN
  [SQL statements]
  END //
  DELIMETER;
In order to call or invoke the stored procedure, you can use the following command:
  CALL procedurename(); 
 

Database Backups

     When managing databases, it is a good practice to have a backup of your database. Backups are an earlier version of one's database. This would give the assurance that the data would always be there; even if the system have failed or something went wrong with the process that would led to the destruction of one's database.  Today we are going to discuss the different ways of backing up database in mysql.

BACKUP TABLE
     To backup a table inside mysql, you will need to use the 'BACKUP TABLE' statement. The syntax for this is "BACKUP TABLE [table_name] to '[path]';"
     Here is an example:

     To restore the backup means to use the backup saved. It is used to return the data to its original condition. The syntax for this is "RESTORE TABLE [tablename] from '[path'];"
     Here is an example:

MYSQLDUMP
     Another way of backing up mysql database is by using mysqldump. You will need to use this outside of the mysql program. The syntax for this is "mysqldump -u root -p [database_name] > [path][filename]"
     Here is an example:

     To restore the backup stored using mysqldump you will need to use this syntax "mysql -u root -p [databasename]<[path][filename]"
     Here is an example:

   
     There are many arguments you can use in mysqldump.
To backup all the databases use this syntax:
     "mysqldump --all-databases > [path][filename]"
To backup a specific database use this syntax:
     "mysqldump -u root -p --databases [database_name] [database_name]  > [path][filename]"
To backup all the tables in a database use this syntax:
     "mysqldump -u root -p [database_name] > [path][filename]"
To backup a specific table use this syntax:

     "mysqldump -u root -p [database_name] [table_name] [table_name]  > [path][filename]"

SELECT...INTO
     Another way of creating a backup is by the use of "SELECT INTO" statement. "SELECT...INTO" enables a query to be written on a file. There are three kinds of using this. There is the "SELECT...INTO OUTFILE", the "SELECT...INTO DUMPFILE" and the "SELECT...INTO VAR_LIST".
     The "SELECT...INTO OUTFILE writes a selected table into a file. The syntax for this is "SELECT...INTO OUTFILE [filename] FIELDS TERMINATED BY [terminator] FROM [tablename];"
     Here is an example:

     Here is the output:

   
     To restore the data back to the database you will need to use the "LOAD DATA INFILE" statement. To use this the syntax is "LOAD DATA INFILE [filename] INTO TABLE [name_of_tables] LINES TERMINATED BY [terminator];.

     Here is an example:

     Here is the output:

     For more information about select into visit the mysql website here.

Mysql Table Views

     A view is a specific look or a peek on data from one or more tables. It is created to act as a dynamic, virtual table computed or collated from the data in the database. It acts like a table or is simply a psuedo-table. It is a query that is stored and is named like a table and it can be referenced like a table. Its field can be from one or more real tables in the database. If you insert, edit or delete a data in the view from which the view comes from one table only that data will also be altered in the real table. But if you try to alter a data in a view from different tables mysql does not allow such alteration thus you will end in an error.
     To create a view you will need to use this syntax:
     CREATE VIEW name_of_view AS
     SELECT column_names 
     FROM table_name
     WHERE condition

     In creating views you can use all of the different types queries available in mysql like the ones with the JOIN clause and GROUP clause and with different conditions you like.
     To query a view, you can query like an ordinary table. Alteration of the view will only be acceptable if the view is from only one table. To show the list of views you can use the SHOW FULL TABLES statement.
     Here is an example:

Performing Transactions with BEGIN and COMMIT

     MYSQL supports the use of transaction in its server. Transaction  is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.
     There are many statement you can use to provide control over the use of transactions. I will discuss four of them, the START TRANSACTION or BEGIN statement, the COMMIT statement, the ROLLBACK statement, and the SET AUTOCOMMIT statment. To start a new transaction, you need to use the START TRANSACTION or BEGIN statement. The COMMIT statement coommits the current transaction which makes its changes permanent. The ROLLBACK statement rolls back the current transaction cancelling the changes made to it. The SET AUTOCOMMIT statement disables or enables the default autocommit mode for the current session.
     Here is a sample code that uses transaction with ROLLBACK:
     CREATE TABLE T(Year INT) TYPE=InnoDB;
     BEGIN;
     INSERT INTO T(Year) VALUES(2012);
     SELECT * FROM T;
     ROLLBACK;
     SELECT  * FROM T;
     Here is the output:

     Here is another code that uses transaction with COMMIT and ROLLBACK:
     BEGIN;
     INSERT INTO T(Year) VALUES(2012);
     SELECT * FROM T;
     COMMIT;
     ROLLBACK;
     SELECT  * FROM T;
     Here is the output:

   
      A good practice of using transactions is by using the Table Lock. The Table Lock locks the table being used and prevents other user from accessing that table. This way one user can access the table at a time preventing the inconsistency in changes made to the database. To use Table Locks you will need to use the LOCK TABLES statement like this 'LOCK TABLES T READ'. The T is the table and the READ is the access your giving to that table. To unlock the table use the UNLOCK TABLES statement.
     For more information about Table Locks you can visit the mysql site here.

Logical, Arithmetic, Comparison, and Bit Operators

     Operators are the building blocks of complex queries meaning these operators are used in the SELECT statement. There are 4 types of operators. They are the 'Logical operators', 'Arithmetic operators', 'Comparison operators' and the 'Bit operators'. I will discuss these operators one by one.

LOGICAL OPERATORS

      Logical operators allow you to relate numbers of condition in various ways. The outcome would be reduce to either true(1) or false(0). The logical operators are the AND(&&), OR(||), and the NOT(!). The AND(&&) operator results to true only if both conditions are true. The OR(||) operator results to true if one of the conditions is true. And the NOT(!) operator results to the opposite value of the condition.

ARITHMETIC OPERATORS

     Arithmetic operators are used to perform basic mathematical operations while using the SELECT statement. There are 5 different type of arithmetic operators. They are the (+) sign, (-) sign, (*) sign, (/) sign and the (%) sign. The (+) sign is used to get the sum of the values you want to add. The (-) sign is used to get the difference of the values you want to subtract. The (*) sign is use to get the product and the (/) sign is used to get the quotient. The (%) sign is used to get the remainder of the values you want to divide.

COMPARISON OPERATORS

     Comparison operators are used when making comparison between values. There are many comparison operators available in mysql. There is the (=) sign that checks if both values being compared are equal. The (!=) and (<>) signs checks if both values being compared are not equal. The (>) sign checks if the first value is greater than the second value while the (<) sign checks if the first value is less than the second value. There is also the (>=) sign and the (<=) sign which checks respectively if first value if greater than of equal to or less than or equal to the second value. The (<=>) sign checks if both values are equal including their null values. The IS NULL operation checks if the value contains a NULL value. The IS NOT NULL operation checks if the value does not contain a NULL value. The BETWEEN operation checks if the value your comparing is between the value of the other two values you compared. There is also the LIKE and NOT LIKE operation which checks the first value matches or do no matches the SQL pattern of the second value.

BIT FUNCTIONS
     Bit functions are operators used by bits. There are seven kinds of bit functions. These are the BIT_COUNT(), (&) sign, (~) sign, (|) sign, (^) sign, (<<) sign and the (>>) sign. The BIT_COUNT() returns the number of bits that are set. The (&) sign is the bitwise AND. The (~) sign inverts the bits. The (|) sign is the bitwise OR. The (^) sign is the bitwise XOR. The (<<) sign is the left shif while the (>>) sign is the right shift.

For more information on how to use operators you can visit the site here.

The SELECT Statement Part 2: Multiple Queries, Multiple Tables and More...

     This is the continuation of the previous discussion about the "SELECT" statement. Previously, we discussed using the "SELECT" statement on single query retrieval. Today we will further discuss the "SELECT" statement on how to use it in multiple queries and multiple tables.

NESTING OF QUERIES
     A nested query is a query that occurs if a query is used on another query. That another query is called the 'outer query'. We use nested query if you want to get a more specific data in a database which is only attainable if you get another query from the database.
     Here is sample situation. Suppose you would like to get the maximum price of the albums with the highest possible ratings in a database. You are likely to use this statement "SELECT max(price) from album where rating = max(rating);".
     This statement would result to this error:

     To fix this statement, you would need to use a nested query. Here is an example:

NULL IN SQL QUERIES
     The NULL keyword is another keyword that can be used after the WHERE-clause. The NULL keyword is used to determine if a value is missing or undefined or not applicable in an attribute. To use this you will need to use the IS NULL or IS NOT NULL keywords.
     Here are some examples:

THE JOINED RELATIONS
     Joined relations are used to get data from two different tables at the same time. It is often used on conditions where you would need to combine two separate tables or relations. To use this you would need to use the JOIN function. You can use either LEFT JOIN, INNER JOIN, RIGHT JOIN and OUTER JOIN keywords.
     Here is an example:

AGGREGATE FUNCTION
     From the examples above, there is a COUNT and MAX keywords used. Those keywords are an example of aggregate functions. Aggregate functions are used like mathematical equations to make retrieval of computed data easier. The aggregate functions are the COUNT, SUM, MAX, MIN and AVG. Just like their keywords suggests, the COUNT gets the total number of count on that attribute, the SUM gets the sum of all data in that attribute, the MAX gets the maximum value of the data in that attribute, the MIN gets the minimum value of the data in that attribute and the AVG gets the average of the data in that attribute.
     Here is an example:

GROUP BY-CLAUSE
     GROUP BY-clause is used to specify which will be the grouping attribute. The grouping attribute will be the attribute wherein all value are different. Usually if that attribute has many data with the same value, the one that will be displayed is the first data of that value.
     Here is an example:

HAVING-CLAUSE
     HAVING-clause is usually added at the latter part of the attribute after a GROUP BY-clause. It is used to add condition on the group created by the GROUP BY-clause.
     Here is an example:

ORDER BY-CLAUSE
     ORDER BY is the clause use to sort the data displayed either in ascending or descending order. It is usually added at the last part of the statement. To choose which order would you like to use just add ASC for ascending or DESC for descending at the end of the ORDER BY-clause.
     Here is an example: