笺注:这是在 CentOS8_Yum安装MySQL8.0 的基础上进行的。
插入防火墙规则:( 打开TCP 3306端口 )
[root@redhat8 ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@redhat8 ~]# firewall-cmd --reload
success
查看防火墙firewalld的配置文件:
[root@redhat8 ~]# cat /etc/firewalld/zones/public.xml
<?xml version="1.0" encoding="utf-8"?>
<zone>
<short>Public</short>
<description>For use in public areas. You do not trust the other computers on networks to not harm your computer. Only selected incoming connections are accepted.</description>
<service name="ssh"/>
<service name="dhcpv6-client"/>
<service name="cockpit"/>
<port port="3306" protocol="tcp"/>
</zone>
服务器本地登录MySQL数据库:( 使用数据库管理员root@localhost )
mysql -uroot -p"P@ssw7rd"
自定义一个数据库用户并授权:
授权用户zhuohua@'%' (密码 P@ssw8rd ),可以从任意IP进行访问,可以对所有的库进行任意操作:
mysql> create user zhuohua@'%' identified with mysql_native_password by 'P@ssw8rd';
mysql> grant all on *.* to zhuohua@'%';
mysql> flush privileges;
查看所有数据库用户及其主机信息:
select user,host from mysql.user;
查看数据库用户zhuohua@'%'的权限:
mysql> show grants for zhuohua@'%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@% |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `zhuohua`@`%` |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `zhuohua`@`%` |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Navicat是一个图形化管理多种数据库的工具。
Windows客户端使用Navicat,远程访问MySQL数据库:
新建“查询”:
使用“查询”创建库:
CREATE DATABASE db1;
使用“查询”创建表:
在库db1里创建一个表t_1:
use db1;
create table t_1(
id int not null auto_increment,
name varchar(20) not null,
grade varchar(10),
age float default 60,
ruxueriqi datetime,
chinese decimal(50, 2),
math numeric(60, 3),
primary key (id)
);
在Navicat查看库db1的表t_1的表结构:
进入服务器查看库db1的表t_1的表结构:
desc db1.t_1;
在库db1里删除一个表t_1:
DROP table db1.t_1;
删除库db1:
DROP DATABASE db1;
相关文章:
CentOS8_重置MySQL8.0的用户密码
CentOS8防火墙(firewalld)
使用Navicat远程管理MariaDB
使用Navicat远程管理MySQL |