mysql 常见配置

基本配置：

1. 配置文件路径：
   /etc/my.cnf
2. 修改字符集：
   character-set-server=utf8mb4
3. 修改端口号：
   port=3306
4. 关闭严格模式：
   sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
5. 开启慢查询日志：

   slow_query_log=1
   slow_query_log_file=/var/log/mysql_slow.log

6. 开启二进制日志：

   server-id=1
   log-bin=日志名

7. 执行语句：
   mysql -uroot -p -e 'select * from mysql.user'
8. 执行文件内命令：
   mysql -uroot -p < test.sql
9. 备份：

   mysqldump  -uroot  -p  --all-databases  >  backup.sql
   mysqldump  -uroot  -p  test  >  backup.sql

10. 还原：
    mysql -uroot -p -D test < backup.sql --default-character-set=utf8
11. 查询全局变量：
    SHOW GLOBAL VARIABLES [LIKE "%xxx%"];
12. 设置全局变量：
    SET GLOBAL 变量名 = 变量值;

用户权限操作：

1. 允许使用弱密码：

   SET GLOBAL validate_password_policy = 0;
   SET GLOBAL validate_password_length = 6;  # 密码长度为 6

2. 创建用户：
   CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';
3. 设置密码：
   SET PASSWORD FOR 'test'@'localhost' = PASSWORD('123456');
4. 删除用户：
   DROP USER 'test'@'localhost';
5. 查看所有用户：
   SELECT DISTINCT CONCAT('''',user,'''@''',host,'''') AS users FROM mysql.user;
6. 授予指定权限：
   GRANT SELECT,INSERT,UPDATE ON db_zuqiuzhiye.* TO 'test'@'localhost';
7. 授予所有权限：
   GRANT ALL PRIVILEGES ON db_zuqiuzhiye.* TO 'test'@'localhost';
8. 撤销指定权限：
   REMOVE SELECT,INSERT,UPDATE ON db_zuqiuzhiye.* FROM 'test'@'localhost';
9. 撤销所有权限：
   REMOVE ALL PRIVILEGES ON db_zuqiuzhiye.* FROM 'test'@'localhost';
10. 查看用户权限：

    # 查看当前用户的权限
    SHOW GRANTS;
    # 查看指定用户的权限
    SHOW GRANTS FOR 'test'@'localhost';

11. 刷新权限（别忘了这一步操作）：
    FLUSH PRIVILEGES;

主从复制：

1. 主服务器配置：
   cat /etc/my.cnf

   [mysqld]
   server-id=1
   log-bin=master
   ...

2. 从服务器配置：
   cat /etc/my.cnf

   [mysqld]
   server-id=2
   slave-skip-errors=all
   ...

3. 主服务器设置：
   1. 进入命令行：
      mysql -uroot -p
   2. 查看主服务器状态：
      show master status;
      
4. 从服务器设置：
   1. 进入命令行：
      mysql -uroot -p
   2. 配置：

      change master to
      master_host='xx.xx.xx.xx',
      master_port=xxxx,
      master_user='root',
      master_password='xxxxxx',
      master_log_file='xxxxx.xxx',
      master_log_pos=xxx;

   3. 开启从模式：
      start slave;
   4. 查看从服务器状态：
      show slave status\G
5. 测试：
   1. 主：
      create database test;
   2. 从：
      show databases;

分区：

 CREATE TABLE `test`  (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 PRIMARY KEY (`id`) USING BTREE
 ) 
 ENGINE = InnoDB 
 AUTO_INCREMENT = 1 
 CHARACTER SET = utf8 
 COLLATE = utf8_general_ci 
 PARTITION BY HASH(id)
 PARTITIONS 4;

二进制日志使用：

1. 查看日志状态：
   show binlog events\G
   
2. 查看 binlog 的位置：
   SHOW VARIABLES LIKE '%datadir%';
3. 备份：
   1. 以位置备份：
      mysqlbinlog --start-position=xxx --stop-position=xxx /path/to/bin-log > test.sql
   2. 以时间备份：
      mysqlbinlog --start-datetime='YYYY-mm-dd HH:ii:ss' /path/to/bin-log > test.sql
4. 还原：
   1. 以位置还原：
      mysqlbinlog --start-position=xxx --stop-position=xxx /path/to/bin-log | mysql -uroot -p
   2. 以时间还原：
      mysqlbinlog --start-datetime='YYYY-mm-dd HH:ii:ss' /path/to/bin-log | mysql -uroot -p

使用 mysql5.7 自带中文全文索引：

1. 创建：

    CREATE TABLE `test` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `title` varchar(255) DEFAULT '',
      `content` text,
      PRIMARY KEY (`id`),
      FULLTEXT KEY `title` (`title`,`content`)  WITH PARSER `ngram`
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 配置（分词数量）：
   ngram_token_size=2
3. 使用：
   SELECT * FROM test WHERE MATCH (title,content) against ('张三');
   

使用 mysql5.7 自带 json 类型：

1. 创建：

    CREATE TABLE `test_json` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `content` json DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 使用：
   SELECT * FROM test_json WHERE content->'$.name'='lee';