skills_mysql

目录

skills_mysql

1 CentOS 6.9 用rpm安装MySQL5.7

1.1 安装

# 清理系统自带的mysql版本
whereis mysql
yum -y remove mysql-libs
yum clean dbcache 
# 下载并安装
wget http://repo.mysql.com/mysql57-community-release-el7-11.noarch.rpm
rpm -ivh mysql57-community-release-el7-11.noarch.rpm
yum -y install mysql-community-server
# 启动并赋权
service mysqld start
mysql -uroot
update user set password=PASSWORD("123456") where user='root';
flush privileges;
quit
mysql -uroot
mysql -uroot -p123456

1.2 报错

执行 yum -y install mysql-community-server 时报错：

Error: Package: mysql-community-client-5.7.26-1.el7.x86_64 (mysql57-community)
Requires: libstdc++.so.6(GLIBCXX_3.4.15)(64bit)
Error: Package: mysql-community-server-5.7.26-1.el7.x86_64 (mysql57-community)
Requires: libstdc++.so.6(GLIBCXX_3.4.15)(64bit)
Error: Package: mysql-community-libs-5.7.26-1.el7.x86_64 (mysql57-community)
Requires: libc.so.6(GLIBC_2.14)(64bit)
Error: Package: mysql-community-server-5.7.26-1.el7.x86_64 (mysql57-community)
Requires: libc.so.6(GLIBC_2.17)(64bit)
Error: Package: mysql-community-server-5.7.26-1.el7.x86_64 (mysql57-community)
Requires: systemd
Error: Package: mysql-community-client-5.7.26-1.el7.x86_64 (mysql57-community)
Requires: libc.so.6(GLIBC_2.14)(64bit)
Error: Package: mysql-community-server-5.7.26-1.el7.x86_64 (mysql57-community)
Requires: libsasl2.so.3()(64bit)

原因：
该文中的rpm包“mysql57-community-release-el7-11.noarch.rpm”，是适用于centos 7 的。

解决方法：
下载适用于centos6.9的rpm包：wget https://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm
查看安装的mysql：yum list installed | grep mysql
查看系统自带的mysql版本：
rpm -qa | grep mysql
mysql-libs.x86_64 5.1.73-8.el6_8 @base
mysql57-community-release.noarch
删除安装的mysql：yum -y remove mysql57-community-release.noarch
重新下载mysql的rpm文件：wget https://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm
安装Mysql的yum源：rpm -ivh mysql57-community-release-el6-9.noarch.rpm
安装Mysql：yum install mysql-community-server
如果这是还报Package: mysql-community-server-5.7.25-1.el7.x86_64 (mysql57-community)，执行下yum clean all。

转载自：https://blog.csdn.net/YYYQYYY/article/details/77469723
　　　　https://blog.csdn.net/u011886447/article/details/89166946
在原文的基础上略有优化。

2 centos 6.9使用yum安装mysql

  1. 查看系统自带的mysql版本：rpm -qa | grep mysql
  2. 卸载mysql：rpm -e mysql-libs-5.1.73-8.el6_8.x86_64 --nodeps
  3. 安装mysql 服务器端：yum -y install mysql-server mysql-devel
  4. 安装mysql客户端：yum install mysql
  5. 启动mysql服务：service mysqld start或者/etc/init.d/mysqld start
  6. 停止：service mysqld stop
  7. 重启：service mysqld restart
  8. 创建root管理员：mysqladmin -u root password root
  9. 登录：mysql -uroot -proot
  10. 设置mysql的开机启动：
    chkconfig --add mysqld
    chkconfig mysqld on

3 centos 7.2中MySql5.6.26升级到5.7.27(rpm包形式)

3.1 安装

  1. 首先去mysql官网下载对应的包，官网下载的最新的5.7.27，需要如下选中的四个就好了：

  2. 停止服务service mysql stop，开始安装前请备份数据，由于我这是测试环境我省去了备份数据，升级完成之后也没有丢失数据，一般来说升级不会丢失数据，但是以防万一，备份之前的 conf文件cp /etc/my.cnf /etc/my.cnf.bak。

  3. 查找安装过的包：rpm -qa |grep -i mysql

  4. 移除安装的包：
    rpm -qa | grep -i mysql | xargs rpm -ev --nodeps
    warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave

    注意：rpm移除过程中，会自动备份my.cnf配置文件为my.cnf.rpmsave！

  5. 安装新的包
    据说安装新包有严格的安装顺序：

rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpm 
rpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm

  1. 查看mysql版本：
    shell命令行下执行：mysql --version
    登录mysql后执行：select version(); #5.7.26
    完美升级！

3.2 报错

连接成功了，但是访问有些方法的时候报错了:
比如： show variables like '%character%';
Table 'performance_schema.session_variables' doesn't exist
解决方法：

  1. 升级数据库：mysql_upgrade -u root -p --force
  2. 重启服务：service mysqld restart

参考链接：https://blog.csdn.net/jingyu333/article/details/90170602

4 mysql5.7修改账户密码

4.1 首次登录时，修改root账户的密码

vim /etc/my.cnf
在末尾添加 skip-grant-tables ，保存。
重启mysqld服务：service mysqld restart
再次登录时，不需要密码验证：

mysql -uroot
mysql> use mysql
mysql> update user set authentication_string=password('HEpan693640.') where user='root';
mysql> flush privileges;
mysql> quit

上面修改的密码相当于在mysql安装的时候的初始化的密码变成了我们修改的密码。所以下次正常登录系统后mysql数据库还是要你把原始密码修改掉。

vim /etc/my.cnf ，注释或删掉 skip-grant-tables
重启mysqld服务：service mysqld restart
再次以账户密码登录：mysql -uroot -pHEpan693640.

此时系统会提示你修改密码，并且必须使用ALTER USER的方式修改，另外，无法执行其他任何语句：
mysql> alter user 'root'@'localhost' identified by 'root';

此时系统报错：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

原因：密码策略过于严格。
将密码设置复杂些，符合密码策略：
mysql> alter user 'root'@'localhost' identified by 'Root1@345';
然后，可以执行其他语句了。

我还想把密码修改得简洁些，于是需要修改密码策略：

修改密码策略：

mysql> set global validate_password_length=1;
mysql> set global validate_password_mixed_case_count=0;
mysql> set global validate_password_number_count=0;
mysql> set global validate_password_policy='LOW';
mysql> set global validate_password_special_char_count=0;

修改过后密码策略如下：

然后再次执行alter user，或其他修改密码的方法 即可。

4.2 其他时候修改密码的方法：

1、mysql> alter user 'root'@'localhost' identified by 'root';
2、mysql> set password=password('root');

4.3 扩展

1、mysql初始化设置（包括设置root密码）：
[root@node03 ~]# /usr/bin/mysql_secure_installation，然后根据提示往下走。
2、mysql取消密码强度验证功能：
vim /etc/my.cnf，在末尾添加 validate-password=OFF。

5 mysql设置字符集为utf8

  1. 查看字符集编码
    show variables like '%character%';
  2. 修改编码
vim /etc/my.cnf

# 设置后的配置文件如下
[mysql]
default-character-set=utf8
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-character-set=utf8
character_set_server=utf8
#lower_case_table_names=1 忽略大小写

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-character-set=utf8
[mysql.server]
default-character-set=utf8
[client]
default-character-set=utf8

  1. 重启MySQL服务
    service mysql restart

  2. 如果上面的都修改了还乱码，那剩下问题就一定在connection连接层上
    解决方法是在发送查询前执行一下下面这句（直接写在SQL文件的最前面）：
    SET NAMES 'utf8';
    它相当于下面的三句指令：
    SET character_set_client = utf8;
    SET character_set_results = utf8;
    SET character_set_connection = utf8;

  3. 验证
    执行show variables like 'character%' 跟下面一样说明修改成功

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

6 mysql设置字符集为utf8mb4

  1. 查看字符集编码
    show variables like '%character%';
  2. 修改编码
vim /etc/my.cnf

# 设置后的配置文件如下(删除了原本配置文件中的注释内容)
[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

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

validate_password=OFF

character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

  1. 重启MySQL服务
    service mysql restart

  2. 验证
    结果跟下面一样说明修改成功。

mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> 
mysql> show variables like '%collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

注：character_set_database这一项，可能还是latin1，没改变。原因是在修改字符集前，数据库已经建好了，无法改变了。修改字符集后新建的数据库，这一项都是utf8mb4。
因为5.7的数据库里的database这个已经被官方弃用，如果不嫌麻烦把mysql升级到8.0就可以设置字符集，如果不设置的话，那么5.7.26里老的数据并不会随着你的设置而改变，除非你新建一个库把老的数据库都迁移过来然后手动改掉每个数据库的字符集。

7 linux下关闭mysql的强密码验证

vim /etc/my.cnf
光标移到最下方：shift + G
进入插入模式，同时换行：o
添加一行语句：validate_password=OFF
保存退出。
重启服务：service mysqld restart

8 MySQL导出数据到csv文件

select * from users
into OUTFILE 'F:/Develop/MySQL57/Uploads/users.csv'
CHARACTER SET utf8
FIELDS TERMINATED BY ','  # 以逗号作为字段之间的分隔符，默认是tab字符	
# 字符串以半角双引号包围，字符串本身的双引号用两个双引号表示
OPTIONALLY ENCLOSED BY '"' # 字段括起字符
ESCAPED BY '"' # 转义字符，默认是反斜杠（backslash： ）
LINES TERMINATED BY '
';  # 以
作为数据行之间的分隔符

说明：数据格式化：关于数据格式化的问题，需要给导出的字段传递一些参数，这个参数是根据RFC4180文档设置的，该文档全称Common Format and MIME Type for Comma-Separated Values (CSV) Files，其中详细描述了CSV格式。

9 mysql5.7首次登录的密码问题

9.1 查询初始密码

  1. 为了加强安全性，MySQL5.7为root用户随机生成了一个密码，如果安装的是RPM包，则默认是在/var/log/mysqld.log中。
[root@lenovo .halo]# grep password /var/log/mysqld.log 
2020-06-13T03:45:16.834884Z 1 [Note] A temporary password is generated for root@localhost: FWef!#I0_8Hj

  1. 关闭mysql的强密码验证，方法参考上面的第6条
    如果不想关闭强密码验证，则参考 https://www.cnblogs.com/kingsonfu/p/9803418.html 来操作。

  2. 用该密码登录到服务端后，必须马上修改密码，不然会报错。
    ALTER USER USER() IDENTIFIED BY '123456';

9.2 跳过密码验证

  1. 在配置文件/etc/my.cnf的[mysqld]下，添加skip-grant-tables语句
  2. 重启mysqld：systemctl start mysqld
  3. 登录，修改root密码
mysql
mysql> use mysql;
mysql> update user set authentication_string=password('123456') where user='root'; 
mysql> flush privileges;
  1. 退出后重新登录
    mysql -uroot -p123456
    没尝试过该方法，不知道登录前是否要去my.cnf中去掉skip-grant-tables语句。

通过上述步骤，在登录以后，执行SQL时如果出现这个错误的话：
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
执行下面语句： SET PASSWORD = PASSWORD(‘your password’);

10 mysql的时间类型设置精度

-- MySQL设置时间类型和精度 
`CREATE_TIME` DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6)  
`UPDATE_TIME` DATETIME DEFAULT CURRENT_TIMESTAMP 
-- now()能精确到微秒
select now(6), current_timestamp(3);
+----------------------------+-------------------------+
| now(6)                     | current_timestamp(3)    |
+----------------------------+-------------------------+
| 2020-06-17 00:05:57.449744 | 2020-06-17 00:05:57.449 |
+----------------------------+-------------------------+
1 row in set (0.00 sec)

-- MySQL抽取（extract）微秒的函数
select microsecond('12:00:00.123456');                          -- 123456
select microsecond('1997-12-31 23:59:59.000010');               -- 10
select extract(microsecond from '12:00:00.123456');             -- 123456
select extract(microsecond from '1997-12-31 23:59:59.000010');  -- 10
select date_format('1997-12-31 23:59:59.000010', '%f');         -- 000010

11 mysql设置允许远程登录

grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
flush privileges;

12 mysql语句中条件查询in、like、=的效率

  1. 如果条件字段都是非索引字段,那么效率都差不多,就看结果大小。

  2. 有差别的在于条件字段是索引字段时:

    “=”在索引的情况下都会进行索引扫描,所以效率总是高的。

    “like”当模糊查询为右模糊,比如'abc%'时,扫描索引,高效。当模糊查询含左模糊时,比如'%abc',进行全表扫描,低效。

    “in”的作用等同于or,也是进行索引扫描,高效。

    另外,“in”还可以连接查询结果集,这时往往会和exists做比较。

    a、selct * from t1 where f1 in(select f1 from t2 where t2.fx='x'),其中子查询的where里的条件不受外层查询的影响,这查询一般情况下,自动优化会转成exist语句,也就是效率和exist一样。
    b、select* from t1 where f1 in(select f1 from t2 where t2.fx=t1.fx),其中子查询的where里的条件受外层查询的影响,这类询的效率要看相关条件涉及的字段的索引情况和数据量多少,一般效率不如exists,数据量大时,效果就更加明显。

13 MySQL导出数据到csv文件

mysql
select * from users
into OUTFILE 'F:/Develop/MySQL57/Uploads/users.csv'
CHARACTER SET utf8
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '
';

说明：数据格式化：关于数据格式化的问题，需要给导出的字段传递一些参数，
这个参数是根据RFC4180文档设置的，该文档全称Common Format and MIME Type for Comma-Separated Values (CSV) Files，其中详细描述了CSV格式，其要点包括：

  1. 字段之间以逗号分隔，数据行之间以 分隔；
  2. 字符串以半角双引号包围，字符串本身的双引号用两个双引号表示。

terminated by分隔符：意思是以什么字符作为分隔符
enclosed by字段括起字符
escaped by转义字符
terminated by描述字段的分隔符，默认情况下是tab字符（ ）
enclosed by描述的是字段的括起字符。
escaped by描述的转义字符。默认的是反斜杠（backslash： ）

14 mysql创建数据库并设置字符集编码

create database `mydb` character set utf8 collate utf8_general_ci;

15 mysql limit关键字

select * from table_name limit [index, ] length;

limit后面跟2个参数：

  1. index：索引号，从0开始计算，表示从哪一行开始；
  2. length：长度，表示要查询的记录条数。

16 ubuntu18.04安装mysql的2种方法

16.1 sudo apt-get install mysql-server

16.2 deb包方式安装

# 1 下载mysql 
https://dev.mysql.com/downloads/mysql/5.7.html 选择ubuntu18的64位的tar包：
mysql-server_5.7.34-1ubuntu18.04_amd64.deb-bundle.tar*
# 2 安装
解压后，按如下顺序安装，可能会遇到额外依赖（标红的）。按如下方法安装即可：  
dpkg -i mysql-common_5.7.34-1ubuntu18.04_amd64.deb
dpkg -i libmysqlclient20_5.7.34-1ubuntu18.04_amd64.deb
dpkg -i libmysqlclient-dev_5.7.34-1ubuntu18.04_amd64.deb
dpkg -i libmysqld-dev_5.7.34-1ubuntu18.04_amd64.deb
dpkg -i libmysqlclient20_5.7.34-1ubuntu18.04_amd64.deb
wget http://security.ubuntu.com/ubuntu/pool/universe/m/mecab/libmecab2_0.996-1.2ubuntu1_amd64.deb
dpkg -i libmecab2_0.996-1.2ubuntu1_amd64.deb
dpkg -i mysql-community-client_5.7.34-1ubuntu18.04_amd64.deb
apt install libtinfo5
dpkg -i mysql-client_5.7.34-1ubuntu18.04_amd64.deb
dpkg -i mysql-community-server_5.7.34-1ubuntu18.04_amd64.deb
dpkg -i mysql-server_5.7.34-1ubuntu18.04_amd64.deb

至此安装成功

# 3 修改配置文件
修改/etc/mysql/mysql.cnf

# 4 重启mysql 
service mysql restart  或 systemctl restart mysql

17

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/mediocreWorld/p/15185305.html