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.
What are the differences between a Bitnami Single-Tier Solution and Multi-Tier Solution?
Single-tier architecture implies that all the required components of an application run on a single server. If your environment is growing and becoming more complex, a single layer architecture will not meet your scalability requirements. Single-Tier Solutions are great for departmental applications, smaller production environments, new users, or those applications that don't support multi-tier architectures.
The typical architecture of a Bitnami Single-Tier Solution looks like this:
Multi-tier architecture involves more than one server and infrastructure resource. For example, the Front End-Database topology separates the application server from the database server. This allows you to extend workloads in the cloud and tailor your application to meet specific scalability and reliability goals. Multi-Tier Solutions provide more sophisticated deployment topologies for improved scalability and reliability for larger production or mission critical environments.
|TIP: Not sure if you have chosen the right solution? Check out the Bitnami Multi-Tier solutions features and benefits to learn more about the benefits of Multi-Tier.|
This Bitnami Multi-Tier Solution uses multiple virtual machines to replicate the databases from the master node to a configurable number of replicas. This topology is illustrated below:
First steps with the Bitnami MySQL Stack
Welcome to your new Bitnami application running on Google Multi-Tier Solutions! 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 that allow you to log in to your new Bitnami application. These credentials consist of a username and password.
- The server credentials that allow you to log in to your Google Multi-Tier Solutions server using an SSH client and execute commands on the server using the command line. These credentials consist of an SSH username and key.
What is the administrator username set for me to log in to the application for the first time?
What SSH username should I use for secure shell access to my application?
SSH username: bitnami
How to start or stop the services?
|NOTE: The steps below require you to execute the commands on the remote server. Please check our FAQ for instructions on how to connect to your server through SSH.|
Each Bitnami server includes a control script that lets you easily stop, start and restart all the services installed on the current individual server.
Obtain the status of a service with the service bitnami status command:
$ sudo service bitnami status
Use the service bitnami command to start, stop or restart all the services in a similar manner:
Start all the services.
$ sudo service bitnami start
Stop all the services.
$ sudo service bitnami stop
Restart all the services.
$ sudo service bitnami restart
|TIP: To start, restart or stop individually each server of the cluster, check the FAQ section about how to start or stop servers in a Multi-Tier Solution.|
What is the default configuration?
The grant tables define the initial MySQL user accounts and their access privileges. The default configuration consists of two user accounts:
- root: This user has all privileges including remote access to the database.
- repl: Has "Replication slave" privileges which enable replication slaves to read binary log events from the master.
Check our recommendations for a production server.
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/conf/my.cnf.
The MySQL official documentation has more details about how to configure the MySQL database.
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.
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/tmp/mysqld.pid.
MySQL error log
The error log file contains information indicating when MySQL was started and stopped and also any critical errors that occur while the server is running. If the MySQL server 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/logs/mysqld.log.
How is the cluster configured?
The Bitnami Multi-Tier Solution for MySQL uses multiple VMs, consisting of 1 master and 1 or more slaves, to provide a horizontally scalable and fault-tolerant deployment. Data automatically replicates from the master node to all slave nodes. Binary logging is enabled.
To understand how it works, consider the example below of a two-node cluster (one master and one slave):
On the master node, create a new table and populate it with some data:
mysql [(none)]> USE bitnami; Database changed mysql [bitnami]> CREATE TABLE test (id INT NOT NULL, value VARCHAR(255) NOT NULL); Query OK, 0 rows affected (0.37 sec) mysql [bitnami]> INSERT INTO test VALUES (1, 'foo'), (2, 'bar'); Query OK, 2 rows affected (0.07 sec) Records: 2 Duplicates: 0 Warnings: 0
On any of the slave nodes, check if the table exists and list its contents. It should display the same data originally entered on the master node:
mysql [(none)]> USE bitnami; Database changed mysql [bitnami]> SELECT * FROM test; +----+-------+ | id | value | +----+-------+ | 1 | foo | | 2 | bar | +----+-------+ 2 rows in set (0.00 sec)
This shows that records added on the master node are automatically replicated to the slave node(s). For more information, refer to the MySQL documentation on replication.
How to check cluster replication status?
To check the status of your MySQL cluster, log in to the master or primary database server host using SSH, start the MySQL command-line client using your administrative credentials, and run the following command within it:
SHOW PROCESSLIST; SHOW SLAVE HOSTS;
The output of these commands will be a list of cluster nodes and their current replication status.
How to connect to cluster nodes?
Some operations such as changing the application password, require that some actions will be repeated in each cluster node. That way, you need to connect to each node for the changes to take effect in the whole cluster. Follow the steps below to connect the cluster nodes in your Google deployments:
- Log in to the Google Cloud Console and select your project.
Navigate to the "Compute Engine -> VM instances" page.
You will see a list of all the instances launched in your project. Click on the instance that corresponds to the node you want to connect.
Once you are in the "VM instance details" page, go to the "Remote access" section and click the "SSH" button.
Now you should be connected to the node you have selected:
|NOTE: Remember to repeat the same operation to connect to each cluster node.|
How to add nodes to the cluster?
|IMPORTANT: These steps assume that you have already installed the Google Cloud SDK and you are signed in to the Google Cloud Platform through the gcloud command-line client. If this is not the case, please refer to the Google Cloud SDK documentation for instructions on how to install and use the command-line client.|
To add nodes to the cluster, follow these steps:
- Log in to the Google Cloud Console.
- Browse to the Deployment Manager and select the deployment to which you wish to add nodes.
- In the deployment overview, review the deployment properties and click to view the "Expanded Config" deployment configuration file.
- Copy or download the contents of the "Expanded Config" file to the server with the Google Cloud SDK as expanded-config.yaml.
Edit the file using a text editor and add configuration for one or more additional nodes, by copying the configuration and metadata for an existing node and its corresponding data disk and then updating the copied configuration to use a unique name for the new node(s) and data disk(s).
For example, to add a new MongoDB node to a MongoDB cluster, here is an abridged example of the configuration and metadata that you would update to add a new node and data disk. To create a unique name for the new node, you would typically replace the XX placeholder in the node name with a number.
NOTE: The code block below is an illustrative example for MongoDB and will differ in your specific deployment. You should always copy the code block from your deployment's actual configuration file.
- metadata: dependsOn: - mongodb-multivm-2-node-XX-data - mongodb-multivm-2-node-0 name: mongodb-multivm-2-node-XX properties: bootDiskType: pd-standard canIpForward: true disks: - autoDelete: true boot: true deviceName: mongodb-multivm-2-node-XX-boot initializeParams: diskType: https://www.googleapis.com/compute/v1/projects/PROJECT-NAME/zones/us-central1-f/diskTypes/pd-standard sourceImage: projects/bitnami-launchpad/global/images/bitnami-mongodb-3-4-7-0-linux-debian-8-x86-64-nami type: PERSISTENT - autoDelete: true boot: false deviceName: mongodb-multivm-2-node-XX-data source: $(ref.mongodb-multivm-2-node-XX-data.selfLink) type: PERSISTENT machineType: https://www.googleapis.com/compute/v1/projects/PROJECT-NAME/zones/us-central1-f/machineTypes/n1-standard-1 metadata: ... networkInterfaces: - accessConfigs: - name: External NAT type: ONE_TO_ONE_NAT network: https://www.googleapis.com/compute/v1/projects/PROJECT-NAME/global/networks/default subnetwork: https://www.googleapis.com/compute/v1/projects/PROJECT-NAME/regions/us-central1/subnetworks/default serviceAccounts: - email: default scopes: - https://www.googleapis.com/auth/cloudruntimeconfig tags: items: - mongodb-multivm-2-node-XX zone: us-central1-f type: compute.v1.instance - name: mongodb-multivm-2-node-XX-data properties: sizeGb: 10 type: https://www.googleapis.com/compute/v1/projects/PROJECT-NAME/zones/us-central1-f/diskTypes/pd-standard zone: us-central1-f type: compute.v1.disk
Preview the updated deployment with the command below. Replace the DEPLOYMENT-ID placeholder in the command below with the correct name of your deployment.
$ gcloud deployment-manager deployments update DEPLOYMENT-ID --config expanded-config.yaml --preview
Once you have verified that the deployment preview is correct, confirm the deployment and initialize the new node(s):
$ gcloud deployment-manager deployments update DEPLOYMENT-ID
Verify that the new node(s) have been added successfully by logging in to the Google Cloud Console and selecting the deployment to check the number of running nodes. Once you have confirmed that the new node(s) have been added successfully, log in to the primary node and verify that the new node(s) are now part of the cluster by following these instructions.
How to connect instances hosted in separate virtual networks or VPCs?
The Google Cloud Platform makes it possible to connect instances hosted in separate Virtual Private Clouds (VPCs), even if those instances belong to different projects or are hosted in different regions. This feature, known as VPC Network Peering, can result in better security (as services do not need to be exposed on public IP addresses) and performance (due to use of private, rather than public, networks and IP addresses).
How to connect to MySQL from a different machine?
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.
Connecting to MySQL from the same network
To connect to MySQL from a different machine in the same network, use a command like the one below:
$ mysql -h SERVER-IP -u root -p
You will be prompted to enter the root user password. This is the same password entered during the server deployment process.
If you wish to connect to the primary MySQL host from a different network using its public IP address, we recommend creating an SSH tunnel, as described in the FAQ. However, you should only do this if you wish to temporarily connect to, or use, the MySQL console. This approach is not recommended to permanently connect your application to the MySQL cluster, as a connectivity failure in the SSH tunnel would affect your application's functionality.
Connecting to MySQL from a different network
If you must connect to the database from a machine that it is not running in the same network as the MySQL cluster, you can follow these approaches (these are shown in order of preference, from the most secure to the less recommended solution):
- Option 1: Peer both virtual networks to secure the connections between the two instances. Learn how to connect instances in different networks using network peering.
Option 2: Create an SSH tunnel to connect the database console to perform administrative tasks using the primary host's public IP address. Refer to the FAQ for more information on this.
NOTE: You should only access the primary server using an SSH tunnel if you wish to temporarily connect to, or use, the MySQL console. This approach is not recommended to permanently connect your application to the MySQL cluster, as a connectivity failure in the SSH tunnel would affect your application's functionality.
- Option 3: Make the server publicly accessible and restrict 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.
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:
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;
It is strongly recommended that you do not have empty passwords for any user accounts when using the server for any production work.
The configuration adopted by the slaves when connecting to the master is set using the "CHANGE MASTER TO" syntax. Replication slaves store the password for the replication master in the master info repository. In case you receive the following warning message in the log file of MySQL you can use the "START SLAVE" syntax to specify credentials for connecting to the master.
|IMPORTANT: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL official documentation for more information.|
How to find the MySQL database credentials?
- Database username: root.
- Database password: The same as the application password. Find out how to obtain application 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 password entered during the server deployment process](/google-templates/faq#how-to-find-application-credentials).
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.
- Configure the connection as follows:
- Click "Test Connection" to test the connection.
If the connection is successful, click "OK" to save the connection.
- 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:
How to change the MySQL root password?
You can modify the MySQL password using the following command at the shell prompt:
$ /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 service bitnami stop
Start MySQL with the following command:
$ sudo /opt/bitnami/mysql/bin/mysqld_safe --defaults-file=/opt/bitnami/mysql/conf/my.cnf --ledir=/opt/bitnami/mysql/bin --datadir=/opt/bitnami/mysql/data --pid-file=/opt/bitnami/mysql/tmp/mysqld.pid --init-file=/home/bitnami/mysql-init 2> /dev/null &
Restart the MySQL server:
$ sudo service bitnami restart
Remove the init script:
$ rm /home/bitnami/mysql-init
You should now be able to access the database server with the new 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 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 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/logs/mysqld.log. Check the latest entries in the MySQL log file with the following command:
$ sudo tail -n 100 /opt/bitnami/mysql/logs/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/tmp/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 service bitnami restart
If you find a different error or cannot fix an issue, we can try to help at http://community.bitnami.com.
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).|
Perform the steps below on each node of the cluster to configure table encryption support:
Edit the /opt/bitnami/mysql/bitnami/my.cnf file and add the following lines to it, within the [mysqld] section, to activate the keyring_file plugin:
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 service bitnami 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'
So long as all the cluster nodes are configured to support table encryption, changes to encrypted tables on the master node will replicate as normal to the slave nodes. The keyring file will also be automatically created on the slave nodes if it does not exist.
How to allow remote connections to the MySQL server?
IMPORTANT: We strongly discourage opening ports to allow inbound connections to the server from a different network. Making the application's network ports public is a significant security risk. The recommended way for connecting two instances deployed in different networks is by using VPC network peering. If you must make it accessible over a public IP address, we recommend restricting access to a trusted list of source IP addresses and ports using firewall rules. To do so, follow the instructions below.
To connect to your remote MySQL database server using a remote client or a visual tool like 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
Comment out this line by placing a hash (#) symbol at the beginning, so that it looks like this:
- 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 service bitnami restart mysql