MySQL Databases (39 Articles)

How to export/import a MySQL database via SSH

In this article we will show you how to export and import a MySQL database via SSH. Managing your databases via SSH is especially useful when dealing with large databases (over 50Mb). SiteGround customers can find more information on how to connect to and use SSH in our SSH tutorial. 1. Exporting a MySQL database […]

How to change the password of a Mysql user in cPanel?

If you have already created a MySQL user through the cPanel -> Mysql Databases tool you may want to update its password. You can complete the task through the same tool -> Current Users section. Click on the respective user and a new MySQL Account Maintenance page will be opened. There you can change the […]

How to reset the password for a MySQL database?

You may need to change the password of your database in order to improve the security of your website or if your account has been compromised by hackers. Basically the easiest way to change the password for your database is to recreate the MySQL user attached to it. You can use the following instructions to […]

I cannot create views in MySQL

Regular MySQL users do not have privileges to create views in MySQL. If you try to execute CREATE VIEW with a regular user, you will receive the following error message: #1142 - CREATE VIEW command denied to user 'user'@'localhost' for table 'table' If you want to create views in your databases, you should do it […]

What are MySQL triggers and how to use them?

The MySQL trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table. The trigger can be executed when you run one of the following MySQL statements on the table: INSERT, UPDATE and DELETE. It can be invoked before or after the […]

How to use MySQL stored procedures and routines?

MySQL stored procedures and stored routines are supported after MySQL 5. All SiteGround clients can use MySQL stored procedures and routines  on their web hosting accounts. To use stored procedures, you should first create a database and add a database user to it. Make sure that you select the "ALL PRIVILEGES" option when you add […]

How to access phpMyAdmin directly from my domain?

In order to access phpMyAdmin directly from: http://yourdomain.com/phpmyadmin you need to install phpMyAdmin on your hosting account. This way you won't have to first log in to your cPanel when you want to use phpMyAdmin. Follow the steps below to complete the installation:  Download the installation package from this page. Extract the package on your […]

What is the default path to the MySQL binary?

The correct path to the MySQL binary at all SiteGround servers is: /usr/bin/mysql cPanel is easy to work with when you have the right host to support you. If you need a reliable partner to help you manage your website with cPanel, check out our cPanel hosting services!

How to change the MySQL timezone

When you develop your website, you may have to compare a certain date/time with the current date/time on the server. The timezone on SiteGround's servers is CST. This is a global setting which cannot be changed. You can, however, use an easy workaround if you wish to use a different timezone - you can modify […]

PHP script for MySQL database import

You should start by creating a file called for example import.php and placing it in your public_html folder. Then edit the file and paste the following code in it:  <?php passthru("nohup mysql -u USERNAME -pPASSWORD DBNAME < dump.sql");  ?> Where USER,PASSWORD and DBNAME are your details for the database in which you want to import […]

How to optimize a MySQL database

One of the most important prerequisites for achieving optimal MySQL database performance is indexing. Indexing is an internal MySQL feature that allows faster gathering of data. Let's take an example table called "sample" with only two rows - "number" and "employee". If you run a simple query such as:

MySQL will check all records […]

How to export a large database using phpMyAdmin?

If you have a large database, it may be difficult to export all of its tables at once through phpMyAdmin. The most common reason for this is the timeout limit most web hosting companies set on their shared servers. Fortunately, there is an easy solution for this issue. Instead of selecting all tables in the […]

I’m getting a ‘Server shutdown in progress’ MySQL error

There are two main reasons for this error message to show: 1) The MySQL query you are trying to execute takes too long and the MySQL server times out. The solution for this issue is to optimize your database for the queries which fail. 2) You have a crashed table in your database. The solution […]

How to change the database engine of a MySQL database table?

In this article we will show you how to change the database engine of a MySQL table. Let's assume that you have a database table called my_table using MyISAM engine and you wish to change the engine from MyISAM to InnoDB. The MySQL query that should be used is: ALTER TABLE my_table ENGINE = InnoDB; […]

How to repair a MySQL database?

In this article we will show you two ways to repair a MySQL database. Through cPanel: Log in to your cPanel and go to MySQL Databases. In the section Modify Databases, select a database from the drop-down box next to Repair DB:  and then click the Repair DB button. If the database engine used by […]

How to change the collation for all tables in a MySQL database to UTF-8?

Changing the collation for all tables in a MySQL database can be time consuming depending on how many tables you have. That's why we recommend using the following PHP script for changing the collation for all tables at a time: <?php $db = mysql_connect('localhost','myuser_mydbuser','mypassword'); if(!$db) echo "Cannot connect to the database - incorrect details"; mysql_select_db('myuser_mydbname'); […]

Multi-language applications and UTF-8 databases

By default, the databases on SiteGround's servers are created with latin1_swedish_ci collation. This may cause some issues if you use your application with languages with non-standard characters (Cyrillic, Chinese, etc.). Therefore, the best practice prior to installing your application is to modify your database to use UTF-8 collation first. This can be done through the […]

How to optimize a MySQL database using phpMyAdmin?

It is always a good idea to keep your databases' tables optimized. Fortunately, making this optimization is quite easy when using the phpMyAdmin tool available in cPanel. To perform the optimization, log in to your phpMyAdmin and select the database whose tables you wish to optimize. A list with all the database's tables will appear. […]