Mysql常用命令

一、创建用户并开启远程服务

查看数据库用户：

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

创建用户并开启远程访问：

CREATE USER 'canal'@'%' IDENTIFIED BY 'canal123';
GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' IDENTIFIED BY 'canal123' WITH GRANT OPTION; 
FLUSH PRIVILEGES;

创建只读权限用户：

CREATE USER 'readonly'@'%' IDENTIFIED BY '123456';

GRANT SELECT ON *.* TO 'readonly'@'%';

FLUSH PRIVILEGES;

二、数据库主从命令

1、查看数据库binlog日志：

• 查看数据库主从状态：

show master status \G

• 查看从库状态

show slave status \G

查看binlog列表：show binary logs；
查看最新binlog: show master status;

查看指定binlog内容：show binlog events in 'mysql-bin.000002';

• mysqlbinlo日志导出命令：

mysqlbinlog --base64-output=decode-rows -v --start-datetime='2021-01-27 12:12:00' --stop-datetime='2021-01-27 12:13:00' -d axc_base /data/3307/logs/bin/mysql-bin.000013 >user_error.sql

mysqlbinlog --base64-output=decode-rows -v --start-position=469853132 --stop-position=539366446 -d mvs_base /data/mysql/binlog/33060-bin.000917 > user_error.sql

• 在master上删除一条记录，而slave上找不到

解决办法：
通过命令直接跳过同步
mysql>stop slave;
mysql>set global sql_slave_skip_counter=1;
mysql>start slave;

或者先停止从库，查看不一样的数据，把从库恢复到和主库一致

三、数据库操作字段命令

• 创建数据库

CREATE DATABASE `mvs_base` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

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

• 查看数据库字符集

use mvs_base;

show variables like '%character%';
show variables like 'collation%';

• 导入数据库脚本

source  /data/mvs_base.sql;

• 导出数据库表脚本

mysqldump -uroot -proot mvs_202112  mvs_steps_20 > /mvs_steps_20.sql;

• 从库导出数据库脚本

mysqldump -uroot -p  --master-data=2 --single-transaction -B mvs_base   > /data/backup/mvs_base.sql

• 在指定字段后添加字段

alter table pf_admin_login_log add  COLUMN  `day_count` int(8) DEFAULT '0' COMMENT '当日登录次数' after version;

• 设置指定字段默认值

alter table mvs_spo2_2 alter column archived set default 0;

• 设置指定字段类型及默认值

ALTER TABLE mvs_hr_19 MODIFY COLUMN `archived` tinyint(3) NOT NULL DEFAULT '0' COMMENT '状态（0：正常，1：已删除）';

• 添加唯一索引

ALTER TABLE `mvs_warn_history` ADD UNIQUE INDEX `USER_ID_WARN_UNIQUE_INDEX` (`user_id`, `warn_datetime`) USING BTREE ;

注意：数据库中字段设置类型为tinyint(1),在转换为实体类时是boolean类型，因为数据库没有boolean,把tinyint(1)默认为boolean类型，1为true,0为false

四、数据库查看参数

查看mysql最大连接数

show variables like '%max_connection%'

五、数据库查询导出文件

select * from mb_user_info into outfile "/data/file/user.xls"

六、查看数据库占用空间

1、查看数据库表占用空间（包含数据大小及数据索引大小）及数据条数

select TABLE_NAME,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024,2),'kB') as data,TABLE_ROWS from TABLES where TABLE_SCHEMA='mvs_202202';

2、查看数据库下所有表的总占用空间（包含数据大小及数据索引大小）

select concat(round(sum((INDEX_LENGTH+DATA_LENGTH)/1024/1024/1024),2),'GB') as total_data from TABLES where table_schema= 'mvs_202202';

linux下占用空间：

表占用空间：

 库占用空间：

 以上可知：

单表最大6g,单条数据根据表结构不同最大1.3k,最小0.18k,平均每天占用磁盘空间17G左右

表数据日最高：血压表近6W，佩戴状态表420W，心率表520W，呼吸率表560W，睡眠表27W，血氧表500W，运动表1500W，温度表600W，轨迹表16W