mysql

# 查带中文的记录
select * from translation where content not REGEXP "[\u0391-\uFFE5]" limit 100

# ngram全文索引
show variables like '%token%';
配置文件加上 ngram_token_size=1

alter table person 
add fulltext index full_name (name) with parser ngram

select name from person where match(name) against ('刘' in boolean mode)

# mac 重启mysql
brew services restart mysql

# 如何知道mysql日志在哪个目录?
mysql -uroot -se "SELECT @@datadir"

# mac 修改mysql配置文件
vim /usr/local/etc/my.cnf
sql_mode=''
max_connections = 2048
innodb_buffer_pool_size = 8G
innodb_log_file_size = 4G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DSYNC
query_cache_size = 0
interactive_timeout = 30
lock_wait_timeout = 60
wait_timeout = 30

# 启动redis server
redis-server /usr/local/etc/redis.conf

# 8.0坑
连上后登不进去, 报错
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql_upgrade -u root -p;

密码报错
Next PDOException: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client [caching_sha2_password] in /vendor/yiisoft/yii2/db/Connection.php:687
在my.cnf加上:
default_authentication_plugin=mysql_native_password

# 调度器
CREATE DEFINER=`root`@`localhost` EVENT `定时修改群封禁用户状态` ON SCHEDULE EVERY 1 MINUTE STARTS '2018-11-13 10:35:43' ON COMPLETION PRESERVE ENABLE DO update chat_participant set until_date=0, kicked=0 where type=0 and until_date>0 and UNIX_TIMESTAMP(now())>=until_date

# 查看调度器状态
show variables like '%event_scheduler%';

# 打开调度器
SET GLOBAL event_scheduler = ON;
# my.cnf
event_scheduler = 1 或者 ON

# 查看调度器线程
show processlist;

# 查看最大连接数
show variables like '%max_connections%';

# 查看当前连接状态
show status like '%connec%';
Connections	917 总共连接次数
Threads_connected	10 当前实际连接数

# 打开sql日志
show variables like 'general%';
set global general_log=1;

# 阿里innodb配置
# This MySQL options file was generated by innobackupex.
# The MySQL server
[mysqld]
innodb_checksum_algorithm=crc32
innodb_log_checksum_algorithm=strict_crc32
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1048576000
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=./
innodb_undo_tablespaces=0
server_id=2802990161
redo_log_version=1
server_uuid=edba05ba-73c3-11e9-bba2-506b4b4b3472
master_key_id=0
innodb_encrypt_algorithm=AES_256_CBC