mysql使用

发表于 更新于 阅读次数：

安装

docker

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#下载
docker pull mysql:5.6

#运行实例
docker run \
--name mysqldb \
-e MYSQL_ROOT_PASSWORD=123456 \
-p 3306:3306 \
-d mysql:5.6

#进入容器
docker exec -it mysqldb /bin/bash

#登录数据库 
root@a1f0b553fed0:/# mysql -uroot -p123456

docker操作

1
2
3
4
5
6
7
查看操作系统版本
# cat /etc/issue

修改apt源 /etc/apt/sources.list
deb http://mirrors.aliyun.com/debian stretch main
deb http://mirrors.aliyun.com/debian-security stretch/updates main
deb http://mirrors.aliyun.com/debian stretch-updates main

navicat

windows

  • mysql 5.6

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    # 下载
    https://dev.mysql.com/downloads/mysql/

    # 配置PATH

    # 进入mysql目录后, 初始化数据库
    mysqld --initialize-insecure

    # 安装MySQL服务
    mysqld -install

    # 启动MySQL服务
    net start MySQL

    # 初始化密码
    mysqladmin -u root password

    # 客户端登录
    mysql -u root -p

    # 关闭MySQL服务
    net stop MySQL

    # 删除mysql服务
    mysqld -remove

  • mysql8普通用户免安装启动
    前提：下载mysql-server.zip

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    1. 配置mysql到path,这样方便使用mysql命令
    set MYSQL_HOME=./mysql-8.0.23-winx64
    set path=%path%;%MYSQL_HOME%/bin

    2. 初始化数据库
    mysqld --initialize --console
    注意记住默认的临时密码

    3. 启动
    mysqld --console

    ## 第一次使用临时密码进入MySQL,随后赶紧重置密码
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password';

    mysql -u root -p

centos6

1.安装
yum install mysql-community-server -y

1
2
3
4
[mysql]
name=mysql
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/x86_64/
gpgcheck=0

2.启动服务
service mysqld start
3.初始化密码
shell> grep 'temporary password' /var/log/mysqld.log
shell> mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

4.修改数据文件存储位置 

1
mysql> show variables like "datadir";

vim /etc/my.cnf

1
2
datadir=/u01/mysql
socket=/u01/mysql/mysql.sock

mysql重启报错

1
setenforce 0

5.查看配置文件位置
mysqld –verbose –help | grep -A 1 ‘Default options’

centos7安装mysql-5.7.31-el7-x86_64.tar.gz

  1. 解压到/opt目录

    1
    2
    tar zxvf mysql-5.7.31-el7-x86_64.tar.gz -C /opt
    mv mysql-5.7.31-el7-x86_64 mysql

  2. 编辑/etc/my.cnf

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    [client]
    #character-set-server=utf8
    port=3306
    socket=/var/lib/mysql/mysql.sock
    [mysqld]
    #skip-grant-tables
    #设置3306端口
    port=3306
    socket=/var/lib/mysql/mysql.sock
    #设置mysql的安装目录
    basedir=/opt/mysql
    #设置mysql数据库的数据的存放目录
    datadir=/opt/mysql/data
    #允许最大连接数
    max_connections=200
    #服务端使用的字符集默认为8比特编码的latin1字符集
    character-set-server=utf8
    #创建新表时将使用的默认存储引擎
    default-storage-engine=INNODB
    user=mysql
    [mysqld_safe]
    pid-file=/usr/local/mysql/data/n1.pid

  3. 安装mysql

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    创建mysql用户和组
    #groupadd mysql
    #useradd -g mysql mysql

    初始化mysql data目录
    #cd /opt/mysql/
    #./bin/mysqld --initialize --user=mysql

    #cp support-files/mysql.server /etc/init.d

    启动mysql服务
    #/etc/init.d/mysql.server restart

    设置为开机启动
    #chkconfig --level 35 mysql.server on

centos8

  1. 安装
    yum install mysql-server

系统管理

1
2
3
4
5
# 查看引擎
show variables like '%storage_engine%';

# 系统版本
select version();

用户及权限操作语句

  • 数据库

  • 用户

    • 创建用户

      1
      2
      3
      create user 'testdbuser' @'%';
      -- 设置用户密码
      alter user 'testdbuser' @'%' IDENTIFIED by '123456' ;

    • 删除用户

      1
      2
      3
      4
              drop user 'testdbuser' @'%';
              ```    
      - [授权](https://dev.mysql.com/doc/refman/8.0/en/grant.html#grant-database-privileges)
          - 给用户授权

      – 授予testdb数据库下所有权限给testdbuser用户
      grant all on testdb.* to ‘testdbuser’ @’%’;

      1
      - 移除用户权限

      revoke all on testdb2.* from ‘testdbuser’ @’%’;

      1
      2
      3
      - [角色](https://dev.mysql.com/doc/refman/8.0/en/create-role.html)(mysql8新增)

      # 索引

alter table ratings
add index rating_restid_index (restid);

show index from ratings;

alter table ratings
drop index rating_restid_index;

– 设置主键
update ratings set id=CONCAT(DATE_FORMAT(createTime,’%Y%m%d%H%i%s’),’-‘,userId,’-‘,restId);
alter table ratings add primary key(id); 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# python操作mysql
1.读取数据库列表
```python
# 
# python2
# pip install pymysql
#
import pymysql

conn=pymysql.connect(host='192.168.18.147',user='testdbuser',passwd='testdbuser',charset='utf8',port=3306)

cur=conn.cursor()

cur.execute('show databases')

databases=[]
for i in cur:
    databases.append(i)

cur.close()
conn.close()
 
print (databases)

2.查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import pymysql

conn=pymysql.connect(host='192.168.18.147', db='testdb',user='testdbuser',passwd='testdbuser',charset='utf8',port=3306)

stmt = conn.cursor()

stmt.execute('select count(1) from ratings')

count = stmt.fetchone()

#stmt.execute('select * from ratings order by createTime')

#firstObj = stmt.fetchone()
#print (firstObj)

##results = stmt.fetchall()
# for result in results:
#     databases.append(i)

stmt.close()
conn.close()

print (count)

导入导出

从cvs文件导入

1
2


问题

1. mysql8 jdbc访问报错 Public Key Retrieval is not allowed

设置连接参数 allowPublicKeyRetrieval=true