MySQL基础

介绍

技术 特性 适用场景 说明
MySQL 关系型数据库存储 - 官网
- MySQL5.5下载
- MySQL5.6下载
- MySQL5.7下载
- MySQL8.0下载

安装

CentOS 安装 MySQL 5.7

1
2
# 检查系统是否装有mysql
$ rpm -qa | grep mysql

CentOS 安装 MySQL 8.0

1
2
# 检查系统是否装有mysql
$ rpm -qa | grep mysql

Mac 安装 MySQL8.0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 安装 MySQL8.0
$ brew install mysql

# mysql默认使用caching_sha2_password作为默认的身份验证插件,
# 而不再是mysql_native_password,但是客户端暂时不支持这个插件。
$ vim /usr/local/etc/my.cnf
##### 增加如下内容,然后重启mysql #####
default_authentication_plugin=mysql_native_password

# 启动服务
$ brew services run mysql

# 安全配置向导
$ mysql_secure_installation

配置文件

文件 位置
my.cnf /usr/local/etc/my.cnf
错误日志 /usr/local/var/mysql/MacBookName.local.err

卸载 MySQL8.0

1
2
3
$ brew remove mysql
$ brew cleanup
$ rm -rf /usr/local/var/mysql/

Mac 安装 MySQL5.7

1
2
3
4
5
6
7
8
9
# 安装
$ brew install mysql@5.7

# 启动
$ brew services start mysql@5.7

# 更改密码
$ cd /usr/local/opt/mysql@5.7/bin
$ ./mysqladmin -u root password "123456"

运维基础

Mac安装MyCLI

1
$ brew update && brew install mycli

修改mysql的root密码

停止MySQL

【系统偏好设置】-> MySQL -> 停止MySQL。

跨过权限验证

1
2
3
$ cd /usr/local/mysql/bin
$ sudo su
$ ./mysqld_safe --skip-grant-tables &

若命令执行停住了,此时已经执行完了,直接回车。

修改密码

方法1

1
2
3
4
$ cd /usr/local/mysql/bin
$ ./mysql -u root
> use mysql;
> update user set authentication_string='123456' where User='root';

方法2

1
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

方法3

1
2
mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
mysql> FLUSH PRIVILEGES;

导出数据

1
$ mysqldump -h 127.0.0.1 -u root -ppassword databaseName > ~/Documents/Workspaces/mysqlback/databaseName.sql

导入数据

1
$ mysql -h 127.0.0.1 -u root -p databaseName < ~/Documents/Workspaces/mysqlback/databaseName.sql

允许Root远程访问(MySQL<8.0)

连接MySQL

1
$ mysql -u root -p123456

切换数据库

1
use mysql;

授权

1
2
3
4
# 语法
GRANT ALL PRIVILEGES ON 数据库名.* TO '用户名'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

使修改生效

1
FLUSH PRIVILEGES;

MySQL8.0设置远程访问

1
2
3
4
5
# (1)/usr/local/etc/my.cnf 中 取消如下:
bind-address = 127.0.0.1

# (2)~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
<string>--bind-address=127.0.0.1</string>

配置默认编码为utf8

查看默认编码

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

修改mysql配置文件

1
2
3
4
5
6
7
[client]
default-character-set=utf8

[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci

重启MySQL

1
$ service mysql restart

检查字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show variables like '%char%';
+--------------------------+-------------------------------------+
| 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 | /software/mysql/run/share/charsets/ |
+--------------------------+-------------------------------------+

CentOS下卸载MySQL

1
2
3
4
5
6
7
# 查看有哪些MySQL相关的包
$ rpm -qa |grep mysql
# 卸载
$ yum remove 包名
# 查找配置文件
$ find / -name mysql
$ rm -rf 配置文件路径

生成需要处理掉的MySQL连接的语句临时文件

1
2
mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root';
mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';

查看进程列表

1
mysql> show processlist;

常用语句

产生删除数据库中所有表的语句

1
mysql> select concat("DROP TABLE IF EXISTS ", table_name, ";") from information_schema.tables where table_schema="Your_database_name";

常见错误

Caused by: java.sql.SQLException: Incorrect string value

问题描述

1
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x80\xE3\x80...' for column 'show_content' at row 1

解决办法

修改mysql数据库的编码为uft8mb4

1
ALTER SCHEMA TableName DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci ;

修改数据表的编码为utf8mb4

1
ALTER TABLE TableName CONVERT TO CHARACTER SET utf8mb4;

修改连接数据库的连接代码

1
url: jdbc:mysql://127.0.0.1:3306/crawler?useUnicode=true&character_set_server=utf8mb4&useSSL=false

MySQL8 show database错误

问题描述

使用root用户登录数据库,使用show databases; 命令时,发现如下错误:

1
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

解决办法

退出数据库,使用如下命令

1
$ mysql_upgrade -u root -p;

运行成功后,再次进入数据库,就可以正常使用了。

MySQL8 连接报错

问题分析

mysql默认使用caching_sha2_password作为默认的身份验证插件,而不再是mysql_native_password,但是客户端暂时不支持这个插件。

解决办法

1
$ vi /usr/local/etc/my.cnf

增加如下内容,然后重启mysql。

1
default_authentication_plugin=mysql_native_password

server has gone away 错误

问题描述

在执行MySQL数据批量恢复时,报[Err] 2006 - MySQL server has gone away。

问题分析

需要重新设置初始的max_allowed_packet参数,默认4M。

解决办法

修改max_allowed_packet

打开Mysql安装目录下的my.ini,如C:Server 5.6.ini,修改

1
max_allowed_packet=100M

具体根据实际情况酌情设置。

重启MySQL

步骤略。

Windows下安装MYSQL ODBC Error 1918错误

问题描述

默认安装MySQL ODBC,会遇到错误,错误提示: Error 1918. Error installing ODBC driver MySQL ODBC5.3 ANSI Driver,ODBC error 13:无法加载 MySQL ODBC5.3 ANSI Driver ODBC驱动程序的安装例程, 因为存在系统错误代码 126:找不到指定的模块。

问题分析

缺乏VC2013的库。

解决办法

需安装Visual C++ Redistributable Packages for Visual Studio 2013。 注意,安装Microsoft Visual C++ 2010 Redistributable Package不起作用。

Windows每天出现taskeng.exe进程

问题描述

Windows每天都会出现taskeng.exe进程。

问题分析

这不是木马、病毒,是微软提供的任务计划程序引擎。 如果不需要的话,可以禁止。

解决办法

【控制面板】->【管理工具】->【计划任务程序】,点击左边的【计划任务程序库】,双击【MySQL】,将MainfestUpdate禁用。

mysql 8.0 Client does not support authentication protocol requested by server; 错误

1
alter user 'root'@localhost IDENTIFIED WITH mysql_native_password by '123456';

Failed to create schema directory 'myapp' (errno: 2 - No such file or directory)

1
$ sudo chown -R mysql:mysql /usr/local/mysql/data

事物锁等待超时

问题症状

1
Lock wait timeout exceeded; try restarting transaction

解决办法

查看数据库当前的进程

1
mysql> show  processlist;

查看当前的事务

1
2
3
4
5
6
7
8
#当前运行的所有事务
mysql> SELECT * FROM information_schema.INNODB_TRX;

#当前出现的锁
mysql> SELECT * FROM information_schema.INNODB_LOCKs;

#锁等待的对应关系
mysql> SELECT * FROM information_schema.INNODB_LOCK_waits;

批量删除事务表中的事务

1
2
mysql>  select concat('KILL ',id,';') from information_schema.processlist where user='mysqlUsername';
18 rows in set (0.00 sec)

修改配置文件

1
innodb_lock_wait_timeout=500

重启MySQL。

Error : Lost connection to MySQL server during query

修改MySQL配置文件

1
max_allowed_packet = 500M

重启。

Caused by: java.sql.SQLException: Incorrect string value

问题描述

1
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x80\xE3\x80...' for column 'show_content' at row 1

解决办法

修改mysql数据库的编码为uft8mb4

1
ALTER SCHEMA TableName DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci ;

修改数据表的编码为utf8mb4

1
ALTER TABLE TableName CONVERT TO CHARACTER SET utf8mb4;

修改连接数据库的连接代码

1
url: jdbc:mysql://127.0.0.1:3306/crawler?useUnicode=true&character_set_server=utf8mb4&useSSL=false

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'

问题描述

服务器突然断电,启动后mysql无法启动,报错:

1
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'

解决办法

1
2
3
4
5
$ cd /tmp
$ rm -rf mysql.sock
$ rm -rf mysql.sock.lock
$ cd /usr/local/mysql/
$ ./support-files/mysql.server start

mysql导入报错 [Err] 1273 - Unknown collation: 'utf8mb4_0900_ai_ci'

问题分析

MySQL8 往低版本的MySQL导入,不兼容。

解决办法

字符:utf8mb4_0900_ai_ci 替换为:utf8_general_ci 。

坚持原创技术分享,您的支持将鼓励我继续创作!
0%