Knowledgebase : MySQL Databases

Yes, RFE Hosting's mysql databases are unicode compatible.

Creating a database in cPanel using the MySQL Database Wizard:

1.       Log into your cPanel

2.      Click the MySQL Database Wizard under the Databases heading

3.       Next to New Database enter a name for your database and click Next Step

4.       Next to Username enter a username.

5.       Enter a password next to Password, enter it again for Password (Again) and then click Create User

6.       On the next page, you'll assign privileges for the user to the database. Check the box next to All Privilegesand then click Next Step.

 Congratulations, you have just successfully created a database!

This will show you how to whitelist your IP to allow remote MySQL connections for Shared, Reseller, VPS/cPanel and Linux Dedicated Servers.

Allowing remote MySQL connections is often done to enable a program on your personal computer (such as Adobe Dreamweaver) to access the database on the server.

Allowing remote MySQL connections is often done to enable a program on your personal computer (such as Adobe Dreamweaver) to access the database on the server.

By default, all IPs are blocked and must be added to an access list.  To find our what your own IP address is, go to hostgator.com/ip.shtml (your IP address will be listed in red).

  1. Login to your cPanel (if not already logged in).
  2. Scroll down to the Databases section.
  3. Click on the Remote MySQL icon.
  4. Enter the IP address of the computer that will be making the remote connection.
  5. Click the Add Host button.

How can I find out my computers IP address?

Click this link to view your computer IP Address

What if I got a Dynamic IP address?

If you got a dynamic IP address. Therefore, every time you go online, or every once in awhile, your IP changes. You have a few options here. The most secure, would be to update your IP in Remote MySQL every time your IP changes. However, you do have other options available. You could simply enter % (a percentage sign) in the Add Access Hosts text field. This will allow any IP to remotely connect to your databases. Another option: Lets say the first two parts of your IP never change. Then, you could enter 93.138.%.% , where the first two parts are your actual IP address numbers.

Note: If you're connecting from a dynamic IP address, you can use the "%" wildcard value instead of an IP address.  However, since this will grant access from anywhere, it is generally considered a security risk and therefore not recommended.

Once the enabled remote access is done, You will need a third party application to access MySQL remotely. Some are free and some cost money.

 

 

You can view whether your account is using localhost or remotemysql host in your cPanel account. Click on 'phpMyAdmin', in the 'Datebases' menu. Your database host will be displayed on the next screen after Server. It will be listed as localhost or remotemysql host.

There are two ways to back up your MySQL databases. You can use the backup utility in your cPanel account or you can manually backup using phpMyAdmin. 

To manually backup using phpMyAdmin follow these steps: 
- Login to your control panel at www.yourdomain.com/cpanel 
- Click on 'phpMyAdmin' under the 'Databases' menu
- From the dropdown list to the left, choose the database you wish to export the backup from. 
- Click on the 'Export' Tab. 
- Click the 'Select All' link shown in blue, then tick the 'Save as file' box and 'Go'. 
-  You will be asked to save the .sql file to the hard drive, click 'Save'. 
- Choose the location to save to and click 'Save'.

Your database has now been exported.

To create a MySQL database please follow the steps below:

- Login to your control panel at www.yourdomain.com/cpanel 
- Click on 'MySQL Databases' in the 'Databases' menu
- Enter the name of your new database and then click on 'Create Database' 
- Add database users by assigning them a username and password then clicking on 'Create User'

To import a database follow the below steps:

I have a backup of my database that I need to import/transfer to my new account.

- Once you have your .sql file you will need to log into your cPanel at www.yourdomain.com/cpanel 
- Click on 'phpMyAdmin' under the 'Databases' menu
- A drop down of your databases is displayed on the left side of the page, select the database you would like to import to. 
- This will create some tabs at the top of the screen, click on 'SQL'.
- This opens a text box and a file upload option. 
- Click browse on the file upload option and find your .sql file 
- After you have selected the file click GO and it will import your database.

To restore a MySQL database please follow the below steps:

- Login to your control panel www.yourdomain.com/cpanel
- Click on 'Backups' in 'Site Management'
- Click on 'Browse' to locate the Database you want to restore then click 'Upload'

It may take a while to upload your .sql and populate the database so a broadband connection would help. This will overwrite the database that you currently have up.

To add yourself to the whitelist so that you can connect to mysql remotely using admin tools such as phpMyAdmin, Navicat, MySQLFront or MySQL-Admin follow the below steps:

- Log into your control panel at www.yourdomain.com/cpanel 
- Click on the 'Remote MySQL' under the 'Databases' menu 
- Type % in the Host field  
- Click on 'Add Host'

You should now be able to connect successfully.

The command line option from SSH is: mysql -d -p database_name > file.sql. Where 'database_name' is the name of your database, and 'file.sql' is the name of the file you would like to write the table structure to. You will be prompted you for a password (-p). Normally mysqldump gives you the entire database, with the '-d' option, you need to tell it to not include the data, just the table structure.

Since we run a shared hosting environment, we have to uniquely identify your usernames to your account only. We do this by attaching your system username and an underscore '_' before all the databases and users that you create. This only applies to your database name and database username, you password stays normal.

Please remember:
- Your username can only be up to 7 characters long, every character after that will be cut off.  
- You need to add your user to your database after the two are created.
- Database Server/Host Address- If your script or program is going to be accessing our database locally, meaning it is stored on your account, the server/host address will always be: localhost.

There are two different ways to connect to a MySQL database:

Locally - from a script on the server, to the server for which the settings are:
Hostname: localhost
Username: username_dbuser
Password: dbpassword db_name: username_dbname db_table: dbname_table 

Remotely - from a remote computer, using Shell, ODBC, or Navicat.
The settings are the same as above, but you must use yourdomain.com in place of 'localhost'.

A database is a structured, organized set of data. In computing terminology a database refers to a software used to store and organize data. Think of it as a file cabinet where you store data in different sections called tables. When you need a particular file you look into that particular section (table) and get the file (data) you need.

WordPress uses MySQL as its database management system. MySQL is a software used to create databases, store and get data when requested. MySQL is also an open source software, just like WordPress and works best with other popular open source software, such as Apache web server, PHP, and Linux operating system.

To install WordPress you need a MySQL database. All WordPress hosting providers offer MySQL included in their hosting packages. During the installation (See: Complete WordPress Installation Tutorial), you provide WordPress your database information, and then WordPress takes care of rest of the things.

What is Database Host

Database host is the computer hosting your database on a MySQL server. Most of the time it is localhost and entering localhost in the host field would connect WordPress to your database. However, some web hosting providers may use different host names to manage MySQL servers. You will find your host name in the MySQL or Database sections of your hosting control panel. Ask your hosting provider if you cannot find the host name.

Example: an office database may have a table called employee_records. This table may have following columns:

  • employee_id
  • employee_name
  • employee_joining_date
  • employee_phone_no

WordPress will automatically create tables inside your database. At the time of writing this, a default installation of WordPress would create the following tables:

  • wp_commentmeta
  • wp_comments
  • wp_links
  • wp_options
  • wp_postmeta
  • wp_posts
  • wp_terms
  • wp_term_relationships
  • wp_term_taxonomy
  • wp_usermeta
  • wp_users

Each of these tables would have different columns where data is stored. For example, wp_users table in WordPress has these columns:

  • ID
  • user_login
  • user_pass
  • user_nicename
  • user_email
  • user_url
  • user_registered
  • user_activation_key
  • user_status
  • display_name

What is SQL Query

SQL is abbreviation for Structured Query Language, it is a special programming language used to manage databases. An instruction issued by SQL to the database server to retrieve data is called a query. WordPress uses MySQL queries to get data and use it to generate web pages. A typical MySQL query looks like this:

SELECT * FROM wp_posts WHERE ID = 23;

SQL is not just limited to retrieve data from the tables. It can also update, insert and delete data from tables and even create new tables. This how WordPress stores and edits all your website data by storing and retrieving it from the database using SQL queries.

How to Manage WordPress Database

It is important to learn how you can do certain tasks on your WordPress database. This can help you troubleshoot some common WordPress issues, help you recover your website, and make your website more secure in general. For example, you would want to learn how to create regular backups.

WordPress databases can be managed using phpMyAdmin, which is an open source web application with a nice easy graphical user interface to manage MySQL databases. There are also many WordPress plugins available which can help you create WordPress database backups.

MySQL is a database management system that is used by WordPress to store and retrieve all your blog information. Think of it this way. If your database is a filing cabinet that WordPress uses to organize and store all the important data from your website (posts, pages, images, etc), then MySQL is the company that created this special type of filing cabinet.

MySQL is an open source relational database management system. It runs as a server and allows multiple users to manage and create numerous databases. It is a central component in the LAMP stack of open source web application software that is used to create websites. LAMP stands for Linux, Apache, MySQL, and PHP. Most WordPress installations use the LAMP stack because it is open source and works seamlessly with WordPress.

WordPress requires MySQL to store and retrieve all of its data including post content, user profiles, and custom post types. Most web hosting providers already have MySQL installed on their web servers as it is widely used in many open source web applications such as WordPress.

WordPress uses the PHP programming language to store and retrieve data from the MySQL database. To retrieve data from the database, WordPress runs SQL queries to dynamically generate content. SQL stands for Structured Query Language and is the programming language typically used to query databases.

For users that are not comfortable writing their own PHP and SQL scripts, most web hosting providers offer easy to use web applications to manage databases. One such web application is phpMyAdmin which allows users to manage their database using a web based graphical interface. You can manipulate your tables visually while phpMyAdmin runs the SQL queries for you.

Out of the box MySQL doesn’t come with any graphical interface and advanced level users may prefer to just manage their databases from the command line.