I. Introduction
It has been long time to use Linux. Because there is no hard task, there is no system study, and recently, since the work needs to use mysql under Linux. I thought that there was a experience of using SQL Server under Windows. I feel that ISQL installed under Linux should be like a thing. It can have gone a lot of detours when I really install and use mysql. I have a lot of problems. After all, Linux and Windows itself have a big difference. . In order to let the beginners in the study less into the process of learning, 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 files
Install MySQL requires the following two files:
MySQL-Server-4.0.16-0.i386.rpm
Mysql-client-4.0.16-0.i386.rpm
The download address is: http://www.mysql.com/downloads/mysql-4.0.html, open this page, drop the "Linux X86 RPM Downloads" item, find "Server" and "Client Programs" items, download and need The above two RPM files.
2, install MySQL
The 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 is installing the parameters that are -IVH, where i indicates that the specified RMP package, V represents the details of the installation, H indicates that "#" symbols during the installation show the current installation process. This symbol will continue until the installation is complete.
1) Install the server side
Run the following command in the directory of two RMP files:
[root @ Test1 local] # rpm -ivh mysql-server-4.0.16-0.i386.rpm
Show the following information.
Warning: mysql-server-4.0.16-0.i386.rpm: V3 DSA Signature: Nokey, Key ID 5072E1F5
Preparing ... #################################################### ]
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
If there is a message, 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.0:3306 0.0.0.0:0:8 Listen
The above 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.rpmwarning: mysql-client-4.0.16-0.i386.rpm: V3 DSA Signature: Nokey, Key ID 5072e1f5
Preparing ... #################################################### ]
1: mysql-client ########################################### %]
The display is installed.
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's initial management account is root, no password, note: This root user is not 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 appears, congratulations, success!
The login format after the password is added as follows:
Mysql -u root -p
ENTER Password: (Enter the password)
Where -U follows the username, -p requires the password, enter the password at the input password after entering the pass.
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 directory
/ VAR / LIB / MYSQL /
2, configuration file
/usr/share/mysql(Mysql.server command and configuration file)
3, related orders
/ usr / bin (mysqladmin mysqldump, etc.)
4, start the script
/etc/rc.d/init.d/ (Start 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
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 does not have a password, the -p old password can be omitted.
3, whether the test is modified successfully
1) Log in without password
[root @ Test1 local] # mysql
Error 1045: Access Denied for User: 'root @ localhost' (Using Password: NO)
Display errors, 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
After the mysql installation is complete, 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 whether mysql is in the automatic launch list
[root @ Test1 local] # / sbin / chkconfig -list
2) Add MySQL to your system's start-up service group
[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 in the home directory
CD / home
Mkdir Data
2, stop the MySQL service process:
mysqladmin -u root -p shutdown
3, move the / var / lib / mysql throughout the directory to / home / data
MV / VAR / LIB / MYSQL / HOME / DATA /
This will move mySQL 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 to / usr / share / mysql / down, copy one of the one to / etc / and rename my.cnf). The command is as follows:
[root @ Test1 mysql] # cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
5, edit mySQL configuration file /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 (edit the My.cnf file with the VI tool, find the following data modification) # The mysql server
[mysqld]
Port = 3306
#Socket = /VAR/LIB/Mysql/mysql.sock (formerly, in order to more stabilize "#" comment this line)
Socket = /Home/data/mysql/mysql.sock (plus this line)
6, modify the mysql boot script /etc/rc.d/init.d/mysql
Finally, you need to modify the mysql boot script /etc/rc.d/init.d/mysql, turn the path in the DATADIR = / var / lib / mysql, the right side of the right, change 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 the MySQL service
/etc/rc.d/init.d/mysql start
Or use the reboot command to restart Linux
If you work properly, you will be 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 the database
mysql> show data;
--------
| Database |
--------
| mysql |
| Test |
--------
2 rows in set (0.04 sec)
MySQL has just installed 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 in the database
Mysql> Use mysql; (open library, do this library for each library, similar to foxpro)
Database change
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;
4. Display records in the table:
SELECT * FROM table name;
For example: Displays a 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 library of a name-bit AAA
Mysql> CREATE DATABASES AAA;
6, Jian Table:
USE library name;
CREATE TABLE Name (Field Settings List);
For example, in the AAA library just created, the table name is established in the table (serial number, automatic growth), XM (name), XB (gender), CSNY (Birthday), 4 fields.
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 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 record
For example: add a number of related records.
Mysql> Insert INTO Name Values ('', 'Zhang 3', 'Men,' 1971-10-01 ');
MySQL> Insert Into Name Values ('', 'Baiyun', 'Female', '1972-05-20');
You can verify the result with the select command.
mysql> Select * from name;
-- ------ ---- ------------
| ID | XM | XB | CSNY |
-- ------ ---- ------------
| 1 | Zhang 3 | Male | 1971-10-01 |
| 2 | Baiyun | Female | 1972-05-20 |
-- ------ ---- ------------
8, modify the record
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 and deletion
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, allowing him to log in on any host and have the permissions of all databases, insert, modify, delete. First use to root users 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 log in to your MySQL database on any computer on the Internet and to see the 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 transferred from val / lib / 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
