MySql安装与使用

shlhhy

已于 2022-07-18 11:31:33 修改

阅读量232

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

于 2021-06-02 10:59:28 首次发布

本文链接：https://blog.csdn.net/shlhhy/article/details/117462929

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1. 安装

1.1 rpm安装

参考教程：https://www.runoob.com/mysql/mysql-install.html

1.2 mariadb安装

yum install mariadb-server -y
systemctl start mariadb
systemctl enable mariadb
#数据库初始化设置
mysql_secure_installation
#首先是设置密码，会提示先输入密码
Enter current password for root (enter for none):<–初次运行直接回车
#设置密码
Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
New password: <– 设置root用户的密码
Re-enter new password: <– 再输入一次你设置的密码
#其他配置
Remove anonymous users? [Y/n] <– 是否删除匿名用户，回车
Disallow root login remotely? [Y/n] <–是否禁止root远程登录,回车,
Remove test database and access to it? [Y/n] <– 是否删除test数据库，回车
Reload privilege tables now? [Y/n] <– 是否重新加载权限表，回车
#初始化MariaDB完成，接下来测试登录，输入密码能正常登陆就完成了
mysql -uroot -p

1.3 docker 安装

docker search mysql

在这里插入图片描述
拉取一个镜像，此处直接选第一个

docker pull docker.io/mysql

启动mysql容器

docker run -id --name=mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 docker.io/mysql:latest

将该容器生成的conf文件复制到本地，删除mysql容器

docker cp mysql:/etc/mysql/my.cnf /home/mysql/conf

启动新的mysql容器，并将conf和数据文件挂载到本地

docker run -tid -p 3306:3306 --name=mysql22  -e MYSQL_ROOT_PASSWORD=123456 -v /opt/install/mysql/conf/my.cnf:/etc/mysql/my.cnf -v /opt/install/mysql/data:/var/lib/mysql docker.io/mysql:latest

2. 知识储备

2.1 字段类型

varchar(n)：字符数
LONGTEXT：4g字节

2.2 索引

mysql的索引包括主键索引，唯一索引和普通索引，普通索引不要求字段值唯一。

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`);
ALTER TABLE `IO_AI_EDI_INFO` ADD INDEX OWNER_ADDR (`OWNER_ADDR`);

2.3 explain和group by

解释一条sql语句的执行过程，重点参数：

type：观察sql执行是否使用了索引

explain select V_GOODS_NAME,count(BILL_NO) from abnormal_result_new where TOTAL_VALUE='10' group by 1 order by 2 desc limit 10;

该查询语法中使用了group by，当表的数据量较大时，内存可能无法加载某条sql的全数据量，因此可以使用mysql 内置的group by语法做些基础的统计分析。

在这里插入图片描述

2.4 查询缓存

mysql可以通过开启查询缓存以提高性能，当mysql中表的数据发生变化时，查询缓存会失效

show variables like 'query_cache%';
set query_cache_type = ON;

2.5 模糊查询

当通配符仅在右边出现时，like查询仍能使用索引

select count(1) from abnormal_result_new where V_GOODS_NAME like '帽子%';

当通配符两边均有时，like查询无法使用索引
！！当数据量较大时，或查询效率要求高时，不建议在mysql中使用like查询
！！应当使用搜索引擎，如ES等进行查询

select count(1) from abnormal_result_new where V_GOODS_NAME like '%帽子%';

2.6 IN查询

某些情况下需要根据一个A表的查询结果，联查另一张B表，采用如下sql执行：

select * from abnormal_result_new where V_GOODS_NAME IN(select DISTINCT V_GOODS_NAME from abnormal_result_new where ( PACK_NO='10'))

当A表的查询sql中使用了limit语法时，需要将A表查询结果临时存下才可联查B表，即如下的sql：

select * from abnormal_result_new where V_GOODS_NAME IN(select V_GOODS_NAME from (select DISTINCT V_GOODS_NAME from abnormal_result_new where (( PACK_NO='10')) limit 5) as t)

2.7 批量更新

当存在某个使用场景：若某行记录存在，则在table中更新该行，若某行记录不存在，则向table中插入该行。
由于单个update操作使用起来很慢，而sqlalchemy的df.to_sql()语法只能批量插入，无法批量更新。
因此采用executemany可以实现该批量更新和插入同时存在的应用场景，需要注意的是，执行如下代码必须保证table中存在一个唯一索引，否则批量更新不会生效，只会执行插入。

cols = ['field1', 'field2', 'field3']
_ = ['%s'] * len(cols)
v = []
for col in cols:
    v.append('%s=values(%s)' % (col, col))
sql = "insert into %s(%s)values(%s) on duplicate key update %s" % \
      ('table', ','.join(cols), ','.join(_), ','.join(v))
cursor.executemany(sql, update_data)
conn.commit()

2.8 联合索引

在2.7节中谈到了唯一索引的问题，若某张表允许field1重复，field2重复，但不允许有2条记录(field1,field2)相同，这种可以建立联合唯一索引。

alter table table add unique index(field1,field2);

3. 问题解决

3.1 索引长度限制

由于该字段为VARCHAR(300)，超出了最大长度的限制，因此需要修改配置后才能建立索引
在这里插入图片描述

set global innodb_file_format = BARRACUDA;
set global innodb_large_prefix = ON;
# 由于表已创建成功，且存在数据，因此做此修改，可在建表时直接指定
alter table IO_AI_EDI_INFO_new row_format=dynamic;

3.2 insert报错

某次插入数据时报错：MySQL server has gone away (BrokenPipeError(32, ‘Broken pipe’))[MySQL插入内容超过4M]

解决方法: 修改MySQL的max_allowed_packet值

show VARIABLES like '%max_allowed_packet%';
# 修改为32m
set global max_allowed_packet = 1024*1024*32;

4. 备份与恢复

开启binlog，找到binlog文件的位置，从binlog中找到事务的start与stop的位置

# 查看binlog是否开启，以及日志的存储位置
show variables like 'log_%';

找到事务的位置，并恢复数据

df = pd.read_sql("show binlog events in 'binlog.001153'" % i, db)
index_list = df[df['Info'].str.contains('data_platform.tender_url')].index.tolist()
    for index in index_list:
        start = 0
        stop = 0
        for m in range(index, index-10, -1):
            info = df['Info'].loc[m]
            if 'BEGIN' == info:
                start = df['Pos'].loc[m]
                break
        for n in range(index, index+100, 1):
            info = df['Info'].loc[n]
            if "SET @@SESSION.GTID_NEXT= 'ANONYMOUS'" == info:
                stop = df['Pos'].loc[n]
                break
        sql = "mysqlbinlog --no-defaults " \
              "--start-position=%s " \
              "--stop-position=%s " \
              "--database=data_platform " \
              "/opt/mysql/data/binlog.001153 | mysql -u<name> -p<pwd>" % (start, stop)
        f.write(sql)
        f.write('\n')