Linux installation and use of Mysql detailed introduction _mysql

First, install MySQL

1, download the MySQL installation files
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://dev.mysql.com/downloads/mysql-4.0.html, open this page, Drop-down page to find "Linux x86 RPM Downloads" item, find "Server" and "Client Programs "Item, download the required two RPM files.

2, install MySQL
The rpm file is a software installation package developed by Red Hat, which allows Linux to remove many complex procedures when installing software packages. The commonly used parameter for this command at installation is –IVH, where I indicates that the specified RMP package will be installed, V represents details of the installation, and H indicates that the "#" symbol appears during installation to display the current installation process. This symbol will continue until the installation is complete before stopping.

1) Install 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
displays the following information.
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 '
... (omitted to display)
starting mysqld daemon with databases From/var/lib/mysql
If the information appears, the server is installed. Test whether the success can be run netstat see whether the MySQL port is turned on, such as open means that the service has been started, the installation was successful. The default port for MySQL is 3306.
[root@test1 local]# netstat-nat
Active Internet connections (servers and established)
Proto recv-q send-q Loca The "address" Foreign address State
tcp00 0.0.0.0:3306 0.0.0.0:* LISTEN
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%]
Show installation complete.
Connect MySQL with the following command to test for success.
Note: In fact, you can also download the compressed file, directly after the local decompression can be.

Second, login MySQL

The command to log in to MySQL is MySQL, and the usage syntax for MySQL is as follows:
MySQL [-u username] [H-host] [-p[password]] [dbname]
Username and password are MySQL username and password respectively, MySQL's initial management account is root, no password, note: This root user is not Linux system users. MySQL Default user is root, because the initial no password, the first time to enter a MySQL can only.
[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>
There is a "mysql>" prompt, congratulations, installation success!
The login format with the password added is as follows:
Mysql-u root-p
Enter Password: (enter password)
Where-U is followed by the username,-p requires a password, enter the password at the input password.

Attention:
1. This MySQL file is in the/usr/bin directory, and the startup file/etc/init.d/mysql is not a file.
2. If you log on to another host locally, the format is as follows:
./mysql-u Root-p-H 192.168.xx.xx
The front must be added./

Three, several important directories of MySQL

MySQL installation is not like the default installation of SQL Server in a directory, its database files, configuration files and command files in different directories, it is very important to understand these directories, especially for Linux beginners, because Linux itself is more complex directory structure, If you don't know the MySQL installation directory, there's no way to get into the deep study.

Here are some of these directories.

1. Database Directory
/var/lib/mysql/

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

3. Related Orders
/usr/bin (mysqladmin mysqldump order)

4. Startup script
/etc/rc.d/init.d/(Directory of startup script files MySQL)

Four, modify the login password

MySQL defaults to no password, the importance of installing the password added is self-evident.

1. Order
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 of 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. Test whether the modification is successful
1 Login 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 modified.

2 Login with modified password
[Root@test1 local]# mysql-u root-p
Enter Password: (enter 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 through the mysqladmin command to modify the password, or you can change the password by modifying the library.

V. Start and stop

1, start
MySQL installation is completed after the boot file MySQL in the/ETC/INIT.D directory, when you need to start running the following command.
[Root@test1 init.d]#/etc/init.d/mysql start

2. Stop
/usr/bin/mysqladmin-u root-p shutdown

3, automatic start
1 See if MySQL is in the auto start list
[Root@test1 local]#/sbin/chkconfig–list
2 Add MySQL to your system's startup service group
[Root@test1 local]#/sbin/chkconfig–add MySQL
3 Remove MySQL from the Startup service group.
[Root@test1 local]#/sbin/chkconfig–del MySQL

Vi. changing the MySQL directory

MySQL defaults to the data file storage directory for/var/lib/mysql. The following steps are required if you want to move the directory to/home/data:

1, the home directory to establish the data directory
Cd/home
mkdir data

2, the MySQL service process to stop:
Mysqladmin-u root-p shutdown

3. Move/var/lib/mysql Entire directory to/home/data
mv/var/lib/mysql/home/data/
This will be the MySQL data file moved to the/home/data/mysql

4, find my.cnf configuration file
If there is no MY.CNF configuration file in the/etc/directory, please find *.cnf file under/usr/share/mysql/, copy one to/etc/and rename to my.cnf). The order is 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 works properly, you need to indicate where the Mysql.sock file will be generated. Modify the value on the right side of the equal sign in Socket=/var/lib/mysql/mysql.sock line:/home/mysql/mysql.sock. The operation is as follows:
VI my.cnf (with VI tool to edit MY.CNF file, find the following data modification)
# The MySQL server
[Mysqld]
Port = 3306
#socket =/var/lib/mysql/mysql.sock (original content, in order to be more secure with "#" comment on this row)
Socket =/home/data/mysql/mysql.sock (plus this row)

6, modify the MySQL startup script/etc/rc.d/init.d/mysql
Finally, you need to modify the MySQL startup script/etc/rc.d/init.d/mysql, datadir=/var/lib/mysql the path to the right of the equal sign in one line, and change it to your current actual storage path: Home/data/mysql.
[Root@test1 etc]# Vi/etc/rc.d/init.d/mysql
#datadir =/var/lib/mysql (note this row)
Datadir=/home/data/mysql (plus this row)

7, restart the MySQL service
/etc/rc.d/init.d/mysql start
or restart Linux with the reboot command
If the work is moving normally, it will be successful, otherwise check the previous 7 steps.

Seven, MySQL common operation

Note: Each command in MySQL is followed by a semicolon;

1. Display Database
mysql> show databases;
+----------+
| Database |
+----------+
| mysql|
| Test |
+----------+
2 rows in Set (0.04 sec)
MySQL has just finished installing two databases: MySQL and test. MySQL library is very important, it has MySQL system information, we change the password and new users, is actually used in this library related tables to operate.

2, display the table in the database
mysql> use MySQL; (Open the Library, operate on each library to open this library)
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, displays the records in the user table in the MySQL library. All users who can operate on the MySQL user are in this table.
Select * from user;

5, build the library:
Create database library name;
For example: Create a library with a first name of AAA
mysql> Create databases AAA;

6, the establishment of the table:
Use library name;
CREATE TABLE table name (field set list);
For example, create a table name in the AAA library you just created, with ID (ordinal, automatic growth), XM (name), XB (gender), CSNY (year of birth) four fields
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 newly established 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: Add a few related records.
mysql> INSERT into name values (', ' John ', ' Men ', ' 1971-10-01 ');
mysql> INSERT into name values (', ' white clouds ', ' women ', ' 1972-05-20 ');
The Select command can be used to validate the results.
Mysql> select * from name;
+----+------+------+------------+
| ID | XM | XB | CSNY |
+----+------+------+------------+
| 1 | John | Male | 1971-10-01 |
| 2 | White clouds | Female | 1972-05-20 |
+----+------+------+------------+

8. Change the Record
For example: Change the birth date of John to 1971-01-10
Mysql> Update name set csny= ' 1971-01-10 ' where xm= ' John ';

9, delete the record
For example, delete a John record.
mysql> Delete from name where xm= ' John ';

10, delete the database and delete the table
drop Database library name;
The drop table table name;

11. View MySQL Version
The commands in mysql5.0 are as follows:
Show variables like ' version ';
Or: Select version ();

Viii. increasing MySQL Users

Format:grant SELECT on database. * To User name @ login 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, modify, delete permissions. First connect the root user 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 of the user is very dangerous, if you know the user_1 password, then he can be on any computer on the Internet to login to your MySQL database and your data to do whatever you want, the solution to see Example 2.

Example 2, add a user user_2 password is 123, so that users can only log on the localhost, and the database can query, insert, modify, delete operations (localhost refers to the local host, The MySQL database is the host, so that users know the user_2 password, he can not access the database directly from the Internet, only through the MySQL host to operate AAA library.

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

With the new user if you can't log in to MySQL, use the following command when you log in:
Mysql-u user_1-p-H 192.168.113.50 (-H followed by the IP address of the host to be logged in)

Ix. Backup and Recovery

1, Backup
For example, the AAA library created by the previous example is backed up into file back_aaa

[Root@test1 root]# Cd/home/data/mysql (into the library directory, this example library has been transferred from Val/lib/mysql to/home/data/mysql, see section VI above)
[Root@test1 mysql]# mysqldump-u root-p--opt aaa > BACK_AAA

2. Recovery
[Root@test mysql]# mysql-u root-p CCC < BACK_AAA