Installation backup and password restoration for Mysql in Linux

Mysql Installation

The 2nd Redhat 9 discs provide Mysql RPM packages for easy installation. This RPM package has: mysql-3.23.54a-11.i386.rpm
Mysql-server-3.23.54a-11.i386.rpm
Mysql-devel-3.23.54a-11.i386.rpm

During installation, you can use this disc for installation, or download the latest RPM package from the Internet, and then install it.

The following uses the RPM package as an example to introduce the Mysql installation process. If you have installed Mysql during RedHat installation, you can skip the following installation steps.
Run the rpm-qa | grep mysql command to check whether Mysql is installed in Linux.

Figure 1: Check whether mysql is installed in Linux
If the system is not installed
Perl-CGI-2.81-88.i386.rpm
Perl-DBI-1.32-5.i386.rpm
Perl-DBD-Mysql-2.1021-3.i386.rpm
Mysql-3.23.54a-11.i386.rpm
Mysql-server-3.23.54a-11.i386.rpm

Why install the Perl rpm package? This is because in RedHat, Mysql requires support from the Perl language to run normally. Therefore, you must install the Perl language and related packages before installing mysql.

We will install the above five rpm packages according to the installation sequence below.
# Rpm-ivh perl-CGI-2.81-88.i386.rpm
# Rpm-ivh perl-DBI-1.32-5.i386.rpm
# Rpm-ivh mysql-3.23.54a-11.i386.rpm perl-DBD-Mysql-2.1021-3.i386.rpm
# Rpm-ivh mysql-server-3.23.54a-11.i386.rpm

Figure 2: Install mysql in Linux

Now that mysql has been installed, run the command to start Mysql and check whether it is started.
We can start Mysql in three ways:

Method 1: use the service command to start Mysql
# Service mysql start

Method 2: Use the mysqld script to start Mysql:
#/Etc/init. d/mys qld start

Method 3: Use the safe_mysqld utility to start the Mysql service.
# Safe_mysqld & // use & to put safe_mysqld in the background for execution.
Here we use method 2 to start Mysql

We can use the Command service mysqld status to check whether mysql has been started, as shown in Figure 4. The "3295" in "pid 3295" is the process number that mysqld runs, this process number is not fixed, but varies according to the number of processes running in different systems.

Mysql has been installed and started. We can use commands in Mysql to operate our Mysql database.
We can use mysql-u root-p to access the database. The system will prompt us to enter the password. when mysql is installed, the password of the root account is blank by default, so we can directly press "enter" to enter our database. We can also use the show databases; command to view the existing databases in the system. See Figure 5. We can see that mysql and test Databases are provided by default in the system.

To change the mysql password, run the mysqladmin command in the format
Mysqladmin-u root-p password. The "password" here is the new password. As shown in, here we set the root password to "public". The system will prompt us to enter the old password. If mysql is just installed, the default password is blank ).

 
Figure 3: Change the mysql password
There are still many Mysql commands. We cannot introduce them one by one here. Interested readers can refer to the Mysql Command manual.

Mysql backup

Our database needs to be backed up frequently. The common backup method of Mysql database is to use the utility mysqldump. The command format is as follows:
# Mysqldump [options] database [tables]
The parameter description is as follows:
Options: indicates the option of mysqldump. You can find it through mysqldump-help.
Database: indicates the database to be backed up.
Tables: indicates the table to be backed up. If no table is specified, the entire database is backed up.
We used mysqldump to back up the mysql library that comes with mysql, and used
Ll command to view the backup file.

Let's take a look at the recovery after the backup, 9. As shown in figure 10, we create a new database bak and then import the mysql. bak file we just backed up to this database.

Figure 4: Create a bak Database

Figure 5: Import mysql. bak to the bak Database

Mysql password recovery

If Mysql is not accessed for a period of time, you may forget the access password. What should I do? reinstall mysql? The cost is too high. Here we will introduce two methods to restore the password.
Method 1: Because the Mysql password is stored in the user table in the mysql database, we only need to copy and overwrite the user table with the known password.
In Linux, there are three user table files in the/var/lib/mysql/directory: user. frm, user. MYD, and user. MYI. They represent:
User. frm // user Table Style File
User. MYD // user table data file
User. MYI // user table index file
For the sake of insurance, we will first back up the files related to the three user tables, as shown in 10,

Figure 10: Backup three related files in the user table
Then we copy the three files in the user table in the mysql database with the password. Then we can use ls to view the six user table files.
In this case, you only need to restart the mysql service to access the Mysql database with a known password.
Restart command: service mysql restart or
/Etc/init. d/mysql restart

Now we can access the Mysql database with a known password.

Method 2:

The first method is to restore the password of another Mysql database without such a known password. How can we recover the database? We use a method described in the Mysql Chinese Reference Manual, which involves several steps:

1: Send the kill command to the mysqld server to disable the mysqld server (not kill-9). The files that store the process ID are usually located in the directory of the MYSQL database.

Killall-TERM mysqld // you must be a UNIX root user or your running
SERVER.

2: Use the '-- skip-grant-tables' parameter to start mysqld. (For LINUX,/usr/bin/safe_mysqld -- skip-grant-tables, for windows, c: \ mysql \ bin \ mysqld -- skip-grant-tables), as shown in;

Figure 13: running results of steps 1 and 2

3: Then you can log on to the mysqld server without a password. At this time, the Mysql password is the same as the one you just installed and is empty. Now we can use mysqladmin to change the mysql password in figure 6.

4: load the permission table. You can use mysqladmin flush-privileges to refresh the permission table.

Figure 14: Use mysqladmin to load a permission table

5: log on with the new password.

Mysql is a powerful database that requires more practice in practice. This article describes how to install Mysql and the most basic commands for accessing and changing passwords, finally, we will introduce two methods to restore the Mysql password. Hope to help you.

References:
1: MySQL database and backup and recovery http://www.yesky.com/257/1817257_1.shtml

2: MySQL password recovery notes
Http://www.chinaitlab.com/www/news/article_show.asp? Id = 30494
3: "Red Hat Linx 9 network service" SmarTraining studio compiles the Mechanical Industry Press