How to change the MySQL database collation ?

Few webmasters come asking such questions wherein they have a requirement to change the MySQL collation to overcome issues related to encoding of foreign characters. The best method to do it is make amendments to the MySQL collation from ‘latin1‘ to ‘utf8‘.

Below is the procedure to change MySQL database collation in PhPMyAdmin :

Step A : Login to cPanel control panel

Step B : Go to the phpMyAdmin

Step C : Choose the database for which you intend to make the changes

Step D : Hit the ‘Operations’ option

Step E : On this page, you should be able to located the ‘Collation‘ option

Step F : You can see a drop-down menu, here you need to choose the collation type

Step G : Hit ‘Go‘

One must take into account that, this would only make changes to the collation of your database for all the newly created or upcoming tables, while the previous would be left as-it-was.

Note: Before making any changes, it is strongly advisable to maintain the latest back-up of the database. To learn the procedure to take backup of your database, please visit : Backup Database from PHPMyAdmin

Most of the times, due to errors or bugs in the MySQL database tables, webmasters have to face various unknown issues. Whenever a database table gets corrupted, the most important thing you should do is to determine the issue behind it. It can be either because of the server crash or the database table is actually corrupted. By checking the recent /var/log/mysql.log you can verify that, what was the exact issue? And if there is such message in the log file, probably it is due to the server dying. Else, it has got corrupted because of a bug.

In this tutorial, we will go through the steps using two different commands on How to Check and Repair a Corrupted MySQL Database Table using Secure SHell (SSH) Linux command prompt ?

1) Login to your MySQL server through Secure SHell (SSH).

2) At the secure shell SSH prompt enter the following command:

mysqlcheck -r [database name]

Where, mysqlcheck is the command to check the database, -r is the command to repair the corrupted table. Replace the [databasename] with your actual database name.

MyISAM is a default storage engine of MySQL unless you change it. Most of the people uses MyISAM as their default engine to check and repair all the tables in the mysql database.

If the above commands doesn’t work for you, then you can try out the following commands via the linux shell prompt.

myisamchk -r /var/lib/mysql/[database name]/*

Where, myisamchk is a command to check mysql database and -r is a command to repair the corrupted tables.

How to optimize a MySQL database

Usually, for a website having large volume of data, optimizing the MySQL database becomes crucial for enhancing the performance to indexing. Indexing helps in a better collation of data and is an internal feature that comes with MySQL.

Consider a table “abc” that has a set of rows may be 2 wherein one row would have a set of numbers and the second having the relevant name/details of the individual. What most webmasters would do is, run a basic query stated below :

SELECT * FROM sample WHERE number = 5;

In this case, what MySQL does is, it runs through all the information that is there in the database and give a result which has a value set of 5. But, this query would probably prove ineffective if you have a large data ie. there are say a trillion entries in the table. This query would take a lot of time for generating an expected result.

But, since you have a distinct “number” field, an index can be created. By creating an Index, you would be basically creating an internal register which gets saved in by the MySQL itself. The below query can be used for creating an Index :

ALTER TABLE sample ADD INDEX (number);

Upon creation and setting of the Index, later whenever you wish to fetch some information pertaining to the individual who has been assigned the number 5, the service would straight-way go to it by using the index, hence generate a result at a much faster pace than the earlier query.

Assuming that you have even larger database,then its loading time would make a significant difference in the process of Indexation which can take longer time hence result in a degraded performance of your web applications due to slower load time.

Here, in such a case it becomes necessary for you to optimize your MySQL Database by using the below query which would enhance the speed and decrease the loading time of your database:

OPTIMIZE TABLE sample;

Upon optimization, your MySQL service would take lesser time for searching through your database and offer results in a drastically reduced time, hence avoiding any unwanted load on your database, which would ultimately result in a better overall performance of your web applications.

Note: MySQL is included with our Cheap VPS Hosting and Linux Hosting packages for free.

Q : How can I back up a database on Dedicated server using SSH?

You must follow the steps as described below inorder to back-up the MySQL database on a Linux dedicated server using SSH.

Step 1 : First of all you must root log-in to your linux dedicated server using SSH

Step 2 : Then, enter the below command to take backup of the MySQL Database

mysqldump -h host -u<databaseuser> -p [<NameofDatabase>]  > BACKUP.sql

Note : Here you must replace “[<NameofDatabase>]” with the <exact name> of your current database which you intend to backup.

With these simple steps you can easily take backup of your database, where the file created would be BackUp.sql .

Q : How can I restore a database on Dedicated server using SSH ?

Now that you have the backup file with you, you can restore it as well, whenever required.

Inorder to do so, you again need to follow few simple steps as stated below.

Step 1 : You must be logged into your dedicated hosting server as root using SSH

Step 2 : Locate the dump file of the MySQL database.

Step 3 : Once in it, you must manually enter the below command for importing it into the chosen database :

mysql -h host -u<databaseuser> -p [<NameofDatabase>]  < BACKUP.sql

NOTE : Replace “[<NameofDatabase>]” with the <exact name> of the database backup file (here it is BACKUP.sql) for restoration.

It may so happen that the dump file of the database contains a “CREATE DATABASE“clause, in that case, you must erase this line from the dump file. Any text editor can be used for that purpose.

We will discuss the technical possibilities of hosting in this article:

Question: Why do customers have to know about the technical capabilities of hosting and hammer head with unnecessary problems, is it not the task of web design studio – to pick the best technology?

Answer: Not exactly. Technologies that are supported by a hosting provider can not be supported by others. If web studio use the technology that is rarely supported from the hosting companies, and tomorrow the client does not like his current hosting and he decides to change the host, then most likely it will be a long process to find those companies that support those technologies, as the choice of technology will be small and have to live with something average in quality and price, or pay money to web studio for the transfer of copyright to new technologies. In any case, you will lose money and time. We are not talking about static web sites designed only to HTML – these sites can be placed at any provider, but the age of such sites has already passed and the holders of such Internet representations look very profitable against its competitors with interactive dynamic sites.

Question: Can you tell me any advanced and skilled technologies that are supported by almost all web hosting companies?

Answer: Yes. The widely used scripting language PHP and the world’s most popular open source database MySQL are the technologies that are supported by most of the web hosting companies.

And the first and second relate to open source technologies, ie, these technologies can be used free of charge, to develop and maintain a large number of programmers and enthusiasts around the world, it is their main difference from the private commercial technologies such as ASP (ASP.NET), SQL.

Question: Who will be responsible if the site written in PHP and MySQL fails due to some internal error in PHP or MySQL? To whom shall I make a complaint?

Answer: Firstly, PHP and MySQL are rightly considered the most reliable technologies, as technologies developed by the entire international community to quickly debugged, if at all there is some bug found. Secondly, the customer can buy a license to support PHP and MySQL, which will enable him to immediately contact the developers of technology in the event of any errors that occurred through no fault of the creators of the site. Thus, the client will have protection itself by 100 percent.

The technologies PHP and MySQL supported by most web hosting providers, since they are compatible with virtually all operating systems, easy to install, configure, operate and possess greater functionality. Moreover, the PHP Hosting and MySQL Hosting on operating systems, UNIX / Linux is the cheapest web hosting. For comparison, hosting with support for ASP, SQL is running the Windows operating system is at 50-70 percent more expensive and has less reliability mainly due to greater instability of Windows.

Windows is a user-friendly operating system for personal computer, but does not operate well as a server operating system.

Q. Do You offer load balanced apache servers and mysql dedicated servers?

Ans : Yes. We offer load balanced apache servers (Web Servers) as well as MySQL servers. We are hosting a couple of biggest forums in the world which use multiple apache servers and high configurations quad core xeons for MySQL. Clustering of multiple MySQL servers is expensive but clustering of apache servers is quite simple and the only cost involved in it is for the dedicated servers. Basic dedicated hosting server configurations are available at http://www.eukhost.com/dedicated-server-hosting.php but high configuration quad xeon servers can be setup as per requirement.

Please contact our sales department for custom quote.

For Load Balanced Web servers’ architecture, we will setup 2 or more similar configuration Web Servers which will be behind the Load Balancer. Each Web Server will have a public IP assigned to it as well as a Internal LAN IP. We prefer LAN over VLAN as internal communication of the Web Servers and Database Server(s) can be completely isolated from external traffic. After the implementation of Load Balancing routing rules on the Load Balancer, we configure Firewall rules to allow web traffic on the Web servers to pass trough the Load Balancer. Our Office IP and Customers IP will be the only IPs to have remote access of all Servers. No other IP will be able to access any of the Web Servers or Database Server(s). Load Balancer will take care of equal distribution of traffic on each of the Web Servers and each of your web servers will then pass on SQL queries to the Database Server. Web Servers will allow only inbound connection on port 80, while SQL servers will allow only inbound connection on port 1433 if using MSSQL on the Database Servers or port 3306 if using MySQL on the Database Server. Database Servers will allow incoming SQL queries only from the LAN IP of the Web Servers. Inbuilt TCP / IP firewall settings of Linux 0S (Load Balancer) will be utilized for this setup and this will eliminate any requirement of additional Hardware Firewall. Firewall rules on the Web Servers and Database Servers will be configured using inbuilt IP security Firewall if Windows or TCP/IP based host access control policy if using Linux Servers. We have expertise in setup of such Load Balanced secure clusters and there’s absolutely no reason to be concerned about server security, Load Balanced routing rules or Firewall settings.

We also use some specific Load Balancers procured from Cisco and Juniper for such Load Balanced clusters. We prefer CSS 11000 (Cisco) & Juniper TX2200 for high-end clusters.

Drupal Website Hosting

Do you offer Drupal Hosting?

- Yes, Drupal is supported on all our web hosting packages. PHP and MySQL are the important requirements for  Drupal Hosting and they are available on all our web hosting plans. We would suggest a linux web hosting package for hosting a Drupal Website as PHP MySQL is the perfect combination on linux servers along with cPanel.

Drupal can be installed at a one click through Fantastico which makes it much easier. cPanel is the most easy to use control panel and allows you to manage database and other tasks of Drupal application.

All our servers are Drupal-optimized and performance oriented. Our support team have expertise in managing Drupal websites and they are available 24×7 to provide technical assistance.

Drupal is one of the leading content management systems (CMS) around. Drupal is free and open source CMS written in PHP programming language. Power features and easy management tools of Drupal supports variety of websites. Small websites, blogs or major community websites can be published via Drupal. Drupal meets the website requirements of all small and big organisations. Drupal includes Community web portals,Social Networking sites,E-commerce applications ,Personal web sites or blogs and many such portals.

Drupal is easy to install and use where you won’t need to be expert in programming language. Search engine friendly URL’s, categories and search functions are some of the features on Drupal. Additional modules with Drupal adds more functionality to a website. Drupal installation can easily be converted to a forum, blog, wiki or any other website as per requirements.

All you need to do is open a Drupal web hosting account where you can easily have the Drupal application installed.

Mambo CMS Hosting

Do you offer Mambo Hosting?
Mambo website hosting is supported on all our servers.

Mambo is most powerful content management system (CMS) around. With the help of web based interface along with user friendly features you can create simpler as well as more complex corporate websites. Mambo is PHP MySQL based so Linux web hosting as well as  windows web hosting package will suited for Mambo website.

eUKhost is most trusted company in UK for Mambo Hosting due to the high level security on our servers. We have enabled mod_security rules on our servers which filters intrusions, hacking attempts and other invalid arguments passed by the intruders.

Mambo is easy to install on a website. If you have cPanel hosting package, you can install Mambo CMS at a one click through Fantastico.

All you need to do is select appropriate domain name and proceed with Mambo installation.

Mambo is easy to use or manage and website can be customized from easy to use administrative panel. You won’t need to be an IT expert as it is suited for newbies as well as advanced users.You can have a look at Mambo tutorial to learn about its functionality and design. Default theme of Mambo can be changed anytime and and can be customized as per your requirements.You can change it to a custom Mambo template which can be used for a website.

Mambo functionality can be further extended by adding extra tools for your website. It will help you to create more advanced websites like ecommerce website, forums, gallery etc. Mambo is an excellent website building tool through which you can create personal as well as business website.

How to Backup Database from PHPMyAdmin

You are able to backup databases in a variety of ways, one of which is via the Backup Facility with cPanel but there are other alternatives to Backup MySQL databases without the need of that facility on its own. You can backup via phpMyAdmin in the Export section of the facility. Just like the backup facility in cPanel, it can give backups to you as a .tar.gz format which means you’ll be able to restore it via the cPanel facility if you need to do so.
• Once you’ve accessed the phpMyAdmin facility go into the database (empty or otherwise) and from the top menu click Export.
• Scroll down on that page until you get to a ‘Save as file’ option and select ‘gzipped’ and leave the settings as default.
• Click ‘Go’

To restore via the phpMyAdmin facility unfortunately you’ll already need the database setup to restore the inner contents, such as the tables, etc. So create a new database (name it the same as the database name that you backed up previously). Once you’ve created the database and you’re inside it, from the top menu again click Import.
• Simply select ‘Browse’ and find the relevant gzipped file, and leave all settings as default.

If all goes well you should see the contents of the database all there. Remember just like the cPanel backup facility, if you use usernames for each database you’ll need to set that up individually, for instance if you restored the backup via the cPanel facility. Make sure you re-connect the username and database together.

Q. Is access to the MySQLDump and MySQL utilities available?

Ans. We offer PHPMyAdmin utility which allows you to dump and restore your MySQL database from your end. cPanel Hosting customers can access PHPMyAdmin from the MySQL section of their control panel, whereas Windows Hosting Packages customers can access PHPMyAdmin from http://www.domain.com/webdataadmin/

You can also enable remote access for your database from your control panel to connect using dreamweaver, MySQLYog, or other MySQL interfaces. There are many other tools available for authenticated MySQL remote access which can be used from your end.