MySQL installation and basic commands

前面我们介绍了apache httpd server ，而在企业网站平台中，为了提供更丰富.强大的web应用功能，还需要后台数据库和网页编程语言等多种角色的支持，这里我们来安装一下MySQL数据库。 MySQL是一个真正的多线程，多用户的关系型数据库服务，凭借其高性能、高可靠、和易于使用的特性，成为数据库中的佼佼者，为了确保数据库的完整性、可定制性，我们采用MySQL 5.x版本的源码安装方式。

One, the MySQL compilation installs
1. Prevent port conflicts and see if there is a RPM-mode installation of MySQL

Installing Ncurses-devel allows MySQL to be used in character terminals

MySQL5.5 need to install CMake, need to wait patiently

2. Create a running user so that it cannot be logged on locally, you can not create a host directory

3. Unpacking, configuring, compiling, installing MySQL

-dcmake_install_prefix: Specify MySQL installation directory
-dsysconfdir: Specifying initialization parameter file directory
-ddefault_charset: Specifies that the default character set is used such as: UTF8
-ddefault_collation: Specifies a character set collation rule that is used by default utf_general_ci is a universal rule for the UTF-8 character set
-dwith_extra_charsets: Specify additional support for other character set encodings

4. Post-Installation adjustments
Set permissions on the directory

Setting up a configuration file

Initializing the database

Set the environment variable to take effect immediately

Add As System service

Start the MySQL service and see if it starts with a port of 3306 (can be changed in/ETC/MYS.CNF)




Ii. access to MySQL and basic commands
MySQL is a typical C/s architecture application that requires client software, but the simplest and most user-friendly client in Linux is its own MySQL command tool.
1. Connect to the database and log in as the root user
Not connected to the database, prompting no MySQL command, to establish a soft link to the system bin, to resolve

Login with Password Plus option-P mysql-u user name-P
After the validation succeeds, after each SQL statement is displayed mysql> the ";" End, the MySQL command is case-insensitive and exit exits the MySQL environment.
2. Using MySQL
Show master logs; View log file information for the current database service.

Query what libraries are in the current database

See which tables are in the current library

View table Structure

Create a new library
Create DATABASE CTO;

Create a table
CREATE TABLE table name (field 1 name type, field 2 name Type, ..., primary key (primary key name))
Create TABLE users contains user_name (not empty), USER_PASSWD (default null), two columns, primary key user_name

Delete a table

Delete a library

Inserting data records
Insert into table name (Field 1, Field 2 ...). Values (the Value of field 1, the value of field 2 ...)

If all the field values in the table are included when inserting new data, the specified field can be omitted,

Querying data records
Select field 1, Field 2,..... from table name where conditional expression
* represents all


modifying data records
Update table name SET field Name 1 = field value 1[, field name 2 = field value 2] Where condition expression


The various user information in the database is stored in Mysql.user and can be set to a password for the user

You can also use the Linux tools mysqladmin settings

Delete data records
Delete from table name where conditional expression


III. Maintenance of the database
The maintenance work of MySQL database mainly includes the setting of user's rights, backup and recovery of database.
1. User authorization of the database
The root user in MySQL has full permissions on all library tables, and frequent use poses a risk, so set up some low-privileged users
The GRANT statement is specifically designed to set the access rights of a database user, and creates a new user when the user does not exist, otherwise it will be used to modify the user information.
Grant permission list on Library name, table name to User name @ source address [identified by ' Password ']
Permissions List: Multiple permissions with "," split, such as: Select,insert,update. Use all to represent all permissions
List of library names: wildcard characters can be used ""For example, using the CTO., which means that the authorization object is all tables in the AUTH library br/> user name @ Source Address: Who can connect on that connection, the source address can be a domain name, IP, and also use "%" to represent all addresses within a region or network segment, such as%.cto.com,192.168.1.%
Set the permissions for Xiaoming to query all tables in the CTO library


Typically, the database in the enterprise is independent of the server, and it is common practice to create one or several Web site-specific libraries, grant permissions, and restrict IP addresses

2. View Permissions
Show grants for user name @ Source Address

3 Revoke Permissions
Revoked users can still connect to MySQL, but disable the corresponding database operation
Revoke permissions list on database name. Table name from user name @ Source Address

4. Database Backup and Recovery
The backup database can be directly packaged with the database folder/usr/local/mysql/data, or directly using the Mysqldump tool
Enter directly on the Linux command line
Back up a table
mysqldump [options] Library Name table name 1 "table Name 2" ... >/backup path/backup file name

Back up a library or multiple libraries
mysqldump [options]--databases Library name 1 "table Name 2" ... >/backup path/backup file name

Back Up all libraries
mysqldump [Options]--all-databases >/backup path/backup file name

Warning because mysqldump default is not to back up the event table, only add--events, plus--events--ignore-table=mysql.events parameters can be created, you can create new or overwrite the backup

Option:-u Specify user ID-p requires a password when the amount of data is large, you can add--opt to increase execution speed
View backup files where/..../, with--start with a comment, you can filter

Restore the database using the MySQL import command
mysql [options] [library name] [table name] </backup path/backup file name



Recovery success
These are some basic operations, a simple summary, the next is the lamp platform to build.

MySQL installation and basic commands