How to start/stop the MySQL server?

    The Stacks include a graphical tool to manage the servers easily. You can find the "manager-windows.exe", "manager-osx" or "manager-linux" tool in your installation directory. Using this tool, you can Start, Stop or Restart the servers and check the log files. You can click on the icon to start it.

    manager-servers.png

     

    manager-osx.png

     

     

     

     

     

     

     

     

     


    win_platform.png On Windows: You can also start the Manager tool from shortcuts: Start -> Program Files -> BitNami Stack -> Manager tool

     

    If you prefer, you can use the "ctlscript.sh" utility from the command line. This script is in the installation directory.

    mac_platform.png

    On OS X: You can start the Manager tool from the installation directory or you can use the "ctlscript.sh" utility from a Terminal.

    host:~ user$ cd /Applications/application-version
    host:~ user$ ./ctlscript.sh start
    

    linux_platform.png On Linux:

    $ cd ~/applicaton-version
    $ ./ctlscript.sh start
    

    What is the default configuration?

    MySQL's grant tables are created during the BitNami Stack installation process. 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 initial password for this account is the same you specified in the setup wizard.
      • If you are using the Virtual Appliance or the AMI, the password is "bitnami" by default.
      • If you are using BitNami Cloud Hosting the password is the same that you set from the admin panel.
    • An user account for the application database. The username is 'bitnami' and the password is randomly created during the installation. You can check the 'bitnami' user credentials in the database configuration file for each application.

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

    For security reasons, the MySQL database does not let external connections by default. You only can connect from the local machine.

    MySQL configuration file

    The location of the MySQL configuration file depends on the platform that you are using.

    Cloud Image/BitNami Hosting

    Ubuntu based Bitnami cloud images use the MySQL Bitnami component, for this platform the MySQL configuration file is located at

    /opt/bitnami/mysql/etc/my.cnf
    

    Native Installer

    On Linux and Mac OS X, the MySQL configuration file is located in your installation directory in mysql/etc/my.cnf.

    On Windows, it is located in your installation directory in mysql/my.ini

    Virtual Machine

    Bitnami virtual machines use the MySQL Bitnami component, for this platform the MySQL configuration file is located at

    /opt/bitnami/mysql/etc/my.cnf
    

    Amazon Linux / RHEL

    Amazon Linux and RHEL Bitnami cloud images are configured using the system components from these platforms. For these platforms, MySQL configuration file is located at:

    /et/my.cnf
    

    Tabs end

    In the MySQL oficial documentation you can find more details about how configure your settings for the MySQL database: https://dev.mysql.com/doc/refman/5.5...-defaults.html

    MySQL socket

    On Unix the MySQL clients can connect to the server using an Unix socket file. In  a BitNami Stack the default socket file is created in /installdir/mysql/tmp/mysql.sock. Usually when you use a mysql client tool included in the stack you will not need to specify the socket for the connection.

    How to connect to the MySQL database?

    You can connect to MySQL database from the same computer where it is installed.

    1. Open the BitNami console
    2. Run the mysql client:
    $ mysql -u root -p

    You can also run the mysql client directly "/installdir/mysql/bin/mysql". If you have installed phpMyAdmin component, you can manage the MySQL database from a browser. You can find more info at phpMyAdmin.

    How to connect to the MySQL database from a different machine?

    Bitnami Cloud Hosting/Cloud Images

    For security reasons, MySQL will be accessible only when using 127.0.0.1 as the hostname.  In order to access it, you can create an SSH tunnel to MySQL, sending your requests from 127.0.0.1 (localhost). If you want to make MySQL less restrictive and allow remote access without creating a tunnel, check the "Native installers/Virtual Machines" configuration instead.

    For creating a tunnel you should follow these steps:

    1. Get your SSH private key if you don't have it already. More info

    2. Open the port 3306 (the default MySQL port) in the firewall. 

    - In Bitnami Cloud Hosting you can use the Bitnami Console. In "Manage" your server go to the firewall tab and add this port in as a custom rule. 

    - For Amazon Cloud images, you will need to add this por in your security group.

    - On Azure, you will need to add an endpoint for this port.

    3. Create the tunnel.

    - If you are in Linux or Mac you can run the following in a Terminal in your local machine (using your Public DNS instead of xyz.bitnamiapp.com):

    $ ssh -N -L 3307:127.0.0.1:3306 -i bitnami-hosting.pem bitnami@xyz.bitnamiapp.com
    

    - If you are using Windows, you can use Putty:

    • Follow the steps described here
    • Before opening the connection. Go to Connection -> SSH -> Tunnels, enter the values below and click "Add" button:
      • Source port: "3307"
      • Destination: "127.0.0.1:3306" 
    • Click Open

    4. Now you should be able to connect to the remote MySQL using 127.0.0.1 as the hostname and port 3307.

     

    Native installers/Virtual Machines

    You can enable external connections for the MySQL database.

    $ /installdir/mysql/bin/mysql -u root -p -e "grant all privileges on *.* to 'root'@'%' identified by 'your_root_password' with grant option";
    

    In recent versions, MySQL specifies the following option in the "/installdir/msyql/my.cnf" or "/installdir/mysql/my.ini" file:

    bind-address = 127.0.0.1

    You should also comment this option and restart the MySQL server. Then you can access to the MySQL database from any machine.

    If you are using a Virtual Appliance, it is also necessary to open the MySQL port in the firewall

    Tabs end

    How to create a database for a custom application?

    If you want to install an application manually, it is possible that it requires the database settings during the installation. These are the basic steps to create a database for your application. You should run the following commands from the console that you can find in the Windows shortcuts or from the Terminal on OS X and Linux.

    $ /installdir/mysql/bin/mysql -u root -p
    Password: (the password that you set during the installation)
    
    mysql> create database database_name;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> grant all privileges on database_name.* TO 'database_user'@'localhost' identified by 'not_easy_password'
    Query OK, 1 row affected (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> exit;
    Bye
    

    It is possible that some application requires specific privileges in the database. Consult the official installation steps in the application documentation.

    How to change the MySQL root password?

    By default nobody can access the database from outside of the Virtual Machine or AMI but it is also recommended that you change it. You can modify the mysql password using the following command at the shell prompt:

    $ /installdir/mysql/bin/mysqladmin -p -u root password <your_new_password>
    
    • The default password for MySQL root user in Virtual Appliances and AMIs is "bitnami"
    • The default password for MySQL root user in BitNami Cloud Hosting is the same that you set in the administration panel.
    • The default password for MySQL root user in a BitNami Magento stack is "bitnami1"

    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:

    Assuming your BitNami stack is located in the /opt/bitnami directory, create a file in /home/bitnami/mysql-init with the content shown below (replace mynewpassword string with the one you want):

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

    Stop the MySQL server:

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

    Start MySQL with the following command:

    $ sudo /opt/bitnami/mysql/bin/mysqld_safe --defaults-file=/opt/bitnami/mysql/my.cnf --pid-file=/opt/bitnami/mysql/data/mysqld.pid --init-file=/home/bitnami/mysql-init 2> /dev/null &
    

    Restart the MySQL server:

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

    Remove the init script

    $ rm /home/bitnami/mysql-init
    

    How to create a database backup?

    If you are interested in a backup of only the database data, you can create a dump file using the “mysqldump” tool.

    $ /installdir/mysql/bin/mysqldump -u root -p database_name > backup.sql
    

    This operation could take some time depending on the data size.

    There is also an alternative way to create a full backup of the Stack, see Native_Installers_Quick_Start_Guide#How_can_I_create_a_full_backup_of_a_Stack.3f.

    How to restore a database backup?

    Once you have the backup file, you can restore it.

    $ mysql -u root -p database_name < backup.sql
    

    If you want to restore the database and the database schema does not exist, it is necessary to first follow the steps described below. As a side note, you can find the value for BITNAMI_USER_PASSWORD below in the application configuration file. This depends on the application and you can find this information in the application page itself.

    $ mysql -u root -p
    mysql> create database database_name;
    mysql> grant all privileges on database_name.* to 'bitnami'@'localhost'
    identified by 'BITNAMI_USER_PASSWORD';
    mysql> flush privileges;
    mysql> exit;
    $ mysql -u root -p database_name < backup.sql
    

    Note that the steps previously described will only back up the state of your database. Other files that you should take into account when performing a backup are files that have been uploaded to the
    application. These files are stored in the application folder itslef so you can copy this folder to have a backup of your uploaded files.

    How to change the data directory?

    The data directory for MySQL is in /installdir/mysql/data folder by default. You can modify the location of this folder modifying the following files:

    /installdir/mysql/my.cnf on OS or Linux or /installdir/mysql/my.ini on Windows:

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

    /installdir/mysql/scripts/ctl.sh:

    --datadir=/installdir/mysql/data

    Then move the "data" folder to the new location and restart the database.

    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". If you are using a Virtual Machine or a Cloud Image you can 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, we got 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
    

    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 (/installdir/mysql/etc/my.cnf) for trying to fix the database:

    [mysqld]
    innodb_force_recovery = 1
    

    Then 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 a MySQL user table.

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

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

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

    If you find a different error or you could not fix the issue, we will be glad to try to help you at http://answers.bitnami.com

    How to debug errors?

    The main MySQL log file is created at /installdir/mysql/data/mysqld.log file.

    Comments

    You must login to post a comment.

    Attach file

    Attachments