Shulou(Shulou.com)06/01 Report--

Earlier we introduced apache httpd server, and in the enterprise website platform, in order to provide richer. Powerful web application functions, but also need the background database and web programming language and other roles to support, here we will install the MySQL database. MySQL is a real multi-threaded, multi-user relational database service. With its high performance, high reliability, and easy to use, it has become a leader in the database. In order to ensure the integrity and customization of the database, we use the source code installation mode of MySQL 5.x version.

I. compilation and installation of MySQL

1. To prevent port conflicts, check whether there is a MySQL installed in rpm mode.

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

MySQL5.5 needs to install cmake and needs to wait patiently.

two。 Create a running user so that it cannot log in locally and do not have to create a host directory

3. Unpack, configure, compile, install mysql

-DCMAKE_INSTALL_PREFIX: specify the MySQL installation directory

-DSYSCONFDIR: specify the initialization parameter file directory

-DDEFAULT_CHARSET: specifies that the character set such as utf8 is used by default

-DDEFAULT_COLLATION: specifies the default character set proofing rules utf_general_ci is a general rule for the UTF-8 character set

-DWITH_EXTRA_CHARSETS: specifies additional supported character set encodings

4. Adjustment after installation

Set permissions on the directory

Establish a configuration file

Initialize the database

Set the environment variable to make it take effect immediately

Add as a system service

Start the MySQL service and check to see if it starts, port 3306 (can be changed in / etc/mys.cnf)

Access to MySQL and basic commands

MySQL is a typical application of MySQL architecture, which requires client software, but the simplest and easy-to-use client in linux is its own command tool.

1. Connect to the database and log in as a root user

Can not connect to the database, prompt no MySQL command, establish a soft link to the system under the bin, solve the problem

Login with password plus option-p mysql-u user name-p

After successful verification, MySQL > each sql statement is displayed, ending with ";", the MySQL command is not case sensitive, and exit exits the MySQL environment.

two。 Use MySQL

Show master logs; views the log file information for the current database service.

Query what libraries are available in the current database

Check which tables are in the current library

View table structure

Create a new library

Create database cto

Create a tabl

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 empty), two columns, primary key is user_name

Delete a table

Delete a library

Insert data record

Insert into table name (field 1, field 2.) values (the value of field 1, the value of field 2.)

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

Query data record

Select field 1, field 2. From table name where conditional expression

* represents all

Modify data record

Update table name set field name 1 = field value 1 [, field name 2 = field value 2] where conditional expression

All kinds of user information in the database is stored in mysql.user, and the password can be set for the user.

You can also use the linux tool mysqladmin to set

Delete data record

Delete from table name where conditional expression

Third, maintain the database

The maintenance of MySQL database mainly includes the setting of user rights, backup and recovery of the database.

1. User authorization of the database

Root users in MySQL have full permissions on all database tables, and frequent use will bring risks, so set up some low-privilege users

The grant statement is specifically used to set access to the database user, and when the user does not exist, a new user is created, 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']

Permission list: multiple permissions are divided by ",", such as: select,insert,update. Use all to represent all permissions

List of library names: you can use the wildcard character "" for example, using cto. Indicates that the authorization object is all tables in the auth library

User name @ source address: who can connect there. The source address can be a domain name, IP, or "%", which represents all addresses in an area or network segment, such as% .cto.com, 192.168.1.%identified by: set password If omitted, the password is empty. Give Xiaoming the permission to query all tables in the cto library! [] (https://s1.51cto.com/images/blog/201801/12/31b7f711dffdcb5fbb76c9a68ee64d16.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=) usually the database and server are independent of each other in the enterprise. The common practice is to set up one or more web site-specific libraries, grant permissions, and restrict IP addresses! [] (https://s1.51cto.com/images/blog/201801/12/bc5be598d8c9f824f0dcb2b24a772000.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)2. View permissions show grants for username @ source address! [] (https://s1.51cto.com/images/blog/201801/12/24c51d2aa13a1767194c3ec7077a7d32.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)3 revoke permissions revoked users can still connect to mysql, but it is forbidden to operate revoke permission list on database name on the corresponding database. Table name from user name @ source address! [] (https://s1.51cto.com/images/blog/201801/12/f1ef876b60bdda89f8866c38b9828e00.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)4. Backup and recovery of database backup database can be directly packaged database folder / usr/local/mysql/data, or directly use the mysqldump tool to enter a backup table mysqldump [option] library name [table name 1] [table name 2] in the linux command line. > / backup path / backup file name! [] (https://s1.51cto.com/images/blog/201801/13/d25728ca0d0bfe5a2549c75366dd657a.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=) backs up one or more libraries mysqldump [options]-- databases library name 1 [table name 2]. > / backup path / backup file name! [] (https://s1.51cto.com/images/blog/201801/13/122defc52f09bbfdeaff6377cb46bc47.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90, Type_ZmFuZ3poZW5naGVpdGk=) backup all libraries mysqldump [option]-- all-databases > / backup path / backup file name! [] (the reason for https://s1.51cto.com/images/blog/201801/13/4a926a9a092318a9d987e84195421831.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)warning is that mysqldump does not back up event tables by default Only if you add-- events Just add-- events-- ignore-table=mysql.events parameter. You can create a new one or overwrite a backup! [] (https://s1.51cto.com/images/blog/201801/13/5870820fb9802f644bd1dd6d0d96987b.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

Option:-u specifies user identity-p requires a password to be added when the amount of data is large-- opt to improve execution speed

Check the backup file in which /. /, starting with-- is a comment, which can be filtered.

Restore a database using the mysql import command

Mysql [option] [Library name] [Table name] < / backup path / backup File name

Recovery succeeded

These are some basic operations, to sum up briefly, the next article is the construction of the lamp platform.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.