Deploy your Bitnami MySQL Stack on Bitnami Cloud Hosting now! Launch Now

MySQL for Bitnami Cloud Hosting

Description

MySQL is a fast, reliable, scalable, and easy to use open-source relational database system. MySQL Server is designed to handle mission-critical, heavy-load production applications.

First steps with the Bitnami MySQL Stack

Welcome to your new Bitnami application running on Bitnami Cloud Hosting! Here are a few questions (and answers!) you might need when first starting with your application.

What credentials do I need?

You need two sets of credentials:

  • The application credentials, consisting of a username and password. These credentials allow you to log in to your new Bitnami application.

  • The server credentials, consisting of an SSH username and key. These credentials allow you to log in to your Bitnami Cloud Hosting server using an SSH client and execute commands on the server using the command line.

What is the administrator username set for me to log in to the application for the first time?

Username: root

What is the administrator password?

Click here for instructions.

What SSH username should I use for secure shell access to my application?

SSH username: bitnami

How do I get my SSH key or password?

You can obtain your SSH key from the Bitnami Cloud Hosting dashboard. Click here for more information.

What are the default ports?

A port is an endpoint of communication in an operating system that identifies a specific process or a type of service. Bitnami stacks include several services or servers that require a port.

Remember that if you need to open some ports you can follow the instructions given in the FAQ to learn how to open the server ports for remote access.

Port 22 is the default port for SSH connections.

The MySQL access port is 3306. This port is closed by default, you must open it to enable remote access.

How to start or stop the services?

Each Bitnami stack includes a control script that lets you easily stop, start and restart services. The script is located at /opt/bitnami/ctlscript.sh. Call it without any service name arguments to start all services:

$ sudo /opt/bitnami/ctlscript.sh start

Or use it to restart a single service, such as Apache only, by passing the service name as argument:

$ sudo /opt/bitnami/ctlscript.sh restart apache

Use this script to stop all services:

$ sudo /opt/bitnami/ctlscript.sh stop

Restart the services by running the script without any arguments:

$ sudo /opt/bitnami/ctlscript.sh restart

Obtain a list of available services and operations by running the script without any arguments:

$ sudo /opt/bitnami/ctlscript.sh

How to upload files to the server with SFTP?

NOTE: Bitnami applications can be found in /opt/bitnami/apps.

First, obtain your SSH credentials by following these steps:

  • Browse to the Bitnami Cloud Hosting dashboard and sign in if required using your Bitnami account.

  • Select the "Servers" menu item.

  • Select your cloud server from the resulting list.

  • Click the "Manage" button.

  • On the resulting page, note the server IP address.

    SSH credentials

  • Click the "Connect" button and download the SSH key for your server in .ppk format (for FileZilla or WinSCP) or in .pem format (for Cyberduck).

    SSH credentials

Although you can use any SFTP/SCP client to transfer files to your server, this guide documents FileZilla (Windows, Linux and Mac OS X), WinSCP (Windows) and Cyberduck (Mac OS X).

Using an SSH Key

Once you have your server's SSH key, choose your preferred application and follow the steps below to connect to the server using SFTP.

FileZilla
IMPORTANT: To use FileZilla, your server private key should be in PPK format.

Follow these steps:

  • Download and install FileZilla.
  • Launch FileZilla and use the "Edit -> Settings" command to bring up FileZilla's configuration settings.
  • Within the "Connection -> SFTP" section, use the "Add keyfile" command to select the private key file for the server. FileZilla will use this private key to log in to the server.

    FileZilla configuration

  • Use the "File -> Site Manager -> New Site" command to bring up the FileZilla Site Manager, where you can set up a connection to your server.
  • Enter your server host name and specify bitnami as the user name.
  • Select "SFTP" as the protocol and "Ask for password" as the logon type.

    FileZilla configuration

  • Use the "Connect" button to connect to the server and begin an SFTP session. You might need to accept the server key, by clicking "Yes" or "OK" to proceed.

You should now be logged into the /home/bitnami directory on the server. You can now transfer files by dragging and dropping them from the local server window to the remote server window.

If you have problems accessing your server, get extra information by use the "Edit -> Settings -> Debug" menu to activate FileZilla's debug log.

FileZilla debug log

WinSCP
IMPORTANT: To use WinSCP, your server private key should be in PPK format.

Follow these steps:

  • Download and install WinSCP.
  • Launch WinSCP and in the "Session" panel, select "SFTP" as the file protocol.
  • Enter your server host name and specify bitnami as the user name.

    WinSCP configuration

  • Click the "Advanced…" button and within the "SSH -> Authentication -> Authentication parameters" section, select the private key file for the server. WinSCP will use this private key to log in to the server.

    WinSCP configuration

  • From the "Session" panel, use the "Login" button to connect to the server and begin an SCP session.

You should now be logged into the /home/bitnami directory on the server. You can now transfer files by dragging and dropping them from the local server window to the remote server window.

If you need to upload files to a location where the bitnami user doesn't have write permissions, you have two options:

  • Once you have configured WinSCP as described above, click the "Advanced…" button and within the "Environment -> Shell" panel, select sudo su - as your shell. This will allow you to upload files using the administrator account.

    WinSCP configuration

  • Upload the files to the /home/bitnami directory as usual. Then, connect via SSH and move the files to the desired location with the sudo command, as shown below:

     $ sudo mv /home/bitnami/uploaded-file /path/to/desired/location/
    
Cyberduck
IMPORTANT: To use Cyberduck, your server private key should be in PEM format.

Follow these steps:

  • Select the "Open Connection" command and specify "SFTP" as the connection protocol.

    Cyberduck configuration

  • In the connection details panel, under the "More Options" section, enable the "Use Public Key Authentication" option and specify the path to the private key file for the server.

    Cyberduck configuration

  • Use the "Connect" button to connect to the server and begin an SFTP session.

You should now be logged into the /home/bitnami directory on the server. You can now transfer files by dragging and dropping them from the local server window to the remote server window.

What is the default configuration?

The grant tables define the initial MySQL user accounts and their access privileges. The default configuration consists of:

  • A privileged account with a username of root. The root user has remote access to the database.
  • An anonymous user without remote access to the database server. This user can only connect from the local machine and it is only intended for testing.
  • A test database only intended for testing.

Check our recommendations for a production server.

MySQL version

In order to see which MySQL version your system is running, execute the following command:

$ mysqld --version

MySQL configuration file

The MySQL configuration file is located at /opt/bitnami/mysql/my.cnf. Some configuration overrides are stored in /opt/bitnami/mysql/bitnami/my.cnf.

The MySQL official documentation has more details about how to configure the MySQL database.

MySQL socket

On Unix, MySQL clients can connect to the server in the local machine using an Unix socket file at /opt/bitnami/mysql/tmp/mysql.sock.

MySQL port

The default port for MySQL is 3306.

MySQL Process Identification Number

The MySQL .pid file allows other programs to find out the PID (Process Identification Number) of a running script. Find it at /opt/bitnami/mysql/data/mysqld.pid.

MySQL error log

The log-error file contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running. If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log.

Find it at /opt/bitnami/mysql/data/mysqld.log.

How to find the database credentials?

How to connect to the MySQL database?

You can connect to the MySQL database from the same computer where it is installed with the mysql client tool.

$ mysql -u root -p

You will be prompted to enter the root user password. This is the same as the application password.

How to connect to MySQL from a different machine?

For security reasons, the MySQL port in this solution cannot be accessed over a public IP address. To connect to MySQL from a different machine, you must open port 3306 for remote access. Refer to the FAQ for more information on this.

IMPORTANT: By default, the database port for the nodes in this solution cannot be accessed over a public IP address. As a result, you will only be able to connect to your database nodes from machines that are running in the same network. For security reasons, we do not recommend making the database port accessible over a public IP address. If you must make it accessible over a public IP address, we recommend restricting access to a trusted list of source IP addresses using firewall rules. Refer to the FAQ for information on opening ports in the server firewall.

Once you have an active SSH tunnel or you opened the port for remote access, you can then connect to MySQL using a command like the one below.

Remember to replace SOURCE-PORT with the source port number specified in the SSH tunnel configuration or 3306 if you opened the port for remote access.

$ mysql -h 127.0.0.1 -P SOURCE-PORT -u root -p

You will be prompted to enter the root user password. This is the same as the application password.

How to create a database for a custom application?

These are the basic steps to create a new database and user for your applications:

  • Create a new database:

     mysql> create database DATABASE_NAME;
     Query OK, 1 row affected (0.00 sec)
    
  • Create a new user (only with local access) and grant privileges to this user on the new database:

     mysql> grant all privileges on DATABASE_NAME.* TO 'USER_NAME'@'localhost' identified by 'PASSWORD';
     Query OK, 1 row affected (0.00 sec)
    
  • Create a new user (with remote access) and grant privileges to this user on the new database:

     mysql> grant all privileges on DATABASE_NAME.* TO 'USER_NAME'@'%' identified by 'PASSWORD';
     Query OK, 1 row affected (0.00 sec)
    
  • After modifying the MySQL grant tables, execute the following command in order to apply the changes:

     mysql> flush privileges;
     Query OK, 1 row affected (0.00 sec)
    

Some applications require specific privileges in the database. Check the MySQL official getting started guide.

How to change the MySQL root password?

You can modify the MySQL password using the following command at the shell prompt. Replace the NEW_PASSWORD placeholder with the actual password you wish to set.

$ /opt/bitnami/mysql/bin/mysqladmin -p -u root password NEW_PASSWORD

How to reset the MySQL root password?

If you don't remember your MySQL root password, you can follow the steps below to reset it to a new value:

  • Create a file in /home/bitnami/mysql-init with the content shown below (replace NEW_PASSWORD with the password you wish to use):

     UPDATE mysql.user SET Password=PASSWORD('NEW_PASSWORD') WHERE User='root';
     FLUSH PRIVILEGES;
    

    If your stack ships MySQL v5.7.x, use the following content instead of that shown above:

     UPDATE mysql.user SET authentication_string=PASSWORD('NEW_PASSWORD') WHERE User='root';
     FLUSH PRIVILEGES;
    
    TIP: Check the MySQL version with the command /opt/bitnami/mysql/bin/mysqladmin --version or /opt/bitnami/mysql/bin/mysqld --version.
  • Stop the MySQL server:

     $ sudo /opt/bitnami/ctlscript.sh stop mysql
    
  • Start MySQL with the following command:

     $ sudo /opt/bitnami/mysql/bin/mysqld_safe --pid-file=/opt/bitnami/mysql/data/mysqld.pid --datadir=/opt/bitnami/mysql/data --init-file=/home/bitnami/mysql-init 2> /dev/null &
    
  • Restart the MySQL server:

     $ sudo /opt/bitnami/ctlscript.sh restart mysql
    
  • Remove the script:

     $ rm /home/bitnami/mysql-init
    

How to create a database backup?

To back up all the databases, create a dump file using the mysqldump tool.

$ mysqldump -A -u root -p > backup.sql

This operation could take some time depending on the database sizes.

NOTE: The steps previously described will only back up the data contained inside your databases. There may be other files that you should take into account when performing a full backup, such as files that may have been uploaded to your application. Refer to your application's documentation for more details.

How to restore a database backup?

Once you have the backup file, you can restore it with a command like the one below:

$ mysql -u root -p < backup.sql

How to secure your server?

Once you have created a new database and user for your application, connect to your MySQL server and follow these recommendations:

  • Remove anonymous users:

     mysql> DELETE FROM mysql.user WHERE User='';
    
  • Remove the test database and access to it:

     mysql> DROP DATABASE test;
     mysql> DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
    
  • Disallow root login remotely:

     mysql> DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
    

    Don't forget to reload the privileges tables to apply the changes:

     mysql> FLUSH PRIVILEGES;
    
  • Change your root user password.

  • It is strongly recommended that you do not have empty passwords for any user accounts when using the server for any production work.

  • If you don't need remote access, uncomment the line

     #bind-address=127.0.0.1
    

    in the MySQL configuration file to only listen for connections on the local machine. Restart the server once done.

How to debug errors in your database?

The main log file is created at /opt/bitnami/mysql/data/mysqld.log on the MySQL database server host.

How to recover a MySQL database with errors?

Before trying to recover a MySQL database, you should check the exact error in the MySQL log file at /opt/bitnami/mysql/data/mysqld.log. Check the latest entries in the MySQL log file with the following command:

$ sudo tail -n 100 /opt/bitnami/mysql/data/mysqld.log 

In this case, assume the following error in the log file:

110108 10:37:45 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'user' is marked as crashed

Here are some steps to resolve this error:

  • The MySQL database is configured to use InnoDB engine by default. You can add the innodb_force_recovery=1 option in the main MySQL configuration file at /opt/bitnami/mysql/etc/my.cnf to try and fix the database:

     [mysqld]
     innodb_force_recovery = 1
    
  • Start the MySQL database with the following command:

     $ mysqld --skip-grant-tables --user=mysql --pid-file=/opt/bitnami/mysql/data/mysqld.pid 
     --skip-external-locking --port=3306 --sock=/opt/bitnami/mysql/tmp/mysql.sock
    
  • Open a new console and try to log in the database:

     $ mysql -u root -p
    
  • In this case, the error was related to the mysql.user table. Run these commands:

     mysql> use mysql;
     mysql> repair table user;
     mysql> check table user;
     mysql> exit;
    

If the table is recovered, you should see "OK" in the mysql.user status table. Do not forget to remove the innodb_force_recovery option from the my.cnf file and restart the MySQL server again.

$ sudo /opt/bitnami/ctlscript.sh restart mysql

If you find a different error or cannot fix an issue, we can try to help at http://community.bitnami.com.

How to change the data directory?

The data directory for MySQL is set to /opt/bitnami/mysql/data by default. You can modify the location of this folder modifying the /opt/bitnami/mysql/my.cnf file, as shown below:

...
datadir=/opt/bitnami/mysql/data
...

Also modify the /opt/bitnami/mysql/scripts/ctl.sh file to reflect the new directory location:

--datadir=/opt/bitnami/mysql/data

Finally, move the data/ directory to the new location and restart the database.

bch