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.