I. Introduction
I have been using Linux for a long time. Since there is no hard task, there is no system to study, recently, due to work, Linux must be used.
The following mysql. I thought that there was a experience of using SQL Server under Windows. I feel that Mysql installed under Linux should be like a thing.
A lot of detours when I really installed and using MySQL, met a lot of problems, after all, Linux and Windows itself have great differences. In order to let and I
The starters are less into the way in the process of learning, and get started as soon as possible, write this article, I hope to help you. The Linux environment in this article 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.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.
2. Installing the MySQL RPM file is the software installation package developed by Red Hat. RPM allows Linux to exempt a lot of complex procedures when installing the package. This command
The parameters used for installing are -IVH, where i indicates that the specified RMP package, V represents the details of the installation, H represents 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, the command to log in to mysql login 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, respectively, MySQL's initial management account is root, no password, pay attention:
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 equally installed in a directory by default, its database file, configuration file, and command files are different.
Directory, understand these directories, especially for beginners of Linux, because Linux itself is more complicated, if not
Clear Mysql installation directory will not talk 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 system information of MySQL, we change your password
And add users, actually use the related tables in this library to operate.
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, 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 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
Except for permissions. 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 The user is very dangerous, if you know the user_1 password, then he can be online Log in to your computer
The mysql database is desired to find out for your data.
Example 2, add a user user_2 password 123, so that this user can only log in on localhost and can query the database AAA,
Insert, modify, delete the operation (localhost refers to the host, the host where the mysql database is located), so that the user knows
User_2 password, he cannot access the database directly 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 transferred by val / lib / mysql
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
