基于Linux CentOS7.9部署MySQL服务以及简单MySQL数据库操作

环境规划cat/etc/redhat-releaseip-4acat/etc/sysconfig/network-scripts/ifcfg-ens33安装MySQL基本环境清理停止服务systemctl stop mariadb查找mariadb 软件包rpm-qa|grepmariadb卸载mariadb 软件包yum-yremove MariaDB *查找MariaDB 相关文件find/-name mysql*制作mariadb 的yum 源cat/etc/yum.repos.d/mariadb.repo直接复制以下内容覆盖原文件即可[mariadb]nameMariaDBbaseurlhttps://mirror.mariadb.org/yum/10.11.17/rhel7-amd64/gpgkeyhttps://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck1官方地址https://mirror.mariadb.org/yum/10.11.17/rhel7-amd64/安装MySQLyum-yinstallMariaDB-server MariaDB-client启停服务systemctl start mariadb#启动服务systemctlenablemariadb#设置开机启动systemctl restart mariadb#重新启动systemctl stop mariadb#停止MariaDB登录MySQLmysql-uroot-p查看详细信息MariaDB[(none)]\s修改字符集cat/etc/my.cnf复制以下内容写入覆盖即可##This group is read both by the client and the server#use it for options that affect everything#[mysqld]character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci init_connectSET NAMES utf8mb4skip-character-set-client-handshake[client]default-character-setutf8mb4[mysql]default-character-setutf8mb4[client-server]##include *.cnf from the config directory#!includedir /etc/my.cnf.d重启服务systemctl restart mariadb数据库简单操作查看数据库结构查看数据库showdatabases;选择/切换数据库usemysql;查看库中有哪些表showtables;查看表结构descuser;或者showcreatetableuser;确认当前所处的数据库selectdatabase();创建和删除库表创建数据库createdatabaseschool创建表 表名称|字段的数据类型|约束useschool;createtablestudents(student_namechar(16)notnull,student_numberchar(48)default,primarykey(student_name));删除库表droptablestudents;dropdatabaseschool;管理表数据添加数据useauth;insertintousers(user_name,user_passwd)values(zhangsan,123456);insertintousersvalues(lisi,123456);select*fromusers;selectuser_name,user_passwdfromuserswhereuser_namezhangsan;updateuserssetuser_passwdwhereuser_namelisi;select*fromusers;维护MySQL数据库用户授权创建数据库用户createuserzhangwuji identifiedby1;usemysql;selecthost,userfromuser;查看授权showgrantsforzhangwuji;showgrantsforzhangwuji%(标准格式userhost)用户授权grantselectonauth.*tozhangwuji%;showgrantsforzhangwuji%;撤销权限revokeselectonauth.*fromzhangwuji%;showgrantsforzhangwuji%;数据库备份和恢复创建备份文件目录mkdir-pv/opt/db_bak备份数据库mysqldump-uroot-pauth/opt/db_bak/auth_db.sqlcat/opt/db_bak/auth_db.sql备份auth库中的users表mysqldump-uroot-pauthusers/opt/db_bak/auth_users.sql删除auth库中的users表并恢复mysql-uroot-puseauth;droptableusers;mysql-uroot-pauth/opt/db_bak/auth_users.sql回到MySQL中查看users表是否恢复useauth;showtables;select*fromusers;