Varobj

2019-07-19

MySQL常用操作



记录 MySQL 相关的一些问题,目前使用版本 5.7.x, 断断续续更新..

日志

MySQL服务日志包含几种

查询日志慢查询日志

mysql root@localhost:mysql> select @@slow_query_log
+------------------+
| @@slow_query_log |
+------------------+
| 1                |
+------------------+
mysql root@localhost:mysql> select @@slow_query_log_file
+--------------------------------+
| @@slow_query_log_file          |
+--------------------------------+
| /var/lib/mysql/my-php-slow.log |
+--------------------------------+
mysql root@localhost:mysql> select @@general_log
+---------------+
| @@general_log |
+---------------+
| 1             |
+---------------+
mysql root@localhost:mysql> select @@general_log_file
+---------------------------+
| @@general_log_file        |
+---------------------------+
| /var/lib/mysql/my-php.log |
+---------------------------+
mysql root@localhost:mysql> select @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE,TABLE   |
+--------------+

如果general_logslow_query_log都为0,可以通过服务开启时设置参数--general_log--slow_query_log

systemctl set-environment MYSQLD_OPTS='--log_output=TABLE,FILE --general_log --slow_query_log'

如果选择TABLE存储查询日志,对应的表存储引擎为CSV,非常方便导出,直接复制表文件即可。

可以开启log_queries_not_using_indexes设置为 1 表示记录没有使用索引的查询到慢日志中

mysql root@localhost:mysql> select @@log_queries_not_using_indexes
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
| 1                               |
+---------------------------------+

当开启log_queries_not_using_indexes,会导致慢日志增长过快,可以通过log_throttle_queries_not_using_indexes来限制频率,默认 0 表示不限制。参考文档

一个查询是否记录到慢日志,还需要判断检查的行数是否大于min_examined_row_limit配置,默认 0

配置

# 查询 my.cnf 配置文件的位置
$ mysql --help|grep 'Default options' -A 1

# 查询 my.cnf 相关配置
$ mysqld --verbose --help

# 开启 binlog, 编辑 my.cnf 添加以下项
log_bin=mysql-bin      # =log_file 默认 log_file 在数据目录
server_id=1            # servers 中的唯一 id ,开启 log_bin 时必须指定

# localhost 可以修改成 % 匹配所有 ip, 或者指定 ip
create user 'your'@'localhost' identified by 'password';

# *.* 表示 database.table, all privileges 可替换成 create select 等
grant all privileges on *.* TO 'your'@'localhost';

# 刷新权限
flush privileges;

# 如果用户密码忘记了,修改
alter user 'your'@'localhost' identified by 'password';

# 如果遇到 Your password does not satisfy the current ,需要查看密码校验策略
show variables like 'validate_password%';

主从同步配置

# Master 机器配置
# Step 1. 编辑 MySQL 配置文件 my.cnf
$ vim /etc/my.cnf

# 去掉注释 log_bin (后面可以加 binlog 日志文件,默认是数据目录上)
log_bin=mysql-bin
server_id=1 # 必须加此项,否则会报错,集群中唯一id
bind_address=192.168.0.231 # 绑定本地 ip

# Step 2. 重启服务,进入 MySQL,创建用户
# 创建 slave1 用户,并指定该用户只能在 slave1 的机器上登录。
mysql$ CREATE USER 'slave1'@'192.168.0.232' IDENTIFIED BY 'slavepass';
Query OK, 0 rows affected (0.00 sec)

# 为 slave1 赋予 REPLICATION SLAVE 权限。
mysql$ GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'192.168.0.232';
Query OK, 0 rows affected (0.00 sec)

# Step 3. 记录当前点加锁导出数据
mysql$ show master status\G
*************************** 1. row ***************************
File: dev01-bin.000006
Position: 874
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

# 记录上面的 File & Position
mysql$ flush tables with read lock;

# 加只读锁,不能写,后面导出数据
$ mysqldump -u root -p --all-databases --master-data > dbdump.sql

# 导出数据后,进入 MySQL 释放只读锁
mysql$ unlock tables;
# Slave1 机器配置
# Step 1. 编辑 MySQL 配置文件 my.cnf 和 Master 一样
# Step 2. scp master 机器的数据到 salve 并同步数据,保持当前一致
$ mysql -u root -p < /path/to/dbdump.sql

mysql$ STOP SLAVE;

# 建立到 Master 机器的连接,注意 *_FILE *_POS 对应 Master 机器的数据
mysql$ CHANGE MASTER TO
 MASTER_HOST='192.168.0.231',
 MASTER_USER='slave1',
 MASTER_PASSWORD='slavepass',
 MASTER_LOG_FILE='dev01-bin.000006',
 MASTER_LOG_POS=874;

# 开始
mysql$ START SLAVE;

# 查看当前 slave 状态,有错误会显示在这里
mysql$ show slave status\G

降低密码策略等级

set global validate_password_policy=0;
set global validate_password_number_count=0;
set global validate_password_special_char_count=0;
set global validate_password_mixed_case_count=0;
set global validate_password_length=4;

修改默认时区

[mysqld]
default-time-zone='+08:00'