mysql
文章平均质量分 82
布丁很胖
优于别人,并不高贵,真正的高贵应该是优于过去的自己
展开
-
msyql 密码忘记 5.6.23
问题描述:登录数据库,无法登录[root@proxy02 mysql]# ./bin/mysql -uroot -proot -S /data/soft/mysql/tmp/mysql.sockWarning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Acc...原创 2018-10-11 15:49:42 · 230 阅读 · 0 评论 -
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
错误现象delete from PaidInRecord pr WHERE pr.packageNumberId= 5661 and pr.contractInfoId= 3235 and pr.repaymentApplyId=179389 and pr.isMatchAll=2;ERROR 1064 (42000): You have an error in your SQL synta...原创 2018-10-24 18:01:40 · 2933 阅读 · 0 评论 -
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> show variables like 'innodb_lock_wait%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 5 ...原创 2018-10-24 18:31:07 · 203 阅读 · 0 评论 -
mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."n
mysqldump报错如下:mysqldump: [Warning] Using a password on the command line interface can be insecure.mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."...原创 2018-11-12 17:26:50 · 6388 阅读 · 0 评论 -
mysql更改目录后,启动报错
现象1[ERROR] InnoDB: auto-extending data file /export/servers/mysql56/data/ibdata1 is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 65536 pages, max 0 (re...转载 2018-11-19 19:24:30 · 1390 阅读 · 0 评论 -
[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
报错如下181226 17:49:18 mysqld_safe Starting mysqld daemon with databases from /export/servers/mysql56/data2018-12-26 17:49:18 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please us...原创 2018-12-26 18:05:17 · 2187 阅读 · 0 评论 -
[Err] 1709 - Index column size too large. The maximum column size is 767 bytes.
描述[Err] 1709 - Index column size too large. The maximum column size is 767 bytes.[Err] CREATE INDEX `groups_1` ON `groups` (`name`); 其实这个“ERROR 1071 (42000): Specified key was too long; max k...转载 2018-12-27 18:19:05 · 5532 阅读 · 0 评论 -
2019-01-07 16:35:40 18616 [Warning] Slave SQL: Could not execute Query event. Detailed error: ;, Err
mysql版本 5.6.16在备库日志出现如下错误 There seems to be a relation with slave-skip-errors = allIf i start the server WITH slave-skip-errors = all these messages will appear.If i leave it out of my c...转载 2019-01-07 17:00:04 · 882 阅读 · 0 评论 -
mysql审计开启--两种方法
参考链接https://blog.csdn.net/qq_35267530/article/details/76242215 摘要:第一种方法:安装插件的方式 这里使用的是macfee的mysql audit插件,虽然日志信息比较大,对性能影响大,但是如果想要开启审计,那也应该忍受了。介绍几个参考地址: wiki首页:https://github.第一种方法:安装插件的方式...转载 2019-01-14 10:32:46 · 2781 阅读 · 0 评论 -
ERROR 1366 (HY000): Incorrect string value: '\xA6' for column 'jy_message' at row 1
这个问题是字符集导致的 查看数据库字符集mysql> show variables like 'character%';+--------------------------+-----------------------------------------+| Variable_name | Value ...原创 2018-10-17 15:09:19 · 314 阅读 · 0 评论 -
Got an error reading communication packets
2018-04-27T15:30:46.946313+08:00 417211 [Note] Aborted connection 417211 to db: 'dynetmanage30' user: 'root' host: '192.168.100.27' (Got an error reading communication packets)2018-04-27T19:25:53.40...原创 2018-10-22 11:20:03 · 5886 阅读 · 0 评论 -
利用binlog进行日志恢复
过滤查看记录[root@localhost]# /usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001 | more全部执行[root@localhost]# /usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001 | /usr/b...原创 2018-10-22 11:19:27 · 811 阅读 · 0 评论 -
2995 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
问题描述由于误操作,将mysql的mysql库删除,导致数据库启动异常,mysql启动报错,报错如下2018-10-11 16:30:11 2995 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist181011 16:30:11 mysqld_safe my...原创 2018-10-11 17:04:35 · 1333 阅读 · 0 评论 -
mysql 大小写敏感
mysql大小写敏感分表名大小写敏感和表字段大小写敏感 1、表名大小写敏感mysql> show variables like 'lower%';+------------------------+-------+| Variable_name | Value |+------------------------+-------+| lower_...原创 2018-10-22 11:12:33 · 220 阅读 · 0 评论 -
mysql参数之innodb_buffer_pool_size大小设置
mysql> show global variables like 'innodb_buffer_pool_size';+-------------------------+-----------+| Variable_name | Value |+-------------------------+-----------+| innodb_buf...转载 2018-10-22 11:13:53 · 3758 阅读 · 0 评论 -
msyql 用户和权限操作
1、查看有哪些用户mysql> use mysql;Database changedmysql> select host,user,password from user; +----------------------+---------------+-------------------------------------------+| host ...原创 2018-10-22 11:14:33 · 153 阅读 · 0 评论 -
mysql的binlog清除
1、binlog设置相关参数expire_logs_day 设置binlog老化日期 触发时机是binlog发生切换: binlog大小超过max_binlog_size 手动执行flush logs 重新启动时(mysql将会new一个新文件用于记录binlog) 2、参数在线更改该参数可以在线修改 mysql> show va...原创 2018-10-22 11:16:30 · 150 阅读 · 0 评论 -
ERROR 1040 (HY000): Too many connections
mysql连接报错查看端口连接数 tcp 0 0 aaaa:53069 192.168.12.13:mysql TIME_WAIT root 0出现很多 TIME_WAIT 1、解决 TIME_WAIT vi /etc/sysctl.conf 编辑文件,加入以下内容...原创 2018-10-22 11:17:20 · 1000 阅读 · 0 评论 -
Last_IO_Error: Got fatal error 1236 from master when reading data from binary lo
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' 这是典型的mysql主从不同步问题,由于mysql主库binlog丢失,导致mysql从无法同步主...原创 2018-10-22 11:17:58 · 1525 阅读 · 0 评论 -
Mysql查看执行计划-explain
Mysql查看执行计划-explain参考链接 https://www.2cto.com/database/201202/120118.html Mysql查看执行计划 一.语法 explain <sql语句> 例如: explain select * from t3 where id=3952602; 二.explain输出解释...转载 2018-10-22 11:18:58 · 135 阅读 · 0 评论 -
MySQL——通过EXPLAIN分析SQL的执行计划
在MySQL中,我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。下面分别对EXPLAIN命令结果的每一列进行说明:.select_type:表示SELECT的类型,常见的取值有:类型 说明 SIMPLE 简单表,不使用表连接或子查询 PRIMARY 主查询,即外层的查询 ...转载 2019-02-14 17:31:25 · 359 阅读 · 1 评论