Charmed MySQL Tutorial > 2. Deploy MySQL

Deploy Charmed MySQL

In this section, you will deploy Charmed MySQL, access a unit, and interact with the MySQL databases that exist inside the application.

Summary

• Deploy MySQL
• Access MySQL
  • Retrieve credentials
  • Access MySQL via the mysql client

Deploy MySQL

To deploy Charmed MySQL, run the following command:

juju deploy mysql

Juju will now fetch Charmed MySQL from Charmhub and deploy it to the LXD cloud. This process can take several minutes depending on how provisioned (RAM, CPU, etc) your machine is. You can track the progress by running:

juju status --watch 1s

This command is useful for checking the real-time information about the state of a charm and the machines hosting it. Check the juju status documentation for more information about its usage.

When the application is ready, juju status will show the mysql app as active and the mysql/0* unit as idle, like the example below:

Model      Controller  Cloud/Region         Version  SLA          Timestamp
tutorial   overlord    localhost/localhost  3.5.2    unsupported  00:52:59+02:00

App    Version          Status  Scale  Charm  Channel     Rev  Exposed  Message
mysql  8.0.32-0ubun...  active      1  mysql  8.0/stable  151  no       Primary

Unit      Workload  Agent  Machine  Public address  Ports           Message
mysql/0*  active    idle   1        10.234.188.135  3306,33060/tcp  Primary

Machine  State    Address         Inst id        Base          AZ  Message
1        started  10.234.188.135  juju-ff9064-0  [email protected]      Running

To exit the screen with juju status --watch 1s, enter Ctrl+C.

You can also watch juju logs with the juju debug-log command. More info on logging in the juju logs documentation.

Access MySQL

The easiest way to access MySQL is via the MySQL Command-Line Client (mysql). For this, we must first retrieve the credentials.

Retrieve credentials

Connecting to the database requires that you know the values for host (IP address), username and password.

To retrieve username and password, run the Juju action get-password on the leader unit as follows:

juju run mysql/leader get-password

Example output:

...
password: yWJjs2HccOmqFMshyRcwWnjF
username: root

To request a password for a different user, use the option username:

juju run mysql/leader get-password username=<username>

To retrieve the host’s IP address, run juju status. This should be listed under the “Public address” of the unit hosting the MySQL application:

...
Unit      Workload  Agent  Machine  Public address  Ports  Message
mysql/0*  active    idle   1        10.234.188.135  3306,33060/tcp  Primary
...

Access MySQL via the mysql client

To access the unit hosting Charmed MySQL, one could normally use the following command:

mysql -h <ip_address> -u<username> -p<password>

However, this is not possible with the root user. For security reasons, the root user is restricted to only allow connections from localhost.

The way to access MySQL server with the root user is to first ssh into the primary Juju unit:

juju ssh mysql/leader

In this case, we know the primary unit is the juju leader unit, since it is the only existing unit.

In a cluster with more units, the primary is not necessarily equivalent to the leader. To identify the primary unit in a cluster, run juju run mysql/<any_unit> get-cluster-status. This will display the entire cluster topology.

Once inside the Juju virtual machine, the root user can access MySQL by calling

mysql -h 127.0.0.1 -uroot -pyWJjs2HccOmqFMshyRcwWnjF

Remember, your password will be different to the example above. Make sure to insert it without a space as -p<password>

You will then see the mysql> command prompt, similar to the output below:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 8.0.32-0ubuntu0.22.04.2 (Ubuntu)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

If at any point you’d like to leave the mysql client, enter Ctrl+D or type exit.

You can now interact with MySQL directly using any MySQL Queries. For example entering SELECT VERSION(), CURRENT_DATE; should output something like:

mysql> SELECT VERSION(), CURRENT_DATE;
+-------------------------+--------------+
| VERSION()               | CURRENT_DATE |
+-------------------------+--------------+
| 8.0.32-0ubuntu0.22.04.2 | 2023-01-29   |
+-------------------------+--------------+
1 row in set (0.00 sec)

Feel free to test out any other MySQL queries.

When you’re ready to leave the mysql shell you can just type exit. Once you’ve typed exit you will be back in the host of Charmed MySQL (mysql/0). Exit this host by once again typing exit. Now you will be in your original shell where you first started the tutorial; here you can interact with Juju and LXD.

Next step: 3. Scale your replicas