备忘录 | MySQL最最最常用操作

MySql操作笔记

基本操作

本地登录
mysql -uusername -ppasswd

//查看当前连接所有的数据库
show databases;
//进入数据库
use estore;
//显示本数据库中所有的表
show tables;

安装rpm（mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar）

官方下载网站：https://dev.mysql.com/downloads/mysql/

image.png

download

右键 No thanks，保存下载链接

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.25-1.el7.x86_64.rpm-bundle.tar

查看是否安装：
rpm -qa|grep mysql

卸载：
rpm -e xxx --nodeps

//使用rpm -ivh命令进行安装
mysql-community-common-5.7.9-1.el7.x86_64.rpm
mysql-community-libs-5.7.9-1.el7.x86_64.rpm --（依赖于common）
mysql-community-client-5.7.9-1.el7.x86_64.rpm --（依赖于libs）
mysql-community-server-5.7.9-1.el7.x86_64.rpm --（依赖于client、common）

初始化数据库,初始密码在/var/log/mysqld.log

service mysqld start

安装常见问题

新安装的MySQL启动不起来

  cd /var/lib/mysql
  chown mysql *；
  chgrp mysql *；
  chmod ug+rwx *

安装失败：

  warning: mysql-community-server-5.7.17-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
  error: Failed dependencies:
      libnuma.so.1()(64bit) is needed by mysql-community-server-5.7.17-1.el6.x86_64
      libnuma.so.1(libnuma_1.1)(64bit) is needed by mysql-community-server-5.7.17-1.el6.x86_64
      libnuma.so.1(libnuma_1.2)(64bit) is needed by mysql-community-server-5.7.17-1.el6.x86_64

解决方法：yum install numactl

centos 7 安装失败
报错信息：mariadb-libs is obsoleted by mysql-community-libs-5.7.9-1.el7.x86_64
分析：Centos默认安装mariadb-libs，卸载即可正常安装
```
     rpm -e $mariadb --nodeps 
```

首次安装设置root密码

mysql –uroot -p  使用默认密码登录
set password =password('123.'); 修改密码

可以在mysql的log日志中查看默认密码(vim /var/log/mysqld.log 需要首先启动服务 service mysqld start)

默认目录

usr/bin/mysql 是指：mysql的运行路径
var/lib/mysql 是指：mysql数据库文件的存放路径
usr/lib/mysql 是指：mysql的安装路径

允许远程登录

授权用户root使用密码passwd从任意主机连接到mysql服务器：

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
 flush privileges;

授权用户root使用密码passwd从指定ip为10.1.0.45的主机连接到mysql服务器：
代码如下:

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.1.0.45' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
 flush privileges;

中文乱码问题

show variable like '%character%'  查看字符编码

修改配置文件
[client]
default-character-set = utf8
[mysqld]
character-set-server = utf8

代码连接
jdbc:mysql://localhost:3306/crawler?useUnicode=true&characterEncoding=utf-8&useSSL=false

命令行读取用户名、密码

配置文件my.cnf添加以下配置

[client]
host=localhost
user=root
password='yyy666'

数据库导出

1、导出整个数据库(包括数据）
mysqldump -uusername -p estore > estore.sql
2、导出数据库结构（只有建表语句，不含数据）
mysqldump -uusername -p -d estore > estore.sql
3、导出数据库中的某张数据表（包含数据）
mysqldump -uusername -p estore product > product.sql
4、导出数据库中的某张数据表的表结构（只有建表语句，不含数据）
mysqldump -uusername -p -d estore product > product.sql

数据库导入

# Linux直接操作
mysql -uusername -p estore < estore.sql
# 进入mysql命令行，执行mysql命令
source estore.sql

# 获取特定表结构
sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `test`/!d;q' database-20180812020000.sql > test.sql 

grep 'INSERT INTO `test`'database-20180812020000.sql > test.sql

异常处理

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.tableName.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

配置文件中添加配置

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

设置密码复杂度

mysql> select @@validate_password_policy;
mysql> set global validate_password_policy=0;

创建用户、授权

创建用户

# 创建用户并允许localhost, 127.0.0.1访问
create user 'test'@'localhost' identified by '123456';
# 创建用户并允许外网访问
create user 'test'@'%' identified by '123456';  
# 生效
flush privileges;

对数据库授权

# 授权用户对testdb的所有权限，本地访问
grant all privileges on `testdb`.* to 'test'@'localhost' identified by '123456';
# 授权用户对testdb的所有权限，外网访问
grant all privileges on `testdb`.* to 'test'@'%' identified by '123456';  
# 生效
flush privileges;

修改默认的数据文件目录

# 设置/data/下mysql文件夹的属主和权限
chown -R mysql:mysql /data/mysql

查看连接数

# 当前连接数
show status like 'Threads%';
# IP连接数
select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;
# 查看连接状态
 show full processlist;
# 查看最大连接数
show variables like '%max_connections%';
# 修改最大连接数
set GLOBAL max_connections = 200;

# 配置文件配置，重启不会失效
max_connections=1000

连接超时设置

# 查看当前超时设置
show variables like "%timeout%";
# 查看wait_timeout（全局变量）
show global variables like 'wait_timeout';
# 临时更改
set global wait_timeout=10;

配置文件配置

[mysqld]
wait_timeout=10

表操作命令

# 查看表结构
desc table_name 

# 修改字段的数据类型
ALTER TABLE table_name MODIFY COLUMN col_name VARCHAR(50);

# 字符替换
update tb_room set room_info = replace(room_info,'-','');

日志查看

在/etc/my.cnf文件中【mysqld】下加上：

 server-id = 1 （在整个Mysql集群中保证唯一）
 log-bin = binlog 
 log-bin-index = binlog.index


# 查看二进制文件是否开启
SHOW VARIABLES LIKE'log_bin'
# 查看所有二进制日志文件
SHOW BINARY LOGS;
# 查看当前二进制日志文件的名称
SHOW MASTER STATUS;

慢查询

# 查看是否开启
show variables like 'slow_query%';
# 查询超过多少秒才记录
show variables like 'long_query_time';

# 将slow_query_log 全局变量设置为“ON”状态
set global slow_query_log='ON';

# 设置慢查询日志存放的位置
set global slow_query_log_file='/data/logs/mysql/slow.log';

# 查询超过1秒就记录
set global long_query_time=5;

配置文件方式

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

查看二进制文件

# --no-dafaults
mysqlbinlog --no-defaults --start-datetime='2018-08-18 16:21:00' --stop-datetime='2018-08-18 16:35:00' -d 数据库名 mysql-bin.000003

mysqlbinlog --no-defaults --start-position=1072696115 --stop-position=1072696117 -d 数据库名 mysql-bin.000003

# --base64-output=decode-rows -v  解码处理
mysqlbinlog --no-defaults --start-position=1072696115 --stop-position=1072696117 -d 数据库名 --base64-output=decode-rows -v mysql-bin.000003 

# 个人Mac主机查看binlog
/usr/local/mysql-5.7.13-osx10.11-x86_64/bin/mysqlbinlog --start-datetime='2018-08-18 16:21:00' --stop-datetime='2018-08-18 16:23:00' -d 数据库名 ~/Downloads/mysql-bin/mysql-bin.000002 > ~/Downloads/mysql-bin/my.sql

# 数据恢复，起止时间点
mysqlbinlog --no-defaults --start-datetime='2018-08-12 02:05:00' --stop-datetime='2018-08-18 16:22:13' -d 数据库名 mysql-bin.000002 | mysql -uroot -p