If you are like me on a shared/reseller/dedicated hosting server chances are your web hosting account has cPanel or some other administration panel through which you can create/delete MySQL databases (DBs)/DB users. But you will not be able to:a. Take selective backup. i.e., backup of only some tables in a DB in different format ( .sql , .gz ,.zip etc)

b. Execute queries. You might have seen that some scripts give you a .sql file that needs to be ‘executed’ in your DB so the tables in it get created/populated.

Enter phpMyAdmin to the rescue. It is a nice web interface to control your MySQL DB. Most hosts already provide phpMyAdmin but it’s usually out-dated.

This is a step-by-step tutorial for installation of phpMyAdmin on your shared/reseller/dedicated hosting space using FTP access or shell access.

1.ftp::Download the latest version from http://www.phpmyadmin.net/home_page/downloads.php.

The latest version is at the top. You can download the .zip file.

Shell::wget
http://mesh.dl.sourceforge.net/sourceforge/phpmyadmin/phpMyAdmin-2.9.0-rc1.tar.gz

2. ftp ::Extract the contents of the .zip file using a program like winrar.

shell:: tar -zxvf phpMyAdmin-2.9.0-rc1.tar.gz

3. [Optional] You may want to delete unneeded language files. This saves time while uploading. Go into ‘lang’ folder and delete all files except a) all the .sh files and b) the three .php files having names starting w/ ‘english’.

4. In this tutorial scope, we are going to have phpMyAdmin access and control all the tables in a single database that you are going to create.

a. Create a DB using cPanel

b. Create MySQL username and a password for this user

c. Assign all privileges to this user to access the DB

5. Edit the config.inc.php file.

a. Usually it is ‘localhost’.

$cfg[’Servers’][$i][’host’] = ‘localhost’; // MySQL hostname or IP address

b. Enter MySQL user you created in step 4 b.

$cfg[’Servers’][$i][’controluser’] = ‘MySQL_user’; // MySQL control user

settings

c. Enter MySQL user’s password that you created in step 4 b.

$cfg[’Servers’][$i][’controlpass’] = ‘MySQL_pass’; // access to the

“mysql/user”

d. Save the file and close it.

6. ftp::Log into your web space using a FTP client and create a directory, say phpmyadmin under public_html. Upload all the content into the directory created.

Shell:: For the convient move the phpMyAdmin fodler to phpmyadmin using following command under your public_html folder

mv phpMyAdmin-2.9.0-rc1 phpmyadmin

7. Installation is complete. Simply point your browser to the directory where you installed phpMyAdmin and you should be able to start using it.

8. Important: Password protect the directory where you installed phpMyAdmin or else anyone will be able to access it! You can do that using the admin panel that your host provides.

CRON
Cron is a linux utility that schedules a command (or commands) to be executed periodically at certain times. They can perform user-defined tasks such as backing up databases, scheduling mailing lists, clearing old records, etc etc.

To set up a CRON Job, you need to know two things.

1. The command that is to be executed (often a user written script or a UNIX command)
2. How often the command is to be executed (what days, months, dates, hours etc etc)

There are following different format to get the backup of mysql database using cron job

1) Simple cron job syntax to take database backup
* * * * * /usr/bin/mysqldump -u databaseusername databasename -ppassword > /path of database store.sql

2) Compress the database backup in gz format
* * * * * /usr/bin/mysqldump -u databaseusername databasename -ppassword|gzip > /path of database store.gz

3) Compress the database backup in bz2 format
* * * * * /usr/bin/mysqldump -u datbaseusername databasename -pdatbasepassword|bzip2 -c > /path of database store.bz2

Forgot the MySQL root password :
First you will have to stop the MySQL service on the server.
# service mysqld stop
Start MySQL as
# /usr/bin/safe_mysqld –skip-grant-tables &
Or
# /usr/bin/mysqld_safe –skip-grant-tables &
safe_mysqld is a script that starts mysqld and traps any forceful terminationof the MYSQL server and it privents any database corruption.
Change the password for root user as
# mysql -u root mysql

mysql> UPDATE user SET Password=PASSWORD(’new_password’) WHERE user=’root’

mysql> FLUSH PRIVILEGES;

mysql> exit;
Start the MySQL service:
# service mysqld start

Cpanel do not provide any utility to convert .dmp file to MySql database. There are third party software which can be use to convert .dmp file to MySql database.
Excel-to-MySQL is a program to convert Microsoft Excel spreadsheets into MySQL databases. Depending on your privileges on the target MySQL server you can export MS Excel data into new database or overwrite the contents of an existing MySQL database.
Requirements

• Windows 2000/XP or Windows NT 4 with Internet Explorer 5 or later or Windows 98/ME or Windows 95 with Internet Explorer 5 or later
• At least 32 MB of available memory

VServer user’s option
If you are using Virtual Server hosting you probably have no sufficient privileges to create new databases on the target MySQL server. For this case we recommend the following approach:

1. ask your Virtual Server administrator to create blank database for you
2. run Excel-to-MySQL and select this database as the conversion target
3. click “Yes” when you will be asked if you’d like to overwrite existing MySQL database

When you complete these steps, the entire contents of the source MS Excel spreadsheet will be imported into the specified MySQL database.
MySQL dump file
Excel-to-MySQL allows users to perform deferred conversion and get more control over the process. Following this way, the program stores the contents of the data source into a local dump file instead of moving it to MySQL server directly. The resulting file contains MySQL statements to create all tables and to fill them with the data. You will be able to import this file into existing MySQL database via MySQL client as follows:
mysql.exe –host=… –user=… –password=… -vvf {MySQL database name}

If you want to copy existing database to another, perform the follwing steps and you will be able to copy the existing database to another database.

1)Create New database from Cpanel >> MySql >> New Database.

2)Go to WHM >> phpMyAdmin >>
3)Select the database which you want to copy it.
4)Select menu Operations from menu bar.
5)Insert name of database under “Copy database to:”
6)Select Structure and data.
7)Go.

After Completing those seven steps you will be able to copy database.

The following article explains how to access your MySQL database remotely.
1)Go to Cpanel >> MySQL® Databases >> Access Hosts: >> Add IP of the machine from which you want to access databse.
2)Click on Add host.In order to access your MySQL database, please follow these steps:

1. Log into your machine from which you want to access database remotely.
2. Open the MySQL client program on the server in the /usr/bin directory.
cd /usr/bin

Give the command
# mysql

3.Type in the following syntax to access your database:

$ mysql -h {hostname} -u username -p {databasename}
Password: {your password}
mysql >
* hostname: the name of the MySQL server that you are assigned to, for example, domainname/servername
* databasename: The name of your MySQL database
* password: The password you use to access your MySQL database