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

I don't know if you have any knowledge about the compilation, installation and basic operation flow of MySQL before. Today, I'm here to tell you a little bit about it. If you are interested, let's take a look at the body. I believe you will gain something after reading the compilation and installation of MySQL and the basic operation process.

I. mysql 5.7installation

-- install the mysql compilation environment--

Yum-y install\

Ncurses\

Ncurses-devel\

Bison\

Cmake

-- install mysql package--

Useradd-s / sbin/nologin mysql

Tar zxvf mysql-5.7.17.tar.gz-C / opt/

Tar zxvf boost_1_59_0.tar.gz-C / usr/local/

Cd / usr/local/

Mv boost_1_59_0 boost

-- mysql compilation and installation--

Cd 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

-Note: if there is an error in the process of CMAKE, when the error is resolved, you need to delete the CMakeCache.txt file in the source directory, and then re-CMAKE, otherwise the error will remain-

Make & & make install

Chown-R mysql.mysql / usr/local/mysql/

-- modify mysql configuration file--

Vi / etc/my.cnf

[client]

Port = 3306

Default-character-set=utf8

Socket = / usr/local/mysql/mysql.sock

[mysql]

Port = 3306

Default-character-set=utf8

Socket = / usr/local/mysql/mysql.sock

[mysqld]

User = mysql

Basedir = / usr/local/mysql

Datadir = / usr/local/mysql/data

Port = 3306

Character_set_server=utf8

Pid-file = / usr/local/mysql/mysqld.pid

Socket = / usr/local/mysql/mysql.sock

Server-id = 1

Sql_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

-- modify environment variables

Chown mysql:mysql / etc/my.cnf

Echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' > > / etc/profile

Echo 'export PATH' > > / etc/profile

Source / etc/profile

-data initialization-

Cd / usr/local/mysql/

Bin/mysqld\

-- initialize-insecure\

-- user=mysql\

-- basedir=/usr/local/mysql\

-- datadir=/usr/local/mysql/data

-- start service--

Cp / usr/local/mysql/usr/lib/systemd/system/mysqld.service / usr/lib/systemd/system/

Systemctl daemon-reload

Systemctl start mysqld

Netstat-anpt | grep 3306

Systemctl enable mysqld

Mysqladmin-u root-p password "abc123" / / set the password for the root account to abc123 prompt for the original password.

Mysql-u root-p

-authorize remote login-

Grant all privileges on *. * to 'root'@'%' identified by' abc123' with grant option

2. Basic operation of mysql database

-- Library--

/ / create a database create database Myschool

/ / View database show databases

/ / Delete database drop database Myschool

/ / enter the database use Myschool

-Table--

/ / create table create table info (id int not null primary key auto_increment,name char (10) not null, score decimal (5) 2), hobby int (2)

/ / Delete table drop table info

/ / View the table structure desc info

/ / View the table show tables in Mycshool

/ / modify the data table name alter table info rename to info7

/ / query select * from info inner join hob where info.hobby=hob.id with multiple tables

Select info.name,ifo.score.hob.hobname from info inner join hob where info.hobby=hob.id

/ / Alias query select i.namedepartment i.score.hobname from info i inner join hob h where i.hobby=h.id

/ / query to generate a new table create table info2 select i.namedepartment i.score.hobname from info i inner join hob h where i.hobby=h.id

-data-

/ / View the data in the info table select * from info

/ / insert data insert into info (id,name,score) values

/ / filter criteria select * from info where id=6

/ / modify update information update info set score=75 where id=6

/ / Delete information delete from info where name='test'

/ / add field alter table info add hobby int (2)

/ / modify column name alter table info rename column score to score2

/ / modify the data type of the field alter table info modify score decimal (4)

/ / sort (ascending / descending) select * from info where 1 / 1 order by score; default ascending order

Select * from info where 1 # 1 order by score asc; ascending order

Select * from info where 1 # 1 order by score desc; descending order

/ / grouping select * from info group by hobby

/ / aggregate function

Statistical count () example: select count (*) from info2

Average avg () example: select avg (score) from info2

III. Mysql Index and transaction

Index creation method: create index index name list of on columns

Example: create index id_index on info (id)

Query index: show index from info; show index from info\ G

Delete index: drop index id_index on info

Create a unique index: create unique index id_index on info (id)

Create primary key index: alter table info add primary key (id)

Add Field: alter table info add column age int

Delete field: alter table info drop column age

Create a full-text index: create table info (descript TEXT,FULLTEXT (descript)); engine=MyISAM

Multi-column index: create index multi_index on info (name,adress)

Transaction: a set of operations are executed together or not at all, and the results are consistent

Begin start set autocommit=0: disable automatic submission

Commit submission

Rollback rollback

Savepoint S1; define the rollback point

Rollback to savepoint S1; rollback to a defined rollback point

There are four characteristics of a transaction:

Atomicity, consistency, isolation, persistence

Virtual tables in the view database

Function: data in one or more tables provide access to different authorized users

Create view score_view as select * from info where score > 80

IV. User management

Create

Create user 'username'@'host' identified by' passwd'

Grant permissions on database. Table to user @ host identified by password

View

Select user,authentication_string,host from user

Delete

Drop user 'lisi'@'localhost'

Rename

Rename user 'zhangsan'@'localhost' to' test'@'192.168.218.130'

The cipher text is converted into ciphertext

Select password ('abc123')

Create user 'username'@'host' identified by password' * 6691484EA6B50DDDE1926A220DA01FA9E575C18A'

Change user password

Set password for 'test'@'192.168.218.130' = password (' abc123')

Forgot password (5.7)

Systemctl stop mysqld

Vim / etc/my.cnf

[mysqld]

Skip-grant-tables

Systemctl start mysqld

Mysql

Update mysql.user set authentication_string = password ('abc123') where user =' root'

Empowering

Grant permissions on database. Table to user @ host identified by password

Revoke the authority

Revoke permissions on database. Table from user @ host

View permissions

Show grants for user @ host

Log management

Vim / etc/my.cnf

[mysqld]

Log-error=/usr/local/mysql/data/mysql_error.log # error log

General_log=ON # General Log

General_log_file=/usr/local/mysql/data/mysql_general.log

Log_bin=mysql-bin # binary log (log all actions)

View binaries

Mysqlbinlog-no-defaults mysql-bin.00001

Slow log

Slow_query_log=ON

Slow_query_log_file=mysql-slow_query.log

Long_query_time=1 # reference time

What do you think of this article after reading the compilation, installation and basic operation flow of MySQL? If you want to know more about it, you can continue to follow our industry information section.

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.