Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "the installation, configuration and use of MySQL CentOS system management files". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. introduction

I have wanted to use Linux for a long time. Because there are no hard tasks and no systematic learning, recently I have to use MySQL CentOS under Linux because of my work. I thought I had the experience of using SQL Server under Windows, and I thought it should be easy to install MySQL CentOS under Linux, but I took a lot of detours and encountered a lot of problems when installing and using MySQL CentOS. After all, there is a big difference between Linux and Windows. In order to let beginners like me take fewer detours in the process of learning and get started as soon as possible, I wrote this article. I hope it will be helpful to you. The Linux environment for this article is Red Hat 9.0. MySQL CentOS is 4.0.16.

Second, install MySQL CentOS

1. Download the installation file of MySQL CentOS

The following two files are required to install MySQL CentOS:

MySQL CentOS-server-4.0.16-0.i386.rpm

MySQL CentOS-client-4.0.16-0.i386.rpm

Download address: http://www.MySQL CentOS.com/downloads/MySQL CentOS-4.0.html, open this page, drop down the page to find the "Linux x86 RPM downloads" item, find the "Server" and "Client programs" items, and download the above two rpm files.

2. Install MySQL CentOS

The rpm file is a software installation package developed by Red Hat. Rpm allows Linux to avoid many complicated procedures when installing the software package.

The commonly used parameter for this command during installation is-ivh, where I indicates that the specified rmp package will be installed, V indicates the details of the installation, and h indicates that the "#" symbol appears during installation to show the current installation process. This symbol will not stop until the installation is complete.

1) install the server side

Run the following command in a directory with two rmp files:

[root@test1 local] # rpm-ivh MySQL CentOS-server-4.0.16-0.i386.rpm

The following information is displayed.

Warning: MySQL CentOS-server-4.0.16-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5

Preparing... # [1:MySQL CentOS-server # #]. (omit display)

/ usr/bin/MySQL CentOSadmin-u root password' new-password'

/ usr/bin/MySQL CentOSadmin-u root-h test1 password' new-password'. (omit display)

Starting MySQL CentOSd daemon with databases from / var/lib/MySQL CentOS

If the above information appears, the server installation is complete. Test whether you can run netstat successfully to see whether the MySQL CentOS port is open. If it is opened, it means that the service has been started and installed successfully. The default port for MySQL CentOS is 3306.

[root@test1 local] # netstat-nat

Active Internet connections (servers and established)

Proto Recv-Q Send-Q Local Address Foreign Address State tcp

00 0.0.0.0VR 3306 0.0.0.0VA * LISTEN

The above shows that the MySQL CentOS service has been started.

2) install the client

Run the following command:

[root@test1 local] # rpm-ivh MySQL CentOS-client-4.0.16-0.i386.rpm

Warning: MySQL CentOS-client-4.0.16-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5

Preparing...### [100%]

Shows that the installation is complete.

Use the following command to connect to MySQL CentOS and test for success.

Log in to MySQL CentOS

The command to log in to MySQL CentOS is MySQL CentOS, and the syntax for MySQL CentOS is as follows:

MySQL CentOS [- u username] [- h host] [- p [password]] [dbname]

Username and password are the user name and password of MySQL CentOS, respectively. The initial administrative account number of MySQL CentOS is root, and there is no password. Note: this root user is not a system user of Linux. The default user of MySQL CentOS is root. Since you don't have a password initially, you only need to type MySQL CentOS when you enter.

[root@test1 local] # MySQL CentOS Welcome to the MySQL CentOS monitor. Commands end with; or\ g. Your MySQL CentOS connection id is 1 to server version: 4.0.16-standard Type 'help;' or'\ h' for help. Type'\ c'to clear the buffer.

The "MySQL CentOS >" prompt appears in MySQL CentOS >. Congratulations on your successful installation!

After adding the password, the login format is as follows:

MySQL CentOS-u root-p Enter password: (enter password) where-u is followed by a user name, and-p requires a password. Enter the password at the place where you enter the password after entering the enter.

Note: this MySQL CentOS file is in the / usr/bin directory and is not the same as the startup file / etc/init.d/MySQL CentOS described later.

IV. Several important catalogs of MySQL CentOS

After MySQL CentOS installation is completed, unlike SQL Server installed in a directory by default, its database files, configuration files and command files are in different directories, it is very important to understand these directories, especially for Linux beginners, because the directory structure of Linux itself is more complex, if you do not understand the installation directory of MySQL CentOS, it is impossible to learn in depth.

Let's introduce these catalogs.

1. Database directory / var/lib/MySQL CentOS/

2. Configuration file / usr/share/MySQL CentOS (MySQL CentOS.server command and configuration file)

3. Related commands / usr/bin (MySQL CentOSadmin MySQL CentOSdump, etc.)

4. Startup script / etc/rc.d/init.d/ (directory of startup script file MySQL CentOS)

Change the login password

MySQL CentOS does not have a password by default, and the importance of adding a password after installation is self-evident.

1. Command usr/bin/MySQL CentOSadmin-u root password' new-password'

Format: MySQL CentOSadmin-u username-p old password password new password

2. Example 1: add a password of 123456 to root.

Type the following command: [root@test1 local] # / usr/bin/MySQL CentOSadmin-u root password 123456

Note: since root does not have a password at the beginning, the-p old password can be omitted.

3. Whether the modification is successful or not

1) Log in without password

[root@test1 local] # MySQL CentOS

ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) displays an error, indicating that the password has been changed.

2) Log in with the modified password

[root@test1 local] # MySQL CentOS-u root-p

Enter password: (enter the modified password 123456)

Welcome to the MySQL CentOS monitor. Commands end with; or\ g.

Your MySQL CentOS connection id is 4 to server version: 4.0.16-standard

Type 'help;' or'\ h' for help. Type'\ c'to clear the buffer.

MySQL CentOS > success!

This is to change the password through the MySQL CentOSadmin command, or you can change the password by modifying the library.

VI. Start and stop

1. After the MySQL CentOS installation is completed, the startup file MySQL CentOS is in the / etc/init.d directory, and you can run the following command when you need to start.

[root@test1 init.d] # / etc/init.d/MySQL CentOS start

2. Stop / usr/bin/MySQL CentOSadmin-u root-p shutdown

3. Automatic start

1) check to see if MySQL CentOS is in the auto startup list

[root@test1 local] # / sbin/chkconfig-list

2) add MySQL CentOS to the startup service group of your system

[root@test1 local] # / sbin/chkconfig-add MySQL CentOS

3) remove MySQL CentOS from the startup service group.

[root@test1 local] # / sbin/chkconfig-del MySQL CentOS

7. Change the MySQL CentOS directory

The default data file storage directory for MySQL CentOS is / var/lib/MySQL CentOS.

If you want to move the directory to / home/data, you need to take the following steps:

1. Set up the data directory cd / home mkdir data under the home directory

2. Stop the MySQL CentOS service process: MySQL CentOSadmin-u root-p shutdown

3. Move the entire directory / var/lib/MySQL CentOS to / home/data mv / var/lib/MySQL CentOS / home/data/

This moves the data file of MySQL CentOS to / home/data/MySQL CentOS

4. Find the my.cnf configuration file

If there is no my.cnf configuration file in the / etc/ directory, find the * .cnf file under / usr/share/MySQL CentOS/ and copy one of them to / etc/ and rename it to my.cnf).

The command is as follows:

[root@test1 MySQL CentOS] # cp / usr/share/MySQL CentOS/my-medium.cnf / etc/my.cnf

5. Edit the configuration file / etc/my.cnf of MySQL CentOS

In order to ensure that MySQL CentOS can work properly, you need to specify the location where the MySQL CentOS.sock file is generated.

Modify the value to the right of the equal sign on the socket=/var/lib/MySQL CentOS/MySQL CentOS.sock line to: / home/MySQL CentOS/MySQL CentOS.sock.

Do the following:

Vi my.cnf

(edit the my.cnf file with the vi tool and find the following data to modify)

# The MySQL CentOS server [MySQL CentOSd]

Port = 3306

# socket = / var/lib/MySQL CentOS/MySQL CentOS.sock (original content, to comment this line with "#" more safely)

Socket = / home/data/MySQL CentOS/MySQL CentOS.sock (plus this line)

6. Modify MySQL CentOS startup script / etc/rc.d/init.d/MySQL CentOS

* you need to modify the MySQL CentOS startup script / etc/rc.d/init.d/MySQL CentOS to change the path to the right of the equal sign in the datadir=/var/lib/MySQL CentOS line to your actual storage path: home/data/MySQL CentOS.

[root@test1 etc] # vi / etc/rc.d/init.d/MySQL CentOS

# datadir=/var/lib/MySQL CentOS (comment this line)

Datadir=/home/data/MySQL CentOS (plus this line)

7. Restart the MySQL CentOS service

/ etc/rc.d/init.d/MySQL CentOS start

Or restart Linux with the reboot command

If you work properly, you will succeed, otherwise check again against the previous 7 steps.

VIII. Common operations of MySQL CentOS

Note: each command in MySQL CentOS ends with a semicolon;.

1. Display the database

MySQL CentOS > show databases

+-+

| | Database |

+-+

| | MySQL CentOS |

| | test |

+-+

2 rows in set (0.04 sec)

MySQL CentOS has just been installed with two databases: MySQL CentOS and test. The MySQL CentOS library is very important. It contains the system information of MySQL CentOS. When we change passwords and add users, we actually use the related tables in this library to operate.

2. Display the tables in the database

MySQL CentOS > use MySQL CentOS; (opening the library and operating on each library requires opening the library, similar to foxpro)

Database changed

MySQL CentOS > show tables

+-+

| | Tables_in_MySQL CentOS |

+-+

| | columns_priv |

| | db |

| | func |

| | host |

| | tables_priv |

| | user |

+-+

6 rows in set (0.01 sec)

3. Display the structure of the data table:

Describe table name

4. Display the records in the table:

Select * from table name

For example: display records in the user table in the MySQL CentOS library.

All users who can operate on MySQL CentOS users are in this table.

Select * from user

5. Build the database:

Create database library name

For example: create a library with the first name bit aaa

MySQL CentOS > create databases aaa

6. Create a table:

Use library name

Create table table name (list of field settings)

For example, create a table name in the newly created aaa library. There are four fields in the table: id (serial number, automatic increment), xm (name), xb (gender) and csny (birth date).

Use aaa

MySQL CentOS > create table name (id int (3) auto_increment not null primary key, xm char (8), xb char (2), csny date)

You can use the describe command to view the table structure that you just created.

MySQL CentOS > describe name

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (3) | | PRI | NULL | auto_increment | |

| | xm | char (8) | YES | | NULL |

| | xb | char (2) | YES | | NULL |

| | csny | date | YES | | NULL |

+-+ +

7. Add records

For example: add several related records.

MySQL CentOS > insert into name values (', 'Zhang San', 'male', '1971-10-01')

MySQL CentOS > insert into name values (', 'Baiyun', 'female', '1972-05-20')

You can use the select command to verify the results.

MySQL CentOS > select * from name

+-- +

| | id | xm | xb | csny | |

+-- +

| | 1 | Zhang San | male | 1971-10-01 |

| | 2 | Baiyun | female | 1972-05-20 | |

+-- +

8. Modify the record

For example, change Zhang San's date of birth to 1971-01-10.

MySQL CentOS > update name set csny='1971-01-10 'where xm=' Zhang San'

9. Delete the record

For example: delete Zhang San's record.

MySQL CentOS > delete from name where xm=' Zhang San'

10. Delete libraries and tables

Drop database library name

Drop table table name

Increase the number of MySQL CentOS users

Format: grant select on database. * to user name @ login host identified by "password"

Example 1. Add a user's user_1 password to 123, so that he can log in on any host and have the authority to query, insert, modify and delete all databases. First use the root user to connect to MySQL CentOS, and then type the following command:

MySQL CentOS > grant select,insert,update,delete on *. * to "Identified by" 123; example 1 the increase in users is very dangerous, if they know the password of user_1, then they can log in to your MySQL CentOS database on any computer on the Internet and do whatever they want with your data. See example 2 for the solution.

Example 2, add a user user_2 password to 123so that the user can only log in on localhost, and can query, insert, modify and delete the database aaa (localhost refers to the local host, that is, the host where the MySQL CentOS database is located), so that even if the user knows the user_2 password, he can not directly access the database from the network, can only operate the aaa library through the MySQL CentOS host.

MySQL CentOS > grant select,insert,update,delete on aaa.* to user_2@localhost identified by "123"

Use the new users if you can't log in to MySQL CentOS

Use the following command when logging in: MySQL CentOS-u user_1-p-h 192.168.113.50 (- h followed by the ip address of the host to be logged in)

Backup and recovery

1. Backup for example: back up the aaa library created in the above example to the file back_aaa

[root@test1 root] # cd / home/data/MySQL CentOS (go to the library directory, this example library has been transferred from val/lib/MySQL CentOS to / home/data/MySQL CentOS, see part VII above)

[root@test1 MySQL CentOS] # MySQL CentOSdump-u root-p-- opt aaa > back_aaa

2. Restore [root@test MySQL CentOS] # MySQL CentOS-u root-p ccc < back_aaa

This is the end of the content of "installation, configuration and use of MySQL CentOS system management files". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.