Detailed description of backing up and restoring MySQL database on Linux system is as follows::
Backing Up the Database:
-
Log in to the Linux system and open a terminal window.
-
Use the mysqldump command to create a backup of the database. The mysqldump command is a tool provided by MySQL that can export the data and structure of the database to a SQL file.
-
The basic syntax of the mysqldump command is:
mysqldump -u [username] -p[password] [database_name] > [backup_file_path]Where the -u parameter specifies the username to connect to the database, the -p parameter specifies the password to connect to the database, the [database_name] parameter specifies the name of the database to be backed up, the > symbol indicates that the output is redirected to a file, and the [backup_file_path] parameter specifies the location and name of the backup file.
-
For example, if you want to back up a database named test and save the backup file to /home/bak/test.sql, you can use the following command:
mysqldump -u root -p test > /home/bak/test.sqlWhen executing the command, the system will prompt for the password, and you need to enter the correct password to proceed.
-
If you want to back up certain tables in the database, you can add the table names after the [database_name] parameter, separated by spaces. For example, if you want to back up the user and order tables in the test database, you can use the following command:
mysqldump -u root -p test user order > /home/bak/test.sql-
If you want to back up all databases, you can use the –all-databases parameter.
For example, you can use the following command:
mysqldump -u root -p --all-databases > /home/bak/all.sql-
The mysqldump command has many other parameters that can control the content and format of the backup. You can use mysqldump –help to view all parameters and usage.
Restoring the Database:
-
Log in to the Linux system and open a terminal window.
-
Use the mysql command to import the backup file into the specified database. The mysql command is a client tool provided by MySQL that can be used to connect to and operate the database.
-
The basic syntax of the mysql command is:
mysql -u [username] -p[password] [database_name] < [backup_file_path]Where the -u parameter specifies the username to connect to the database, the -p parameter specifies the password to connect to the database, the [database_name] parameter specifies the name of the database to restore, the < symbol indicates that the input is redirected from a file, and the [backup_file_path] parameter specifies the location and name of the backup file.
-
For example, to restore the /home/bak/test.sql file to the test database, you can use the following command:
mysql -u root -p test < /home/bak/test.sqlWhen executing the command, the system will prompt for the password, and you need to enter the correct password to proceed.
-
If you want to restore all databases, you can omit the [database_name] parameter. For example, you can use the following command:
mysql -u root -p < /home/bak/all.sql-
Before restoring the database, ensure that the target database already exists, or that the backup file contains statements to create the database. If the target database does not exist, you can create an empty database using the mysqladmin command. For example, to create a database named test, you can use the following command:
mysqladmin -u root -p create test-
The mysql command also has many other parameters that can control the content and method of restoration. You can use mysql –help to view all parameters and usage.
