•   21/03/2005 10:50 AM
•  

---

 

Applicable Plans – All eApps general VPS Plans (Standard, Advanced, Premier, Dedicated)

User Guide – Using the MySQL database

Overview

MySQL is a relational database management system (RDBMS) that has more than 6 million installations. MySQL stands for "My Structured Query Language". The program runs as a server providing multi-user access to a number of databases...MySQL is often used in free software projects that require a full-featured database management system, such as WordPress, phpBB and other software built on the LAMP software stack. It is also used in very high-scale World Wide Web products including Wikipedia, Google and Facebook – from http://en.wikipedia.org/wiki/MySQL

The eApps Hosting service is designed to make it easy to use MySQL in your Virtual Private Server (VPS). This User Guide is not intended as a reference source for MySQL. See the Links to other information at the end of this document if you need detailed information about MySQL.

Installing MySQL 

Creating databases and database users 

Importing content to a MySQL database 

Stopping and starting MySQL 

MySQL Configuration 

MySQL remote access setup

JDBC connections 

Backing up your MySQL databases 

Upgrading MySQL
MySQL with SSL 
Links to other information

---

Installing MySQL

These instructions assume that MySQL is not currently installed on your VPS. If MySQL is already installed, and you want to upgrade it, please see the Upgrading MySQL section of this User Guide. MySQL can be installed when your order your VPS, or you can install it at any time.

To install MySQL, follow these steps:

Log in to your Control Panel, and click on the System Tab. If necessary, click the Select Another System (Subscription) link on the left and choose the correct Virtuozzo container.

Go to Applications, and click on the Add Application link. Select the box next to the version of MySQL  you want to install, and then scroll down and click the Next button. Depending on your current plan, there may be only one version of MySQL available to install.

This takes you back to the All Applications screen. Wait for five to ten minutes, then click on the Refresh link at the upper right, just under the word Parallels. The application should now show as installed. If it is still in a Scheduled state, wait another five minutes, and click Refresh again. If you see it in Error state, or it still shows as Scheduled, please contact eApps Technical Support.

Along with MySQL, you may also want to install phpMyAdmin, which provides a browser-based interface to manage MySQL databases.

MySQL versions available per plan

The version of MySQL that is available to you will depend on what Operating System your plan is running.

The newest versions of MySQL will only be available in the CentOS 5 plans, the older Fedora and CentOS 4 plans will not have the newest version of MySQL available. If you need the newest version of MySQL and are on a Fedora or CentOS 4 plan, you will need to upgrade to CentOS 5 for that version to be available to you.

To see what operating system (OS) you have, click on the Subscriptions icon from the My Account tab of your Control Panel. Then click on the name of the subscription you want to see. The OS for the subscription will be displayed near the top of the page. If you are not on a CentOS 5 plan, but would like information on updating your plan, please contact eApps Technical Support for more information.

• Fedora Core - The latest version of MySQL available for the Fedora Core plans is 4.1.13.

• CentOS 4 - The latest version of MySQL available for the CentOS 4 plans is 5.0.45.

• CentOS 5 -  The latest version of MySQL available for the CentOS 5 plans can be found in the list of Hosted Applications, found here – https://signup.eapps.com/store/apps.php

The current version of MySQL is in the MySQL 5.1 branch.

Changing the root MySQL password

The first thing to do after MySQL is installed is to change the root MySQL password. When MySQL is installed, a root MySQL password is created that is a long string of random characters. This needs to be changed to a password of your choosing – both for security reasons, and so that you are not having to enter a long password string every time you need to access MySQL as the root MySQL user.

Keep in mind that the root MySQL password and the system root user password are totally different things. The MySQL root password is only for accessing MySQL.

WARNING - The MySQL root password must be changed from the Control Panel only

It is possible to change the MySQL root password from the command line, but the Control Panel will not recognize the change and will become out of sync with MySQL, causing problems for both the Control Panel and the applications that rely on the Control Panel’s access to MySQL.

To change the root MySQL password, log in to your Control Panel, and click on the System Tab. If necessary, click the Select Another System (Subscription) link on the left and choose the correct Virtuozzo container.

Scroll down to Databases Management, and click on Databases. To change the MySQL root password, click on Change Root Password.

• Password – enter the new MySQL root password. This password can have numbers, letters, and special characters.

• Retype password – retype the password to confirm

Click Update to save the new MySQL root password.

---

Creating databases and database users

Once MySQL is installed, you can add databases and database users. This can be done from the Control Panel, the command line, or from phpMyAdmin.

Control Panel

Login to your Control Panel, and click on the System Tab. If necessary, click the Select Another System (Subscription) link on the left and choose the correct Virtuozzo container.

Scroll down to Databases Management, and click on Databases. To add a Database, click on Add Database.

Enter the Database Name – note that it must start with a letter and use only letters, numbers and underscores. Click Save.

This takes you to the Add Database User screen.

• Select an existing database user – check this if you want to use an existing database user to be able to access this database. If so, select that user from the drop down list, and click Save

• Create a new database user – check this if you want to create a new database user for this database.

Click Save to add the new database user.

Command line

To add a MySQL database and user from the command line, you will first need to log in to the VPS using SSH. See the SSH User Guide for more information. It does not matter what user you connect with via SSH, since all MySQL commands are run as the MySQL root user.

Remember that all commands within MySQL have to end with a semi-colon ( ; ) or a slash g ( \g ) .

First, login to MySQL as the root MySQL user:

[webadmin@example ~]$ mysql -uroot -p Enter password: passwd Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 235 Server version: 5.1.38 MySQL Community Server GA (GPL) by eApps.com Hosting Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Create the new database:

mysql> create database example_database; Query OK, 1 row affected (0.02 sec)

Create a user that can access this database, along with a password for that user. The same caveats about using the !,@,#, and $ characters in a password string apply here.

mysql> GRANT ALL ON example_database.* TO example_user@localhost IDENTIFIED BY "examplepass"; Query OK, 0 rows affected (0.06 sec) mysql> quit Bye

Test that user and password to make sure they work:

[webadmin@example ~]$ mysql -u example_user -p Enter password: passwd Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 236 Server version: 5.1.38 MySQL Community Server GA (GPL) by eApps.com Hosting Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>quit Bye

If you go back to the Databases section in the Control Panel and refresh the screen by clicking on the Databases link again, the new database and database user will show in the Control Panel. You can manage the database and database user from the Control Panel if you wish.

Also be aware that only the root MySQL user can see all the databases in MySQL. If you login as another user, you will only be able to see the databases associated with that specific user.

phpMyAdmin

phpMyAdmin is a browser based application that will allow you to manage your MySQL databases. To use phpMyAdmin, both it and PHP must be installed.

If you are not sure if phpMyAdmin or PHP is installed, go to Applications, and click on All Applications. If you see both of them in the list, then the applications are already installed.

If you do not see phpMyAdmin and/or PHP  installed, click on Add Application, and click the box next to the application(s) you need to install, and then scroll down and click the Next button.

This takes you back to the All Applications screen. Wait for five minutes, then click on the Refresh link at the upper right, just under the word Parallels. The application(s) should now show as installed. If it is still in a Scheduled state, wait another five minutes, and click Refresh again. If you see it in Error state, or it still shows as Scheduled, please contact eApps Technical Support.

Once phpMyAdmin is installed, click on the link to the Application in the All Applications list. This will show the Control Panel URL, which is in the form of http://example.com/myadmin/. You can also access phpMyAdmin from the URL directly, without having to use the Control Panel.

If you are using Tomcat/JBoss/Glassfish with mod_jk, please see the appropriate User Guide to learn how to pass requests for phpMyAdmin directly to Apache.


phpMyAdmin login screen

Adding a database and database user

To add a database and database user with phpMyAdmin, you will first need to login to phpMyAdmin as the root MySQL user, with the root MySQL password.

Once logged in, you are on the Home screen. Under the MySQL localhost heading, there is a Create new database section. Enter the name of the database, and click Create. (the defaults of Collation and utf8_general_ci can be left as is).


phpMyAdmin Create new database

Once you click Create, you will be taken to the Structure tab for the new database where you can create a new table. Click on the Privileges tab instead, so you can add a new user for this database.

Login Information

Login Information
On the Privileges tab, click Add a new User

• User name (Use text field): - enter the username for this database

• Host (Any host): - choose localhost from the drop down list

• Password (Use text field): - enter a password for the new user, keeping in mind the caveat about the !, @. #, and $ characters

• Re-type: - retype the password to confirm

• Generate Password – click on Generate to have phpMyAdmin generate a password for you if you wish

Database for user


Database for user

Leave the default of Grant all privileges on database “database_name” checked


Global priviliges (Check All/Uncheck All) - Leave this section at the defaults, which should be where everything is unchecked.


Global privileges and GO

Click Go to create the user.

The next screen shows the new user being created. You can edit their privileges here if needed.


User Added


Edit privileges

Once the database and database user has been created, log out of phpMyAdmin (click the Exit button), and then log back in with the new database user and password to test.

The database and database user are also now available in the Control Panel, and can be managed from there.

A few notes regarding phpMyAdmin – while phpMyAdmin can be used to manage every part of a MySQL database, do not use it to access sensitive data, because all the information sent to phpMyAdmin is sent in plain text. If you need to access sensitive data using phpMyAdmin, either purchase a commercial SSL certificate or set up a self-signed SSL certificate. See the SSL User Guide for more details.

Also be aware that only the root MySQL user can see all the databases in phpMyAdmin. If you login as another user, you will only be able to see the databases associated with that specific user.

---

Importing content to a MySQL database

There are several methods available to upload or import data into a MySQL database. Data can be uploaded via the Control Panel, or imported from the command line or through phpMyAdmin.

All of these examples assume that you have a valid MySQL dump file in .sql format to upload or import

Control Panel

Log in to your Control Panel, and click on the System Tab. If necessary, click the Select Another System (Subscription) link on the left and choose the correct Virtuozzo container.

Scroll down to Databases Management, and click on Databases. Then click on the name of the Database you want to upload content to, and click on the Database Content tab.

Click on the Upload MySQL Dump File icon. This allows you to either select a file located on the VPS, or on your local computer.

• File inside Container – enter the full path to the file, including the filename. For example, if the file were called data.sql and it was located in the example.com site directory, the entry would be similar to this: /home/webadmin/example.com/data.sql. There is no file size restriction on a MySQL dump file that is being imported from inside the container (other than the disk size limitations of your VPS plan).

• Upload a file – this allows you to browse for a file on your local computer. Note that the maximum file upload size is 5 MB

Command Line

This example assumes you are familiar with working from the Linux command line, and can easily navigate the file system using standard Linux commands. There is no file size restriction on a MySQL dump file that is being imported from the command line (other than the disk size limitations of your VPS plan).

To import a MySQL database from the command line, log in to the VPS using SSH. The user you connect with to the VPS is not important, what is important is that you import the database as the correct database user.

In this example, a MySQL dump file in sql format called example.sql is being imported to the example_database, which is owned by the example_user database user.

[webadmin@example ~]$ mysql -u example_user -p example_database < example.sql Enter password: passwd [webadmin@example ~]$

To verify that the content was imported, you can connect to MySQL as the database user, and view the tables.

[webadmin@example ~]$ mysql -u example_user -p Enter password: passwd Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 348 Server version: 5.1.38 MySQL Community Server GA (GPL) by eApps.com Hosting Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | example_database   | +--------------------+ 2 rows in set (0.00 sec) mysql> use example_database; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------------------+ | Tables_in_example_database | +----------------------------+ | attachment                 | | auth_cookie                | | component                  | | enum                       | | milestone                  | | ticket                     | | ticket_change              | | ticket_custom              | | version                    | | wiki                       | +----------------------------+ 10 rows in set (0.00 sec) mysql>

This should match what you know to be in the SQL file you imported.

Another way to view the database content is from the Control Panel, System Tab, Databases Management. Click on the database in question, and then click the Database Content tab. This will show the database tables.

phpMyAdmin

To import a MySQL dump file in sql format from phpMyAdmin, login to phpMyAdmin with the database username and password of the user who owns the database. Click on the database name, and once that screen loads, click on the Import tab.

With phpMyAdmin, the MySQL dump file has to be located on your local computer. Also, the size of the file that can be uploaded depends on the value of upload_max_filesize in the /etc/php.ini file. By default, that value is 2 MB. If you are trying to import a MySQL dump file using phpMyAdmin that is larger than 2 MB, you will need to increase this value and then restart the web server.


File to import

• Location of the text file – click on Browse to find the MySQL dump file on your local computer.

Unless you have very specific reasons to make any changes, leave the rest of the values at their defaults, and click on Go at the bottom right corner.

If the import is successful, there will be a message at the top of the screen that says: Import has successfully finished, some_number of queries executed.



Click back on the Home icon in phpMyAdmin, and then select the database. This will now show the database tables.

---

Stopping and starting MySQL

MySQL can be stopped/started/restarted from either the Control Panel, or from the command line of the VPS.

Control Panel

To stop, start or restart MySQL from the Control Panel, follow these steps. In the Control Panel, click on the System Tab. If necessary, click the Select Another System (Subscription) link on the left, and then choose the correct Virtuozzo container.

Go to Applications, and click on the All Applications link, and then click on MySQL. There will be a button to Stop or Start MySQL, depending on its current status. Note that it can take MySQL five minutes or so to fully shut down or start up, depending on the load and configurations.

MySQL can also be Stopped, Started or Restarted from the Services link on the System Tab. Click on the link for mysqld, and there will be a button to Stop, Start or Restart MySQL, depending on the current status.

Command line

To stop, start or restart MySQL from the command line, first log in to the VPS via SSH. See the SSH User Guide for more information if needed. Then, as the root user, use one of the following commands.

The name of the MySQL service is different on CentOS 4 than it is on Fedora and CentOS 5, and the commands return slightly different results.

To see what operating system (OS) you have, click on the Subscriptions icon from the My Account tab of your Control Panel. Then click on the name of the subscription you want to see. The OS for the subscription will be displayed near the top of the page. If you are not on a CentOS 5 plan, but would like information on updating your plan, please contact eApps Technical Support for more information.

Generally, it should be obvious whether or not the command was successful, no matter what OS you are using.

For Fedora and CentOS 5 users, the service is mysqld

Check the status of MySQL (Fedora and CentOS 5)

[root@example ~]# service mysqld status mysqld (pid 31833) is running... [root@example ~]#

Stopping MySQL (Fedora and CentOS 5)

[root@example ~]# service mysqld stop Stopping MySQL:                                            [  OK  ] [root@example ~]#

Starting MySQL (Fedora and CentOS 5)

[root@example ~]# service mysqld start Starting MySQL:                                            [  OK  ] [root@example ~]#

Restarting MySQL (Fedora and CentOS 5)

[root@example ~]# service mysqld restart Stopping MySQL:                                            [  OK  ] Starting MySQL:                                            [  OK  ] [root@example ~]#

For CentOS 4 users, the service is mysql

Check the status of MySQL (CentOS 4)

[root@example ~]# service mysql status  SUCCESS! MySQL running (4071) [root@example ~]#

Stopping MySQL (CentOS 4)

[root@example ~]# service mysql stop Shutting down MySQL.. SUCCESS! [root@example ~]#

Starting MySQL (CentOS 4)

[root@example ~]# service mysql start Starting MySQL SUCCESS! [root@example ~]#

Restarting Mysql (CentOS 4)

[root@example ~]# service mysql restart Shutting down MySQL. SUCCESS! Starting MySQL SUCCESS! [root@example ~]#

---

MySQL Configuration

Using a my.cnf file

MySQL uses option or configuration files to read startup options from. On your eApps VPS, this file is my.cnf, located in the /etc directory.

There is also a .my.cnf (dot my dot cnf) file located in the /root directory. Do not add these changes to that file. It is used by the Control Panel to store information.

If you have MySQL version 5.0.4 or later, an /etc/my.cnf file with default settings was created when MySQL was installed. If you have an older version of MySQL, you will need to create an /etc/my.cnf file manually.

If you are creating an /etc/ my.cnf file manually, the permissions on the file need to be 644, or rw-r--r-- , because MySQL will ignore any configuration file that is world writable.

A  my.cnf file is not necessary to successfully use MySQL. A my.cnf file is only used when some parameter of MySQL has to be changed or specified on startup.

If you are creating or modifying a my.cnf file, make sure to read all the appropriate MySQL documentation regarding the settings for that file for your version of MySQL. An incorrectly formatted my.cnf file can cause MySQL to fail on start up, and debugging the my.cnf file is outside of the standard eApps support.

The MySQL distribution provides some my.cnf files as examples. These files are located in the /usr/share/mysql directory. The files are my-huge.cnf, my-large.cnf, my-medium.cnf and my-small.cnf. Each file has a description at the top of the file describing the type of system it was designed for. Please read these descriptions carefully and choose the my.cnf file that is appropriate for your needs.

To use one of these files, copy it to your /etc directory, and rename it my.cnf. Edit the file to suit your specific needs, and restart MySQL. Make sure that you do not overwrite an existing my.cnf file, especially if you are using MySQL 5.0.4 and above.

Sample my.cnf configurations

Below are some common configurations seen in my.cnf files. Be aware that these configurations are used to solve very specific problems or issues, and are not generally required to successfully use MySQL.

Also, understand that using a value in a my.cnf file incorrectly, or with the incorrect syntax, can cause MySQL to fail on start up. Debugging your my.cnf file is outside of the standard eApps support. Use these sample configurations at your own risk!

Add these configurations to the my.cnf file either from the Control Panel, or via SSH. After adding the configurations, you will need to restart MySQL. If for some reason MySQL fails to start, remove the changes you made to the my.cnf file, and restart MySQL again.

• Logging slow queries – at times you might want to log which queries are taking longer than a specified time frame, in order to see what you need to optimize in your MySQL databases. This goes under the general [mysqld] heading. The location of the slow_queries.log is up to you, as is the long_query_time, which is in seconds.

• Case insensitive tables – if for some reason your tables are mixed case (which is not a best practice, and should be avoided whenever possible), this will tell MySQL to accept that the tables are not all in lower case. This also goes under the general [mysqld] heading.

• MyISAM tables optimization - the following is taken from the /usr/share/mysql/my-medium.cnf file, and will help optimize MyISAM tables for small to medium sized databases (10K to 20K records). This also goes under the general [mysqld] heading.

• InnoDB tables optimization - the following will help to optimize InnoDB tables for small and medium sized database (10K to 20K records). This also goes under the general [mysqld] heading.

• Increasing the idle timeout - by default MySQL drops any idle connections after 8 hours (28800 seconds). This means that your application can have problems connecting to your database if it sits idle for over 8 hours  (for example, overnight). This increases the idle timeout to 24 hours (86400 seconds). This also goes under the [mysqld]heading. 

---

MySQL remote access setup

By default, MySQL is set up to only accept connections from localhost. If you need to allow access to MySQL from a remote connection, you will need to create a user that can connect remotely.

To MySQL, the users bob@localhost and bob@example.com are totally separate users and can have totally separate passwords and privileges, even though they might be the same person.

To create a user with administrative privileges that can connect from a remote workstation, log in to the VPS via SSH, and connect to MySQL as the root user.

This example creates a user that can only connect from the example.com domain

[webadmin@example ~]$ mysql -u root -p Enter password: passwd Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 30 Server version: 5.1.38 MySQL Community Server GA (GPL) by eApps.com Hosting Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> GRANT ALL PRIVILEGES ON *.* TO 'bob'@'example.com' IDENTIFIED BY 'bobspasswd' WITH GRANT OPTION; Query OK, 0 rows affected (0.01 sec) mysql>

This example creates a user that can connect from any workstation 

mysql> GRANT ALL PRIVILEGES ON *.* TO 'bob'@'%' IDENTIFIED BY 'bobsotherpassword'; Query OK, 0 rows affected (0.00 sec)

Now you can use a tool like MySQL Query Browser or Toad to connect to your MySQL database.

For assistance with a third party product such as MySQL Query Browser or Toad, please contact the support community for those products. eApps offers no support or assistance for any third party connection tools.

---

JDBC connections

To make JDBC connections from your Java application to MySQL, you will need to install a connector on your VPS to allow MySQL to access the JVM. The connector is called MySQL Connector/J.

For users on Fedora or CentOS 4 plans, you will need to download the connector from the MySQL.com site.

For users on CentOS 5 or better plans, the connector can be installed from the Control Panel, Add Applications.

To see what operating system (OS) you have, click on the Subscriptions icon from the My Account tab of your Control Panel. Then click on the name of the subscription you want to see. The OS for the subscription will be displayed near the top of the page. If you are not on a CentOS 5 plan, but would like information on updating your plan, please contact eApps Technical Support for more information.

Fedora and CentOS 4

To install MySQL Connector/J for a Fedora or CentOS 4 system, you will need to either download the connector file to your local computer, extract the contents, and then upload the resulting .jar file to your VPS (the current version of the jar file is mysql-connector-java-5.1.10-bin.jar), or download the file to your VPS directly and extract it and then move the .jar file to the correct location.

The .jar file needs to go into the lib folder of your application server or application (depending on how you have your application configured). Common locations would be /opt/tomcat5/common/lib or /opt/tomcat6/lib.

To download the file, go to this location: http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.10.tar.gz/from/pick#mirrors

The site will try to look up your IP address to see where the closest mirror is. Click on the appropriate link if you want to download the file to your local computer.

The file is in .tar.gz format, so Windows users will need a program installed that will unpack that type of file, such as WinRAR. Mac OS X users can just double click on the file to open and extract it. Linux users can extract the file from the command line using the tar xvzf filename.tar.gz command.

Once the file is extracted, you can then move the .jar file to your VPS using whatever method your are comfortable with (FTP, SFTP, Control Panel) and then move it into place using either your FTP/SFTP client or the File Manager.

To download the file directly to your VPS, you will need to login to the VPS using SSH. Then, as the root user, you will need to use wget to retrieve the file. To get the correct URL, right click on the available HTTP  download link, and select Copy Link Location. Then, from the command line of the VPS, do the following (this example uses one of the mirrors located in the US, substitute your closest mirror):

[root@example ~]# wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.10.tar.gz/ --12:03:33--  http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.10.tar.gz/ Resolving dev.mysql.com... 213.136.52.29 Connecting to dev.mysql.com|213.136.52.29|:80... connected. HTTP request sent, awaiting response... 302 Found Location: http://opensource.become.com/mysql/Downloads/Connector-J/mysql-connector-java-5.1.10.tar.gz [following] --12:03:35--  http://opensource.become.com/mysql/Downloads/Connector-J/mysql-connector-java-5.1.10.tar.gz Resolving opensource.become.com... 64.124.85.129 Connecting to opensource.become.com|64.124.85.129|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 3666173 (3.5M) [application/x-gzip] Saving to: `mysql-connector-java-5.1.10.tar.gz' 100%[=================================================================>] 3,666,173   62.1K/s   in 60s    12:04:36 (59.6 KB/s) - `mysql-connector-java-5.1.10.tar.gz' saved [3666173/3666173] [root@example ~]#

The command is wget, then a space, and then paste the URL from Copy Link Location.

Once the file is downloaded, unpack the file.

[root@example ~]# tar xvzf mysql-connector-java-5.1.10.tar.gz

Then change directories into the new directory. The .jar file is there, and can be moved to the correct directory (in this example, /opt/tomcat6/lib)

[root@example ~]# cd mysql-connector-java-5.1.10 [root@example mysql-connector-java-5.1.10]# ll total 988 -rw-r--r-- 1 root root  47187 Sep 22 10:01 build.xml -rw-r--r-- 1 root root 180797 Sep 22 10:01 CHANGES -rw-r--r-- 1 root root  19451 Sep 22 10:01 COPYING drwxr-xr-x 2 root root   4096 Dec 10 12:54 docs -rw-r--r-- 1 root root   5256 Sep 22 10:01 EXCEPTIONS-CONNECTOR-J -rw-r--r-- 1 root root 724225 Sep 22 10:01 mysql-connector-java-5.1.10-bin.jar -rw-r--r-- 1 root root   1349 Sep 22 10:01 README -rw-r--r-- 1 root root   1394 Sep 22 10:01 README.txt drwxr-xr-x 7 root root   4096 Sep 22 10:01 src [root@example mysql-connector-java-5.1.10]# mv mysql-connector-java-5.1.10-bin.jar /opt/tomcat6/lib [root@example mysql-connector-java-5.1.10]#

Depending on how your application is set up, you may need to change the owner and group of the file to be tomcat:tomcat.

Remember that this is just an example to show the general process. Your exact steps may vary slightly. Please contact eApps Support if you need assistance.

CentOS 5

For CentOS 5, the Connector/J jar file can be installed from the Control Panel.

Log in to your Control Panel, and click on the System Tab. If necessary, click the Select Another System (Subscription) link on the left and choose the correct Virtuozzo container.

Go to Applications, and click on the Add Application link. Select the box next to MySQL-Connector-J, and then scroll down and click the Next button.

This takes you back to the All Applications screen. Wait for five to ten minutes, then click on the Refresh link at the upper right, just under the word Parallels. The application should now show as installed. If it is still in a Scheduled state, wait another five minutes, and click Refresh again. If you see it in Error state, or it still shows as Scheduled, please contact eApps Technical Support.

Once the MySQL Connector/J application is installed, you can create your JDBC connections for your application.

---

Backing up your MySQL databases

Your eApps VPS is backed up once a day, usually in the overnight hours of US Eastern Time (GMT -4/GMT -5 depending on the time of year). Please read the User Guide – Data Backups for more information.

Every seventh day a full snapshot backup is made, and an incremental backup is made in the six days between full backups. When the next full backup is run seven days later, the previous full backup is rotated off the backup server and removed. eApps only guarantees to have a maximum of seven days worth of backups available at any time.

It is important to understand that at no time during the backup process are any services stopped or any databases dumped.

All that happens is that a standard file copy is run. For example, if any database transactions are in process when the VPS is being backed up, they will not appear in the backup. If transactions are added to the database after the backup is run, but are deleted before the next backup is run (usually 24 hours later), they will be lost.

You need to take responsibility for backing up your mission critical data. If the data is important to you or your business, making sure you have current backups needs to be one of your top priorities. Do not rely on eApps to have your data!

There are several strategies you can take to backup your databases. You can use the mysqldump command from the VPS command line, or the Export feature in phpMyAdmin. Database replication using a master/slave setup is also available – contact the Sales Department for more information on that feature.

Using mysqldump to backup your databases

To backup your databases using mysqldump, you will need to connect to the VPS using SSH. The mysqldump commands will need to be run as the system root user, not as the mysql root user.

Using mysqldump to back up a single database

The mysqldump command can be used to back up a single database. This example shows the example_database being backed up to the example_backup.sql file.

[root@example ~]# mysqldump -p example_database > example_backup.sql Enter password: passwd [root@example ~]#

The syntax of the command is:
mysqldump -p database_name > name_of_backup_file.sql

You can enter the password at the -p prompt in the command string if you wish. This is what you would do if you were scripting the backup, for example.

Using mysqldump to backup all databases

The mysqldump command can also be used to make a backup of all databases at once. This is useful if you are going to have to move a large number of databases

[root@example ~]# mysqldump --all-databases  -p > databases_file.sql Enter password: passwd [root@example ~]#

This backs up all databases on the VPS.

mysqldump is a complex application, with many options and variables. If you have questions on other uses for mysqldump that are not covered in this User Guide, please reference the official documentation – http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

Using phpMyAdmin to backup your databases

The phpMyAdmin program has an Export feature that can be used to backup your databases. You can backup either single databases, or select multiple databases, or all databases.

To begin, login to phpMyAdmin as the root MySQL user. Click on the Export tab. This will list all the MySQL databases on the VPS. You can either Select All (or Unselect All), or select multiple databases by holding down the Control key on Windows or the Command key on Mac OS X.

After the database or databases are selected, the only thing that has to be checked is the Save as file selection at the bottom of the screen. If you wish to use Compression, select that here also. Then click on Go to download the database to your personal computer. The file will be downloaded as localhost.sql (with a compression type extension if used). It is advisable to change the name of the file to reflect the name of the database (keep the extension unchanged).


Export database

There are many available options to use for Exporting your databases using phpMyAdmin. The default options should work in most cases. If you have questions on some of the other options, please consult the official phpMyAdmin documentation, which is available by clicking the Question Mark (?) icon in the left navigation pane of the application. The official MySQL documentation is also available by clicking on the SQL icon.

---

Upgrading MySQL

Before upgrading any version of MySQL, make certain to read the Release Notes for you version of MySQL you are upgrading to.

Reading the Release Notes and following the instructions for upgrading is vital if you wish to avoid data loss or corruption. eApps Hosting is not responsible for any data loss if you do not follow the instructions in the Release Notes.

Before you upgrade MySQL, make a backup of your databases. See the Backing up your MySQL Databases section of this User Guide for more information.

For some versions of MySQL, the upgrade process is as simple as backing up your MySQL database, uninstalling the current version of MySQL, and installing the new version.

For other versions of MySQL, you will need to backup your databases, uninstall the old version of MySQL, install the new version, import your databases again, and then run some SQL commands to fix the existing tables to work with the new version of MySQL.

The Release Notes for upgrading MySQL are found here – http://support.eapps.com/release/all

The Release Notes are either for MySQL 4.1.13a or MySQL 5. Please read them carefully.

---

MySQL with SSL

The ability to connect to the database remotely using SSL encryption is also available, starting with MySQL 5.0.27. For information on this, see the SSL for MySQL User Guide.

---

Links to other information

Official MySQL site – http://mysql.com/
Official MySQL documentation – http://dev.mysql.com/doc/
Wikibooks MySQL Cheat Sheet – http://en.wikibooks.org/wiki/MySQL/CheatSheet
MySQL Cheat Sheet from Added Bytes – http://www.addedbytes.com/cheat-sheets/mysql-cheat-sheet/
phpMyAdmin documentation – http://www.phpmyadmin.net/home_page/docs.php

Please login to comment