mysql的安装使用_Mysql的安装与使用

一、CentOS 7下启动、关闭、重启、查看MySQL服务

1.启动命令

[root@peiyanbing ~]# systemctl start mysqld.service

2.关闭命令

[root@peiyanbing ~]# systemctl stop mysqld.service

3.重启命令

[root@peiyanbing ~]# systemctl restart mysqld.service

4.查看服务状态

[root@peiyanbing ~]# systemctl status mysqld.service

● mysqld.service - MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

Active: active (running) since 三 2019-08-21 14:20:17 CST; 3 weeks 0 days ago

Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Main PID: 29654 (mysqld)

Status: "Server is operational"

Tasks: 41

Memory: 14.4M

CGroup: /system.slice/mysqld.service

└─29654 /usr/sbin/mysqld

8月 21 14:20:04 peiyanbing systemd[1]: Starting MySQL Server...

8月 21 14:20:17 peiyanbing systemd[1]: Started MySQL Server.

5.查看MySql系统配置

[root@peiyanbing ~]# cat /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

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

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

symbolic-links=0

log-error=/var/log/mysqld.log

二、Linux --- CentOS7彻底卸载mysql

1、卸载Centos7自带的Maridb数据库

卸载maridb：

[root@host-131 ~]# rpm -qa | grep mariadb

mariadb-libs-5.5.52-1.el7.x86_64

[root@host-131 ~]# yum -y remove mari*

[root@host-131 ~]# rm -rf /var/lib/mysql/*

[root@host-131 ~]# rpm -qa | grep mariadb

[root@host-131 ~]#

2、彻底卸载MySQL数据库：

基于MySQL数据库基于Centos7.3-部署后，卸载mysql数据库：

[root@host-131 ~]# yum remove mysql-community-serve

# yun remove mysql

使用remove发现还有部分残余文件：

这就是默认的卸载，其实还有很多 MySQL 依赖的软件包都并没有移除，

我们可以使用 rpm 命令的方式查看有哪些相关的软件名

[root@host-131 ~]# rpm -qa |grep mysql

mysql-community-libs-5.7.22-1.el7.x86_64

mysql-community-server-5.7.22-1.el7.x86_64

mysql80-community-release-el7-1.noarch

mysql-community-client-5.7.22-1.el7.x86_64

mysql-community-common-5.7.22-1.el7.x86_64

[root@host-131 ~]# yum remove mysql-* //挨个卸载

卸载并不会自动删除配置文件等，所以我们还需要手动来删除

[root@host-131 ~]# find / -name mysql

/etc/logrotate.d/mysql

/var/lib/mysql

/var/lib/mysql/mysql

/usr/bin/mysql

/usr/lib64/mysql

/usr/share/mysql

[root@host-131 ~]#

[root@host-131 ~]# rm -rf /var/lib/mysql //将上边的文件全部删除

通过这个方法可以完全卸载MySQL

三、CentOS7.2 在线安装MySQL8.0

四、docker安装Mysql 8

1. 运行容器，自动下载镜像

docker run --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql

2. 创建主机挂载配置文件

mkdir -p ~/i/apps/mysql/{conf,data}

conf存放配置文件

data存放数据库文件

3. 初始化配置文件

docker cp mysql:/var/lib/mysql ~/i/apps/mysql/data

将数据库初始文件复制到/usr/mysql/data里面

docker cp mysql:/etc/mysql/my.cnf ~/i/apps/mysql/conf/my.cnf

4. 停止删除容器

docker rm -f mysql

5. 生成启动文件 - start.sh

[root@wanfei ~]# cd ~/i/apps/mysql/

$ cat < start.sh

#!/bin/bash

HOST_NAME=mysql.wanfei.xyz

REDIS_DIR=`pwd`

docker stop mysql

docker rm mysql

docker run -d \\

--hostname \${HOST_NAME} \\

-p 3306:3306 \\

--name mysql \\

--privileged=true \\

-v \${REDIS_DIR}/conf/my.cnf:/etc/mysql/my.cnf \\

-v \${REDIS_DIR}/data/mysql:/var/lib/mysql \\

-e MYSQL_ROOT_PASSWORD=123456 \\

mysql

EOF

参数说明

--hostname \${HOST_NAME}: 设置访问的域名地址，${HOST_NAME}是上面定义的mysql.wanfei.xyz这个地址

-p 3306:3306: 把容器内的3306端口映射到宿主机3306端口

--privileged=true: 给容器加上特定权限 。否则会chown: changing ownership of ‘/var/lib/mysql/....‘: Permission denied

-v \${REDIS_DIR}/conf/my.cnf:/etc/mysql/my.cnf：把宿主机配置好的my.cnf放到容器内的这个位置中

-v \${REDIS_DIR}/data/mysql:/var/lib/mysql：把mysql持久化的数据在宿主机内显示，做数据备份

-e MYSQL_ROOT_PASSWORD=123456: 指定数据库初始访问密码，第一次设置才有小，后面重启以修改的密码为准

mysql：mysql镜像名，后面没有tag后，下载最新的tag，latest

6. 运行start.sh 启动mysql

[root@wanfei mysql]# sh start.sh

mysql

mysql

f3d297633f4432495dc587d2860c212a371166897b6a380aceb0ebcdccc5dad8

7. 进入容器修改密码，开启远程连接

#进入容器

docker exec -it mysql /bin/sh

#进入mysql(有可能上面密码设置无效)

mysql -u root -p

#输入123456，如果失败，直接enter

mysql> use 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

mysql> select host,user,plugin from user;

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

| host | user | plugin |

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

| localhost | mysql.infoschema | caching_sha2_password |

| localhost | mysql.session | caching_sha2_password |

| localhost | mysql.sys | caching_sha2_password |

| localhost | root | caching_sha2_password |

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

4 rows in set (0.00 sec)

#上面的root用户如果是localhost，只能localhost连接，不能远程连接，下面设置密码报错

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'xxxxxx';

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'

#修改localhost为%

mysql> update user set host='%' where user='root';

Query OK, 1 row affected (0.14 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select host,user,plugin from user;

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

| host | user | plugin |

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

| % | root | caching_sha2_password |

| localhost | mysql.infoschema | caching_sha2_password |

| localhost | mysql.session | caching_sha2_password |

| localhost | mysql.sys | caching_sha2_password |

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

4 rows in set (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'xxxxxx';

Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)