Tag-Archive for ◊ mysql ◊

Backup MySQL using VBS scripts
Monday, January 12th, 2009 | Author:

I have seen so many people searching ASP or ASP .NET scripts to backup their MySQL databases. Not only this but my Blog has been getting to many hits for the search term “backup mysql asp”. After seeing the need of so many people for the script I decided to write a VBS script to backup all or single MySQL database on Windows server. I am not very good at ASP so I didn’t write the script in ASP or ASP .NET but one can convert this script in either ASP or ASP .NET, it should not be a huge problem I suppose.

backup-mysql-asp more…

Backup and Restore MySQL database using phpmyadmin
Monday, October 13th, 2008 | Author:

Log into Plesk Control Panel and  steer yourself to “phpMyAdmin

For Backup of MySQL database click on Export.

1) Make sure that entire database is selected for Export, and not just one table.
All the Tables under Database list should be added in Export List.

2) Now, follow the steps given
select “SQL” for output format
Check “Structure”
and “Add AUTO_INCREMENT” value
Check “Enclose table and field name with back quotes”
Check “DATA
check use “hexadecimal for binary field”
Export type set to “INSERT

3) Check “Save as file”, it is not necessary to change the file name.
Click “GO” to download the backup file.

Backup of your MySQL database is created !

Now, for Restoring backup of MySql database

1) Click on SQL tab, to start restoration of your backup file.

2) Un-Check “Show this query here again”.

3) Browse to your database backup file.

4) Click – “GO”

Backup is Restored !

Large MySQL Backups:

Error: MySQL has gone away

To restore large SQL backups there are 2 setting that you will need to take of:

1. Make sure that you PHP upload size is great than your MySQL backup file, this setting will be needed to be changed in php.ini file. Only those who has server administrator access would be able to make changes in PHP.INI file. Hence you will have to contact your Hosting provider if you are hosted on a Shared server. But if you have a Dedicated Server then this file is normally stored in C:\Windows\php.ini or you can esily get the path from phpinfo() page. The option that you need to change is upload_max_filesize. It is also suggested that you increase the following parameters as per the size of your MySQL backup file:

max_execution_time = 30     ; Maximum execution time of each script, in seconds
max_input_time = 60    ; Maximum amount of time each script may spend parsing request data
memory_limit = 8M      ; Maximum amount of memory a script may consume (8MB)

2. You will need to increase the MySQL buffer size in MySQL configuration size, the file is my.cnf in Linux server and my.ini on Windows Server. The parameter that you need to change is:

read_buffer_size

By default it is set to 1MB, if the entry is not present then add it under [MYSQLD] section.

Please Make sure that you restart IIS webserver after making change to php.ini and restart MySQL after making changes in my.ini file.

If you looking backup MySQL using VBS or ASP script then refer the link below:

How to backup MySQL using VBS or ASP script.

MySQL Database Indexing
Sunday, September 28th, 2008 | Author:

What is Database Indexing?
Database index is like data structure that improves the performance of a database.
Index on table can be created one or more column on databse table, which improves random access of any records and efficient access of ordered records.

Types of Indexes in MySql

1. Normal Indexes :-
Normal Index have not restriction like Uniqueness,it is a basic index.

2. Unique Indexes :–
It is like a Normal index but only one difference, all values of the indexed columns must only occur once.

3.Primary Keys :-
Primary keys are basically unique index and must be add “PRIMARY KEY” in specific column.

How to Create Index in Mysql at time of table creation.

CREATE TABLE student (
fname VARCHAR(30),
lname VARCHAR(30),
studID INT, INDEX (studID)
)

You can create index on existing table, used following MySQL Statement

CREATE INDEX index_studID ON student(studID)

Advantages of database index
1. Database indexes speed up the database selection operation.

Advantages of database index
1. Database indexes slow down the database insert,update,delete operations.
2. Database Index takes more space.

Category: MySQL Hosting |  Tags: , , , | Comments off