Detailed installation and configuration of MySQL in Linux system

Two, install MySQL
1, download mysql installation file
Install MySQL need the following two files:
mysql-server-4.0.16-0.i386.rpm
Mysql-clien t-4.0.16-0.i386.rpm
Download Address: http://www.mysql.com/downloads/mysql-4.0.html, open this page, Drop-down page to find "Linux x86 rpm Downloads" item , locate the "Server" and "Client programs" items
to download the required two RPM files.
2, installing the MySQL
rpm file is a software installation package developed by Red Hat Company, and RPM 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
indicates details at installation time, 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
Displays the following letter Interest.
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 Local address Foreign
TCP 0 0 0.0.0.0:3306 0.0.0.0:* 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.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.

Third, 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. The MySQL default user is root, because
No password Initially, just type MySQL the first time you enter.
[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.
Note: This MySQL file is in the/usr/bin directory and is not a file/etc/init.d/mysql the startup file that follows.
Four, 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's own directory structure is more complex, if not clear MySQL installation directory that can not talk about in-depth 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)
V. 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.

Vi. Initiation and cessation
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

Vii. change 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. 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.

Viii. common operation of MySQL

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, 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 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 (, 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 (|　　 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 ', ' Male ', ' 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;

Nine, increase the MySQL user
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 in 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 do whatever you want, the solution is shown in 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, that is, the MySQL database
In 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 the 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)

10, Backup and Recovery
1, Backup
For example: To back up the AAA library created in the previous example to file Back_aaa
[Root@test1 root]# cd/home/data/mysql (into the library directory, this example library has been provided by VA L/lib/mysql go to/home/data/mysql, see the above parts)
[root@test1 mysql]# mysqldump-u root-p–opt aaa > BACK_AAA
2 , restore
[root@test mysql]# mysql-u root-p CCC < BACK_AAA