How to Create Tables in MySQL

How to Create Tables in MySQL
Hostman Team
Technical writer
MySQL
27.06.2024
Reading time: 13 min

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.

  1. 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.

  1. 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 | REPLACE
AS 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;
  1. 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_name
LIKE 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:

Image1

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 
AS
SELECT id, title, year, running_time_min 
FROM movies 
WHERE year > 1999;

The result:

Image3

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.

Image2

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 KEY
index_name (col_name, ...)
REFERENCES tbl_name (col_name,...)
ON DELETE reference_option
ON 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:

Image4

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;

Image6

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 cinema
ADD Language VARCHAR(50) NULL;

Deleting a Column

ALTER TABLE cinema
DROP 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 cinema
MODIFY 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 cinema
ADD 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
27.06.2024
Reading time: 13 min

Similar

Debian

How to Install MySQL on Debian

Installing MySQL on Debian effectively creates a robust and flexible database (DB) infrastructure that accommodates a wide range of applications as well as services. It is renowned for its scalability, dependability, and durability. By setting it, individuals experience the operations in an efficient manner and enhance the overall efficiency of DB infrastructure. This combination is especially beneficial for administrators, DB analysts, and industries that demand a dependable database solution for dealing with huge data. Additionally, MySQL's comprehensive guide and supporters help make it simpler to troubleshoot problems and enhance operations.  In this guide, we will demonstrate the thorough procedure for installing and configuring MySQL on Debian. How to Install MySQL on Debian The default repositories do not contain the MySQL database server package on Debian. To install it on a  Linux system follow the below instructions. We will download the recent version of the MySQL. Step 1: Download MySQL Package Let us obtain the MySQL repository information package, which is in the .deb format: wget https://dev.mysql.com/get/mysql-apt-config_0.8.30-1_all.deb Note: To authenticate the most updated release, go to the MySQL repository webpage. Step 2: MySQL Configuration Package Installation Then, employ the .deb file for initializing the installation via dpkg: sudo dpkg -i mysql-apt-config_0.8.30-1_all.deb Respond to the prompt. For instance, pick MySQL Server & Cluster and hit Enter for starting configurations: For picking a version such as (mysql-8.4-lts), scroll downward and hit OK for the next step: Step 3: Refresh the System Now, update the server's package indexes to implement the updated MySQL info: sudo apt update Step 4: MySQL Installation Debian's default manager makes sure to install MySQL in an easier manner. Installing the package with this command: sudo apt install mysql-server -y You will see the interface for setting the root account. Input a stronger password to secure the database. In the end, hit the Ok button: Check the version on the server via the --version utility: mysql --version Step 5: Managing the Services Now, you can enable the MySQL service to initialize automatically at boot time: sudo systemctl enable mysql Activate the service via the systemctl utility: sudo systemctl start mysql Check if the system service is operational by viewing its status: sudo systemctl status mysql Step 6: MySQL Secure Installation The key or password that the individual created at the initialising procedure is currently protecting the root DB user on the server. MySQL also includes other insecure defaults, such as remote access to test databases and the root database user on the server.  It is vital to secure the MySQL installation after it has been completed as well as disable all unsafe default settings. There is a security script that can assist us in this procedure. Run the script: sudo mysql_secure_installation To activate the VALIDATE PASSWORD component and guarantee stringent password procedures, type Y and hit Enter. Next, you will need to configure several security settings: Set the Root Password: Select a strong password and make sure that it is correct. Configure the password policy for the DB server. For instance, type 2 to permit only the strong passwords on the server and hit Enter. When required to modify the root password, input N; alternatively, input Y to modify the password. Eliminate Anonymous Users: It is advised to eliminate the accessibility of anonymous users. For this, input Y and Enter when prompted. Prevent Accessibility of Remote Root: It is a better practice to avoid remote root login for multiple security concerns. To prevent the root user from having a remote access, input Y and hit Enter. Delete the Test DB: For enhancing security, the test database, which is utilized for testing, can be deleted. To do so, input Y and hit Enter. Refreshing Privilege Tables: It guarantees that all modifications are implemented instantly. To implement the configuration and edit the privileges table, hit Enter. Step 7: Access MySQL Utilizing the mysql client utility, MySQL establishes the connection and provides access to the database server console.  Now, access the shell interface and run general statements on the DB server. Let’s input the root and the password created at the time of the safe installation procedure: sudo mysql -u root -p Step 8: Basic MySQL Operations The creation of a DB and a new user for your applications rather than utilizing the root is a better practice. To accomplish the task, employ the given instructions: Create a Database: First, create a database. For instance, hostmandb is created via the below command: CREATE DATABASE hostmandb; Display All Databases: List all databases to make sure hostmandb is created: SHOW DATABASES; Create of a New User: Create a user and assign a strong password. In our example, we set Qwer@1234 as a password for the user  minhal. Replace these values with your data. CREATE USER 'minhal'@'localhost' IDENTIFIED BY 'Qwer@1234'; Give Permissions to the User: Give complete access to the hostmandb to the new user: GRANT ALL PRIVILEGES ON hostmandb.* TO 'minhal'@'localhost'; Flush Privileges: To implement the modifications, refresh the table: FLUSH PRIVILEGES; Exit the Shell: For closing the interface, utilize the EXIT statement: EXIT; Access MySQL Console as the Particular User For the purpose of testing hostmandb access, log in to MySQL as the new user, in our case minhal. sudo mysql -u minhal -p It accesses the console after entering the minhal user password when prompted: For verification, display all DBs and confirm that the hostmandb is available: SHOW DATABASES; Step 9: Configuration for Remote Access Setting up the server for supporting remote accessibility is necessary if an individual is required to access MySQL remotely. Follow these steps: Access the mysql.cnf file and modify the particular file for MySQL: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf Look for the line with the bind-address and change it to: bind-address = 0.0.0.0 Reload the MySQL service: sudo systemctl restart mysql Permit the user to have remote access: sudo mysql -u root -p GRANT ALL PRIVILEGES ON hostmandb.* TO 'minhal'@'localhost';FLUSH PRIVILEGES;EXIT; Step 10: Firewall Configuration If you have a firewall activated, you need to open the MySQL port 3306 to traffic. Set up the firewall following the below steps: Allow traffic through MySQL: sudo ufw allow mysql Now, activate the UFW on the system: sudo ufw enable Reload the firewall: sudo ufw reload Step 11: Restore and Backup Maintaining regular backups is crucial to avoiding data loss. The mysqldump utility is provided by MySQL for backup creation. To achieve this, consider these instructions: Backup a Single Database: This command employs mysqldump to create the backup of the hostmandb as a hostmandb_backup.sql file: sudo mysqldump -u root -p hostmandb> hostmandb_backup.sql Backup All Databases: For creating a backup of all databases as a file named all_databases_backup.sql with root privileges, utilize mysqldump: sudo mysqldump -u root -p --all-databases > all_databases_backup.sql Restore a Particular Database: Now, restore the hostmandb from the backup file hostmandb_backup.sql: sudo mysql -u root -p hostmandb < hostmandb_backup.sql Step 12: Optimize MySQL Operations (Optional) Depending on the workload and server resources, you can adjust settings to guarantee peak performance. These instructions will help you maximize MySQL's speed: Adjust InnoDB Buffer Pool Size: Caches for data and indexes are kept in the InnoDB buffer pool. Expanding its size can enhance its functionality. Edit the MySQL configuration file: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf The below line should be added or changed: innodb_buffer_pool_size = 1G Its size should be adjusted according to the amount of memory on the server. Enable Query Cache: The query cache stores the outcome of SELECT queries. Enabling it can enhance operations for repetitive queries. Modify the .cnf file: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf Add or edit the below lines: query_cache_type = 1query_cache_size = 64M Optimize Table Structure: Frequently optimize your customers table in hostmandb to recover wasted space and boost efficiency: USE hostmandb;OPTIMIZE TABLE customers; Analyze Operations: DB operations can be tracked and analyzed with tools like MySQL Workbench and mysqltuner. Using the command below, install mysqltuner: sudo apt install mysqltuner Run mysqltuner to get performance recommendations: sudo mysqltuner Conclusion Installing a MySQL environment is important in today's digital world. By following this instruction, you'll be able to safely install and connect to your MySQL database. This strategy not only increases security but also improves remote database maintenance efficiency. It helps to prevent breaches and ensures the confidentiality of your data. This article has given thorough instructions for the installation of MySQL's database environment on Debian. It is suggested that MySQL servers should be regularly monitored and optimized to guarantee optimum performance and dependability. In addition, Hostman offers pre-configured and ready-to-use cloud databases, including cloud MySQL. 
14 January 2025 · 8 min to read
MySQL

Creating an SSH Tunnel for MySQL Remote Access

Maintaining a secure database environment is vital in today's digital age. It helps prevent breaches and ensure the confidentiality of your information. A highly effective process for enhancing MySQL connection security is by implementing an SSH tunnel for remote access. This approach establishes an encrypted tunnel between your device and the server, ensuring data remains secure. SSH Tunneling SSH tunneling, also referred to as SSH port forwarding, enables the secure transmission of data between networks. By establishing an encrypted SSH tunnel, data can be safely transferred without the risk of exposure to potential threats. It possesses several benefits: Security: Encrypts data, keeping it safe from being seen or intercepted by others. Bypassing Restrictions: Allows access to services and resources blocked by firewalls. Flexibility: Can handle all network traffic types, fitting many uses. Types of SSH Tunneling SSH tunneling is of three types: Local Port Forwarding: It lets you redirect a port from your local machine to a destination machine using a tunnel. This is the method used in our guide. For example: ssh -L 3307:localhost:3306 your_username@your_server_ip Remote Port Forwarding: It lets you redirect a port from a remote machine to your local machine. This is useful for accessing local services from a remote machine. For example: ssh -R 9090:localhost:80 your_username@your_server_ip Dynamic Port Forwarding: It lets you create a SOCKS proxy to dynamically forward traffic through an SSH tunnel. This is useful for secure web browsing or bypassing firewalls. For example: ssh -R 9090:localhost:80 your_username@your_server_ip Prerequisites Before beginning, ensure you have: SSH client (OpenSSH, or PuTTY for Windows) MySQL server info SSH into the MySQL host machine securely. Setting Up Remote Access Go through these essential steps to securely set up remote access to your MySQL server through SSH tunnel: Step 1: Facilitate Connectivity For remote access, tune it to listen on an external IP. This allows SQL access from localhost to all IPs. Here’s how to do it: Access MySQL Config File Using a text editor, access the config file. On Ubuntu, it's typically located at: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf If the file isn't in its expected place, search for it with: sudo find / -name mysqld.cnf Edit bind-address Inside the file, find bind-address line, which is set to 127.0.0.1 by default, limiting server to local connections: Change the address to allow connections from all IP addresses by setting it to 0.0.0.0. Save changes by pressing Ctrl+X, Y to confirm, and Enter to exit. Restart MySQL Restart service to apply the updated settings: sudo systemctl restart mysql Step 2: Adjust Firewall By default, 3306 is the standard port in MySQL. To permit remote access, ensure this port is opened in your firewall settings. Tailor these steps to your specific firewall service. Open Port via UFW On Ubuntu, UFW is a pre-installed firewall utility. To allow traffic on 3306: sudo ufw allow from remote_ip to any port 3306 Substitute remote_ip with actual IP. Open Port via Firewalld On Red Hat-based and Fedora systems, Firewalld is the primary firewall tool. To open port 3306 for traffic, run these commands: sudo firewall-cmd --zone=public --add-service=mysql --permanentsudo firewall-cmd --reload The first command permanently allows MySQL traffic, and the second reloads the firewall to make the changes. Step 3: Open Your SSH Client Fire up your go-to SSH client. Opt for PuTTY on Windows, or the terminal if using macOS or Linux. Using Terminal (Linux or macOS) Implement this command: ssh -L 3307:localhost:3306 your_username@your_server_ip 3307: It's the local port your computer will listen to. localhost: It's a MySQL server address used by the SSH. It's where the service runs on the machine you're connecting to. 3306: The remote port where the server listens for incoming connections. username@server_ip: Your SSH login details. When required, verify the server's fingerprint. Confirm it matches by typing "yes" and pressing Enter.  Once confirmed, enter your SSH password if asked and press Enter for tunneling. After the tunnel is up, all traffic destined to local port 3307 will be forwarded to the remote machine in a secure fashion. Using PuTTY (Windows) Windows users can use the below-given instructions to perform tunneling: Launch PuTTY. From the left menu, direct to Connection > SSH > Tunnels. Input 3307 for Source port and localhost:3306 for the Destination field. Then hit Add. Navigate back to Session menu, enter server’s IP address and start the session using the Open button. Step 4: Connect to MySQL After setting up the tunnel, seamlessly link to the server through: sudo mysql -h localhost -P 3307 -u your_mysql_user -p Step 5: Verify the Connection Log into server and check if you can run queries: Additional Safeguards for Enhanced Security To further enhance the MySQL remote access security, consider the following: Implement Robust Passwords and Authentication Ensure using strong, unique passwords for both servers accounts. Implement key-based SSH authentication for added security. Here's how to set up SSH key authentication: Generate an SSH key pair via: ssh-keygen -t rsa -b 4096 -C "[email protected]" Copy the public key to the server via: ssh-copy-id your_username@your_server_ip Regularly Update Your Software Ensure that your server, client, and all associated software are consistently updated with the latest security patches and enhancements. This practice safeguards your system against known vulnerabilities and potential threats. Supervise and Audit Access Consistently examine access logs on both your MySQL and SSH server. Watch for any unusual activities or unauthorized attempts to gain access. Set up logging for both services: Check the SSH logs via: sudo tail /var/log/auth.log Enable and check MySQL logs by adding the below-given lines in the configuration file: [mysqld]general_log = 1general_log_file = /var/log/mysql/mysql-general.log You can view the general query log via: sudo cat /var/log/mysql/mysql-general.log To continuously monitor the log file in real time, use: sudo tail -f /var/log/mysql/mysql-general.log Implement IP Whitelisting Limit access to your MySQL by applying IP whitelisting. It ensures that connections are permitted only from specified IP addresses, thereby enhancing security: sudo ufw allow from your_trusted_ip to any port 3306 Replace your_trusted_ip with the IP address you trust. Troubleshooting Issues Here are a few common problems and solutions: Unable to Connect: Check SSH configuration and firewall rules. Ensure the SSH tunnel is correctly established and the server is reachable. Port Already in Use: Change the local forwarding port from 3307 to another available port. Authentication Errors: Verify your server's credentials. Ensure that the correct user permissions are set. MySQL Server Not Listening on Correct IP: Double-check the MySQL bind-address configuration and ensure the server is listening on the correct IP. Conclusion By adhering to this guide, you'll securely connect to your MySQL database via an SSH tunnel. This method not only boosts security but also enhances remote database management efficiency.  Regularly check your SSH tunnel setup to ensure a stable, secure connection. This practice ensures your data stays protected, providing peace of mind for seamless database operations. Hostman provides pre-configured and ready-to-use cloud databases, including cloud MySQL.
27 December 2024 · 6 min to read
MySQL

How To Use Triggers in MySQL

SQL triggers are a vital component of many database systems, allowing automated execution of specific actions when predefined events occur. Triggers act as responsive mechanisms within a database, ensuring consistency and enabling automation of repetitive tasks. These event-driven procedures are particularly effective for handling operations triggered by changes such as  INSERT,  UPDATE, or DELETE in a table. By using triggers, database administrators and developers can enforce rules, maintain logs, or even invoke complex processes with minimal manual intervention. Let’s begin by defining an example database for a small online store to understand how triggers work in practice: -- Let’s create a databse called SHOP ; CREATE DATABASE SHOP ; USE SHOP ; -- Now we create the Products table CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Stock INT, Price DECIMAL(10, 2) ); -- Then the StockAudit table CREATE TABLE StockAudit ( AuditID INT AUTO_INCREMENT PRIMARY KEY, ProductID INT, ChangeType VARCHAR(10), QuantityChanged INT, ChangeTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); Classification of SQL Triggers SQL triggers can be classified based on their scope and timing. Row-level triggers are executed once for every row affected by a database operation, making them adequate for detailed tracking of data changes. For example, when updating inventory quantities for multiple products, a row-level trigger can record changes for each product individually. Conversely, statement-level triggers run once for an entire operation, regardless of how many rows are affected. These are useful for performing global checks or logging summary information. Triggers can also be categorized by their execution timing relative to the triggering event. Before triggers are executed prior to the event, often to validate or modify data before it is written to the database. After triggers execute after the event, making them ideal for tasks such as auditing or enforcing referential integrity. This is an example of a row-level AFTER INSERT trigger which logs new product additions: -- The DELIMITER command is used to change the statement delimiter from ; to // while defining the trigger DELIMITER // CREATE TRIGGER LogNewProduct AFTER INSERT ON Products FOR EACH ROW BEGIN INSERT INTO StockAudit (ProductID, ChangeType, QuantityChanged) VALUES (NEW.ProductID, 'ADD', NEW.Stock); END; // DELIMITER ; How Triggers Operate in a Database Triggers are defined by specifying the event they respond to, the table they act upon, and the SQL statements they execute. When a trigger’s event occurs, the database automatically invokes it, running the associated logic seamlessly. This behavior eliminates the necessity for external application code to maintain consistency. For instance, consider a scenario where we need to prevent negative stock levels in our inventory. We can achieve this with a BEFORE UPDATE trigger that validates the updated stock value: DELIMITER // -- Trigger to prevent negative stock values CREATE TRIGGER PreventNegativeStock BEFORE UPDATE ON Products FOR EACH ROW BEGIN -- Check if the new stock value is less than 0 IF NEW.Stock < 0 THEN -- Raise an error if the stock value is negative SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock cannot be negative'; END IF; END; // DELIMITER ; This guarantees that no changes violating the business rules are applied to the database. Practical Advantages of Using Triggers Triggers offer numerous advantages, such as enforcing business logic directly within the database layer. This ensures that data integrity is preserved across all applications accessing the database, reducing the need for repetitive coding. By centralizing critical logic, triggers simplify maintenance and enhance consistency. For example, a trigger can automate logging of stock adjustments, saving developers from implementing this functionality in multiple application layers. Consider this AFTER UPDATE trigger: DELIMITER // -- Trigger to log stock adjustments after an update on the Products table CREATE TRIGGER LogStockAdjustment AFTER UPDATE ON Products FOR EACH ROW BEGIN -- Insert a record into the StockAudit table with the product ID, change type, and quantity changed INSERT INTO StockAudit (ProductID, ChangeType, QuantityChanged) VALUES (OLD.ProductID, 'ADJUST', NEW.Stock - OLD.Stock); END; // DELIMITER ; This trigger automatically records every stock change, streamlining audit processes and ensuring compliance. Challenges and Considerations While triggers are powerful, they are not without challenges. Debugging triggers can be tricky since they operate at the database level and their effects may not be immediately visible. For example, a misconfigured trigger might inadvertently cause cascading changes or conflicts with other triggers, complicating issue resolution. Performance is another critical consideration. Triggers that are not well designed can slow down database operations, especially if they include resource-intensive logic or are triggered frequently. For instance, a trigger performing complex calculations on large datasets can bottleneck critical operations like order processing or stock updates. To mitigate these challenges, it is advisable to: Keep trigger logic concise and efficient. Use triggers sparingly and only for tasks best handled within the database. Test triggers extensively in controlled environments before deployment. Real-World Example: Cascading Triggers Cascading triggers can ensure data integrity across related tables. Consider a database with Orders and OrderDetails tables. When an order is deleted, it is essential to remove all associated details: DELIMITER // -- Trigger to cascade delete order details after a delete on the Orders table CREATE TRIGGER CascadeDeleteOrderDetails AFTER DELETE ON Orders FOR EACH ROW BEGIN -- Delete the corresponding records from the OrderDetails table DELETE FROM OrderDetails WHERE OrderID = OLD.OrderID; END; // DELIMITER ; This ensures that orphaned records are automatically removed, maintaining database consistency without manual intervention. However, cascading triggers require careful documentation to avoid unintended interactions. Optimizing Trigger Performance To prevent performance bottlenecks, triggers should handle minimal logic and avoid intensive operations. For tasks requiring significant processing, consider using scheduled jobs or batch processes instead. For example, instead of recalculating inventory levels on every update, a nightly job could summarize stock levels for reporting purposes. Here’s a simplified trigger that avoids complex calculations: DELIMITER // -- Trigger to log stock changes after an update on the Products table CREATE TRIGGER SimpleStockLog AFTER UPDATE ON Products FOR EACH ROW BEGIN -- Check if the new stock value is different from the old stock value IF NEW.Stock <> OLD.Stock THEN -- Insert a record into the StockAudit table with the product ID, change type, and quantity changed INSERT INTO StockAudit (ProductID, ChangeType, QuantityChanged) VALUES (NEW.ProductID, 'UPDATE', NEW.Stock - OLD.Stock); END IF; END; // DELIMITER ; Conditional Logic and Business Rules Conditional logic within triggers enables dynamic enforcement of business rules. For example, a trigger can adjust discounts based on stock availability: DELIMITER // -- Trigger to adjust discount based on stock levels after an update on the Products table TRIGGER AdjustDiscount AFTER UPDATE ON Products FOR EACH ROW BEGIN -- Check if the new stock value is greater than 100 IF NEW.Stock > 100 THEN -- Set the discount to 10 if the stock is greater than 100 UPDATE Products SET Discount = 10 WHERE ProductID = NEW.ProductID; ELSE -- Set the discount to 0 if the stock is 100 or less UPDATE Products SET Discount = 0 WHERE ProductID = NEW.ProductID; END IF; END; // DELIMITER ; This dynamic adjustment ensures that promotions align with inventory levels. Conclusion SQL triggers are indispensable for automating tasks, enforcing rules, and maintaining data integrity within a database. While they offer significant benefits, their design and implementation require careful consideration to avoid performance issues and unintended consequences. By adhering to best practices, such as keeping triggers simple, testing thoroughly, and documenting dependencies, developers can harness their full potential. Properly implemented triggers can elevate database management, making operations more efficient and reliable. Hostman provides pre-configured and ready-to-use cloud databases, including cloud MySQL.
24 December 2024 · 7 min to read

Do you have questions,
comments, or concerns?

Our professionals are available to assist you at any moment,
whether you need help or are just unsure of where to start.
Email us
Hostman's Support