I. Introduction
I want to use Linux for a long time, because there is no hard task, there is no system to learn, recently
The job needs to be used under Linux Mysql. I thought that I have experience in using SQL Server under Windows.
I feel that Mysql installed under Linux should be like a thing, and you can get along with MySQL when you really install and use mysql.
Multi-bending road, encounter a lot of problems, after all, Linux and Windows itself have great differences. In order to make it like me
The beginners will bend less in the process of learning, starting as soon as possible, write this article, I hope to help you. this
The Linux environment of the text is RED HAT 9.0, MySQL is 4.0.16.
Second, install mysql
1. Download mysql installation file to install MySQL requires 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-4.0.html, open this network
Page, drop-down web page to find "Linux X86 RPM Downloads" item, find "Server" and "Client
Programs item, download the two RPM files required.
2, install the mysql rpm file is the software installation package developed by Red Hat, rpm allows Linux to exempt when installing the package
Many complex procedures. The parameter that is often used when installing is -IVH, where i means the specified
The RMP package, V represents the details of the installation, H indicates that "#" symbols during installation are currently displayed.
The 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 prepaing ... #############################################################################################################################################################################################################################################################################################
[100%] 1: mysql-server ######################################################################################################################################################################################################################### ##
[100%]. . . . . . (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. Test if it is successful to run NetStat to see the mysql port
Whether to 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:0:3306 0.0.0.0:0:0:* listen The above shows that the mysql service has been started. 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, Keyid 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 account number
Is root, no password, note: This root user is not Linux system user. MySQL default user is
ROOT, due to the initial no password, the first time I will only 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. 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, the launch of the lapse
Piece /etc/init.d/mysql is not a file.
Fourth, several important directories of mysql
After the mysql installation is complete, it is not equally installed in a directory by default, its database file, configuration
Documents and command files are very important in different directories, especially for Linux's beginners.
Because Linux itself is more complicated, if you don't know the mysql installation directory, there is no
From talking in depth.
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) 5, 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 successfully 1) Do not have a password to log in [root @ Test1 local] # mysql error 1045: Access Denied for user: 'root @ localhost' (Using Password)
NO) Display error, indicating that 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 when you need to start
The command can be. [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 transfer your directory
The following steps are required under / Home / Data:
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 * .cnf text to / usr / share / mysql /
Parts, copy one of which is / 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 Value on the right side of a line
To: /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 "#"
Release this line) Socket = /HOME/Data/mysql/mysql.sock (coupled with this line)
6, modify the mysql startup script /etc/rc.d/init.d/mysql, you need to modify the mysql boot script /etc/rc.d/init.d/mysql, put it
DataDir = / var / lib / mysql, the path to the right side 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. MySQL library is very important, there is
System information of MySQL, we change your password and add users, actually use related tables in this library to operate
Work.
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 with MySQL users here
Table. SELECT *.
5, build the library: CREATE DATABASE library name; for example: Create a name bit AAA library mysql> create databases aaa; 6, the table: USE library name; 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, deletion library and delete the table DROP DATABASE library name; DROP TABLE table name; Nine, add MYSQL users
Format: Grant SELECT ON Database. * To Username @ Login Host Identified By Password
"Example 1, add a user user_1 password 123, let him log in on any host, and for all data
The library has the permissions of query, insert, modification, and delete. First use the root user into MySQL, then type the following
command:
MySQL> Grant SELECT, INSERT, UPDATE, DELETE ON *. * To user_1 @ "%"
Identified by "123"; Example 1 Increased users are very dangerous, if you know the password of User_1, then he can go online.
Seeing an example 2 for your data for your data on your computer and the solution to your data.
Example 2, add a user user_2 password 123, so that this user can only log in on LocalHost, and
You can query, insert, modify, and delete database AAA (localhost refers to this host, ie
The host where the mysql database is located), so that users don't use the password of User_2, he cannot
Access the database directly, you 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
VAL / lib / mysql go to / home / data / mysql, see the above seven part of the content) [Root @ Test1 MySQL] # mysqldump -u root -p --opt aaa> back_aaa
2, restore
[root @ Test mysql] # mysql -u root -p ccc 9CBS ID Yishao (Feilong) (http://www.94888.net)
