Mysql Database Backup Using PHP / Batch File in Windows System.

In this section, I will show how to backup Mysql database using php script and batch file(.bat).
I had created this script for our client as they require automatic mysql database backup for their project.

So You can schedule below script in task schedular of windows system to backup database on daily /monthly basis.

Mysql Database Backup Using PHP Script :

Example: backup.php

<?php
ini_set("display_errors",1);

$dbhost = 'localhost'; // Datatbase host name.
$dbuser = 'root'; // Database user name
$dbpass = '123'; // database password
$dbname="testdb"; // database name;

system("cd c:\\wamp\\bin\\mysql\\mysql5.5.24\\bin &  mysqldump --opt -h $dbhost -u$dbuser -p$dbpass $dbname>c:\\wamp\\backup\\backup.sql");
?>


"cd c:\\wamp\\bin\\mysql\\mysql5.5.24\\bin" is the path of Mysql folder.

"mysqldump --opt -h $dbhost -u$dbuser -p$dbpass $dbname>c:\\wamp\\backup\\backup.sql" is the Mysql command used to take backup of mysql database.

Mysql Database Backup Using Batch File :

It is very easy to backup mysql database using batch file as you have to just write commands in batch file and schedule batch file as per your requirment in Task Shedular.

Example: backup.bat


cd c:\wamp\bin\mysql\mysql5.5.24\bin

@echo off
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set dt=%%c-%%a-%%b)
mysqldump --opt -h localhost -uroot -p123 testdb>c:\wamp\backup\testdb_%dt%.sql



I hope above examples will help you to take backup of mysql database.

Update Query using joins in MySql 


In this Article, I will show how to write Mysql update query using joins.You can use multiple tables in your single Update query.

Example :

Departments Table :
id	Name
1	Accounts
2	R&D
3	Sales

Employees Table :
id	Name	Salary	Department Id
1	Rajesh	50000	2
2	Sachin	80000	2
3	Rahul	20000	1

MySql Query to update employee record whose salary is greater than 50000 and department name is R&D.


Update employees 
inner join departments ON empoyees.department_id=departments.id
SET employees.salary=40000
WHERE  employees.salary>50000 and department.name='R&D'



MySql Query to delete employee record whose salary is greater than 50000 and department name is R&D.


DELETE employees 
inner join departments ON empoyees.department_id=departments.id
WHERE employees.salary>50000 and department.name='R&D'


Updating Query Using Joins in PHP Script:

<?php
$host = 'localhost:3036';
$user = 'root';
$pass = 'rootpassword';
$conn = mysql_connect($host, $user, $pass);
mysql_select_db('sampledb');

$sql = "Update employees 
join departments ON empoyees.department_id=departments.id
SET employees.salary=40000
WHERE  employees.salary>50000 and department.name='R&D'";

$result = mysql_query( $sql, $conn );
echo "data updated successfully\n";
mysql_close($conn);
?>


Find second and fourth saturday's of the month in PHP-MySql


In this section, I have created simple script in PHP to find second(2nd) and fourth(4th) Saturday of the month.

<?php
// Mysql Connection
$conn = mysql_connect('localhost','root','');
if (!$conn) {
die('Could not connect to MySQL: ' . mysql_error());
}
echo 'Connection OK'; mysql_close($conn);

// Select Database
mysql_select_db("test");

// Array of the Month's
$month_array=array("jan","Feb","Mar","Apr","May","Jun","july",'Aug',"Sep","Oct","Nov","Dec");

// Array of the Years's
$year_array=array("2013","2014");

foreach($year_array as $year)
{

foreach($month_array as $month)
{
echo $second=date('Y-m-d', strtotime("second sat of $month $year"));
echo "<br>";
echo $fourth=date('Y-m-d', strtotime("fourth sat of $month $year"));
echo "<br>";

}
}

?>


You can use this PHP script to find second and fourth saturday of particular month and year.

One to One relationship example in database(oracle, MYSQL, SQl, Ms access)

In this Article, I will explain how to create one to one relationship between two tables.In a relational database such as Mysql,SQl,Ms access,  you can create relationships between pairs of tables like..
1.one-to-one
2.one-to-many 
3.many-to-many.

One-to-one relationships in a relational database:

one-to-one relationships are bidirectional relationships.each row in one database table is linked to one and only one other row in another table.
An example of a one-to-one relationship is a Person can have only one Passport and that a Passport can belong to only one Person.
we are designing Database with a One To One Relationship between Person and Passport Table.



You may not use one-to-one relationships very usually, but you should know how to create them.

There are other types of database relationships, one to many and many to many relationships.

Mysql Binary Logs Tutorial | Mysql Binary Log Backups:

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows).The binary log has two important purposes:

1) For replication, the binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.

2) Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.The binary log is not used for statements such as SELECT or SHOW that do not modify data.The format of the events recorded in the binary log is dependent on the binary logging format. Three format types are supported, row-based logging, statement-based logging and mixed-base logging.

To enable the binary log, start the server with the --log-bin[=base_name] option. If no base_name value is given, the default name is the value of the pid-file option (which by default is the name of host machine) followed by -bin.we have to uncommnet the following filed from my.ini or my.cnf file to enable the binary log-

# binary logging is required for replication
log-bin=mysql-bin // base name is mysql-bin

The server creates a new file in the series each time it starts or flushes the logs. The server also creates a new binary log file automatically after the current log's size reaches max_binlog_size(which is 1gb).Binary log size can be seen using following statement:
SHOW VARIABLES LIKE 'max_binlog_size'

A replication slave server by default does not write to its own binary log any data modifications that are received from the replication master. To log these modifications, start the slave with the --log-slave-updates option in addition to the --log-bin option.

You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE BINARY LOGS. You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days.

RESET Syntax
RESET reset_option;
reset_option can be any of the following:
1)MASTER .
2)SLAVE.

PURGE BINARY LOGS Syntax
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }


Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2012-03-22 22:46:26';


Expire_logs_days Syntax


We can set the variable expire_logs_days in following way:-
1) Mysql> SET GLOBAL expire_logs_days = 6;
Query OK, 0 rows affected (0.00 sec)

Mysql> show variables like 'expire_%';
+------------------+--------+
| Variable_name| Value |
+------------------+--------+
| expire_logs_days | 7 |
+------------------+--------+
1 row in set (0.00 sec)

2) Also we can cghange in Masters my.cnf or my.ini file like this:-

#expire bin logs
expire_logs_days = 7


You can display the contents of binary log files with the mysqlbinlog utility. So to read and display the contents of the binary log file named binlog.000001, use this command:

mysqlbinlog binlog.000001

The binary log files and its data are likely to be very huge, thus making it almost impossible to read anything on screen. However, you can pipe the output of mysqlbinlog into a file which can be open up for later browsing in text editor, by using the following command:


mysqlbinlog binlog.000001 > filename.txt

References:-
1)http://dev.mysql.com/doc/refman/5.0/en/binary-log.html


2)http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html

wait_timeout Mysql :

wait_timeout defines the number of seconds a mysql thread waits in idle state before mysql kills it.

MySQL uses a lot of different timeout variables at different stages.when connection is just being established connect_timeout is used. When server waits for another query to be sent to it wait_timeout.
By default, the server closes the connection after eight hours if nothing has happened.
You can change the time limit by setting the wait_timeout variable when you
start mysqld.

Clustered indexes Vs Non-clustered indexes-MySql

• Clustered index will physically order the data based on the values in the index.
• A non clustered index is a special type of index that stores the records in logical order rather than physical order. This means, a table can have many non clustered indexes. More non clustered indexes per table means more time it takes to write new records.
• A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
• A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
• Clustered index is physically stored a table can have 1 clustered index.
• Non clustered index is logically stored a table can have 249 non  clustered  index.

How to count non-empty cells in one row in MySql.
In this section, I will explain how to count non-empty cells in one row in MySql.

Suppose we have to count non empty cells of Price table.

Price table:
Col1          Col2        Col3
1000000     200           1000
4545556     400            NULL     

Query:
SELECT IF(col1 IS NOT NULL, 1, 0) + IF(col2 IS NOT NULL, 1, 0) + IF(col3 IS NOT NULL, 1, 0) AS total_price 
FROM price_table

This query will return Total price of non empty cells(col1,col2,col3)