Databases

Overview

N0C allows you to perform many functions related to databases which allow you to manage a website. Firstly, the SQL Databases section allows you to manage and create databases, add users, modify their privileges, etc. 

Secondly, the phpMyAdmin section redirects you to this software allowing you to manage, import, export and modify your databases. phpMyAdmin is an open source software designed for managing the administration of MySQL on the Web. It supports a wide range of operations in which most — database management, tables, columns, relations, indexes, users, permissions, etc. — can be done through the interface, while you can directly execute any SQL command. 

This article is for beginners.

Prerequisite

Access to https://mg.n0c.com/en/.

SQL Databases Management

  1. In N0C, click on Databases.
  2. Click on SQL Databases:

Create an SQL database

  1. Click on the Create button:
  1. Enter the name of your database in the NEW DATABASE field.
  1. Check CREATE A USER FOR THE DATABASE (recommended) and provide its NEW USERNAME and PASSWORD.
  2. Click on Create.

While creating the password, the following icons can be used:

Manage users

  1. In the EXISTING USERS window, click on the Create button:
  1. Enter a NEW USERNAME and PASSWORD:
  1. Click on Create.

Manage users’ passwords

  1. Click on the Change password button:
  1. Enter a new password in the PASSWORD field and click on the Save button.

Rename users

  1. Click on the Rename user icon:
  1. Enter a new username in this field.
  2. Click on the Save icon.

Add users with privileges

Adding users to databases is mandatory to allow their connection.

  1. Click on Add User for the applicable database:
  1. Choose the user in the ADD USER drop-down list:
  1. Click on the Add button.
  2. Click Back to table or Add another user if needed:

Manage user privileges  

  1. Click on the user whose privileges you wish to change:
  1. Check the appropriate boxes (all are checked by default, meaning the user has all privileges) and click on Save

Privileges definitions

PrivilegeDefinition
ALTERModify properties (ALTER AGGREGATE, ALTER CONVERSION, etc.).
ALTER ROUTINEModify the definition of a routine, that can be an aggregate function, a normal function or a procedure. Routines are SQL objects that allow defining a logic to apply to data.
CREATEDefine a table (CREATE TABLE), a new database (CREATE DATABASE), etc.
CREATE ROUTINEGroup stocked functions and procedures. 
CREATE TEMPORARY TABLESCreate a temporary table. A temporary table exists only in the session that created it. 
CREATE VIEWDefine a view. A view is a virtual table; in other words, it is a table whose data are not stored in the database, and containing informations from many tables.
DELETEDelete lines in a table. By using commands associated to WHERE, it is possible to select the lines that will be deleted.
DROPPermanently delete a table from a database (DROP TABLE), a role (DROP ROLE), etc.
EVENTAllow to obtain data to monitor or solve problems in SQL.
EXECUTEExecute a prepared instruction.
INDEXPerform commands associated to indexes such as CREATE INDEX. With an index placed on one or many columns, a database system can first search data on the index and, if what is searched is found, it knows more rapidly where are the records.
INSERTInsert data in a table.
LOCK TABLESLock a table.
REFERENCESThe reference permission on a table allows to create a foreign key that refers to this table; this way, administrators can perform required modifications without having to take into account the needs of the persons to whom the table has been referenced.
SELECTReturn the records in a result table. This command can select one or many columns in a table.
SHOW VIEWDisplay the value of a executions parameter.
TRIGGERExecute a set of SQL instructions right after an event.
UPDATEPerform modifications on existing lines. Often, this command is used with WHERE to specify on which lines the modification is applicable.

Remove users 

  1. In EXISTING USERS, identify the user you wish to remove:
  1. Click on the Remove icon (trash can).

Remove the access to databases for certain users

  1. In EXISTING DATABASES, identify the user in which you wish to revoke access:
  1. Click on the Remove icon (trash can).

Delete an SQL database

  1. In EXISTING DATABASES, identify the database you wish to delete:
  1. Click on the Delete icon (trash can).

PHPMyAdmin

As soon as the database in SQL has been created as explained previously, you can design it in PHPMyAdmin. You can also import it if it already exists on your machine.

Note

More experienced users can refer to the PHPMyAdmin documentation here.

Access through N0C

  1. In N0C, click on Databases.
  2. Click on PhpMyAdmin.
  1. This opens the phpMyAdmin software:
  1. The Navigation Panel (left side) is primarily used to choose a database or a table, or to come back to the phpMyAdmin Homepage.
  2. The purpose of the Main Panel, also called Central Page (right side), is to interact with the system.

General description of the Administration Panel icons

General description of main panel tabs

How to create a table

  1. Click on the name of the database:
  1. Enter the Name and the Number of columns of the table.
  2. Click on the Go button.
  3. Enter following information for each field of the table :
    • a. Name: defines the name of the table;
    • b. Type: the type of data that will be recorded in the field. Most usual are INT (whole number), VARCHAR (short text), TEXT (long text) and DATE (date, month, year);
    • c. Length/Values: maximum size of the field, useful for VARCHAR in particular, to limit the number of authorized characters;
    • d. Index: activates the indexation of the field. This word means that the field will be adapted to queries. The PRIMARY index is used on fields of id type;
    • e. A_I (AUTO_INCREMENT): allows the field to increment alone at each entry. It is often used for id type fields.   
  1. Click on Save, so the table becomes visible in the left Navigation panel.
  1. Continue to create as many tables as necessary by repeating the previous steps.

How to modify the fields in a table

Once created, a table is not fixed: you have the option to rename, delete and add fields,  etc.

  1. After having chosen the table to modify in the left Navigation panel, click on the Structure tab:
  1. Click on relevant action, Change changes the field of table, while Drop removes a field.
  2. More gives access to icons allowing additional actions:

How to insert values in a table

  1. Select a table in the left Navigation panel then click on the Insert tab:
  1. Fill in the Value fields.

How to write a query to be executed in a table

  1. Select a table in the left Navigation panel then click on the SQL tab:
  1. Write the query in the designated field.
  2. Then click on the Go button; the result of the query is then displayed.

How to browse the records in a table

  1. Select a table in the left Navigation panel then click on the Browse tab:
  1. Entries are displayed.

How to search records in a table

Before beginning, it is important to note that the operator LIKE means « exactly the same», while LIKE %…% means « includes ».

To search a value chosen in a table selected in the left Navigation panel:

  1. Click on the Search tab.
  2. Click on the Table Search tab:
  1. Using the Operator and Value fields, define search criteria.
  2. Click on the Go button.
  3. You can edit, copy, delete or export the entries :
  1. Other actions on the results of the query are possible:

To make a search by value on two different columns:

  1. Click on the Zoom search tab:
  1. Enter the requested information.
  2. Click on the Go button.

To search and replace a record:

  1. Click on the Find and replace tab:
  1. Enter the information in the Find field and the new data in Replace with field; then,  specify the Column.
  2. Click on the Go button.
  3. Click on Replace to save the modified string.

Note

The % character (joker) replaces a string (it can be null).

How to export a table

  1. Select the desired table.
  1. Click on the Export tab.
  2. Define the format of the data you wish to export (SQL, CSV, PDF or other format).
  3. Click on the Go button.

How to import data from a similar table

Sometimes, it is more practical to use a file containing SQL queries in order to import many queries instead of writing each query individually.

  1. Select the desired table.
  2. Click on the Import tab:
  1. Define the import settings.
  2. Click on the Go button.

How to perform operations on a table

More advanced users can modify tables with the Alter table functions. 

  1. Select the desired table.
  2. Click on the Operations tab:

You may need to:

  • Rename table to: change the table name.
  • Move table to (database.table):  Move the table to another database.
  • Copy table to (database.table): copy the table in the same database or another one (beware: in this case, it must have another name).
  • Optimize table: speed up queries.
  • Empty the table (TRUNCATE): empty all the data in the table. All records disappear and only the structure of the table stays (that is, the fields). Beware: this operation cannot be undone.
  • Delete the table (DROP):  removes one or more tables (structure and data). You must have the DROP privilege for each table. Once again, be careful before deleting a table, as nothing can be recovered afterwards, unless a save has been done previously with the export tool.

How to manage the triggers on a table

  1. Select the desired table.
  2. Click on the Triggers tab:
  1. Click on Add trigger.
  2. Define the procedure for each trigger and click on the Go button:

How to empty a saturated table

It often happens that a user saturates a table while making a blog. It is then mandatory to empty the table.

Note

Before doing so, check the content of the table to avoid losing important elements of your website, such as articles.

  1. Select the table causing problems:
  1. Check Check all.
  2. Click on Delete to empty the table.

How to export a database

The PHPMyAdmin function has two purposes:

  • Exporting databases on the Internet.
  • Make backup copies of databases in several export file formats (dump).

If the SQL format is chosen, the file that you will obtain upon exporting with phpMyAdmin specifies to MySQL how to recreate your databases with SQL queries.

Note

It is strongly recommended to make a backup of your database before exporting anything in PHPmyAdmin.

How to export all your databases

  1. Go to the Homepage.
  2. Click on the Export tab:
  1. Define the Export method by clicking on the Quick – display only the minimal options radio button.
  2. Define the export file Format (SQL, CSV, PDF or any other format).
  3. Click on Go button.

How to export selected databases

  1. Go to the Homepage.
  2. Click on the Export tab:
  1. Define the Export method by clicking on the Custom – display all possible options radio button.
  2. Define the export file Format (SQL, CSV, PDF or any other format). 
  3. Select your Databases to export.

The options depend on the chosen format (SQL only is used for our explanations in this article).

  1. In Output, choose Rename exported databases/tables/columns if needed.
  1. Enter relevant information.
  2. Click the Save & close button.
  3. Continue to fill in the Output options (you can choose either Save output to a file or View output as text):
  1. Define the Format-specific options:
  1. If you choose to export the structure only, click on Structure.
  1. Define the Object creation options.
  2. Click on the Go button.
  3. If you choose to export the data only, click Data:
  1. Define Data creation options and click on the Go button.
  2. If you choose to export structure and data, click Structure and data:
  1. Define Object creation options and Data creation options, and click Go.

How to import a database with phpMyAdmin

It is important to know good practices for importing databases. You must have an empty database in your N0C account in order to import one. In addition, you must ensure that if your file is compressed, its name ends with .[format].[compression]. Example:. sql.zip. If the .sql you attempt to import is too large for phpMyAdmin, you have to import it with SSH (in this case, please see section How to import a database in command line).

  1. Refer to the section Create an SQL database and follow the creation steps. 
  2. Click on the name of the database that will receive import information. The page will refresh to display the related information:
  1. Click on the Import tab.
  2. Click Choose file. Your browser will then allow you to select the database file on your computer.
  3. Define the Character set of the file.
  4. Check Partial import options:
    • a. Allowing the interruption of an import in case the script detects it is close to the PHP timeout limit might be a good way to import large files; however it can break transactions.
    • b. The field Skip this number of queries (for SQL) starting from the first one allows you to ignore some queries at the beginning.
  5. Check Enable foreign key checks under Other options in order to insert tables that have foreign keys.
  6. Choose the Format (SQL by default).
  7. Choose Format specific options. They allow you to choose a compatibility mode. This is very useful in particular to import various types of SQL files from many databases (ANSI, ORACLE, etc.).
  8. Click on the Go button.

As soon as the file import is finished, the data and the structure it contains will be available for use in the database.

How to import a database in command line

Importing a database via SSH can be done in Windows with puTTY software. It can also be done directly with the terminal in some environments. We will cover the use of Windows in this article.

For our example, let’s suppose the user having the cPanel username « fbkfdayw » desires to import a database called « atomes.sql » in an empty database called « BD_Atomes ».

Note

This procedure is for advanced users. Always save your work beforehand.

Recover your password

The first step is to recover you cPanel username and your password. 

  1. Connect to The World and click on My Portfolio:
  1. Click on your domain (host):
  1. Click on the blurred zone to display your password. Memorize it or copy it by highlighting it and pressing CTRL + C:

Prepare hosting

  1. Refer to the section Create an SQL database and follow the steps to create your empty « BD_Atomes » database where the import will be done:
  1. Create the exportat file (dump) on your machine (« atomes.sql » in our example). Refer to the French article https://kb.n0c.com/knowledge-base/les-importations-exportations-des-bases-de-donnees-en-ligne-de-commande-mode-user/:
  1. Send the export file  to the N0C server. There are 2 possibilities, the first one is recommended:

Connection in SSH

You are now ready to remotely connect from your machine.

  1. Load PuTTY from its official website. Click on the installation program (here, putty-64bit-0.74-installer.msi):
  1. Then, run PuTTY. Make sure that the Connection Type is set to SSH in the PuTTY Configuration window:
  1. Enter your server IP address or Host Name (for example, node2-ca.n0c.com). 
  2. Specify the port. This varies depending on the hosting type you have: 5022 for World plans and 2908 for HybridCloud servers.
  3. To save the information kept in memory – so it is not necessary to redo this at each connection – give a name to the session in the field named Saved Sessions and click on the Save button.
  4. Launch the SSH session by clicking on the Open button. On the first time, PuTTY should ask for a confirmation.
  5. As the server displays login as:, identify yourself by entering  your cPanel username and press the Enter key on your keyboard :
  1. Enter your cPanel password (since you have copied it at step 3 when you recovered your password, you can right click your mouse):

Import in command line

You are now ready to import your dump file in the empty database.

  1. Launch the import command:
mysql -u user -ppass database < file_dump

Where :

  • user: the user you wish to connect with.
  • pass: the password of this user.
  • database: the name of the database where data will be imported.
  • file_dump: name of the dump file containing SQL instructions to import.

In our example, we will write :

mysql -u fbkfdayw -ppass fbkfdayw_BD_Atomes < atomes.sql
  1. You can then access phpMyAdmin and confirm that your database has been imported:

Some useful Putty commands

CommandDefinition
lsUsed to list the content of the folder you are in.
cdUsed to browse the folders of your machine. For exemple, if your are at the root of the machine and you desire to go to public_html/xyz.ca, you have to type cd public_html/xyz.ca. If you just type cd, this brings you to your user or root depending on what your are connected with.
cd ..Used to go to one folder higher. If, for example, you are in / public_html/xyz.ca and you want to go in /public_html, type « cd.. ».
exitUsed to disconnect.

Settings

  1. The Settings tab allows you to configure PhpMyAdmin as you desire. Preferences are saved in current sessions only.

A permanent storage requires the installation of phpMyAdmin configuration storage.

You can customize the information displayed on various pages:

TabDescription
Manage your settingsAllows you to change the default selections of Import and Export tabs.
Two-factor authentificationAdds a second element to authentification besides a password. By default, authentification using only a password is selected.
FeaturesAllows you to personalize some functions: text fields, browser’s title bar text, disable some of the warnings shown by phpMyAdmin and console for SQL queries.
SQL queriesAllows you to configure SQL query parameters and to customize links shown in SQL Query boxes.
Navigation panelCustomize the appearance of the navigation panel (left panel).
Main panelCustomize the startup page (right panel).
ExportCustomize the default export options associated to various formats (SQL, CodeGen, CSV, LaTex, Microsoft Office, OpenDocument and Texte Texy.
ImportUsed to customize the values ​​usually used for import and the default options associated with various formats (SQL, CSV and OpenDocument).

Server variables and parameters

  1. The Variables tab contains all MySQL variables and their values.
  1. The Edit action allows you to change the value of any variable. Take note that you cannot necessarily change the values of all variables, this being permitted only for the root user on the server.

Character sets and collations

  1. The Charsets tab gives access to the character sets and list of each collation supported by the MySQL server.

Storage engines

  1. The Engines tab opens a page wth all storage engines that are supported by the MySQL server.

InnoDB is selected by default. Users can refer to the documentation by clicking here to get more information about storage engines.

Plugins

  1. The Plugins tab opens a page showing all installed MySQL plugins, with relevant information such as description and version.

References

https://docs.planethoster.com/guide/astuces-techniques/importer-une-base-de-donnees-de-plus-de-50-mo-sur-un-compte-world-en-ssh (in French).

https://kb.n0c.com/knowledge-base/les-importations-exportations-des-bases-de-donnees-en-ligne-de-commande-mode-user/ (in French).

https ://docs.planethoster.com/guide/astuces-techniques/se-connecter-en-ftp-avec-le-logiciel-filezilla (in French).

https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15 (in English).

Updated on January 19, 2021

Was this article helpful?

Related Articles