MySQL : Create a function to compare the two conman separated values

CREATE FUNCTION `FIND_IN_TWO_SET`(val TEXT, val2 TEXT) RETURNS int(11)
BEGIN
        DECLARE output TEXT;
        DECLARE inc INT;
        DECLARE mach INT;
        SET inc = 0;
        SET mach = 0;
        
        label1: LOOP
            SET inc = inc + 1;
            SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(val, ',', inc), CHAR_LENGTH(SUBSTRING_INDEX(val, ',', inc - 1)) + 1), ',', '');
            IF output = '' THEN
                SET output = null;
                LEAVE label1;
            END IF;
            if find_in_set(output, val2) > 0 THEN
                SET mach = 1;
                LEAVE label1;
            END IF;
                ITERATE label1;
            END LOOP label1;
        RETURN mach;
    END

PHP : Import database

public static function updateDatabase($path){
ini_set(‘memory_limit’, ‘-1’);
ini_set(‘max_execution_time’,’-1′);
$em = $this->getEntityManager();
$fp = fopen ( $path, ‘r’ );
$fetchData = fread ( $fp, filesize ( $path) );
$sqlInfo = explode ( “;\n”, $fetchData); // explode dump sql as a array data
foreach ($sqlInfo AS $sqlData ){
$qb = $em->createQueryBuilder ();
$connection = $em->getConnection ();
$connection->executeQuery ( $sqlData);

}
}

MySql : Create new user in mysql with access to only certain databases

There are the three steps

  1. Create Database
  2. Create new User
  3. Give the rights with grant all command

Like this

CREATE DATABASE DBTEST

If this is run successfully without any error, then create new user

CREATE USER 'NEWUSER'@'LOCALHOST' IDENTIFIED BY 'NEWUSERPASSWORD'

After user creation give the right of create table drop table

GRANT ALL PRIVILEGES ON *.* TO 'NEWUSER'@'LOCALHOST'

And after all give one command

FLUSH PRIVILEGES

You can give the rights on particular database like this

GRANT ALL PRIVILEGES ON DBTEST.* TO 'NEWUSER'@'LOCALHOST'



Continue reading

PHP : Backup Database

backup_tables(‘hostname’, ‘database user’, ‘database password’, ‘database name’);

function backup_tables($host,$user,$pass,$name,$tables = ‘*’)
{

$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);

//get all of the tables
if($tables == ‘*’)
{
$tables = array();
$result = mysql_query(‘SHOW TABLES’);
while($row = mysql_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(‘,’,$tables);
}
$return = “”;
//cycle through
foreach($tables as $table)
{
$result = mysql_query(‘SELECT * FROM ‘.$table);
$num_fields = mysql_num_fields($result);

$return.= ‘DROP TABLE ‘.$table.’;’;
$row2 = mysql_fetch_row(mysql_query(‘SHOW CREATE TABLE ‘.$table));
$return.= “\n\n”.$row2[1].”;\n\n”;

for ($i = 0; $i < $num_fields; $i++)
{
while($row = mysql_fetch_row($result))
{
$return.= ‘INSERT INTO ‘.$table.’ VALUES(‘;
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = str_replace(“\n”,”\n”,$row[$j]);
if (isset($row[$j])) { $return.= ‘”‘.$row[$j].'”‘ ; } else { $return.= ‘””‘; }
if ($j<($num_fields-1)) { $return.= ‘,’; }
}
$return.= “);\n”;
}
}
$return.=”\n\n\n”;
}

//save file
$handle = fopen(‘db-backup-‘.time().’-‘.(md5(implode(‘,’,$tables))).’.sql’,’w+’);
fwrite($handle,$return);
fclose($handle);
}

Mysql : Dump All Data in InnoDB

This is what will give you an exact point-in-time snapshot of the data:

mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

--single-transaction produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes. Those incoming changes do not become part of the dump. That ensures the same point-in-time for all tables.
Continue reading