带你了解MYSQL知识点

mysql知识点

MYSQL的依赖包

1、ncurses ncurses-devel:ncurses是字符终端下屏幕控制的基本库
2、bison:用于自动生成语法分析器程序，实际上可用于所有常见的操作系统。Bison把LALR形式的上下文无关文法描述转换为可做语法分析的C或C++程序。
3、cmake:CMake是一个跨平台的安装（编译）工具，可以用简单的语句来描述所有平台的安装(编译过程)。CMake 可以编译源代码、制作程序库、产生适配器(wrapper)、还可以用任意的顺序建构执行档。

*cmake *
1、-DCMAKE INSTALL PREFIX=/usr/local/mysql #安装目录
2、-DMYSQL_UNIX_ ADDR=/usr/local/mysql/mysql.sock #指定套接字文件的存储路径
3、-DSYSCONFDIR=/etc \ #配置my.cnf的目录
4、-DSYSTEMD PID DIR=/usr/local/mysql #主服务进程所在路径
5、-DDEFAULT_CHARSET=utf8 #默认字符集
6、-DDEFAULT_COLLATION=utf8_general ci #默认编码
7、-DWITH_INNOBASE_STORAGE_ENGINE=1#启用InnoDB引擎
8、-DWITH_ARCHIVE STORAGE ENGINE=1\ #启用ARCHIVE引擎
9、-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ #启用BLACKHOLE引擎
10、-DWITH PERFSCHEMA STORAGE ENGINE=1\ #启用perfschema引擎
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
11、-DMYSQL DATADIR=/usr/local/mysql/data #数据存储路径
12、-DWITH BOOST=boost \ #boost库是为C++语言标准库提供扩展的一些C++程序库的总称
13、-DWITH SYSTEMD=1 #-DWITH SYSTEMD=1这是MySQL 5.7原生支持Systemd的选项，如果要是用systemct1启动，就必须开启。

项目

1、
[root@server1 ~]# yum -y install ncurses ncurses-devel bison cmake
2、创建一个不可登录的用户
[root@server1 ~]# useradd -s /sbin/nologin mysql

3、把MySQL的软件包放进root目录里解压缩在进入mysql-5.7.20/
tar zxvf mysql-boost-5.7.20.tar.gz
cd mysql-5.7.20/

4、安装依赖包
[root@server1 mysql-5.7.20]# 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_ENGIE=1 \
  -DMYSQL_DATADIR=/usr/local/mysql/data \
  -DWITH_BOOST=boost \
  -DWITH_SYSTEMD=1


[root@server1 mysql-5.7.20]# make && make install

5、 把/usr/local/mysql/修改数组属组
[root@server1 mysql-5.7.20]#   cd
[root@server1 ~]# chown -R mysql.mysql /usr/local/mysql/


[root@server1 ~]# 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
socket = /usr/local/mysql/mysql.sock
pid-file = /usr/local/mysql/mysql.pid
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

[root@server1 ~]# chown mysql.mysql /etc/my.cnf

[root@server1 ~]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/mysql/lib:$PATH' >> /etc/profile
[root@server1 ~]# echo 'export PATH' >> /etc/profile
[root@server1 ~]# tail -5 /etc/profile

unset i
unset -f pathmunge
PATH=/usr/local/mysql/bin:/usr/local/mysql/mysql/lib:$PATH
export PATH
[root@server1 ~]# source /etc/profile

[root@server1 ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/mysql/mysql/lib:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@server1 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

[root@server1 ~]# cd /usr/local/mysql/
[root@server1 mysql]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system

[root@server1 mysql]# systemctl daemon-reload 
[root@server1 mysql]# systemctl start mysqld
[root@server1 mysql]# cd
[root@server1 ~]# netstat -anpt | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      116781/mysqld       
[root@server1 ~]# 

[root@server1 ~]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

[root@server1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

给mysql设置密码

1、给数据库设置密码
[root@server1 ~]# mysqladmin -uroot -p password '123123'
2、登录数据库
[root@server1 ~]# mysql -uroot -p123123

给mysql修改密码

用SET PASSWORD命令

登录mysql

[root@server1 ~]# mysql -uroot -p123123
mysql> set password for root@localhost = password('abc123');

用mysqladmin修改

[root@server1 ~]# mysqladmin -uroot -pabc123 password '123123'

用UPDATE直接编辑user表

[root@server1 ~]# mysql -uroot -p123123
mysql> use mysql;
mysql> update mysql.user set authentication_string = password('123456') where user='root' and host='localhost';
mysql> flush privileges;    刷新

在不知道密码的情况下 跳过密码登录mysql

vi /etc/my.cnf
[mysqld]
末尾添加
skip_name_resolve
skip_grant_tables
bind-address=0.0.0.0

[root@server1 ~]# systemctl restart mysqld