Moving all of the tables from one mysql database to another is very easy. It can be done using phpMyAdmin to dump the database to a .sql file. However, in this tutorial, we are going to dump the database to a .sql file using the command line interface. With the command line, you can complete your task more faster compare to the phpMyAdmin method.

In order to start, you need to be a user who has full privileges in both the databases or should be a root user. Usually, it is not necesssary to use username and password when dumping a mysql database in Linux cPanel Hosting. It won’t prompt you for the password and automatically takes the username and password. Follow the below steps:

How to Dump the MySQL Database to an SQL file

Enter the following command to dump a database:

mysqldump database_name > Database_name.sql

For example, suppose you wanted to dump a database called “eukblog”.

mysqldump eukblog > eukblog.sql

Now, since you have the database backup ready, simply import the contents to your destination database (i.e. another database).

mysql new_database_name < Database_name.sql

Here we are going to use the new destination database known as “neweukblog”.

For Example:

mysql neweukblog < eukblog.sql

Once the transfer is completed, you will see two databases that are similar, but with different names.

To provide multi user  access to number of databases a relation database management system is been developed that runs on a server and called as MySQL. MySQL is developed under GNU General Public License and its source code is available under its defined terms. MySQL is a popular choice of database for use in web applications and it performs really well with cPanel hosting accounts to store data of any websites. And suppose if you have forget the MySQL root password and you are not able to access your mysql service then you need to reset the password of MySQL root user by using following steps which are as follows,

Steps to Reset MySQL root Password :

1)root@server [~]/etc/init.d/mysqld stop

2)Start to MySQL server without password:
root@server [~]mysqld_safe –skip-grant-tables &

3) root@server [~] mysql
After that you can get the mysql prompt then run the following command

4)mysql >use mysql;

5)mysql >update user set password=PASSWORD(“NEWPASSWORD”) where User=’root’;

6)mysql > quit

7)root@server [~]/etc/init.d/mysqld stoproot@server [~]/etc/init.d/mysqld start
Now you can access your mysql service by using
[root@server ~]#mysql -u root -p

The article would help you with a step-by-step procedure about how to copy a table with phpMyAdmin located within the cPanel control panel.

Step 1 : Log into cPanel

Step 2 : Go to phpMyAdmin and choose the database and the table that you intend to copy

Step 3 : Go to the option titled “Operations” listed amongst the other tabs

Step 4 : Locate the box titled “Copy table to (database,table): ”

Step 5 : There are two options you may choose from ie. either create a copy of the table to the current database (usually gets selected by default) OR copy the table to a different database

Step 6 : Choose the database name from the drop-box and the name of the table to copy the structure and / or data into.

You are required to choose between the following options before proceeding any further :

• Structure only – Selecting this option would only create a copy of the table and its name. You must note that the data wouldn’t be copied in this case.

• Structure and data – Choosing this option would allow you to copy the table along-with its name, but would also copy the data it contains to the destination you choose further in the process ie. INSERT INTO >> SELECT * FROM …
• Data only – Clicking the radio button would only copy the data to the destination.

Furthermore, you are offered the option to choose whether you wish to drop the table that you are copying to before creating the table and copying the data. This runs a DROP TABLE IF EXISTS query for the copy of the table, not the original. You would not notice any change if you’ve chosen data only copy.

Then you’d have the option to choose to copy the auto increment value. This isn’t a default function, and you’d need to check it manually. The auto increment value will end up being whatever it would be after the INSERT INTO query is done. Checking the box would activate the CREATE TABLE syntax which would then include the auto increment value from the original table.

Lastly, you can see the option termed “Switch to copied table ”, as the title suggests you’d be switched over to the copied table once the queries are completed. If you leave it unchecked, the resulting page will still be looking at the original table; if checked it will change to the copied database (if applicable) and table.

–

Like this post ?

Share on your Social Networking Profile ( Facebook, Twitter & Google+ ) and get a flat 10% Recurring discount on our VPS Hosting and Dedicated Servers.

Email us the shared link at : sales@eukhost.com or speak to our live chat operator now, by clicking on the “Live Chat” Scroller on the left-hand side of this page and we will provide you with the discount Coupon right away!

MySQL Database is named as a relational database management system that runs a server providing multi-user access to a number of databases. It is a Free-software-open source project developed by a community of programmers with a full-featured database management system called MySQL. Collation in MySQL Database is a set of rules used in comparisons. Because many people use MySQL with data to be stored in languages other than English, they need to select the rules of comparisons which in turn depends on the character set used for storing that data. To solve problems related with foreign character encoding you usually change your MySQL collation The most common method to change MySQL collation from latin1 to utf8 can be done by using phpMyAdmin and following the instructions below:

1. Please Enter successful log-in details of your cPanel hosting account and click on the phpMyAdmin icon in the Databases box.

2. Select the database you wish to manage from the drop-down menu on the left.

3. Click on the Operations tab in the top menu of your phpMyAdmin.

4. At the bottom of the page you will see the collation option. You can now select a collation from the drop down menu and click on the Go button.

Please make a notice of this thing, once you change the collation of a database only the new tables will get created with the new collation and all other tables remain with the collation where they are initially created. And because of convenience of the customers, most web hosts have set MySQL’s collation to utf8 by default. You can also benefit from this feature by joining eUKhost. The UK web hosting service provider offers reliable and secure hosting services from last decade to thousands of customers with reliability and security to run any online business.

In web hosting industry cPanel is the only control panel which supports easy to understand GUI based (Graphical User Interfarce) to mange hosting resources of a website. cPanel hosting supports different  features to mange hosting resources which includes Spam Assasian,  Email forwarding, Awstats,  MySQL to support and manage databases of a website. And it certain time it become s necessary to upgrade from a older version of MySQL. So to upgrade you MySQL version you just have to Login to WHM with root user and access the option Server Configuration then Tweak Settings. Look for MySQL and check the radio button for 5.0 from 4.1 OR login to the server via SSH and open the file /var/cpanel/cpanel.config and find for mysql-version. Change it to 5.0 from 4.1. Save the file and exit. Now execute the commands on the server to upgrade the mysql version.

/scripts/upcp –force

/scripts/mysqlup –force
Wait for mysql to upgrade. Once the process is completed, check the mysql version.

To make changes in cPanel some of newbies as well as online organizations rely on their hosting service provider eUKhost web hosting service provider is leader in tech support services from last decade to deploy quality tech and phone support with different server technologies which includes UK Cloud hosting servers, Windows hosting server and highly configured dedicated servers.

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.

–

Like this post ?

Share on your Social Networking Profile ( Facebook, Twitter & Google+ ) and get a flat 10% Recurring discount on our VPS Hosting and Dedicated Servers.

Email us the shared link at : sales@eukhost.com or speak to our live chat operator now, by clicking on the “Live Chat” Scroller on the left-hand side of this page and we will provide you with the discount Coupon right away!

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.

–

Like this post ?

Share on your Social Networking Profile ( Facebook, Twitter & Google+ ) and get a flat 10% Recurring discount on our VPS Hosting and Dedicated Servers.

Email us the shared link at : sales@eukhost.com or speak to our live chat operator now, by clicking on the “Live Chat” Scroller on the left-hand side of this page and we will provide you with the discount Coupon right away!

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.