碎碎念
一年多没在公众号写笔记了，继上周开始记录后，本周的笔记也来了，哈哈，还是日常工作中遇到的一些案例，有些是比较经典的场景，有些是比较细节的操作，考虑到其他同学也可能会遇到相关问题，便都整理记录啦，现在，往后看就行了，相信不会辜负你的期待。
character_set_client相关参数的小"坑"
案例说明
问题的开始要从一个表情包说起，同事向MySQL实例中插入特殊字符表情包报错，原因是字符集utf8不支持特殊字符，然后同事执行set global variables xxx操作修改字符集，修改后退出当前session，然后重新执行插入操作，依旧报错，发现字符集修改并不是和自己想的一样，从而引发了接下来的故事，现在，一起开始吧。
测试流程:
#当前character_set_client值
mysql> show global variables like "%char%";
+--------------------------+------------------+
| Variable_name | Value |
+--------------------------+------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
+--------------------------+------------------+
mysql> show variables like "%char%";
+--------------------------+------------------+
| Variable_name | Value |
+--------------------------+------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
+--------------------------+-------------------
#测试表结构
mysql> show create table emoji\G;
*************************** 1. row ***************************
Table: emoji
Create Table: CREATE TABLE `emoji` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` text CHARACTER SET utf8mb4,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
#执行插入操作insert into emoji(a) values ('😲');
mysql> insert into emoji(a) values ('\U+1F632');
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\xB2' for column 'a' at row 1
#全局级别设置character_set_client值为utf8mb4
mysql> set global character_set_client=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
可以看到，当前情况下character_set_client等值会话级别值为utf8,全局级别为utf8mb4,执行插入特殊字符会报错，尝试进行全局级别修改character_set_client值为utf8mb4，退出当前会话重新尝试插入。
mysql> show variables like "%char%";
+--------------------------+------------------+
| Variable_name | Value |
+--------------------------+------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
+--------------------------+-------------------
mysql> show global variables like "%char%";
+--------------------------+-------------------
| Variable_name | Value
+--------------------------+------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
+--------------------------+-------------------
#再次尝试插入特殊字符记录
mysql> insert into emoji(a) values ('\U+1F632');
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\xB2' for column 'a' at row 1
#会话级别修改character_set_client值
mysql> set character_set_client=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
#再次尝试插入特殊字符记录
mysql> insert into emoji(a) values ('\U+1F632');
Query OK, 1 row affected, 1 warning (0.00 sec)
#进行查询记录
mysql> select * from emoji;
+----+------+
| id | a |
+----+------+
| 1 | ? |
+----+------+
1 row in set (0.00 sec)
#执行set names操作
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
#再次插入特殊字符记录
mysql> insert into emoji(a) values ('\U+1F632');
Query OK, 1 row affected (0.00 sec)
#进行查询记录
mysql> select * from emoji;
+----+------+
| id | a |
+----+------+
| 1 | ? |
| 2 | 😲 |
+----+------+
2 rows in set (0.00 sec)
进行全局级别修改character_set_client值为utf8mb4后重新会话登录，发现会话级别值并没有改变，尝试会话级别修改character_set_client值，可以成功插入，但是实际查询结果是乱码，通过设置set names utf8mb4后再次插入，结果符合预期，这里就涉及到character_set_client，character_set_connection，character_set_results三个变量之间的爱恨情仇了，生产上这三个变量需要保持一致，不然可能就会乱码，单个设置太麻烦，可以通过如下方式轻松搞定。
[client]
default-character-set = utf8mb4
mysql -uroot -p'xxx' --default-character-set=utf8mb4
mysql> insert into emoji(a) values ('\U+1F632');
mysql> set names utf8mb4;
mysql> insert into emoji(a) values ('\U+1F632');
mysql> charset utf8mb4;
Charset changed
mysql> insert into emoji(a) values ('\U+1F632');
mysql> select * from emoji;
+----+------+
| id | a |
+----+------+
| 1 | 😲 |
| 2 | 😲 |
| 3 | 😲 |
+----+------+
3 rows in set (0.00 sec)
如上，简单设置的四种方式: 方式一:set names xxx语句 方式二:配置文件设置default-character-set 方式三:客户端连接加上--default-character-set选项 方式四:charset xxx语句 更多的细节，可以参考官网https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html#charset-connection-client-configuration
数据库权限问题
案例说明
随着数字化时代来临，人们对数据安全越来越关注，数据库作为数据的承载工具，自然对安全也有很高的要求，数据安全是一个大的范畴，细化下来，这其中就涉及数据操作安全，也就是权限管控，作为常用的数据库之一，应该如何对MySQL进行权限控制呢，本部分围绕这个进行展开。
MySQL提供的权限：
mysql> select * from mysql.user where user='test'\G;
*************************** 1. row ***************************
Host: %
User: test
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: Y
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: xxxxxxxxxxxxxxxxx
password_expired: N
password_last_changed: 2024-03-25 07:36:02
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
#授予所有权限并支持对其他用户赋权
mysql> GRANT ALL PRIVILEGES ON *.* TO test@"%" IDENTIFIED BY 'xxx' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
#查看权限
mysql> show grants for test@'%';
+---------------------------------------------+
| Grants for test@% |
+---------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' |
| WITH GRANT OPTION |
+---------------------------------------------+
1 row in set (0.00 sec)
#回收对其他用户赋权权限
mysql> revoke GRANT OPTION ON *.* from test@'%';
Query OK, 0 rows affected (0.00 sec)
#查看权限
mysql> show grants for test@'%';
+-------------------------------------------+
| Grants for test@% |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)
#回收创建用户权限
mysql> revoke CREATE USER ON *.* from test@'%';
Query OK, 0 rows affected (0.00 sec)
#除CREATE USER以外的其他权限
mysql> show grants for test@'%';
+---------------------------------------------+
| Grants for test@% |
+---------------------------------------------+
|GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER,CREATE TABLESPACE ON *.* TO 'test'@'%'|
+---------------------------------------------+
1 row in set (0.00 sec)
#all权限不包含proxy权限和grant option
mysql> GRANT proxy ON mysql@'%' TO 'test'@'%';
Query OK, 0 rows affected (0.00 sec)
#赋予proxy权限
mysql> show grants for test@'%';
+-------------------------------------------+
| Grants for test@% |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' |
| GRANT PROXY ON 'mysql'@'%' TO 'test'@'%' |
+-------------------------------------------+
2 rows in set (0.00 sec)
如上,MySQL中用户的权限可以通过mysql.user表查看，也可以通过show grants for user@host方式查看,那么具体授权时该如何选择呢，一般来说，建议遵循最小权限原则，另外，需要注意的是，MySQL中权限有部分和全局之分，具体如下: 测试流程：
mysql> show grants for test@'%';
+----------------------------------+
| Grants for test@% |
+----------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' |
+----------------------------------+
1 row in set (0.00 sec)
#针对单库进行赋权，也可针对单表，具体权限可以根据实际删减
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON test.* TO 'test'@'%';
Query OK, 0 rows affected (0.00 sec)
#全局变量不能部分授权
mysql> GRANT PROCESS, FILE, REPLICATION CLIENT ON test.* TO 'test'@'%';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> GRANT PROCESS, FILE, REPLICATION CLIENT ON *.* TO 'test'@'%';
Query OK, 0 rows affected (0.00 sec)
#查看权限
mysql> show grants for test@'%';
+---------------------------------------------+
| Grants for test@% |
+---------------------------------------------+
| GRANT PROCESS, FILE, REPLICATION CLIENT ON *.* TO 'test'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'test'@'%' |
+---------------------------------------------+
2 rows in set (0.00 sec)
针对部分权限场景，可以针对单库/单表赋予读写权限，alter,索引等权限，针对全局权限，例如某个用户想查看show processlist状态，复制状态，可以参考上面的语句。有趣的是，我们知道添加索引可以通过alter table xxx add index 语句或者create index xxx语句，但是如果某个用户仅仅有其中一个权限时，执行添加索引语句结果却很有差别，一起了解下吧 : 测试流程：
#仅赋予alter权限
mysql> show grants for test@'%';
+-----------------------------------------+
| Grants for test@% |
+-----------------------------------------+
| GRANT ALTER ON *.* TO 'test'@'%' |
| GRANT ALTER ON `test`.* TO 'test'@'%' |
+-----------------------------------------+
2 rows in set (0.00 sec)
#通过create index方式添加索引
mysql> create index idx_age on my_test(age);
ERROR 1142 (42000): INDEX command denied to user 'test'@'xxx' for table 'my_test'
#通过alter table方式添加索引
mysql> alter table my_test add index idx_age(age);
Query OK, 0 rows affected (0.01 sec)
仅赋予index权限
mysql> show grants for test@'%';
+-----------------------------------------+
| Grants for test@% |
+-----------------------------------------+
| GRANT INDEX ON *.* TO 'test'@'%' |
| GRANT INDEX ON `test`.* TO 'test'@'%' |
+-----------------------------------------+
2 rows in set (0.00 sec)
#通过alter table方式添加索引
mysql> alter table my_test add index idx_age2(age2);
ERROR 1142 (42000): ALTER command denied to user 'test'@'xxx' for table 'my_test'
#通过create index方式添加索引
mysql> create index idx_age2 on my_test(age2);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
可以看到，当仅有alter权限时，可以通过alter table xxx add index方式成功添加索引，而通过create index 方式则会报权限不足问题，而当仅有index权限时，通过alter table xxx add index方式则会失败，而create index方式却可以执行成功。这是不是很有趣呢。
jemalloc内存分配器配置
案例说明
在生产环境上，经常遇到MySQL中内存问题，其中一个比较好的方法是更换默认内存分配器为jemalloc内存分配器，相关的操作方法在网上帖子也很多，而今天来谈论实际操作中遇到的一些细节内容，没准有不一样的收获，先从jemalloc安装说起吧。
#通过yum方式安装
yum -y install jemalloc
生效环境变量
source /usr/bin/jemalloc.sh
env|grep LD_PRELOAD
#通过编译方式安装
下载安装包:
wget https://github.com/jemalloc/jemalloc/releases/download/5.2.1/jemalloc-5.2.1.tar.bz2
yum install bzip2
解压:
tar -jxvf jemalloc-5.2.1.tar.bz2
cd jemalloc-5.2.1
./configure --prefix=/usr
make && make install
ln -s /usr/lib/libjemalloc.so.2 /usr/lib64/libjemalloc.so.1
这里提供了两种jemalloc内存分配器安装方法，好奇的你可能在想，既然yum方式可以一条命令搞定，为什么还要麻烦的去自行编译呢，这里温馨提示一下，经过多个项目的验证，5.2版本及以上的jemalloc内存分配器在内存回收方面表现更好，而通过yum方式安装的jemalloc内存分配器版本往往比较低，效果自然就大打折扣，这当然非我们所愿。安装jemalloc完成后，如何在MySQL中使用jemalloc内存分配器呢。
加载jemalloc内存分配器：
方式一:通过指定LD_PRELOAD环境变量加载jemalloc:
切换到mysql运行用户
sudo su - root
设置LD_PRELOAD环境变量
export LD_PRELOAD=/usr/lib64/libjemalloc.so.1
env|grep LD_PRELOAD
持久化到.bash_profile配置文件
重启数据库
确认MySQL服务是否加载了jemalloc
lsof -p `ps -ef|grep mysqld`|grep -i jemalloc
注:可以写进./bash_profile文件然后source ./bash_profile，只对当前用户生效
写进/etc/profile文件，然后source /etc/profile，对全局用户生效
方式二:在配置文件中指定malloc-lib参数:
在配置文件中[mysqld_safe]下添加:
malloc-lib=/usr/lib64/libjemalloc.so.1
重启数据库,注意需要通过mysqld_safe方式启动，service方式底层也是通过mysqld_safe方式启动的。
确认MySQL服务是否加载了jemalloc
lsof -p `ps -ef|grep mysqld`|grep -i jemalloc
注:LD_PRELOAD是环境变量，用于动态库的加载，动态库加载的优先级最高，动态库加载的优先级最高，一般加载顺序为LD_PRELOAD > LD_LIBRARY_PATH => /etc/ld.so.cache => /lib => /usr/lib ,详细可参考官网:https://dev.mysql.com/doc/refman/8.0/en/mysqld-safe.html#option_mysqld_safe_malloc-lib
方式三:写进启动命令:
示例:LD_PRELOAD=/usr/lib64/libjemalloc.so.1 /opt/mysql/base/5.7.25/bin/mysqld --defaults-file=/etc/my.cnf --daemonize --pid-file=/opt/mysql/data/3310/mysqld.pid --user=mysql --socket=/opt/mysql/data/3310/mysqld.sock --port=3310
方式四:写进mysql.service文件:
在mysql.service文件中添加EnvironmentFile=-/etc/sysconfig/mysql选项，/etc/sysconfig/mysql文件内容为LD_PRELOAD=/usr/lib64/libjemalloc.so.1，也可以直接使用如下方式Environment="LD_PRELOAD=/usr/lib64/libjemalloc.so.1"
注:通过mysql.service文件对应的是systemctl启动服务方式，如果/etc/sysconfig/mysql文件不存在，则使用默认内存分配器。
茴香豆的茴有四种写法这且是一个玩笑，但是jemalloc的不同加载方式却是为了更好的适应不同场景而做出的改变，如果通过mysql_safe或者service方式启动MySQL,则可以选择方式二，如果通过mysqld方式启动，则可以选择其他方式，另外，考虑操作方便性和安全性，建议的是使用方式四，另外，配置好不要忘了进行验证，可以通过cat /proc/${pid of mysqld}/status |grep VmRSS命令查看MySQL服务占用的内存使用情况，看看效果如何。
sys库存储过程和函数恢复
案例说明
这是之前遇到的一个问题，客户MySQL实例的sys库损坏，需要进行修复，在经过查阅官网及查阅资料并经过验证后，提供两种方式用于参考，这里也记录一下。
方式一：
#查看存储过程
mysql> select `name` from mysql.proc where db = 'sys' and `type` = 'PROCEDURE';
+-------------------------------------+
| name |
+-------------------------------------+
| create_synonym_db |
| diagnostics |
| execute_prepared_stmt |
| ps_setup_disable_background_threads |
| ps_setup_disable_consumer |
| ps_setup_disable_instrument |
| ps_setup_disable_thread |
| ps_setup_enable_background_threads |
| ps_setup_enable_consumer |
| ps_setup_enable_instrument |
| ps_setup_enable_thread |
| ps_setup_reload_saved |
| ps_setup_reset_to_default |
| ps_setup_save |
| ps_setup_show_disabled |
| ps_setup_show_disabled_consumers |
| ps_setup_show_disabled_instruments |
| ps_setup_show_enabled |
| ps_setup_show_enabled_consumers |
| ps_setup_show_enabled_instruments |
| ps_statement_avg_latency_histogram |
| ps_trace_statement_digest |
| ps_trace_thread |
| ps_truncate_all_tables |
| statement_performance_analyzer |
| table_exists |
+-------------------------------------+
26 rows in set (0.03 sec)
#查看函数
mysql> select `name` from mysql.proc where db = 'sys' and `type` = 'FUNCTION';
+----------------------------------+
| name |
+----------------------------------+
| extract_schema_from_file_name |
| extract_table_from_file_name |
| format_bytes |
| format_path |
| format_statement |
| format_time |
| list_add |
| list_drop |
| ps_is_account_enabled |
| ps_is_consumer_enabled |
| ps_is_instrument_default_enabled |
| ps_is_instrument_default_timed |
| ps_is_thread_instrumented |
| ps_thread_account |
| ps_thread_id |
| ps_thread_stack |
| ps_thread_trx_info |
| quote_identifier |
| sys_get_config |
| version_major |
| version_minor |
| version_patch |
+----------------------------------+
22 rows in set (0.00 sec)
#模拟sys库异常
mysql> DROP DATABASE sys;
Query OK, 101 rows affected (0.34 sec)
#验证是否删除
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| universe |
+--------------------+
5 rows in set (0.00 sec)
#使用mysql_upgrade命令进行修复
[root@localhost ~]# /opt/mysql/base/5.7.25/bin/mysql_upgrade -uroot -p'xxx' -S /opt/mysql/data/3306/mysqld.sock --upgrade-system-tables --force
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
The --upgrade-system-tables option was used, databases won't be touched.
#官网描述
For upgrades, mysql_upgrade installs the sys schema if it is not installed, and upgrades it to the current version otherwise. To permit this behavior to be suppressed, mysql_upgrade has a --skip-sys-schema option.
mysql_upgrade returns an error if a sys schema exists but has no version view, on the assumption that absence of this view indicates a user-created sys schema. To upgrade in this case, remove or rename the existing sys schema first.
sys schema objects have a DEFINER of 'mysql.sys'@'localhost'. Use of the dedicated mysql.sys account avoids problems that occur if a DBA renames or removes the root account.
方式二
下载代码地址:https://github.com/mysql/mysql-sys
[root@localhost mysql-sys-master]# ll
-rw-r--r-- 1 root root 782 Jul 7 2016 after_setup.sql
-rw-r--r-- 1 root root 891 Jul 7 2016 before_setup.sql
-rw-r--r-- 1 root root 17987 Jul 7 2016 COPYING
drwxr-xr-x 2 root root 4096 Jul 7 2016 functions
-rw-r--r-- 1 root root 7842 Jul 7 2016 generate_sql_file.sh
-rw-r--r-- 1 root root 707 Jul 7 2016 LICENSE
drwxr-xr-x 3 root root 19 Jul 7 2016 mysql-test
-rw-r--r-- 1 root root 20596 Jul 7 2016 NEWS.md
drwxr-xr-x 2 root root 4096 Jul 7 2016 procedures
-rw-r--r-- 1 root root 270716 Jul 7 2016 README.md
-rw-r--r-- 1 root root 7613 Jul 7 2016 sys_56.sql
-rw-r--r-- 1 root root 8366 Jul 7 2016 sys_57.sql
drwxr-xr-x 2 root root 85 Jul 7 2016 tables
drwxr-xr-x 2 root root 47 Jul 7 2016 templates
drwxr-xr-x 2 root root 82 Jul 7 2016 triggers
drwxr-xr-x 4 root root 47 Jul 7 2016 views
##禁用sql_log_bin，不记录到binlog日志中
mysql> source /root/sys_sql/mysql-sys-master/before_setup.sql;
Query OK, 0 rows affected (0.01 sec)
#创建sys库，会自动调用其他文件夹中的sql语句进行表、视图、存储过程、触发器的创建，注意，此处是5.7版本，根据实际版本选择sql文件
mysql> source /root/sys_sql/mysql-sys-master/sys_57.sql;
Query OK, 0 rows affected (0.01 sec)
#开启sql_binlog_bin,将sql_log_bin恢复到操作前的状态
mysql> source /root/sys_sql/mysql-sys-master/after_setup.sql;
Query OK, 0 rows affected (0.00 sec)
以上就是两种修复sys库的方法啦，后续遇到sys库损坏的问题再也不用慌了，是不是又多了一项关键时刻保住饭碗的技能。
今日份总结
本周的4个小Case就到此结束了，从character_set_client相关参数的调整到MySQL权限管控的注意事项，以及sys库损坏的修复方式，核心的是针对MySQL内存问题更换jemalloc内存分配器的全流程设置，每个小Case可以当作一个独立的的案例进行学习，不仅可以在遇到问题时提供帮助，面试时也不怕没案例了，好了，希望小伙伴们阅读完能有所收获，也就不枉费我辛辛苦苦写笔记了,更多好的案例和知识点等待我的后续，哈哈。
温馨提示
原文作者：DBA修行之路；如有侵权，请联系管理员删除。
上一篇：
MySQL中强烈推荐你开启的监控项
下一篇：
最近遇到的几个MySQL小问题
请登录后发表评论
登录
全部评论
