One place for hosting & domains

      How To Reset Your MySQL or MariaDB Root Password on Ubuntu 20.04


      The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      Forgot your database password? It happens to the best of us. If you’ve forgotten or lost the root password to your MySQL or MariaDB database, you can still gain access and reset the password if you have access to the server and a user account with sudo privileges.

      This tutorial demonstrates how to reset the root password for MySQL and MariaDB databases installed with the apt package manager on Ubuntu 20.04. The procedure for changing the root password differs depending on whether you have MySQL or MariaDB installed and the default systemd configuration that ships with the distribution or packages from other vendors. While the instructions in this tutorial may work with other system or database server versions, they have been tested with Ubuntu 20.04 and distribution-supplied packages.

      Note: On fresh Ubuntu 20.04 installations, the default MySQL or MariaDB configuration usually allows you to access the database (with full administrative privileges) without providing a password as long as you make the connection from the system’s root account. In this scenario, it may not be necessary to reset the password. Before you proceed with resetting your database root password, try to access the database with the sudo mysql command. Only if the default configuration for authentication was altered, and this results in an access denied error, follow the steps in this tutorial.

      Prerequisites

      To recover your MySQL or MariaDB root password, you will need:

      Note: Both database installation guides retain the default configuration for the database root account where a password is not needed to authenticate, as long as you can access the system’s root account. You can still follow this guide to set and verify a new password.

      Step 1 — Identifying the Database Version and Stopping the Server

      Ubuntu 20.04 runs either MySQL or MariaDB—a popular drop-in replacement that is fully compatible with MySQL. You’ll need to use different commands to recover the root password depending on which of these you have installed, so follow the steps in this section to determine which database server you’re running.

      Check your version with the following command:

      If you’re running MariaDB, you’ll see “MariaDB” preceded by the version number in the output:

      MariaDB output

      mysql Ver 15.1 Distrib 10.3.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

      You’ll see output like this if you’re running MySQL:

      MySQL output

      mysql Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))

      Note the database you are running. This will determine the appropriate commands to follow in the rest of this tutorial.

      In order to change the root password, you’ll need to shut down the database server. If you’re running MariaDB, you can do so with the following command:

      • sudo systemctl stop mariadb

      For MySQL, shut down the database server by running:

      • sudo systemctl stop mysql

      With the database stopped, you can restart it in safe mode to reset the root password.

      Step 2 — Restarting the Database Server Without Permission Checks

      Running MySQL and MariaDB without permission checking allows accessing the database command line with root privileges without providing a valid password. To do this, you need to stop the database from loading the grant tables, which store user privilege information. Since this is a bit of a security risk, you may also want to disable networking to prevent other clients from connecting to the temporarily vulnerable server.

      Depending on which database server you’ve installed, the way of starting the server without loading the grant tables differs.

      Configuring MariaDB to Start Without Grant Tables

      In order to start the MariaDB server without the grant tables, we’ll use the systemd unit file to set additional parameters for the MariaDB server daemon.

      Execute the following command, which sets the MYSQLD_OPTS environment variable used by MariaDB upon startup. The --skip-grant-tables and --skip-networking options tell MariaDB to start up without loading the grant tables or networking features:

      • sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"

      Then start the MariaDB server:

      • sudo systemctl start mariadb

      This command won’t produce any output, but it will restart the database server, taking into account the new environment variable settings.

      You can ensure it started with sudo systemctl status mariadb.

      Now you should be able to connect to the database as the MariaDB root user without supplying a password:

      You’ll immediately see a database shell prompt:

      Now that you have access to the database server, you can change the root password as shown in Step 3.

      Configuring MySQL to Start Without Grant Tables

      In order to start the MySQL server without its grant tables, you’ll alter the systemd configuration for MySQL to pass additional command-line parameters to the server upon startup.

      To do this, execute the following command:

      • sudo systemctl edit mysql

      This command will open a new file in the nano editor, which you’ll use to edit MySQL’s service overrides. These change the default service parameters for MySQL.

      This file will be empty. Add the following content:

      MySQL service overrides

      [Service]
      ExecStart=
      ExecStart=/usr/sbin/mysqld --skip-grant-tables --skip-networking
      

      The first ExecStart statement clears the default value, while the second one provides systemd with the new startup command, including parameters to disable loading the grant tables and networking capabilities.

      Press CTRL-x to exit the file, then Y to save the changes that you made, then ENTER to confirm the file name.

      Reload the systemd configuration to apply these changes:

      • sudo systemctl daemon-reload

      Now start the MySQL server:

      • sudo systemctl start mysql

      The command will show no output, but the database server will start. The grant tables and networking will not be enabled.

      Connect to the database as the root user:

      You’ll immediately see a database shell prompt:

      Now that you have access to the server, you can change the root password.

      Step 3 — Changing the Root Password

      The database server is now running in a limited mode; the grant tables are not loaded, and there’s no networking support enabled. This lets you access the server without providing a password, but it prohibits you from executing commands that alter data. To reset the root password, you must load the grant tables now that you’ve gained access to the server.

      Tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command:

      You can now change the root password. The method you use depends on whether you are using MariaDB or MySQL.

      Changing the MariaDB Password

      If you are using MariaDB, execute the following statement to set the password for the root account, making sure to replace new_password with a strong new password that you’ll remember:

      • ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

      You’ll see this output indicating that the password changed:

      Output

      Query OK, 0 rows affected (0.001 sec)

      MariaDB allows using custom authentication mechanisms, so execute the following two statements to make sure MariaDB will use its default authentication mechanism for the new password you assigned to the root account:

      • UPDATE mysql.user SET authentication_string = '' WHERE user="root";
      • UPDATE mysql.user SET plugin = '' WHERE user="root";

      You’ll see the following output for each statement:

      Output

      Query OK, 0 rows affected (0.01 sec)

      The password is now changed. Type exit to exit the MariaDB console and proceed to Step 4 to restart the database server in normal mode.

      Changing the MySQL Password

      For MySQL, execute the following statement to change the root user’s password, replacing new_password with a strong password you’ll remember. MySQL allows using custom authentication mechanisms, so the following statement also makes sure that MySQL will use its default authentication mechanism to authenticate the root user using the new password:

      • ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'new_password';

      You’ll see this output indicating the password was changed successfully:

      Output

      Query OK, 0 rows affected (0.01 sec)

      The password is now changed. Exit the MySQL console by typing exit.

      Let’s restart the database in normal operational mode.

      Step 4 — Reverting Your Database Server to Normal Settings

      In order to restart the database server in its normal mode, you have to revert the changes you made so that networking is enabled and the grant tables are loaded. Again, the method you use depends on whether you used MariaDB or MySQL.

      For MariaDB, unset the MYSQLD_OPTS environment variable you set previously:

      • sudo systemctl unset-environment MYSQLD_OPTS

      Then, restart the service using systemctl:

      • sudo systemctl restart mariadb

      For MySQL, remove the modified systemd configuration:

      • sudo systemctl revert mysql

      You’ll see output similar to the following:

      Output

      Removed /etc/systemd/system/mysql.service.d/override.conf. Removed /etc/systemd/system/mysql.service.d.

      Then, reload the systemd configuration to apply the changes:

      • sudo systemctl daemon-reload

      Finally, restart the service:

      • sudo systemctl restart mysql

      The database is now restarted and is back to its normal state. Confirm that the new password works by logging in as the root user with a password:

      You’ll be prompted for a password. Enter your new password, and you’ll gain access to the database prompt as expected.

      Conclusion

      You have restored administrative access to the MySQL or MariaDB server. Make sure the new password you chose is strong and secure, and keep it in a safe place.

      For more information on user management, authentication mechanisms, or ways of resetting database passwords for other versions of MySQL or MariaDB, please refer to the official MySQL documentation or MariaDB documentation.



      Source link

      Erstellen eines neuen Benutzers und Erteilen von Berechtigungen in MySQL


      Einführung

      MySQL ist eine Open-Source Datenbankverwaltungssoftware, die Benutzern beim Speichern, Organisieren und späterem Abrufen von Daten hilft. Sie verfügt über eine Vielzahl von Optionen, um bestimmten Benutzern nuancierte Berechtigungen innerhalb der Tabellen und Datenbanken zu erteilen – dieses Tutorial gibt einen kurzen Überblick über einige der vielen Optionen.

      Was die Hervorhebungen bedeuten

      In diesem Tutorial sind alle von dem Benutzer einzugebenden oder anzupassenden Zeilen hervorgehoben! Der Rest sollte größtenteils kopiert und eingefügt werden können.

      Erstellen eines neuen Benutzers

      In Teil 1 des MySQL Tutorials haben wir alle Bearbeitung in MySQL als root user vorgenommen, der vollen Zugriff auf alle Datenbanken hat. In Fällen, in denen möglicherweise mehr Einschränkungen erforderlich sind, gibt es Möglichkeiten, Benutzer mit benutzerdefinierten Berechtigungen zu erstellen.

      Beginnen wir mit dem Erstellen eines neuen Benutzers innerhalb der MySQL-Shell:

      • CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

      Anmerkung: Wenn wir in diesem Tutorial Benutzer innerhalb der MySQL-Shell hinzufügen, geben wir den Host des Benutzers als localhost und nicht die IP-Adresse des Servers an. localhost ist ein Hostname, der „diesen Computer“ bezeichnet und MySQL behandelt diesen speziellen Hostnamen besonders: Wenn sich ein Benutzer mit diesem Host bei MySQL anmeldet, versucht er, über eine Unix-Socket-Datei eine Verbindung mit dem lokalen Server herzustellen. Daher wird localhost typischerweise verwendet, wenn Sie eine Verbindung per SSH zu Ihrem Server herstellen wollen oder wenn Sie den lokalen mysql-Client ausführen, um eine Verbindung zum lokalen MySQL-Server herzustellen.

      Zu diesem Zeitpunkt hat newuser keine Berechtigungen, mit den Datenbanken etwas zu tun. Tatsächlich ist es so, dass, selbst wenn newuser versucht, sich anzumelden (mit dem Passwort, password), er nicht in der Lage ist, die MySQL-Shell zu erreichen.

      Daher besteht der erste Schritt darin, dem Benutzer Zugriff auf die benötigten Informationen zu verschaffen.

      • GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

      Die Sternchen in diesem Befehl beziehen sich auf die Datenbank bzw. Tabelle, auf die sie zugreifen können – dieser spezifische Befehl ermöglicht dem Benutzer, alle Aufgaben in allen Datenbanken und Tabellen zu lesen, zu bearbeiten, auszuführen und durchzuführen.

      Bitte beachten Sie, dass wir in diesem Beispiel newuser vollen Root-Zugriff auf alles in unserer Datenbank gewähren. Dies ist zwar hilfreich für das Erklären einiger MySQL-Konzepte, ist aber für die meisten Anwendungsfälle unpraktisch, und könnte die Sicherheit Ihrer Datenbank stark gefährden.

      Sobald Sie die Berechtigungen, die Sie für Ihre neuen Benutzer einrichten möchten, endgültig festgelegt haben, sollten Sie immer sicherstellen, dass Sie alle Berechtigungen neu laden.

      Ihre Änderungen werden nun wirksam.

      Gewähren verschiedener Benutzerberechtigungen

      Nachstehend finden Sie eine Liste anderer gängiger möglicher Berechtigungen, die Benutzer haben können.

      • ALL PRIVILEGES- wie wir bereits gesehen haben, würde dies einem MySQL-Benutzer vollen Zugriff auf eine bestimmte Datenbank (oder, wenn keine Datenbank ausgewählt ist, den globalen Zugriff auf das gesamte System) erlauben.
      • CREATE- ermöglicht ihnen, neue Tabellen oder Datenbanken zu erstellen
      • DROP- ermöglicht ihnen, Tabellen oder Datenbanken zu löschen
      • DELETE- ermöglicht ihnen das Löschen von Zeilen aus Tabellen
      • INSERT- ermöglicht ihnen das Einfügen von Zeilen in Tabellen
      • SELECT- ermöglicht ihnen die Verwendung des Befehls SELECT zum Lesen von Datenbanken
      • UPDATE- ermöglicht ihnen das Aktualisieren von Tabellenzeilen
      • GRANT OPTION- ermöglicht ihnen, anderen Benutzern Berechtigungen zu erteilen oder zu entziehen

      Um einem bestimmten Benutzer eine Berechtigung zu erteilen, können Sie dieses Framework verwenden:

      • GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost';

      Wenn Sie ihnen Zugriff auf eine Datenbank oder eine Tabelle gewähren möchten, stellen Sie sicher, dass Sie ein Sternchen (*) an die Stelle des Datenbanks- oder Tabellennamens setzen.

      Stellen Sie bei jedem Aktualisieren oder Ändern einer Berechtigung sicher, dass Sie den Befehl „Flush Privileges“ verwenden.

      Wenn Sie eine Berechtigung entziehen müssen, ist die Struktur fast identisch mit dem Erteilen einer Berechtigung:

      • REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost';

      Beachten Sie, dass die Syntax beim Entziehen von Berechtigungen verlangt, dass Sie FROM verwenden anstelle von TO, wie wir es bei der Erteilung von Berechtigungen verwendet haben.

      Sie können die aktuellen Berechtigungen eines Benutzers überprüfen, indem Sie Folgendes ausführen:

      • SHOW GRANTS FOR 'username'@'localhost';

      Genauso wie Sie Datenbanken mit DROP löschen können, können Sie DROP verwenden, um einen Benutzer vollständig zu löschen:

      • DROP USER 'username'@'localhost';

      Um Ihren neuen Benutzer zu testen, melden Sie sich ab, indem Sie Folgendes eingeben:

      Und melden Sie sich mit diesem Befehl wieder am Terminal an:

      Zusammenfassung

      Nach dem Abschluss dieses Tutorials sollten Sie ein Grundverständnis dafür haben, wie Sie neue Benutzer hinzufügen und ihnen eine Reihe von Berechtigungen in einer MySQL-Datenbank erteilen. Von hier aus können Sie weiter erkunden und mit verschiedenen Berechtigungseinstellungen für Ihre Datenbank experimentieren Sie möchten möglicherweise mehr über einige MySQL-Konfigurationen auf höherer Ebene erfahren.

      Für weitere Informationen über die Grundlagen von MySQL empfehlen wir Ihnen, sich die folgenden Tutorials anzusehen:



      Source link

      How To Set Up Redis as a Cache for MySQL with PHP on Ubuntu 20.04


      The author selected Girls Who Code to receive a donation as part of the Write for DOnations program.

      Introduction

      Redis (Remote Dictionary Server) is a fast open-source, in-memory database that you can use as a key-value store for a highly scalable and performance-oriented system. Some of Redis’ use cases include: caching, high-speed transactions, real-time analytics, live notifications, machine learning, searching, and queue/job processing. Since Redis is an in-memory key-value store, its performance makes it suitable for caching data in your application.

      Caching is storing data temporarily in a high-speed storage layer (for example, in a computer RAM) to serve data faster when clients make the same future requests. This enhances the re-use of previously computed data instead of fetching it each time from the disk.

      When you’re working with PHP and MySQL, using Redis as a cache improves your application performance because Redis stores data in RAM, which is several times faster than a hard disk (HDD) or a solid-state drive (SSD). Caching also reduces database costs—that is, the number of round trips made to the back-end database—and avoids overloading the backend.

      Caching data is an integral design feature when you’re designing web applications with higher reads than writes. Such applications include blogs, online stores, and social media sites.

      In this tutorial, you’ll use Redis to cache MySQL data with PHP on Ubuntu 20.04.

      Prerequisites

      To complete this tutorial, you’ll need the following:

      Step 1 — Installing the Redis Library for PHP

      To begin you’ll install the php-redis extension, which will allow you to use PHP to communicate with Redis. Run the following commands to update your server and install the extension:

      • sudo apt update
      • sudo apt install php-redis

      Confirm the installation and restart the Apache web server to load the extension:

      • sudo systemctl restart apache2

      Now that you have installed your dependencies, you’ll set up your database.

      Step 2 — Setting Up a Test Database, Table, and Sample Data

      In this step, you’ll create a MySQL database to store data permanently to disk. You’ll also create some tables and a user account with full privileges to the database.

      First, log in to your MySQL server as a root user:

      Enter the root password of your MySQL server that you set up in the LAMP prerequisite. Then, press ENTER to continue.

      Next, create a test_store database with the following command:

      • CREATE database test_store;

      Make sure the action is successful by confirming the output:

      Output

      Query OK, 1 row affected (0.00 sec)

      Next, create a user for your database. We’ll call this user test_user in this tutorial. Replace PASSWORD with a strong password as well:

      • CREATE USER 'test_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

      Then grant test_user full privileges to the test_store database with:

      • GRANT ALL PRIVILEGES ON test_store.* TO 'test_user'@'localhost';

      Finally run the following command to reload the grant tables in MySQL:

      Ensure you get the following output after each successful command:

      Output

      Query OK, 0 rows affected (0.01 sec)

      End the MySQL root session:

      You’ll receive the word Bye and the system will take you back to the server’s command line interface.

      Log back in to the MySQL server with the credentials for the test_user that you just created:

      Enter the password for the test_user to proceed. Then, switch to the test_store database when you’re in the mysql> prompt:

      Ensure you receive the following output:

      Output

      Database Changed.

      Next, you’ll create a products table with three columns. You’ll use the product_id column to uniquely identify each product. To avoid assigning the IDs manually, you’ll use the AUTO_INCREMENT keyword. Then, you’ll use the BIGINT data type for the product_id column to support a large data set. The BIGINT data type can hold a minimum value of -2^63 and a maximum value of 2^63 - 1.

      The product_name field will hold the actual names of your items. In this case, a VARCHAR data type with a length of 50 characters will be enough. The last column in the products table is the price—you’ll use the DOUBLE data type to accommodate prices with decimals (for example, 16.33).

      To create the products table, run the following command:

      • CREATE table products
      • (
      • product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
      • product_name VARCHAR(50),
      • price DOUBLE
      • ) Engine = InnoDB;

      You will receive the following output:

      Output

      Query OK, 0 rows affected (0.01 sec)

      Now you’ll populate the products table with some records for testing purposes.

      You don’t need to enter data to the product_id column manually since the AUTO_INCREMENT column will complete this. Run the following commands one by one:

      • INSERT INTO products(product_name, price) VALUES ('Virtual Private Servers', '5.00');
      • INSERT INTO products(product_name, price) VALUES ('Managed Databases', '15.00');
      • INSERT INTO products(product_name, price) VALUES ('Block Storage', '10.00');
      • INSERT INTO products(product_name, price) VALUES ('Managed Kubernetes', '60.00');
      • INSERT INTO products(product_name, price) VALUES ('Load Balancer', '10.00');

      After running each command, ensure you get this output:

      Output

      Query OK, 1 row affected (0.00 sec)

      Verify the data using the SELECT command:

      You will receive output similar to the following:

      Output

      +------------+-------------------------+-------+ | product_id | product_name | price | +------------+-------------------------+-------+ | 1 | Virtual Private Servers | 5 | | 2 | Managed Databases | 15 | | 3 | Block Storage | 10 | | 4 | Managed Kubernetes | 60 | | 5 | Load Balancer | 10 | +------------+-------------------------+-------+ 5 rows in set (0.00 sec)

      End the MySQL session for the test_user:

      Once you’ve set up the test_store database, products table, and test_user, you’ll code a PHP script to retrieve data from the MySQL database and cache it to Redis.

      Step 3 — Designing a PHP Script for Fetching and Caching MySQL Data

      In this step, you’ll create a PHP script for retrieving the sample data that you’ve created in the previous step.

      When you run the script for the first time, it will read the data from MySQL (that is, from disk) and then cache it to Redis. As a result subsequent reads of the products’ data will be from Redis (that is, from system RAM). System memory is multiple times faster than even the fastest solid-state drive, thus data will be retrieved faster from the Redis cache than reading from the system disk.

      Note: While you might not get any performance boost, since you are retrieving just a few records from the MySQL database, several benchmarks prove that retrieving cached data from Redis is several times faster than reading it from MySQL when dealing with several hundred thousand records.

      Create a products.php file in the root directory of your website:

      • sudo nano /var/www/html/products.php

      To start, enter the following information to connect and create an instance of Redis and store it as an object in a $redis variable.

      The address 127.0.0.1 connects to the localhost. You may change this value if you’re running Redis from a remote server. Remember to replace REDIS_PASSWORD with the specific password for Redis set in the /etc/redis/redis.conf configuration file.

      Also, enter the appropriate port number. By default, Redis runs on port 6379:

      /var/www/html/products.php

      <?php
      
      $redis = new Redis();
      $redis->connect('127.0.0.1', 6379);
      $redis->auth('REDIS_PASSWORD');
      

      Note: In this guide, the $redis->auth('REDIS_PASSWORD') command sends your password to Redis in plain text. In a production environment, you may consider securing end-to-end communication between Redis and the client server running PHP code with a more powerful access control layer, such as TLS (Transport Layer Security). Also, when configuring your Redis password in the /etc/redis/redis.conf file, make sure you set a long and strong value to prevent brute-force attacks.

      The next step is initializing a PHP variable you’ll use as a key in Redis.

      As mentioned earlier in this guide, Redis acts as a key-value database and therefore you must have a unique key for the data that you intend to store and retrieve from it.

      So, define a PRODUCTS key by adding the following information to the /var/www/html/products.php file. You are free to use any name in place of PRODUCTS key.

      Your PHP script will use this key to cache information to Redis once data gets retrieved from the MySQL database:

      /var/www/html/products.php

      ...
      $key = 'PRODUCTS';
      

      Next, include a conditional PHP if...else statement to check if the PRODUCTS key exists in Redis:

      /var/www/html/products.php

      ...
      if (!$redis->get($key)) {
          $source="MySQL Server";
          $database_name="test_store";
          $database_user="test_user";
          $database_password = 'PASSWORD';
          $mysql_host="localhost";
      
          $pdo = new PDO('mysql:host=" . $mysql_host . "; dbname=" . $database_name, $database_user, $database_password);
          $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      
          $sql  = "SELECT * FROM products";
          $stmt = $pdo->prepare($sql);
          $stmt->execute();
      
          while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
             $products[] = $row;
          }
      
          $redis->set($key, serialize($products));
          $redis->expire($key, 10);
      
      } else {
           $source = "Redis Server';
           $products = unserialize($redis->get($key));
      
      }
      
      echo $source . ': <br>';
      print_r($products);
      

      If the key doesn’t exist in Redis, the script connects to the database that you created earlier, queries the products table, and stores the data in Redis using the $redis->set($key, serialize($products)) command.

      The $redis->expire($key, 10); command sets the expiration to 10 seconds. You may tweak this value depending on your cache policy.

      The $source variable helps you to identify the source of the data once it is echoed as an array at the end of the script using the echo $source and print_r($products) commands.

      Once you’ve put everything together, your /var/www/html/products.php file will be as follows:

      /var/www/html/products.php

      <?php
      
      $redis = new Redis();
      $redis->connect('127.0.0.1', 6379);
      $redis->auth('REDIS_PASSWORD');
      
      $key = 'PRODUCTS';
      
      if (!$redis->get($key)) {
          $source="MySQL Server";
          $database_name="test_store";
          $database_user="test_user";
          $database_password = 'PASSWORD';
          $mysql_host="localhost";
      
          $pdo = new PDO('mysql:host=" . $mysql_host . "; dbname=" . $database_name, $database_user, $database_password);
          $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      
          $sql  = "SELECT * FROM products";
          $stmt = $pdo->prepare($sql);
          $stmt->execute();
      
          while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
             $products[] = $row;
          }
      
          $redis->set($key, serialize($products));
          $redis->expire($key, 10);
      
      } else {
           $source = "Redis Server';
           $products = unserialize($redis->get($key));
      
      }
      
      echo $source . ': <br>';
      print_r($products);
      
      

      Save and close the file.

      You’ve now set up a PHP script that will connect to MySQL and cache data to Redis. You’ll test your script in the next step.

      Step 4 — Testing the PHP Script

      To test if Redis is caching data from the MySQL database, you’ll enter the path of the PHP script in a browser window. Remember to replace your_server_IP with the public IP address of your server, like so: http://your_server_IP/products.php.

      When you run the script for the first time, you will receive the following output that displays data from the MySQL database because, at this point, the PHP script has not yet cached any data in Redis:

      MySQL Server
      Array ( [0] => Array ( [product_id] => 1 [product_name] => Virtual Private Servers [price] => 5 ) [1] => Array ( [product_id] => 2 [product_name] => Managed Databases [price] => 15 ) [2] => Array ( [product_id] => 3 [product_name] => Block Storage [price] => 10 ) [3] => Array ( [product_id] => 4 [product_name] => Managed Kubernetes [price] => 60 ) [4] => Array ( [product_id] => 5 [product_name] => Load Balancer [price] => 10 ) )
      

      Once you run the script again, you’ll get an output confirming that it’s reading data from Redis, which is acting as a cache for MySQL.

      Redis Server
      Array ( [0] => Array ( [product_id] => 1 [product_name] => Virtual Private Servers [price] => 5 ) [1] => Array ( [product_id] => 2 [product_name] => Managed Databases [price] => 15 ) [2] => Array ( [product_id] => 3 [product_name] => Block Storage [price] => 10 ) [3] => Array ( [product_id] => 4 [product_name] => Managed Kubernetes [price] => 60 ) [4] => Array ( [product_id] => 5 [product_name] => Load Balancer [price] => 10 ) )
      

      Remember that the key will expire after 10 seconds and data will again be retrieved from MySQL.

      Conclusion

      In this guide, you’ve used Redis to cache MySQL data with PHP on Ubuntu 20.04. You may use the coding in this guide to set up a caching mechanism for your MySQL data, which is especially useful for high-traffic web applications.

      You can check out our Redis topic page for more educational resources. Or, learn more about coding in PHP with further tutorials and content on the PHP topic page.



      Source link