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

Second, install Mysql

1. Download the installation file of MySQL

The following two files are required to install MySQL:

MySQL-server-4.0.16-0.i386.rpm

MySQL-client-4.0.16-0.i386.rpm

Download address: http://www.mysql.com/downloads/mysql-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

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-server-4.0.16-0.i386.rpm

The following information is displayed.

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

Preparing... # # [100%]

1:MySQL-server # # [100%]

. (omit display)

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

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

. (omit display)

Starting mysqld daemon with databases from / var/lib/mysql

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

[root@test1 local] # netstat-nat

Active Internet connections (servers and established)

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

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN

The above shows that the MySQL service has been started.

2) install the client

Run the following command:

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

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

Preparing... # # [100%]

1:MySQL-client # # [100%]

Shows that the installation is complete.

Use the following command to connect to mysql and test for success.

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.

[root@test1 local] # mysql

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

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

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

Mysql >

The "mysql >" prompt appears. Congratulations, the installation is successful!

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

Mysql-u root-p

Enter password: (enter password)

The user name is followed by-u, and the password is required by-p. Enter the password at the place where you enter the password.

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

IV. 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)

Change the login password

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

1. Command

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

Format: mysqladmin-u username-p old password password new password

2. Examples

Example 1: add a password 123456 to root.

Type the following command:

[root@test1 local] # / usr/bin/mysqladmin-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

ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

An error is displayed indicating that the password has been changed.

2) Log in with the modified password

[root@test1 local] # mysql-u root-p

Enter password: (enter the modified password 123456)

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

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

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

Mysql >

Success!

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

VI. Start and stop

1. Start

After the MySQL installation is completed, the startup file mysql is in the / etc/init.d directory. Run the following command when you need to start.

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

2. Stop

/ usr/bin/mysqladmin-u root-p shutdown

3. Automatic start

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

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

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

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

3) remove MySQL from the startup service group.

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

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

Mysqladmin-u root-p shutdown

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@test1 mysql] # cp / usr/share/mysql/my-medium.cnf / etc/my.cnf

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

/ etc/rc.d/init.d/mysql start

Or restart Linux with the reboot command

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 |

+-+

| | mysql |

| | test |

+-+

2 rows in set (0.04 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; (opening the library and operating on each library requires opening the library, similar to foxpro)

Database changed

Mysql > show tables

+-+

| | Tables_in_mysql |

+-+

| | 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 library. All users who can operate on MySQL 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 > 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 the number of 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)

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 (go to the library directory, this example library has been transferred from val/lib/mysql to / home/data/mysql, see part VII above)

[root@test1 mysql] # mysqldump-u root-p-- opt aaa > back_aaa

2. Restore

[root@test mysql] # mysql-u root-p ccc < back_aaa

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.