Mysql database backup and restore by mysqldump
MySQL: The Go-To Open-Source RDBMS
MySQL is a premier open-source Relational Database Management System (RDBMS) acclaimed for its ability to efficiently store, manage, and retrieve structured information. It serves as the backbone for a myriad of applications, ranging from modest personal projects to complex, high-traffic internet sites and sophisticated enterprise solutions.
Key highlights of MySQL include its high performance, robust reliability, seamless scalability, and user-friendly nature. Moreover, it boasts wide compatibility across leading operating systems such as Linux, macOS, Windows, and Ubuntu, making it a versatile solution for any environment.
Prerequisites: -
Require two Ubuntu 22.04
mysql-server-8.0
Update && upgrade
$ apt-get update -y && apt-get upgrade -y
Install MySQL
Install MySQL by typing the following command:
$ apt-get install mysql-server-8.0 -y
To secure the installation, MySQL inside-
Create MySQL database:
$ mysql> create database testing_database
;
Using testing database-
$ mysql> user tesing_database;
Create testing_table in the testing_database
$ mysql> create table testing_table (id int, name varchar(50), address varchar(50), primary key (id));
Insert data to testing_table
$ mysql> insert into testing_table(id, name, address) values("001", "Ubuntu", "Hiroshima");
show testing_table
$ mysql> select * from testing_table;
TAKE MYSQL BACKUP AND RESTORE-
$ mysqldump --lock-all-tables --all-databases --events > mysql_dump.sql
Dump all data without locking but with transaction-ensured data integrity by [--single-transaction] option -
Dump specific database -
$ mysqldump test_database --single-transaction --events > mysql_dump.sql
For Restore Database -
For restoring data from backup on another host, run as follows. Before restoring, transfer dump data to the target host with [rsync] or [scp] and so on.
Copy these mysql_dump.sql another machine:
$ sftp root@another_machine_ip
Go to another server machine:
$ apt-get install mysql-server-8.0 -y
$ mysql testing_database < mysql_dump.sql
Last updated