win配置MySQL

发表于 | 分类于 | | 阅读次数:

1 遇到的错误

1.1 mysql服务启动不了

查看D:\mysql\3381\logs\mysql.err.log错误日志文件

1
2016-02-03 10:19:54 6448 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_table_stats uses space ID: 1 at filepath: .\mysql\innodb_table_stats.ibd. Cannot open tablespace scrapers/openpasts_records which uses space ID: 1 at filepath: .\scrapers\openpasts_records.ibd

问题出现原因是，由于移动其他盘的空间扩充到系统盘，系统盘扩充不会受到影响，而其他盘的文件索引改变了导致问题出现。

解决方法：
1 停止mysql服务
2 备份数据库，删掉出问题的数据库
3 删掉D:\mysql\3381\data\data文件夹下ibdata1及log文件,删掉D:\mysql\3381\logs中的日志文件
4 重启mysqls服务，导入新库

测试主从。
主从参考：点一下

1.2 执行更新语句报错

1
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因：
查询SQL_MODE信息

1
2
select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

对于GROUP by聚合操作，如果在select中的列没有在GROUP BY中出现，那么sql语句是不合法的，因为a列不在group by从句中。
select id,sum(date) from tt group by date; // 不合法的sql语句
解决方法：

1
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

类型长度

下载：https://dev.mysql.com/downloads/mysql/

int和bigint超限会改变值
char和varchar超限会截取值

win配置mysql

解压mysql-5.7.17-winx64.zip配置环境变量

新增编辑用户变量：
MYSQL_HOME
C:\mysql\mysql-5.7.17-winx64
在编辑系统变量中path变量新增%MYSQL_HOME%\bin;

初始化和配置

1
C:\mysql\mysql-5.7.17-winx64\bin>mysqld  --initialize

增加服务

1
C:\mysql\mysql-5.7.17-winx64\bin>mysqld -install

启动停止服务

1
2
3
4
5
6
7
C:\Users\Administrator>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

C:\Users\Administrator>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

查看版本号

1
2
C:\Users\Administrator>mysql -V
mysql  Ver 14.14 Distrib 5.7.17, for Win64 (x86)

配置my.ini文件

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
26
27
28
29
30
31
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]
#绑定IPv4和3306端口
bind-address = 127.0.0.1
port = 3306
log_bin
# 允许最大连接数
max_connections = 200
basedir = C:\mysql\mysql-5.7.17-winx64
datadir = C:\mysql\mysql-5.7.17-winx64\data
port = 3306
server_id = 15
#设置字符集为utf8  
loose-default-character-set = utf8
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 
[client]  
#设置客户端字符集  
loose-default-character-set = utf8

# 不区分表的大小写
lower_case_table_names = 1
  
[WinMySQLadmin]
Server = C:\mysql\mysql-5.7.17-winx64\bin\mysqld.exe

net start mysql报错

1
2
3
4
5
6
7
8

C:\Users\Administrator>net start mysql
MySQL 服务正在启动 .
MySQL 服务无法启动。

服务没有报告任何错误。

请键入 NET HELPMSG 3534 以获得更多的帮助。

或者遇到1067进程意外终止错误

解决方法：
1、打开任务管理器终止mysqld进程；
2、# 设置默认引擎(可分表)
default-storage-engine=MRG_MyISAM8
3、打开mysql安装目录的data文件夹，删除以下2个文件：
ib_logfile0和ib_logfile1
4、找到在配置MySQL服务器时指定的InfoDB目录删除掉ibdata1
根据my.ini文件中：
#* INNODB Specific options *
innodb_data_home_dir=”D:/“。
5、重新启动MySQL的Service

win下忘记mysql登录密码

1.停止服务再执行跳过权限登录命令

跳过权限命令
mysqld –defaults-file=”C:\mysql\mysql-5.7.17-winx64\my.ini” –console –skip-grant-tables

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
C:\mysql\mysql-5.7.17-winx64\bin>net stop mysql
mysql 服务正在停止.
mysql 服务已成功停止。

C:\mysql\mysql-5.7.17-winx64\bin>mysqld --defaults-file="C:\mysql\mysql-5.7.17-winx64\my.ini" --console --skip-grant-tables

2017-01-04T08:01:40.506477Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is
 deprecated. Please use --explicit_defaults_for_timestamp server option (see doc
umentation for more details).
2017-01-04T08:01:40.506477Z 0 [Note] --secure-file-priv is set to NULL. Operatio
ns related to importing and exporting data are disabled
2017-01-04T08:01:40.507477Z 0 [Note] mysqld (mysqld 5.7.17-log) starting as proc
ess 9168 ...
2017-01-04T08:01:40.513478Z 0 [Warning] No argument was provided to --log-bin, a
nd --log-bin-index was not used; so replication may break when this MySQL server
 acts as a master and has his hostname changed!! Please use '--log-bin=PC-201606
29TTDK-bin' to avoid this problem.
2017-01-04T08:01:40.527478Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows in
terlocked functions
2017-01-04T08:01:40.528479Z 0 [Note] InnoDB: Uses event mutexes
2017-01-04T08:01:40.529479Z 0 [Note] InnoDB: _mm_lfence() and _mm_sfence() are u
sed for memory barrier
2017-01-04T08:01:40.530479Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-01-04T08:01:40.531479Z 0 [Note] InnoDB: Number of pools: 1
2017-01-04T08:01:40.531479Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2017-01-04T08:01:40.534479Z 0 [Note] InnoDB: Initializing buffer pool, total siz
e = 128M, instances = 1, chunk size = 128M
2017-01-04T08:01:40.542479Z 0 [Note] InnoDB: Completed initialization of buffer
pool
2017-01-04T08:01:40.609483Z 0 [Note] InnoDB: Highest supported file format is Ba
rracuda.
2017-01-04T08:01:40.708489Z 0 [Note] InnoDB: Creating shared tablespace for temp
orary tables
2017-01-04T08:01:40.710489Z 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12
MB. Physically writing the file full; Please wait ...
2017-01-04T08:01:40.893499Z 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2017-01-04T08:01:40.897500Z 0 [Note] InnoDB: 96 redo rollback segment(s) found.
96 redo rollback segment(s) are active.
2017-01-04T08:01:40.899500Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are
 active.
2017-01-04T08:01:40.905500Z 0 [Note] InnoDB: 5.7.17 started; log sequence number
 2535781
2017-01-04T08:01:40.908500Z 0 [Note] InnoDB: Loading buffer pool(s) from C:\mysq
l\mysql-5.7.17-winx64\data\ib_buffer_pool
2017-01-04T08:01:40.908500Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-01-04T08:01:40.912500Z 0 [Warning] unknown variable 'loose-default-characte
r-set=utf8'
2017-01-04T08:01:41.122513Z 0 [Warning] Failed to set up SSL because of the foll
owing SSL library error: SSL context is not usable without certificate and priva
te key
2017-01-04T08:01:41.126513Z 0 [Note] Server hostname (bind-address): '127.0.0.1'
; port: 3306
2017-01-04T08:01:41.129513Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2017-01-04T08:01:41.131513Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2017-01-04T08:01:41.169515Z 0 [Note] InnoDB: Buffer pool(s) load completed at 17
0104 16:01:41
2017-01-04T08:01:41.209517Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA
.TABLES;' to get a list of tables using the deprecated partition engine. You may
 use the startup option '--disable-partition-engine-check' to skip this check.
2017-01-04T08:01:41.211518Z 0 [Note] Beginning of list of non-natively partition
ed tables
2017-01-04T08:01:41.242519Z 0 [Note] End of list of non-natively partitioned tab
les
2017-01-04T08:01:41.243519Z 0 [Note] mysqld: ready for connections.
Version: '5.7.17-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

出现以上效果说明已经跳过了权限，如果遇到10065错误，就先停止mysqld.exe进程

2.在新的CMD中执行命令

提示输入密码，直接按回车就好了。

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
C:\mysql\mysql-5.7.17-winx64\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Database changed
mysql> select count(1) from user where user = 'root';
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> UPDATE user SET password = PASSWORD('root') WHERE user = 'root';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> update user u set u.authentication_string = PASSWORD('root') WHERE u.user
 = 'root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql> quit;
Bye

C:\mysql\mysql-5.7.17-winx64\bin>net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。

C:\mysql\mysql-5.7.17-winx64\bin>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17-log

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

sql：update user u set u.authentication_string = PASSWORD(‘root’) WHERE u.user = ‘root’;
不同版本的mysql密码字段不一样，越过权限需要查看user表中字段信息

分表

参考地址：
http://www.cnblogs.com/miketwais/articles/mysql_partition.html
http://blog.csdn.net/shandalue/article/details/52035040

哪些引擎可以分表？