Install and use mysql
First, download mysql installation file to install mysql need the following two files: mysql-server-4.0.16-0.i386.rpm mysql-client-4.0.16-0.i386.rpm download address is: http: // www .MYSQL.COM / DOWNLOADS / MYSQL.COM / DOWNLOADS / MySQL-4.0.html, open this page, drop the "Linux X86 RPM Downloads" item, find the "Server" and "Client Programs" item, download the two two RPM files required.
Second, the installation MySQL RPM file is the software installation package developed by Red Hat, and RPM allows Linux to exempt many complex procedures when installing packages. The parameter that is often used when installing is -IVH, where i indicates that the specified RMP package, V represents the details of the installation, and h indicates during installation.
The "#" symbol appears to display the current installation process. This symbol will continue until the installation is complete. 1) Install the server side running under the directory of two RMP files as follows: [Root @ Test1 local] # rpm -ivh mysql-server-4.0.16-0.i386.rpm displays the following information. Warning: mysql-server-4.0.16-0.i386.rpm: v3 DSA Signature: Nokey, Key ID 5072E1F5 preplay ... ################################################################################################################################################################################################################### ############### [100%] 1: mysql-server #################### ########################. . . . . . (Omitted) / usr / bin / mysqladmin -u root password 'new-password' / usr / bin / mysqladmin -u root -h test1 password 'new-password'. . . . . . (Omitted) Starting MySQLD daemon with databases from / var / lib / mysql As mentioned above, the server is installed. The test is successful to run NetStat to see if the mysql port is open. If the service has been started, the installation is 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.0.0.0:3306 0.0.0.0.0:0:0:3306 0.0.0.0:0:00 listen's display can see mysql The service has been launched. 2) Install the client to run as follows: [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 prepaing ... #################################################################################################################################################################################################################################################################################### #### [100%] 1: mysql-client ####################################################################################################################################################################################################################################################### ###### [100%] The display is complete. Use the following command to connect MySQL, whether the test is successful. Third, log in MYSQL
The command to log in to mysql is mysql, MySQL's usage syntax is as follows: mysql [-u username] [-h host] [-p [password]] [dbname] UserName and Password are MySQL username and password, MySQL initial management The account is root, no password, note: this root user is not a Linux system user. MySQL default user is root, because there is no password in the initial, just type mysql 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> "MySQL>" prompt, congratulations, success! The login format after the password is added as follows: mysql -u root -p enter password: (Input Password) where -U follows the username, -P requires the password, enter the password at the input password after entering the password. Note: This mysql file is in / usr / bin directory, with the launch file /etc/init.dc/init.dc/init.dc/init.dc/init.d/mysql, is not a file.
Fourth, several important directories of mysql
After the mysql installation is complete, it is not like a directory by default, its database file, configuration file, and command files are very important in different directories, especially for Linux's beginners, because Linux itself is in the directory structure of Linux itself. More complicated, if you don't know the mysql installation directory, you will not talk about in-depth learning.
Let's take a look at these directorys.
1. Database / VAR / LIB / MYSQL /
2, configuration file /usr/share/mysql (Mysql.server command and configuration file)
3, related commands / usr / bin (mysqladmin mysqldump and other orders)
4, start the script /etc/rc.d/init.d/ (start the script file mysql directory)
V. Modify the login password
MySQL has no password by default, and the importance of adding passwords in 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, Example 1: Add a password to the root 123456. Type the following command: [root @ Test1 local] # / usr / bin / mysqladmin -u root password 123456 Note: Because the root is no password, the -p old password can be omitted.
3, the test is modified 1) Do not have a password to log in [root @ Test1 local] # mysql error 1045: Access Denied for user: 'root @ localhost' (using password '(using password') Display error, the password has been modified. 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 modify the password through the mysqladmin command, or change the password by modifying the library. Six, start and stop
1. Start the MYSQL installation After start the file mysql 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) Look at the mysql in the automatic start list [root @ Test1 local] # / sbin / chkconfig -list 2) Add mysql to your system's launch service group to go [Root @ Test1 local] # / sbin / chkconfig - add mysql 3) Remove MySQL from the startup service group. [Root @ Test1 local] # / sbin / chkconfig - DEL MYSQL
Seven, change mySQL directory
MySQL default data file storage directory is / var / lib / mysql. If you want to move the directory to / home / data, you need to do next steps:
1. Establish a Data directory CD / HOME MKDIR DATA in the home directory
2, stop the mysql service process: mysqladmin -u root -p Shutdown
3, move the / var / lib / mysql's entire directory to / home / data mv / var / lib / mysql / home / data / this moves mysql's data file to / home / data / mysql
4, find my.cnf configuration file if there is no My.cnf configuration file in / etc / directory, please find the * .cnf file, copy one of the / etc /
And renamed my.cnf. The command is as follows: [Root @ Test1 mysql] # cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
5. Edit MySQL's profile /etc/my.cnf To ensure that mysql works properly, you need to indicate the location of the mysql.sock file. modify
Socket = / var / lib / mysql / mysql.sock The value of the right side is: /Home/mysql/mysql.sock. The operation is as follows: vi my.cnf (edited by the VI tool, find the following data modification) # The mysql server [mysqld] port = 3306 #socket = /var/lib/mysql/mysql.sock (original content, In order to more stabilize "#" comment this line) socket = /Home/data/mysql/mysql.sock (couple this row) 6, modify the mysql startup script /etc/rc.d/init.d/mysql, the final, need Modify the mysql launch script /etc/rc.d/init.d/mysql, in the DATADIR = / var / lib / mysql, etc.
The path is changed 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 line) DATADIR = / home / data / mysql (plus this line)
7, restart mySQL service /etc/rc.d/ininit.d/mysql start or use the reboot command to restart Linux If the work is moved, it is successful, otherwise check it in front of the 7 steps.
Eight, the common operation of mysql
Note: After each command in MySQL, you must end.
1, display database mysql> show databases; -------- | Database | ---------- | MySQL | | TEST | ------- - 2 ROWS IN SET (0.04 sec) MySQL just installed with two databases: MySQL and TEST. The MySQL library is very important, there is a MySQL system information, we change your password and add users, actually use the related table in this library.
2. Display the table mysql> use mysql in the database; (open the library, do this library for each 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 sheet: Describe table name;
4. Display the record in the table: SELECT * FROM table name; for example: Displays the record in the User table in the MySQL library. All users who can operate to MySQL users are in this table. SELECT *.
5, Jian Library: CREATE DATABASE library name; for example: Create a name bit AAA library mysql> create database aaa; 6, Table: Use the table: CREATE TABLE table name (field setting list); for example: started The AAA library establishes the table name, ID (serial number, automatic growth), XM (name), XB (gender), CSNY (born month), four fields, 4,,, 性, 出, 3) AUTO_INCREMENT NOT NULL Primary Key, XM Char (8), XB Char (2), CSNY Date); You can use the describe command to view the original 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 number of related records. Mysql> Insert INTO NAME VALUES ('', 'Zhang San', 'Men,' 1971-10-01 '); MySQL> Insert Into Name Values (' ',' Baiyun ',' Female ',' 1972-05 -20 '); 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. Modifying records, for example: change Zhang San's birth year to 1971-01-10 mysql> Update name set csny = '1971-01-10' where xm = 'Zhang San';
9. Delete record, for example: Delete a record of Zhang San. Mysql> Delete from name where xm = 'Zhang San';
10, delete libraries and delete the table DROP DATABASE library name; DROP TABLE table name;
Nine, add MYSQL users
Format: Grant SELECT ON Database. * To User Name @ Login Host Identified By "Password" Example 1, add a user user_1 password 123, let him log in on any host, and have queries, insert, modify, Delete permissions. First, use the root user into MySQL, then type the following command:
MySQL> Grant SELECT, INSERT, UPDATE, DELETE ON *. * To mailto: user_1 @ "" "Identified by" 123 "; Example 1 Increased users are very dangerous, if you know the user_1 password, then he can be online Any computer logged in to your MySQL database and see you for your data for your data.
Example 2, add a user user_2 password 123, allowing this user to log in in localhost, and can query, insert, modify, and delete database AAA (localhost refers to the host host, the host of the mysql database). This way, this user does not use the password knows User_2, he cannot directly access the database from the Internet, and 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 you can't log in with new users, use the following command when logging in:
MySQL -U User_1 -P-H 192.168.113.50 (followed by H, is the IP address to log in to the host)
Ten, backup and recovery
1, backup
For example: back up the AAA library created by the above example into the file back_aaa
[root @ Test1 root] # CD / home / data / mysql (enter the library directory, this library has been turned to / home / data / mysql to / home / data / mysql, see the above section) [root @ Test1 MySQL] # mysqldump -u root -p --opt aaa> back_aaa
2, restore
[root @ Test mysql] # mysql -u root -p ccc
