Mysql installation under Linux configuration

2010-11-09  来源:本站原创  分类:OS  人气:108 

1 the MySQL installation files for Linux

Installing MySQL for Linux requires the following two files:


Download address:, open this page, scroll down page to find the "Linux x86 generic RPM (statically linked against glibc 2.2.5) downloads" items found " Server "and" Client programs "item, these two need to download rpm files.
2, Linux install MySQL

rpm files is Red Hat's software package developed, rpm allows Linux to install a package eliminating many complex procedures. This command is commonly used in the installation parameters-ivh, where i will install the specified rmp package, V is the installation details, h said during the installation a "#" symbol to show the current installation process. This symbol will continue to stop after the installation is complete.

1) Install the server-side

There are two rmp files in the directory, run the following command:

[Root @ test1 local] # rpm-ivh MySQL-server-5.1.7-0.i386.rpm MySQL-client-5.1.7-0.i386.rpm

Displays the following information.

warning: MySQL-server-5.1.7-0.i386.rpm

signature: NOKEY, key ID 5072e1f5

Preparing ... ########################################### [100% ]

1: MySQL-server ########################################### [100 %]

. . . . . . (Omit the display)

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

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

. . . . . . (Omit the display)

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

In case of above information, the server installed. Test can be run netstat to see the success of Mysql port is open, such as opening, said the service is started, the installation was successful. Mysql default port 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 * LISTEN

Shown above we can see that MySQL service has started.

2) install the client

Run the following command:

[Root @ test1 local] # rpm-ivh MySQL-client-5.1.7-0.i386.rpm

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

Preparing ... ########################################### [100% ]

1: MySQL-client ########################################### [100 %]

The installation is completed.

Connection with the following command mysql, test was successful.

Third, the log MySQL

Log in the MySQL command mysql, mysql to use the following syntax:

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

MySQL username and password are the username and password, mysql initial management account is root, no password Note: This is not a Linux system as root user. MySQL default user is root, not as the initial password the first time into the time you can just type mysql.

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


The emergence of "mysql>" prompt, Congratulations, the installation is successful!

Increased the password login form:

mysql-u root-p

Enter password: (password)

Which is followed by-u username,-p password is required to enter a password after entering the password.

Note: The mysql file in / usr / bin directory, and after talking about the startup file / etc / init.d / mysql is not a file.

Four, MySQL directory of several important

MySQL installation is complete, unlike the default installation of SQL Server in a directory, its database files, configuration files and command files are in different directories, these directories is very important to understand, especially for Linux beginners, because the directory structure of Linux itself more complicated, not sure if it would be impossible to MySQL installation directory depth study.

Here to tell us about these directories.

1, the database directory

/ Var / lib / mysql /

2, the configuration file

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

3, the relevant command

/ Usr / bin (mysqladmin mysqldump command, etc.)

4, the startup script

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

Fifth, change password

MySQL default is no password, installed to increase the importance of passwords is self-evident.

1, the command

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

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

2, examples

Example 1: to add a root password 123456.

Type the following command:

[Root @ test1 local] # / usr / bin / mysqladmin-u root password 123456

Note: Since the beginning of root without a password, so-p old password one can be omitted.

3, the test is successfully modified

1) no password

[Root @ test1 local] # mysql

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

Error, indicating the password has been changed.

2) using 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.



This is done by the mysqladmin command to modify the password, but also by modifying the library to change the password.

Sixth, start and stop

1, start

After installation is complete, start the MySQL file mysql in / etc / init.d directory, you need to run the following command to start.

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

2, stopped

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

3, automatically start

1) look at whether the auto start mysql list

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

2) to add to your MySQL system inside to start the service group

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

3) start the MySQL service group from which to delete.

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

Seven, change the MySQL directory

MySQL data files stored in the default directory is / var / lib / mysql. If necessary directory to / home / data required under the following steps:

1, home directory, create the data directory

cd / home

mkdir data

2, stopped the MySQL service process:

mysqladmin-u root-p shutdown

3, the / var / lib / mysql entire directory to / home / data

mv / var / lib / mysql / home / data /

This put the MySQL data files moved to / home / data / mysql under

4, find the my.cnf configuration file

If / etc / my.cnf configuration file directory is not, go to / usr / share / mysql / *. cnf files to find, one copy to / etc / and renamed my.cnf) in. Command as follows:

[Root @ test1 mysql] # cp / usr / share / mysql / my-medium.cnf / etc / my.cnf

5, edit the MySQL configuration file / etc / my.cnf

To ensure that MySQL can work, you need to specify the location of mysql.sock file generation. Modify socket = / var / lib / mysql / mysql.sock line is the middle number right: / home / mysql / mysql.sock. As follows:

vi my.cnf (use vi to edit your my.cnf file, find the following data modified)

# The MySQL server


port = 3306

# Socket = / var / lib / mysql / mysql.sock (original content, in order to be more secure with "#" comment this line)

socket = / home / data / mysql / mysql.sock (with this trip)

6, to modify the MySQL startup script / etc / rc.d / init.d / mysql

Finally, the need to modify the MySQL startup script / etc / rc.d / init.d / mysql, to which datadir = / var / lib / mysql row, right-hand side of the path into the actual storage of the path you are now: home / data / mysql.

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

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

datadir = / home / data / mysql (with this trip)

7, restart the MySQL service

/ Etc / rc.d / init.d / mysql start

Or use the reboot command to reboot Linux

If this works move on success, otherwise front of the 7-step control check again.

Eight, MySQL's popular operating

Note: MySQL after each command should be a semicolon; end.

1 shows the database

mysql> show databases;


| Database |


| Mysql |

| Test |


2 rows in set (0.04 sec)

Mysql just installed two databases: mysql and test. mysql database is very important, it contains information on the MySQL system, we change the password and add users actually use this library in the relevant table.

2 shows the database tables

mysql> use mysql; (open the library, each library to operate the library will open, 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 shows the structure of the data sheet:

describe table;

4, show records in the table:

select * from table name;

For example: Display mysql database user table record. MySQL users can operate all the users in this table.

Select * from user;

5, building a database:

create database database name;

For example: Create a library name bit aaa

mysql> create databases aaa;

6, construction of the table:

use library name;

create table table name (field set list);

For example: aaa created in the library just to create the table name, table with id (serial number, auto-grow), xm (name), xb (gender), csny (birth years) four field

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 see the newly created table structure.

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, increase the record

For example: to increase a few relevant records.

mysql> insert into name values ​​('',' Joe Smith ',' male ', '1971 -10-01');

mysql> insert into name values ​​('',' clouds ',' female ', '1972 -05-20');

Select command can be used to verify the results.

mysql> select * from name;


| Id | xm | xb | csny |


| 1 | Joe Smith | M | 1971-10-01 |

| 2 | Bai Yun | Female | 1972-05-20 |


8 Revision History

For example: Joe Smith's date of birth changed to 1971-01-10

mysql> update name set csny = '1971-01-10 'where xm =' Joe Smith ';

9, delete the record

For example: remove the seating of the record.

mysql> delete from name where xm = 'Joe Smith';

10, delete the library and delete tables

drop database database name;

drop table table name;

Nine, increasing the MySQL user

Format: grant select on database .* to username @ log host identified by "password"

Example 1, add a user user_1 password is 123, so that he can log on any host, and all databases have query, insert, update, delete permissions. First, the root user connected to MySQL, and then type the following command:

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

Example 1 increases the user is very dangerous, if you know user_1 password, then he can be online on any computer to access your data in your MySQL database and do whatever they want, and the solution, see Example 2.

Example 2, add a password for the user user_2 123, so that the user can only log on localhost, and can query the database aaa, insert, update, delete operations (localhost means the local host, that is where the MySQL database that hosts ), so that users know user_2 that the use of a password, he can not access the database directly from the Internet, only through the host operating aaa MYSQL database.

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

If you log in using the new user can not MySQL, in the log with the following command:

mysql-u user_1-p-h (-h followed by the host to log the ip address)

X. backup and recovery

1, the backup

For example: This example creates a database backup to a file back_aaa aaa in

[Root @ test1 root] # cd / home / data / mysql (enter the library catalog, in this case library has been val / lib / mysql to / home / data / mysql, see part VII above)

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

2, recovery

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