Everything you wanted to know about databases

In your eXtend Control Panel, you should be able to find the server’s IP address and port as well as your username and password in the MS-SQL Databases area.

To connect remotely to your database:

1. Open your Microsoft SQL Server Management Studio
2. In the ‘Connect to Server’ dialogue box:
   1. Set authentication to ‘SQL Server Authentication’
   2. Put the server’s IP address and port in server name as [IP],[Port]
   3. Put in your username and password
   4. You should now be able to connect to your database

1. Log into your eXtend Control Panel
2. Click ‘MS-SQL Web Data Administrator Tool’

To back up your MS-SQL database:

1. Select your MS-SQL database
2. Click ‘Backup’
3. Download the .sql file

To restore your MS-SQL database:

1. Click ‘Restore’
2. Select your .sql file from your computer
3. Click ‘Upload’

The MS-SQL Web Data Administrator Tool can only produce and restore .sql files.  Please raise a Support Ticket with the Customer Services team if you have a .bak file from another MS-SQL server you need to restore from.

MSAccess:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb;User Id=admin;Password=;

Driver={Microsoft Access Driver (*.mdb)};Dbq=db.mdb;Uid=Admin;Pwd=;

MS-SQL (.NET Framework for SQL Server):

To connect MS-SQL, you have a few options:

Connecting to an MS-SQL Server database with OLE DB:

Set objConn = Server.CreateObject(“ADODB.Connection”)
objConn.Open “Provider=SQLOLEDB; Data Source=YOUR_SERVER_NAME; Initial Catalog=your_database_name; User ID=your_username; Password=your_password” providerName=”System.Data.SqlClient”
objConn.Close
Set objConn = Nothing

As would be seen in web.config:

<connectionStrings>
<add name=”YourConnectStringName” connectionString=” Provider=SQLOLEDB; Data Source=YOUR_SERVER_NAME/IP-ADDRESS; Initial Catalog=your_database_name; User ID=your_database_username; Password=your_database_password” providerName=”System.Data.SqlClient” />
</connectionStrings>

Or connect to an MS-SQL Server database with SQL Driver:

Set objConn = Server.CreateObject(“ADODB.Connection”)
objConn.Open “Driver={SQL Server};” & _
“Server=YOUR_SERVER_NAME;” & _
“Database=your_database_name;” & _
“Uid=your_username;” & _
“Pwd=your_password;”
objConn.Close
Set objConn = Nothing

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;

The port 3306 is the default MySQL port, but you can use a different TCP port if you require it.  If the UNIX socket is used, this value will be ignored.

Server=myServerAddress;Port=1234;Database=myDatabase;Uid=myUsername;Pwd=myPassword;

Through this, you can connect to a server in a replicated server configuration without needing to worry about which server to use.

Server=serverAddress1, serverAddress2, serverAddress3;Database=myDatabase;Uid=myUsername;Pwd=myPassword;

If you have an SSL certificate installed onto your server, you can use this to activate SSL encryption for all data sent between the client and the server.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;Encrypt=true;

Preventing batch mode or batch files from running can protect your database from major changes.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;AllowBatch=False;

You can allow users to enter in their own data by allowing user variables.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;AllowUserVariables=True;

This will return a MySQL DateTime object for invalid values and a System.DataTime object for valid values.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;AllowZeroDate=True;

This returns System.DataTime.MinValue valued System.DataTime object for invalid values and a System.DataTime object for valid values.

Server=myServerAddress;Database=myDatabase;Uid=myUserName;Pwd=myPassowrd;ConvertZeroDateTime;True;

The use of auto-enlist transactions can cause trouble in Medium Trust environments.  This removes it as the default behaviour.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;AutoEnlist=False;

Parameters for stored routines and procedures are checked against the server and can slow down the system. This removes it as the default behaviour.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;CheckParameters=False;

If you have errors related to permissions and value casting, these errors can be fixed by this string.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;UserProcedureBodies=False;

This uses the changed rows instead of found rows, providing different data as needed.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;UseAffectedRows=True;

This can cut down on the amount of time it takes to implement a script.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;UseCompression=True;

Log operations that are inefficient, for tighter development later on.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;UserUsageAdvisor=True;

Turn on the performance counters for your project, and review regularly for optimised programming.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;UserPerformanceMonitor=True;

1. Log into your eXtend Control Panel
2. Click ‘MySQL Databases’
3. Scroll down to the database you wish to manage
4. Click ‘Manage’
5. You will now be in phpMyAdmin for that database

1. Log into your eXtend Control Panel
2. Click on ‘MySQL Databases’
3. Click ‘Manage’ on one of your databases
4. You are now in phpMyAdmin

To use ‘Import’:

1. Click ‘Import’
2. Select the file you wish to upload
3. Select the options you need for this database
4. Click ‘Go’
5. The database will be imported

Please note: ‘Import’ can only be used on databases that are smaller than 16MB.

To paste your SQL script in:

1. Select the database you wish to import into
2. Click the SQL tab
3. Paste your SQL script into the text area
4. Click ‘Go’
5. Your database, through the SQL script, will be imported

If your SQL script contains a large amount of data, you may be able to paste it in sections to get all the content in.

You can restore existing backups through the eXtend Control Panel MySQL Database page.

To restore a backup:

1. Log into your eXtend Control Panel
2. Click on ‘MySQL Databases’
3. Click ‘Restore Backup’
4. Select the database you wish to restore from the drop-down
5. Select the SQL file you wish to upload
6. Click ‘Restore’
7. The database will be imported

MySQL offers the MySQL Workbench, a piece of free software that allows you to manage, backup, and restore MySQL databases. The software can restore large databases, and also comes packaged with the MySQL Query Browser.

To download and find out more about MySQL Workbench, please visit the MySQL site: http://dev.mysql.com/downloads/workbench/

If you have SSH access to your account, you can upload your MySQL backup to your home directory, and then execute the MySQL client to load a MySQL dump file.

To get the MySQL dump file:

1. Log into your account using SSH
2. Enter in this line:

Mysqldump –opt -Q dbname

Where the dbname is your database’s name

3. Upload the SQL dump file into your home directory

To load your MySQL dump using MySQL:

1. Enter this line:

mysql -h mysqlhost -u mysqlusername -p databasename < dumpfile.sql

or

cat dumpfile | mysql -h mysqlhost -u mysqlusername -p databasename

Where mysqlhost is the name of the server, mysqlusername is your MySQL user name, datbasename is your database’s name, and dumpfile.sql is the file created by mysqldump.

1. Log into your eXtend Control Panel
2. Click on ‘MySQL Databases’
3. Scroll down to the database you wish to backup
4. Click ‘Backup’
5. The backup will download onto your computer

You can use phpMyAdmin to download a backup of your database by using ‘Export’.

1. Log into your eXtend Control Panel
2. Click on ‘MySQL Databases’
3. Click ‘Manage’ on one of your databases
4. You are now in phpMyAdmin
5. Select the database you wish to back up
6. Click the ‘Export’ tab
7. Decide if you want a ‘Quick’ or ‘Custom’ download
8. Make the changes you need
9. Click ‘Go’
10. Your backup will download onto your computer

If you have SSH access to your account, you can download a backup of your MySQL database to the current directory you are in.

To create the backup:

1. Log into your account using SSH
2. Enter in this line:

mysqldump -h servername -u username -p –add-drop-table –create-options –skip-add-locks -e –set-charset –disable-keys -Q databasename > databasedump.sql

Where servername is your server’s name, username is your user name, and databasename is the database’s name. You can name the backup anything as long as .sql is at the end.

To backup and compress the dump file:

1. Enter in this line:

mysqldump -h servername -u username -p –add-drop-table –create-options -e –set-charset –skip-add-locks –disable-keys -Q databasename | gzip > databasedump.sql.gz

2. Download the .gz file using FTP or if you move the file into your public_html directory, you can download it directly from your website.

MySQL offers the MySQL Workbench, a piece of free software that allows you to manage, backup, and restore MySQL databases.  The software also comes packaged with the MySQL Query Browser.

To download and find out more about MySQL Workbench, please visit the MySQL site: http://dev.mysql.com/downloads/workbench/

use DBI;

# Connect To Database
# * The DBI interface to MySQL uses the method “connect” to make a
# * connection to the database. It takes as it’s first argument
# * the string “DBI:mysql:database:hostname”, where database is equal
# * to the name of your database, and hostname to the server that
# * it’s located on. The second and third arguments, respectively,
# * should be your account username and password. The connection is
# * assigned to a variable that is used by most other methods in the
# * module.

$database = “your database name”;
$username = “your database username”;
$password = “your database password”;
$hostname = “your database hostname”;
$db = DBI->connect(“DBI:mysql:$database:$hostname”, $username, $password);

# Execute a Query
# * executing a query is done in two steps. First,
# * the query is setup using the “prepare” method.
# * this requires the use of the variable used to
# * initiate the connection. Second, the “execute”
# * method is called, as shown below.

$query = $db->prepare(“SELECT * FROM test”);
$query->execute;

# How many rows in result?
# * the “rows” method using the variable name the
# * query was executed under returns the number
# * of rows in the result.

$numrows = $query->rows;

# Display Results
# * the fetchrow_array method executed on the
# * query returns the first row as an array.
# * subsequent calls return the other rows in
# * sequence. It returns zero when all rows have
# * been retrieved.

while (@array = $query->fetchrow_array) { ($field1, $field2, $field3) = @array; print “field1 = $field1, field2 = $field2, field3 = $field3 \n”; }

# Cleaning Up
# * with the DBI module, it is a good idea to clean up by
# * explicitly ending all queries with the “finish” method,
# * and all connections with the “disconnect” method.

$query->finish;
$db->disconnect;

exit(0);

1. Open a new file in your text editor
2. Paste in the following code:

<?php
$dbhost = ‘localhost’;
$dbuser = ‘[DATABASE USERNAME]’;
$dbpass = ‘[DATABASE PASSWORD]’;

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (‘Error connecting to mysql’);

$dbname = ‘[DATABASE NAME]’;
mysql_select_db($dbname);
?>

Replace [DATABASE USERNAME] with the name of your database user.  Replace [DATABASE PASSWORD] with the password for your database user.  Replace [DATABASE NAME] with the name of your database.

3. Save this file as [NAME].php and upload it to your account.

When you want to access the database in any PHP script:

1. Add the following line to your script:

include ‘[NAME].php’

2. The database can now be queried, using standard SQL commands, and the following line:

mysql_query(‘QUERY’)

[username] – Your username

[databasename] – The database’s name

You will then be prompted to enter in the database’s password. Once you have logged into the database, you will be at the MySQL prompt.

For more information on the commands you can use in the MySQL prompt, please refer to the MySQL Documentation: http://dev.mysql.com/doc/

rsEvents.ActiveConnection = “Driver={MySQL ODBC 5.1 Driver}; DATABASE=[DATABASE NAME];USER=[USER NAME]; PASSWORD=[PASSWORD]; Server=[SERVER IP ADDRESS]”

Or the following JScript:

var rsEvents = Server.CreateObject(“ADODB.Recordset”);

rsEvents.ActiveConnection = “Driver={MySQL ODBC 5.1 Driver}; DATABASE=[DATABASE NAME];USER=[USER NAME]; PASSWORD=[PASSWORD]; Server=[SERVER IP ADDRESS]”;

Replace [DATABASE NAME] with the name of your database.

Replace [USER NAME] with your user name.

Replace [PASSWORD] with your password.

Replace [SERVER IP ADDRESS] with your server’s IP address or name (which you can find in your eXtend control panel).

1. Log into your eXtend Control Panel
2. Scroll down to ‘Web Tools’
3. Click ‘MySQL Databases’
4. Enter in the username for the database
5. Click ‘Generate Password’ to create a password for that username
6. Click ‘Create’
7. The database will be created, with the same name as the username

In the MySQL Databases section, you can also manage existing databases, create new database users, or delete databases if you no longer need them.

1. Log into your eXtend Control Panel
2. Click ‘MySQL Databases’
3. Under ‘Create a Stand-alone MySQL User’, enter in the user name and generate a password
4. Click ‘Create’
5. The user is now created

By default, a standalone user has no permissions for the database. You must grant each permission specifically for the user.

If you have created the database and the standalone user, you can grant permissions for the standalone user by:

1. Log into the database as the original user
2. Run this SQL command:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES ON `webXX-dbname`.* TO ‘n-webXX-user’@’%’ WITH GRANT OPTION;
(Please note if your database has an underscore in it, such as webXX-db_name, you will need to add a back slash just before the underscore. For example: webXX-db\_name.)

3. Replace webXX-dbname with the name of the database, n-webXX-user with the standalone user’s name, and delete the permissions you do not want the standalone user to have
4. The standalone user will now have the permissions they need

You can also adjust the permissions of your standalone user by using PHPMyAdmin.

To access your standalone user:

1. Log into your eXtend Control Panel
2. Click ‘MySQL Databases’
3. Under ‘Manage MySQL Databases’, click ‘Manage’ on the row for the standalone user
4. Modify your user in PHPMyAdmin

If there are any other privileges you need to grant on a database, but you do not have access to those permissions, please raise a Support Ticket with our Customer Services team with the details.

And now that you know the password, you can log into MySQL with root administration privileges on any version of Plesk with this command line:

mysql -uadmin –p’cat /etc/psa/.psa.shadow’

This command gives you access to the MySQL database on the server.

1. Open Dreamweaver
2. Open a new file
3. Select ‘Dynamic Page’ as the type, and then ‘PHP’ or another language
4. Click ‘Create’
5. Your new dynamic page will appear as a blank page
6. In ‘Tools’, select ‘Application’, then ‘Database’
7. If you have already created a site, skip the first option
8. Since you have already created the document type, skip the second option
9. Click the ‘Testing Server’ link in the third option
10. Select the server model that matches up to the language you chose in Step 3 (for example, ‘PHP MySQL’)
11. Select ‘FTP’ for access, and enter in the FTP host for your domain name
12. Enter in the directory you would like to upload the files to under ‘Host Directory’
13. Enter in your FTP login
14. Enter in your FTP password
15. Enter in the full URL for your site under URL Prefix (for example, http://your-domain.com)
16. Click ‘OK’
17. In the ‘Databases’ tab, click the + icon at the top of the window and choose ‘MySQL Connection’
18. Your connection name can be anything, as this is only a descriptive name for you to recognise the connection
19. Enter in ‘localhost’ for the MySQL Server Name
20. Enter in the database’s user name
21. Enter in the database’s user name’s password
22. Under ‘Database’, enter in the database’s user name again
23. Click ‘Test’
24. If the test is successful, click ‘OK’
25. The connection is now available to create dynamic pages

If you need to connect to a database that is on another server, you would replace localhost with the IP address of the server.

You can find the IP address of the database’s server by checking your eXtend Control Panel.

1. Log into your eXtend Control Panel
2. Click on ‘MySQL Databases’
3. Scroll down to the existing databases
4. Copy the IP address next to the database you require
5. Paste that IP address into your mysql_connect line

When you have downloaded and installed MySQL Workbench onto your computer, you can connect it to your MySQL database and run queries, create or edit SQL statements, manage your databases, create, modify, or delete tables, and get help and information on objects, parameters, and functions within MySQL.

To connect MySQL Workbench to your database:

1. Under ‘Hostname’, enter in your domain name or the IP address of your web server
2. Under ‘Port’, enter 3306
3. Under ‘Username’, enter in the database name
4. Under ‘Password’, enter in the database password
5. You should now be able to connect to your database

UPDATE – Updates the data in tables or columns in the database

DELETE – Deletes rows from tables in the database

CREATE – Creates new databases, tables, or columns

DROP – Removes existing databases, tables, and views

INDEX – Creates or removes indices of existing tables

ALTER – Allows you to alter tables in the database

LOCK TABLES – Allows you to lock tables within the database

The following privileges can also be granted:

CREATE TEMPORARY TABLES – Creates temporary data tables in the database

CREATE VIEW – Creates new views of the data

CREATE ROUTINE – Creates stored routines in the database

Please raise a Support Ticket with our Customer Services team to acquire these additional privileges.

MSAccess:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb;User Id=admin;Password=;

Driver={Microsoft Access Driver (*.mdb)};Dbq=db.mdb;Uid=Admin;Pwd=;

MS-SQL (.NET Framework for SQL Server):

To connect MS-SQL, you have a few options:

Connecting to an MS-SQL Server database with OLE DB:

Set objConn = Server.CreateObject(“ADODB.Connection”)
objConn.Open “Provider=SQLOLEDB; Data Source=YOUR_SERVER_NAME; Initial Catalog=your_database_name; User ID=your_username; Password=your_password” providerName=”System.Data.SqlClient”
objConn.Close
Set objConn = Nothing

As would be seen in web.config:

<connectionStrings>
<add name=”YourConnectStringName” connectionString=” Provider=SQLOLEDB; Data Source=YOUR_SERVER_NAME/IP-ADDRESS; Initial Catalog=your_database_name; User ID=your_database_username; Password=your_database_password” providerName=”System.Data.SqlClient” />
</connectionStrings>

Or connect to an MS-SQL Server database with SQL Driver:

Set objConn = Server.CreateObject(“ADODB.Connection”)
objConn.Open “Driver={SQL Server};” & _
“Server=YOUR_SERVER_NAME;” & _
“Database=your_database_name;” & _
“Uid=your_username;” & _
“Pwd=your_password;”
objConn.Close
Set objConn = Nothing

1. Log into your server using SSH
2. Enter this command:

service mysqld restart

3. Your MySQL service should restart

However, this often does not work.  The MySQL service needs to create a file in order to run, but it cannot create the file because a duplicate file of the same name exists.

To fix this problem:

1. Log into your server using SSH
2. Enter this command to rename the duplicate file:

mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak

3. Then enter this command:

service mysqld restart

4. Your MySQL service will restart, and the Plesk error should be fixed

MySQL is an essential aspect of many content management systems and other web-based applications, such as WordPress, Joomla!, phpBB, and osCommerce.  MySQL is also an open source database solution, meaning that it is possible for anyone to use and modify the software as needed.

• Apache 2.4 with all standard modules installed, including mod_rewrite
• MySQL version 5.5
• PHP versions 5.6, 7.0, 7.1, and 7.2, with Zend Engine
• Perl version 5.10
• Python version 2.6
• Ruby version 1.8
• Image::Magick version 6.9
• CURL version 7.19

We also have a range of Perl, PHP, and Pear modules installed, which you can see in the eXtend Control Panel.

1. Log into the eXtend Control Panel
2. Click on ‘Perl & PHP Module List’ under Website Help & Diagnostics
3. Scroll through the lists of modules to find the ones you are looking for

Please note:  These are the current and correct versions of our software packages, as of October 2018.  We have a regular schedule of maintenance and upgrading for our servers to ensure the most up to date software and the best possible performance and features for our customers.

There are also Frequently Asked Questions at http://cpanel.net/help/ and a Support Forum at http://forums.cpanel.net/.

MailEnable, the email service for Windows servers, is included with all Windows-based Dedicated Servers.

Official documentation on MailEnable can be found at http://www.mailenable.com/references.asp.

The MailEnable Knowledge Base can be found at http://www.mailenable.com/KB/search.asp.

And there is also a Support Forum for MailEnable at http://forum.mailenable.com/.

You can find the official documentation for MySQL at http://dev.mysql.com/doc/.

There is also a MySQL developer’s area at http://dev.mysql.com/ and user forum at http://forums.mysql.com/.

You can find the official documentation for the Ubuntu operating system at https://help.ubuntu.com/.

There is also a community-maintained Wiki at https://help.ubuntu.com/community/CommunityHelpWiki.

You can find the official documentation for Windows Web Server 2008 at http://technet.microsoft.com/en-us/library/dd349801(v=ws.10).aspx.

You can also find the documentation for the latest versions of Windows Server at http://technet.microsoft.com/library/bb625087.aspx.

There is also information and advice on how to use IIS at http://www.iis.net/.

However, you may need to raise a Support Ticket with our Customer Services team in order for the flush-hosts command to be performed.

If you are producing SQL that looks like:

SELECT * FROM
a, b
LEFT JOIN c ON a.d = c.e;

That can produce the error.  It may look fine, and it did work in previous versions of MySQL, but the SQL standard means that MySQL 5 reads it as:

SELECT * FROM
a,
b LEFT JOIN c ON a.d = c.e;

Where the LEFT JOIN no longer makes any sense.

To fix this problem, either move the JOIN statement so that it is between the two tables it is joining:

SELECT * FROM
a LEFT JOIN c ON a.d = c.e,
b;

Or explicitly group like ((a,b),c) instead of (a,(b,c)):

SELECT * FROM
(a,
b) LEFT JOIN c ON a.d = c.e;

1. The database name
2. The content of your database
3. Any passwords associated with the database

Our backups are generally taken every day early in the morning, writing over the previous week’s backups.

This service is available only for our Shared Hosting customers and not for our Reseller or Server customers.  Please look at our Snapshot Backups to prevent this from happening with your Server or Reseller account.

MSAccess:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb;User Id=admin;Password=;

Driver={Microsoft Access Driver (*.mdb)};Dbq=db.mdb;Uid=Admin;Pwd=;

MS-SQL (.NET Framework for SQL Server):

To connect MS-SQL, you have a few options:

Connecting to an MS-SQL Server database with OLE DB:

Set objConn = Server.CreateObject(“ADODB.Connection”)
objConn.Open “Provider=SQLOLEDB; Data Source=YOUR_SERVER_NAME; Initial Catalog=your_database_name; User ID=your_username; Password=your_password” providerName=”System.Data.SqlClient”
objConn.Close
Set objConn = Nothing

As would be seen in web.config:

<connectionStrings>
<add name=”YourConnectStringName” connectionString=” Provider=SQLOLEDB; Data Source=YOUR_SERVER_NAME/IP-ADDRESS; Initial Catalog=your_database_name; User ID=your_database_username; Password=your_database_password” providerName=”System.Data.SqlClient” />
</connectionStrings>

Or connect to an MS-SQL Server database with SQL Driver:

Set objConn = Server.CreateObject(“ADODB.Connection”)
objConn.Open “Driver={SQL Server};” & _
“Server=YOUR_SERVER_NAME;” & _
“Database=your_database_name;” & _
“Uid=your_username;” & _
“Pwd=your_password;”
objConn.Close
Set objConn = Nothing