标签 mysql 下的文章

MySQL报错This function has none of DETERMINISTIC解决

创建存储过程出错log

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

原因:开启了bin-log就须指函数是否是

1 DETERMINISTIC 不确定的

2 NO SQL 没有SQl语句,当然也不会修改数据

3 READS SQL DATA 只是读取数据,当然也不会修改数据

4 MODIFIES SQL DATA 要修改数据

5 CONTAINS SQL 包含了SQL语句

其中在function里面,只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。
如果开启了 bin-log, 我们就必须为function指定一个参数。
临时设置:

mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name          | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF  |
+---------------------------------+-------+
mysql> set global log_bin_trust_function_creators=1;
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name          | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON  |

修改配置文件my.cnf在mysqld部分增加

log_bin_trust_function_creators=1
/etc/init.d/mysql restart

重启MySQL服务。

MySQL命令行参数整理

一、MySQL命令行参数:

Usage: mysql [OPTIONS] [database]   //命令方式  
 -?, --help          //显示帮助信息并退出  
 -I, --help          //显示帮助信息并退出  
 --auto-rehash       //自动补全功能,就像linux里面,按Tab键出提示差不多,下面有例子  
  
 -A, --no-auto-rehash  //默认状态是没有自动补全功能的。-A就是不要自动补全功能  
 -B, --batch         //ysql不使用历史文件,禁用交互  
 (Enables --silent)  
 --character-sets-dir=name   //字体集的安装目录                      
 --default-character-set=name    //设置数据库的默认字符集  
 -C, --compress      //在客户端和服务器端传递信息时使用压缩  
 -#, --debug[=#]     //bug调用功能  
 -D, --database=name //使用哪个数据库  
 --delimiter=name    //mysql默认命令结束符是分号,下面有例子  
 -e, --execute=name  //执行mysql的sql语句  常用于配合shell脚本
 -E, --vertical      //垂直打印查询输出  
 -f, --force         //如果有错误跳过去,继续执行下面的  
 -G, --named-commands  
 /*Enable named commands. Named commands mean this program's 
 internal commands; see mysql> help . When enabled, the 
 named commands can be used from any line of the query, 
 otherwise only from the first line, before an enter. 
 Disable with --disable-named-commands. This option is 
 disabled by default.*/  
 -g, --no-named-commands  
 /*Named commands are disabled. Use \* form only, or use 
 named commands only in the beginning of a line ending 
 with a semicolon (;) Since version 10.9 the client now 
 starts with this option ENABLED by default! Disable with 
 '-G'. Long format commands still work from the first 
 line. WARNING: option deprecated; use 
 --disable-named-commands instead.*/  
 -i, --ignore-spaces //忽视函数名后面的空格.  
 --local-infile      //启动/禁用 LOAD DATA LOCAL INFILE.  
 -b, --no-beep       //sql错误时,禁止嘟的一声  
 -h, --host=name     //设置连接的服务器名或者Ip  
 -H, --html          //以html的方式输出  
 -X, --xml           //以xml的方式输出  
 --line-numbers      //显示错误的行号  
 -L, --skip-line-numbers  //忽略错误的行号  
 -n, --unbuffered    //每执行一次sql后,刷新缓存  
 --column-names      //查寻时显示列信息,默认是加上的  
 -N, --skip-column-names  //不显示列信息  
 -O, --set-variable=name  //设置变量用法是--set-variable=var_name=var_value  
 --sigint-ignore     //忽视SIGINT符号(登录退出时Control-C的结果)  
 -o, --one-database  //忽视除了为命令行中命名的默认数据库的语句。可以帮跳过日志中的其它数据库的更新。  
 --pager[=name]      //使用分页器来显示查询输出,这个要在linux可以用more,less等。  
 --no-pager          //不使用分页器来显示查询输出。  
 -p, --password[=name] //输入密码  
 -P, --port=#        //设置端口  
 --prompt=name       //设置mysql提示符  
 --protocol=name     //使用什么协议  
 -q, --quick         //不缓存查询的结果,顺序打印每一行。如果输出被挂起,服务器会慢下来,mysql不使用历史文件。  
 -r, --raw           //写列的值而不转义转换。通常结合--batch选项使用。  
 --reconnect         //如果与服务器之间的连接断开,自动尝试重新连接。禁止重新连接,使用--disable-reconnect。  
 -s, --silent        //一行一行输出,中间有tab分隔  
 -S, --socket=name   //连接服务器的sockey文件  
 --ssl               //激活ssl连接,不激活--skip-ssl  
 --ssl-ca=name       //CA证书  
 --ssl-capath=name   //CA路径  
 --ssl-cert=name     //X509 证书  
 --ssl-cipher=name   //SSL cipher to use (implies --ssl).  
 --ssl-key=name      //X509 密钥名  
 --ssl-verify-server-cert //连接时审核服务器的证书  
 -t, --table         //以表格的形势输出  
 --tee=name          //将输出拷贝添加到给定的文件中,禁时用--disable-tee  
 --no-tee            //根--disable-tee功能一样  
 -u, --user=name     //用户名  
 -U, --safe-updates  //Only allow UPDATE and DELETE that uses keys.  
 -U, --i-am-a-dummy  //Synonym for option --safe-updates, -U.  
 -v, --verbose       //输出mysql执行的语句  
 -V, --version       //版本信息  
 -w, --wait          //服务器down后,等待到重起的时间  
 --connect_timeout=# //连接前要等待的时间  
 --max_allowed_packet=# //服务器接收/发送包的最大长度  
 --net_buffer_length=# //TCP / IP和套接字通信缓冲区大小。  
 --select_limit=#    //使用--safe-updates时SELECT语句的自动限制  
 --max_join_size=#   //使用--safe-updates时联接中的行的自动限制  
 --secure-auth       //拒绝用(pre-4.1.1)的方式连接到数据库  
 --server-arg=name   //Send embedded server this as a parameter.  
 --show-warnings     //显示警告  

MySQL的max_allowed_packet参数说明

max_allowed_packet 定义的是所允许的单条sql语句的大小。
引用官方的说法: http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet

You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

Property    Value
Command-Line Format    --max-allowed-packet=#
System Variable    max_allowed_packet
Scope    Global, Session
Dynamic    Yes
Type    integer
Default Value    4194304
Minimum Value    1024
Maximum Value    1073741824

解释:
该值的类型为integer,允许的最大值为1G (理论上4个字节的表达能力的上限是4G,也或许协议实现上硬编码了吧),修改
该变量的默认值为4MB,一般来讲是够的,如果存储大的BLOB列,可能不够,需要修改该配置
该值总是1KB的整数倍,最小值为1KB;修改的值应该是1024的整数倍,如果不是整数倍,则会按照小于该值的最接近的那个1024的整数倍进行截断处理,并产生1个warning;(注意: 如果设置的值小于1024,则自动调整为1024)如下:

将max_allowed_packet 设置为比 net_buffer_length 小的意义不大,所以,这里同样会出现一个warning,只是不会强制将max_allowed_packet修改为大于等于net_buffer_length的
比较专业的设置该值的方法为:
set global max_allowed_packet=102410241024;
session的max_allowed_packet是不允许修改的,修改了全局配置对当前session也不会生效的,只有重新连接才能看到变化,一般设置为1024M即可;

MySQL创建函数-存储过程报“ERROR 1418 ”错误 解决方法

MySQL创建函数或存储过程的时候报error 1418错误:

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

是因为log_bin_trust_function_creators参数在起作用:
当二进制日志启用后,这个变量就会启用。它控制是否可以信任存储函数创建者,不会创建写入二进制日志引起不安全事件的存储函数。
如果设置为0(默认值),用户不得创建或修改存储函数,除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1,MySQL不会对创建存储函数实施这些限制,此变量也适用于触发器的创建。

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql>  show variables like '%log_bin_trust_function_creators%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

如果数据库没有使用主从复制,那么就可以将参数log_bin_trust_function_creators设置为1。

mysql> set global log_bin_trust_function_creators=1;

这个动态设置的方式会在服务重启后失效,所以我们还必须在my.cnf中设置,加上

log_bin_trust_function_creators=1

这样就会永久生效。
注意:如果开启了主从复制,同时又打开了log_bin_trust_function_creators参数,可以创建函数、存储过程,可能会引起主从复制故障·

MySQL忽略区分大小写

在MySQL中,数据库对应数据目录中的目录。数据库中的每个表至少对应数据库目录中的一个文件(也可能是多个,取决于存储引擎)。因此,所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。

在大多数Unix中数据库名和表名对大小写敏感,而在Windows中对大小写不敏感。一个显著的例外情况是Mac OS X,它基于Unix但使用默认文件系统类型(HFS+),对大小写不敏感。然而,Mac OS X也支持UFS卷,该卷对大小写敏感,就像Unix一样。

变量lower_case_file_system说明是否数据目录所在的文件系统对文件名的大小写敏感。

ON说明对文件名的大小写不敏感,OFF表示敏感。

一般线上不建议忽略大小写,仅在一些特殊场景下适用,

大小写区分规则

    linux下:
    数据库名与表名是严格区分大小写的;
    表的别名是严格区分大小写的;
    列名与列的别名在所有的情况下均是忽略大小写的;
    变量名也是严格区分大小写的;
    windows下:
    都不区分大小写
    Mac OS下(非UFS卷):
    都不区分大小写

MySQL默认是区分大小写的:

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.01 sec)
lower_case_table_names = 0时,mysql会根据表名直接操作,大小写敏感。 
lower_case_table_names = 1时,mysql会先把表名转为小写,再执行操作。 

由大小写敏感转换为不敏感方法:

    如果原来所建立库及表都是对大小写敏感的,想要转换为对大小写不敏感,主要需要进行如下3步:
    1.将数据库数据通过mysqldump导出。
    2.在my.cnf中更改lower_case_tables_name = 1,并重启mysql数据库。
    3.将导出的数据导入mysql数据库。

mysqldump导出所有库,修改my.cnf, 在[mysqld]下加入一行:

lower_case_table_names=1;
/etc/init.d/mysql restart

重新查询

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.01 sec)

source进刚才备份的数据;

Docker下面安装MySQL5.7.21

安装MySQL5.7.21镜像

docker pull mysql:5.7.21

查看安装的镜像docker images

[root@cops163 ~]# docker images | grep mysql
docker.io/mysql         5.7.21              5195076672a7        6 days ago          371 MB

创建数据目录

mkdir /data/mysql_data

创建配置文件目录

mkdir /data/conf

创建并运行镜像

docker run -p 3306:3306 --name mysql001 --restart=always -v /data/conf:/etc/my.cnf.d -v /data/mysql_data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.21 --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci

参数说明如下:

-p 3306:3306  #将容器的3306端口映射到主机的3306端口
--name mysql001  #容器的名字为mysql001
--restart=always  #在宿主机重启后或者Docker服务重启后自动启动容器,
no #不重启(不带restart参数时,默认不重启)
on-failure  #退出状态非0时重启
always  #始终重启
-v /data/conf:/etc/my.cnf.d  #将/data/conf目录映射到/etc/my.cnf.d下面 
-v /data/mysql_data:/var/lib/mysql  #将/data/mysql目录映射到/var/lib/mysql下面
-e MYSQL_ROOT_PASSWORD=123456 #设置root初始密码为123456
 -d mysql:5.7.21  #后台运行容器,并返回容器ID
--character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci   #设置编码为utf8mb4

进入Docker MySQL控制台

docker exec -it mysql001 mysql -uroot -p
mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using  EditLine wrapper

Connection id:          2
Current database:       langold_enrolment
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.21 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 47 min 16 sec

Threads: 2  Questions: 1785612  Slow queries: 0  Opens: 246  Flush tables: 1  Open tables: 135  Queries per second avg: 629.623
--------------
mysql> 

查看docker容器的log

docker logs -f mysql001

导入SQL文件到MySQL中

docker exec -i mysql001 mysql -uroot -p123456 langold_enrolment < /tmp/langold_enrolment.sql

导出SQL文件

docker exec -it mysql001 mysqldump -uroot -p123456 aaa > /tmp/aaa.sql

更多参考Docker官方文档:
https://hub.docker.com/r/mysql/mysql-server/

MySQL中grant授权和撤销权限revoke的语法说明

MySQL的权限系统围绕着两个概念:
认证->确定用户是否允许连接数据库服务器
授权->确定用户是否拥有足够的权限执行查询请求等。
如果认证不成功的话,哪么授权肯定是无法进行的。
revoke 跟 grant 的语法差不多,只需要把关键字 “to” 换成 “from”
GRANT和REVOKE管理的权限

ALL PRIVILEGES
影响除WITH GRANT OPTION之外的所有权限

ALTER
影响ALTER TABLE命令的使用

ALTER ROUTINE
影响创建存储例程的能力

CREATE
影响CREATE TABLE命令的使用

CREATE ROUTINE
影响更改和弃用存储例程的能力

CREATE TEMPORARY TABLES
影响CREATE TEMPORARY TABLE命令的使用

CREATE USER
影响创建、弃用;重命名和撤销用户权限的能力

CREATE VIEW
影响CREATE VIEW命令的使用

DELETE
影响DELETE命令的使用

DROP
影响DROP TABLE命令的使用

EXECUTE
影响用户运行存储过程的能力

EVENT
影响执行事件的能力(从MySQL5.1.6开始)

FILE
影响SELECT INTO OUTFILE和LOAD DATA INFILE的使用

GRANT OPTION
影响用户委派权限的能力

INDEX
影响CREATE INDEX和DROP INDEX命令的使用

INSERT
影响INSERT命令的使用

LOCK TABLES
影响LOCK TABLES命令的使用

PROCESS
影响SHOW PROCESSLIST命令的使用

REFERENCES
未来MySQL特性的占位符

RELOAD
影响FLUSH命令集的使用

REPLICATION CLIENT
影响用户查询从服务器和主服务器位置的能力

REPLICATION SLAVE
复制从服务器所需的权限

SELECT
影响SELECT命令的使用

SHOW DATABASES
影响SHOW DATABASES命令的使用

SHOW VIEW
影响SHOW CREATE VIEW命令的使用

SHUTDOWN
影响SHUTDOWN命令的使用

SUPER
影响管理员级命令的使用,如CHANGE、MASTER、KILL thread、mysqladmin debug、PURGE MASTER LOGS和SET GLOBAL

TRIGGER
影响执行触发器的能力(从MySQL5.1.6开始)

UPDATE
影响UPDATE命令的使用

USAGE
只连接,不授予权限

MySQL数据库查看数据表占用空间大小和记录数

MySQL数据库中每个表占用的空间、表记录的行数的话,可以打开MySQL的 information_schema 数据库。在该库中有一个 TABLES 表,这个表主要字段分别是:

TABLE_SCHEMA : 数据库名
TABLE_NAME:表名
ENGINE:所使用的存储引擎
TABLES_ROWS:记录数
DATA_LENGTH:数据大小
INDEX_LENGTH:索引大小

一个表占用空间的大小,相当于是 数据大小 + 索引大小,
示例:
1、查看enrolment_db库的所有表大小:

select table_name,table_rows from tables where TABLE_SCHEMA = 'enrolment_db' order by table_rows desc; 

2、查看enrolment_db库的所有表大小、索引长度:

SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA='enrolment_db' order by TABLE_ROWS DESC;

3、统计enrolment_db表的所有记录条数:

SELECT sum(TABLE_ROWS) as heji FROM information_schema.TABLES WHERE TABLE_SCHEMA='enrolment_db';

注意:InnoDB引擎下table_rows行计数仅是大概估计值.

MySQL存储过程创建、查看、调用及参数、存储过程权限介绍

存储过程(Stored Procedure):
一组可编程的函数,是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

优点(为什么要用存储过程?):
  将重复性很高的一些操作,封装到一个存储过程中,简化了对这些SQL的调用
  批量处理:SQL+循环,减少流量,也就是“跑批”
  统一接口,确保数据的安全

相对于oracle数据库来说,MySQL的存储过程相对功能较弱,使用较少。
一、存储过程的创建和调用
  >存储过程就是具有名字的一段代码,用来完成一个特定的功能。
  >创建的存储过程保存在数据库的数据字典中。
创建存储过程

CREATE
    [DEFINER = { user | CURRENT_USER }]
 PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
  Valid SQL routine statement

[begin_label:] BEGIN
  [statement_list]
    ……
END [end_label]

存储过程权限相关:

CREATE ROUTINE : 创建存储过程的权限 
ALTER ROUTINE : 修改存储过程的权限 
EXECUTE :执行存储过程的权限
GRANT SELECT ON `mysql`.`proc` TO 'bis_enrolment_u'@'10.200.%' identified by 'xxxxx'; ; 普通账号没有查看存储过程或函数的权限,增加后可以看到系统有多少个函数或存储过程·









---阅读剩余部分---

MySQL中group_concat长度限制

GROUP_CONCAT参数默认值是1024,拼接的字符串的长度字节超过1024 则会被截断,数据量大的时候需要修改下字符大小:
查看group_concat 默认的长度:

mysql> show variables like 'group_concat_max_len';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| group_concat_max_len | 1024 |
+----------------------+-------+
1 row in set

修改默认字符大小

   1).在MySQL配置文件my.cnf的[mysqld]段加上
   group_concat_max_len = 4294967295 #最大长度
   2).直接控制台上设置立即生效
   SET GLOBAL group_concat_max_len=4294967295;
   SET SESSION group_concat_max_len=4294967295;

MySQL5.7不要设置这两个参数为-1,设置为-1后,group_concat_max_len值会变成4,即最小值。
参加官方文档:https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_group_concat_max_len

MySQL使用MySQLdump导出所有库

导出所有数据库
/usr/local/mysql/bin/mysqldump -u用户名 -p密码 --lock-all-tables --all-databases > /tmp/all.sql
导入数据库
/usr/local/mysql/bin/mysqldu -u用户名 -p密码 < /tmp/all.sql

导出指定库,排除不导出的库

#!/bin/bash
USER="用户名"
PASSWORD="密码" 
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
    fi
done

备份所有用户名密码

mysql -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user != 'root'" | \
while read uh; do mysql -BNe "show grants for $uh" | sed 's/$/;/; s/\\\\/\\/g'; done > grants.sql

MySQL的sql_mode说明

sql_mode直接理解就是:sql的运作模式。官方的说法是:sql_mode可以影响sql支持的语法以及数据的校验执行,这使得MySQL可以运行在不同的环境中以及和其他数据库一起运作。
MySQL5.7.4之后的版本和之前的版本的默认sql_mode有一个比较大的改变。
直接在my.cnf配置:

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

在启动的时候带参数--sql-mode="xxxxx"
直接在线修改SET GLOBAL sql_mode = 'xxxxx';
查看当前sql_mode

mysql> show variables like 'sql_mode';
+---------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                  |
+---------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)




---阅读剩余部分---

MySQL冥等模式slave_exec_mode

线上系统DB在MySQL 5.6.12下面的主从环境中经常出现1023错误(记录没找到)、1062错误(主键重复),即使重做主从,过不来多久又会出现,开启slave_exec_mode的idempotent模式:

set global slave_exec_mode='idempotent';
stop slave;
start slave;
show slave status\G;

默认值是strict(严格模式) 设置完毕之后,并不能立即生效,需要重启下复制进程,观察一段时间在没有出现1023、1062等主从同步问题;
在MySQL5.7.18和5.7.20下面的主从也是基于GTID的,并没有出现类似的问题,slave_exec_mode工作在STRICT模式下,可见MySQL5.7在性能、兼容性等等方面有很大的提升,推荐新项目直接上MySQL5.7.20。

MySQLbinlog 查看binlog时报错unknown variable 'default-character-set=utf8'

mysqlbinlog是用来查看binlog文件内容的(使用方式man mysqlbinlog查看),但是使用mysqlbinlog将binlog文件转换成人类可读的内容时却报错:
mysqlbinlog: unknown variable 'default-character-set=utf8'
原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令,
直接带上--no-defaults参数即可,

/usr/local/mysql/bin/mysqlbinlog --no-defaults -vv --base64-output=decode-rows binlog.000020

-vv --base64-output=decode-rows参数是进行base64解码,不带的话查看的是乱码·

--start-position=N 从二进制日志中第1个位置等于N参量时的事件开始读。
--stop-position=N 从二进制日志中第1个位置等于和大于N参量时的事件起停止读。

如把binlog.000020文件中75350到75357的记录导出到/tmp/f.log文件中:

/usr/local/mysql/bin/mysqlbinlog --no-defaults -vv --base64-output=decode-rows -v --start-position=263639 --stop-position=265331 /home/data/mysql/binlog/binlog.000020 > /tmp/f.log

也可以直接指定时间点操作
--start-datetime="2017-11-28 00:00:00" --stop-datetime="2017-11-28 23:59:59"

Mysql 8小时内无请求自动断开连接解决方法

Mysql服务器默认的“wait_timeout”是8小时,也就是说一个connection空闲超过8个小时,Mysql将自动断开该 connection。这就是问题的所在,在Hibernate默认连接池中的connections如果空闲超过8小时,Mysql将其断开,而Hibernate默认连接池并不知道该connection已经失效,如果这时有 Client请求connection,Hibernate默认连接池将该失效的Connection提供给Client,将会造成上面的异常。

---阅读剩余部分---

修改sync_binlog innodb_flush_log_at_trx_commit和sync_binlog参数 提高写入速度

innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数。

innodb_flush_log_at_trx_commit
如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行,该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。
如果innodb_flush_log_at_trx_commit设置为1,每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去。



---阅读剩余部分---

MySQL5.7设置通用查询日志

一:通用查询日志:
记录建立的客户端连接和执行的语句
1)show variables like '%verision%';
显示数据库版本号,存储引擎等信息

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.18 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.18-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Win64 |
+-------------------------+------------------------------+
8 rows in set, 1 warning (0.04 sec)

2)查看当前的通用日志是否开启
show variables like '%general%';

mysql> show variables like '%general%';
+------------------+--------------------+
| Variable_name | Value |
+------------------+--------------------+
| general_log | OFF |
| general_log_file | PC201608171713.log |
+------------------+--------------------+
2 rows in set, 1 warning (0.00 sec)
开启通用日志查询: set global general_log = on;
关闭通用日志查询:set global general_log = off;
设置通用日志保存位置:set global general_log_file='/tmp/general.log'; 

3)查看当前慢文件的格式





---阅读剩余部分---

快速查看MySQL所有库中哪个表数据最大

MySQL中有几十上百甚至上千张表,要怎么查看哪个表的数据量最大呢?
可以通过查看information_schema库中的tables表来获取哪个库中哪个表的数据最大:

mysql> select table_schema,table_name,table_rows from INFORMATION_SCHEMA.tables order by table_rows desc limit 10;
+--------------------+-----------------------------+------------+
| table_schema       | table_name                  | table_rows |
+--------------------+-----------------------------+------------+
| test               | UC_USER_1                   |   92529720 |
| test               | UC_USER                     |   90396596 |
| earth_plocc_system | GATEWAY_RECONICILIATION_LOG |    1017275 |
| plocc_back         | UC_USER_INFO                |     799422 |
| plocc_back         | UC_USER                     |     749713 |
| earth_plocc_system | WIFI_PICKLED_CLIENT_INFOS   |     627443 |
| earth_plocc_system | SCHE_EXECUTER               |     536726 |
| crm                | member_ids                  |     402739 |
| dna_bis_db_back    | TB_BIS_POS_ORDER            |     301389 |
| earth_plocc_system | SEARCH_AUTO_COMPLETE_STOCK  |     215817 |
+--------------------+-----------------------------+------------+
10 rows in set (1.50 sec)

test库中的UC_USER_1表里面的记录最多·


---阅读剩余部分---

MySQL的information_schema数据库详解

MySQL安装完成后,有一个自带的库information_schema,这个数据库存放的是数据库和数据表的元信息,
该数据库下有一张SCHEMATA表,这个表记录的是MySQL上所有的数据库信息,我们使用的show databases 命令就是来自于这个表
等价于

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

SCHENATA表里面的SCHEMA_NAME字段记录了所有的数据库名字;
TABLES表里面记录了数据库里面所有库都有那些表,表名字、类型、引擎、长度等等所有信息;

SELECT * FROM INFORMATION_SCHEMA.TABLES;




---阅读剩余部分---

最新

分类

归档

评论

其它