SQL Database
Hosting

Duplicate entries may inadvertently accumulate in databases, which are crucial for storing vast amounts of structured data. These duplicates could show up for a number of reasons, including system errors, data migration mistakes, or repeated user submissions. A database with duplicate entries may experience irregularities, sluggish performance, and erroneous reporting. Using the GROUP BY and HAVING clauses, as well as a different strategy that makes use of temporary tables, we will discuss two efficient methods for locating and removing duplicate rows in MySQL. With these techniques, you can be sure that your data will always be accurate, clean, and well-organized. Database duplication in MySQL tables can clog your data, resulting in inaccurate analytics and needless storage. Locating and eliminating them is a crucial database upkeep task. This is a detailed guide on how to identify and remove duplicate rows. If two or more columns in a row have identical values, it is called a duplicate row. For instance, rows that have the same values in both the userName and userEmail columns of a userDetails table may be considered duplicates. Benefits of Removing Duplicate Data The advantage of eliminating duplicate data is that duplicate entries can slow down query performance, take up extra storage space, and produce misleading results in reports and analytics. The accuracy and speed of data processing are improved by keeping databases clean, which is particularly crucial for databases that are used for critical applications or are expanding. Requirements Prior to starting, make sure you have access to a MySQL database or have MySQL installed on your computer. The fundamentals of general database concepts and SQL queries. One can execute SQL commands by having access to a MySQL client or command-line interface. To gain practical experience, you can create a sample database and table that contains duplicate records so that you can test and comprehend the techniques for eliminating them. Creating a Test Database Launch the MySQL command-line tool to create a Test Database. mysql -u your_username -p Create a new database called test_dev_db after entering your MySQL credentials. CREATE DATABASE test_dev_db; Then, switch to this newly created database:. USE test_dev_db; Add several rows, including duplicates, to the userDetails table after creating it with the CREATE TABLE query and INSERT query below. CREATE TABLE userDetails ( userId INT AUTO_INCREMENT PRIMARY KEY, userName VARCHAR(100), userEmail VARCHAR(100) ); INSERT INTO userDetails (userName, userEmail) VALUES (‘Alisha’, ‘[email protected]’), (‘Bobita, ‘[email protected]’), (‘Alisha’, ‘[email protected]’), (‘Alisha’, ‘[email protected]’); Using GROUP BY and HAVING to Locate Duplicates Grouping rows according to duplicate-defining columns and using HAVING to filter groups with more than one record is the simplest method for finding duplicates. Now that you have duplicate data, you can use SQL to determine which rows contain duplicate entries. MySQL's GROUP BY and HAVING clauses make this process easier by enabling you to count instances of each distinct value. An example of a table structure is the userDetails table, which contains the columns userId, userName, and userEmail. The GROUP BY clause is useful for counting occurrences and identifying duplicates because it groups records according to specified column values. The HAVING clause  allows duplicate entries in groups formed by GROUP BY to be found by combining groups based on specific criteria. Table userDetails Structure userId userName userEmail 1 Alisha  [email protected] 2 Bobita  [email protected] 3 Alisha  [email protected] 4 Alisha  [email protected] In the above table userDetails, records with identical userName and userEmail values are considered duplicates. Finding Duplicates Query for find the duplicate entries: SELECT userName, userEmail, COUNT(*) as count FROM userDetails GROUP BY userName, userEmail HAVING count > 1; Rows are grouped by userName and userEmail in the aforementioned query, which also counts entries within the group and eliminates groups with a single entry (no duplicates). Explanation: SELECT userName, userEmail, COUNT(*) as count: Retrieves the count of each combination of username and userEmail, as well as their unique values. GROUP BY userName, userEmail: Records are grouped by username and user email using the GROUP BY userName, userEmail function COUNT (*): Tallies the rows in each set. HAVING occurrences > 1: Recurring entries are identified by displaying only groups with more than one record. This query will return groups of duplicate records based on the selected columns. userName userEmail count Alisha [email protected] 3 Eliminating Duplicate Rows After finding duplicates, you may need to eliminate some records while keeping the unique ones. Joining the table to itself and removing rows with higher userId values is one effective method that preserves the lowest userId for every duplicate. Use the SQL query to remove duplicate rows while keeping the lowest userId entry. DELETE u1 FROM userDetails u1 JOIN userDetails u2 ON u1. userName = u2. userName AND u1. userEmail = u2. userEmail AND u1. userId > u2. userId ; Explanation: u1 & u2: Aliases for the userDetails table to ease a self-join. ON u1. userName = u2. userName AND u1. userEmail = u2. userEmail: Matches rows with identical userName, userEmail. AND u1. userId > u2. userId: Removes rows with higher userId values, keeping only the row with the smallest userId. Because this action cannot be undone, it is advised that you backup your data before beginning the deletion procedure. Confirming Duplicate Removal To confirm that all duplicates have been removed, repeat the Step 1 identification query. SELECT userName, userEmail, COUNT(*) as count FROM userDetails GROUP BY userName, userEmail HAVING count > 1; All duplicates have been successfully eliminated if this query yields no rows. Benefits of Employing GROUP BY and HAVING The GROUP BY and HAVING clauses serve as vital instruments for the aggregation of data and the filtration of grouped outcomes. These functionalities are especially useful for detecting and handling duplicate entries or for condensing extensive datasets. Below are the primary benefits of employing these clauses. Efficient Identification of Duplicates Data Aggregation and Summarization Filtering Aggregated Results with Precision Versatility Across Multiple Scenarios Compatibility and Simplicity Enhanced Query Readability Support for Complex Aggregations The GROUP BY and HAVING clauses serve as essential instruments for data aggregation, identifying duplicates, and filtering results. Their effectiveness, ease of use, and adaptability render them crucial for database management and data analysis activities, allowing users to derive insights and handle data proficiently across a variety of applications. Identifying Duplicates Using a Temporary Table When dealing with large datasets, it can be easier and more efficient to separate duplicates using a temporary table before deleting them. Creating the Table Make a temporary table to store duplicate groups according to predetermined standards (e.g. A. username, along with userEmail. CREATE TEMPORARY TABLE temp_view_duplicates AS SELECT username, userEmail, MIN (userId) AS minuid FROM userDetails GROUP BY username, userEmail, HAVING COUNT(*) > 1; Explanation: CREATE TEMPORARY TABLE temp_view_duplicates AS: Creates a temporary table named temp_view_duplicates. SELECT userName, userEmail, MIN(userId) AS minuid: Groups duplicates by userName and userEmail, keeping only the row with the smallest userId. GROUP BY userName, userEmail: Groups rows by userName, userEmail. HAVING COUNT(*) > 1: Filters only groups with more than one row, identifying duplicates. This temporary table will now contain one representative row per duplicate group (the row with the smallest id). Deleting Duplicates from the Main Table Now that we have a list of unique rows with duplicates in the temp_view_duplicates table, we can use the temporary table to remove duplicates while keeping only the rows with the smallest userId. Use the following DELETE command: DELETE FROM userDetails WHERE (username, userEmail) IN ( SELECT username, userEmail FROM temp_view_duplicates ) AND userId NOT IN ( SELECT minuid FROM temp_view_duplicates ); Explanation: WHERE (username, userEmail,) IN: Targets only duplicate groups identified in temp_view_duplicates. AND userId NOT IN (SELECT minuid FROM temp_view_duplicates): Ensures that only duplicate rows (those with higher userId values) are deleted. Verifying Results To confirm that duplicates have been removed, query the userDetails table: SELECT * FROM userDetails; Only unique rows should remain. Temporary tables (CREATE TEMPORARY TABLE) are automatically dropped when the session ends, so they don’t persist beyond the current session. When making extensive deletions, think about utilizing a transaction to safely commit or undo changes as necessary. Key Advantages of Using a Temporary Table Lower Complexity: By isolating duplicates, the removal process is simpler and clearer. Enhanced Efficiency: It's faster for large datasets, as it avoids repeated joins. Improved Readability: Using a temporary table makes the process more modular and easier to understand. Conclusion Eliminating duplicate records is essential for maintaining a well-organized database, improving performance, and ensuring accurate reporting. This guide presented two approaches: Direct Method with GROUP BY and HAVING Clauses: Ideal for small datasets, using self-joins to delete duplicates. Temporary Table Approach: More efficient for larger datasets, leveraging temporary storage to streamline deletion. Choose the method that best fits your data size and complexity to keep your database clean and efficient.

MariaDB is an open-source relational database management system, which has made it a popular alternative to MySQL. It is often included in LAMP stacks (which consist of Linux, Apache, MySQL, and PHP, sometimes with Python and Perl compilers). This document provides a brief guide to setting up MariaDB. The setup involves three steps: updating the package index, installing the mariadb-server, and activating a security script restricting unauthorized access to the remote host.   The sequence of commands looks like this: sudo apt update sudo apt install mariadb-server sudo mysql_secure_installation For this tutorial, we will use a Hostman cloud server with Ubuntu 22.04 installed. Before diving into this guide, we recommend performing initial setup steps, such as creating a user account with sudo privileges and setting basic UFW firewall rules. Step 1: Installing MariaDB Repositories are regularly updated to include stable versions of utilities. Distributions, on the other hand, include outdated releases that were current at the time of the build, so they need to be updated to avoid compatibility issues. This procedure is executed with the following command: sudo apt update Now we can install the package we need: sudo apt install mariadb-server The installation proceeds without prompting for a password or making any other configuration changes. Using MariaDB in this form on the server is not advisable, as it will operate in an insecure mode. To rectify this situation, we will apply the mysql_secure_installation script that comes with the software. This script will restrict access to the server and eliminate unauthorized accounts. Step 2: Setting Up MariaDB The security script modifies the insecure options that are set by default. For instance, it creates test accounts and allows remote connections using the root account. This potentially poses a risk of hacking and unauthorized access to the information stored in the created database. To run the script, use the following command: sudo mysql_secure_installation This command will initiate a series of prompts that allow you to change the database's security parameters.  The first prompt relates to the root account, and the system will request the password for the active database. Press Enter. This indicates that there is currently no protection. Enter current password for root (enter for none): Switch to unix_socket authentication [Y/n] Enter n and press Enter Change the root password? [Y/n] Enter Y and press Enter. New password: Re-enter new password: Enter and re-enter the new root user password and press Enter. Answer Yes (Y) to all the following prompts. Remove anonymous users? [Y/n] Disallow root login remotely? [Y/n] Remove test database and access to it? [Y/n] Reload privilege tables now? [Y/n] The system will remove the test database and anonymous accounts, disable access through the root account, and load the updated rules.  The installation and configuration of the MariaDB package are complete, and you can now begin using it. Alternatively, you can proceed to an optional step to create an administrator account to enable password access. Step 3: Create an Administrator Account By default, on Ubuntu, MariaDB is installed using the unix_socket plugin, which means that password prompts do not apply. In most cases, this approach provides a high level of security. However, it also complicates administrative tasks, such as those done through phpMyAdmin. When starting or stopping the server or managing logs, the root account is used. That is why we did not change its data. However, during a package update, these settings may change on their own, so it's better to enable password authentication right away. As an example, we will create an account named admin and give it the same privileges as the root account. First, open the MariaDB command line: sudo mariadb Next, create the new user: GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION; Replace admin and password with any preferred combinations.  After creating the account, flush the privileges while keeping the settings in the current session: FLUSH PRIVILEGES; Now you can close the shell: exit; Next, you should test MariaDB to ensure that the settings are correct. Step 4: Diagnostics When the MariaDB is installed from the official repository, it automatically configures the settings to ensure that the MariaDB module starts automatically. However, it's still a good practice to manually check its status: sudo systemctl status mariadb The output on the screen will look something like this: If the utility is not running, you will need to start it manually and also enable the service: sudo systemctl enable mariadb sudo systemctl start mariadb After forcibly starting the service, you can make a test connection to the database using mysqladmin. It allows you to interact with the database with administrative rights, execute commands, and change settings. Here’s an example of connecting and displaying the version number: sudo mysqladmin version The output on the screen will look like this: If access was configured using the administrator password, you can use the command: mysqladmin -u admin -p version The current version output confirms that the database is running and functioning, and that the user has access to its contents. Conclusions We have completed an overview of the installation and configuration for the MariaDB database management system. We discussed methods to protect against unauthorized access to the database and the creation of a new user who will have access to information equal to that of the root user.

Calculating age from a date of birth (DOB) is a common requirement in many applications. In MySQL, this can be efficiently achieved using the TIMESTAMPDIFF function. This tutorial will guide you through the process of calculating age using TIMESTAMPDIFF, handling edge cases, and integrating the query into applications. Prerequisites Before diving into the tutorial, ensure you have: A MySQL database set up and accessible. Basic knowledge of SQL queries and MySQL functions. A table with a date of birth column to work with. Overview of TIMESTAMPDIFF Function The TIMESTAMPDIFF function calculates the difference between two dates based on the specified unit of time (e.g., years, months, days). For calculating age, you will use TIMESTAMPDIFF to find the difference in years between the current date and the date of birth. TIMESTAMPDIFF(unit, datetime1, datetime2) unit: The unit of time for the result (e.g., YEAR, MONTH, DAY). datetime1: The first date (usually the date of birth). datetime2: The second date (usually the current date). Writing the Basic TIMESTAMPDIFF Query To calculate age from a date of birth, use the following query: SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS ageFROM users; In this query: YEAR specifies that the result should be in years. date_of_birth is the column containing the date of birth. CURDATE() returns the current date. Handling Edge Cases When calculating age, consider the following edge cases: Leap Years Leap years do not significantly affect age calculations, as TIMESTAMPDIFF accurately accounts for these in its calculations. Birthdays on February 29 For individuals born on February 29, TIMESTAMPDIFF will handle their age calculation correctly, but be aware of potential issues if you use functions that do not recognize leap years. Different Date Formats Ensure that the date format stored in the database matches MySQL's date format (YYYY-MM-DD). If you encounter format issues, use the STR_TO_DATE function to convert strings to date formats. Practical Examples and Use Cases Here are some practical examples of using TIMESTAMPDIFF: Example 1: Calculate Age for a Specific User SELECT TIMESTAMPDIFF(YEAR, '1990-05-15', CURDATE()) AS age; This query calculates the age of someone born on May 15, 1990. Example 2: Age Calculation for All Users SELECT name, TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS ageFROM users; This query retrieves names and ages of all users from the users table. Integrating the Query in Applications To integrate this query into an application: In a PHP Application: $query = "SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age FROM users";$result = mysqli_query($conn, $query); In a Python Application:  query = "SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age FROM users"  cursor.execute(query) Ensure that your application handles database connections securely and efficiently. Performance Considerations The TIMESTAMPDIFF function is optimized for performance, but be mindful of the following: Indexes: Ensure that the date_of_birth column is indexed to speed up queries. Query Optimization: For large datasets, consider optimizing queries to improve performance. Troubleshooting Common Issues Here are some common issues and their solutions: Incorrect Results Issue: Age calculation is incorrect. Solution: Ensure that dates are correctly formatted and the date_of_birth column contains valid date values. Query Errors Issue: Syntax or execution errors. Solution: Verify that the SQL syntax is correct and that you are using valid MySQL functions. Conclusion Calculating age from a date of birth using TIMESTAMPDIFF in MySQL is straightforward and efficient. By following the steps outlined in this tutorial, you can accurately determine age and handle various edge cases. Integrate these calculations into your applications and optimize performance for the best results.

phpMyAdmin is a specialized utility written in PHP that provides a graphical interface for managing MySQL databases via a browser. In addition to displaying tables visually, phpMyAdmin simplifies database management, allowing the creation of SQL queries through a control panel without directly writing commands or code. It implements the full functionality of SQL queries: viewing, adding, deleting, and modifying databases, as well as their tables, fields, and indexes. In this guide, we will install phpMyAdmin and all its dependencies on a remote host. The technological stack within which phpMyAdmin will work is as follows: MySQL database Nginx web server PHP interpreter Note that this guide uses the Nginx web server instead of the more common Apache for PHP. Therefore, the overall toolset demonstrated in this instruction looks like this: PHP + phpMyAdmin + Nginx + MySQL. Thus, Nginx handles user requests and redirects them to PHP via the FastCGI protocol. The PHP interpreter processes phpMyAdmin scripts, which "communicate" with and manage the MySQL database. Prerequisites To install and configure phpMyAdmin, you will need: A cloud server running Ubuntu 22.04.  Below we will describe how to deploy an Ubuntu server on Hostman. Step 1: Preparing the System Configuring the Cloud Server To create a cloud server on Hostman, log into the control panel and go to Cloud Servers in the left menu. Next, click Create server. The most important thing is to choose Ubuntu 22.04. You can customize the rest of the parameters as you wish. After completing the configuration, click Order. In a couple of minutes the server will be online and you'll be able to connect to it remotely via SSH, using the command: ssh root@IP For example: ssh [email protected] You can copy the command from your server's Dashboard. In our case, root is the default username for the Hostman cloud server. After entering the command, the terminal will prompt for the root password, which you also find on the Dashboard.  Updating the System Before installing the necessary components, update the list of available repositories: sudo apt update And update the packages already installed on the system: sudo apt upgrade Step 2: Installing MySQL First, install MySQL on your Ubuntu server: sudo apt install mysql-server -y Ensure the MySQL service is running: systemctl status mysql If it is, the console output will include the following status: Active: active (running) You can also check the MySQL version: mysql --version The console will display something like this: mysql  Ver 8.0.36-0 ubuntu 0.22.04.1 for Linux on x86_64 ((Ubuntu)) Note that this guide uses MySQL version 8.0.36. Next, run a special security script to configure MySQL: sudo mysql_secure_installation Log into MySQL to set a password for the root user: mysql Check the list of existing users: SELECT User, Host FROM mysql.user; The console will display a list of usernames and hosts: +------------------+-----------+| User             | Host      |+------------------+-----------+| debian-sys-maint | localhost || mysql.infoschema | localhost || mysql.session    | localhost || mysql.sys        | localhost || root             | localhost |+------------------+-----------+5 rows in set (0.00 sec) We are interested in the root user, so execute the following command for it: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'my_pass_123'; Replace my_pass_123 with a strong password. Exit MySQL: exit In the future, to log into MySQL, use the following command: mysql -u root -p The console will always prompt for the password that you just set. Efficient and scalable Virtual Servers Step 3: Installing Nginx In this example, we will use the Nginx web server as an alternative to Apache. Install it via the APT package manager: sudo apt install nginx -y After installation, check the status of the Nginx service: systemctl status nginx After installing, Nginx starts automatically, so the console output should show the status: Active: active (running) Check the Nginx version: nginx -v The console will display: nginx version: nginx/1.18.0 (Ubuntu) So, in this tutorial, phpMyAdmin will run on Nginx version 1.18.0. Now you can enter your server's address in the browser to ensure that Nginx responds to user HTTP requests. In our case: http://166.1.227.252 The browser should open the standard Nginx welcome page. After we install phpMyAdmin, it will be available at http://server-ip/phpmyadmin. Step 4: Installing PHP For phpMyAdmin to work, you need to install PHP on your Ubuntu server. We will download it from an independent software provider's repository, so first add a new remote repository to the APT package manager: sudo add-apt-repository ppa:ondrej/php Then install the FastCGI Process Manager (FPM) and an additional module for working with MySQL: sudo apt install php8.3-fpm php8.3-mysql -y To check that the installation was successful, query the PHP version: php -v The console will display an output similar to this: PHP 8.3.4 (cli) (built: Mar 16 2024 08:40:08) (NTS)Copyright (c) The PHP GroupZend Engine v4.3.4, Copyright (c) Zend Technologies    with Zend OPcache v8.3.4, Copyright (c), by Zend Technologies As you can see, this guide uses PHP version 8.3.4. Step 5: Installing phpMyAdmin To install phpMyAdmin on Ubuntu, download it from the official website. Select the latest version of the program in a .tar.gz archive, then copy the download link and use it in the wget command: wget -c https://files.phpmyadmin.net/phpMyAdmin/5.2.1/phpMyAdmin-5.2.1-english.tar.gz Next, unpack the downloaded archive: tar -xzvf phpMyAdmin-5.2.1-english.tar.gz In our case, we are using phpMyAdmin version 5.2.1. You can delete the archive itself: rm phpMyAdmin-5.2.1-english.tar.gz Move the unpacked directory to a separate directory specifically for phpMyAdmin: sudo mv phpMyAdmin-5.2.1-english /usr/share/phpmyadmin Create a symbolic link associated with the local host's web directory: ln -s /usr/share/phpmyadmin /var/www/html Now proceed to configure the Nginx web server. Step 6: Configuring Nginx Make changes to the web server's main configuration file to allow Nginx to open phpMyAdmin's root web page. sudo nano /etc/nginx/sites-available/default First, find the line defining the root page index parameter. By default, it looks like this: index index.html index.htm index.nginx-debian.html; To allow Nginx to process PHP files, add index.php so that the final line looks like this: index index.php index.html index.htm index.nginx-debian.html; Next, make changes to the FastCGI protocol handler so that Nginx can forward all requests to PHP files directly to the PHP interpreter. Find the root request handler code, which by default looks like this: location / {    # First attempt to serve request as file, then    # as directory, then fall back to displaying a 404.    try_files $uri $uri/ =404;} Add the PHP file request handler code right after it: location ~ \.php$ {    try_files $fastcgi_script_name =404;    include fastcgi_params;    fastcgi_pass unix:/run/php/php8.3-fpm.sock;    fastcgi_index index.php;    fastcgi_param DOCUMENT_ROOT $realpath_root;    fastcgi_param SCRIPT_FILENAME $realpath_root$fastcgi_script_name;} Overall, the file content should look like this: server { listen 80 default_server; listen [::]:80 default_server; root /var/www/html; index index.php index.html index.htm index.nginx-debian.html; server_name _; location / { try_files $uri $uri/ =404; } location ~ \.php$ { try_files $fastcgi_script_name =404; include fastcgi_params; fastcgi_pass unix:/run/php/php8.3-fpm.sock; fastcgi_index index.php; fastcgi_param DOCUMENT_ROOT $realpath_root; fastcgi_param SCRIPT_FILENAME $realpath_root$fastcgi_script_name; } } Check the Nginx configuration: nginx -t If the syntax is correct, the console will display: nginx: the configuration file /etc/nginx/nginx.conf syntax is oknginx: configuration file /etc/nginx/nginx.conf test is successful Restart the Nginx web server to apply all changes: sudo systemctl restart nginx At this point, all the necessary dependencies are installed, and you can access phpMyAdmin by going to: http://server-ip/phpmyadmin For example: http://166.1.227.252/phpmyadmin The main phpMyAdmin page should open in the browser. You can login to phpMyAdmin using the username and password of the MySQL user created to access the control panel and start managing databases. Hostman Cloud Databases As an alternative to deploying databases and management tools, Hostman provides pre-configured and ready-to-use cloud databases, including MySQL, PostgreSQL, Redis, MongoDB, and others. MySQL and PostgreSQL databases have pre-installed web-interfaces for database management: phpMyAdmin and Adminer. To create a cloud database, log in to the Hostman control panel and select Databases in the left sidebar menu. Click Create database. This will open the cloud database configuration page, which is similar to the cloud server configuration pages. In our case, we choose MySQL as the database type. However, in your projects, you can use any other databases. To apply the selected settings, you need to click the Order button. After this, you will be redirected to the main cloud database management page. The database will be configured and started in a few minutes, after which it will be available for work through the web interface or remote connection. To work with the database through the console terminal, you need to go to the Connection tab and copy the command to connect to the remote database via the MySQL client. For example, the connection command might look like this: mysql -u gen_user -p')<07dCV46*GdPE' -h 91.206.179.29 -P 3306 -D default_db After the -u flag, specify the username. After the -p flag, specify the root password in single quotes. After the -h flag, specify the IP address of the remote host. After the -P flag, specify the host port (for example, the standard port for MySQL is 3306, and for MongoDB, it is 27017). After the -D flag, specify the database name. Alternatively, in the upper left corner, you can click on the Web Interface button and choose one of the two available database management utilities. After this, a familiar login page will open in a new tab, where you will need to enter the login credentials for the database. Setup and manage your cloud database with ease Conclusion This tutorial demonstrated the installation and configuration of phpMyAdmin with the preliminary manual installation of all necessary dependencies: MySQL database Nginx web server PHP interpreter in FPM format In your own projects, you can expand the technological stack shown in this guide to a more familiar and common combination of Nginx + Apache + PHP. Additionally, as an alternative to manually deploying MySQL and phpMyAdmin, the creation of a pre-configured and ready-to-use cloud database on Hostman servers with pre-installed web-based database management interfaces, one of which is phpMyAdmin, was demonstrated. Hostman provides pre-configured and ready-to-use cloud databases, including MySQL, PostgreSQL, Redis, MongoDB, and others.

Data starts with creating tables. Relational tables must follow certain rules. In MySQL, special queries are used to create tables, specifying the attributes (fields) of the table and the data types for each field and optionally providing field descriptions. In this article, we will describe how to work with tables in MySQL, explain the CREATE TABLE syntax, and show you how to create tables and properly enter data.  Creating Tables in MySQL There are three main ways to create tables. If the table is created from scratch, the first commands must describe all fields. For this, MySQL uses the CREATE TABLE statement with the following syntax:  CREATE TEMPORARY  TABLE IF NOT EXISTS table_name( column_name_1 data_type_1,column_name_2 data_type_2, ...,column_name_N data_type_N,) ; Required parameters:  table_name is the name of the table being created (must be unique, and for convenience, the name should describe what the table represents);  column_name_1 is the name of the column;  data_type_1 defines the type of data that will be stored in the column.  Optional parameters:  TEMPORARY. If used in the statement, a temporary table is created. It exists only in the current session and disappears when reconnecting to the database. Temporary tables are usually used to store intermediate data during complex selections. Only the user who created it has access to the temporary table.  IF NOT EXISTS prevents an error if a table with that name already exists. It does not check if the structure of the existing table matches the one we tried to create; it only checks the name. The second way to create a table in MySQL is based on a query:  CREATE TEMPORARY  TABLE IF NOT EXISTS table_name( column_name_1 data_type_1,column_name_2 data_type_2, ...,column_name_N data_type_N,) IGNORE | REPLACEAS query_expression; Required parameter:  query_expression is an SQL query based on the results of which the table is created. For example, the query might look like this:  SELECT id, column_1 FROM table WHERE id > 15  Optional parameter:  IGNORE | REPLACE specifies how to handle rows that duplicate unique key values. The full query statement might look like this:  CREATE TABLE movies_copy (id INT, title CHAR(50) UNIQUE, year YEAR, summary TEXT) IGNORE SELECT id, title, year, storyline as summary FROM movies; The third way is when a new table is created based on the structure of another table. We can say that we are copying another table: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_nameLIKE old_tbl_name; old_tbl_name is the name of the table we want to "clone".  This method creates a table with the same structure as the original but does not copy the data stored in it. We get the same table but empty.  Data Types in MySQL MySQL supports various data types. Choosing the right type significantly affects the performance of the database, especially with large sizes. All data types can be divided into several groups.  Numeric Data Types Numeric types are divided into integer and floating-point. To store integers, data types TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT are used. Each of them allows storing values in the range from -2(N-1) to 2(N-1)-1, where N is the number of bits required for storage. If necessary, it is possible to use the UNSIGNED attribute to disallow negative values. In this case, the value range can be shifted to start from 0, for example, from 0 to 255 for TINYINT. The most common data type is INT. MySQL uses three data types for floating-point numbers: FLOAT (uses 4 bytes) — stores up to 24 decimal places; DOUBLE (uses 8 bytes) — stores up to 54 decimal places; DECIMAL (the number of bytes depends on the chosen precision). The DECIMAL data type is used to store exact fractional numbers. For example, it is used for storing financial data where precise results are needed during calculations. Synonyms (aliases) for DECIMAL include NUMERIC, DEC, and FIXED. For DECIMAL, you can choose the number of values you want to see before and after the decimal point, which determines the number of bytes it will use. DECIMAL can take two parameters DECIMAL(precision, scale). The precision parameter specifies the maximum number of digits the number can store, ranging from 1 to 65. The scale parameter specifies the maximum number of digits the number can contain after the decimal point, ranging from 0 to the precision value, with a default of 0. For example, with parameters DECIMAL(5,2), you can store the number 69839.12 or 71468.2. Character (String) Data Types CHAR has a fixed length of up to 255 characters. It is useful when you need to store short strings or when all data is approximately the same length. Since the length is fixed, the allocated space is also fixed. If the table is created with CHAR(10) (10 is the length of the string to be stored), all strings will have a length of 10. If fewer characters are entered, the remaining space will be filled with spaces, meaning that space in the database is allocated for nothing. VARCHAR stores variable-length character strings from 0 to 65535 characters, provided the MySQL version is higher than 5.0.3. It is useful when you do not know the length of the text to be stored. It uses only the necessary amount of space for the data. TEXT is intended for storing large amounts of character data. There are 4 types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. They differ in the maximum length of the string they can contain. Due to its size, it is often stored separately, sorted differently, and not indexed to full length. The BLOB family has similar features. These data types can store large amounts of information in binary form. The BLOB family is very similar to TEXT. BLOB types include TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. ENUM stores a predefined set of string values specified when the column is created. It is suitable if a field frequently repeats certain values that are few and the list doesn't need frequent updates, such as car body types or planets in the solar system. All these values are stored as integers and take up less space than strings, using 1-2 bytes. SET is a string object that can contain 0 or more values, each chosen from a predefined list specified when the table is created (maximum size is 64 elements). For example, to store book genres in a column with a SET type. SET stores values as integers, using 1-8 bytes. Date and Time Data Types Several types are available for date and time. DATE stores only the date in 'YYYY-MM-DD' format and uses 3 bytes. TIME stores only the time in 'hh:mm:ss' format and uses 3 bytes, with a range from '-838:59:59.00' to '838:59:59.00'. YEAR stores the year, with a range of 1901-2155, using 1 byte. DATETIME uses 8 bytes. It allows storing values from the year 1001 to 9999 with a precision of 1 second in the format: 'YYYY-MM-DD hh:mm:ss'. It is not dependent on the time zone. TIMESTAMP uses 4 bytes, thus having a much smaller date range: from '1970-01-01 00:00:01' to '2038-01-19 03:14:07'. It is stored as the number of seconds elapsed since the Unix epoch (January 1, 1970, GMT). The displayed value depends on the time zone. Later versions added support for storing time in microseconds. When both date and time are needed, two types are required: DATETIME and TIMESTAMP. Constraints in MySQL To ensure database integrity, MySQL uses specific constraints (CONSTRAINT). They can be divided into two types: column-level and table-level constraints (applied to a specific column or the entire table). Constraints are declared during table creation. They include: NOT NULL — indicates that the column cannot contain a NULL value. UNIQUE — prevents duplicate values (all values in the column must be unique). PRIMARY KEY — only unique non-NULL values can be stored in the column. Only one such column can exist in a table. FOREIGN KEY — creates a relationship between two tables based on a specific column. CHECK — controls the values in the column, checking if they are acceptable. DEFAULT — sets a default value for the column. If the field is omitted when entering a record, the default value will be inserted. Index Types in MySQL An index is a structure that stores the value of a table column (or several columns) and references the rows where these values are located. Creating indexes helps increase MySQL's efficiency, significantly speeding up queries. Most indexes have a tree-like data structure (are B-tree indexes). Indexes take up memory space, so typically only the fields that are used for data retrieval are indexed. Without an index, a query search goes through all table records, which can be time-consuming and computationally intensive.  Indexes in MySQL speed up operations: Searching for rows matching a WHERE query; Retrieving rows during joins; Finding minimum (MIN()) and maximum (MAX()) values of a specific indexed column; Sorting or grouping tables, provided the operation uses the leftmost prefix of the used index. There are following types of Indexes in MySQL Primary key — This column uniquely identifies each row in the table. Usually specified when creating the table. If not done manually, MySQL will create a hidden key. The primary key contains unique values. If it consists of several columns, the combination of values must be unique. The primary key cannot have NULL values. A table can have only one primary key. Unique index — Ensures the uniqueness of values in one or more columns. Unlike the primary key, you can create many unique indexes. It can have NULL values. Composite index — An index on multiple columns. MySQL allows creating composite indexes containing up to 16 columns. Typically used to speed up queries that need to retrieve data from multiple fields. Indexes are not recommended for small tables. The improvement from using indexes will not be noticeable. Indexes should be created primarily for slow queries or the most frequently used ones. Collect query performance statistics and conduct an assessment to determine this. Creating indexes for everything is not a good idea. Example 1: Creating a Table Let's create a MySQL table from scratch to store a collection of movies: CREATE TABLE movies (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,title CHAR(100) NOT NULL,year YEAR NOT NULL,running_time_min INT NOT NULL,storyline TEXT); This table includes the following columns: id: Unique identifier for the movie. title: The movie's title. year: The release year of the movie. running_time_min: The movie's duration in minutes. storyline: A brief description of the movie's plot. All fields have the NOT NULL constraint. The primary key is the id field. Initially, the table only has columns with specified data types.  Now let's insert some data into the table. For this, MySQL uses the INSERT INTO statement with VALUES.   INSERT movies3(title, year, running_time_min, storyline) VALUES ('Harry Potter and the Philosophers Stone', 2001, 152, "An orphaned boy enrolls in a school of wizardry, where he learns the truth about himself, his family and the terrible evil that haunts the magical world."),('Harry Potter and the Chamber of Secrets', 2002, 162,"An ancient prophecy seems to be coming true when a mysterious presence begins stalking the corridors of a school of magic and leaving its victims paralyzed."),('The Green Mile', 1999, 188,'Death Row guards at a penitentiary, in the 1930s, have a moral dilemma with their job when they discover one of their prisoners, a convicted murderer, has a special gift.'),('Forrest Gump', 1994, 142,"The presidencies of Kennedy and Johnson, the Vietnam War, the Watergate scandal and other historical events unfold from the perspective of an Alabama man with an IQ of 75, whose only desire is to be reunited with his childhood sweetheart."),('Cast Away', 2000, 143,"A FedEx executive undergoes a physical and emotional transformation after crash landing on a deserted island."); The result will be this table: Example 2: Creating a Table from a Query Now, let's create a table based on a query. We will select all movies released after 1999: CREATE TABLE movies_query ASSELECT id, title, year, running_time_min FROM movies WHERE year > 1999; The result: Example 3: Creating a Table Based on Another Table's Structure We can create a table based on the structure of another table: CREATE TABLE movies_copy LIKE movies; This creates an empty table with the same structure as the movies table but without data. Foreign Keys When there are multiple tables in a database, you might need to link them. Foreign keys are used in MySQL for this purpose. A foreign key is a column (or group of columns) that creates a relationship between tables. It refers to the primary key in another table. The table with the primary key is called the parent table, and the table with the foreign key is the child table. Creating a Foreign Key CONSTRAINT symbol FOREIGN KEYindex_name (col_name, ...)REFERENCES tbl_name (col_name,...)ON DELETE reference_optionON UPDATE reference_option Mandatory Parameters: FOREIGN KEY [index_name] (col_name, ...): Specifies the field to be used as a foreign key. index_name: Name of the index. col_name: Name of the column. REFERENCES tbl_name (col_name, ...): Specifies the column of the parent table with which our foreign key will be associated. tbl_name: Name of the table. col_name: Name of the column. Optional Parameters: CONSTRAINT symbol: Used to create and delete constraints. ON DELETE/ON UPDATE: Defines what to do when the parent table is deleted or updated. Options include: CASCADE: Automatically deletes or updates records in the child table when the parent table's records are deleted or updated. SET NULL: Sets the value to NULL in the child table when the parent table's records are deleted or updated. RESTRICT: Prevents deletion or updating of records in the parent table if they are used in the child table. Example 4: Creating a Table with a Foreign Key Let's create a table for movie genres: CREATE TABLE genres (id INT AUTO_INCREMENT PRIMARY KEY,genre VARCHAR(200) UNIQUE NOT NULL); Populate the table: INSERT INTO genres (genre) VALUES ('drama'),('fantasy'),('sci-fi'),('cartoon'); We will get the id for each genre. Example of creating a new table in MySQL with a FOREIGN KEY: CREATE TABLE movies2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,title CHAR(100) NOT NULL,year YEAR NOT NULL,running_time_min INT NOT NULL,genre_id INT,FOREIGN KEY (genre_id) REFERENCES genres (id)); Our table: To display the genres instead of just numbers, you can use a LEFT JOIN with the genres table: SELECT movies2.id, title, year, genre FROM movies2 LEFT JOIN genres ON genres.id = genre_id; Manipulating Tables Various commands can be used to work with an already created table. Here are some basic MySQL commands for renaming, modifying, deleting, and adding columns, and changing data types. Renaming a Table Option 1. Used if we initially indicated which database we were working in: USE movies_db;RENAME TABLE movies2 TO cinema; Option 2. Applies if the database is not specified: RENAME TABLE movies_db.movies2 TO movies_db.cinema; You can also use the RENAME TABLE command to move a table from one database to another: RENAME TABLE movies_db.movies2 TO cinema_db.cinema; Adding a New Column In MySQL, the ALTER TABLE statement is used to change a table in some way. ALTER TABLE cinemaADD Language VARCHAR(50) NULL; Deleting a Column ALTER TABLE cinemaDROP COLUMN Language; Renaming and Modifying Columns Use the CHANGE command to rename and redefine a column: ALTER TABLE cinema CHANGE year date INT NOT NULL; If you don't want to change the column name, specify the old name twice: ALTER TABLE cinema CHANGE year year INT NOT NULL; Use the MODIFY command to change the column definition without renaming it: ALTER TABLE cinemaMODIFY COLUMN title VARCHAR(100); Use the RENAME COLUMN command to change a column's name: ALTER TABLE cinema RENAME COLUMN running_time_min TO running_time; Deleting Operations To clear a table of data, use the TRUNCATE TABLE command: TRUNCATE TABLE cinema; To completely remove a table from the database, use the DROP TABLE command: DROP TABLE cinema; Adding and Removing Foreign Keys To add a foreign key: ALTER TABLE cinemaADD FOREIGN KEY (producer_id) REFERENCES producer (Id); Conclusion These operators and examples cover the basic operations with tables, enabling you to perform many useful tasks. These skills can be applied, including for working with MySQL cloud databases deployed on platforms like Hostman.

MySQL is an open-source database management system often installed as part of the popular LAMP stack (Linux, Apache, MySQL, PHP). It is a relational database that uses SQL (Structured Query Language). In short, the installation process is very easy: Update your package index. sudo apt update Install the mysql-server package. sudo apt install mysql-server Run the built-in security script. sudo mysql_secure_installation Below we'll explain in more detail how to install MySQL on an Ubuntu server and perform basic setup. Prerequisites To follow this guide, you will need: A cloud server running Ubuntu 22.04. A non-root user with sudo privileges. Step 1: Install MySQL To install MySQL on Ubuntu 22.04, first update your server's package index using apt: sudo apt update Install the default package: sudo apt install mysql-server MySQL will be installed, but it will not prompt you to set a password or make other configuration changes. Since this leaves MySQL insecure, we will address the setup in the next step. Step 2: Configure MySQL If you are installing MySQL on your Ubuntu server for the first time, you need to run the internal security script. It changes settings like remote root access and default users, which are not secure by default. sudo mysql_secure_installation This command will guide you through a series of prompts to make changes to your MySQL security settings. First, you'll be asked if you want to configure the Validate Password Plugin, which can be used to test the strength of MySQL passwords. Regardless of your choice, you will then need to set a password for the MySQL root user. Create a secure password, enter it, and then confirm it. You can now press Y and then ENTER to accept the default values for all subsequent questions. This will remove some anonymous users along with the test database and disable remote root logins. All changes will then be applied to your MySQL. Note that although you set a password for the root user, this user is not configured for password authentication when connecting to the MySQL shell. If desired, you can change this setting by performing step 3. Step 3 (Optional): Configure Authentication and Privileges The MySQL root user is set to authenticate using the auth_socket plugin by default, rather than with a password. In many cases, this improves security and usability, but it complicates things if you need to allow an external program (like phpMyAdmin) to access MySQL. To use a password to connect to MySQL as the root user, you need to change the authentication method from auth_socket to caching_sha2_password. To do this, open the MySQL command line: sudo mysql Then check which authentication method each of your MySQL accounts uses: SELECT user,authentication_string,plugin,host FROM mysql.user; In this example, you can see that the root user uses auth_socket authentication. To configure password authentication, run the ALTER USER command. Be sure to replace password with your own password and remember that this command will change the root user's password you set in step 2: ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password'; Then run FLUSH PRIVILEGES to apply the changes: FLUSH PRIVILEGES; Check the authentication methods for each user again to ensure that the root user no longer uses auth_socket: SELECT user,authentication_string,plugin,host FROM mysql.user; After confirming that the root user now authenticates with a password, you can exit the MySQL shell: exit Some people find it more convenient to connect to MySQL through a separate user. To create one, reopen the MySQL shell: sudo mysql Note: If you have enabled password authentication for the root user as described above, you will need to use a different command to access the shell. The above command will launch your MySQL client with ordinary user permissions, so to gain administrator privileges for your database, run: mysql -u root -p Now create a new user and choose a strong password: CREATE USER 'hostman'@'localhost' IDENTIFIED BY 'password'; Then grant the new user the necessary privileges. For example, you can give the user access to all tables in the database and the right to add, modify, and delete user rights with the following command: GRANT ALL PRIVILEGES ON *.* TO 'hostman'@'localhost' WITH GRANT OPTION; Note that at this stage you do not need to rerun the FLUSH PRIVILEGES command. This command is only necessary when modifying the access tables using INSERT, UPDATE, or DELETE statements. Since you created a new user rather than modifying an existing one, FLUSH PRIVILEGES is not required here. Now exit the MySQL shell: exit Finally, let's test our MySQL. Step 4: Test MySQL Regardless of how you installed it, MySQL should have started automatically. Check its status with the following command: systemctl status mysql.service If MySQL is not running, you can start it with: sudo systemctl start mysql For additional verification, you can try connecting to the database using the mysqladmin tool. This client allows you to run administrative commands. For example, the following command tells mysqladmin to connect to MySQL as the root user (-u root), prompt for a password (-p), and return the version: sudo mysqladmin -p -u root version This way, you can ensure that MySQL is running and working.   Congratulations! Your server now has a basic MySQL setup running and configured. You can now start working with your databases. 

When working with databases, having multiple copies of your data can be beneficial. This provides redundancy in case one of the servers fails, as well as improves availability, scalability, and overall performance of the database. The practice of synchronizing data across multiple separate databases is called replication. MySQL is a relational database management system and the most popular open-source relational database today. It has several built-in replication features that allow you to maintain multiple copies of your data. In this guide, we will set up two servers with MySQL: the original database, the "source", and its "replica". We will also look at how MySQL performs replication. Note: Historically, this type of replication was called "master-slave". In a post published in July 2020, the MySQL team acknowledged the negative connotation of this term and announced plans to update the program and accompanying documentation to use more inclusive language. However, this process is not fully complete. While the documentation and most commands in MySQL version 8 have been updated to refer to servers as "source" and its "replicas", there are still places with the old terminology. By default, this guide will use the more modern terms and commands, but in some cases, it is unavoidable to refer to the concepts of master and slave. Prerequisites Two cloud servers running Ubuntu 20.04. Both should have a non-root user with sudo privileges and a firewall configured using the UFW utility. MySQL installed on each server. In this guide, we will use the 8.0.25 version. On Hostman, you can install hosted MySQL on your cloud server with one click. The process described here involves designating MySQL on one server as the original database, the "source", and then setting up MySQL on another server as its copy, the "replica". This guide also describes transferring existing data from the source to the replica. This process involves creating a snapshot of the source database and transferring the resulting file to the replica. For this, we recommend setting up SSH keys on the source server and then ensuring that the source's public key is copied to the replica. How Replication Works in MySQL In MySQL, replication involves the source database recording every data change in one or more databases into a special file known as the binary log. Once the replica starts, two parallel threads are created. The first, called the IO thread, connects to the source MySQL instance and reads events from the binary log line by line, then copies them to a local file on the replica server called the relay log. The second thread, called the SQL thread, reads events from the relay log and applies them to the replica as quickly as possible. Recent versions of MySQL support two methods of data replication. The difference between them lies in how the replicas track which database events from the source they have already processed. The traditional replication method is called position-based binary log file replication. If you set up a MySQL replica using this method, you must provide it with a set of coordinates from the binary log. These consist of the name of the log file on the source that the replica should read from and a specific position in that log. This position represents the first event in the source database that the replica should copy. Since replicas receive a copy of the entire binary log from the source database, without the correct coordinates, they will start copying every recorded database event. This can cause issues if you want to replicate data only from a certain point in time or duplicate only a subset of the source data. Position-based binary log file replication is suitable for many cases but can become cumbersome in more complex systems. This led to the development of a new MySQL replication method, sometimes called transaction-based replication. This method involves creating a Global Transaction Identifier (GTID) for each transaction, or isolated piece of work, that the source MySQL instance performs. The mechanism of this replication is similar to position-based binary log file replication: each time a transaction occurs in the source, MySQL assigns and records a GTID for it in the binary log along with the transaction itself. The GTID and transaction are then sent to the replicas for processing. Transaction-based replication has several advantages over the traditional method. For example, both the source and its replicas maintain the GTID, so if the source or a replica detects a transaction with an already processed GTID, they skip it. This helps ensure consistency between the source and its replicas. Additionally, with transaction-based replication, replicas do not need to know the binary log coordinates of the next database event. Starting new replicas or changing the order of replicas in the replication chain becomes much simpler. Keep in mind that this is just an overview of how MySQL handles replication; MySQL provides many settings that you can adapt to your needs. In this guide, we set up position-based binary log file replication. If you want to set up a different replication environment, check the official MySQL documentation. Step 1 — Configuring the Source Server's Firewall for MySQL Replication Your firewalls on both servers should be configured using UFW. This will help protect both your servers; however, now the source's firewall will block any connection attempts from the MySQL replica. To change this, you need to enable a new rule in UFW that will allow connections from the replica through the source's firewall. You can do this by running the following command on the source server. This command allows any connections originating from the replica's IP address (in this example, replica_server_ip) to the default MySQL port, 3306: sudo ufw allow from replica_server_ip to any port 3306 Be sure to replace replica_server_ip with the actual IP address of your replica server. After this, you don't need to change anything in the replica's firewall since this server will not receive any incoming connections, and UFW does not block outgoing connections to the source MySQL server. Now, let's move on to configuring the source. Step 2 — Configuring the Source Database for MySQL Replication To get your source MySQL database to start synchronizing data, you need to make a few changes to its configuration. In Ubuntu 20.04, the default MySQL server configuration file is named mysqld.cnf and is located in the /etc/mysql/mysql.conf.d/ directory. Open this file on the source server in any text editor. Here, we will use nano: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf Find the bind-address directive in the file. By default, it looks like this: /etc/mysql/mysql.conf.d/mysqld.cnf ... bind-address = 127.0.0.1 ... 127.0.0.1 is the IPv4 address representing localhost. If you specify this value for the bind-address directive, MySQL will listen for connections only on the localhost address. In other words, this MySQL instance will only accept connections originating from the server it is installed on. Remember that you are turning another MySQL instance into a replica of this one, so the replica needs to be able to read all new data written to the source. Therefore, you need to configure the source MySQL server to listen for connections on an address accessible to the replica, such as the source's public IP address. Replace 127.0.0.1 with the source server's IP address. After this, the bind-address directive will look like this, but with your server's IP address instead of source_server_ip: ... bind-address = source_server_ip ... Then, find the server-id directive, which defines the identifier by which MySQL distinguishes servers within the replication system. Each server in the system, including the source and all its replicas, must have its unique server-id value. By default, this directive is commented out and looks like this: /etc/mysql/mysql.conf.d/mysqld.cnf ... # server-id = 1 Uncomment this line by removing the # sign. You can choose any number as the value for this directive, but remember that the number must be unique and cannot match other server-id values in your replication group. For simplicity, in the following example, this value remains the default (1): /etc/mysql/mysql.conf.d/mysqld.cnf ... server-id = 1 ... Find the log_bin directive under the server-id line. It specifies the name and location of the MySQL binary log. By default, this directive is commented out, so binary logging is disabled. To know when and how to start replication, your replica server needs to read the source's binary log, so uncomment this line to enable logging on the source. After this, it will look like this: /etc/mysql/mysql.conf.d/mysqld.cnf ... log_bin = /var/log/mysql/mysql-bin.log ... Finally, scroll down to the end of the file to find the commented binlog_do_db directive: /etc/mysql/mysql.conf.d/mysqld.cnf ... # binlog_do_db = include_database_name Remove the # sign to uncomment this line, and replace include_database_name with the name of the database you want to replicate. In this example, the binlog_do_db directive points to a database named db. If you already have an existing database on the source that you want to replicate, use its name instead of db: /etc/mysql/mysql.conf.d/mysqld.cnf ... binlog_do_db = db Note: If you are replicating more than one database, you can add another binlog_do_db directive for each database you need to add to the group. In this guide, we will continue with replicating just one database, but if you have several, it will look something like this: /etc/mysql/mysql.conf.d/mysqld.cnf ... binlog_do_db = db binlog_do_db = db_1 binlog_do_db = db_2 Alternatively, you can specify which MySQL databases should not be duplicated by adding a binlog_ignore_db directive for each of them: /etc/mysql/mysql.conf.d/mysqld.cnf ... binlog_ignore_db = db_to_ignore ... After making these changes, save and close the file. If you used nano, press CTRL + X, Y, and then ENTER. Restart MySQL by running the following command: sudo systemctl restart mysql Now this MySQL instance is ready to act as the source database, which your MySQL server will replicate. However, before configuring the replica, we need to perform a few more steps on the source to ensure that replication works correctly. To begin with, we need to create a special MySQL user account that will perform all the replication-related actions. Step 3 — Creating a User Account for MySQL Replication Each replica in a MySQL environment connects to the source database using a username and password. Replicas can connect using any MySQL user that exists in the source database and has the appropriate privileges, but in this guide, we will create a special user account for this purpose. Start by opening the MySQL shell: sudo mysql Note: If you have set up a password-authenticated user account, you can connect to MySQL using the following command: mysql -u username -p Replace username with your user name and enter the password when prompted. Keep in mind that some operations in this guide, including those performed on the replica server, require elevated privileges. For this reason, connecting with administrator privileges may be more convenient. If you still want to use a MySQL user with limited rights, they must at least be granted the CREATE USER, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, and REPLICATION_SLAVE_ADMIN privileges. Create a new MySQL user. In this example, a user named replica_user is created, but you can use any name you prefer. Be sure to replace replica_server_ip with the public IP address of your replica server and set a more secure password instead of the default password: CREATE USER 'replica_user'@'replica_server_ip' IDENTIFIED WITH mysql_native_password BY 'password'; Note that this command specifies that replica_user will use the mysql_native_password plugin for authentication. You can instead use the default mechanism in MySQL, caching_sha2_password, but this will require setting up an encrypted connection between the source and the replica. This type may be suitable for production environments, but setting up encrypted connections is beyond the scope of this guide. If you wish, you can find instructions for setting up a replication environment with encrypted connections in the MySQL documentation. After creating the new user, grant them the appropriate privileges. At a minimum, such a user should have the REPLICATION SLAVE permissions: GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'replica_server_ip'; Next, it is recommended to run the FLUSH PRIVILEGES command. This will clear all cached memory on the server for the previous CREATE USER and GRANT statements: FLUSH PRIVILEGES; With this, we have finished setting up the replication user in the source MySQL. However, do not exit the MySQL shell yet. You will need it in the next step to obtain important information about the binary log of the source database, so leave it open for now. Step 4 — Obtaining Binary Log Coordinates from the Source Database As mentioned in the "How Replication Works in MySQL" section, MySQL performs replication by line-by-line copying of database events from the source's binary log and executing each event on the replica. When using position-based binary log file replication, you must provide the replica with a set of coordinates detailing the name of the source binary log and the specific position within that file. The replica then uses these coordinates to determine the point in the log file from which it should start copying database events and track which events it has already processed. In this step, we will see how to obtain the current coordinates of the source database's binary log to start copying data on the replicas from the last line in the log. To avoid issues related to other users changing the data while obtaining the coordinates, you will need to lock the database so that no one can read or write information. This will not take long but will pause your database's operation. Execute the following command in the open MySQL shell on the source server. It will close all open tables in each database in the source instance and lock them: FLUSH TABLES WITH READ LOCK; Then execute the following command to output information about the current state of the source's (master's) binary log: SHOW MASTER STATUS; This position is where the replica will start copying database events. Record (remember or write down) the File and Position values; you will need them later when you start the replication. Your next steps depend on whether there are any existing data in the source database that you want to transfer to your replicas. Proceed to the appropriate subsection. If Your Source Has No Data to Transfer If you have just installed this MySQL or there is no existing data that you want to transfer to the replica, you can unlock the tables at this point: UNLOCK TABLES; You can create a database for master-slave replication while you still have the MySQL shell open. As in the example from Step 2, the following command will create a database named db: CREATE DATABASE db; After that, close the MySQL shell: exit Now you can proceed to the next step. If Your Source Has Data to Transfer You can transfer existing data from the source database to the replica by creating a snapshot (copy) of the database using the mysqldump utility. However, your database is still locked. If you make any new changes in the same window or close it, it will automatically unlock, which could lead to problems. Unlocking the tables means that data in the database may change. This could potentially lead to discrepancies between your snapshot and the binary log coordinates you just obtained. Therefore, you should open a new terminal window or tab on your local computer. This will allow you to create a database snapshot without unlocking MySQL. In the new window or tab, open another SSH session to the server hosting the source MySQL instance: ssh username@source_server_ip Then, in the new tab or window, export your database using mysqldump. In this example, a dump file named db.sql is created from the database named db. Make sure to use the name of your database. Also, remember to run this command in the bash shell, not in the MySQL shell: sudo mysqldump -u root db > db.sql Now you can close this window or tab and return to the original window where the MySQL shell should still be open. In the MySQL command line, unlock the databases to make them writable again: UNLOCK TABLES; Exit the MySQL shell: exit You can now send the snapshot to your replica server. Assuming you have already set up SSH keys on the source server and added the source's public key to the authorized_keys file of your replica, you can securely send the snapshot using the scp command: scp db.sql username@replica_server_ip:/tmp/ Remember to replace username with the name of the Ubuntu administrative user created on the replica server and change replica_server_ip to the IP address of your replica server. Also, note that this command places the snapshot in the replica server's /tmp directory. After sending the snapshot to the replica server, connect to it via SSH: ssh username@replica_server_ip Open the MySQL shell: sudo mysql Create the database that you will copy from the source: CREATE DATABASE db; You do not need to create tables or load any sample data into this database. Everything will be filled in automatically when you import the database using the snapshot you just created. Instead, exit the MySQL shell: exit Import the snapshot: sudo mysql db < /tmp/db.sql Now your master-slave replica contains all the existing data from the source database. Let's move on to the final step to configure the replica server to start replication. Step 5 — Configuring the Replica Now we need to change the configuration of the replica, similar to how you changed the source database. Open the MySQL configuration file, mysqld.cnf, on the replica server: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf As mentioned earlier, each MySQL instance in the environment must have a unique server-id value. Find the server-id directive on the replica, uncomment it, and change its value to any positive integer different from the source database: /etc/mysql/mysql.conf.d/mysqld.cnf server-id = 2 Next, update the log_bin and binlog_do_db values to match the values you set in the source server's configuration file: /etc/mysql/mysql.conf.d/mysqld.cnf ... log_bin = /var/log/mysql/mysql-bin.log ... binlog_do_db = db ... Finally, add the relay-log directive, defining the location of the replica's relay log file. Include this line at the end of the configuration file: /etc/mysql/mysql.conf.d/mysqld.cnf ... relay-log = /var/log/mysql/mysql-relay-bin.log After making these changes, save and close the file. Then restart MySQL on the replica to apply the new settings: sudo systemctl restart mysql After restarting the MySQL service, the replication is ready to start. Step 6 — Starting and Testing Replication By this point, both of your MySQL instances are fully configured to begin master-slave replication. To start it, open the MySQL shell on the replica server: sudo mysql In the command line, execute the command below; it sets several MySQL replication parameters simultaneously. After running this command and starting replication, the server will attempt to connect to the IP address in SOURCE_HOST using the login and password (SOURCE_USER and SOURCE_PASSWORD, respectively). It will also look for the binary log named SOURCE_LOG_FILE and begin reading it from position SOURCE_LOG_POS. Make sure to replace source_server_ip with the IP address of the source server. Similarly, replica_user and password should match the replication user you created in Step 3; mysql-bin.000001 and 899 should reflect the binary log coordinates obtained in Step 4. You can enter this command in a text editor before running it on the server to make it easier to replace all the necessary information: CHANGE REPLICATION SOURCE TO SOURCE_HOST='source_server_ip', SOURCE_USER='replica_user', SOURCE_PASSWORD='password', SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_LOG_POS=899; Now activate the replica server: START REPLICA; If you entered all the information correctly, this instance will start replicating all changes made to the db database on the source. You can get all the details about the current status of the replica with the following command. The \G modifier restructures the text, making it more readable: SHOW REPLICA STATUS\G; This command outputs a lot of useful information that can be used during debugging and troubleshooting. Note: If the replica has connection issues or replication unexpectedly stops, an event in the source's binary log might be blocking replication. In such cases, try running the SET GLOBAL SQL_SLAVE_SKIP_COUNTER command to skip a certain number of events following the binary log position specified in the previous command. For example, to skip only the first event: SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; Then you will need to restart the replica: START REPLICA; If you ever need to stop replication, run this command on the replica server: STOP REPLICA; Your replica now copies data from the source. All changes made to the source database will be reflected in the MySQL replica instance. To test, let's create a test table and verify the successful execution of replication. Open the MySQL shell on the source server: sudo mysql Select the database for replication: USE db; Create a table in it. In this example, we have a table named example_table with one column example_column: CREATE TABLE example_table ( example_column varchar(30) ); If desired, you can add some data: INSERT INTO example_table VALUES ('First row'), ('Second row'), ('Third row'); After this, return to the MySQL shell on your replica server and select the copied database: USE db; Then run the SHOW TABLES statement to display a list of all tables in the selected database: SHOW TABLES; If MySQL replication is working correctly, you will see the newly created table in the list. Additionally, if you added data to the table on the source, you can check it by running a similar query: SELECT * FROM example_table; In SQL, the asterisk (*) means "all columns". Essentially, this query tells MySQL to display every column from example_table. If MySQL replication is working correctly, this operation will show this data. If none of these operations output the table or data from the source, there might be an error somewhere in the configuration. To find the problem, you can try running the SHOW REPLICA STATUS\G operation. Additionally, you can refer to the MySQL documentation for troubleshooting replication. Conclusion With this guide, we have set up a position-based binary log replication environment in MySQL with one source and one replica. But remember, this is just one way to set up replication in MySQL. MySQL offers several different master-slave synchronization options that you can choose and configure according to your needs. There are also several third-party tools, such as Galera Cluster, which can be used to extend the built-in MySQL replication features.

MySQL is an open-source relational database management system (RDBMS) widely used for storing, managing, and processing structured data. It is one of the most popular and widely adopted RDBMS in the world, especially in the field of application and web service development. Properly structured relational databases rely heavily on structural elements such as primary and foreign keys.  In this article, we will discuss the purpose of these keys in MySQL and the process of creating and deleting them in a table. Additionally, we will provide a practical example of their use in a database for a more detailed understanding. Primary Key and Its Purpose A primary key in MySQL is a special type of constraint used to uniquely identify records in a table. It ensures the uniqueness of each row and forms the basis for linking different tables. Primary keys have several distinctive properties: Uniqueness: The values of this key must not be duplicated in the table. Immutability: Only one column can be designated as the primary key in a table. If multiple such fields are needed, composite keys should be used, but this article will not focus on them. Non-null Value: The value of a primary key cannot be null (NULL). Here is an example of a users table in a database that contains a primary key: user_id  username email birthdate registration_date 1 example_user1 [email protected] 1990-05-15 2024-05-19 10:30:13 2 example_user2 [email protected] 1988-11-30 2024-05-19 11:45:10 3 example_user3 [email protected] 1999-10-15 2024-05-20 12:15:13 4 example_user4 [email protected] 1998-12-07 2024-05-20 14:34:56 In this example, the user_id field is the primary key that uniquely identifies each record. Methods to Create a Primary Key To create a primary key in MySQL during the table creation phase, the user must add the corresponding line to the command: CREATE TABLE table_name ( ... field_name data_type PRIMARY KEY, ... ); Here, the keyword PRIMARY KEY indicates the creation of the primary key field. The ALTER TABLE command is used to modify the structure of existing tables and allows adding, modifying, and deleting columns, as well as setting various constraints and indexes. It is also used to set a primary key: ALTER TABLE table_name ADD PRIMARY KEY (field_name); To delete this key in MySQL, use the following command: ALTER TABLE table_name DROP PRIMARY KEY; Foreign Key and Its Purpose A foreign key in MySQL is a table element used to establish a link with another table. When two tables are linked via a foreign key, the one containing the primary key is called the parent table, and the one with the foreign key is called the child table. Unlike primary keys, foreign keys can contain null and duplicate values, and there can be more than one foreign key in a child table. Methods to Create a Foreign Key To create a foreign key in MySQL when forming a table, add the following entry to the command: CREATE TABLE child_table_name ( ... child_field_name data_type, FOREIGN KEY (child_field_name) REFERENCES parent_table_name(parent_field_name), ... ); Alternatively, you can add a foreign key to an existing table using the command: ALTER TABLE child_table_name ADD FOREIGN KEY (child_field_name) REFERENCES parent_table_name(parent_field_name); To delete a foreign key from a table, use: ALTER TABLE table_name DROP FOREIGN KEY field_name; When creating a foreign key, you can add parameters to define actions for rows in the child table when a row in the parent table is updated (ON UPDATE) or deleted (ON DELETE). These parameters are added after the REFERENCES operator. Here is a list of possible parameters: CASCADE: If you delete (update) a row from the parent table, MySQL automatically deletes (updates) the related rows in the child table. SET NULL: If you delete (update) a row from the parent table, MySQL automatically sets the foreign key value in the child table to NULL. RESTRICT: If you delete (update) a row from the parent table, MySQL cancels the action (throws an error) if there are related rows in the child table. NO ACTION: The same as RESTRICT. Example of Creating Keys in a Database In this chapter, as an example, we will create a database on the Hostman cloud server. First, start cloud MySQL on the server: sudo mysql Next, create a new database and switch to it: create database TestDB; use TestDB; As an example, we will create two tables. The first will store categories of Hostman services, and the second will list services of different categories. To create the first table with primary key, enter the following in the console: CREATE TABLE Categories ( CategoryID INT PRIMARY KEY, CategoryName VARCHAR(30) NOT NULL ); Here, CategoryID is the unique identifier for the category, and CategoryName is its name. In this example, the primary key CategoryID is needed to link it with the second table. Now, create the second table: CREATE TABLE Services ( ServiceID INT PRIMARY KEY, ServiceName VARCHAR(30) NOT NULL, CategoryID INT, FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) ); In the created table: ServiceID is the primary key identifying the service; ServiceName is the name of the service; CategoryID is the foreign key linking the service to a specific category. This foreign key references the CategoryID column in the "Categories" table. Next, insert data into both tables. Hostman has three service categories, so the data for the first table will be: INSERT INTO Categories (CategoryID, CategoryName) VALUES (1, 'Compute'), (2, 'App Platform'), (3, 'Services'); For the second table, we will take the services: INSERT INTO Services (ServiceID, ServiceName, CategoryID) VALUES (101, 'Cloud servers', 1), (102, 'Frontend apps', 2), (103, 'Backend apps', 2), (104, 'Databases', 3), (105, 'Queue', 3); Now the tables look like this: And finally, let's check how our primary key works. To do this, we will compose a query using the LEFT JOIN operator to output general information about all services and their categories in the company: SELECT Services.ServiceID, Services.ServiceName, Categories.CategoryName FROM Services LEFT JOIN Categories ON Services.CategoryID = Categories.CategoryID; As a result, we get the following table: As seen above, we have combined two tables where each service shows the category it belongs to instead of a number.

Accessing databases is key task for server management and web application maintenance. MySQL is one of the most common and powerful database management systems (DBMS). As a database administrator, you manage critical information, and the MySQL root password plays a crucial role in ensuring the security and integrity of this data. You may occasionally lose or forget the MySQL root password. This can happen for various reasons, from unforeseen circumstances to team changes or simple human error. In such cases, knowing how to reset the root password and regain access to the database is essential. In this article, we will present methods and tools to help you reset the MySQL root password and restore full control over your database. We will cover various approaches, from using official MySQL tools like mysqladmin and mysqld_safe to editing configuration files and making system changes. We will also discuss important precautions to help you secure your database and prevent the root password from being lost in the future. These include using strong passwords, limiting database access to only necessary users, and regularly backing up data to minimize risks. So, if you need to reset the MySQL root password, below are detailed instructions and recommendations to help you regain access and resume managing your MySQL database. MySQL Password Reset Methods MySQL does not use PAM, and the SSH password will not work. The root password is set during the database server installation at the stage of forming the privilege tables. If you receive the error ERROR 1045: Access denied for user 'root'@'localhost' instead of a greeting after an attempt to log in, you need to reset the root password. To reset the root password in MySQL 8, you need to follow a simple procedure. This method is equally suitable for MySQL and MariaDB, as they are fully compatible and have the same commands. In Ubuntu, you can reset the MySQL root password using the same methods as in Debian. A slight difference may occur for CentOS 7, which we will note in the corresponding section below. How to Reset the MySQL Root Password Using skip-grant-tables This is a fairly common method due to its simplicity. Here is a step-by-step plan for this operation: Step 1: Stop the Database Service service mysqld stop Step 2: Start the Service Without Privilege Tables mysqld --skip-grant-tables& The ampersand at the end allows you to run the service in the background, so it does not occupy a separate terminal window. Step 3: Connect to the Server Since the privilege tables are not loaded, there is no need to enter a password: mysql -u root Step 4: Load the Privilege Tables Each command to the database must end with a semicolon. If the query is not completed, MySQL will wait for the continuation of the command. You can simply add a semicolon if it was missed in the new line. FLUSH PRIVILEGES; The response should be: Query OK, 0 rows affected. Step 5: Set a New Root Password To set a new root password in MySQL, for Ubuntu and Debian, use: ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD'; If this query does not work, try: UPDATE `mysql`.`user` SET `password`=password('PASSWORD') WHERE `user`='root'; Or: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('PASSWORD'); On CentOS 7, you can reset the MySQL root password with the command: UPDATE `mysql`.`user` SET `authentication_string`=password('PASSWORD') WHERE `user`='root'; You will see the response: Query OK, 0 rows affected if it is successfully executed.  The password can even be an empty value, but in this case, you will lose the ability to connect to the server by any other means except the command line. The password changes immediately after executing the command. You can check it, for example, by connecting through phpMyAdmin if no empty value was set. Step 6: Exit MySQL quit; The response should be Bye, and you will return to the regular terminal. Step 7: Start the Database Server in Normal Mode service mysqld start How to Reset the MySQL Root Password on Windows Resetting the MySQL superuser password on Windows is similar to the procedure for resetting the root password for Linux systems. Some differences may only be in the process of finding and starting the database service itself.  Here is a step-by-step guide for resetting the database superuser password for Windows OS. Step 1: Open Command Prompt as Administrator There is a difference depending on which database is installed. To check, run: For MySQL: sc qc "mysql" For MariaDB: sc qc "mariadb" If the database is not installed, you will see the message that the service does not exist. Otherwise, you will see the basic information about MySQL or MariaDB. Step 2: Navigate to the Database Service Executable If the %PATH% variable is not set, go to the folder with the database service executable file. You can find the pathin the sc qc command output in the "BINARY_PATH_NAME". In our case, it is C:\localhost\mariadb\bin\. cd C:\localhost\mariadb\bin\ Step 3: Stop the Running Service For MySQL: net stop mysql For MariaDB: net stop mariadb Step 4: Open a New Command Prompt Window Use the start command. Unlike Linux, in Windows, the ampersand at the end of the command does not work, and when the service starts, it will not allow further commands until the session ends (CTRL+C). Step 5: Start the Server Without Privileges You can use the same command for MySQL and MariaDB. mysqld --skip-grant-tables Step 6: In the Second Window, Enter: mysql -u root -p Enter the blank password, and you will see the standard greeting. Now you need to enter commands for the database server. Step 7: Execute the Initial Command FLUSH PRIVILEGES; Without this initial command, you may receive an error when changing the password: "ERROR 1290: The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement." Step 8: Execute the Command to Change the Password ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD'; Note: If the command does not work, try the options from Step 5 of the instructions for Linux systems. Close the command prompt window with the temporary database version running or press CTRL+C. Step 9: Start the System Service For MySQL: net start mysql For MariaDB: net start mariadb Conclusion Resetting the MySQL root password is an important skill for database administrators. In this article, we reviewed various methods and tools for resetting the root password and regaining full control over your managed MySQL database. In conclusion, here are some key points regarding database security: Use complex passwords that combine letters, numbers, and special characters, and change them regularly. Limit database access to only essential users to minimize the risk of unauthorized access. Regularly update the MySQL system to eliminate known vulnerabilities and receive the latest security patches. Regularly back up the database. This is your insurance in case of data loss or system failure. Develop a backup strategy, determine the frequency and methods for creating backups, and check their recoverability to ensure data restoration capability. Remember the importance of training and self-development in database management. Stay informed about the latest trends, new features, and MySQL tools. Use educational resources, attend seminars and conferences, and communicate with experienced professionals in this field. Constantly updating your knowledge and skills will help you manage the database effectively and solve emerging problems. Resetting the MySQL root password is a critical task that requires attention and knowledge. However, with the studied methods and recommendations, you can handle this task and regain complete control over your MySQL database. Remember to ensure security, create backups, and continue learning, and your work with MySQL will be successful and secure.

When you install MySQL, it already has a root account with almost unlimited privileges. This account is necessary for creating, modifying, and deleting other database users. Let's figure out how to delete a MySQL user. This tutorial is also applicable to MariaDB. Authorizing in MySQL with root Let's assume you have a cloud server on Hostman with MySQL installed. Log in as the root superuser: mysql -u root -p password After the -u argument, enter the username, in this case root. After -p, specify the MySQL root password (this is not the operating system root user's password). Querying Data First, check which users exist in the database server to avoid mistakes. In MySQL, you can view users with the command: SELECT User, Host FROM mysql.user; The list will display the username and host. Checking Permissions Suppose you need to delete a MySQL user 'hostman@localhost'. First, check what permissions this user has: SHOW GRANTS FOR 'hostman'@'localhost'; The response will be a table with the permissions. In our example, this user has full privileges for the hostman_db database. Revoking Permissions You have obtained the list of users and their permissions through MySQL. Next, you need to decide what to do with the user. You don’t always have to delete it; you can start by revoking privileges. The account will still exist, but the permissions will be limited. To delete user’s permissions in MySQL, execute the command: REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'hostman'@'localhost'; Replace 'hostman'@'localhost' with the actual username in your case. The response should be: Query OK, 0 rows affected (0.00 sec) You can verify everything is correct by requesting the list of privileges again: SHOW GRANTS FOR 'hostman'@'localhost'; Deleting the MySQL User If the user is no longer needed, delete it: DROP USER 'hostman'@'localhost'; The response should be: Query OK, 0 rows affected (0.00 sec) You can verify everything is correct by requesting the list of users: SELECT User, Host FROM mysql.user; The DROP USER command deletes one or more MySQL users and their privileges. Therefore, revoking privileges is an optional step if you ultimately decide to delete the account completely. DROP USER does not automatically close open sessions. If you delete a user with an open session, the changes will only take effect once the session is closed. After the session is closed, the user will be deleted, and the next login attempt will fail.