mysql基础

mysql基础

一.mysql体系结构

二.简介

1.什么是mysql ?

MySQL是最受欢迎的开源SQL数据库管理系统，由Oracle Corporation开发，分发和支持MySQL网站（[http://www.mysql.com/]{.underline}）提供有关MySQL软件的最新信息。

2.MySQL是一个数据库管理系统。

数据库是数据的结构化集合。从简单的购物清单到图片库，或者企业网络中的大量信息，它都可以是任何东西。要添加，访问和处理存储在计算机数据库中的数据，就需要一个数据据库管理系统 mysql就是其中之一

3.mysql 数据库的类型？

MySQL数据库是关系型的。

关系数据库将数据存储在单独的表中，而不是将所有数据放在一个大的库房中。数据库结构被组织成针对速度优化的物理文件。逻辑模型具有数据库，表，视图，行和列等对象，可提供灵活的编程环境

存放数据的仓库，数据存放在一张张表中 表与表之间存在一定的关系 因此 就称之为关系型数据库

4.MySQL软件是开源的。

开源意味着任何人都可以使用和修改该软件。任何人都可以从互联网上下载MySQL软件并使用它而无需支付任何费用

5.数据库技术构成

数据库系统 DBS

A. 数据库管理系统（DataBase Management System， DBMS）:

SQL(RDS): ORACLE、Oracle MySQL、MariaDB、Percona server、DB2 NoSQL: Redis、MongoDB、Memcache

B. DBA

SQL语言（数据库结构化查询语言）

A. DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程、函数， CREATE DROP ALTER //开发人员

B. DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE //开发人员

C. DQL语句 数据库查询语言： 查询数据 SELECT

D. DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

数据访问技术

A. ODBC PHP <.php>

B. JDBC JAVA <.jsp>

6.常见的关系型数据库和非关系统型数据库关系型：

[Oracle]{.underline}

[MySQL]{.underline}

[MariaDB]{.underline} [DB2]{.underline}

非关系型：

[Redis]{.underline}

[Memcached]{.underline} [MongoDB]{.underline}

7.mysql的默认端口: 3306

三.mysql5.7安装

1.mariadb安装

1. 清理环境

# rpm -qa | grep mariadb

mariadb-libs-5.5.60-1.el7_5.x86_64

#rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps #rpm -qa | grep mysql

# id mysql

id: mysql: no such user

# userdel -r mysql 有mysql用户就删除没有就不管

2. 查看本地yum源是否有mariadb安装包

# ls /mnt/iso/Packages/ | grep mariadb mariadb-5.5.60-1.el7_5.x86_64.rpm mariadb-bench-5.5.60-1.el7_5.x86_64.rpm mariadb-devel-5.5.60-1.el7_5.x86_64.rpm mariadb-libs-5.5.60-1.el7_5.x86_64.rpm mariadb-server-5.5.60-1.el7_5.x86_64.rpm mariadb-test-5.5.60-1.el7_5.x86_64.rpm

3. 安装mariadb

yum -y install mariadb*

4. 启动数据库,查看端口3306 #systemctl start mariadb

ss -tanlp | grep mysql

LISTEN 0 50 *:3306 *:* users:(("mysqld",pid=7885,fd=13))

5. 初始化数据库设置密码

# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation.

Set root password? [Y/n] y (给数据库设置密码) New password:

Re-enter new password:

Password updated successfully! Reloading privilege tables..

... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation

go a bit smoother. You should remove them before moving into a production environment.

Remove anonymous users? [Y/\]

 ... Success!

Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]

 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment.

Remove test database and access to it? [Y/n]

-   Dropping test database...

... Success!

-   Removing privileges on test database...

 ... Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n]

... Success! Cleaning up...

All done! If you\'ve completed all of the above steps, your MariaDB installation should now be secure.

6. 进入数据库

# mysql -u root -p1 -p 初始化数据库时的密码

Welcome to the MariaDB monitor. Commands end with ; or g.

Your MariaDB connection id is 10

Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]>

7. 退出数据库

MariaDB [(none)]> exit Bye

至此数据库安装成功

=======================================================================

2.数据库配置文件

# cat /etc/my.cnf

[mysqld] datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0

# Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the # instructions in [[http://fedoraproject.org/wiki/Systemd]{.underline}](http://fedoraproject.org/wiki/Systemd)

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid

#

# include all files from the config directory 
#!includedir /etc/my.cnf.d

3.修改密码

# mysql -p1

Welcome to the MariaDB monitor. Commands end with ; or g.

Your MariaDB connection id is 19

Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. **MariaDB [(none)]> set password=password('111');**

Query OK, 0 rows affected (0.01 sec)

######## MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> exit Bye

# mysql -p111

Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 20

Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]>

=======================================================================

4.忘记密码操作

1. 关闭服务

#systemctl stop mairadb 或者 pkill -9 mysql 停止数据库

#systemctl status mariadb

2. 略过密码

# mysqld_safe --skip-grant-table & 单用户模式略过密码并放到后台

[1] 35654

[root@localhost ~]# 200704 15:36:24 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'. 200704 15:36:24 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

3. 进入数据库无需密码

#mysql 直接进入数据库

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 1

Server version: 5.5.60-MariaDB MariaDB Server

Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

MariaDB [(none)]> use mysql ; 切换到mysql数据库

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [mysql]> update user set password=password(‘1’) where user=‘root’; 执行修改密码的sql语句

Query OK, 0 rows affected (0.00 sec)

Rows matched: 4 Changed: 0 Warnings: 0

MariaDB [mysql]> flush privileges; Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> exit Bye

[root@localhost ~]# systemctl start mariadb

[root@localhost ~]# mysql -p1

Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3

Server version: 5.5.60-MariaDB MariaDB Server

Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement. MariaDB [(none)]>

5.客户端远程连接

服务器：192.168.124.128 客户端：192.168.124.129

1. 服务器授权用户

    #mysql -uroot -p1
   
   MariaDB [(none)]> grant all PRIVILEGES on *.* to 'u1'@\'%' identified by '1\'**;**
   
   Query OK, 0 rows affected (0.01 sec)
   
   MariaDB [(none)]> FLUSH PRIVILEGES
   
   -> ;
   
   Query OK, 0 rows affected (0.00 sec)
   
   MariaDB [(none)]> exit Bye
   

2. 客户端远程登录

yum install -y mariadb.x86_64 mariadb-libs.x86_64 

# mysql -h 192.168.124.128 -P3306 -uu1 -p1

Welcome to the MariaDB monitor. Commands end with ; or g.

Your MariaDB connection id is 16

Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]>

6.源码安装

先检查本地源是否可用**** xshell远程上传下载安装工具

yum -y install lrzsz

安装：

1. 清理安装环境：（erase,remove,install,reinstall）

##yum erase mariadb mariadb-server mariadb-libs mariadb-devel -y

##userdel -r mysql

##rm -rf /etc/my*

##rm -rf /var/lib/mysql rpm和二进制#rm -rf /usr/local/mysql 源 码

##rm -rf CMakeCache.txt 解压包里面

2. 创建mysql账户：(没有登录shell，没有家目录的用户)

useradd -r mysql -M -s /sbin/nologin

3. 从官网下载**.gz**的源码安装包

mysql-5.7.20.tar.gz

boost_1_59_0.tar.gz

解压mysql压缩包，在解压boost安装包，把boost压缩包解压到mysql目录里面去。

[root@mysql ~]# ls /mysql/ boost_1\_59_0.tar.gz mysql-5.7.26.tar.gz

[root@mysql ~]# tar -xvf /mysql/mysql-5.7.26.tar.gz -C /usr/local/

[root@mysql ~]# tar -xvf /mysql/boost_1\_59_0.tar.gz -C /usr/local/mysql-5.7.26/

7.编译安装

编译安装有一定的系统安装条件，也就时对其他包的依赖。

这时候我们就要安装一些依赖包。下面呢，我们来讲一下，这些依赖包的作用以及需要使用的版本。

1> cmake

2> make3.75

3> gcc4.4.6

4> Boost1.59.0

5> bison2.1

6> ncurses

#yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make cmake

#mkdir -p /data /usr/local/mysql &&chown -R mysql:mysql /usr/local/mysql /data

配置：可以写入一个脚本执行

首先需要[cd到解压的mysql]{.underline}目录（#cd mysql-5.7.22 ），执行下面的命令

#cmake . -DWITH_BOOST=boost_1\_59_0/ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DMYSQL_DATADIR=/data -DINSTALL_MANDIR=/usr/share/man -DMYSQL_TCP_PORT=3306 - DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DEXTRA_CHARSETS=all - DDEFAULT_COLLATION=utf8_general_ci -DWITH_READLINE=1 -DWITH_SSL=system - DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1

####### 差依赖包错误提示

错误1

-bash: cmake: 未找到命令 (没有安装cmake)

错误2

CMake Error: your C compiler: “CMAKE_C_COMPILER-NOTFOUND” was not found. Please set

CMAKE_C_COMPILER to a valid compiler path or name.

CMake Error: your CXX compiler: “CMAKE_CXX_COMPILER-NOTFOUND” was not found. Please set CMAKE_CXX_COMPILER to a valid compiler path or name.

– CMAKE_GENERATOR: Unix Makefiles

(没有安装gcc-c++ gcc)

错误3

CMake Error at cmake/readline.cmake:64 (MESSAGE):

Curses library not found. Please install appropriate package,

remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.

(没有安装ncurses-devel)

[root@mysql-5.7.17 ~]# cmake . \

-DWITH_BOOST=boost_1_59_0/ \

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql MySQL的安装目录

-DSYSCONFDIR=/etc 存放 配置文件的位置 （默认可以不安装配置文件） my.cnf my.conf.d

-DMYSQL_DATADIR=/usr/local/mysql/data 数据目录的存放位置，同时错误日志文件也会在这个目录

-DINSTALL_MANDIR=/usr/share/man 帮助文档

-DMYSQL_TCP_PORT=3306 默认端口

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock sock文件位置，用来做网络通信的，客户端连接服务器的时候会用到

-DDEFAULT_CHARSET=utf8 默认字符集。字符集的支持，可以调

-DEXTRA_CHARSETS=all 扩展的字符集支持所有的

-DDEFAULT_COLLATION=utf8_general_ci 支持的utf8字符集

-DWITH_READLINE=1 上下翻历史命令

-DWITH_SSL=system 使用私钥和证书登陆（公钥） 可以加密。 适用与长连接。坏处：速度慢

-DWITH_EMBEDDED_SERVER=1 嵌入式数据库

-DENABLED_LOCAL_INFILE=1 从本地倒入数据，不是备份和恢复。

-DWITH_INNOBASE_STORAGE_ENGINE=1 默认的存储引擎，支持外键这些配置参数我偶有在下边做解释，大家一会看一下。

提示：boost也可以使用如下指令自动下载，如果不下载bost压缩包，把下面的这一条添加到配置中第二行

-DDOWNLOAD_BOOST=1/

提示一下内容标识配置成功：

– Configuring done 配置完成

– Generating done 生成完成

#make -j 数字（增加内核CPU）

#make&&make install

初始化

需要先进入mysql的安装目录。它安装目录的的功能

命令如下：

#cd /usr/local/mysql

#./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data 5>z(l?g5ymbO

配置MySQL配置文件my.cnf

第一种：系统默认的配置文件

在5.6中常常使用源码包提供的默认配置文件，而在生产环境中一般是进行自定义配置文件（在5.7中会遇到support-files下没有my-default.cnf文件的情况，这种情况直接选择自定义配置 ）

#cp support-files/my-default.cnf /etc/my.cnf

第二种：自定义配置（生产环境常用）

[root@mysql1 mysql]# vim /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql 安装目录

datadir=/data 数据存放目录

启动mysql

cp support-files/mysql.server /etc/init.d/mysqld //生成mysql.server脚本(system V)

chmod 755 /etc/init.d/mysqld（默认是755，安全起见敲一下） # chkconfig --add mysqld // 给MySQL设置启动项

#chkconfig mysqld on //给MySQL设置开机启动

#service mysqld start // 执行启动命令

**修改密码： **

方法一：

#/usr/local/mysql/bin/mysqladmin -u root -p’旧密码’ password’新密码’

或者

利用初始密码进入MySQL

#/cd /usr/local/mysql

#./bin/mysql -uroot -p’Psxf*=g/y28i’ mysql> SET PASSWORD=PASSWORD(‘123’);

方法二：

set password=password(‘123’)

添加环境变量：

vi ~/.bash_profile

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin # source ~/.bash_profile

之后就可以在任何地方使用mysql命令登陆Mysql服务器：

#mysql -u root -p新密码

查看数据库的版本号和创建日期

mysql> SELECT VERSION(), CURRENT_DATE;

登陆mysql

#/usr/local/mysql/bin/mysql -u root -p’123’

然后：shows databases // 查看数据库退出：exit

关闭MySQL服务

#service mysqld stop；

强制关闭退出mysql服务：（非常时期，非常手段）

pkill -9 进程名

重新安装MySQL的步骤：（之前安装过MySQL）

1. 删除安装目录：rm -rf /usr/local/mysql

2. 删除解压包目录：rm -rf …/mysql.5.7.22 或者只删除CMakeCache.txt

3. 删除mysql用户

4. 删除mysql配置文件 /etc/my.cnf

8.mysql客户端安装

1.准备客户端安装包[https://www.mysql.com/]{.underline}打开官网

这三个包是依赖关系注意必须按顺序安装,先将自带的mariadb安装包删除不然也会报错

#rpm -qa | grep mariadb** 查看已安装的mariadb包

mariadb-libs-5.5.52-1.el7.x86_64

#rpm -e --nodeps **mariadb-libs-5.5.52-1.el7.x86_64**

#rpm -ivh mysql-community-common-5.7.25-1.el7.x86_64.rpm 1 

#rpm -ivh mysql-community-libs-5.7.25-1.el7.x86_64.rpm 2

#rpm -ivh mysql-community-client-5.7.25-1.el7.x86_64.rpm 3

安装完成后查看mysql客户端版本

mysql --version

mysql Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using EditLine wrapper

客户端安装完成

测试:

第一步:客户端 和服务器端要关闭 防火墙 selinux ip 相互能拼通

#systemctl stop firewalld

#systemctl disabled firewalld 开机关闭防火墙

#vim /etc/selinux/config 关 闭 selinux 将 SELINUX=enforcing 改 成 SELINUX=disabled shift + “:” ==>输入 wq 保存退出

#setenforce 0

第二步:服务器端授权

#mysql -uroot p2 进数据库授权用户

mysql>grant all privileges on . to ‘root’@’%’ identified by ‘password’; mysql>flush privileges;

第三步:客户端测试

[root@client ~]# mysql -h 192.168.234.128 -uroot -P 3306 -p2

-h 主机名或ip

-u 用户名

-p 密 码

-P 端 口

看到能进入到数据库表示ok

9.rpm安装

1.清理安装环境：

#yum erase mariadb mariadb-server mariadb-libs mariadb-devel -y

#userdel -r mysql 
# rm -rf /etc/my\*

#rm -rf /var/lib/mysql

2. 创建mysql账户：

#useradd -r mysql -M -s /bin/false

在安装过程中，如果出现找不到密码的情况，把/var/lib/mysql/删除后，重新初始化

mysqld --initialize --user=mysql

下载yum源安装包：

[https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm]{.underline}（可能已经失效，以官方包为准）

#md5sum mysql57-community-release-el7-9.noarch.rpm

安装yum源安装包：

#yum -y install mysql57-community-release-el7-9.noarch.rpm

#yum repolist all | grep mysql 查看所有关于mysql的库

#yum -config-manager --enable mysql-community 将禁用的yum源库启用

#yum -y install mysql-community-server

先关防火墙、selinux然后再起服务

# systemctl start mysqld //第一次启动先初始数据库

# systemctl enable mysqld

#grep password /var/log/mysqld.log #mysqladmin -u root -p'原密码' passwd '新密码' 
#mysql -u root -p'新密码'登录mysql

10.二进制安装

二 进 制 预 编 译 mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz [http://dev.mysql.com/doc/refman/5.7/en/binary-installation.html]{.underline}（英文文档，可用谷歌浏览器翻译）

#groupadd mysql
#useradd -r -g mysql -s /bin/false mysql 
# cd /usr/local

[root@mysql1 local]# tar xf /root/mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz

[root@mysql1 local]# ln -s mysql-5.7.19-linux-glibc2.12-x86_64 mysql（做软连接）

mysql 初始化

没有编译安装的过程

[root@mysql1 local]# cd mysql

[root@mysql1 mysql]# mkdir mysql-files
[root@mysql1 mysql]# chmod 750 mysql-files 
[root@mysql1 mysql]# chown -R mysql . 
[root@mysql1 mysql]# chgrp -R mysql .

[root@mysql1 mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/ data

[root@mysql1 mysql]# bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data 
[root@mysql1 mysql]# chown -R root .

[root@mysql1 mysql]# chown -R mysql data mysql-files

**建 立 MySQL 配 置 文 件 my.cnf **

[root@mysql1 mysql]# vim /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

启动MySQL

方法一：使用mysqld_safe

[root@mysql1 mysql]# bin/mysqld_safe --user=mysql &

方法二：使用centos6 mysql.server脚本(system V)

[root@mysql1 mysql]# cp support-files/mysql.server /etc/init.d/mysqld

[root@mysql1 mysql]# chkconfig --add mysqld

[root@mysql1 mysql]# chkconfig mysqld on //设置启动项并开机启动

[root@mysql2 mysql]# service mysqld start

Starting MySQL.Logging to ‘/usr/local/mysql/data/mysql2.err’. SUCCESS!

[root@mysql2 mysql]# ps aux |grep mysqld

mysql 76 pts/0 Sl 14:38 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/ mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=mysql2.err --pid-file=/usr/local/ mysql/data/mysql2.pid

PATH[可选]

[root@mysql2 mysql]# mysql

-bash: mysql: command not found

[root@mysql2 mysql]# /usr/local/mysql/bin/mysql

[root@mysql1 mysql]# echo “export PATH=$PATH:/usr/local/mysql/bin” >> /etc/profile

[root@mysql1 mysql]# source /etc/profile

[root@mysql1 mysql]# mysql -uroot -p’xxxx’

mysql> alter user root@‘localhost’ identified by ‘alan’;

**如果需要重新初始化…[可选] **

#killall mysqld

#rm -rf /usr/local/mysql/data

[root@mysql1 mysql]# chown -R mysql .

[root@mysql1 mysql]# chgrp -R mysql .

[root@mysql1 mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/ data

[root@mysql1 mysql]# bin/mysql_ssl_rsa_setup

[root@mysql1 mysql]# chown -R root .

[root@mysql1 mysql]# chown -R mysql data mysql-files

11.windows远程连接Mysql工具

1.下载安装windows远程 连接mysql工具navicat

2.安装

3. 启动连接

   

4. 使用

12.数据库卸载

1.源码安装的卸载方法：

到源码目录，如果可以运行make uninstall就可以卸载。如果没有，就直接删除。如果是./configure --prefix指定了目录用 rm -rf 目录名 删除

[root@localhost ~]# cd /usr/local/src/mysql-5.1.45 

[root@localhost mysql-5.1.45]# make uninstall 

[root@localhost mysql-5.1.45]# cd .. 

[root@localhost src]# rm -rf mysql-5.1.45 

[root@localhost src]# cd ..

[root@localhost local]# ls

bin etc games include lib libexec mysql sbin share src web [root@localhost local]# rm -rf mysql

[root@localhost local]# ls

bin etc games include lib libexec sbin share src web

2.rpm方式安装的卸载方法：

注意：/var/lib/mysql

查看是否安装mysql的命令

rpm -qa | grep mysql

然后 逐个使用 rpm -e mysq-* 卸载

如果出现

error: Failed dependencies:

libmysqlclient.so.10 is needed by (installed) libdbi-dbd-mysql-0.6.5-10.RHEL4.1.i386 libmysqlclient.so.10 is needed by (installed) MySQL-python-1.0.0-1.RHEL4.1.i386 libmysqlclient.so.10 is needed by (installed) MyODBC-2.50.39-21.RHEL4.1.i386 libmysqlclient.so.10 is needed by (installed) qt-MySQL-3.3.3-9.3.i386

mysqlclient10 is needed by (installed) MySQL-python-1.0.0-1.RHEL4.1.i386

mysqlclient10 = 3.23.58-4.RHEL4.1 is needed by (installed) mysqlclient10-devel-3.23.58-4.RHEL4.1.i386

时，使用 --nodeps 参数，如：

rpm -e mysqlclient10-devel-3.23.58-4.RHEL4.1 --nodeps

rpm -e mysqlclient10-3.23.58-4.RHEL4.1 --nodeps

13.编绎安装脚本

#!/usr/bin/env bash

#mysql 编译安装 by blackmed loc=/usr/local/mysql/bin blackmed(){

**#**前期准备

rpm -qa | grep mariadb if [ $? -eq 0 ];then

rpm -e --nodeps >/dev/null

fi

rm -rf /etc/my* >/dev/null

rm -rf /var/lib/mysql >/dev/null userdel -r mysql >/dev/null

rpm -qa | grep cmake if [ $? -ne 0 ];then

yum -y install cmake ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make >/dev/null fi

useradd -r mysql -M -s /sbin/nologin echo "前期环境搭建成功."

mkdir /mysql

wget -O /mysql/mysql-5.7.20.tar.gz [https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20.tar.gz]{.underline} >/ dev/null

tar xvf /mysql/mysql-5.7.20.tar.gz -C /mysql/ cd /mysql/mysql-5.7.20

cmake . -DDOWNLOAD_BOOST=1 -DWITH_BOOST=boost_1_59_0/ -DCMAKE_INSTALL_PREFIX=/usr/local/ mysql -DSYSCONFDIR=/etc -DMYSQL_DATADIR=/usr/local/mysql/data -DINSTALL_MANDIR=/usr/share/man - DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -

DEXTRA_CHARSETS=all -DDEFAULT_COLLATION=utf8_general_ci -DWITH_READLINE=1 -DWITH_SSL=system -

DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1

#wget -O /mysql [ftp://192.168.1.129/peizhi.sh]{.underline} #bash peizhi.sh >/dev/null

make && make install echo "mysql 安装成功**"**

**#**初始化

chown -R mysql.mysql /usr/local/mysql/ local_mysql=/usr/local/mysql/bin

KaTeX parse error: Can't use function '\`' in math mode at position 112: …ysql/data mima=\̲`̲{local_mysql}/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/

mysql/data | awk ‘/localhost\😕{print $NF}’ `

#wget -O /etc/my.cnf [ftp://192.168.1.129/my.cnf]{.underline} #配置my.cnf文件

touch /etc/my.cnf

echo “[mysqld]” >> /etc/my.cnf

echo “basedir=/usr/local/mysql” >> /etc/my.cnf echo “datadir=/usr/local/mysql/data” >> /etc/my.cnf

sed -i ‘/PATH=/cPATH=$PATH:$HOME/bin:/usr/local/mysql/bin’ ~/.bash_profile source ~/.bash_profile

mysqld_safe --user=mysql &

mysqladmin -u root -p${mima} password 1 echo "安装成功"

}

blackmed

14.密码管理

1.设置密码

方法一：

mysql> set password=password('1');

Query OK, 0 rows affected, 1 warning (0.00 sec)

方法二：(不能有skip-grant-tables)

service mysqld reload

mysql>set password for root@'localhost' = password('2');

方法三：

#vim /etc/my.cnf

[mysqld]

skip-grant-tables service mysqld restart mysql -uroot -p

update mysql.user set authentication_string=password('2') where User="root" and Host="localhost"; #new update user set password=password('root') where user='root'; #old version

flush privileges;

vi /etc/my.cnf

#skip-grant-tables // 删掉或注释掉

service mysqld restart

报错:

提示你需要修改密码

ERROR 1820 (HY000): You must reset your password using ALTER USER statement；

SET PASSWORD = PASSWORD('1');

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

需要执行两个参数来把mysql默认的密码强度的取消了才行

set global validate_password_policy=0; set global validate_password_mixed_case_count=2;

SET PASSWORD = PASSWORD('1');

**方法三：(不能有skip-grant-tables)**

 service mysqld reload

alter user 'root'@'localhost' identified by '3';

Tips：

**此方式不能在无密码登录模式下修改密码**

2.破解密码

【mysql 5.7】vim /etc/my.cnf

增加跳过授权文件语句。

[mysqld]

skip-grant-tables

service mysqld restart mysql

// MySQL 5.7.5 and earlier:

mysql> update mysql.user set password=password("1\") where user=\"root\" and host=\"localhost\";

// MySQL 5.7.6 and later:

mysql> update mysql.user set authentication_string=password('1\') where user=\'root\';

mysql> flush privileges; 

mysql> q

[root@mysql1 ~]# vim /etc/my.cnf [mysqld\]

#skip-grant-table

[root@mysql1 ~]# service mysqld restart

【mysql 8.0】

数据库版本：8.0.13

mysql> select version();

+-----------+

| version() |

+-----------+

| 8.0.13 |

+-----------+

1.  row in set (0.00 sec)



1.  设置无密码登录： # vim /etc/my.cnf

[mysqld]

skip-grant-tables

systemctl restart mysqld

2.  设置数据库密码为空mysql> use mysql;

mysql> update user set authentication_string=\'' where user=\'root\';

3.  删除无密码登录设置# vim /etc/my.cnf

[mysqld\

4.  登录数据库重置密码(alter修改变更)

mysql> alter user 'root'@'localhost' identified by '1';

mysql> q