Bitnami MySQL Installer

NOTE: Before running the commands shown on this page, you should load the Bitnami stack environment by executing the installdir/use_APPNAME script (Linux and Mac OS X) or by clicking the shortcut in the Start Menu under "Start -> Bitnami APPNAME Stack -> Application console" (Windows). Learn more.
NOTE: When running the commands shown on this page, replace the installdir placeholder with the full installation directory for your Bitnami stack.

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! Here are a few questions (and answers!) you might need when first starting with your application.

What are the system requirements?

Before you download and install your application, check that your system meets these requirements.

How do I install the Bitnami MySQL Stack?

Windows, OS X and Linux installer
  • Download the executable file for the Bitnami MySQL Stack from the Bitnami website.

  • Run the downloaded file:

    • On Linux, give the installer executable permissions and run the installation file in the console.
    • On other platforms, double-click the installer and follow the instructions shown.

Check the FAQ instructions on how to download and install a Bitnami Stack for more details.

The application will be installed to the following default directories:

Operating System Directory
Windows C:\Bitnami\APPNAME-VERSION
Mac OS X /Applications/APPNAME-VERSION
Linux /opt/APPNAME-VERSION (running as root user)
OS X VM
  • Download the OS X VM file for the Bitnami MySQL Stack from the Bitnami website.
  • Begin the installation process by double-clicking the image file and dragging the WordPress OS X VM icon to the Applications folder.
  • Launch the VM by double-clicking the icon in the Applications folder.

What credentials do I need?

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

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

  • For Windows, Linux and OS X installers, the username was configured by you when you first installed the application.
  • For OS X VMs, the username can be obtained by clicking the Bitnami badge at the bottom right corner of the application welcome page.

What is the administrator password?

  • For Windows, Linux and OS X installers, the password was configured by you when you first installed the application.
  • For OS X VMs, the password can be obtained by clicking the Bitnami badge at the bottom right corner of the application welcome page.

How to start or stop the services?

Linux

Bitnami native installers include a graphical tool to manage services. This tool is named manager-linux-x64.run on Linux and is located in the installation directory. To use this tool, double-click the file and then use the graphical interface to start, stop or restart services. Server log messages can be checked in the "Server Events" tab.

Management tool

The native installer also includes a command-line script to start, stop and restart applications, named ctlscript.sh. This script can be found in the installation directory and accepts the options start, stop, restart, and status. To use it, log in to the server console and execute it following the examples below:

  • Call it without any service names to start all services:

      $ sudo installdir/ctlscript.sh start
    
  • Use it to restart a specific service only by passing the service name as argument - for example, mysql, postgresql or apache:

      $ sudo installdir/ctlscript.sh restart mysql
      $ sudo installdir/ctlscript.sh restart postgresql
      $ sudo installdir/ctlscript.sh restart apache
    
  • Obtain current status of all services:

      $ installdir/ctlscript.sh status
    

The list of available services varies depending on the required components for each application.

Mac OS X

Bitnami native installers include a graphical tool to manage services. This tool is named manager-osx on Mac OS X and is located in the installation directory. To use this tool, double-click the file and then use the graphical interface to start, stop or restart services. Server log messages can be checked in the "Server Events" tab.

Management tool

The native installer also includes a command-line script to start, stop and restart applications, named ctlscript.sh. This script can be found in the installation directory and accepts the options start, stop, restart, and status. To use it, log in to the server console and execute it following the examples below:

  • Call it without any service names to start all services:

    $ sudo installdir/ctlscript.sh start
    
  • Use it to restart a specific service only by passing the service name as argument - for example, mysql or apache:

     $ sudo installdir/ctlscript.sh restart mysql
     $ sudo installdir/ctlscript.sh restart apache
    
  • Obtain current status of all services:

     $ installdir/ctlscript.sh status
    

The list of available services varies depending on the required components for each application.

NOTE: If you are using the stack manager for Mac OS X-VM, please check the following blog post to learn how to manage services from its graphical tool.

Windows

Bitnami native installers include a graphical tool to manage services. This tool is named manager-windows.exe on Windows and is located in the installation directory. To use this tool, double-click the file and then use the graphical interface to start, stop or restart services. Server log messages can be checked in the "Server Events" tab.

Management tool

The Windows native installer creates shortcuts to start and stop services created in the Start Menu, under "Programs -> Bitnami APPNAME Stack -> Bitnami Service". Servers can also be managed from the Windows "Services" control panel. Services are named using the format APPNAMESERVICENAME, where APPNAME is a placeholder for the application name and SERVICENAME is a placeholder for the service name. For example, the native installer for the Bitnami WordPress Stack installs services named wordpressApache and wordpressMySQL.

These services will be automatically started during boot. To modify this behaviour, refer to the section on disabling services on Windows.

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 installdir/mysql/my.cnf. Some configuration overrides are stored in installdir/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 installdir/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 installdir/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 installdir/mysql/data/mysqld.log.

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 find the MySQL 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?

IMPORTANT: Bitnami Native Installers do not modify the firewall configuration of your computer, therefore the MySQL ports could be open which is a significant security risk. You are strongly advised to close the MySQL ports (refer to the FAQ for more information on this).

Once you have an active SSH tunnel or if you did not close 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 did not close 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 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.

$ installdir/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 installdir/mysql/bin/mysqladmin --version or installdir/mysql/bin/mysqld --version.
  • Stop the MySQL server:

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

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

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

     $ rm /home/bitnami/mysql-init
    

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 data directory?

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

...
datadir=installdir/mysql/data
...

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

--datadir=installdir/mysql/data

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

How to encrypt a database table?

NOTE: Table encryption support is only available for InnoDB tables stored as individual files (the innodb_file_per_table option, enabled by default).

Follow the steps below to configure table encryption support:

  • Edit the installdir/mysql/bitnami/my.cnf file and add the following lines to it, within the [mysqld] section, to activate the keyring_file plugin:

    early-plugin-load=keyring_file.so
    keyring_file_data=installdir/mysql/data/keyring
    
    NOTE: The keyring file will be automatically created in the above location when the first table is encrypted. Keep a backup of this file as the data stored in the encrypted tables cannot be recovered without it.
  • Restart the MySQL server:

    $ sudo installdir/ctlscript.sh restart mysql
    
  • Confirm that the keyring_file plugin is active by running the query below in the MySQL client:

    SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';
    

You should now be able to create an encrypted table by adding the ENCRYPTED='Y' clause to any CREATE TABLE command. Here is an example:

CREATE TABLE mytable (id INT, value VARCHAR(255)) ENCRYPTION='Y'

Tables which are not already encrypted can be encrypted by using an ALTER TABLE command, such as the one below:

ALTER TABLE mytable ENCRYPTION='Y'

How can I run a command in the Bitnami MySQL Stack?

Load the Bitnami Console and then run the command as usual.

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 debug errors in your database?

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

How to change the MySQL root password in Windows?

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.

installdir\mysql\bin\mysqladmin.exe -p -u root password NEW_PASSWORD

How to reset the MySQL root password in Windows?

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

  • Stop the MySQL server using the graphic manager tool. Refer to the how to start or stop the services section.
  • Check the MySQL version:

    installdir\mysql\bin\mysqladmin.exe –version

  • Create a file named mysql-init.txt with the content shown below depending on your MySQL version (replace NEW_PASSWORD with the password you wish to use):
    • MySQL 5.6.x or earlier:

       UPDATE mysql.user SET Password=PASSWORD('NEW_PASSWORD') 
       WHERE User='root';
       FLUSH PRIVILEGES;
      
    • MySQL 5.7.x or later:

       ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_PASSWORD'; 
      
  • Start MySQL server with the following command. Remember to replace PATH with the location in which you have saved the mysql-init.txt file:

     installdir " installdir\mysql\bin\mysqld.exe" --defaults-file=" installdir\mysql\my.ini" --init-file="\PATH\mysql-init.txt" --console
    
    • The --init file option is used by the server for executing the content of the mysql-init.txt file at startup, it will change each root account password.
    • The --defaults-file option is specified since you have installed MySQL using the Bitnami installer.
    • The --console option (optional) has been added in order to show the server output at the console window rather than in the log file.
  • After some minutes, hit Ctrl-C to force the shutdown.
  • Restart the MySQL server from the graphic manager tool.
  • After the server has restarted successfully, delete the mysql-init.txt file.

How to configure MySQL Workbench?

NOTE: This section assumes that you have downloaded and installed MySQL Workbench.

To connect to your remote MySQL database server using MySQL Workbench, you have to allow remote connections to the server.

Once your MySQL server is configured to accept remote connections, you can connect to it using MySQL Workbench. Follow these steps:

  • Launch MySQL Workbench.
  • Click the "+" symbol in the "MySQL Connections" tab to add a new connection.

    MySQL Workbench configuration

  • Configure the connection as follows:
    • Enter a name for the connection in the "Connection Name" field.
    • Select "Standard (TCP/IP)" as the "Connection Type".
    • Enter your server's IP address in the "Hostname" field.
    • Specify the "Port" as "3306".
    • Specify the "Username" as "root".

      MySQL Workbench configuration

  • Click "Test Connection" to test the connection.
  • If the connection is successful, click "OK" to save the connection.

    MySQL Workbench configuration

  • Double-click the new connection to launch the MySQL Workbench SQL Editor. You may be prompted for a password. Use the same password you used when previously configuring the server to accept remote connections. Once connected, the SQL editor window will open and you can interact with the server using SQL commands, as shown below:

MySQL Workbench configuration

How to allow remote connections to the MySQL server?

IMPORTANT: Bitnami Native Installers do not modify the firewall configuration of your computer, therefore the MySQL ports could be open which is a significant security risk. You are strongly advised to close the MySQL ports (refer to the FAQ for more information on this).

To connect to your remote MySQL database server using a remote client or a visual tool like MySQL Workbench, follow these steps:

Windows, Mac OS X, Linux

  • Open port 3306 in your firewall. For example, if you are using ufw, you would run the following commands (on Linux only):

     $ sudo ufw allow mysql/tcp
    
  • At the server console, edit the file installdir/mysql/my.cnf and find the line containing

     bind-address=127.0.0.1
    
  • Comment out this line by placing a hash (#) symbol at the beginning, so that it looks like this:

     #bind-address=127.0.0.1
    
  • Save the file.
  • At the server console, allow remote access to the MySQL database server using the MySQL command line client. Use the following command, remembering to replace PASSWORD with the application password from the first step:

    $ installdir/mysql/bin/mysql -u root -p -e "grant all privileges on *.* to 'root'@'%' identified by 'PASSWORD' with grant option";
    
  • When prompted for a password, enter the MySQL password you entered during the installation process.
  • Restart the MySQL server using the graphical manager (Windows and Mac OS X) or by executing the following command at the server console (Linux):

    $ sudo installdir/ctlscript.sh restart mysql
    

    MySQL manager

Mac OS X-VM

  • Select the "Network" tab, then click "Add".

MySQL Workbench configuration for Mac OS X-VM

  • On the resulting dialog, enter 3306 as local and remote port. Active the "Require local" and "Over SSH" checkboxes, and click "Ok" to save your changes.

MySQL Workbench configuration for Mac OS X-VM

  • On the "Network" section, you will see an SSH tunnel to the 3306 port enabled.

MySQL Workbench configuration for Mac OS X-VM

  • If the connection is successful, click "OK" to save the connection.

MySQL Workbench configuration

  • Double-click the new connection to launch the MySQL Workbench SQL Editor. You may be prompted for a password. Use the following password: bitnami1.

Once connected, the SQL editor window will open and you can interact with the server using SQL commands, as shown below:

MySQL Workbench configuration

How to install and run mysqltuner?

To install mysqtuner, download the mysqltuner.pl script. For example, you can download it with wget:

$ wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl

To monitor a MySQL server installed by a Bitnami stack, pass the connection values through the command line, as shown below. Note that you will need Perl installed on the system.

$ perl mysqltuner.pl --socket installdir/mysql/tmp/mysql.sock

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 installdir/mysql/data/mysqld.log. Check the latest entries in the MySQL log file with the following command:

$ sudo tail -n 100 installdir/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 installdir/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=installdir/mysql/data/mysqld.pid 
     --skip-external-locking --port=3306 --sock=installdir/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 installdir/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.

nativeInstaller

Bitnami Documentation