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'

Query cache settings

Unless MariaDB has been specifically built without the query cache, the query cache will always be available, although inactive. The have_query_cache system variable will show whether the query cache is available.

How to cache database queries

There are many types of caching mechanisms like memcached, APC, eAccelerator but for the simplicity of this tip, we will use plain file to hold our cache data. This is enough in most of cases.

SQL Tuning or SQL Optimization

Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries.
But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement.
Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

Backup Your MySQL Database Using PHP

backup_tables('$host','$DB_User','$DB_Pass','$DB_Name');


/* backup the db OR just a table */
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);
	}
	
	//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] = ereg_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);
}