MySQL常用命令

建库

CREATE DATABASE `databaseName` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

创建用户

CREATE USER 'userName'@'localhost' IDENTIFIED BY 'eKw5Ap9EBwjIRc_AO2!Y84';

-- userName： 你将创建用户的名字
-- localhost ： 本地用户，如果想让该用户可以**从任意远程主机登陆**，可以使用`%`
-- password ： 密码

授权

GRANT all ON databaseName.* TO 'userName'@'%';

GRANT ALL PRIVILEGES ON databaseName.* TO 'userName'@'ip' IDENTIFIED BY 'password' WITH GRANT OPTION;

-- 授权后必须刷新权限才能生效
flush privileges;

查看权限

-- '\G' 表示以竖向的形式展示, 方便查看
select * from mysql.user where user='userName'\G;

删除用户

DROP USER 'username'@'localhost';

撤销权限

REVOKE all ON databaseName.tableName FROM 'userName'@'localhost';

还原数据

source workDispatcher.sql

增加字段

ALTER TABLE tableName ADD COLUMN columnName VARCHAR(20) DEFAULT NULL COMMENT '';

修改密码

set password for '用户名'@'%' = password('新密码');
//或者
ALTER USER 'root'@'%' IDENTIFIED BY '123456';

flush privileges;

备份数据库

mysqldump -u root -p DB_NAME > /root/DB_NAME.sql

binlog日志相关

# 查看当前的日志名称
show master status;

# 查看binlog日志
mysqlbinlog -v BIN_LOG_FILE_NAME
--start-positon=11001
--stop-position=11004
--start-datetime='2022-10-10 23:27:27'
--start-datetime='2022-10-10 23:29:00'

# 使用binlog日志还原数据
# 还原之前最好先使用 flush logs 开启新的binlog日志，以便确定业务数据的最后边界
mysqlbinlog -v --start-positon=11001 --stop-position=11004 BIN_LOG_FILE_NAME | mysql -u root -p

重新统计索引扫描行数

analyze table tableName

强制使用索引

-- 强制使用索引index_a
select * from t force index(index_a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

配置主从

1、master修改配置文件/etc/my.cnf，在[mysqld]下增加

log-bin=mysql-bin
server-id=1
binlog-format=row
binlog-do-db=lyzz_image

2、master创建用户，专门用于复制操作，用于从节点连接主节点时使用，slave_ip为从节点IP

-- mysql 5.7 默认身份验证插件default_authentication_plugin是：mysql_native_password
CREATE USER 'master'@'slave_ip' IDENTIFIED WITH mysql_native_password BY '123456';

-- mysql 8.0 默认身份验证插件default_authentication_plugin是：caching_sha2_password
CREATE USER 'master'@'slave_ip' IDENTIFIED WITH caching_sha2_password BY '123456';

-- 为master用户授权
GRANT REPLICATION SLAVE ON *.* TO 'master'@'slave_ip';

-- 刷新权限
flush privileges;

-- 查看主节点binlog日志信息，记录FILE及POSITION
show master status;

3、slave修改配置文件/etc/my.cnf，在[mysqld]下增加

log-bin=mysql-bin
server-id=2
binlog-format=row
replicate-do-db=lyzz_image

4、在Slave节点上设置主节点参数，然后开启主从同步

-- 设置主节点参数
CHANGE MASTER TO MASTER_HOST = '${MASTER_IP}',
    MASTER_USER ='master',
    MASTER_PASSWORD ='${MASTER_PASSWORD}',
    MASTER_LOG_FILE ='${FILE}',
    MASTER_LOG_POS = '${POSITION}',
    get_master_public_key =1;

-- 开启同步
start slave;

-- 设置失败时清除同步配置
reset slave all;

5、查看主从同步状态show slave status;，确认数据一致，且Slave_IO_Running，Slave_SQL_Running为Yes，Last_IO_Error为空

Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.227
Master_User: master
Master_Port: 3306
Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: lyzz_arch
Last_IO_Error: 
Get_master_public_key: 1

6、其他配置：

不同步哪个数据库（在master[mysqld]）：binlog-ignore-db=mysql
只同步哪个数据库（在master[mysqld]）：binlog-do-db=lyzz_arch，其他库不同步
接收哪个库的binlog日志（在slave[mysqld]）：replicate-do-db=lyzz_arch