# Deploy Redis as a cache for MySQL on an AWS Arm based Instance ## In this learning path - [Introduction](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/) - [Deploy Redis as a cache for MySQL on an AWS Arm based Instance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/redis_mysql_aws/) - [Deploy Redis as a cache for MySQL on an Azure Arm based Instance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/redis_mysql_azure/) - [Deploy Redis as a cache for MySQL on a GCP Arm based Instance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/redis_mysql_gcp/) - [Deploy Redis as a cache for Postgres on an AWS Arm based Instance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/redis_psql_aws/) - [Deploy Redis as a cache for Postgres on an Azure Arm based Instance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/redis_psql_azure/) - [Deploy Redis as a cache for Postgres on a Google Cloud Arm based Instance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/redis_psql_gcp/) - [Next Steps](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/_next-steps/) You can deploy Redis as a cache for MySQL on an AWS Arm based Instance using Terraform and Ansible. In this section, you will deploy Redis as a cache for MySQL on an AWS Instance. If you are new to Terraform, you should look at [Automate AWS EC2 instance creation using Terraform](https://learn.arm.com/learning-paths/servers-and-cloud-computing/aws-terraform/terraform/) before starting this Learning Path. ## Before you begin You should have the prerequisite tools installed before starting the Learning Path. Any computer which has the required tools installed can be used for this section. The computer can be your desktop or laptop computer or a virtual machine with the required tools. You will need an [AWS account](https://portal.aws.amazon.com/billing/signup?nc2=h_ct&src=default&redirect_url=https%3A%2F%2Faws.amazon.com%2Fregistration-confirmation#/start) to complete this Learning Path. Create an account if you don’t have one. Before you begin, you will also need: - An AWS access key ID and secret access key. - An SSH key pair. ### Generate an SSH key-pair Generate an SSH key-pair (public key, private key) using `ssh-keygen` to use for AWS EC2 access. To generate the key-pair, follow this [guide](https://learn.arm.com/install-guides/ssh/#ssh-keys). > **Note**: If you already have an SSH key-pair present in the `~/.ssh` directory, you can skip this step. ### Acquire AWS Access Credentials The installation of Terraform on your desktop or laptop needs to communicate with AWS. Thus, Terraform needs to be able to authenticate with AWS. For authentication, generate access keys (access key ID and secret access key). These access keys are used by Terraform for making programmatic calls to AWS via the AWS CLI. To generate and configure the Access key ID and Secret access key, follow this [guide](https://learn.arm.com/install-guides/aws_access_keys/). ## Create an AWS EC2 instance using Terraform Using a text editor, save the code below in a file called `main.tf`: ```hcl provider "aws" { region = "us-east-2" } resource "aws_instance" "MYSQL_TEST" { count = "2" ami = "ami-0000456e99b2b6a9d" instance_type = "t4g.small" security_groups = [aws_security_group.Terraformsecurity1.name] key_name = aws_key_pair.deployer.key_name tags = { Name = "MYSQL_TEST" } } resource "aws_default_vpc" "main" { tags = { Name = "main" } } resource "aws_security_group" "Terraformsecurity1" { name = "Terraformsecurity1" description = "Allow TLS inbound traffic" vpc_id = aws_default_vpc.main.id ingress { description = "TLS from VPC" from_port = 3306 to_port = 3306 protocol = "tcp" cidr_blocks = ["0.0.0.0/0"] } ingress { description = "TLS from VPC" from_port = 22 to_port = 22 protocol = "tcp" cidr_blocks = ["0.0.0.0/0"] } egress { from_port = 0 to_port = 0 protocol = "-1" cidr_blocks = ["0.0.0.0/0"] } tags = { Name = "Terraformsecurity1" } } resource "local_file" "inventory" { depends_on = [aws_instance.MYSQL_TEST] filename = "/tmp/inventory" content = < **Note**: The instance type is t4g.small. This is an Arm-based instance and requires an Arm Linux distribution. The inventory file is automatically generated and does not need to be changed. ## Terraform Commands Use Terraform to deploy the `main.tf` file. ### Initialize Terraform Run `terraform init` to initialize the Terraform deployment. This command downloads the dependencies required for AWS. ``` terraform init ``` The output should be similar to: ``` __output__Initializing the backend... __output__ __output__Initializing provider plugins... __output__- Reusing previous version of hashicorp/aws from the dependency lock file __output__- Reusing previous version of hashicorp/local from the dependency lock file __output__- Using previously-installed hashicorp/aws v4.52.0 __output__- Using previously-installed hashicorp/local v2.3.0 __output__ __output__Terraform has been successfully initialized! __output__ __output__You may now begin working with Terraform. Try running "terraform plan" to see __output__any changes that are required for your infrastructure. All Terraform commands __output__should now work. __output__ __output__If you ever set or change modules or backend configuration for Terraform, __output__rerun this command to reinitialize your working directory. If you forget, other __output__commands will detect it and remind you to do so if necessary. ``` ### Create a Terraform execution plan Run `terraform plan` to create an execution plan. ``` terraform plan ``` A long output of resources to be created will be printed to the console. ### Apply a Terraform execution plan Run `terraform apply` to apply the execution plan and create all AWS resources. ``` terraform apply ``` Answer `yes` to the prompt to confirm you want to create AWS resources. The output should be similar to: ``` __output__Apply complete! Resources: 6 added, 0 changed, 0 destroyed. ``` ## Configure MySQL through Ansible Install MySQL and the required dependencies. Using a text editor, save the code below in a file called `playbook.yaml`. This Playbook installs & enables MySQL in the instances and creates databases inside them. ```yaml --- - hosts: mysql1, mysql2 remote_user: root become: true tasks: - name: Update the Machine and Install dependencies shell: | apt-get update -y apt-get -y install mysql-server apt -y install python3-pip pip3 install PyMySQL - name: start and enable mysql service service: name: mysql state: started enabled: yes - name: Change Root Password shell: sudo mysql -u root -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '{{Your_mysql_password}}'" - name: Create database user with password and all database privileges and 'WITH GRANT OPTION' mysql_user: login_user: root login_password: {{Your_mysql_password}} login_host: localhost name: Local_user host: '%' password: {{Give_any_password}} priv: '*.*:ALL,GRANT' state: present - name: Create a new database with name 'arm_test1' when: "'mysql1' in group_names" community.mysql.mysql_db: name: arm_test1 login_user: root login_password: {{Your_mysql_password}} login_host: localhost state: present login_unix_socket: /run/mysqld/mysqld.sock - name: Create a new database with name 'arm_test2' when: "'mysql2' in group_names" community.mysql.mysql_db: name: arm_test2 login_user: root login_password: {{Your_mysql_password}} login_host: localhost state: present login_unix_socket: /run/mysqld/mysqld.sock - name: MySQL secure installation become: yes expect: command: mysql_secure_installation responses: 'Enter current password for root': '{{Your_mysql_password}}' 'Set root password': 'n' 'Remove anonymous users': 'y' 'Disallow root login remotely': 'n' 'Remove test database': 'y' 'Reload privilege tables now': 'y' timeout: 1 register: secure_mysql failed_when: "'... Failed!' in secure_mysql.stdout_lines" - name: Enable remote login by changing bind-address lineinfile: path: /etc/mysql/mysql.conf.d/mysqld.cnf regexp: '^bind-address' line: 'bind-address = 0.0.0.0' backup: yes notify: - Restart mysql handlers: - name: Restart mysql service: name: mysql state: restarted ``` Replace `{{Your_mysql_password}}` and `{{Give_any_password}}` in this file with your own password. ### Ansible Commands Run the playbook using the `ansible-playbook` command: ``` ansible-playbook playbook.yaml -i /tmp/inventory ``` Answer `yes` when prompted for the SSH connection. Deployment may take a few minutes. The output should be similar to: ``` __output__PLAY [mysql1, mysql2] ****************************************************************************************************************************************** __output__ __output__TASK [Gathering Facts] ***************************************************************************************************************************************** __output__The authenticity of host '18.188.123.253 (18.188.123.253)' can't be established. __output__ED25519 key fingerprint is SHA256:xtVtL9RoEV61xPEnSpiCVf2Up78+L4+uOmGN6kYKqGA. __output__This key is not known by any other names __output__The authenticity of host '3.142.152.108 (3.142.152.108)' can't be established. __output__ED25519 key fingerprint is SHA256:LtdJlI6dJULcrKxaOQaDlbBiFQnNPKN6+3quotP7am4. __output__This key is not known by any other names __output__Are you sure you want to continue connecting (yes/no/[fingerprint])? yes __output__ok: [18.188.123.253] __output__yes __output__ok: [3.142.152.108] __output__ __output__TASK [Update the Machine and Install dependencies] ************************************************************************************************************* __output__changed: [18.188.123.253] __output__changed: [3.142.152.108] __output__ __output__TASK [start and enable mysql service] ************************************************************************************************************************** __output__ok: [3.142.152.108] __output__ok: [18.188.123.253] __output__ __output__TASK [Change Root Password] ************************************************************************************************************************************ __output__changed: [3.142.152.108] __output__changed: [18.188.123.253] __output__ __output__TASK [Create database user with password and all database privileges and 'WITH GRANT OPTION'] ****************************************************************** __output__changed: [3.142.152.108] __output__changed: [18.188.123.253] __output__ __output__TASK [Create a new database with name 'arm_test1'] ************************************************************************************************************* __output__skipping: [3.142.152.108] __output__changed: [18.188.123.253] __output__ __output__TASK [Create a new database with name 'arm_test2'] ************************************************************************************************************* __output__skipping: [18.188.123.253] __output__changed: [3.142.152.108] __output__ __output__TASK [MySQL secure installation] ******************************************************************************************************************************* __output__changed: [18.188.123.253] __output__changed: [3.142.152.108] __output__ __output__TASK [Enable remote login by changing bind-address] ************************************************************************************************************ __output__changed: [3.142.152.108] __output__changed: [18.188.123.253] __output__ __output__RUNNING HANDLER [Restart mysql] ******************************************************************************************************************************** __output__changed: [3.142.152.108] __output__changed: [18.188.123.253] __output__ __output__PLAY RECAP ***************************************************************************************************************************************************** __output__18.188.123.253 : ok=9 changed=7 unreachable=0 failed=0 skipped=1 rescued=0 ignored=0 __output__3.142.152.108 : ok=9 changed=7 unreachable=0 failed=0 skipped=1 rescued=0 ignored=0 ``` ## Connect to Database from local machine To connect to the database, you need the `public-ip` of the instance where MySQL is deployed. You also need to use the MySQL Client to interact with the MySQL database. Run the commands as shown: ``` apt install mysql-client ``` ``` mysql -h {public_ip of instance where Mysql deployed} -P3306 -u {user of database} -p{password of database} ``` Replace `{public_ip of instance where Mysql deployed}`, `{user of database}` and `{password of database}` with your values. In this example, `user`= `Local_user`, which is getting created in the `playbook.yaml` file. The output will be: ``` __output__ubuntu@ip-172-31-38-39:~/mysql$ mysql -h 18.188.123.253 -P3306 -u Local_user -p __output__Enter password: __output__Welcome to the MySQL monitor. Commands end with ; or \g. __output__Your MySQL connection id is 8 __output__Server version: 8.0.32-0ubuntu0.22.04.2 (Ubuntu) __output__ __output__Copyright (c) 2000, 2023, Oracle and/or its affiliates. __output__ __output__Oracle is a registered trademark of Oracle Corporation and/or its __output__affiliates. Other names may be trademarks of their respective __output__owners. __output__ __output__Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. __output__ __output__mysql> ``` ### Access Database and Create Table 1. You can access your database by using the commands as shown: ``` show databases; ``` ``` use {your_database}; ``` The output will be: ``` __output__mysql> show databases; __output__+--------------------+ __output__| Database | __output__+--------------------+ __output__| arm_test1 | __output__| information_schema | __output__| mysql | __output__| performance_schema | __output__| sys | __output__+--------------------+ __output__5 rows in set (0.00 sec) __output__mysql> use arm_test1; __output__Database changed ``` 2. Use the commands below to create a table and insert values into it: ``` create table book(name char(10),id varchar(10)); ``` ``` insert into book(name,id) values ('Abook','10'),('Bbook','20'),('Cbook','20'),('Dbook','30'),('Ebook','45'),('Fbook','40'),('Gbook','69'); ``` The output will be: ``` __output__mysql> create table book(name char(10),id varchar(10)); __output__Query OK, 0 rows affected (0.03 sec) __output__ __output__mysql> insert into book(name,id) values ('Abook','10'),('Bbook','20'),('Cbook','20'),('Dbook','30'),('Ebook','45'),('Fbook','40'),('Gbook','69'); __output__Query OK, 7 rows affected (0.01 sec) __output__Records: 7 Duplicates: 0 Warnings: 0 ``` 3. Use the command below to access the content of the table: ``` select * from {{your_table_name}}; ``` The output will be: ``` __output__mysql> select * from book; __output__+--------+------+ __output__| name | id | __output__+--------+------+ __output__| Abook | 10 | __output__| Bbook | 20 | __output__| Cbook | 20 | __output__| Dbook | 30 | __output__| Ebook | 45 | __output__| Fbook | 40 | __output__| Gbook | 69 | __output__+--------+------+ __output__7 rows in set (0.00 sec) ``` 4. Now connect to the second instance and repeat the above steps with a different database as shown below: The output will be: ``` __output__mysql> show databases; __output__+--------------------+ __output__| Database | __output__+--------------------+ __output__| arm_test2 | __output__| information_schema | __output__| mysql | __output__| performance_schema | __output__| sys | __output__+--------------------+ __output__5 rows in set (0.00 sec) __output__mysql> use arm_test2; __output__Database changed __output__mysql> create table movie(name char(10),id varchar(10)); __output__Query OK, 0 rows affected (0.02 sec) __output__ __output__mysql> insert into movie(name,id) values ('Amovie','1'), ('Bmovie','2'), ('Cmovie','3'), ('Dmovie','4'), ('Emovie','5'), ('Fmovie','6'), ('Gmovie','7'); __output__Query OK, 7 rows affected (0.01 sec) __output__Records: 7 Duplicates: 0 Warnings: 0 __output__ __output__mysql> select * from movie; __output__+--------+------+ __output__| name | id | __output__+--------+------+ __output__| Amovie | 1 | __output__| Bmovie | 2 | __output__| Cmovie | 3 | __output__| Dmovie | 4 | __output__| Emovie | 5 | __output__| Fmovie | 6 | __output__| Gmovie | 7 | __output__+--------+------+ __output__7 rows in set (0.00 sec) ``` ## Deploy Redis as a cache for MySQL using Python You will create two `.py` files on the host machine to deploy Redis as a MySQL cache using Python: `values.py` and `redis_cache.py`. Using a text editor of your choice, create the file `values.py` with the content below: ```python MYSQL_TEST=[["{{public_ip of MYSQL_TEST[0]}}", "arm_test1"], ["{{public_ip of MYSQL_TEST[1]}}", "arm_test2"]] ``` Replace `{{public_ip of MYSQL_TEST[0]}}` & `{{public_ip of MYSQL_TEST[1]}}` with the public IPs generated in the `/tmp/inventory` file after running the Terraform commands. `values.py` is used to store the IP addresses of the instances and the databases created in them. Now create the file `redis_cache.py` with the content below: ```python import sys import MySQLdb import redis from values import * import argparse parser = argparse.ArgumentParser() parser.add_argument("-db", "--database", help="Database") parser.add_argument("-k", "--key", help="Key") parser.add_argument("-q", "--query", help="Query") args = parser.parse_args() R_SERVER = redis.Redis( host='localhost', port='6379') for i in range(0,2): if (MYSQL_TEST[i][1]==args.database): try: conn = MySQLdb.connect (host = MYSQL_TEST[i][0], user = "{{Your_database_user}}", passwd = "{{Your_database_password}}", db = MYSQL_TEST[i][1]) except MySQLdb.Error as e: print ("Error %d: %s" % (e.args[0], e.args[1])) sys.exit (1) sqldata = R_SERVER.get(args.key) if not sqldata: cursor = conn.cursor() cursor.execute(args.query) rows = cursor.fetchall() mystr = ' '.join(map(str,rows)) R_SERVER.set(args.key,mystr,120) print ("Updated redis with MySQL data") print (rows) else: print ("Loaded data from redis") print (sqldata) break else: print("this database doesn't exist") ``` Replace `{{Your_database_user}}` & `{{Your_database_password}}` with the database user and password created through Ansible-Playbook. Also change the `range` in `for loop` according to the number of instances created. `redis_cache.py` is used to access data from Redis Cache and, if not present, store it in the Redis Cache. Install the required Python modules using `pip` and other required dependencies: ``` apt-get install redis libmysqlclient-dev ``` ``` pip install redis mysqlclient ``` To execute the `redis_cache.py` script, run the following command: ``` python3 redis_cache.py -db {database_name} -k {key} -q {query} ``` Replace `{database_name}` with the database you want to access, `{query}` with the query you want to run in the database, and `{key}` with a variable to store the result of the query in Redis cache. When the script is executed for the first time, the data is loaded from the MySQL database and stored in the Redis cache. The output will be similar to: ``` __output__ubuntu@ip-172-31-38-39:~/mysql$ python3 redis_cache.py -db arm_test1 -k AA -q "select * from book limit 3" __output__Updated redis with MySQL data __output__(('Abook', '10'), ('Bbook', '20'), ('Cbook', '20')) ``` ``` __output__ubuntu@ip-172-31-38-39:~/mysql$ python3 redis_cache.py -db arm_test2 -k BB -q "select * from movie limit 3" __output__Updated redis with MySQL data __output__(('Amovie', '1'), ('Bmovie', '2'), ('Cmovie', '3')) ``` When executed after that, it loads the data from Redis cache. In the example above, the information stored in Redis cache is in the form of string. When accessing the information (within the 120-second expiry time), the data is loaded from Redis cache and dumped. The output will be: ``` __output__ubuntu@ip-172-31-38-39:~/mysql$ python3 redis_cache.py -db arm_test1 -k AA -q "select * from book limit 3" __output__Loaded data from redis __output__b"('Abook', '10') ('Bbook', '20') ('Cbook', '20')" ``` ``` __output__ubuntu@ip-172-31-38-39:~/mysql$ python3 redis_cache.py -db arm_test2 -k BB -q "select * from movie limit 3" __output__Loaded data from redis __output__b"('Amovie', '1') ('Bmovie', '2') ('Cmovie', '3')" ``` ### Redis-cli Commands Execute the steps below to verify that the MySQL query is getting stored in Redis cache. 1. Install redis-tools to interact with redis-server. ``` apt install redis-tools ``` 2. Connect to redis-server through redis-cli. ``` redis-cli -p 6379 ``` 3. Retrieve data from Redis cache. ``` get ``` > **Note**: Key is the variable in which you store the data. In the above command, you are storing the data from the tables `book` and `movie` in `AA` and `BB` respectively. The output will be: ``` __output__ubuntu@ip-172-31-38-39:~/mysql$ redis-cli -p 6379 __output__127.0.0.1:6379> get AA __output__"('Abook', '10') ('Bbook', '20') ('Cbook', '20')" __output__127.0.0.1:6379> get BB __output__"('Amovie', '1') ('Bmovie', '2') ('Cmovie', '3')" ``` You have successfully deployed Redis as a cache for MySQL on an AWS Arm based Instance. ### Clean up resources Run `terraform destroy` to delete all resources created. ``` terraform destroy ``` Continue the Learning Path to deploy Redis as a cache for MySQL on an Azure Arm based Instance.