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 To export a MySQL database, you need to use the mysqldump command. Here is the full command for exporting your database: mysqldump -uUSERNAME -pPASSWORD DATABASE > backup.sql Make sure you replace USERNAME, PASSWORD and DATABASE with the appropriate values for your database. The MySQL database will be exported to a file named " backup.sq l " in your current directory. The name of the MySQL dump you are exporting to can be whatever you want. 2. Importing a MySQL database To import a MySQl database, you need to use the mysql command. Here is the full command for importing a MySQL dump into a database: mysql -uUSERNAME -pPASSWORD DATABASE < backup.sql Make sure you replace USERNAME, PASSWORD and DATABASE with the appropriate values for your database. For DATABASE you must use an existing…

Error about legacy type authentication (old-style) when connecting remotely to MySQL

This particular error can occur in some MySQL clients. The reason for it is that our servers support both the old and new password hashing for database users. Thus, if your MySQL client sees that we support both types, it may refuse connecting with an error like: A workaround would be using a MySQL client such as MySQL WorkBench configured to accept Legacy connections. This can be done by following these steps: 1. Right-click on the database button. 2. Choose Edit connection. 3. Click Advanced. 4. Add useLegacyAuth=1 to the bottom of the 'Others' box. Alternatively, if you need to use some specific MySQL client that has no workaround to it, you can open a support ticket so our team can disable the legacy hashing…

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 "Set Password" button next to the respective user. There you can change the old password by entering the new password into the two password fields. You can also use the built-in Password…

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 do so: Log in your cPanel -> MySQL Databases . Find the corresponding database and see which is the user attached to it. For example we will consider that your database is called yourusername_database and the database user attached to it is yourusername_user . Scroll to the bottom of the page and you will see a field called Current Users . There is a little circle with X in it – press it to delete the database user yourusername_user . You will be prompted to confirm this action: Are you sure you wish to permanently remove user yourusername_user? Click on the Delete User button in order for the database user to be successfully deleted. Then you should create a new database user: Go back to cPanel -> MySQL Databases and create a new user: Type the same database username and type (or generate) the new password for it. Bear in mind that you will be able to change only the user part of the database name yourusername_user . The prefix yourusername_ is added by default by the system. When the MySQL user is created you have to attach it to your database/s. On the same page you will find a section called Add User To Database – just assign the database user to the desired database/s and grant all privileges for it. NB : Note that if the database is used by a script/web application (WordPress, Joomla etc.) you will have to reconfigure it and set the new password inside its configuration file. If you are not certain how to do this, you can post new ticket in the SiteGround’s Help Desk and we will gladly assist you with the re-configuration. You can find more details on how to create MySQL databases/users in our MySQL tutorial: http://www.siteground.com/tutorials/php-MySQL/MySQL_database_user.htm We strongly recommend you to create a full backup of your website prior making any major changes 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 with your main cPanel username. It has the necessary privileges to execute CREATE VIEW on the server.…

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 event. Triggers are available in MySQL 5.0.2 and later. You can find detailed explanation of the trigger functionality including its syntax in the following article: http://dev.mysql.com/doc/refman/5.0/en/triggers.html However, the setup of a MySQL trigger requires the MySQL SUPERUSER privileges. On the servers offered by SiteGround, such privileges can be granted only on dedicated servers or VPS hosting . Granting SUPERUSER privileges to a user hosted on a shared server is a security issue and this is why we don’t do it. The alternative solution is to manipulate the data inserted using the above MySQL statements through a suitable php code in your scripts. Here is an example of a MySQL trigger: First we will create the table for which the trigger will be set: mysql> CREATE TABLE people (age INT, name varchar(150)); Next we will define the trigger. It will be executed before every INSERT statement for the people table: mysql> delimiter // mysql> CREATE TRIGGER agecheck BEFORE INSERT ON people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;// Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; We will insert two records to check the trigger functionality. mysql> INSERT INTO people VALUES (-20, ‘Sid’), (30, ‘Josh’); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 At the end we will check the result. mysql> SELECT * FROM people; +——-+——-+ | age | name | +——-+——-+ | 0 | Sid | | 30 | Josh | +——-+——-+ 2 rows in set (0.00…

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 the user to the database. Then you should open cPanel > PhpMyAdmin, navigate to your database, open the SQL tag and paste the stored procedure code. Comprehensive details on MySQL stored procedures and their syntax can be found in the MySQL5 official documentation: http://dev.mysql.com/doc/refman/5.1/en/stored-routines.html …

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 personal computer. Upload the files into a suitable folder under your account. For example, you may upload the application files to: public_html/phpmyadmin/ This way the application will be accessible via: http://yourdomain.com/phpmyadmin You do not need to make any further set up for the application. You can log in to phpMyAdmin with your cPanel login details or with the login details for any of your Mysql Users added in cPanel. Depending on the users, you will be able to manage different databases according to the privileges which the users have. For additional security we would also recommend you to Password Protect the directory in which the tool is installed.…

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 the return of the MySQL NOW() function. Let's take a look at the most standard query for selecting the current date/time: SELECT NOW(); If you want to add 2 hours to the result, you can use the following syntax: SELECT DATE_ADD(NOW(), INTERVAL 2 HOUR); In a similar way, if you want to subtract 2 hours from the server timezone, you can use this query: SELECT DATE_SUB(NOW(), INTERVAL 2 HOUR); Using DATE_SUB and DATE_ADD , you can modify the query result to fit your specific needs.…

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 -p PASSWORD DBNAME < dump.sql ");  ?> Where USER , PASSWORD and DBNAME are your details for the database in which you want to import the dumped file, and dump.sql is the file you want to import. Make sure you give the correct path to the dump.sql file. When you are ready, open your browser and type in the URL to the update.php file, e.g. http://yourdomain.com/update.php . To test if the import was successful, log in to your phpMyAdmin tool from your cPanel and review the database.…

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: [crayon-58426aaf39cea057457546/] MySQL will check all records and will return only the one that has its number value set to 4. But if you have several thousand entries for example, this will be a slow query. In this case we have a unique field - " number ". Therefore, we can create an index for it. Indexing will create an internal register that is saved in by the MySQL service. It can be done with the following query: [crayon-58426aaf39cfd691832247/] Once this index is set, next time you want to get the information for employee number 4, the service will go directly to it using the index and will return the information much faster. This is just a very basic example. For bigger databases, the difference in the loading time can be significant. Indexing your database can drastically decrease the loading time of your web applications. There is another query that you can use to increase the loading speed of your database: [crayon-58426aaf39d06796644477/] You can use the following command to optimize/repair a database through SSH : mysqlcheck -orp -u user_name database_name where: -o - stands for optimize -r - stands for repair -p - when this option is used with the command you will be prompted to enter the password of the database username user_name -u - the username assigned to the database; in this case you can use your cPanel username with its corresponding password Alternatively, you can use the detailed instructions in this article: How to Optimize a MySQL Database with phpMyAdmin There are other methods which can be used for MySQL optimization such as using of persistent connections, query caching, etc. However, they may require some fine tuning from the Apache and MySQL configuration files. After the optimization, the MySQL service will take much less time to search in your database which will result in better performance of your…

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 database and then exporting them, you should export the large database by selecting only several tables at a time. This way, if you wish to import the database, you will have to import smaller SQL dumps and thus you will avoid a phpMyAdmin timeout during the import process.…

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 in this case is to repair and optimize your database. If a particular table crashes often and repairing/optimizing it does not help,  this most probably means that the table's structure is not working properly with our MySQL server setup. In such cases the easiest way to fix a crashed MyISAM table for good is to change its database engine from MyISAM to InnoDB. More information on how to do this can be found in this article .…

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; The easiest way to make this change is through phpMyAdmin: Open your phpMyAdmin tool and select the database which contains my_table . Then click the SQL tab, paste the above query there and click the Go button. If the query is executed properly, the database engine of the table will be changed to 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 your database supports repair (for example, MyISAM supports it), you will see a list of the repaired tables within the database. If your database engine does not support repair (e.g. InnoDB database engine), you will get the following error message: note : The storage engine for the table doesn't support repair Through phpMyAdmin: Open your phpMyAdmin tool and select the database whose tables you wish to repair. A list with all the database's tables will appear. Tick the tables you wish to repair, or simply click [ Check All ] to select all tables. Click the box [ With selected: ] and choose Repair table . This will execute the REPAIR TABLE SQL query on the selected tables and they will be…

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'); $result=mysql_query('show tables'); while($tables = mysql_fetch_array($result)) { foreach ($tables as $key => $value) { mysql_query("ALTER TABLE $value COLLATE utf8_general_ci "); }} echo "The collation of your database has been successfully changed!"; ?> Make sure to substitute in the above script: - myuser_mydbname with your database name; - myuser_mydbuser with your mysql username; - mypassword with your password for the mysql user; - utf8-general_ci with your new collation if different;…

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 phpMyAdmin tool in your cPanel. Once you have loaded it, you should select your database and click on the Operations tab on the top of the screen. Then select utf8_general_ci from the " Collation " drop-down menu and click on the "Go" button. Next, you can proceed with installing your application, using the newly created database. You can repeat the same steps for modifying the database of already installed applications. This, however, will change the collation only for the newly added data and may not be enough for your application to work properly. Therefore, you can create a PHP file (you can call it whatever you wish, e.g. collation.php) in your public_html folder with the following content: <?php $db = mysql_connect('localhost',' USERNAME ',' PASSWORD '); if(!$db) echo "cannot connect to the database"; mysql_select_db(' DATABASE '); $result=mysql_query('show tables'); while($tables = mysql_fetch_array($result)) { foreach ($tables as $key => $value) { mysql_query("ALTER TABLE $value COLLATE utf8_general_ci"); } } ?> You should make sure you add the correct data into the USERNAME, PASSWORD and DATABASE variables. Next, just call the newly created file, and it will change the collation of all the existing fields to utf8_general_ci . For most web applications this should be enough for them to start working correctly with the new encoding. However, some scripts may still malfunction. Unfortunately, in such cases you have to create a new database, change its collation and re-install your application with it.…

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. Tick the tables you wish to optimize, or simply click [ Check All ] to select all tables. From the [ With selected: ] drop-down menu choose Optimize table . This will execute the OPTIMIZE TABLE SQL query on the selected tables and they will be updated and optimized.…

‘1064 – You have an error in your SQL syntax’ error in phpMyAdmin

Sometimes, when you try to modify a database in phpMyAdmin, you can get this error message when you try to execute the SQL query: MySQL said:  #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use... This is a known issue in cPanel which occurs when you access your cPanel via the Access cPanel Normally button in your Customer Area. In other words, this issue appears when you access your cPanel using the cPanel proxy, i.e: http://cpanel.sitegroundXXX.com In order to avoid it, please access your cPanel via: http://yourdomain.com/cpanel or using the Access cPanel Securely button in your Customer Area. If the above doesn't help, you should check the syntax of the query you are trying to execute and make sure it is correct.…