Installing and using MySQL under Linux

Introduction
Want to use Linux for a long time, because there is no hard task has not been systematic learning, recently due to work need to use MySQL under Linux. I thought I had the experience of using SQL Server under Windows, and I thought it would be a breeze to install MySQL under Linux, which made a lot of detours when I actually installed and used MySQL, and met many problems, after all, Linux and windows were very different.

First, Introduction

Want to use Linux for a long time, because there is no hard task has not been systematic learning, recently due to work need to use MySQL under Linux. I thought I had the experience of using SQL Server under Windows, and I thought it would be a breeze to install MySQL under Linux, which made a lot of detours when I actually installed and used MySQL, and met many problems, after all, Linux and windows were very different. In order to let the same as I beginners in the process of learning less detours, as soon as possible to get started, wrote this article, I hope you have some help. The Linux environment for this article is red Hat 9.0,mysql is 4.0.16.

　　 Second, install MySQL

1. Download the MySQL installation file
The following two files are required to install MySQL:
mysql-server-4.0.16-0.i386.rpm
mysql-client-4.0.16-0.i386.rpm
To: http://www.mysql.com/downloads/mysql-4.0.html, open this page, drop-down page find "Linux x86 RPM Downloads" entry, find "Server" and "Client programs "Item, download the required two RPM files.

2. Install MySQL
RPM file is a software installation package developed by Red Hat, which allows Linux to be exempt from many complex procedures when installing packages. The parameters commonly used for this command during installation are CIVH, where I means that the specified RMP package will be installed and V represents the details of the installation, and h means that the "#" symbol appears during installation to show 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:
[Email protected] local]# RPM-IVH mysql-server-4.0.16-0.i386.rpm
The following information is displayed.
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 '
。。。。。。 (Omit display)
Starting mysqld daemon with databases From/var/lib/mysql
If the above information appears, the server installation is complete. Test success can run netstat see if the MySQL port is open, such as open indicates that the service has been started and the installation is successful. The default port for MySQL is 3306.
[Email protected] 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:* LISTEN
The above shows you can see that the MySQL service has been started.
2) Install the client
Run the following command:
[Email protected] 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%]
The display is complete and installed.
Connect MySQL with the following command to see if the test was successful.

　　 third, login MySQL

The command to log in to MySQL is MySQL, and MySQL uses the following syntax:
MySQL [-u username] [-h host] [-p[password]] [dbname]
Username and password are mysql user name and password, MySQL's initial management account is root, no password, note: This root user is not a Linux system user. MySQL Default user is root, because the initial no password, the first time to enter the only need to type MySQL.
[[email protected] 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 after adding the password is as follows:
Mysql-u root-p
Enter Password: (enter password)
Where-u followed by the user name,-p requires a password, enter the password at the input password.

Note: This MySQL file is in the/usr/bin directory, and the boot file/etc/init.d/mysql is not a file.

　　 Iv. Several important directories of MySQL

MySQL installation is not as if SQL Server is installed by default in a directory, its database files, configuration files and command files in different directories, it is important to understand these directories, especially for the Linux beginners, because the Linux itself directory structure is more complex, If you don't know the MySQL installation directory then you can't talk about deep learning.

Here are a few of the directories to look at.

1. Database Directory
/var/lib/mysql/

2. Configuration files
/usr/share/mysql (mysql.server command and configuration file)

3. Related commands
/usr/bin (Mysqladmin mysqldump and other commands)

4. Startup script
/etc/rc.d/init.d/(startup script file for MySQL directory)

Five, change the login password

MySQL does not have a password by default, the importance of increasing the password 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 root 123456.
Type the following command:
[[email protected]local]#/usr/bin/mysqladmin-u root Password 123456
Note: Because 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
[[email protected]local]# MySQL
ERROR 1045:access denied for user: '[email protected]' (Using password:no)
An error is displayed stating that the password has been modified.
2) Log in with the modified password
[[email protected]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 done by changing the password with the Mysqladmin command, or 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 and run the following command when it needs to start.
[[email protected]init.d]#/etc/init.d/mysql Start

2. Stop
/usr/bin/mysqladmin-u root-p shutdown

3. Auto-start
1) See if MySQL is in the auto-start list
[[email protected]local]#/sbin/chkconfig Clist
2) Add MySQL to your system's start-up service group
[[email protected]local]#/sbin/chkconfig C Add MySQL
3) Remove MySQL from the Startup service group.
[[email protected]local]#/sbin/chkconfig C del MySQL

　　 Seven, change the MySQL directory

The default data file storage directory for MySQL is/var/lib/mysql. The following steps are required if you want to move the directory to/home/data:

1. Set up the data directory in the home directory
Cd/home
mkdir data

2. Stop the MySQL service process:
Mysqladmin-u root-p shutdown

3. Move/var/lib/mysql Entire directory to/home/data
mv/var/lib/mysql/home/data/
This will move the MySQL data file to/home/data/mysql.

4. Locate the MY.CNF configuration file
If there is no MY.CNF configuration file under the/etc/directory, locate the *.cnf file under/usr/share/mysql/, and copy one of them to/etc/and rename it to MY.CNF). The command is as follows:
[Email protected] 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 correctly, you need to indicate where the Mysql.sock file is generated. Modify the value in the Socket=/var/lib/mysql/mysql.sock line to the right of the equals sign:/home/mysql/mysql.sock. The operation is as follows:
VI my.cnf (Edit the My.cnf file with VI tool, find the following data to modify)
# The MySQL server
[Mysqld]
Port = 3306
#socket =/var/lib/mysql/mysql.sock (original content, in order to be more secure with "#" Comment this line)
Socket =/home/data/mysql/mysql.sock (plus this line)

6. Modify MySQL startup script/etc/rc.d/init.d/mysql
Finally, the MySQL startup script needs to be modified/etc/rc.d/init.d/mysql, the path to the right of the equal sign in the Datadir=/var/lib/mysql line is changed to your current actual storage path: Home/data/mysql.
[Email protected] 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/init.d/mysql start
or restart Linux with the reboot command
If the work is moving properly, otherwise check the previous 7 steps.

　　 Viii. Common operations for 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 a database of two databases: MySQL and test. MySQL library is very important, it has the MySQL system information, we change the password and new users, in fact, the library with the relevant tables to operate.

2. Display the tables in the database
mysql> use MySQL; (Open 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;
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 name;
For example: Create a library with a name-bit AAA
mysql> Create databases AAA;
6, build the table:
Use library name;
CREATE TABLE table name (field settings list);
For example: Create a table in the AAA library that you just created name, the table has ID (ordinal, autogrow), XM (name), XB (gender), CSNY (birth date) four fields
Use AAA;
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 table structure you just created.
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. Add record
For example: Add a few related records.
mysql> INSERT into name values (' ', ' Zhang San ', ' Male ', ' 1971-10-01 ');
mysql> INSERT into name values (' ', ' white Clouds ', ' female ', ' 1972-05-20 ');
You can use the Select command to verify the results.
Mysql> select * from name;
+----+------+------+------------+
ID XM XB CSNY
+----+------+------+------------+
1 Three men 1971-10-01
2 Baiyun woman 1972-05-20
+----+------+------+------------+

8. Change of record
For example: Change Zhang San's birth date to 1971-01-10
Mysql> Update name set csny= ' 1971-01-10 ' where xm= ' Zhang San ';

9. Delete records
For example: Delete the Zhang San record.
mysql> Delete from name where xm= ' Zhang San ';

10. Deleting the library and deleting the table
drop database name;
drop table name;

　　 ix. adding MySQL users

Format: Grant Select on database. * To User name @ login host identified by "password"
Example 1, add a user user_1 password of 123, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:

Mysql> Grant Select,insert,update,delete on * * to [e-mail protected] "%" identified by "123";
Example 1 added user is very dangerous, if you know the user_1 password, then he can be on any computer on the Internet to log on to your MySQL database and your data to do whatever you like, the solution is shown in Example 2.

Example 2, the addition of a user user_2 password of 123, so that the user can only log on localhost, and the database AAA can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use the password that knows user_2, and he cannot 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 [e-mail protected] identified by "123";

With the new user if you can't log in MySQL, log in with the following command:

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 example above to file Back_aaa

[[email protected] root]# Cd/home/data/mysql (go to the library directory, this example library has been transferred from Val/lib/mysql to/home/data/mysql, see part VII above)
[Email protected] mysql]# mysqldump-u root-p--opt aaa > BACK_AAA

2. Recovery

[Email protected] mysql]# mysql-u root-p CCC < BACK_AAA

Reprinted from http://www.laohan213.net/

Installing and using MySQL under Linux