Configuration and installation of MySQL

1. Database

What is a database?

A database is a warehouse that organizes, stores, and manages data according to its structure.

Each database has one or more different APIs that are used to create, access, manage, search, and assign values to the saved data.

We can also store the data in a file, but it is relatively slow to read the data in the file.

So we use relational databases to store and manage large volumes of data.

Characteristics:

1. Data appears in tabular form

2. Each action bit various record names

3. Data fields corresponding to each yours faithfully record name

4. Many rows and columns form a single table

5. Several tables comprise the database

MySQL Database

MySQL is the most popular relational database management system, Web-based MySQL is the best RDBMS: (Relational data management system) One of the application software. Developed by the Swedish MySQL AB company, currently owned by Oracle Corporation. MySQL is an associated database management system, and the associated database stores the data in different tables rather than putting all the data in a large warehouse, which adds flexibility.

MySQL uses the standard SQL data language form.

MySQL can be allowed on multiple systems and supports multiple languages. These programming languages include C, C + +, Python, Java, Perl, PHP, Eiffel, Ruby, and Tcl.

MySQL has good support for PHP, which is currently the most popular web development language.

MySQL support large database, support 50 million records of Data Warehouse, 32-bit system table file maximum support 4gb64 bit system to support the largest table file is 8TB.

MySQL can be customized, using the BPL protocol, you can modify the source code to develop their own MySQL system

2.MYSQL Database Installation and use

Install MySQL on Linux/unix

The installation steps are as follows:

Log in to your Linux system using the root user.

Download the mysqlrpm package for: Baidu

can also be installed by command, RPM package for you to download the RPM package:

1 [[email protected]]# r
-i MySQL-5.0. 9 - 0. i386.rpm

The MySQL user is created during the installation of the MySQL server above, and a MySQL profile my.cnf is created.

All MySQL-related binaries can also be found directly by modifying/usr/bin and/usr/sbin. All data tables and databases will be created in/Var/lib/mysql.

Here are some MySQL optional installation procedures that you can install according to your needs

1 [[email protected]]# rpm-I MySQL-Client-5.0.9-0. i386.rpm2 [[email protected]]# rpm-I MySQL-Devel-5.0.9-0. i386.rpm3 [[email protected]]# rpm-I MySQL-Shared-5.0.9-0. i386.rpm4 [[email protected]]# rpm-I MySQL-Bench-5.0.9-0. i386.rpm

Install MySQL on Windows

Installing MySQL on Windows is simple, just download the zip file and unzip the installation package.

Double-click the Setup.exe, install it according to the prompts, and install it in the C:\mysql directory by default.

You can then open the command line to switch to the c:\mysql\bin\ directory and enter the command:

-- Console

Successful installation will output MySQL boot and innodb information.

Using Mysqlclient to execute a simple SQL command

You can use the MySQL command on the MySQL client to connect to the MySQL server, the default password is empty, so the first time you do not need to enter a password.

Start MySQL on Linux system startup

If you need to start the MySQL server when the Linux system starts, you need to add a command to the/etc/rc.local file:

/etc/init.d/mysqld start

Again, you need to add the mysqld binaries to the/etc/init.d/directory.

3.mysql Management

Starting and shutting down the MySQL server

First, we need to check whether the MySQL server is started with a command:

- | grep mysqld

If MySQL is already started, the above command will output the MySQL process table, and if MySQL does not start, you can use the following command to start the MySQL server:

Root@host/usr/bin. / &

If you want to close the currently running MySQL server, you can execute the following command:

Root@host/usr/bin. / - - shutdown ******

MySQL user settings

If you need to add a MySQL user, you only need to add new users to the user table in the MySQL database.

The following is an example of adding a command, a user named Guest, a password of guest123, and an authorized user to perform Select,insert and update operation permissions:

Root@host# MySQL-U root-penter Password:*******MySQL>  UseMySQL;Databasechanged MySQL> INSERT  into User(Host,User, password, Select_priv, Insert_priv, Update_priv)VALUES('localhost','Guest', PASSWORD ('guest123'),'Y','Y','Y'); Query OK,1Row affected (0.20sec) MySQL>FLUSHPrivileges; Query OK,1Row affected (0.01sec) MySQL> SELECTHostUser, password from User WHERE User = 'Guest';+-----------+---------+------------------+|Host| User    |Password|+-----------+---------+------------------+|localhost|Guest|6f8c114b58f2ce9e|+-----------+---------+------------------+1Rowinch Set(0.00Sec

When adding a user, be aware that you use the password () function provided by MySQL to encrypt the password. You can see that the user password is encrypted after the above instance:

6f8c114b58f2ce9e.

Note: The password of the user table in Mysql5.7 has been replaced with authentication_string.

Note: the FLUSH privileges statement needs to be executed in attention. This command will reload the authorization table when it is executed.

If you do not use this command, you will not be able to use the newly created user to connect to the MySQL server unless you restart the MySQL server.

You can specify permissions for the user when creating the user, in the corresponding permission column, set as ' Y ' in the INSERT statement, and the user Rights list is as follows:

Select_priv,insert_priv,update_priv,delete_priv,create_priv,drop_priv,reload_priv,shutdown_priv,process_priv, File_priv,grant_priv,references_priv,index_priv,alter_priv

Another way to add a user to the GRANT command via SQL is to add the user Zara to the specified database tutorials, with the password zara123.

Root@host# MySQL-U root-p password; Enter Password:*******MySQL>  UseMySQL;Databasechanged MySQL> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP     -  onTutorials.*     -  to 'Zara'@'localhost'     -Identified by 'zara123';

/ETC/MY.CNF file Configuration

In general, you do not need to modify this configuration file, which is configured by default as follows:

[mysqld]DataDir=/var/Lib/Mysqlsocket=/var/Lib/Mysql/Mysql.sock[Mysql.server]User=Mysqlbasedir=/var/Lib[Safe_mysqld]Err-Log=/var/Log/Mysqld.LogPID-file=/var/Run/Mysqld/Mysqld.pid

Commands for managing MySQL

The commands commonly used in the MySQL database process are listed below:

• Use database name : Select the MySQL database you want to manipulate, and all MySQL commands are only for that database after using this command.

• SHOW DATABASES: Lists a list of databases for the MySQL database management system.

• SHOW TABLES: #显示指定数据库的所有表, you need to use the use command to select the database you want to manipulate before using this command.

• SHOW COLUMNS from data table: #显示数据表的属性, property type, primary key information, whether null, default value, and other information.

• Create DATABASE TestDB charset "UTF8"; #创建一个叫testdb的数据库, and let IT support Chinese

• Drop database TestDB; #删除数据库

• Show index from data table: Displays detailed index information for the data table, including primary key (primary key).

Configuration and installation of MySQL