Skip to main content

Backup/Restore MySQL database in linux

This document contains steps to perform backup and restore operations of OPNBI Database with MySQL database in linux platform.

References​

Links usefull for backup and restore data are listed here,

  1. https://devopsarticle.com/install-create-backup-restore-mysql-on-ubuntu-20-04-lts-6-easy-steps/

  2. https://phoenixnap.com/kb/how-to-backup-restore-a-mysql-database

  3. https://www.digitalocean.com/community/tutorials/how-to-backup-mysql-databases-on-an-ubuntu-vps

  4. https://linuxize.com/post/how-to-create-a-mysql-database/

  5. https://www.softwaretestinghelp.com/mysql-docker/

  6. https://hub.docker.com/_/mysql

  7. https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04

There are 2 ways we can perform this,

  1. Using terminal

  2. MySQL Workbench application

1. Using terminal​

Backup​

List of commands used to perform backup and restore operation are listed below,

warning

Skip the steps of installing & creating sample and backup database if you already have installed or created.

  1. Install MySQL by executing this command: sudo apt install mysql-client

    Docusaurus Slash Introduction

  2. Check Installed MySQL version by running this command: mysql -V

    Docusaurus Slash Introduction

  3. Install MySQL server by executing this command sudo apt install mysql-server

    Docusaurus Slash Introduction

  4. In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This plugin requires that the name of the operating system user that invokes the MySQL client matches the name of the MySQL user specified in the command, so you must invoke mysql with sudo privileges to gain access to the root MySQL user: sudo mysql

    info

    If you find wny error executing this command like ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) it might due to MySQL server is not running.

    Execute this command to start MySQL server : sudo service mysql start and try again.

    Docusaurus Slash Introduction

  5. Once you have access to the MySQL prompt, you can create a new user with a CREATE USER statement. These follow this general syntax: CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

    info

    After CREATE USER, you specify a username. This is immediately followed by an @ sign and then the hostname from which this user will connect. If you only plan to access this user locally from your Ubuntu server, you can specify localhost. Wrapping both the username and host in single quotes isn’t always necessary, but doing so can help to prevent errors.

    For example, CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'Password@123';

    Docusaurus Slash Introduction

  6. After creating your new user, you can grant them the appropriate privileges. The general syntax for granting user privileges is as follows:

    GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

    Docusaurus Slash Introduction

  7. Following this, it’s good practice to run the FLUSH PRIVILEGES command. This will free up any memory that the server cached as a result of the preceding CREATE USER and GRANT statements: FLUSH PRIVILEGES;

    Docusaurus Slash Introduction

  8. Exit from mysql login: exit

    Docusaurus Slash Introduction

    info

    In the future, to log in as your new MySQL user, you’d use a command like the following:

    mysql -u sammy -p

    Enter password you have provided instep No 5 and hit enter

    Docusaurus Slash Introduction

  9. Start MySQL server by running this command: sudo service mysql start

    Docusaurus Slash Introduction

  10. Check MySQL server version by executing below command,

    sudo mysqladmin -p -u sammy version

    Enter password you have applied for created user sammy in Step No 5.

    Docusaurus Slash Introduction

  11. Connect with MySQL user, created in Step No 5 by running this command:mysql -u sammy -p

    Enter password you have applied for created user sammy in Step No 5.

    Docusaurus Slash Introduction

  12. Once connected, execute this command to create database: CREATE DATABASE sample_data;

    Docusaurus Slash Introduction

  13. Switch to sample database created by executing this command: USE sample_data;

    Docusaurus Slash Introduction

  14. Execute this query to create sample table,

    CREATE TABLE Persons (
        PersonID int,
        LastName varchar(255),
        FirstName varchar(255),
        Address varchar(255),
        City varchar(255)
    );

    Docusaurus Slash Introduction

  15. Insert sample data in to this created table by writing below query,

    INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
    VALUES ('2267', 'Erichsen', 'Jhon', 'Stavanger','Norway'),('2268', 'west', 'Allen', 'Central City','New York');

    Docusaurus Slash Introduction

  16. Disconnect from mysql user by writing : exit command

    Docusaurus Slash Introduction

  17. Execute this command to take backup of sample_data database : mysqldump -u root -p sample_data > backup.sql

    info

    Here sample_data is database name of which we are taking backup and backup.sql is the backup file name which will be generated.

:::warning Tip
If you are facing error like **mysqldump: Got error: 1698: Access denied for user 'root'@'localhost' when trying to connect**

![Docusaurus Slash Introduction](/opnbi_img/usecase/BackupRestoreMySQLlinux37.png)

while executing this command, you need to reset password of root user. do this by executing this command,

```sudo mysql -u root```

![Docusaurus Slash Introduction](/opnbi_img/usecase/BackupRestoreMySQLlinux38.png)

and then

```ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password_here';```

![Docusaurus Slash Introduction](/opnbi_img/usecase/BackupRestoreMySQLlinux39.png)

Now Exit from mysql server using : ```Exit``` command

Execute **Step No 18** Command again and this time provide updated password for root user and it will work.

![Docusaurus Slash Introduction](/opnbi_img/usecase/BackupRestoreMySQLlinux40.png)
:::

  1. Check backup.sql file created in your system execute ls command

    Docusaurus Slash Introduction

Restore​

Follow this steps to restore backup file (.sql) file in MySQL database.

  1. To restore data in new database, first let us create new database. login to mysql,

    mysql -P 3306 --protocol=tcp -u root -p

    Enter password for root user and hit enter.

    Docusaurus Slash Introduction

  2. create new backup database CREATE DATABASE restore_data;. here restore_data is name of database.

    Docusaurus Slash Introduction

  3. Exit from database by writing exit

  4. Execute this command to restore backup.sql file in restore_data database.

    mysql -u root -p restore_data < backup.sql

    Enter password for root user and hit enter

    Docusaurus Slash Introduction

  5. To check backup file restored or not, Connect with MySQL database: mysql -u root -p

    Enter password for root user and hit enter

    Docusaurus Slash Introduction

  6. Change database to restore_data : Use restore_data;

    Docusaurus Slash Introduction

  7. Run this query to fetch data from table,

    select * from Persons;

    Docusaurus Slash Introduction

2. MySQL Workbench application​

For Backup and restore database using self-contained file [.sql] please follow steps provided in below link,

Click Here..

For Docker User​

  1. Pull MySQL image in docker by executing this command in powershell: docker pull mysql/mysql-server:latest or direclty fetch mysql image and run using command given in step 2.

  2. Run MySQL image by executing this command : 

    docker run --name backup-mysql -e MYSQL_ROOT_PASSWORD=1234 -d mysql:latest
    Note

    where some-mysql is the name you want to assign to your container, my-secret-pw is the password to be set for the MySQL root user and tag is the tag specifying the MySQL version you want. See the list above for relevant tags.

    info

    This will run mysql if proper name of mysql image is provided,if not found any image it will download new image automatically. so use this command directly if you do not have mysql image downloaded.

    Docusaurus Slash Introduction

  3. Get the list if docker images for MySQL docker ps you will see container ID as gievn in below code,

    Docusaurus Slash Introduction

  4. Connect with MySQL containerID docker exec -it 3680d544408d bash

    Docusaurus Slash Introduction

  5. Connect with MySQL database by executing this command : mysql -P 3306 --protocol=tcp -u root -p

    Provide password which you have applied in step 2 command and hit enter. Now you're connected to MySQL running in your container

    Docusaurus Slash Introduction

  6. Check the list of database available by default in MySQL by executing this command: SHOW DATABASES;

    Docusaurus Slash Introduction

  7. Create new database in order to perform backup and restore operation : CREATE DATABASE sample_data;

    Docusaurus Slash Introduction

    :::info If you have your database then skip this step :::

  8. Connect with created database to create sample tables inside it. execute this command: USE sample_data;

    Docusaurus Slash Introduction

  9. Now you are connected with sample_data database. to create a sample table execute this query,

    info

    If you have own table and data, you can create table accordingly. This query is for demonstration only. if you have data tables created then skip this step

    CREATE TABLE Persons (
        PersonID int,
        LastName varchar(255),
        FirstName varchar(255),
        Address varchar(255),
        City varchar(255)
    );

    Docusaurus Slash Introduction

  10. Once table is created, add a sample data in to this table bby executing below query,

    info

    If you have database created, skip this step.

    INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
    VALUES ('2267', 'Erichsen', 'Jhon', 'Stavanger','Norway'),('2268', 'west', 'Allen', 'Central City','New York');

    Docusaurus Slash Introduction

  11. Once table is created, let's execute command to take backup of database. to do that you need to exit from mysql by executing exit command

    Docusaurus Slash Introduction

  12. execute this command from root user: mysqldump -u root -p sample_data > backup.sql

    Docusaurus Slash Introduction

  13. On successfull completion of above command check backup file is created or not execute this command: ls

    Docusaurus Slash Introduction

  14. To backup data in new database, first let us create new database. login to mysql using mysql -P 3306 --protocol=tcp -u root -p

    Enter password and hit enter

    info

    If you already have backup database created, skip this step

    Docusaurus Slash Introduction

  15. Create new backup database :CREATE DATABASE restore_data;

    Docusaurus Slash Introduction

  16. Disconnect from MySQL database execute: exit

    Docusaurus Slash Introduction

  17. Restore from backup file execute this command: mysql -u root -p restore_data < backup.sql. Enter password and it will work

    Docusaurus Slash Introduction

  18. To check database is restored or not login to MySQL: mysql -u root -p

    Docusaurus Slash Introduction

  19. Connect with restore_data database Use: Use restore_data;

    Docusaurus Slash Introduction

  20. Execute this query to get the table data: select * from Persons;

    Docusaurus Slash Introduction

If your database is restored, you will get table data as output. you have successfully backup and restored MySQL database.