Sign In
Sign In

The UPDATE Command: How to Modify Records in a MySQL Table

The UPDATE Command: How to Modify Records in a MySQL Table
Hostman Team
Technical writer
MySQL
12.12.2024
Reading time: 11 min

Updating data in databases is a critical task when working with MySQL. It involves modifying the values of existing records in a table. Updates can range from modifying fields in a group of rows (or even all rows in a table) to adjusting a specific field in a single row.

Understanding the syntax for updating data is essential for effectively working with both local and cloud databases.

The key command for modifying records in a MySQL database table is UPDATE. Updates occur sequentially, from the first row to the last. Depending on the type of update, there are two syntax options for the UPDATE statement in MySQL.

Syntax for Updating a Single Table

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    WHERE where_condition
    ORDER BY ...
    LIMIT row_count;

Parameters:

  • Required:

    • SET assignment_list: Specifies which columns to modify and how (assignment_list is the list of columns and their new values).

  • Optional:

    • LOW_PRIORITY: If specified, the UPDATE is delayed until no other user is reading data from the table.

    • IGNORE: Ensures the UPDATE continues even if errors occur. Rows with duplicate values in unique key columns are not updated.

    • WHERE where_condition: Specifies the conditions for selecting rows to update. If omitted, all rows in the table will be updated.

    • ORDER BY: Determines the order in which rows are updated.

    • LIMIT row_count: Limits the number of rows updated (row_count specifies the number of rows). This count applies to rows matching the WHERE condition, regardless of whether they are actually modified.

Syntax for Updating Multiple Tables

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    WHERE where_condition;

Parameters:

  • table_references: Specifies the tables to update. Changes are applied as defined in assignment_list.

  • ORDER BY and LIMIT are not allowed when updating multiple tables.

  • Other optional parameters (LOW_PRIORITY, IGNORE, WHERE) behave the same as for a single-table update.

Note that when updating multiple tables, there is no guarantee that updates will occur in a specific order.

Creating a Test Database

Let’s create a database for a bookstore that sells rare and antique books from around the world. The table will have four tables: author, genre, book, and sales.

CREATE TABLE author (
   id INT PRIMARY KEY AUTO_INCREMENT,
   author_name VARCHAR(50) NOT NULL
);
INSERT INTO author (author_name) 
VALUES ('Leo Tolstoy'), 
       ('Franz Kafka'), 
       ('Nikolai Gogol'),
       ('William Shakespeare'),
       ('Homer');

CREATE TABLE genre (
   id INT PRIMARY KEY AUTO_INCREMENT, 
   genre_name VARCHAR(30) NOT NULL
);
INSERT INTO genre (genre_name) 
VALUES ('Realist novel'),
       ('Dystopian novel'),
       ('Picaresque novel'),
       ('Epic poetry');

CREATE TABLE book (
   book_id INT PRIMARY KEY AUTO_INCREMENT, 
   title VARCHAR(50), 
   author_id INT NOT NULL, 
   genre_id INT,
   price DECIMAL(8,2) NOT NULL, 
   amount INT DEFAULT 0, 
   FOREIGN KEY (author_id) REFERENCES author (id),
   FOREIGN KEY (genre_id) REFERENCES genre (id)
);
INSERT INTO book (title, author_id, genre_id, price, amount) 
VALUES ('Anna Karenina', 1, 1, 650.00, 15),
       ('The Castle', 2, 2, 570.20, 6),
       ('Dead Souls', 3, 3, 480.00, 2),
       ('Iliad', 5, 4, 518.99, 4),
       ('Odyssey', 5, 4, 518.99, 7);

CREATE TABLE sales (
   id INT PRIMARY KEY AUTO_INCREMENT,
   book_id INT NOT NULL,
   count INT NOT NULL,
   cost DECIMAL(8,2) NOT NULL,
   FOREIGN KEY (book_id) REFERENCES book (book_id)
);

We will get the following tables.

Table: book

1

Here, we have the columns:

  • book_id: Unique book identifier.

  • title: The book's title.

  • author_id: Author identifier (foreign key to author).

  • genre_id: Genre identifier (foreign key to genre).

  • price: Price of the book per unit.

  • amount: Number of books in stock.

The genre table will have the following content: 

id

genre_name

1

Realist novel

2

Dystopian novel

3

Picaresque novel

4

Epic poetry

And our author table will look like this:

id

author_name

1

Leo Tolstoy

2

Franz Kafka

3

Nikolai Gogol

4

William Shakespeare

5

Homer

Table: sales

4

The columns here are:

  • id: Unique identifier for the transaction.

  • book_id: Unique book identifier (foreign key to book).

  • count: Number of books sold.

  • cost: Total cost of the purchased books.

Data Update Operations

Now, having created a sample database, we will demonstrate the execution of various data update operations using the UPDATE statement and other commands in MySQL.

1. Updating All Rows

If we omit the WHERE clause in an UPDATE statement, all rows in the table will be updated. For example, suppose there is a promotion in a bookstore where all books are priced at a fixed rate of 500. The query would look like this:

UPDATE book
SET price = 500;

Resulting Table:

5

If we try to assign a value that is already in a column, MySQL will not update it.

If we want to assign a NULL value to a column defined as NOT NULL will, the query will return an error: 

Column 'name_column' cannot be null

Using the IGNORE parameter forces the value to default:

  • 0 for numeric types,

  • "" for string types,

  • default dates (e.g., 0000 for YEAR, 0000-00-00 00:00:00 for DATETIME).

2. Updating Rows with a Condition

Updating all rows is rare; typically, updates are performed based on specific conditions. For instance, to apply a discount on books with fewer than 5 copies in stock:

UPDATE book 
SET price = 300
WHERE amount < 5;

Resulting Table:

6

3. Updating Values Using Expressions

Columns can be updated using expressions instead of static values. For example, we can apply a 15% discount on Russian classics (author IDs 1 and 3):

UPDATE book 
SET price = price * 0.85
WHERE author_id IN (1, 3);

Resulting Table:

7

Updates are executed from left to right. For example, the query below increments the amount by 1 and then doubles it:

UPDATE book 
SET amount = amount + 1, amount = amount * 2;

Resulting Table:

8

4. Updating with DEFAULT

We can update column values to their default values (DEFAULT), which are defined during the creation or modification of the table. To find out the default values used in our table, we can execute the following query:

DESC book;

Table Structure:

9

Next, we reset the values of the amount column to its default value. Since the default value for amount is 0, all rows will now have amount set to 0:

UPDATE book 
SET amount = DEFAULT;

Resulting Table:

10

5. Updating Multiple Columns

We can update multiple columns in a single query. For example, let's change the price and amount values for rows where book_id < 4:

UPDATE book 
SET price = price * 0.9,
    amount = amount - 1
WHERE book_id < 4;

Resulting Table:

11

6. Using LIMIT

The LIMIT clause allows us to restrict the number of rows to be updated. For instance, we update only the first row where genre_id = 4:

UPDATE book 
SET price = 100
WHERE genre_id = 4
LIMIT 1;

The table contains two rows with genre_id equal to 4, but since we specified LIMIT 1, only one will be updated.

Resulting Table:

12

Note: The LIMIT N parameter does not guarantee that exactly N rows will be updated—it processes the first N rows matching the WHERE condition, regardless of whether the rows are updated or not.

7. Updating Multiple Tables

In MySQL, it is possible to update multiple tables simultaneously. For example, update the amount in the book table and set the author_name to "-" in the author table for specific conditions:

UPDATE book, author
SET amount = amount + 3,
    author.author_name = '-'
WHERE book.book_id = 4 AND author.id = 4;

Resulting book Table:

13

While the author table will look like this:

id

author_name

1

Leo Tolstoy

2

Franz Kafka

3

Nikolai Gogol

4

William Shakespeare

5

Homer

8. Updating Tables with a Join (INNER JOIN)

While performing updates, we can also join tables using the INNER JOIN command.

UPDATE book b INNER JOIN author a 
ON b.author_id = a.id
SET b.title = CONCAT(b.title, ' (', a.author_name,')');

15

Specifying INNER is not mandatory, as this type of join is used by default. We can rewrite the query as follows and get the same result:

UPDATE book, author a 
SET b.title = CONCAT(b.title, ' (', a.author_name,')')
WHERE b.author_id = a.id;

9. Updating Tables with a Join (LEFT JOIN)

We can also use a LEFT JOIN. In this case, we must specify LEFT JOIN in the query. 

For example, we can update the stock quantity of books after a purchase. Let's add two rows to the sales table:

INSERT INTO sales (book_id, count, cost) 
VALUES (1, 3, (SELECT price FROM book WHERE book_id = 1)*3),
(3, 1, (SELECT price FROM book WHERE book_id = 3)*1);

The store sold 3 copies of 'Anna Karenina' and 1 copy of 'Dead Souls'. Let's execute the query:

UPDATE book LEFT JOIN sales on book.book_id = sales.book_id
SET amount = amount - count
WHERE sales.book_id is not NULL;

We can see that now we have less copies of these books:

16

If we try not to use LEFT JOIN, we will encounter the error:

Out of range value for column 'amount' at row 3

This happens because amount cannot be negative. Alternatively, if we add IGNORE, the result will be:

17

As we can see, we reduced the quantity in all rows by three books, which is not what we wanted.

10. Updating with CASE, IF, IFNULL, COALESCE

When updating a table, it is also possible to use conditional operators such as CASE, IF, and others.
The CASE function evaluates a set of conditions and, depending on the result, returns one of the possible outcomes. The syntax for using CASE and WHEN in an UPDATE statement in MySQL is as follows:

UPDATE book
SET price = 
  CASE genre_id
  WHEN 1 THEN 100
  WHEN 2 THEN 150
  ELSE price 
  END;

In this case, if the book has genre 1, we set the price to 100, and if the genre is 2, the price is set to 150.

18

The IF function returns one of two values depending on the result of a conditional expression. If the book has genre 4, we decrease its price by 200; otherwise, we leave the price unchanged.

UPDATE book
SET price = IF (genre_id = 4, price-200, price);

The result:

19

The IFNULL function checks the value of an expression – if it is NULL, a specified value is returned; otherwise, the expression itself is returned. Let's assume that one of the amount values is NULL:

20

Let's check all the values in the amount column, and if any NULL values are found, we will replace them with 0:

UPDATE book
SET amount = IFNULL(amount, 0);

Resulting Table:

21

The COALESCE function is quite similar to IFNULL. The main difference is that this function can accept multiple values (two or more). Like IFNULL, it returns the first value that is not NULL.

To see how this works, let's create a table like this:

id

col1

col2

col3

col4

1

 

val12

val13

val14

2

   

val23

val24

3

     

val34

And run the query:

UPDATE test_table
SET col4 = COALESCE(col1, col2, col3, 'no value');

We will get:

id

col1

col2

col3

col4

1

 

val12

val13

val12

2

   

val23

val23

3

     

no value

11. Updating with Sorting

Sorting can help when updating a field with a unique key. If we want to shift our id values by 1, updating the first row would result in two rows having id = 2, which will cause an error. However, if we add ORDER BY and start updating from the end, the query will execute successfully:

UPDATE book
SET book_id=book_id+1 

We will get:

24

12. Updating Based on Data from Other Tables

In MySQL, when working with UPDATE, it is possible to use nested SELECT and FROM commands in the WHERE condition. In the following example, we first retrieve the id of the 'Epic poetry' genre, then use the retrieved value to select the rows for updating the table.

UPDATE book
SET amount = 0
WHERE genre_id = (
   SELECT id
   FROM genre 
   Where genre_name = 'Epic poetry'
);

25

Alternatively, we can select the values that need to be changed using the query:

UPDATE book
SET price = (
SELECT MIN (cost)
FROM sales)
WHERE amount < 5;

We are updating the price values of all books whose stock quantity is less than 5, setting the price to the minimum selling amount.

26

The minimum selling amount is 480:

27

It is not possible to update the table by selecting values from the same table in a subquery. However, there is a trick we can use – we can join the table with itself:

UPDATE book AS book_1
    INNER JOIN(
        SELECT genre_id, MIN(amount) AS min_amount
        FROM book
        GROUP BY genre_id
    ) AS book_2 ON book_1.genre_id = book_2.genre_id
SET book_1.amount = book_2.min_amount;

In this case, the subquery creates a temporary table for the join and closes it before the UPDATE begins execution.

The subquery finds the minimum quantity of books for each genre, which is then used to update the amount column. In our table, only genre 4 has more than one row. The values in both rows should be replaced with the minimum value for that genre, which is 4.

We will get:

28

Another option is using SELECT FROM SELECT:

UPDATE book AS book_1
SET book_1.price = (SELECT MIN(price) AS min_price FROM (
SELECT price 
FROM book) as book_2);

In this case, a temporary table is also created. However, only a single value is assigned to all rows.

Conclusion

We have covered the features of using the UPDATE statement in MySQL in as much detail as possible and covered simple scenarios with practical examples.

MySQL
12.12.2024
Reading time: 11 min

Similar

MySQL

How to Show Users in MySQL

MySQL stands as a widely recognized relational database system, extensively utilized for handling structured data. It provides administrators with robust utilities to oversee users' accounts effectively. Whether the goal is to retrieve a full user list, check access rights, or filter specific accounts, MySQL offers multiple ways to accomplish these tasks. Understanding these methods can help ensure better control over database security and access management. In this write-up, we’ll explore all possible approaches to show users in MySQL. Prerequisites Before proceeding, ensure you meet these prerequisites: MySQL is set up on your machine. Superuser permissions for MySQL (root or an account with sufficient rights). A MySQL interface (such as MySQL Shell, Workbench, or a command terminal with the MySQL CLI tool). Understanding MySQL Users In MySQL, accounts represent users who can interact with the database by executing tasks like reading, updating, or deleting data. Each account consists of a username and a host, which specifies the allowed connection source. For instance, 'user'@'localhost' restricts access to the same computer where MySQL is running, whereas 'user'@'%' allows access from any IP address(location). We can handle user accounts with simple commands, as illustrated below: Task Command Create User CREATE USER 'anees'@'localhost' IDENTIFIED BY 'password'; Grant Permissions GRANT ALL PRIVILEGES ON mydb.* TO 'anees'@'localhost'; See Existing Users SELECT user, host FROM mysql.user; Delete User DROP USER 'anees'@'localhost'; MySQL controls user access using privileges, ensuring that each account can execute only permitted actions, thereby maintaining database security. Different Methods to Show MySQL Users MySQL offers multiple ways to view user accounts, each designed for a particular need. Whether you require a basic user list, detailed account insights, or filtered results, MySQL offers flexible options to fetch user details. Common approaches include querying the mysql.user table, executing SHOW GRANTS to review privileges, and using SELECT statements with filtering conditions. These methods help database administrators effectively manage accounts, maintain secure access, and enforce proper control within the database. 1. Querying the mysql.user Table The mysql.user is MySQL's built-in system table that stores all user account details, including usernames, host specifications, authentication methods, and assigned privileges. Administrators can retrieve data from this table to gain a comprehensive view of all accounts and their respective access rights. It plays a crucial role in auditing permissions, checking authentication plugins, and filtering users based on defined parameters. However, accessing this table requires administrative rights, and the privilege data may appear intricate for those unfamiliar with its structure. Despite this, it remains one of the most detailed and effective tools for handling user accounts in MySQL. Here’s how to fetch all users using this table: SELECT User, Host FROM mysql.user; The User column depicts account names, while the Host column indicates the originating host that allows user connections: 2. Retrieving Unique Users with DISTINCT In MySQL, the DISTINCT clause can be applied within a SELECT statement on the mysql.user table to retrieve distinct user accounts. Since MySQL associates each account with both a username and a host, the same username may appear multiple times with varying host values. Utilizing DISTINCT removes redundant usernames, yielding a streamlined list of unique accounts. This approach is particularly beneficial for obtaining a summarized view of all database users, especially in environments where multiple hosts are involved: SELECT DISTINCT User FROM mysql.user; This technique is ideal when you require only the usernames without including host-related details. 3. Extracting User Privileges with SHOW GRANTS The SHOW GRANTS statement in MySQL helps us extract the permissions granted to a particular user. SHOW GRANTS presents access rights in a structured format that mirrors the GRANT commands used to grant those privileges. This feature makes it an essential tool for reviewing user privileges and ensuring security compliance. Employ this command to fetch a particular user alongside its access rights: SHOW GRANTS FOR 'user'@'host'; Substitute the username and host with the relevant user information, as depicted in the snippet below: 4. Checking Users via MySQL Workbench This method offers a graphical interface (GUI) for handling MySQL user accounts, making it suitable for those who prefer a visual approach over command line operations (CLI). MySQL Workbench enables users to view existing accounts, assigned roles, and granted privileges via its Users and Privileges section. This structured view reduces the risk of errors compared to manually running SQL commands, making it beginner-friendly.  To view user accounts in MySQL Workbench, launch the application and establish a connection to your MySQL server: Next, in the Navigator panel, choose Administration, expand the Management section, and click the "Users and Privileges" to access the user management window: Within the Users tab, a list of existing MySQL user accounts alongside their corresponding hosts will be displayed: We can choose a user to review or modify its privileges, authentication method, and other settings. Fetching Logged-In User Details in MySQL The SELECT USER(); query in MySQL fetches the current session’s authenticated user, including the originating host. It’s a fast and efficient way to examine the active user without needing additional tools or administrative rights. This method works on all MySQL installations, including remote connections, and has minimal system overhead. However, it only exhibits the authenticated user and lacks information regarding roles or permissions: SELECT USER(); The resultant outcome demonstrates that root@localhost is currently logged in user on our system: Alternatively, we can employ the below-listed query to fetch the current user details: SELECT CURRENT_USER(); How Can We Check the Existence of a Particular User in MySQL? To confirm if a particular MySQL account is available, we can query the mysql.user table with the EXISTS clause. The succeeding command checks for a user’s presence: SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = 'username' AND host = 'host'); It returns one if the account is present and zero if not: If you are uncertain about the host, you may exclude the host condition or execute the command below: SELECT User, Host FROM mysql.user WHERE User = 'anees'; If the desired account is found, it depicts the username and hostname; otherwise, it shows "Empty Set": Effective Ways to Display MySQL Accounts Here are key guidelines to follow for efficient handling and presenting MySQL accounts to maintain security and streamline user administration: To efficiently handle MySQL accounts, ensure you have administrative privileges before running commands like retrieving data from the mysql.user table or utilizing SHOW GRANTS. Avoid revealing confidential details such as passwords by only selecting the necessary columns, like User and Host, rather than employing "SELECT *". When searching for certain user accounts, apply WHERE to refine the results and avoid fetching irrelevant data. To obtain a refined list of distinct accounts, apply DISTINCT, as MySQL records users with both their usernames and hosts. To depict a user's permissions in a clear format, execute SHOW GRANTS FOR 'username'@'hostname';.  If you're not comfortable with the command line interface, utilize MySQL Workbench, which provides a user-friendly interface to handle accounts. Ensure limited access to the mysql.user table to protect sensitive data, and periodically audit user accounts and privileges to maintain security. Conclusion Effectively handling MySQL users/accounts is crucial for enhancing security and proper access control within your database. Whether you're using SQL queries like SELECT and SHOW GRANTS, or MySQL Workbench for a more intuitive experience, multiple approaches are available for listing and managing accounts. It's important to consider optimal practices, such as using appropriate privileges, limiting data exposure, and regularly auditing user accounts, to ensure your MySQL setup remains secure. In this write-up, we explained how to confidently manage users, monitor permissions, and uphold the reliability of your database system.
13 February 2025 · 7 min to read
MySQL

How to Create a MySQL Database Dump

MySQL is the most popular relational database management system that performs various operations with tables, such as adding, deleting, searching, sorting, and outputting data based on user queries. It's important to understand that MySQL controls databases but is not itself a database. Therefore, MySQL and the database are separate entities: MySQL is a program that operates on information. The database is the information recorded on a hard disk. Based on this architecture, MySQL supports exporting information — creating a database dump. This functionality allows several useful operations: Database Backup: Unexpected situations when using cloud (or local) servers can lead not only to system failures but also to data loss. Therefore, it’s important to regularly create database dumps, which can be stored on other secure storage devices. Database Transfer from One Server to Another: Manually copying database elements may be challenging or impossible when migrating from one server to another. A dump makes it possible to transfer data quickly. A database dump is essentially a sequential set of SQL instructions that create an exact copy of the original database, including both its structure and content. This guide will cover the primary methods for creating a database dump and importing it back into MySQL to restore data. Preparing a Test Database We will create a cloud database to test the examples in this guide. If you already have a MySQL database where you can test the dump creation function, you can skip this step. In the Hostman panel, we will create a MySQL 8 database, leaving all other parameters as default. You can connect to the cloud database via a terminal. The necessary command can be copied from the control panel. Let's connect to our database: mysql -u USER -p'PASSWORD' -h HOST -P 3306 -D DATABASE For example, a real connection command might look like this: mysql -u gen_user -p'sU}NEyx#<2P~\e' -h 91.206.179.29 -P 3306 -D default_db Next, we need to create a simple table consisting of three columns: CREATE TABLE People ( id INT, name VARCHAR(255) NOT NULL, bord DATE ); And populate it with some values: INSERT INTO People VALUES (120, 'Natalie', NOW()), (121, 'Meredith', NOW()), (122, 'James', NOW()); This fills the new database so that we can later create a dump from it. By the way, on the Users tab of the database management page, there are buttons that open interfaces for graphical MySQL database administration tools — phpMyAdmin and Adminer. Method 1: Console Terminal A more traditional but less interactive way to create a MySQL database dump is by using the appropriate command in a console terminal. To do this, you need to connect to MySQL via an SSH connection and then enter the dump creation command: mysqldump -u USER -p'PASSWORD' -h ADDRESS -P PORT DATABASE > FILE Let's break down each of the specified parameters: USER: The username used to authenticate in MySQL. PASSWORD: The password for the user to authenticate in MySQL. ADDRESS: The IP address of the remote MySQL server. PORT: The port of the remote MySQL server. DATABASE: The name of the database to be dumped. FILE: The name of the file where the database dump will be saved on the local machine. There are two possible ways to create a dump via the console: Local MySQL: The dump is created from a database located on a local MySQL server. In this case, we don’t need to specify the ADDRESS and PORT parameters. Remote MySQL: The dump is created from a database located on a remote MySQL server. In this case, you need to specify ADDRESS and PORT. Local MySQL dump command example: mysqldump -u admin -p'qwerty123' default_db > just_dump.sql Remote MySQL dump command example: mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db > just_dump.sql In both cases, for security reasons, you can omit the explicit password specification — this way, the system will prompt you to enter the password manually: mysqldump -u admin -p default_db > just_dump.sqlmysqldump -u admin -p -h 91.206.179.29 -P 3306 default_db > just_dump.sql Warnings and Errors After executing the command, several warnings and errors may appear in the console output. Let’s break down each message in detail. Password Security Warning The first warning from MySQL notifies you about the insecurity of using a password as an explicit parameter: mysqldump: [Warning] Using a password on the command line interface can be insecure. To suppress this warning, use the -p flag without specifying the password directly. Global Transaction Identifier (GTID) Warning The next warning concerns the inclusion of the Global Transaction Identifier (GTID) in the resulting dump and suggests disabling it with the --set-gtid-purged=OFF flag: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. GTID (Global Transaction Identifier) is a unique 128-bit identifier associated with each transaction, which improves overall data consistency. Disabling GTID may lead to data inconsistency (for example, due to duplication of certain SQL statements). Data Dump Consistency Warning Another GTID-related warning indicates that the dump operation is not atomic: Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump.In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --master-data. This means that database changes performed by other applications during the dump creation may be missing, leading to data inconsistency. To avoid this issue, use one of the following flags: --single-transaction to create the dump within a single transaction. --lock-all-tables to block any other operations on the database during the dump. Access Denied Error You might encounter an error preventing the dump creation due to insufficient privileges: mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces Even if the user specified in the command has all database privileges, they may lack the global PROCESS privilege. To grant this privilege, execute the following command: GRANT PROCESS ON *.* TO 'admin'@'localhost'; However, this is not the best solution from a security perspective. Instead of granting global privileges, it's better to use the --no-tablespaces flag during the dump command execution. With all the additional flags, the dump creation command will look like this: mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql In this case, only one harmless warning will remain about explicitly specifying the password: mysqldump: [Warning] Using a password on the command line interface can be insecure. Non-existent Database Error If you accidentally specify the name of a non-existent database, an unclear error will appear denying access to the database for the specified user: ERROR 1044 (42000): Access denied for user 'admin'@'%' to database 'default_db' This can cause confusion, so always double-check the accuracy of the database name specified in the command. Dump File After successfully executing the dump command, you can check the file system using: ls You should see the corresponding database dump file: just_dump.sql  resize.log  snap Although you can open this file in any text editor, its size may be quite large, especially if the original database contained a lot of information: cat just_dump.sql At the beginning of the file, there is information about the created dump, followed by SQL instructions: -- MySQL dump 10.13 Distrib 8.0.40, for Linux (x86_64) -- -- Host: 37.220.80.65 Database: default_db -- ------------------------------------------------------ -- Server version 8.0.22-13 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; ... /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2025-01-19 5:33:16 The output file doesn't have to be saved in the current directory; you can specify any other directory: mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --no-tablespaces --set-gtid-purged=OFF --single-transaction > /tmp/just_dump.sql In this case, we create the dump file just_dump.sql in the /tmp directory. Dumping Multiple Databases In real projects, MySQL often handles multiple databases. You can use a special flag to dump all existing databases: mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 --all-databases --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql This command differs from the previous one only in that the --all-databases flag is specified instead of a specific database name. Alternatively, you can list the databases you want to include in the dump: mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 db_first db_second db_third --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql Structure Without Data You can create a dump containing only the database structure (table schemas) without any data by using the --no-data flag: mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --no-data --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql Specific Tables Instead of dumping an entire MySQL database, you can dump only specific tables by listing their names after the database name: mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db table1 table2 --no-data --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql On the other hand, you can dump a database excluding specific tables using the --ignore-table parameter: mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --ignore-table=default_db.logs --no-data --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql Note that the table name must always be preceded by the database name and separated by a dot. To exclude multiple tables, list each one with the --ignore-table option: mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --ignore-table=default_db.table1 --ignore-table=default_db.table2 --no-data --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql Character Encoding In some cases, it may be necessary to explicitly specify the character encoding for the dump: mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --no-tablespaces --set-gtid-purged=OFF --single-transaction --default-character-set=utf8 > just_dump.sql Typically, UTF-8 is the preferred character encoding. Archiving the Dump Sometimes it’s useful to compress the dump immediately after creation. You can do this by piping the mysqldump output into gzip, then saving the compressed archive: mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --no-tablespaces --set-gtid-purged=OFF --single-transaction | gzip > just_dump.sql.gz If you check the current directory with the ls command, you’ll see the compressed dump: just_dump.sql  just_dump.sql.gz  resize.log  snap Restoring Data A database dump is usually created to restore data in the future, for example, in case of data loss or server migration. To load the database dump into MySQL and restore data, use the following command: mysql -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db < just_dump.sql If the dump file size is too large, MySQL may have a default limit that prevents loading it. To adjust the maximum allowed dump size, you can use the --max_allowed_packet flag: mysql -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --max_allowed_packet=64M < just_dump.sql In this example, the maximum allowed dump size is set to 64 MB. mysql -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db < just_dump.sqlmysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 --all-databases --no-tablespaces --set-gtid-purged=OFF > just_dump.sql Method 2: Using phpMyAdmin If you're using phpMyAdmin, creating a database dump can be done through the graphical interface without manually executing commands — phpMyAdmin handles everything for you. Log In to phpMyAdmin. Open the phpMyAdmin interface and log in with your credentials.  Select the database. In the left sidebar, choose the database you want to export. This will open a page displaying the list of existing tables within the selected database. Configure the export. Click the Export button. It will take you to a dedicated page to configure the database export (dump). You can also access the export page from the phpMyAdmin home page, but doing so may not display all databases available for export. It's better to first navigate to the specific database and then click Export. Note that phpMyAdmin allows exporting only databases that contain tables. Empty databases cannot be exported. There are two export options in phpMyAdmin: Quick Export. It creates the dump using default export settings. Custom Export. It Allows you to manually configure the export settings, such as excluding specific tables, changing character encoding, and adjusting format options. phpMyAdmin supports exporting to various formats beyond just SQL, such as PDF, JSON, CSV, YAML, and others. The configuration options for creating a dump in phpMyAdmin are more user-friendly and visually intuitive than command-line flags. Start the export. Once you've configured all the export parameters, scroll down and click the Export button. The dump file will be generated and downloaded through your browser. Method 3: Using Adminer Creating a database dump in Adminer is very similar to phpMyAdmin. In fact, Adminer’s graphical interface is even simpler. Log In to Adminer: Start by logging into Adminer, then navigate to the export page by clicking the Export link in the left sidebar. Configure the Export. Adminer does not have predefined export types, so the system immediately offers all configuration options. You can select specific database tables to include in the dump. The dump file can be either saved (in a specific format or as a GZIP archive) or opened in a new window for manual copying of SQL instructions. Conclusion The native way to create a MySQL database dump, without requiring additional tools, is by using the mysqldump command with additional parameters. An alternative is to use visual database management tools with graphical interfaces. Utilities like phpMyAdmin or Adminer simplify database interactions by providing a user-friendly and interactive environment. This is particularly useful for those who are not well-versed in SQL syntax, turning tasks such as creating a dump into a series of simple mouse clicks.
13 February 2025 · 12 min to read
MySQL

How to Import and Export Databases in MySQL or MariaDB

Database management is a crucial aspect of Linux server and web application administration. Importing and exporting databases are essential tasks for DevOps and system administrators. At a minimum, developers should know how to back up databases and transfer them between servers. This guide explains how to import and export database dumps in MySQL or MariaDB on a Linux server (using Ubuntu as an example). Working with Databases MySQL and MariaDB are popular relational database management systems (RDBMS) used for storing data in large applications. MariaDB is a fork of MySQL developed by its original creators due to licensing concerns following Oracle's acquisition of MySQL. Both MariaDB and MySQL share identical or similar APIs and operating mechanisms. Creating a Database Connect to MySQL or MariaDB with root privileges: For MySQL: mysql -u root -p   For MariaDB: mariadb -u root -p   Create a database (if it doesn't already exist): CREATE DATABASE IF NOT EXISTS <database_name>; Viewing Databases To see the list of available databases: SHOW DATABASES; Switching Databases To switch to a specific database: USE <database_name>; Viewing Tables To list all tables in the selected database: SHOW TABLES; Common SQL Commands Creating a table: CREATE TABLE IF NOT EXISTS users (  user_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,  username VARCHAR(100) NOT NULL); This creates a table named users with fields user_id and username. Inserting data into the table: INSERT INTO users (username) VALUES ('John Doe'); This adds a new row to the users table. Selecting all rows from the table: SELECT * FROM users; Monitoring MySQL/MariaDB status To check the server's global status and statistics: SHOW GLOBAL STATUS; Exporting Databases Exporting data from MySQL or MariaDB can be efficiently done using the mysqldump CLI utility or third-party tools like phpMyAdmin. The mysqldump utility allows you to save your database as an SQL dump, which contains the necessary commands for creating columns and populating them with data. This dump file can be easily managed, imported, or transferred. You will need: A database you want to export. User credentials with at least read access to the database. In the terminal, run the following command: mysqldump -u <username> -p<password> <database_name> > db_dump.SQL Where: -p<password>: Password for the database user (you can omit the password and simply use -p to prompt for it manually). db_dump.SQL: The name of the output dump file. <username>: The privileged user with read access. <database_name>: The name of the database you are exporting. To create dumps from a remote server, add the -h flag: mysqldump -h <ip-address> -u <username> -p<password> <database_name> > db_dump.SQL If the MySQL server uses a non-standard port, specify it with the -P flag: mysqldump -h <ip-address> -P <port> -u <username> -p<password> <database_name> > db_dump.SQL While the default export format is SQL, mysqldump also supports exporting data as CSV, XML, and other formats by configuring additional parameters. The SQL dump typically includes: Information about the RDBMS (MySQL or MariaDB) Commands for creating the required tables and their columns Data to populate those columns By default, it provides a comprehensive snapshot of the database structure and contents, making it an essential tool for database backups and migrations. Importing Data into MySQL or MariaDB To import a database dump, you don’t need mysqldump; a direct call to MySQL will suffice. Run the following command in your terminal: mysql -u <username> -p<password> <new_database_name> < db_dump.SQL Where: -p<password>: The user's password (use -p without the password to be prompted manually). db_dump.SQL: The dump file containing your database data. <username>: A privileged user with write access. <new_database_name>: The name of the target database to which you are importing the dump. If the process completes successfully, there will be no output. If any errors or warnings occur, MySQL or MariaDB will display them. You can check if the import was successful with these commands: SHOW DATABASES;  -- Lists all databasesUSE <database_name>;  -- Selects the target databaseSHOW TABLES;  -- Lists all tables within the selected database By executing these commands, you can confirm that the database structure and data have been imported correctly. Creating a systemd Service for Backup Suppose you want to automate the database backup (export) process. In this guide, we will create a service-timer that will trigger a script for backing up data. A Timer is a mechanism used to schedule the execution of a specific service at a given time or through certain intervals. Follow these steps to set it up: First, connect to the server and create the directory for backup scripts: mkdir -p /usr/bin/backup_scripts  # Create directory for backup scripts Create and open the file /usr/bin/backup_scripts/mysql_backup.sh in any text editor (e.g., nano): nano /usr/bin/backup_scripts/mysql_backup.sh Inside the file, add the following script: TIMESTAMP=$(date +'%F') # Get the current date BACKUP_DIR='<path_to_backup_directory>' MYSQL_USER='<username>' MYSQL_PASSWORD='<password>' DATABASE_NAME='<database_name>' mkdir -p "$BACKUP_DIR/$TIMESTAMP" # Create directory for this date mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD $DATABASE_NAME > "$BACKUP_DIR/$TIMESTAMP/$DATABASE_NAME-$TIMESTAMP.sql" # Create dump find "$BACKUP_DIR" -type d -mtime +28 -exec rm -rf {} \; # Delete backups older than 28 days Replace the placeholder variables with the actual backup directory path, MySQL user credentials, and the database name. Grant execution permissions to the script: chmod +x /usr/bin/backup_scripts/mysql_backup.sh Create the service file /etc/systemd/system/mysql-backup.service: sudo nano /etc/systemd/system/mysql-backup.service Add the following content: [Unit] Description=MySQL Database Backup Service [Service] Type=oneshot ExecStart=/usr/bin/backup_scripts/mysql_backup.sh [Install] WantedBy=multi-user.target Create the timer file: sudo nano /etc/systemd/system/mysql-backup.timer Add this content to schedule the backup: [Unit] Description=Run MySQL Backup Service Weekly [Timer] OnCalendar=weekly Persistent=true [Install] WantedBy=timers.target Reload the systemd configuration, enable the timer for autostart, and start it: systemctl daemon-reload  # Reload systemd configurationsystemctl enable mysql-backup.timer  # Enable timer to start automaticallysystemctl start mysql-backup.timer  # Start the timer Check the status of the timer and ensure it is working: systemctl status mysql-backup.timersystemctl list-timers  # Lists active timers Now, your system will automatically create a backup of the specified database every week. Export and Import via phpMyAdmin You can perform database imports and exports not only through command-line utilities but also through the phpMyAdmin web interface. This method is typically more convenient when the dump size is small (less than 1GB). This section will cover the basic process of importing and exporting databases. Export To export a database: Go to the phpMyAdmin interface and select the desired database from the left-hand panel. Click on the Export tab. Choose export method: Quick Export: Select this if you want a basic export with default settings. Custom Export: Choose this for more specific export options, such as selecting certain tables, formats, or compression methods. Click Export. To export specific tables: Click on the database name in the left sidebar to view its tables. Select the tables you want to export by checking the boxes next to their names. At the bottom of the page, choose Export from the list of actions. On the next page, verify the export format and options, then click Go to save the dump to your local machine. Import The process of importing a database is very similar to exporting. Follow these steps: Open phpMyAdmin and navigate to the database you want to import into. If the database doesn't exist, create it by clicking Create Database from the left sidebar. Inside the database, click on the Import tab. Click the Choose File button to browse for and select the SQL dump file from your device. Choose the necessary options like file encoding, format (typically default settings are fine), and other options as needed. Click Go to start the import process. Once the process is complete, you will see the imported files and their contents listed in the left sidebar. You can also modify them if needed. Conclusion The choice of method depends on your needs: phpMyAdmin is ideal for manually creating backups or for quick access to a database. It’s user-friendly and simple for small-scale tasks. Using command-line utilities would be more efficient and flexible for automation or if you’re transferring infrastructure.
10 February 2025 · 8 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