Installation and basic operation of MySQL-5.7 based on CentOS7

Introduction of installation and basic operation of MySQL-5.7 based on CentOS7

A database is a warehouse that organizes, stores, and manages data according to its structure, which is generated more than more than 60 years ago, with the development of information technology and markets, especially after the 1990s, data management is no longer just a way to store and manage data, but to transform it into the kind of data management that users need. There are many types of databases, ranging from the simplest tables with various data to large database systems that can store massive amounts of data, are widely used in all aspects.

Defined

The database, in a nutshell, is the electronic file cabinet-the place where the electronic files are stored, and the user can add, intercept, update, delete the data in the file.

A database is a collection of data that is stored in a certain way, shared for multiple users, has the smallest possible redundancy, and is separate from the application.

Characteristics

1. Structured storage of large amounts of data information to facilitate effective user search and access
2. Effectively maintain the consistency and integrity of data information, reduce data redundancy
3. Meet the sharing and security requirements of your app.

Installation of experimental lab environment

CentOS7 Virtual Machine One set

[MySQL source code compilation package] Mysql-5.7.17.tar.gz

C + + Runtime library Boost_1_59_0

# # #实验配置
Install the relevant environment package first

[email protected] ~]# Yum install-y ncurses ncurses-devel bison cmake gcc gcc-c++

Create an administrative user for MySQL

[[email protected] ~]# useradd-s/sbin/nologin MySQL

Unzip the appropriate package (MySQL-5.7.17)

[Email Protected]gon mysql]# tar zxvf mysql-5.7.17.tar.gz-c/opt

Unzip the C + + runtime into the/usr/local/directory

[Email protected] mysql]# tar zxvf boost_1_59_0.tar.gz-c/usr/local/

Switch directories to the/usr/local/directory and rename the boost file

[Email protected] local]# MV Boost_1_59_0/boost

Then switch to the MySQL installation directory to compile the configuration

[[email protected] mysql-5.7.17]# cmake > -DCMAKE_INSTALL_PREFIX=/usr/local/mysql > -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock > -DSYSCONFDIR=/etc > -DSYSTEMD_PID_DIR=/usr/local/mysql > -DDEFAULT_CHARSET=utf8  > -DDEFAULT_COLLATION=utf8_general_ci > -DWITH_INNOBASE_STORAGE_ENGINE=1 > -DWITH_ARCHIVE_STORAGE_ENGINE=1 > -DWITH_BLACKHOLE_STORAGE_ENGINE=1 > -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 > -DMYSQL_DATADIR=/usr/local/mysql/data > -DWITH_BOOST=/usr/local/boost > -DWITH_SYSTEMD=1

After the configuration is done, binary conversion and compilation are installed.

[[email protected] mysql-5.7.17]# make && make install

Modify the owner and owner Group of the MySQL working folder

[[email protected] mysql-5.7.17]# chown -R mysql:mysql /usr/local/mysql/

Modify the main configuration file for MySQL

[[email protected] mysql-5.7.17]# vim /etc/my.cnf//在行首插入[client]port = 3306default-character-set=utf8socket = /usr/local/mysql/mysql.sock[mysql]port = 3306default-character-set=utf8socket = /usr/local/mysql/mysql.sock//删除原来的mysqld的配置参数，重新插入[mysqld]user = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = /usr/local/mysql/mysqld.pidsocket = /usr/local/mysql/mysql.sockserver-id = 1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

After modifying the master configuration file above, modify the owner and owner group of the configuration file

[Email protected] mysql-5.7.17]# chown mysql:mysql/etc/my.cnf

Adding environment variables

[Email protected] mysql-5.7.17]# echo ' path=/usr/local/mysql/bin:/usr/local/mysql/lib: $PATH ' >>/etc/profile

[[email protected] mysql-5.7.17]# echo ' export PATH ' >>/etc/profile

[Email protected] mysql-5.7.17]# Source/etc/profile

Initializing the database

[[email protected] mysql-5.7.17]# cd /usr/local/mysql/[[email protected] mysql]# [[email protected] mysql]# bin/mysqld > --initialize-insecure \     > --user=mysql > --basedir=/usr/local/mysql > --datadir=/usr/local/mysql/data//    **注：初始化这里可能会遇到一个报错信息：error：too many arguments (first extra is ‘‘);error:Aborting**解决方法:切换目录/usr/local/mysql/,ls查看当前目录下有哪些文件，应该会找到一个data（数据）的文件夹，但是文件夹可能为空，所以这里我们直接rm -rf data；删除data文件夹即可，然后继续初始化数据库

Finally, the Mysqld management script is copied to the system directory to enable the system to recognize

[[email protected] mysql]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/[[email protected] mysql]# systemctl daemon-reload    //刷新识别[[email protected] mysql]# systemctl start mysqld     //启动MySQL服务[[email protected] mysql]# netstat -anpt | grep 3306     

Finally, you can set the password for the login database by turning off the firewall and the enhanced security feature.

[[email protected] mysql]# systemctl stop firewalld.service [[email protected] mysql]# setenforce 0

At this point you can log in the MySQL database, but there is a problem, the initialization of the database by default generated a password, but this password is empty, so we also have to set the password

[[email protected] mysql]# mysqladmin -u root -p password "abc123"然后会提示输入密码：因为初始化时生成的密码是空的，所以直接回车，然后密码就设置成功了此时在重新登入一次，使用新密码abc123就可以登入MySQL命令行模式了。

Then we do the basic operation of the database

[[email protected] ~]# mysql -u root -pEnter password:        //输入密码，我们设置的密码是abc123mysql> show databases;       //查看数据库信息+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                |+--------------------+5 rows in set (0.01 sec)

mysql> create database school;     //添加一个school的数据库Query OK, 1 row affected (0.00 sec)

mysql> use school;     //进入school的数据库Database changed

mysql> create table info (id int,name char(10),score decimal(5,2));     //创建一个表Query OK, 0 rows affected (0.11 sec)

mysql> desc info;      //查看表结构+-------+--------------+------+-----+---------+-------+| Field | Type         | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id    | int(11)      | YES  |     | NULL    |       || name  | char(10)     | YES  |     | NULL    |       || score | decimal(5,2) | YES  |     | NULL    |       |+-------+--------------+------+-----+---------+-------+3 rows in set (0.00 sec)

mysql> select * from info;    //查看表内数据Empty set (0.00 sec)      //这里还未插入任何数据，所以为空

mysql> insert into info (id,name,score) values (1,‘zhangsan‘,75.5);Query OK, 1 row affected (0.00 sec)mysql> insert into info (id,name,score) values (2,‘lisi‘,77.5);Query OK, 1 row affected (0.00 sec)mysql> insert into info (id,name,score) values (3,‘wangwu‘,80);Query OK, 1 row affected (0.00 sec)         //连续插入三个数据

mysql> select * from info;     //此时在查看表内数据，发现就有数据存入在表内+------+----------+-------+| id   | name     | score |+------+----------+-------+|    1 | zhangsan | 75.50 ||    2 | lisi     | 77.50 ||    3 | wangwu   | 80.00 |+------+----------+-------+3 rows in set (0.00 sec)

mysql> update info set name=‘zhaoliu‘ where id=3;    //修改id=3的数据的name为zhaoliuQuery OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from info;     //再次查看表信息+------+----------+-------+| id   | name     | score |+------+----------+-------+|    1 | zhangsan | 75.50 ||    2 | lisi     | 77.50 ||    3 | zhaoliu  | 80.00 |       //原先的wangwu就被修改为zhaoliu+------+----------+-------+3 rows in set (0.00 sec)

mysql> delete from info where id=2;    //删除id=2的表内数据Query OK, 1 row affected (0.00 sec)mysql> select * from info;+------+----------+-------+| id   | name     | score |+------+----------+-------+|    1 | zhangsan | 75.50 ||    3 | zhaoliu  | 80.00 |     //这里原先的id=2的数据lisi就被删除了+------+----------+-------+2 rows in set (0.00 sec)

mysql> drop table info;      //删除info表Query OK, 0 rows affected (0.01 sec)mysql> select * from info;     //此时在查看表信息，就会报错，该表不存在，因为已经被我们删除了ERROR 1146 (42S02): Table ‘school.info‘ doesn‘t exist

mysql> show databases;     //先查看有哪些数据库+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                |+--------------------+5 rows in set (0.00 sec)mysql> drop database school;     //删除school的数据库Query OK, 0 rows affected (0.00 sec)mysql> show databases;        //再次查看时，就可以看到school的数据库已经被删除了+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)

More MySQL content to look forward to, MySQL index and transaction, MySQL database management .....

Installation and basic operation of MySQL-5.7 based on CentOS7