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

This article mainly introduces "the installation and configuration of MySql database under Linux and the introduction of simple operation". In the daily operation, I believe that many people have doubts about the installation and configuration of MySql database under Linux and the introduction of simple operation. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "installation and configuration of MySql database under Linux as well as simple operation introduction". Next, please follow the editor to study!

RPM install Mysql

Mysql requires the support of perl (DBI) and perl-DBD-MySQL, so install perl-DBI and perl-DBD-MySQL first

Perl-DBI installation

[root@linux-a] # rpm-ivh / media/Server/perl-DBI-1.52-1.fc6.i386.rpm

Warning: / media/Server/perl-DBI-1.52-1.fc6.i386.rpm: Header V3 DSA signature: NOKEY, key ID 37017186

Preparing... # [100%]

1:perl-DBI # # [100%]

[root@linux-a ~] #

Perl-DBD-MySQL installation

[root@linux-a] # rpm-ivh / media/Server/perl-DBD-MySQL-3.0007-1.fc6.i386.rpm

Warning: / media/Server/perl-DBD-MySQL-3.0007-1.fc6.i386.rpm: Header V3 DSA signature: NOKEY, key ID 37017186

Preparing... # [100%]

1:perl-DBD-MySQL # # [100%]

[root@linux-a ~] #

Start installing mysql

[root@linux-a] # rpm-ivh / media/Server/mysql-5.0.22-2.1.i386.rpm

Warning: / media/Server/mysql-5.0.22-2.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 37017186

Preparing... # [100%]

1:mysql # # [100%]

[root@linux-a ~] #

Install mysql server

[root@linux-a] # rpm-ivh / media/Server/mysql-server-5.0.22-2.1.i386.rpm

Warning: / media/Server/mysql-server-5.0.22-2.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 37017186

Preparing... # [100%]

1:mysql-server # # [100%]

[root@linux-a ~] #

Initialize the MySql database

[root@linux-a ~] # mysql_install_db

Installing all prepared tables

Fill help tables

To start mysqld at boot time you have to copy support-files/mysql.server

To the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER!

To do so, start the server, then issue the following commands:

/ usr/bin/mysqladmin-u root password' new-password'

/ usr/bin/mysqladmin-u root-h linux-a.test.com password' new-password'

See the manual for more instructions.

You can start the MySQL daemon with:

Cd / usr; / usr/bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:

Cd sql-bench; perl run-all-tests

Please report any problems with the / usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at

Http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

[root@linux-a ~] #

Modify mysql directory permissions

After installation, the mysql directory permissions are as follows

[root@linux-a] # ls-l / var/lib/mysql/

Total 8

Drwx- 2 root root 4096 08-16 16:55 mysql

Drwx- 2 root root 4096 08-16 16:55 test

Modify the mysql directory owner and user group to mysql

[root@linux-a] # chown-R mysql/ var/lib/mysql/

[root@linux-a] # chgrp-R mysql/ var/lib/mysql/

Start the mysql service

[root@linux-a ~] # service mysqld start

Start MySQL: [OK]

[root@linux-a ~] # mysql

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

Your MySQL connection id is 2 to server version: 5.0.22

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

Mysql >

The "mysql >" prompt appears, indicating that the installation is successful and that mysql has started normally.

Modify root user password

By default, the root password is empty after the mysql database is installed. For security reasons, the root user password needs to be modified.

[root@linux-a ~] # mysqladmin-u root password 111111 / / change the password of the root user to 111111

Log in to MySQL

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

Mysql [- u username] [- h host] [- p [password]] [dbname]

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

Several important catalogs of MySQL

After MySQL 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, it is impossible to learn in depth.

Let's introduce these catalogs.

1. Database directory

/ var/lib/mysql/

2. Configuration file

/ usr/share/mysql (mysql.server commands and configuration files)

3. Related orders

/ usr/bin (mysqladmin mysqldump, etc.)

4. Startup script

/ etc/rc.d/init.d/ (directory of startup script file mysql)

Power on and start mysql automatically

Check to see if mysql is in the automatic startup list.

[root@linux-a ~] # chkconfig-- list | grep mysql

Mysqld 0: close 1: close 2: close 3: close 4: close 5: close 6: close

0-6 is the seven startup modes of redhat, of which 3 is command line mode and 5 is graphical interface mode, which can be modified in / etc/inittab

Add MySQL to the startup service group of your system

[root@linux-a] # chkconfig-- add mysql

3) remove MySQL from the startup service group.

[root@linux-a] # chkconfig-- del mysql

Change the MySQL directory

The default data file storage directory for MySQL is / var/lib/mysql. If you want to move the directory to / home/data, you need to take the following steps:

1. Establish a data directory under the home directory

Cd / home

Mkdir data

2. Stop the MySQL service process:

[root@linux-a] # mysqladmin-u root-p shutdown

Enter password:

[root@linux-a ~] #

3. Move the entire directory of / var/lib/mysql to / home/data

Mv / var/lib/mysql / home/data/

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

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/ and copy one of them to / etc/ and rename it to my.cnf). The command is as follows:

[root@linux-a ~] # cp / usr/share/doc/mysql-server-5.0.22/my-medium.cnf / etc/my.cnf

A mysql profile is preset in this directory, and you can choose which profile to use according to the size of the database application.

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

In order to ensure that MySQL can work properly, you need to specify the location where the mysql.sock file is generated. Modify the value to the right of the equal sign on the socket=/var/lib/mysql/mysql.sock line to: / home/mysql/mysql.sock. Do the following:

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

# The MySQL server

[mysqld]

Port = 3306

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

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

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

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

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

# datadir=/var/lib/mysql (comment this line)

Datadir=/home/data/mysql (plus this line)

7. Restart the MySQL service

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

Common operations of MySQL

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

1. Display the database

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | test |

+-+

3 rows in set (0.00 sec)

Mysql has just been installed with two databases: mysql and test. The mysql library is very important. It contains the system information of MySQL. 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 > use mysql

Database changed

Mysql > show tables

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 library. All users who can operate on MySQL users are in this table.

Select * from user

5. Build a database:

Create database library name

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

Mysql > 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 > 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 > 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 > insert into name values (', 'Zhang San', 'male', '1971-10-01')

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

You can use the select command to verify the results.

Mysql > 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 > update name set csny='1971-01-10 'where xm=' Zhang San'

9. Delete the record

For example: delete Zhang San's record.

Mysql > delete from name where xm=' Zhang San'

10. Delete libraries and tables

Drop database library name

Drop table table name

Increase MySQL 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, and then type the following command:

Mysql > grant select,insert,update,delete on *. * to user_1@ "%" Identified by "123"

Example 1 the increase in users is very dangerous, if he knows the password of user_1, then he can log into your MySQL database on any computer on the Internet and do whatever he wants with your data. The solution is in example 2.

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 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 host.

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

If the new user cannot log in to MySQL, use the following command when logging in:

Mysql-u user_1-p-h 192.168.113.50 (- h followed by the ip address of the host to be logged in)

Here is a special hint (when using mysql front links)

-Open a user who can do anything remotely:

Grant all privileges on *. * to user_name@'%' identified by 'passwd'

-- Open a local user:

Grant all privileges on *. * to user_name@'localhost 'identified by' passwd'

What needs to be paid special attention here is to run it after opening an account:

Flush privileges

This can also be done when the account cannot be logged in.

At this point, the study on "the installation and configuration of MySql database under Linux and the introduction of simple operation" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.