系统运维 - mysql

开启全文搜索 fulltext

开启全文索引需要使用 MySQL 版本大于 5.7，由于该配置比较繁琐，所以所有系统默认不支持，需要通过运维手动开启。

全文索引是为了解决需要基于相似度的查询，而不是精确数值比较。

可以通过 SQL 命令查看当前配置的最小搜索长度（分词长度）：

SHOW VARIABLES LIKE 'ft%';

变量名	描述
`ft_boolean_syntax`	全文搜索的布尔语法
`ft_max_word_len`	最大词长度
`ft_min_word_len`	最小词长度
`ft_query_expansion_limit`	查询扩展限制
`ft_stopword_file`	停用词文件

全文索引的相关参数都无法进行动态修改，必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1，首先打开 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下内容：

[mysqld]
innodb_ft_min_token_size = 1
ft_min_word_len = 1

配置完后重启 MySQL 服务器。

完成后可使用以下脚本测试全文搜索：

## 查看索引
SHOW INDEX FROM config;
## 删除全文索引
ALTER TABLE config DROP INDEX xxx;
## 创建全文索引，注意该字段只能创建一个全文索引，多余的使用以上的删除语句删除
ALTER TABLE config ADD FULLTEXT(value) WITH PARSER ngram;

## 使用以下语句测试查询
SELECT `id`,`key`,`value`,MATCH(`value`) AGAINST('测试查询') AS _score
    FROM config
    WHERE MATCH(`value`) AGAINST('测试查询')
    ORDER BY _score DESC;

备份与恢复 mysqldump

备份

#!/bin/bash

PASSWORD="123456"
DIRECTORY="/mysql-backup"

databases=$(mysql -u root -p$PASSWORD -e "SHOW DATABASES;" | grep -Ev '^(information_schema|performance_schema|mysql|sys)$')
for db in $databases; do
    echo "backup: $db"
    temp_file=$(mktemp)
    mysqldump -u root -p$PASSWORD --databases "$db" > "$temp_file"
    echo "CREATE DATABASE IF NOT EXISTS \`$db\`;" > "${db}_backup.sql"
    echo "USE \`$db\`;" >> "${db}_backup.sql"
    cat "$temp_file" >> "$DIRECTORY/${db}_backup.sql"
    rm "$temp_file"
done

恢复

#!/bin/bash

PASSWORD="123456"
DIRECTORY="/mysql-backup"

for sql_file in "$DIRECTORY"/*.sql; do
    if [[ -f "$sql_file" ]]; then
        echo "正在导入文件: $sql_file"
        mysql -u root -p$PASSWORD mysql < "$sql_file"
    fi
done