Creating a MySQL Database Backup via Console

When the time comes, if you’re using a VPS, you’ll need to backup your database. If you have a control panel, this can be straightforward. However, with the more budget-friendly options, we must consider that we can use the console to make a copy of our database.

To help you understand this better, let’s use XAMPP to create a common database, perhaps a table with users or something similar.

Creating our database in XAMPP.

First, if you don’t have XAMPP installed, you should install it. You can do this from https://www.apachefriends.org/es/index.html.

Once installed, you should run the XAMPP program and in its interface, start MySQL by clicking the “Start” button.

Once installed, you should create your database. To do this, you can click the “Start” button for Apache and then go to http://localhost/phpmyadmin/. From there, you can create a database.

You could create a database with a “products” table, for example. Here’s some code you can use to avoid manually entering the products:

-- Crear la tabla 'productos'
CREATE TABLE productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(255),
    precio DECIMAL(10, 2),
    descripcion TEXT,
    categoria VARCHAR(50)
);

-- Insertar 20 productos de ejemplo en la tabla 'productos'
INSERT INTO productos (nombre, precio, descripcion, categoria)
VALUES
    ('Producto 1', 19.99, 'Descripción del Producto 1', 'Electrónica'),
    ('Producto 2', 29.99, 'Descripción del Producto 2', 'Ropa'),
    ('Producto 3', 9.99, 'Descripción del Producto 3', 'Hogar'),
    ('Producto 4', 49.99, 'Descripción del Producto 4', 'Electrónica'),
    ('Producto 5', 14.99, 'Descripción del Producto 5', 'Ropa'),
    ('Producto 6', 39.99, 'Descripción del Producto 6', 'Hogar'),
    ('Producto 7', 24.99, 'Descripción del Producto 7', 'Electrónica'),
    ('Producto 8', 19.99, 'Descripción del Producto 8', 'Ropa'),
    ('Producto 9', 7.99, 'Descripción del Producto 9', 'Hogar'),
    ('Producto 10', 54.99, 'Descripción del Producto 10', 'Electrónica'),
    ('Producto 11', 12.99, 'Descripción del Producto 11', 'Ropa'),
    ('Producto 12', 34.99, 'Descripción del Producto 12', 'Hogar'),
    ('Producto 13', 29.99, 'Descripción del Producto 13', 'Electrónica'),
    ('Producto 14', 22.99, 'Descripción del Producto 14', 'Ropa'),
    ('Producto 15', 8.99, 'Descripción del Producto 15', 'Hogar'),
    ('Producto 16', 64.99, 'Descripción del Producto 16', 'Electrónica'),
    ('Producto 17', 17.99, 'Descripción del Producto 17', 'Ropa'),
    ('Producto 18', 42.99, 'Descripción del Producto 18', 'Hogar'),
    ('Producto 19', 31.99, 'Descripción del Producto 19', 'Electrónica'),
    ('Producto 20', 11.99, 'Descripción del Producto 20', 'Ropa');

Creating a backup.

Once we’ve created our database, we need to know how to use MySQL from the console:

• If we open a command prompt (CMD) and navigate to C:\xampp\mysql\bin (assuming XAMPP is installed on the C drive), we can use the following command:

mysql -u root -p

Here, you might be wondering, “What is this code for?” Well, this way, we are entering MySQL with the root user and the password it will prompt us for as soon as we run our code in CMD.

So, what will we do after entering MySQL? Here, we can check which database we are in, make queries, and more. Now, what we need to do is exit the MySQL command line, and you can do that with the command quit;.

In theory, you can use mysqldump within the command line, but I have never been able to make it work.

We will use mysqldump from outside the command line. Of course, in this case, we need to provide the username and password.

mysqldump -u usuario -p base_de_datos > archivo.sql

On the other hand, you can restore your backup as follows:

mysql -u usuario -p base_de_datos < archivo.sql

Let's get back to the topic. I've created a database named "example_products," and now it's time to perform our mysqldump.

I'll use the default root user, which doesn't have a password, so when it prompts for the password, I'll simply press Enter.

mysqldump example_products> C:\Users\luisg\ejemplo_de_mysqldump\productos.sql

That's how we create a backup of our database. If we go to the specified location where our file would be, we'll find our database backup there.