分类 MySQL 下的文章

MySQL视图查看及权限说明

查看视图是指查看数据库中,已经存在的视图的定义 必须要有SHOW VIEW的权限
查看视图有三种方式:
DESCRIBE语句
使用DESCRIBE语句,查看视图
MySQL中,使用DESCRIBE可以查看视图的字段信息,其中,包括字段名、字段类型等信息
语法格式:

DESCRIBE 视图名; 
或者
DESC 视图名;
root@sso_db_69 17:58:  [escdb]> desc oa_org;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | varchar(36)  | NO   |     | NULL    |       |
| name        | varchar(255) | NO   |     | NULL    |       |
| fullName    | varchar(255) | YES  |     | NULL    |       |
| resource_id | varchar(255) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)





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

MySQL:Incorrect integer value: '' for column 'id' at row 1警告信息

MySQL5.x在insert into插入时,如果不带id写成为空如下:

INSERT INTO `server` VALUE ('',
'SSO系统',
'sso-db',
'192.168.6.37',
'/',
'运行',
'生产',
'4',
'8',
'100',
'CentOS Linux release 7.5',
'IDC机房',
'zuul',
'8000',
'/',
'王二小',
'2018-06-30',
server_update = NOW(),
'test11111111111111111111'
);

就会警告如下:

Warning Code : 1366
Incorrect integer value: '' for column 'id' at row 1

MySQL5的都会遇到这样的问题,插入空字符要使用NULL,改成如下:

INSERT INTO `server` VALUE (NULL,
'SSO系统',
'sso-db',
'192.168.6.37',
'/',
'运行',
'生产',
'4',
'8',
'100',
'CentOS Linux release 7.5',
'IDC机房',
'zuul',
'8000',
'/',
'王二小',
'2018-06-30',
server_update = NOW(),
'test11111111111111111111'
);

即可插入成功,同时也无警告信息。

MySQL5.7.5X在线调整innodb_buffer_pool_size参数

MySQL5.7.5以前,调整innodb_buffer_pool_size需要重启mysql进程才可以生效,建议业务低峰时间执行,在MySQL 5.7.5版本后,innodb_buffer_pool_size参数的值可以动态的设置,可以通过innodb_buffer_pool_chunk_size参数配置块的大小,Innodb_buffer_pool_resize_status状态变量记录了从调整操作的状态。

innodb_buffer_pool_size参数表示缓冲池字节大小,InnoDB缓存表和索引数据的内存区域。mysql默认的值是128M。最大值与你的CPU体系结构有关,在32位操作系统,最大值是 4294967295 (2^32-1) ,在64 位操作系统,最大值为18446744073709551615 (2^64-1)。在32位操作系统中,CPU和操作系统实用的最大大小低于设置的最大值。

innodb_log_file_size表示在一个日志组每个日志文件的字节大小。日志文件的总大小(innodb_log_file_size* innodb_log_files_in_group)不能超过最高值512GB。例如一对255 GB的日志文件,已经接近了极限,不能超过它。默认值是48M。比较合适的值的范围是从1MB到1 / N个的缓冲池大小,其中N是该组中的日志文件的数量。该值越大,缓冲池中必要的检查点刷新活动就会越少,节省磁盘I/ O。但是越大的日志文件,mysql的崩溃恢复就越慢,尽管在mysql5.5之后改进了恢复性能和日志文件恢复的代价,一般2G左右就可以了;

在线调整innodb_buffer_pool_size方法如下:
当前大小为128M

show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
row in set (0.03 sec)

select 134217728/1024/1024;  
+---------------------+
| 134217728/1024/1024 |
+---------------------+
|        128.00000000 |
+---------------------+
row in set (0.00 sec)

动态调整为512M

set global innodb_buffer_pool_size = 512*1024*1024;
Query OK, 0 rows affected (0.18 sec)

show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
row in set (0.02 sec)

select 536870912/1024/1024;
+---------------------+
| 536870912/1024/1024 |
+---------------------+
|        512.00000000 |
+---------------------+
1 row in set (0.00 sec)

调整时,内部会把数据页移动到一个新的位置,单位是块。如果想提升移动速度,则需要调整
innodb_buffer_pool_chunk_size的参数大小,默认是128M
innodb_buffer_pool_size/innodb_buffer_pool_instances = innodb_buffer_pool_chunk_size的参数大小,默认是128M
调整完以后在my.cnf的mysqld部分增加

innodb_buffer_pool_size = 536870912

以防止重启后仍然生效。

MySQL报错“Illegal mix of collations for operation 'like'”错误代码: 1271

在使用LIKE对日期或者时间字段进行模糊查询的时候,MySQL5.7X会报错误:

错误代码: 1271
Illegal mix of collations for operation 'like'

解决方法:在 MySQL5.5以上版本, 必需改成like binary '%中文%' ;
若字段 Type 是 time,date,datetime 在 select 時若使用 like '%中文%' 会出现 Illegal mix of collations for operation 'like'在写程序时要对每个字段进行搜索,在执行时可能就会出现时间字段 like '%中文%' 这种语法,
低版本MySQL是不会出现错误的。

使用sysbench对MySQL进行测试

sysbench是一个模块化的、跨平台、多线程基准测试工具,主要用于评估测试各种不同系统参数下的系统、数据库负载情况,项目地址:https://github.com/akopytov/sysbench
sysbench主要支持以下几种测试模式:
CPU运算性能
磁盘IO性能
调度程序性能
内存分配及传输速度
POSIX线程性能
数据库性能(OLTP基准测试)
目前sysbench主要支持 Mysql,Drizzle,PgSQL,Oracle等几种数据库。
快速安装:

yum -y install make automake libtool pkgconfig libaio-devel        
yum -y install mariadb-devel openssl-devel    # For MySQL support, replace with mysql-devel on RHEL/CentOS 5
yum -y install postgresql-devel        #For PostgreSQL support
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
yum -y install sysbench

查看版本

[root@hongsin-monitor]# sysbench --version
sysbench 1.0.15

测试脚本路径

[root@hongsin-monitor sysbench]# pwd
/usr/share/sysbench
[root@hongsin-monitor sysbench]# ll
total 64
-rwxr-xr-x 1 root root  1452 Jul  4 04:06 bulk_insert.lua
-rw-r--r-- 1 root root 14369 Jul  4 04:06 oltp_common.lua
-rwxr-xr-x 1 root root  1290 Jul  4 04:06 oltp_delete.lua
-rwxr-xr-x 1 root root  2415 Jul  4 04:06 oltp_insert.lua
-rwxr-xr-x 1 root root  1265 Jul  4 04:06 oltp_point_select.lua
-rwxr-xr-x 1 root root  1649 Jul  4 04:06 oltp_read_only.lua
-rwxr-xr-x 1 root root  1824 Jul  4 04:06 oltp_read_write.lua
-rwxr-xr-x 1 root root  1118 Jul  4 04:06 oltp_update_index.lua
-rwxr-xr-x 1 root root  1127 Jul  4 04:06 oltp_update_non_index.lua
-rwxr-xr-x 1 root root  1440 Jul  4 04:06 oltp_write_only.lua
-rwxr-xr-x 1 root root  1919 Jul  4 04:06 select_random_points.lua
-rwxr-xr-x 1 root root  2118 Jul  4 04:06 select_random_ranges.lua
drwxr-xr-x 4 root root  4096 Oct 20 16:48 tests

写性能测试,数据准备

sysbench --test=/usr/share/sysbench/oltp_write_only.lua --mysql-host=192.168.255.90 --mysql-port=3306 --mysql-user=ice --mysql-password=xxxxxx --mysql-db=a1 --report-interval=10 --max-requests=0 --time=120 --threads=4500 --tables=10 --table-size=100000 prepare
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest9'...Creating table 'sbtest8'...
Creating table 'sbtest3'...
Creating table 'sbtest4'...Creating table 'sbtest6'...


Creating table 'sbtest1'...
Creating table 'sbtest7'...
Creating table 'sbtest5'...
Creating table 'sbtest2'...
Creating table 'sbtest10'...
Inserting 100000 records into 'sbtest4'
Inserting 100000 records into 'sbtest10'
Inserting 100000 records into 'sbtest1'
Inserting 100000 records into 'sbtest2'
Inserting 100000 records into 'sbtest5'
Inserting 100000 records into 'sbtest8'
Inserting 100000 records into 'sbtest6'
Inserting 100000 records into 'sbtest9'
Inserting 100000 records into 'sbtest7'
Inserting 100000 records into 'sbtest3'
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest3'...
参数的解释:
--threads=4500  表示发起4500个并发连接
--oltp-read-only=off 表示不要进行只读测试,也就是会采用读写混合模式测试
--report-interval=10 表示每10秒输出一次测试进度报告
--rand-type=uniform 表示随机类型为固定模式,其他几个可选随机模式:uniform(固定),gaussian(高斯),special(特定的),pareto(帕累托)
--time=120 表示最大执行时长为 120秒
--max-requests=0 表示总请求数为 0,因为上面已经定义了总执行时长,所以总请求数可以设定为 0;也可以只设定总请求数,不设定最大执行时长
--tables=10  表示10个表
--table-size=100000  单表100000条记录
--percentile=99 表示设定采样比例,默认是 95%,即丢弃1%的长请求,在剩余的99%里取最大值

运行测试

sysbench --test=/usr/share/sysbench/oltp_write_only.lua --mysql-host=192.168.255.90 --mysql-port=3306 --mysql-user=ice --mysql-password=hongsinCS --mysql-db=a1 --report-interval=10 --max-requests=0 --time=120 --threads=4500 --tables=10 --table-size=100000 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 4500
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 4500 tps: 618.00 qps: 4951.50 (r/w/o: 0.00/1950.97/3000.53) lat (ms,95%): 8038.61 err/s: 91.70 reconn/s: 0.00
[ 20s ] thds: 4500 tps: 562.83 qps: 3806.01 (r/w/o: 0.00/1849.30/1956.71) lat (ms,95%): 15934.78 err/s: 227.01 reconn/s: 0.00
[ 30s ] thds: 4500 tps: 434.80 qps: 3051.53 (r/w/o: 0.00/1599.01/1452.51) lat (ms,95%): 22842.77 err/s: 226.20 reconn/s: 0.00
[ 40s ] thds: 4500 tps: 479.70 qps: 3310.41 (r/w/o: 0.00/1803.90/1506.50) lat (ms,95%): 28352.44 err/s: 224.60 reconn/s: 0.00
[ 50s ] thds: 4500 tps: 495.10 qps: 3403.11 (r/w/o: 0.00/1910.30/1492.80) lat (ms,95%): 28867.59 err/s: 219.60 reconn/s: 0.00
[ 60s ] thds: 4500 tps: 453.20 qps: 3140.89 (r/w/o: 0.00/1769.59/1371.29) lat (ms,95%): 28867.59 err/s: 215.80 reconn/s: 0.00
[ 70s ] thds: 4500 tps: 469.90 qps: 3246.01 (r/w/o: 0.00/1848.80/1397.20) lat (ms,95%): 28867.59 err/s: 228.90 reconn/s: 0.00
[ 80s ] thds: 4500 tps: 466.59 qps: 3238.67 (r/w/o: 0.00/1859.68/1378.99) lat (ms,95%): 29926.15 err/s: 225.20 reconn/s: 0.00
[ 90s ] thds: 4500 tps: 441.30 qps: 3084.43 (r/w/o: 0.00/1765.82/1318.61) lat (ms,95%): 29926.15 err/s: 225.30 reconn/s: 0.00
[ 100s ] thds: 4500 tps: 434.20 qps: 3026.20 (r/w/o: 0.00/1737.20/1289.00) lat (ms,95%): 29926.15 err/s: 217.40 reconn/s: 0.00
[ 110s ] thds: 4500 tps: 471.70 qps: 3247.39 (r/w/o: 0.00/1872.59/1374.80) lat (ms,95%): 32161.14 err/s: 218.40 reconn/s: 0.00
[ 120s ] thds: 4500 tps: 476.39 qps: 3289.44 (r/w/o: 0.00/1892.36/1397.07) lat (ms,95%): 31023.52 err/s: 232.80 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0         读操作
        write:                           233015     --写总数
        other:                           197026  --其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作,例如COMMIT等)
        total:                           430041  --全部总数
    transactions:                        62617  (486.84 per sec.)  --总事务数(每秒事务数)
    queries:                             430041 (3343.55 per sec.) --查询数(每秒查询数)
    ignored errors:                      27087  (210.60 per sec.)  --忽略错误数
    reconnects:                          0      (0.00 per sec.)  --重新连接次数

General statistics:
    total time:                          128.6147s  --运行总时间
    total number of events:              62617  --事件总数

Latency (ms):
         min:                                    2.09
         avg:                                 8957.64
         max:                                99069.80
         95th percentile:                    27846.48
         sum:                            560900840.54

Threads fairness:    #线程平均数
    events (avg/stddev):           13.9149/3.92
    execution time (avg/stddev):   124.6446/2.39

清理数据:

sysbench --test=/usr/share/sysbench/oltp_write_only.lua --mysql-host=192.168.255.90 --mysql-port=3306 --mysql-user=ice --mysql-password=hongsinCS --mysql-db=a1 --report-interval=10 --max-requests=0 --time=120 --threads=4500 --tables=10 --table-size=100000 cleanup
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'...

可以根据/usr/share/sysbench下面的lua脚本进行读、写、更新,随机等等测试,条件允许建议时间1小时以上,以便测试数据准确·

mysqlslap: [ERROR] unknown variable 'default-character-set=utf8mb4'

运行mysqlslap报错如下:

[root@monitor-db ~]# mysqlslap --version
mysqlslap: Error when connecting to server: Access denied for user 'root'@'localhost' (using password: NO)

解决方法:带上--no-defaults参数

[root@sso-db-a binlog]# mysqlslap --no-defaults --version
mysqlslap  Ver 1.0 Distrib 5.7.23, for Linux (x86_64)

或者在my.cnf里面将default-character-set修改为utf8,需重启生效

[client]
#设置MySQL客户端的字符集
default-character-set=utf8

mysqlslap.png

FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"

使用sysbench做MySQL性能压测的时候,报错:

FATAL: mysql_stmt_prepare() failed
(last message repeated 2 times)
FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"
(last message repeated 1 times)

max_prepared_stmt_count参数限制了同一时间在mysqld上所有session中prepared 语句的上限。
它的取值范围为“0 - 1048576”,默认为16382。
mysql对于超出max_prepared_stmt_count的prepare语句就会报1461的错误。
sysvar_max_prepared_stmt_count.png
官方文档:https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
一般而言,默认值应该是足够用的,因为现场的并发其实没有那么的大。
一个可能的原因是应用端那边没有关闭prepared的语句。
直连后端master执行如下命令
mysql> show global status like ‘com_stmt%’;
查看如下3个参数值:
Com_stmt_close prepare语句关闭的次数
Com_stmt_execute prepare语句执行的次数
Com_stmt_prepare prepare语句创建的次数

请确认Com_stmt_close的值是否接近于Com_stmt_prepare。

mysql> show global status like 'com_stmt%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Com_stmt_execute        | 79358467 |
| Com_stmt_close          | 18100    |
| Com_stmt_fetch          | 0        |
| Com_stmt_prepare        | 67646    |
| Com_stmt_reset          | 0        |
| Com_stmt_send_long_data | 0        |
| Com_stmt_reprepare      | 0        |
+-------------------------+----------+
7 rows in set (0.00 sec)
mysql> show global variables like 'max_prepared_stmt_count';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count | 16382 |
+-------------------------+-------+
1 row in set (0.01 sec)
mysql> set global max_prepared_stmt_count=1048576;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'max_prepared_stmt_count';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count |1048576|
+-------------------------+-------+
1 row in set (0.00 sec)

修改之后再次使用100并发压测则没有报错了。
1 row in set (0.01 sec)
为保证mysql实例重启生效可以写到配置文件my.cnf

[mysqld]
max_prepared_stmt_count=1048576

log_error_verbosity日志参数

MySQL中log_error定义是否启用错误日志的功能和错误日志的存储位置,log_warnings定义是否将告警信息(warning messages)也写入错误日志。此选项默认启用,具体来说:

log_warnings 为0, 表示不记录告警信息。
log_warnings 为1, 表示告警信息写入错误日志。
log_warnings 大于1, 表示各类告警信息,例如有关网络故障的信息和重新连接信息写入错误日志。

此参数在不同版本略有差别,在MySQL 5.6中,log_warnings的默认值为1
在MySQL 5.7中,有些版本默认值为2,有些版本默认值为1, 具体参考官方文档信息,如下所示:
5.7.png
从MySQL 5.7.2开始,首选log_error_verbosity系统变量,而不是使用--log-warnings选项或log_warnings系统变量,这个参数从MySQL 8.0.3开始被移除了:
This system variable was removed in MySQL 8.0.3. Use the log_error_verbosity system variable instead.

新参数log_error_verbosity更简单,它有三个可选值, 分别对应:1 错误信息;2 错误信息和告警信息; 3:错误信息、告警信息和通知信息。 具体参考官方文档,下面部分截取官方文档。
8.0.png
查看错误log设置:

root@sso_db_69 15:34:  [(none)]> show variables like '%log_error%';
+---------------------+-----------------------+
| Variable_name       | Value                 |
+---------------------+-----------------------+
| binlog_error_action | ABORT_SERVER          |
| log_error           | /data/mysql/error.log |
| log_error_verbosity | 2                     |
+---------------------+-----------------------+
3 rows in set (0.00 sec)

调整log_error_verbosity级别:

SET GLOBAL log_warnings=2;
SET GLOBAL log_error_verbosity=2;

永久调整my.cnf增加设置:

[mysqld]
log_warnings=1
log_error_verbosity=1

参数说明

Binlog_error_action=ABORT_SERVER
Binlog_error_action参数控制当不能写binlog时,mysql-server将会采取什么行动。
设置binlog_error_action=ABORT_SERVER会使mysql-server在写binlog遇到严重错误时退出,比如磁盘满了,文件系统不可写入了等。
在ABORT_SERVER选项下,binlog和从库都是安全的,这是官方修改此默认值的原因。
在先前的选项下(binlog_error_action=IGNORE_ERROR),如果一个错误发生,导致无法写入binlog,mysql-server会在错误日志中记录错误并强制关闭binlog功能。这会使mysql-server在不记录binlog的模式下继续运行,导致从库无法继续获取到主库的binlog。

官方参考资料:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_log_warnings
https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_log-warnings

MySQL全局锁库锁表

1、全局读锁定:

FLUSH TABLES WITH READ LOCK ;

执行了命令之后所有库所有表都被锁定只读,一般用在数据库联机备份,这个时候数据库的写操作将被阻塞,读操作顺利进行。

解锁:

UNLOCK TABLES ;

2、全局表级别锁定:

LOCK TABLES tbl_name ; #不影响其他表的写操作

解锁也是:

UNLOCK TABLES ;

这两个语句在执行的时候都需要注意个特点,就是隐式提交的语句,在退出mysql终端的时候都会隐式的执行unlock tables,也就是如果要让表锁定生效就必须一直保持对话。

read-lock:  允许其他并发的读请求,但阻塞写请求,即可以同时读,但不允许任何写。也叫共享锁
write-lock: 不允许其他并发的读和写请求,是排他的(exclusive)。也叫独占锁

MySQL删除数据库中的所有表的两个方法

快速删除MySQL数据库中的所有表的两个方法:

1、最简单的方法,最方便的方法:

删除数据库,然后重新建立一个空数据库

2、但是有删除所有表的方法

(1)使用concat函数产生删除表的sql语句,

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

(2)执行sql语句

DROP TABLE IF EXISTS aws_active_data;
DROP TABLE IF EXISTS aws_answer;
DROP TABLE IF EXISTS aws_answer_comments;
DROP TABLE IF EXISTS aws_answer_thanks;
DROP TABLE IF EXISTS aws_answer_uninterested;
DROP TABLE IF EXISTS aws_answer_vote;
DROP TABLE IF EXISTS aws_approval;
DROP TABLE IF EXISTS aws_article;
DROP TABLE IF EXISTS aws_article_comments;
DROP TABLE IF EXISTS aws_article_vote;
DROP TABLE IF EXISTS aws_attach;
DROP TABLE IF EXISTS aws_category;
DROP TABLE IF EXISTS aws_column;
DROP TABLE IF EXISTS aws_column_focus;
DROP TABLE IF EXISTS aws_draft;
DROP TABLE IF EXISTS aws_edm_task;
DROP TABLE IF EXISTS aws_edm_taskdata;
DROP TABLE IF EXISTS aws_edm_unsubscription;
DROP TABLE IF EXISTS aws_edm_userdata;
DROP TABLE IF EXISTS aws_edm_usergroup;
DROP TABLE IF EXISTS aws_education_experience;
DROP TABLE IF EXISTS aws_favorite;

附concat函数使用方法:
CONCAT(str1,str2,…)

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。

注意:
如果所有参数均为非二进制字符串,则结果为非二进制字符串。
如果自变量中含有任一二进制字符串,则结果为一个二进制字符串。
一个数字参数被转化为与之相等的二进制字符串格式;若要避免这种情况,可使用显式类型 cast, 例如:

SELECT CONCAT(CAST(int_col AS CHAR), char_col)

MySQL的concat函数可以连接一个或者多个字符串,如

mysql> select concat('10');
+--------------+
| concat('10') |
+--------------+
| 10   |
+--------------+
1 row in set (0.00 sec)
mysql> select concat('11','22','33');
+------------------------+
| concat('11','22','33') |
+------------------------+
| 112233 |
+------------------------+
1 row in set (0.00 sec)

MySQL的concat函数在连接字符串的时候,只要其中一个是NULL,那么将返回NULL
mysql> select concat('11','22',null);
+------------------------+
| concat('11','22',null) |
+------------------------+
| NULL   |
+------------------------+
1 row in set (0.00 sec)

Mysql 查看连接数、状态、最大并发数

MySQL: ERROR 1040: Too many connections”的异常情况,造成这种情况的一种原因是访问量过高,MySQL服务器抗不住,这个时候就要考虑增加从服务器分散读压力;另一种原因就是MySQL配置文件中max_connections值过小。
首先,我们来查看mysql的最大连接数:

root@hongsin-monitor-db 18:14:58 [(none)]>show variables like '%max_connections%';
+-----------------+--------+
| Variable_name   | Value  |
+-----------------+--------+
| max_connections | 100000 |
+-----------------+--------+
1 row in set (0.00 sec)

查看服务器响应的最大连接数:

root@hongsin-monitor-db 18:11:29 [(none)]> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 139   |
+----------------------+-------+
1 row in set (0.01 sec)

可以看到服务器响应的最大连接数为139,远远低于mysql服务器允许的最大连接数值。

对于mysql服务器最大连接数值的设置范围比较理想的是:服务器响应的最大连接数值占服务器上限连接数值的比例值在10%以上,如果在10%以下,说明mysql服务器最大连接上限值设置过高.

Max_used_connections / max_connections * 100% = 139/100000 *100% ≈ 0.139%

我们可以看到占比远低于10%(因为这是本地监控测试服务器,结果值没有太大的参考意义,大家可以根据实际情况设置连接数的上限值)。
设置这个最大连接数值
方法1:

set GLOBAL max_connections=256;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%max_connections%';

+-----------------+-------+

| Variable_name  | Value |

+-----------------+-------+

| max_connections | 256  |

+-----------------+-------+

1 row in set (0.00 sec)

方法2:

修改mysql配置文件my.cnf,在[mysqld]段中添加或修改max_connections值:
max_connections=256

重启mysql服务即可。

Mysql5.5 mysql5.6 mysql5.7:默认的最大连接数都是151,上限为:100000
Mysql5.0版本:默认的最大连接数为100,上限为16384

Zabbix3.4.11使用自带模板监控MySQL

Zabbix默认的agentd模板里面有一个userparameter_mysql.conf配置文件,修改文件内容如下:

[root@c75 zabbix_agentd]# cat /usr/local/zabbix/conf/zabbix_agentd/userparameter_mysql.conf 
UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/usr/local/zabbix/conf/ mysql -N | awk '{print $$2}'
UserParameter=mysql.size[*],bash -c 'echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema=\"$1\"")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name=\"$2\"");" | HOME=/usr/local/zabbix/conf/ mysql -N'
UserParameter=mysql.ping,netstat -ntpl |grep 3306 |grep mysql |wc|awk '{print $1}'
UserParameter=mysql.version,mysql -V
UserParameter=mysql.vars[*],echo "show variables where Variable_name='$1';" | HOME=/usr/local/zabbix/conf/ mysql -N | awk '{print $$2}'

做下MySQL的软连接:

ln -s /usr/local/mysql/bin/mysql /bin/

修改zabbix_agentd.conf文件,增加

Include=/usr/local/zabbix/conf/zabbix_agentd/*.conf

登陆MySQL控制台创建只读用户:

GRANT SELECT ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY 'xxxxxx';

在/usr/local/zabbix/conf创建.my.cnf文件,内容如下:

[root@c75 conf]# cat /usr/local/zabbix/conf/.my.cnf 
# Zabbix Agent
[mysql]
host=localhost
user=zabbix
password="xxxxxx"
socket=/data/mysql/mysql.sock
[mysqladmin]
host=localhost
user=zabbix
password="xxxxxx"
socket=/data/mysql/mysql.sock

重启zabbix-agentd

killall -9 zabbix_agentd
/usr/local/zabbix/sbin/zabbix_agentd -c /usr/local/zabbix/conf/zabbix_agentd.conf

在zabbix-server机器上检测

[root@monitor ~]# zabbix_get -s 192.168.3.244 -p 10050 -k mysql.version
mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper
[root@monitor ~]# zabbix_get -s 192.168.3.244 -p 10050 -k mysql.ping
1

注意:如果测试的时候报错:sh: mysql: command not found,sh: mysqladmin: command not found,
修改userparameter_mysql.conf中mysql,mysqladmin命令的路径,使用绝对路径或者做下软连接即可;
如果报错

(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.

执行的脚本中执行

chmod +s /bin/netstat

来解决chmod +s 的意思是:为了方便普通用户执行一些特权命令,SUID/SGID程序允许普通用户以root身份暂时执行该程序,并在执行结束后再恢复身份
给要监控的主机添加MySQL模板,最后效果如下图:
1.png
2.png

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即可;

用mysqlslap对MySQL进行压力测试

MySQL从5.1.4版开始带有一个压力测试工具mysqlslap,通过模拟多个并发客户端访问mysql来执行测试。

[root@test-db data]# mysqlslap -a --concurrency=10000 --number-of-queries 10000 --iterations=10 --engine=innodb –debug-info -uroot -pyueworldtest
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 6.451 seconds
        Minimum number of seconds to run all queries: 1.963 seconds
        Maximum number of seconds to run all queries: 24.031 seconds
        Number of clients running queries: 10000
        Average number of queries per client: 1

参数说明:

–auto-generate-sql, -a
自动生成测试表和数据
–auto-generate-sql-load-type=type
测试语句的类型。取值包括:read,key,write,update和mixed(默认)。
–number-char-cols=N, -x N
自动生成的测试表中包含多少个字符类型的列,默认1
–number-int-cols=N, -y N
自动生成的测试表中包含多少个数字类型的列,默认1
–number-of-queries=N
总的测试查询次数(并发客户数×每客户查询次数)
–query=name,-q
使用自定义脚本执行测试,例如可以调用自定义的一个存储过程或者sql语句来执行测试。
–create-schema
测试的schema,MySQL中schema也就是database
–commint=N
多少条DML后提交一次
–compress, -C
如果服务器和客户端支持都压缩,则压缩信息传递
–concurrency=N, -c N
并发量,也就是模拟多少个客户端同时执行select。可指定多个值,以逗号或者–delimiter参数指定的值做为分隔符
–engine=engine_name, -e engine_name
创建测试表所使用的存储引擎,可指定多个
–iterations=N, -i N
测试执行的迭代次数
–detach=N
执行N条语句后断开重连
–debug-info, -T
打印内存和CPU的信息
–only-print
只打印测试语句而不实际执行

mysqldump导出报错-Got error: 1449错误解决办法

mysqldump -uroot -pPasswd DBName > /home/lsf/DB_Backup.sql

报错,显示:

Got error: 1449: The user specified as a definer ('xxx'@'') does not exist when using LOCK TABLES

或者直接报:

the user specified as a definer ('xxx'@'') does not exist

解决办法:

给xxx用户再添加一个对全部host都有可以访问的权限:

mysql -uroot -pPasswd
mysql >grant all privileges on *.* to xxx@"%" identified by "Passwd";
mysql >flush privileges;

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进刚才备份的数据;

MySQL错误ERROR 1786 (HY000)解决

业务上需要支持create table XXX as select * from XXX; 这种创建表的语法,但是MySQL5.7.x版本里面gtid是开启的,会报错

ERROR 1786 (HY000):Statement violates GTID consistency: CREATE TABLE ... SELECT.

官方说明:https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-restrictions.html

CREATE TABLE ... SELECT statements.  CREATE TABLE ... SELECT is not safe for statement-based replication. When using row-based replication, this statement is actually logged as two separate events—one for the creation of the table, and another for the insertion of rows from the source table into the new table just created. When this statement is executed within a transaction, it is possible in some cases for these two events to receive the same transaction identifier, which means that the transaction containing the inserts is skipped by the slave. Therefore, CREATE TABLE ... SELECT is not supported when using GTID-based replication.

解决办法关闭GTID模式:
my.cnf里面修改参数为:

gtid_mode = OFF
enforce_gtid_consistency = OFF

重启MySQL,再次创建成功:

mysql> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show variables like '%enforce_gtid_consistency%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | OFF   |
+--------------------------+-------+
1 row in set (0.01 sec)

mysql> create table t1 as select * from BS_CONT;
Query OK, 0 rows affected (0.12 sec)

最新

分类

归档

评论

  • Liang: 贴下编译参数和步骤,...
  • shao3911: 您好,为什么我在编译...
  • aliang: 先看是yum安装还是...
  • aliang: 将原来的nginx安...
  • yen: 3、如果要回滚的话,...
  • yen: 刚好需要升级ngin...
  • 文雨: 一些新的method...
  • aliang: 默认不屏蔽估计开发团...
  • 山野愚人居: PHP既然允许直接使...
  • aliang: 最下面有github地址·

其它