Bitnami MySQL for Google Cloud Platform

MySQL is one of the world’s most popular open source database servers. It supports pluggable database engines, replication, partitioning, stored procedures, triggers, views and many other features.

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.

Find out how to obtain application credentials.

How to create a database backup?

To back up only the database, create a dump file using the mysqldump tool.

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

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

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 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, the value for the BITNAMI_USER_PASSWORD placeholder is included in the application credentials or, if the credentials were defined by the user, it is the same as the application password.

$ 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 data contained inside your database. 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 the application. These files are stored in the application folder itself, so copy this folder to have a backup of your uploaded files.

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.

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 you are using MySQL 5.7 only, use the following content instead of that shown above:

     UPDATE mysql.user SET authentication_string=PASSWORD('NEW_PASSWORD') 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 --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 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, follow these steps:

  • Make sure that you have your cloud server's IP address and application credentials (instructions).

  • Open port 3306 in the server firewall (instructions).

  • Connect to your cloud server using PuTTY or another SSH client (instructions).

  • At the server console, edit the file /opt/bitnami/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:

     $ /opt/bitnami/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 application password you noted in the first step.

  • Restart the MySQL server:

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

Your MySQL server is now configured to accept remote connections, and 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 cloud 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 connect to MySQL from a different machine?

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. Then, use the command below and replace SERVER-IP with the IP address of the server hosting the database.

$ mysql -h SERVER-IP -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. Consult the official installation steps in the application's documentation.

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 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 /opt/bitnami/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 /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 secure your server from remote connections?

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 as explained here.

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

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.

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.