MySQL installation and use 1. what is MySQLMySQL (pronounced MyEssQueEll) is an SQL database Server developed by Tcx (www. tcx. se) with multiple users and multiple execution threads. MySQL aims to be fast, stable, and easy to use. MySQL can be obtained at www.mysql.net. II. install MySQL
Mysql/> install and use MySQL
1. what is MySQL?
MySQL (pronounced "My Ess Que Ell") is a multi-user, multi-thread SQL database Server developed by Tcx (http://www.tcx.se. MySQL aims to be fast, stable, and easy to use.
MySQL can be obtained in this http://www.mysql.net.
II. MySQL installation
The MySQL version used in this article is mysql-3.22.27.tar.gz (original code file), the job environment is RedHat6.0 + CLE0.8.
MySQL is installed in the/usr/local directory by default. However, we recommend that you install mysql independently in the/usr/local/mysql directory for later convenience. Follow these steps to install MySQL:
After obtaining the mysql-3.22.27.tar.gz, undo it in the/usr/local directory:
# Cd/usr/local
# Tar zxvf mysql-3.22.27.tar.gz
# Cd mysql-3.22.27
Set configure installation options, select the installation directory (prefix), and support Chinese Big5 code (with-charset = big5 ):
#./Configure -- prefix =/usr/local/mysql # -- with-charset = big5
Start compilation and installation:
# Make
# Make install
# Scripts/mysql_install_db
The last step is to generate the MySQL grant tables (a mysql database and some tables will be created to manage the authorization information for using MySQL, that is, what permissions the user has to use the database ).
III. start and stop MySQL
How to Start MySQL: (This document uses the installation of MySQL in/usr/local/mysql as an example)
#/Usr/local/mysql/share/mysql. server start
Note that before the first execution, mysql. server to be executable (chmod 744 mysql. server). In addition, you can add this line of commands to/etc/rc. d/rc. in the local file, MySQL is automatically started at startup.
To stop MySQL:
#/Usr/local/mysql/bin/mysqladmin shutdown
If you set a password for the MySQL Administrator root account (not the root of the job system), you must do the following to stop MySQL, mySQL will ask you the root password before shutdown is executed:
#/Usr/local/mysql/bin/mysqladmin-u root-p shutdown
IV. management and use of MySQL
Before you start
MySQL provides many Tools (Client Tools) to connect to the MySQL database Server. The most important tool is the mysql conversation tool and the mysqladmin utility. In most cases, users use mysql to communicate with the database Server. The following describes how to maintain and use mysql using the MySQL connection tool. (Take the installation in this article as an example. the mysql tool is stored in/usr/local/mysql/bin/mysql ).
Mysql syntax:
Mysql [-u username] [-h host] [-p [password] [dbname]
The MySQL database has its own user account and permission control method. Therefore, the username and password specified here are MySQL Users and passwords, instead of the operating system user and password (of course, any user can execute mysql, and then use any account of MySQL ).
When you install MySQL for the first time, the MySQL management account is root, and no password is set (non-job system root ). Therefore, set the password for the root user as follows before starting:
Connect mysql to the MySQL database Server:
#/Usr/local/mysql/bin/mysql-u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 201 to server version: 3.22.27
Type 'HELP' for help.
Mysql>
In the mysql-u root mysql command, specify the root account and enable the mysql system database. after connecting to MySQL, you will see some prompts and symbols of the mysql tool, most of the subsequent work is completed under this prompt.
Change the root password of the MySQL system administrator:
Mysql> update user set password = password ('New password') where user = 'root ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Mysql> quit
Bye
Note that a semicolon ";" must be added after each command to start mysql execution. The second command updates the mysql database that has been loaded into the memory and leaves the mysql tool program at last.
After the root password is updated, the method for connecting to MySQL is as follows:
New mysql-u root-p password
Or, ask mysql for the root password:
Mysql-u root-p
Database maintenance
Next, we will use a simple address book database as an example to introduce how to use mysql tool programs for database maintenance (addition, authorization, and table maintenance ).
First, connect to the MySQL root account and create an addbook database:
#/Usr/local/mysql/bin/mysql-u root-p
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 207 to server version: 3.22.27
Type 'HELP' for help.
Mysql> create databae addbook;
Query OK, 1 row affected (0.00 sec)
Specify to use the addbook database and create a friends table:
Mysql> use addbook;
Database changed
Mysql> create table friends (
-> Name Char (15 ),
-> Telphone VarChar (20 ),
-> Icq Char (10 ),
-> Address VarChar (30)
-> );
Query OK, 0 rows affected (0.00 sec)
Add a few pieces of information and check it out:
Mysql> insert into friends values (
-> "Paa", "29016710", "46243046", "Taibei new district"
-> );
Query OK, 1 row affected (0.00 sec)
Mysql> insert into friends (name, icq, telphone, address) Values (
-> "Cxlin", "39425893", "7654321", "Taipei County"
-> );
Query OK, 1 row affected (0.01 sec)
Mysql> select * from friends;
+ ------- + ---------- + -------------- +
| Name | telphone | icq | address |
+ ------- + ---------- + -------------- +
| PAA | 29016710 | 46243046 | Xincheng District, Taibei County |
| Cxlin | 7654321 | 39425893 | Taibei County |
+ ------- + ---------- + -------------- +
2 rows in set (0.00 sec)
The second insert command specifies the insert sequence of the data column, which is more flexible than the first one, and the first command must insert data in the order when the data table is created.
Update and delete data table records:
Mysql> update friends set address = "Taoyuan County" where name = "cxlin ";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Mysql> select * from friends where name = "cxlin ";
+ ------- + ---------- + --------- +
| Name | telphone | icq | address |
+ ------- + ---------- + --------- +
| Cxlin | 7654321 | 39425893 | Taoyuan County |
+ ------- + ---------- + --------- +
1 row in set (0.00 sec)
Mysql> delete from friends where name = "Paa ";
Query OK, 1 row affected (0.01 sec)
Mysql> select * from friends;
+ ------- + ---------- + --------- +
| Name | telphone | icq | address |
+ ------- + ---------- + --------- +
| Cxlin | 7654321 | 39425893 | Taoyuan County |
+ ------- + ---------- + --------- +
1 row in set (0.00 sec)
Finally, after the database and data table are created, grant the permission (select, insert, update, and delete) of all data tables in the addbook database to locally @ localhost (remind again, in this example, the account is the user account of MySQL, rather than the account of the job system ):
Mysql> grant select, insert, update, delete
-> On addbook .*
-> To PAA @ localhost identified by '20140901 ';
Query OK, 0 rows affected (0.00 sec)
Then, you can access the addbook database in MySQL as the identity of the locally available account:
#/Usr/local/mysql/bin/mysql-u maa-p addbook
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 211 to server version: 3.22.27
Type 'HELP' for help.
Mysql> status
--------------
./Mysql Ver 9.36 Distrib 3.22.27, for pc-linux-gnu (i686)
Connection id: 26
Current database: addbook
Current user: PAA @ localhost
Server version 3.22.27
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket/tmp/mysql. sock
Uptime: 2 hours 29 min 33 sec
Threads: 11 Questions: 107 Slow queries: 0 Opens: 11 Flush tables: 1
Open 7
--------------
