MySQL基础命令与集群操作指南-恒创新域建站

★ FEATURED ARTICLE

MySQL基础命令与集群操作指南文档说明涵盖从基础数据库操作到高可用集群维护的常用命令。适用于数据库开发人员、运维工程师及系统管理员帮助快速掌握 MySQL 日常管理、性能监控、用户权限、字符集配置、Binlog 管理以及 InnoDB 集群Group Replication / MySQL Shell的恢复与运维操作。✅适用版本MySQL 5.7 / 8.x MySQL Shell 8. 快速命令速查表操作命令创建数据库CREATE DATABASEdbCHARACTER SET utf8mb4;删除数据库DROP DATABASE IF EXISTSdb;使用数据库USE db;创建表CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(50));查表结构DESC t;加列ALTER TABLE t ADD COLUMN age INT;改类型ALTER TABLE t MODIFY COLUMN name VARCHAR(100);改列名ALTER TABLE t RENAME COLUMN old TO new;删列ALTER TABLE t DROP COLUMN age;改表名ALTER TABLE t RENAME TO t_new;删表DROP TABLE IF EXISTS t;一、数据库操作Database Operations操作SQL 语法说明创建数据库CREATE DATABASE 数据库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;推荐使用utf8mb4支持完整 UTF-8如 emoji删除数据库DROP DATABASE [IF EXISTS] 数据库名;删除整个数据库不可恢复慎用查看所有数据库SHOW DATABASES;显示当前 MySQL 实例中所有数据库使用数据库USE 数据库名;切换当前操作的数据库示例创建与删除数据库-- 1. 创建数据库支持中文、emojiCREATEDATABASEschool_dbCHARACTERSETutf8mb4COLLATEutf8mb4_general_ci;-- 2. 查看所有数据库SHOWDATABASES;-- 3. 使用该数据库USEschool_db;-- 4. 删除数据库谨慎DROPDATABASEIFEXISTSschool_db;✅建议数据库名使用小写字母下划线避免特殊字符。二、表操作Table Operations操作SQL 语法说明创建表CREATE TABLE 表名 (列名数据类型 [约束]);定义字段、主键、默认值等查看表结构DESCRIBE 表名;或DESC 表名;快速查看字段信息查看建表语句SHOW CREATE TABLE 表名;查看完整建表 SQL含引擎、字符集删除表DROP TABLE [IF EXISTS] 表名;彻底删除表及数据重命名表ALTER TABLE 原表名 RENAME TO 新表名;修改表名示例创建students表-- 1. 创建表CREATETABLEstudents(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT主键ID,nameVARCHAR(50)NOTNULLCOMMENT姓名,ageINTCHECK(age0)COMMENT年龄,emailVARCHAR(100)UNIQUECOMMENT邮箱,class_nameVARCHAR(30)DEFAULT未分配COMMENT班级,create_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间)ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT学生表;-- 2. 查看表结构DESCRIBEstudents;-- 3. 查看完整建表语句SHOWCREATETABLEstudents;-- 4. 重命名表ALTERTABLEstudentsRENAMETOpupils;-- 5. 删除表DROPTABLEIFEXISTSpupils;三、列操作Column Operations操作SQL 语法说明添加列ALTER TABLE 表名 ADD COLUMN 列名数据类型 [约束];新增字段修改列类型ALTER TABLE 表名 MODIFY COLUMN 列名新类型;修改字段类型如长度重命名列ALTER TABLE 表名 RENAME COLUMN 旧名 TO 新名;MySQL 8.0 支持删除列ALTER TABLE 表名 DROP COLUMN 列名;删除字段数据一并删除⚠️ 注意MODIFY COLUMN不能改字段名低版本 MySQL 用CHANGE COLUMN可同时改名和类型。示例对students表进行列操作-- 1. 添加新列ALTERTABLEstudentsADDCOLUMNphoneVARCHAR(15)COMMENT手机号;-- 2. 修改字段长度ALTERTABLEstudentsMODIFYCOLUMNnameVARCHAR(100)NOTNULL;-- 3. 重命名列MySQL 8.0ALTERTABLEstudentsRENAMECOLUMNclass_nameTOclass;-- 4. 删除列ALTERTABLEstudentsDROPCOLUMNphone;-- 5. 低版本兼容改名改类型MySQL 8.0ALTERTABLEstudents CHANGECOLUMNclass class_nameVARCHAR(30);四、数据操作Data Operations4.1 数据查询查询表记录数SELECTCOUNT(*)FROM表名WHERE条件;示例按时间筛选数据SELECTCOUNT(*)FROMt_abilityWHEREupdate_time2025-06-20 10:06:40;4.2 查询数据库大小SELECTTABLE_SCHEMAASDatabase,ROUND(SUM(data_length)/1024/1024,2)ASData Size (MB),ROUND(SUM(index_length)/1024/1024,2)ASIndex Size (MB),ROUND(SUM(data_lengthindex_length)/1024/1024,2)ASTotal Size (MB)FROMinformation_schema.tablesWHERETABLE_SCHEMANOTIN(information_schema,performance_schema,mysql,sys)ANDTABLE_TYPEBASE TABLEGROUPBYTABLE_SCHEMAORDERBYSUM(data_lengthindex_length)DESC;4.3 查询每张表占用空间SELECTTABLE_NAMEASTable,ROUND(data_length/1024/1024,2)ASData Size (MB),ROUND(index_length/1024/1024,2)ASIndex Size (MB),ROUND((data_lengthindex_length)/1024/1024,2)ASTotal Size (MB)FROMinformation_schema.tablesWHERETABLE_SCHEMAyour_database_nameANDTABLE_TYPEBASE TABLEORDERBY(data_lengthindex_length)DESC;4.4 查询所有表占用空间及所有库总计SELECTDatabase,Data Size,Index Size,Total SizeFROM(-- 第一部分各库明细SELECTTABLE_SCHEMAASDatabase,-- 智能判断 Data SizeCASEWHENSUM(data_length)1024*1024*1024THENCONCAT(ROUND(SUM(data_length)/1024/1024/1024,2), GB)ELSECONCAT(ROUND(SUM(data_length)/1024/1024,2), MB)ENDASData Size,-- 智能判断 Index SizeCASEWHENSUM(index_length)1024*1024*1024THENCONCAT(ROUND(SUM(index_length)/1024/1024/1024,2), GB)ELSECONCAT(ROUND(SUM(index_length)/1024/1024,2), MB)ENDASIndex Size,-- 智能判断 Total SizeCASEWHENSUM(data_lengthindex_length)1024*1024*1024THENCONCAT(ROUND(SUM(data_lengthindex_length)/1024/1024/1024,2), GB)ELSECONCAT(ROUND(SUM(data_lengthindex_length)/1024/1024,2), MB)ENDASTotal Size,0ASis_total,-- 用于排序的标记位SUM(data_lengthindex_length)ASraw_bytes-- 用于排序的原始字节数FROMinformation_schema.tablesWHERETABLE_SCHEMANOTIN(information_schema,performance_schema,mysql,sys)ANDTABLE_TYPEBASE TABLEGROUPBYTABLE_SCHEMAUNIONALL-- 第二部分所有库总计SELECT【所有库总计】ASDatabase,CASEWHENSUM(data_length)1024*1024*1024THENCONCAT(ROUND(SUM(data_length)/1024/1024/1024,2), GB)ELSECONCAT(ROUND(SUM(data_length)/1024/1024,2), MB)ENDASData Size,CASEWHENSUM(index_length)1024*1024*1024THENCONCAT(ROUND(SUM(index_length)/1024/1024/1024,2), GB)ELSECONCAT(ROUND(SUM(index_length)/1024/1024,2), MB)ENDASIndex Size,CASEWHENSUM(data_lengthindex_length)1024*1024*1024THENCONCAT(ROUND(SUM(data_lengthindex_length)/1024/1024/1024,2), GB)ELSECONCAT(ROUND(SUM(data_lengthindex_length)/1024/1024,2), MB)ENDASTotal Size,1ASis_total,SUM(data_lengthindex_length)ASraw_bytesFROMinformation_schema.tablesWHERETABLE_SCHEMANOTIN(information_schema,performance_schema,mysql,sys)ANDTABLE_TYPEBASE TABLE)ASresultORDERBYis_totalASC,raw_bytesDESC;五、用户权限管理User Privileges5.1 创建用户CREATEUSER用户名%IDENTIFIEDBYxxxxxx;%表示允许从任意主机连接生产环境建议限制为具体 IP如10.x.%5.2 授权操作-- 授予所有权限GRANTALLPRIVILEGESON*.*TO用户名%WITHGRANTOPTION;-- 授予只读权限GRANTSELECTON*.*TO用户名%;-- 授予监控权限GRANTPROCESS,REPLICATIONCLIENT,SELECTON*.*TOmonitor10.%;-- 立即生效FLUSHPRIVILEGES;5.3 修改 root 密码-- 方法1修改本地 rootALTERUSERrootlocalhostIDENTIFIEDBYnew_password;-- 方法2修改远程 rootALTERUSERroot%IDENTIFIEDBYnew_password;-- 方法3MySQL 5.x 兼容写法SETPASSWORDFORrootlocalhostPASSWORD(new_password);5.4 查询客户端连接情况SELECTSUBSTRING_INDEX(host,:,1)AShost_name,state,COUNT(*)FROMinformation_schema.processlistGROUPBYstate,host_name;六、字符集管理Charset Management6.1 修改数据库字符集生成语句⚠️ 建议先测试确认后再执行-- 生成修改所有数据库字符集的语句SELECTCONCAT(ALTER DATABASE ,SCHEMA_NAME, CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;)FROMINFORMATION_SCHEMA.SCHEMATAWHEREDEFAULT_CHARACTER_SET_NAMEutf8mb4ANDDEFAULT_COLLATION_NAMEutf8mb4_0900_ai_ci;6.2 修改所有表字符集-- 生成修改表字符集的语句SELECTCONCAT(ALTER TABLE ,TABLE_SCHEMA,.,TABLE_NAME, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;)FROMINFORMATION_SCHEMA.TABLESWHERETABLE_COLLATIONutf8mb4_0900_ai_ci;七、Binlog 管理Binary Log-- 方法1查看 binlog 是否开启 SHOW VARIABLES LIKElog_bin;-- 方法2查看 binlog 相关参数 SHOW VARIABLES LIKElog_bin%;7.1 查看 Binlog 过期设置-- MySQL 5.xSHOWVARIABLESLIKEexpire_logs_days;-- MySQL 8.xSHOWVARIABLESLIKEbinlog_expire_logs_seconds;7.2 手动清理 Binlog-- 查看 Binlog 列表SHOWBINARYLOGS;#SHOW MASTER LOGS;-- 删除指定时间前的 BinlogPURGEBINARYLOGS BEFORE2024-04-01 00:00:00;-- 删除指定文件前的 BinlogPURGEBINARYLOGSTOmysql-bin.000003;7.3 设置 Binlog 过期时间-- MySQL 5.xSETGLOBALexpire_logs_days7;-- MySQL 8.xSETGLOBALbinlog_expire_logs_seconds604800;-- 7天 7 * 24 * 3600 秒7.4 永久设置配置文件my.cnf[mysqld] # MySQL 5.x expire_logs_days 7 # MySQL 8.x推荐 binlog_expire_logs_seconds 604800八、其他实用命令8.1 查看进程列表SHOWPROCESSLIST;-- 或查看完整连接SHOWFULLPROCESSLIST;8.2 时间与时间戳转换Shell 命令# 将日期转换为时间戳如360天前date-d$(date-d-360 day%Y%m%d)%s# 将时间戳转换为可读日期date-d1520179200 %Y-%m-%d %H:%M:%S九、MySQL 集群恢复常用命令InnoDB Cluster / Group Replication9.1 集群连接与管理连接 MySQL Shell# 使用 root 用户连接mysqlsh--urirootlocalhost:3306# 使用管理用户连接mysqlsh--urimysqladminlocalhost:3306实例配置检查// 检查当前实例是否满足集群要求dba.checkInstanceConfiguration()// 自动配置实例以加入集群dba.configureInstance()查看集群成员状态\sqlSELECT*FROMperformance_schema.replication_group_members;9.2 集群重启操作// 从完全宕机中恢复集群dba.rebootClusterFromCompleteOutage()9.3 添加实例到集群// 添加新实例cluster.addInstance(mysql-cluster-2.mysql-cluster-instances.mysql-cluster.svc.cluster.local)// 指定恢复方式克隆或增量// cluster.addInstance(..., {recoveryMethod: clone})9.4 强制仲裁操作// 使用指定节点强制建立仲裁挽救脑裂,待验证dba.forceQuorumUsingPartitionOf(mysql-cluster-0.mysql-cluster-instances.mysql-cluster.svc.cluster.local:3306)9.5 用户与权限管理集群环境\sqlALTERUSERmysqladmin%IDENTIFIEDBYnew_password;\sqlFLUSHPRIVILEGES;9.6 创建与管理集群// 创建新集群dba.createCluster(mysql_cluster)// 将实例加入命名集群cluster.addInstance(sysmysqlcluster-0.mysql-cluster-instances.mysql-cluster.svc.cluster.local)9.7 复制与恢复操作# 启动组复制\sqlSTARTGROUP_REPLICATION;# 停止组复制\sqlSTOP GROUP_REPLICATION;# 重置所有从节点状态\sqlRESET REPLICAALL;# 设置全局只读维护时使用\sqlSETGLOBALread_onlyOFF;9.8 元数据操作高级// 清除集群元数据谨慎用于彻底重建dba.dropMetadataSchema() 实用小贴士Best Practices✅字符集统一使用utf8mb4避免存储 emoji 或特殊字符失败。✅字段加COMMENT便于团队协作和后期维护。✅主键建议用AUTO_INCREMENT保证唯一性。✅生产环境操作前备份mysqldump-uroot-pdatabase_namebackup.sql⚠️删除操作不可逆使用IF EXISTS避免报错但数据一旦删掉难恢复。添加索引提升性能进阶ALTERTABLEstudentsADDINDEXidx_email(email);

阅读完成 · 觉得有帮助？

咨询建站