Applicable Plans - eApps Cloud Hosting Plans (eApps templates only)

User Guide - Using MySQL

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 Cloud Hosting service is designed to make it easy to use MySQL in your Virtual Machine. 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
Changing the root MySQL password

Creating databases and database users
Creating a database from the Control Panel
Add a database user from the Control Panel

Creating a database and user from the Command Line
Creating a database and user using phpMyAdmin

Importing content to a MySQL database
Command Line
phpMyAdmin

Stopping and starting MySQL
Control Panel
Command Line

MySQL Configuration
Using a my.cnf file

MySQL Remote Access

JDBC Connections

Backing up MySQL Databases
Using mysqldump
Using phpMyAdmin

Links to other information

Installing MySQL and setting the MySQL root password

Installing MySQL from the Control Panel

MySQL is installed from the Applications section of the Control Panel. See the User Guide - Installing and Managing Applications http://support.eapps.com/control_panel_eapps/app_manager for more information if needed.

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

Changing the root MySQL password

The first thing to do after MySQL is installed is to set the root MySQL password. When MySQL is installed, no root MySQL password is created. A password needs to be created of your choosing for security reasons.

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.

Control Panel

Click on Servers in the toolbar at the top, then MySQL Database Server, then Change Administration Password 

Here you can change the MySQL admin (root) password.

• New password - enter in the new mysql root password. Your Virtual Machine uses a program called cracklib that will require the password to have at least six (6) characters, and at least one special character such as !@#$%. This feature is mandatory, and will not be disabled.
• Repeat password - enter the password again to confirm

Click on Change Now to set the new password.

Command Line

To set the MySQL root password from the command line of the Virtual Machine, you will need to connect to the command line of your VM. See the User Guide - Connecting to your Virtual Machine (SSH and Console) http://support.eapps.com/portal/ssh for more information if necessary.

Your password will need to pass the cracklib verification, meaning that it must have a minimum of six (6) characters, and at least one special character such as @#$%. This feature is mandatory, and will not be disabled.

It does not matter what user you connect with via the command line, since all MySQL commands will be 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. Remember that at this point, there is no MySQL root password.

[webadmin@example ~]$ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.1.34 MySQL Community Server GA (GPL) by eApps.com Hosting

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Set a new password:

mysql> use mysql;
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> update user set password=password('insert your password here') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye

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.

Creating a database from the Control Panel

Login to the Control Panel, and click on Servers in the toolbar at the top. See the User Guide - Using the Control Panel http://support.eapps.com/control_panel_eapps/control_panel for more information about configuring and accessing the Control Panel.

Click on MySQL Database Server, log in with the “root” user name and password (or whatever user you wish) and click on Create a new database.

Enter the Database Name – note that it must start with a letter and use only letters, numbers and underscores. Configure anything else you wish to at this point.

Click Create.

Add a database user from the Control Panel

Login to the Control Panel, and click on Servers in the toolbar at the top. Click on MySQL Database Server, log in with the “root” user name and password (or whatever user you wish)

In Global Options click on User Permissions

Click on a User name to change the permissions, or click on Create new user to create a new MySQL user.

MySQL user details

• Username - click the button next to the textbox, and enter the MySQL user name
• Password - set the password for the user
• Hosts - select Any to allow this user to connect from any host, or specify a hostname
• Permissions - set the desired permissions for the user

Click Create to create the user.

Creating a database and user from the Command line

To add a MySQL database and user from the command line, you will first need to connect to the command line of the Virtual Machine.

It does not matter what user you connect with via the command line, 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. This password must be a minimum of six (6) characters, and contain at least one special character such as !@#$%.

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 MySQL Databases section in the Control Panel, the new database will show in the Control Panel and the user will appear in User Permissions. 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.

Creating a database and user using 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.

Adding a database and user to phpMyAdmin

Once phpMyAdmin is installed, you will will be able to access it via any website or IP address assigned to the Virtual Machine at /myadmin – for example, http://eapps-example.com/myadmin

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).

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

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, which must be a minimum of six (6) characters and have at least one special character such as !@#$%
• 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

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.

Click Go to create the user.

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

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 imported from the command line or uploaded through phpMyAdmin.

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

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 Virtual Machine).

See the User Guide - Connecting to your Virtual Machine (SSH and Console) http://support.eapps.com/portal/ssh for more information on how to connect to your VM. The user you connect with to the Virtual Machine 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, Servers menu item, MySQL Database Server, MySQL Databases. Click on the database in question, 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.

• 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 Virtual Machine.

Control Panel

To stop, start or restart MySQL from the Control Panel, log in to the Control Panel, and click on the System menu item. MySQL can be restarted from either the MySQL Database Server, or from Service Management.

MySQL Database Server

To stop and start MySQL from the MySQL Database Server module, click the button. This will stop the MySQL server. You will be taken to a screen where you can

Service Management

To stop, start and restart MySQL from Service Management click on the Service Management icon

Find and click on the mysqld service

Edit Action

This screen show the script that is run when the MySQL service starts. You can also configure whether MySQL starts at system boot, and you can Start Now, Restart Now and Stop Now.

NOTE - only change values in the Action Script section if you are a MySQL expert, and you know exactly what you are changing and why. Otherwise make no changes here.

Command line

To stop, start or restart MySQL from the command line, you will need to connect to the command line of the VM. Then, as the root user, use one of the following commands:

Check the status of MySQL

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

Stopping MySQL

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

Starting MySQL

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

Restarting MySQL

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

MySQL Configuration

Using a my.cnf file

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

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 File Manager in the Control Panel, or via the command line. 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.

  log-slow-queries=/tmp/slow_queries.log long_query_time=2

• 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.

  lower_case_table_names = 1

• 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.

  skip-locking key_buffer = 16M max_allowed_packet = 8M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M

• 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.

  #innodb innodb_buffer_pool_size = 96M innodb_flush_log_at_trx_commit = 1 innodb_additional_mem_pool_size = 2M innodb_log_buffer_size = 2M

• 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.

  interactive_timeout = 86400

MySQL remote access setup

If you need to allow access to MySQL from a remote connection, you will need to create a user that can connect remotely.

NOTE - To MySQL, the users bob@localhost and bob@eapps-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, connect to the command line of the VM, and then connect to MySQL as the root user.

This example creates a user that can only connect from the eapps-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'@'eapps-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 Virtual Machine to allow MySQL to access the JVM. The connector is called MySQL Connector J (eApps).

To install the MySQL Connector J, you will need to log in to the Control Panel and install the application from the Application Manager.

Once the MySQL Connector J application is installed, you can create your JDBC connections for your application. Please consult the documentation for your application framework if you need assistance in creating the JDBC connections.

Backing up your MySQL databases

By default, there are no backups taken of your Virtual Machine. Please read the User Guide - Managing Virtual Machine Backups http://support.eapps.com/portal/backups for more information. If you want to make backups of your VM that you can store on the storage array, you will need to purchase additional backup space.

WARNING - 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 Virtual Machine 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 command line of the VM. 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 or backup 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 Virtual Machine.

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 Virtual Machine. 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).

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.

Links to other information

MySQL main site - http://www.mysql.com/

MySQL documentation - http://dev.mysql.com/doc/