Optimizing tables is very necessary for database. That will improve the your website performance. Optimize command is helpful to manage database files in right manner. This command will optimize the size of file size of database files.
How optimize all tables using php script
If you want to optimize the all tables of database then use following script.
<!--?<span class="hiddenSpellError" pre="" data-mce-bogus="1"-->php
$dbconnect = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$dbconnect) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected to database successfully';
mysql_select_db('YOUR_DATABASE',$dbconnect);
dbConnect();
$alltables = mysql_query("SHOW TABLES");
while ($table = mysql_fetch_assoc($alltables))
{
foreach ($table as $db => $tablename)
{
mysql_query("OPTIMIZE TABLE '".$tablename."'")
or die(mysql_error());
}
}
echo 'All tables optimized successfully';
mysql_close($dbconnect);
?>
The MySQL Optimize Table command will effectively de-fragment a mysql table and is very useful for tables which are frequently updated and/or deleted.
I faced the sleep query issue sometimes. Many times that kills the server. When ever you are using dedicated virtual hosting server that time you need to me very careful how you are setting up the apache and mysql. we solved mysql sleep processes issue
mysql sleep processes issue solved
I found some major reasons for mysql sleep processes. Here I am giving some reasons:
[viral-lock message=”Reasons and Solution is Hidden! It’s Visible for Users who Liked/Shared This article on Facebook or Twitter or Google+. Like or Tweet this article to reveal the content.”]
1. apache requests increase
2. mysql queries increase
3. mysql slow queries increase
4. apache connections start to wait on slow mysql queries
5. apache connections take longer to close
6. more apache connections are opened
7. with each apache request a new sleeping mysql connection is made
8. mysql & apache reach max_connections
9. server slows to a crawl
[/viral-lock]
Using linux top command you can check the load average and which process is taking so much memory of your server. If you saw mysqld is taking much memory of server then you should check using following mysql commands:
> show full processlist;
using this command you can check the mysql processes. Which sql queries being fired on mysql server. If you found so much sleep processes then you should check your mysql variables and settings. If so much mysql queries being fired on specific table then you should change the table storage engine type.
Using following command you can change the storage engine type.
> ALTER TABLE tablename ENGINE = MYISAM;
MYISAM storage engine type is faster then inodb. you can create the mediator tables also for big size table to handing a load of table.
Then major change for solving the issue of sleep processes is, you should change the wait_timeout variable. Default value is 2800 seconds.
mysql> show variables;
you can change the setting using following mysql command. Best value is 60 second need to be set for wait timeout.
mysql> show variables like ‘wait_timeout’;
mysql> set global wait_timeout=60;
mysql>show variables like ‘wait_timeout’;
After doing above changes you can check the mysql processlist. If you are still facing issues with mysql then please write to me.
As a developers need to check the mysql logs. Many times for any request how many mysql queries are running or executed we need to know.
How to create mysql logs for specific queries.
In this tutorial I will show you how to created mysql log file for checking the SELECT, UPDATE, DELETE or any types of mysql queries.
If you are using the linux then use the my.cnf file which will be located in etc folder. If you are using the windows then use the my.ini file. That file you will find in following location in case of wamp server.
c:\wamp\bin\mysql\mysql5.0.45\my.ini
I am using the Linux so here I am giving that example. use following command.
If you want to use the mongodb with php then you need to install pecl libraries. Mongo extension is not bundled with PHP.
The MongoDB server is built to already work with your current web server. The problem is that you’ll need to install drivers for your preferred backend language – PHP, Ruby, Node.js, Perl, whatever.
How to use mongodb with php
For installing the Mongo use following URL:
If you dont have pecl installed on your linux machine then use following command.
# yum install php-pecl*
then use following command
# pecl install mongo
Then open the php.ini file. if you are using the linux then use following command.
# vim /etc/php.ini
## Add following lines, end of php.ini file ##
# MongoDB Driver
extension=mongo.so
Restart the apache webserver using following command
# /etc/init.d/httpd restart
If Mongodb server is running then use can test your mongodb database using following code:
Create the mongotest.php file
<!--?php
// connect
$m = new Mongo();
// select a database
$db = $m--->wordpress;
$collection = $db->wordpressapi;
// add an element
$obj = array( "title" => "Sony this Good.\n", "author" => "Wordpressapi.com" );
$collection->insert($obj);
// add another element, with a different "shape"
$obj = array( "title" => "Wordpressapi.com", "online" => true );
$collection->insert($obj);
// find everything in the collection
$cursor = $collection->find();
// iterate through the results
foreach ($cursor as $obj) {
echo $obj["title"] . "\n";
echo $obj["author"] . "\n";
}
// disconnect
$m->close();
?>
If you open this file in browser then you can see the following words in browser:
Sony this Good. WordPressapi.com Sony this Good. WordPressapi.com
If you want to use the mongodb with php then you need to install pecl libraries. Mongo extension is not bundled with PHP.
For installing the Mongo use following URL:
If you dont have pecl installed on your linux machine then use following command.# yum install php-pecl*
then use following command# pecl install mongo
Then open the php.ini file. if you are using the linux then use following command.
# vim /etc/php.ini
## Add following lines, end of php.ini file ### MongoDB Driverextension=mongo.so
Restart the apache webserver using following command
# /etc/init.d/httpd restart
If Mongodb server is running then use can test your mongodb database using following code:
Create the mongotest.php file
<!--?php
// connect$m = new Mongo();
// select a database$db = $m--->wordpress;$collection = $db->wordpressapi;
// add an element$obj = array( "title" => "Sony this Good.\n", "author" => "Wordpressapi.com" );$collection->insert($obj);
// add another element, with a different "shape"$obj = array( "title" => "Wordpressapi.com", "online" => true );$collection->insert($obj);
// find everything in the collection$cursor = $collection->find();
// iterate through the resultsforeach ($cursor as $obj) { echo $obj["title"] . "\n"; echo $obj["author"] . "\n";}
// disconnect$m->close();
?>
If you open this file in browser then you can see the following words in browser:Sony this Good. WordPressapi.com Sony this Good. WordPressapi.com
Mongodb is used for many projects now, it is quite fast. I given full detailed information about How to install Mongodb on linux box. Before installing the Mongodb on linux box you need to install following packages on box.
[root@sonyk-pc mongodb-linux-i686-1.6.2]# ./bin/mongod
./bin/mongod --help for help and startup options
Thu Sep 9 13:10:55 MongoDB starting : pid=22159 port=27017 dbpath=/data/db/ 32-bit
** NOTE: when using MongoDB 32 bit, you are limited to about 2 gigabytes of data
** see http://blog.mongodb.org/post/137788967/32-bit-limitations
Thu Sep 9 13:10:55 db version v1.6.2, pdfile version 4.5
If you want to create mongodb service in linux then use following steps:
create the File: /opt/bin/mongodb-stop
Put following code in that file:
#!/bin/bash
pid=`ps -o pid,command ax | grep mongod | awk '!/awk/ && !/grep/ {print $1}'`;
if [ "${pid}" != "" ]; then
kill -2 ${pid};
fi
create the File: /opt/bin/mongodb-start
Put following code in that file:
#!/bin/sh
/opt/mongodb/bin/mongod --config /opt/config/mongodb \
## --upgrade \ ##runs a database upgrade option if needed \
File: /opt/config/mongodb
Put following code in that file:
# Configuration Options for MongoDB
#
# For More Information, Consider:
# - Configuration Parameters: http://www.mongodb.org/display/DOCS/Command+Line+Parameters
# - File Based Configuration: http://www.mongodb.org/display/DOCS/File+Based+Configuration
dbpath = /srv/db/mongodb
logpath = /srv/db/mongodb.log
logappend = true
bind_ip = 127.0.0.1
port = 27017
fork = true
auth = true
# noauth = true
Do that file as linux executable
chmod +x /opt/bin/mongodb-start
chmod +x /opt/bin/mongodb-stop
We’ve also created a very basic “init script” as a wrapper around the mongodb-start and mongo-stop scripts described above. You will still need to modify and manage the configuration of your MongoDB server in the files above. This script only provides a means for ensuring that MongoDB will start at boot. Issue the following commands:
wget http://library.linode.com/databases/mongodb/reference/init-rpm.sh
mv init-rpm.sh /etc/rc.d/init.d/mongodb
chmod +x /etc/rc.d/init.d/mongodb /etc/init.d/mongodb
chkconfig –add mongodb
chkconfig –level 35 mongodb on
You will also need to create a user and group for mongodb; issue the following command:
useradd -M -r --home-dir /opt/mongodb mongodb
Now issue the following command to ensure that the MongoDB user you just created will have access to all required files in the /srv/db/ hierarchy:
chown mongodb:mongodb -R /srv/db/
To start and stop MongoDB using the init script, issue the appropriate command from the following:
/etc/init.d/mongodb start
/etc/init.d/mongodb stop
For checking the web admin interface of Mongodb – listening on port 28017
Check this URL : http://localhost:28017/
Before installing the Mongodb on linux box you need to install following packages on box.[root@sonyk-pc Download]# sudo yum -y install git tcsh scons gcc-c++ glibc-devel[root@sonyk-pc Download]# sudo yum -y install boost-devel pcre-devel js-devel readline-devel[root@sonyk-pc Download]# sudo yum -y install boost-devel-static readline-static ncurses-static
For 32bit user use following command[root@sonyk-pc Download]# wget http://fastdl.mongodb.org/linux/mongodb-linux-i686-1.6.2.tgz
For 64bit use following command[root@sonyk-pc Download]# wget http://fastdl.mongodb.org/linux/mongodb-linux-x86_64-1.6.2.tgz
[root@sonyk-pc Download]# tar xzf mongodb-linux-i686-1.6.2.tgz [root@sonyk-pc Download]# cd mongodb-linux-i686-1.6.2
Mongo stores database in data/db folder. so we need to create those folder using following command.
[root@sonyk-pc mongodb-linux-i686-1.6.2]# sudo mkdir -p /data/db/[root@sonyk-pc mongodb-linux-i686-1.6.2]# sudo chown `id -u` /data/db
Using following command you can start the mongo database.
[root@sonyk-pc mongodb-linux-i686-1.6.2]# ./bin/mongod./bin/mongod –help for help and startup optionsThu Sep 9 13:10:55 MongoDB starting : pid=22159 port=27017 dbpath=/data/db/ 32-bit
** NOTE: when using MongoDB 32 bit, you are limited to about 2 gigabytes of data** see http://blog.mongodb.org/post/137788967/32-bit-limitations
Thu Sep 9 13:10:55 db version v1.6.2, pdfile version 4.5
If you want to create mongodb service in linux then use following steps:
[root@sonyk-pc Download]# wget http://fastdl.mongodb.org/linux/mongodb-linux-i686-1.6.2.tgz [root@sonyk-pc Download]# tar xzf http://fastdl.mongodb.org/linux/mongodb-linux-i686-1.6.2.tgz[root@sonyk-pc Download]# mv mongodb-linux-i686-1.6.2 /opt/mongodb[root@sonyk-pc Download]# mkdir -p /srv/db/mongodb[root@sonyk-pc Download]# touch /srv/db/mongodb.log
[root@sonyk-pc Download]# mkdir /opt/bin/[root@sonyk-pc Download]# mkdir /opt/config/
create the File: /opt/bin/mongodb-stopPut following code in that file;
create the File: /opt/bin/mongodb-startPut following code in that file:
#!/bin/sh
/opt/mongodb/bin/mongod --config /opt/config/mongodb \## --upgrade \ ##runs a database upgrade option if needed \
File: /opt/config/mongodbPut following code in that file:
# Configuration Options for MongoDB## For More Information, Consider:# - Configuration Parameters: http://www.mongodb.org/display/DOCS/Command+Line+Parameters# - File Based Configuration: http://www.mongodb.org/display/DOCS/File+Based+Configuration
dbpath = /srv/db/mongodblogpath = /srv/db/mongodb.loglogappend = true
bind_ip = 127.0.0.1port = 27017fork = true
auth = true# noauth = true
Do that file as linux executatblechmod +x /opt/bin/mongodb-startchmod +x /opt/bin/mongodb-stop
We’ve also created a very basic “init script” as a wrapper around the mongodb-start and mongo-stop scripts described above. You will still need to modify and manage the configuration of your MongoDB server in the files above. This script only provides a means for ensuring that MongoDB will start at boot. Issue the following commands:
wget http://library.linode.com/databases/mongodb/reference/init-rpm.shmv init-rpm.sh /etc/rc.d/init.d/mongodbchmod +x /etc/rc.d/init.d/mongodb /etc/init.d/mongodbchkconfig –add mongodbchkconfig –level 35 mongodb on
You will also need to create a user and group for mongodb; issue the following command:
useradd -M -r --home-dir /opt/mongodb mongodb
Now issue the following command to ensure that the MongoDB user you just created will have access to all required files in the /srv/db/ hierarchy:
chown mongodb:mongodb -R /srv/db/
To start and stop MongoDB using the init script, issue the appropriate command from the following:
/etc/init.d/mongodb start/etc/init.d/mongodb stop
For checking the web admin interface of Mongodb – listening on port 28017Check this URL : http://localhost:28017/
You can use following commands for installing the Mysql workbench on Linux system. You can use following commands for installing the Mysql workbench on Linux system. you will get answer of how install mysql workbench, is very nice tool for creating the ER diagram on mysql database. I personally like that tool so much.
After installation you will find the mysql workbench under application-> programming menu
After that you will you will see the following screen.
clicking on new connection you can create the new connections and after that If you want to create the ER diagram then go to database Option and reverse engineering.
How you find this article for “how install mysql workbench”. please write to me.
Another mysql related article which will be helpful to you.
Using following article You can find the largest tables from Mysql database. Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted to post little query I use for the purpose so I can easily find it later, plus it is quite handy in a way it presents information:
I do some converting and rounding to see number of rows in millions and data and index size in GB so I can save on counting zeros.
The last column shows how much does the index take compared to the data which is mainly for informational purposes but for MyISAM can also help you to size your key buffer compared to operating system cache.
I also use it to see which tables may be worth to review in terms of indexes. Large index size compared to data size often indicates there is a lot of indexes (so it is well possible there are some duplicates, redundant or simply unused indexes among them) or may be there is long primary key with Innodb tables. Of course it also could be perfectly fine tables but it is worth to look.
Changing the query a bit to look for different sorting order or extra data – such as average row length you can learn quite a lot about your schema this way.
It is also worth to note queries on information_schema can be rather slow if you have a lot of large tables. On this instance it took 2.5 minutes to run for 450 tables.
UPDATE: To make things easier I’ve added INFORMATION_SCHEMA to the query so it works whatever database you have active. It does not work with MySQL before 5.0 still of course
This method or trick is important when your database crash and you are not able connect to database. Still your will be able to take mysql backup from file system. Many times we fail to restore the mysql dump from command line.
How to take mysql backup from file system
If your mysql server crash. Follow my steps to take mysql backup from file system without database connection.
First go to your filesystem.
Here I am using the fedora OS for this example
In linux go to /var/lib/mysql path
In that folder each folder means separate database.
Copy that all folders to your local machine.
again go to your mysql local installation and paste that folder into your mysql folder.
For in each database folder file you will find following types of files in the folder
db.opt
test.frm
test.MYD
test.MYI
Multi level unique is very important feature in mysql. This feature is present all databases. Using composite unique key we can achieve multilevel uniqueness. In article for create multi level unique id in mysql. Using this composite unique key we can achieve multilevel uniqueness.
create multi level unique id in mysql
Here I am going to give you how to create the composite unique key in mysql.
mysql> alter table `user` add unique `DATE_uuid` (`date`, `uuid`)
how to check indexes on table
mysql> show index from user;
+—————-+————+————+————–+————-+———–+————-+———-+——–+——+————+———+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+—————-+————+————+————–+————-+———–+————-+———-+——–+——+————+———+
| user | 0 | PRIMARY | 1 | user_id | A | 0 | NULL | NULL | | BTREE | |
| user | 0 | DATE_uuid | 1 | date | A | NULL | NULL | NULL | | BTREE | |
| user | 0 | DATE_uuid | 2 | uuid | A | 0 | NULL | NULL | | BTREE | |
+—————-+————+————+————–+————-+———–+————-+———-+——–+——+————+———+
3 rows in set (0.01 sec)
If you are programmer then you dont need introduction of array. In every language array is the most important part.
how to work with multidimensional array in php
Using array we can achieve so many things and array is useful for so many times when we do programming.
In this article I am going to share some method about PHP array.
What is PHP array?
An array in PHP is actually an ordered map. A map is a type that associates values to keys. This type is optimized for several different uses; it can be treated as an array, list (vector), hash table (an implementation of a map), dictionary, collection, stack, queue, and probably more. As array values can be other arrays, trees and multidimensional arrays are also possible.
Following is syntax
array(key => value)
Creating PHP array:
<?php
$arr = array("str_arr" => "this is string", 25 => true);
echo $arr["str_arr"]; // print this is string
echo $arr[25]; // print ture
?>