搭建数据库服务器
PostgreSql服务器
软件介绍
PostgreSQL的架构如图1所示,主要进程说明如表1所示。
表 1 PostgreSql中的主要进程说明
配置环境
说明
以下环境配置仅为参考示例,具体配置视实际需求做配置。
关闭防火墙并取消开机自启动
说明
测试环境下通常会关闭防火墙以避免部分网络因素影响,视实际需求做配置。
在root权限下停止防火墙。
shellsystemctl stop firewalld
在root权限下关闭防火墙。
shellsystemctl disable firewalld
说明
执行disable命令关闭防火墙的同时,也取消了开机自启动。
修改SELINUX为disabled
在root权限下修改配置文件。
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
创建组和用户
说明
服务器环境下,为了系统安全,通常会为进程分配单独的用户,以实现权限隔离。本章节创建的组和用户都是操作系统层面的,不是数据库层面的。
在root权限下创建PostgreSQL用户(组)。
shellgroupadd postgres useradd -g postgres postgres
在root权限下设置postgres用户密码(重复输入密码)。
shellpasswd postgres
搭建数据盘
说明
测试极限性能时,建议单独挂载IO性能更优的NVME SSD存储介质创建PostgreSQL测试实例,避免磁盘IO对性能测试结果的影响,本文以单独挂载NVME SSD为例,参考步骤1~步骤4。
非性能测试时,在root权限下执行以下命令,创建数据目录即可。然后跳过本小节:mkdir /data
在root权限下创建文件系统(以xfs为例,根据实际需求创建文件系统),若磁盘之前已做过文件系统,执行此命令会出现报错,可使用-f参数强制创建文件系统。
shellmkfs.xfs /dev/nvme0n1
在root权限下创建数据目录。
shellmkdir /data
在root权限下挂载磁盘。
shellmount -o noatime,nobarrier /dev/nvme0n1 /data
数据目录授权
在root权限下修改目录权限。
shellchown -R postgres:postgres /data/
安装、运行和卸载
安装
配置本地yum源,详细信息请参考搭建repo服务器。
清除缓存。
shelldnf clean all
创建缓存。
shelldnf makecache
在root权限下安装PostgreSQL服务器。
shelldnf install postgresql-server
查看安装后的rpm包。
shellrpm -qa | grep postgresql
运行
初始化数据库
须知
此步骤在postgres用户下操作。
切换到已创建的PostgreSQL用户。
shellsu - postgres
初始化数据库,其中命令中的/usr/bin是命令initdb所在的目录。
shellusr/bin/initdb -D /data/
启动数据库
启动PostgreSQL数据库。
shell/usr/bin/pg_ctl -D /data/ -l /data/logfile start
确认PostgreSQL数据库进程是否正常启动。
shellps -ef | grep postgres
命令执行后,打印信息如下图所示,PostgreSQL相关进程已经正常启动了。
登录数据库
登录数据库。
shell/usr/bin/psql -U postgres
说明
初次登录数据库,无需密码。
配置数据库帐号密码
登录后,设置postgres密码。
shellpostgres=#alter user postgres with password '123456';
退出数据库
执行\q退出数据库。
shellpostgres=#\q
停止数据库
停止PostgreSQL数据库。
shell/usr/bin/pg_ctl -D /data/ -l /data/logfile stop
卸载
在postgres用户下停止数据库。
shell/usr/bin/pg_ctl -D /data/ -l /data/logfile stop
在root用户下执行dnf remove postgresql-server卸载PostgreSQL数据库。
shelldnf remove postgresql-server
管理数据库角色
创建角色
可以使用CREATE ROLE语句或createuser来创建角色。createuser是对CREATE ROLE命令的封装,需要在shell界面执行,而不是在数据库界面。
CREATE ROLE rolename [ [ WITH ] option [ ... ] ];
createuser rolename
其中:
- rolename:角色名。
- option为参数选项,常用的有:
- SUPERUSER | NOSUPERUSER:决定一个新角色是否为"超级用户",若未指定,则默认为NOSUPERUSER,即不是超级用户。
- CREATEDB | NOCREATEDB:定义一个角色是否能创建数据库,若未指定,则默认为NOCREATEDB,即不能创建数据库。
- CREATEROLE | NOCREATEROLE:决定一个角色是否可以创建新角色,若未指定,则默认为NOCREATEROLE,即不能创建新角色。
- INHERIT | NOINHERIT:决定一个角色是否"继承"它所在组的角色的权限。一个带有 INHERIT 属性的角色可以自动使用已经赋与它直接或间接所在组的任何权限。若未指定,则默认为INHERIT。
- LOGIN | NOLOGIN:决定一个角色是否可以登录,一个拥有LOGIN属性的角色可以认为是一个用户,若无此属性的角色可以用于管理数据库权限,但是并不是用户,若未指定,则默认为NOLOGIN。但若创建角色是使用的是CREATE USER而不是CREATE ROLE,则默认是LOGIN属性。
- [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password':设置角色的密码,密码只对那些拥有 LOGIN 属性的角色有意义。ENCRYPTED | UNENCRYPTED表示是否对密码进行加密,若未指定,则默认为ENCRYPTED,即加密。
- VALID UNTIL 'timestamp':角色的密码失效的时间戳,若为指定,则表示密码永久有效。
- IN ROLE rolename1:列出一个或多个现有的角色,新角色rolename将立即加入这些角色,成为rolename1的成员。
- ROLE rolename2:列出一个或多个现有的角色,它们将自动添加为新角色rolename的成员,即新角色为"组"。
要使用这条命令,必须拥有 CREATEROLE 权限或者是数据库超级用户。
示例
创建一个可以登录的角色roleexample1。
postgres=# CREATE ROLE roleexample1 LOGIN;
创建一个密码为123456的角色roleexample2。
postgres=# CREATE ROLE roleexample2 WITH LOGIN PASSWORD '123456';
创建角色名为roleexample3的角色。
[postgres@localhost ~]# createuser roleexample3
查看角色
可以使用SELECT语句或psql的元命令\du查看角色。
SELECT rolename FROM pg_roles;
\du
其中:rolename:角色名。
示例
查看roleexample1角色。
postgres=# SELECT roleexample1 from pg_roles;
查看现有角色。
postgres=# \du
修改角色
修改用户名
可以使用ALTER ROLE语句修改一个已经存在的角色名。
ALTER ROLE oldrolername RENAME TO newrolename;
其中:
- oldrolername:旧的角色名。
- newrolename:新的角色名。
修改用户示例
将角色名roleexample1修改为roleexapme2。
postgres=# ALTER ROLE roleexample1 RENAME TO roleexample2;
修改用户密码
可以使用ALTER ROLE语句修改一个角色的登录密码。
ALTER ROLE rolename PASSWORD 'password'
其中:
- rolename:角色名。
- password:密码。
修改角色密码示例
将roleexample1的密码修改为456789。
postgres=# ALTER ROLE roleexample1 WITH PASSWORD '456789';
删除角色
可以使用DROP ROLE语句或dropuser来删除角色。dropuser是对DROP ROLE命令的封装,需要在shell界面执行,而不是在数据库界面。
DROP ROLE rolename;
dropuser rolename
其中:rolename为角色名。
示例
删除userexample1角色。
postgres=# DROP ROLE userexample1;
删除userexample2角色。
[postgres@localhost ~]# dropuser userexample2
角色授权
可以使用GRANT语句来对角色授权。
对角色授予表的操作权限:
GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
对角色授予序列的操作权限:
GRANT { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } ON SEQUENCE sequencename [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
对角色授予数据库的操作权限:
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE databasename [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
对角色授予函数的操作权限:
GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
对角色授予过程语言的操作权限:
GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE langname [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
对角色授予模式的操作权限:
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
对角色授予表空间的操作权限:
GRANT { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespacename [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
将角色rolename1的成员关系赋予角色rolename2:
GRANT rolename1 [, ...] TO rolename2 [, ...] [ WITH ADMIN OPTION ]
其中:
- SELECT、INSERT、UPDATE、DELETE、REFERENCES、TRIGGER、USAGE、CREATE、CONNECT、TEMPORARY、TEMP、EXECUTE、ALL [ PRIVILEGES ]:用户的操作权限,ALL [ PRIVILEGES ]表示所有的权限,PRIVILEGES关键字在 PostgreSQL里是可选的,但是严格的SQL 要求有这个关键字。
- ON字句:用于指定权限授予的对象。
- tablename:表名。
- TO字句:用来指定被赋予权限的角色。
- rolename、rolename1、rolename2:角色名。
- groupname:角色组名。
- PUBLIC:表示该权限要赋予所有角色,包括那些以后可能创建的用户。
- WITH GRANT OPTION:表示权限的接收者也可以将此权限赋予他人,否则就不能授权他人。该选项不能赋予给PUBLIC。
- sequencename:序列名。
- databasename:数据库名。
- funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ):函数名及其参数。
- langname:过程语言名。
- schemaname:模式名。
- tablespacename:表空间名。
- WITH ADMIN OPTION:表示成员随后就可以将角色的成员关系赋予其他角色,以及撤销其他角色的成员关系。
示例
对userexample授予数据库database1的CREATE权限。
postgres=# GRANT CREATE ON DATABASE database1 TO userexample;
对所有用户授予表table1的所有权限。
postgres=# GRANT ALL PRIVILEGES ON TABLE table1 TO PUBLIC;
删除用户权限
可以使用REVOKE语句来撤销以前赋予一个或多个角色的权限。
撤销角色对表的操作权限:
REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...]
撤销角色对序列的操作权限:
REVOKE [ GRANT OPTION FOR ] { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } ON SEQUENCE sequencename [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
撤销角色对数据库的操作权限:
REVOKE [ GRANT OPTION FOR ] { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE databasename [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
撤销角色对函数的操作权限:
REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
撤销角色对过程语言的操作权限:
REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE langname [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
撤销角色对模式的操作权限:
REVOKE [ GRANT OPTION FOR ] { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
撤销角色对表空间的操作权限:
REVOKE [ GRANT OPTION FOR ] { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespacename [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
删除rolename2的rolename1的成员关系:
REVOKE [ ADMIN OPTION FOR ] rolename1 [, ...] FROM rolename2 [, ...] [ CASCADE | RESTRICT ]
其中:
- GRANT OPTION FOR:表示只是撤销对该权限的授权的权力,而不是撤销该权限本身。
- SELECT、INSERT、UPDATE、DELETE、REFERENCES、TRIGGER、USAGE、CREATE、CONNECT、TEMPORARY、TEMP、EXECUTE、ALL [ PRIVILEGES ]:用户的操作权限,ALL [ PRIVILEGES ]表示所有的权限,PRIVILEGES关键字在 PostgreSQL里是可选的,但是严格的SQL 要求有这个关键字。
- ON字句:用于指定撤销权限的对象。
- tablename:表名。
- FROM字句:用来指定被撤销权限的角色。
- rolename、rolename1、rolename2:角色名。
- groupname:角色组名。
- PUBLIC:表示撤销隐含定义的、拥有所有角色的组,但并不意味着所有角色都失去了权限,那些直接得到的权限以及通过一个组得到的权限仍然有效。
- sequencename:序列名。
- CASCADE:撤销所有依赖性权限。
- RESTRICT:不撤销所有依赖性权限。
- databasename:数据库名。
- funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ):函数名及其参数。
- langname:过程语言名。
- schemaname:模式名。
- tablespacename:表空间名。
- ADMIN OPTION FOR:表示传递的授权不会自动收回。
示例
对userexample授予数据库database1的CREATE权限。
postgres=# GRANT CREATE ON DATABASE database1 TO userexample;
对所有用户授予表table1的所有权限。
postgres=# GRANT ALL PRIVILEGES ON TABLE table1 TO PUBLIC;
管理数据库
创建数据库
可以使用CREATE DATABASE语句或createdb来创建数据库。createrdb是对CREATE DATABASE命令的封装,需要在shell界面执行,而不是在数据库界面。
CREATE DATABASE databasename;
createdb databasename
其中:databasename为数据库名。
要使用这条命令,必须拥有CREATEDB权限。
示例
创建一个数据库database1。
postgres=# CREATE DATABASE database1;
选择数据库
可以使用\c语句来选择数据库。
\c databasename;
其中:databasename为数据库名称。
示例
选择databaseexample数据库。
postgres=# \c databaseexample;
查看数据库
可以使用\l语句来查看数据库。
\l;
示例
查看所有数据库。
postgres=# \l;
删除数据库
可以使用DROP DATABASE语句或dropdb来删除数据库。dropdb是对DROP DATABASE命令的封装,需要在shell界面执行,而不是在数据库界面。
注意
删除数据库要谨慎操作,一旦删除,数据库中的所有表和数据都会删除。
DROP DATABASE databasename;
dropdb databasename
其中:databasename为数据库名称。
DROP DATABASE会删除数据库的系统目录项并且删除包含数据的文件目录。
DROP DATABASE只能由超级管理员或数据库拥有者执行。
示例
删除databaseexample数据库。
postgres=# DROP DATABASE databaseexample;
备份数据库
可以使用pg_dump命令备份数据库,将数据库转储到一个脚本文件或其他归档文件中。
pg_dump [option]... [databasename] > outfile
其中:
- databasename:数据库名称。如果没有声明这个参数,那么使用环境变量 PGDATABASE 。如果那个环境变量也没声明,那么使用发起连接的用户名。
- outfile:数据库备份的文件。
- option:pg_dump命令参数选项,多个参数之间可以使用空格分隔。常用的pg_dump命令参数选项如下:
- -f,--file= filename :指输出到指定的文件。如果忽略,则使用标准输出。
- -d,--dbname= databasename :指定转储的数据库。
- -h,--host= hostname :指定主机名。
- -p,--port= portnumber :指定端口。
- -U,--username= username :指定连接的用户名。
- -W,--password:强制口令提示(自动)。
示例
备份主机为192.168.202.144,端口为3306,postgres用户下的database1数据库到db1.sql中。
[postgres@localhost ~]# pg_dump -h 192.168.202.144 -p 3306 -U postgres -W database1 > db1.sql
恢复数据库
可以使用psql命令恢复数据库。
psql [option]... [databasename [username]] < infile
其中:
- databasename:数据库名称。如果没有声明这个参数,那么使用环境变量 PGDATABASE 。如果那个环境变量也没声明,那么使用发起连接的用户名。
- username:用户名。
- infile:pg_dump命令中的outfile参数。
- option:psql命令参数选项,多个参数之间可以使用空格分隔。常用的psql命令参数选项如下:
- -f,--file=filename:指输出到指定的文件。如果忽略,则使用标准输出。
- -d,--dbname=databasename:指定转储的数据库。
- -h,--host=hostname:指定主机名。
- -p,--port=portnumber:指定端口。
- -U,--username=username:指定连接的用户名。
- -W,--password:强制口令提示(自动)。
psql命令不会自动创建databasename数据库,所以在执行psql恢复数据库之前需要先创建databasename数据库。
示例
将db1.sql脚本文件导入到主机为192.168.202.144,端口为3306,postgres用户下newdb数据库中。
[postgres@localhost ~]# createdb newdb
[postgres@localhost ~]# psql -h 192.168.202.144 -p 3306 -U postgres -W -d newdb < db1.sql
Mariadb服务器
软件介绍
MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可。MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品,MariaDB还提供了许多更好的新特性。
MariaDB的架构如图2所示。
当Mariadb接受到Sql语句时,其详细的执行过程如下:
- 当客户端连接到mariadb的时候,会认证客户端的主机名、用户、密码,认证功能可以做成插件。
- 如果登录成功,客户端发送sql命令到服务端。由解析器解析sql语句。
- 服务端检查客户端是否有权限去获取它想要的资源。
- 如果查询已经存储在query cache当中,那么结果立即返回。
- 优化器将会找出最快的执行策略,或者是执行计划,也就是说优化器可以决定什么表将会被读,以及哪些索引会被访问,哪些临时表会被使用,一个好的策略能够减少大量的磁盘访问和排序操作等。
- 存储引擎读写数据和索引文件,cache用来加速这些操作,其他的诸如事物和外键特性,都是在存储引擎层处理的。
存储引擎在物理层管控数据,它负责数据文件、数据、索引、cache等的管理,这使得管理和读取数据变得更高效,每一张表,都有一个.frm文件,这些文件包含着表的定义。
每一个存储引擎管理、存储数据的方式都是不同的,所支持的特性和性能也不尽相同。例如:
- MyISAM,适合读多写少的环境,且不支持事务,支持全文索引等。
- noDB,支持事务,支持行锁和外键等。
- MEMORY,将数据存储在内存当中。
- CSV,将数据存储为CSV格式。
配置环境
说明
以下环境配置仅为参考示例,具体配置视实际需求做配置
关闭防火墙并取消开机自启动
说明
测试环境下通常会关闭防火墙以避免部分网络因素影响,视实际需求做配置。
在root权限下停止防火墙。
shellsystemctl stop firewalld
在root权限下关闭防火墙。
shellsystemctl disable firewalld
说明
执行disable命令关闭防火墙的同时,也取消了开机自启动。
修改SELINUX为disabled
在root权限下修改配置文件。
shellsed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux
创建组和用户
说明
服务器环境下,为了系统安全,通常会为进程分配单独的用户,以实现权限隔离。本章节创建的组和用户都是操作系统层面的,不是数据库层面的。
在root权限下创建MySQL用户(组)。
shellgroupadd mysql
shelluseradd -g mysql mysql
在root权限下设置MySQL用户密码。
shellpasswd mysql
重复输入密码(根据实际需求设置密码)。
搭建数据盘
说明
进行性能测试时,数据目录使用单独硬盘,需要对硬盘进行格式化并挂载,参考方法一或者方法二。
非性能测试时,在root权限下执行mkdir /data
创建数据目录即可。然后跳过本小节。
方法一:在root权限下使用fdisk进行磁盘管理
创建分区(以/dev/sdb为例,根据实际情况创建)
shellfdisk /dev/sdb
输入n,按回车确认。
输入p,按回车确认。
输入1,按回车确认。
采用默认配置,按回车确认。
采用默认配置,按回车确认。
输入w,按回车保存。
创建文件系统(以xfs为例,根据实际需求创建文件系统)
shellmkfs.xfs /dev/sdb1
挂载分区到“/data”以供操作系统使用。
shellmkdir /data
shellmount /dev/sdb1 /data
执行命令“vi /etc/fstab", 编辑“/etc/fstab”使重启后自动挂载数据盘。如下图中,添加最后一行内容。
其中,/dev/nvme0n1p1为示例,具体名称以实际情况为准。
方法二:在root权限下使用LVM进行磁盘管理
说明
此步骤需要安装镜像中的lvm2相关包,步骤如下:
(1)配置本地yum源,详细信息请参考搭建repo服务器。如果已经执行,则可跳过此步。
(2)在root权限下执行yum install lvm2
命令安装lvm2。
创建物理卷(sdb为硬盘名称,具体名字以实际为准)。
shellpvcreate /dev/sdb
创建物理卷组(其中datavg为创建的卷组名称,具体名字以实际规划为准)。
shellvgcreate datavg /dev/sdb
创建逻辑卷(其中600G为规划的逻辑卷大小,具体大小以实际情况为准;datalv为创建的逻辑卷的名字,具体名称以实际规划为准。)。
shelllvcreate -L 600G -n datalv datavg
创建文件系统。
shellmkfs.xfs /dev/datavg/datalv
创建数据目录并挂载。
shellmkdir /data mount /dev/datavg/datalv /data
执行命令vi /etc/fstab,编辑“/etc/fstab”使重启后自动挂载数据盘。如下图中,添加最后一行内容。
其中,/dev/datavg/datalv为示例,具体名称以实际情况为准。
创建数据库目录并且授权
在已创建的数据目录 /data 基础上,使用root权限继续创建进程所需的相关目录并授权MySQL用户(组)。
shellmkdir -p /data/mariadb cd /data/mariadb mkdir data tmp run log chown -R mysql:mysql /data
安装、运行和卸载
安装
配置本地yum源,详细信息请参考搭建repo服务器。
清除缓存。
shelldnf clean all
创建缓存。
shelldnf makecache
在root权限下安装mariadb服务器。
shelldnf install mariadb-server
查看安装后的rpm包。
shellrpm -qa | grep mariadb
运行
在root权限下开启mariadb服务器。
shellsystemctl start mariadb
- shell
/usr/bin/mysql_secure_installation
命令执行过程中需要输入数据库的root设置的密码,若没有密码则直接按“Enter”。然后根据提示及实际情况进行设置。
登录数据库。
shellmysql -u root -p
命令执行后提示输入密码。密码为2中设置的密码。
说明
执行 \q 或者 exit 可退出数据库。
卸载
在root权限下关闭数据库进程。
shellps -ef | grep mysql kill -9 进程ID
在root权限下执行dnf remove mariadb-server命令卸载mariadb。
shelldnf remove mariadb-server
管理数据库用户
创建用户
可以使用CREATE USER语句来创建一个或多个用户,并设置相应的口令。
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
其中:
- username:用户名。
- host:主机名,即用户连接数据库时所在的主机的名字。若是本地用户可用localhost,若在创建的过程中,未指定主机名,则主机名默认为“%”,表示一组主机。
- password:用户的登录密码,密码可以为空,如果为空则该用户可以不需要密码登录服务器,但从安全的角度而言,不推荐这种做法。
使用CREATE USER语句必须拥有数据库的INSERT权限或全局CREATE USER权限。
使用CREATE USER语句创建一个用户帐号后,会在系统自身的数据库的user表中添加一条新记录。若创建的帐户已经存在,则语句执行时会出现错误。
新创建的用户拥有的权限很少,只允许进行不需要权限的操作,如使用SHOW语句查询所有存储引擎和字符集的列表等。
示例
创建密码为123456,用户名为userexample1的本地用户。
> CREATE USER 'userexample1'@'localhost' IDENTIFIED BY '123456';
创建密码为123456,用户名为userexample2,主机名为192.168.1.100的用户。
> CREATE USER 'userexample2'@'192.168.1.100' IDENTIFIED BY '123456';
查看用户
可以使用SHOW GRANTS语句或SELECT语句查看一个或多个用户。
查看特定用户:
SHOW GRANTS [FOR 'username'@'hostname'];
SELECT USER,HOST,PASSWORD FROM mysql.user WHERE USER='username';
查看所有用户:
SELECT USER,HOST,PASSWORD FROM mysql.user;
其中:
- username:用户名。
- hostname:主机名。
示例
查看userexample1用户。
> SHOW GRANTS FOR 'userexample1'@'localhost';
查看mysql数据库中所有用户。
> SELECT USER,HOST,PASSWORD FROM mysql.user;
修改用户
修改用户名
可以使用RENAME USER语句修改一个或多个已经存在的用户名。
RENAME USER 'oldusername'@'hostname' TO 'newusername'@'hostname';
其中:
- oldusername:旧的用户名。
- newusername:新的用户名。
- hostname:主机名。
RENAME USER语句用于对原有的帐号进行重命名。若系统中旧帐号不存在或者新帐号已存在,则该语句执行时会出现错误。
使用RENAME USER语句,必须拥有数据库的UPDATE权限或全局CREATE USER权限。
修改用户示例
将用户名userexample1修改为userexapme2,主机名为locahost。
> RENAME USER 'userexample1'@'localhost' TO 'userexample2'@'localhost';
修改用户密码
可以使用SET PASSWORD语句修改一个用户的登录密码。
SET PASSWORD FOR 'username'@'hostname' = PASSWORD('newpassword');
其中:
- FOR 'username'@'hostname':FOR字句,可选项,指定欲修改密码的用户名及主机名。
- PASSWORD('newpassword'):表示使用函数PASSWORD()设置新口令,即新口令必须传递到函数PASSWORD()中进行加密。
注意
PASSWORD()函数为单向加密函数,一旦加密后不能解密出原明文。
在SET PASSWORD语句中,若不加上FOR子句,表示修改当前用户的密码。
FOR字句中必须以'username'@'hostname'的格式给定,username为帐户的用户名,hostname为帐户的主机名。
欲修改密码的帐号必须在系统中存在,否则语句执行时会出现错误。
修改用户密码示例
将用户名为userexample的密码修改为0123456,主机名为locahost。
> SET PASSWORD FOR 'userexample'@'localhost' = PASSWORD('0123456') ;
删除用户
可以使用DROP USER语句来删除一个或多个用户帐号以及相关的权限。
DROP USER 'username1'@'hostname1' [,'username2'@'hostname2']…;
注意
用户的删除不会影响他们之前所创建的表、索引或其他数据库对象,因为数据库并不会记录创建了这些对象的帐号。
DROP USER语句可用于删除一个或多个数据库帐号,并删除其原有权限。
使用DROP USER语句必须拥有数据库的DELETE权限或全局CREATE USER权限。
在DROP USER语句的使用中,若没有明确地给出帐号的主机名,则该主机名默认为“%”。
示例
删除用户名为userexample的本地用户。
> DROP USER 'userexample'@'localhost';
用户授权
可以使用GRANT语句来对新建用户的授权。
GRANT privileges ON databasename.tablename TO 'username'@'hostname';
其中:
- ON字句:用于指定权限授予的对象和级别。
- privileges:用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所有的权限则使用ALL。
- databasename:数据库名。
- tablename:表名。
- TO字句:用来设定用户密码,以及指定被赋予权限的用户。
- username:用户名。
- hostname:主机名。
如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,如*.*。
如果在TO子句中给系统中存在的用户指定密码,则新密码会将原密码覆盖。
如果权限被授予给一个不存在的用户,则会自动执行一条CREATE USER语句来创建这个用户,但同时必须为该用户指定密码。
示例
对本地用户userexample授予SELECT和INSERT权限。
> GRANT SELECT,INSERT ON *.* TO 'userexample'@'localhost';
删除用户权限
可以使用REVOKE语句来删除一个用户的权限,但此用户不会被删除。
REVOKE privilege ON databasename.tablename FROM 'username'@'hostname';
其中REVOKE语句的参数与GRANT语句的参数含义相同。
要使用 REVOKE 语句,必须拥有数据库的全局CREATE USER权限或UPDATE权限。
示例
删除本地用户userexample的INSERT权限。
> REVOKE INSERT ON *.* FROM 'userexample'@'localhost';
管理数据库
创建数据库
可以使用CREATE DATABASE语句来创建数据库。
CREATE DATABASE databasename;
其中:databasename为数据库名称,且数据库名称不区分大小写。
示例
创建数据库名为databaseexample的数据库。
> CREATE DATABASE databaseexample;
查看数据库
可以使用SHOW DATABASES语句来查看数据库。
SHOW DATABASES;
示例
查看所有数据库。
> SHOW DATABASES;
选择数据库
一般创建表,查询表等操作首先需要选择一个目标数据库。可以使用USE语句来选择数据库。
USE databasename;
其中:databasename为数据库名称。
示例
选择databaseexample数据库。
> USE databaseexample;
删除数据库
可以使用DROP DATABASE语句来删除数据库。
注意
删除数据库要谨慎操作,一旦删除,数据库中的所有表和数据都会删除。
DROP DATABASE databasename;
其中:databasename为数据库名称。
DROP DATABASE命令用于删除创建过(已存在)的数据库,且会删除数据库中的所有表,但数据库的用户权限不会自动删除。
要使用DROP DATABASE,您需要数据库的DROP权限。
DROP SCHEMA是DROP DATABASE的同义词。
示例
删除databaseexample数据库。
> DROP DATABASE databaseexample;
备份数据库
可以在root权限下使用mysqldump命令备份数据库。
备份一个或多个表:
mysqldump [options] databasename [tablename ...] > outfile
备份一个或多个库:
mysqldump [options] -databases databasename ... > outfile
备份所有库:
mysqldump [options] -all-databases > outputfile
其中:
- databasename:数据库名称。
- tablename:数据表名称。
- outfile:数据库备份的文件。
- options:mysqldump命令参数选项,多个参数之间可以使用空格分隔。常用的mysqldump命令参数选项如下:
- -u, --user= username :指定用户名。
- -p, --password[= password]:指定密码。
- -P, --port= portnumber :指定端口。
- -h, --host= hostname :指定主机名。
- -r, --result-file= filename :将导出结果保存到指定的文件中,等同于“>”。
- -t:只备份数据。
- -d:只备份表结构。
示例
备份主机为192.168.202.144,端口为3306,root用户下的所有数据库到alldb.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 --all-databases > alldb.sql
备份主机为192.168.202.144,端口为3306,root用户下的db1数据库到db1.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 --databases db1 > db1.sql
备份主机为192.168.202.144,端口为3306,root用户下的db1数据库的tb1表到db1tb1.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 db1 tb1 > db1tb1.sql
只备份主机为192.168.202.144,端口为3306,root用户下的db1数据库的表结构到db1.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 -d db1 > db1.sql
只备份主机为192.168.202.144,端口为3306,root用户下的db1数据库的数据到db1.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 -t db1 > db1.sql
恢复数据库
可以在root权限下使用mysql命令恢复数据库。
恢复一个或多个表:
mysql -h hostname -P portnumber -u username -ppassword databasename < infile
其中:
- hostname:主机名。
- portnumber:端口号。
- username:用户名。
- password:密码。
- databasename:数据库名。
- infile:mysqldump命令中的outfile参数。
示例
恢复数据库。
# mysql -h 192.168.202.144 -P 3306 -uroot -p123456 -t db1 < db1.sql
MySQL服务器
软件介绍
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,目前属于Oracle旗下产品。MySQL是业界最流行的RDBMS (Relational Database Management System,关系数据库管理系统)之一,尤其在WEB应用方面。
关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就加快了速度并提高了灵活性。
MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。MySQL软件采用了双授权模式,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择MySQL作为网站数据库。
配置环境
说明
以下环境配置仅为参考示例,具体配置视实际需求做配置
关闭防火墙并取消开机自启动
说明
测试环境下通常会关闭防火墙以避免部分网络因素影响,视实际需求做配置。
在root权限下停止防火墙。
shellsystemctl stop firewalld
在root权限下关闭防火墙。
shellsystemctl disable firewalld
说明
执行disable命令关闭防火墙的同时,也取消了开机自启动。
修改SELINUX为disabled
在root权限下修改配置文件。
shellsed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux
创建组和用户
说明
服务器环境下,为了系统安全,通常会为进程分配单独的用户,以实现权限隔离。本章节创建的组和用户都是操作系统层面的,不是数据库层面的。
在root权限下创建MySQL用户(组)。
shellgroupadd mysql
shelluseradd -g mysql mysql
在root权限下设置MySQL用户密码。
shellpasswd mysql
重复输入密码(根据实际需求设置密码)。
搭建数据盘
说明
进行性能测试时,数据目录使用单独硬盘,需要对硬盘进行格式化并挂载,参考方法一或者方法二。
非性能测试时,在root权限下执行mkdir /data
创建数据目录即可。然后跳过本小节。
方法一:在root权限下使用fdisk进行磁盘管理
创建分区(以/dev/sdb为例,根据实际情况创建)
shellfdisk /dev/sdb
输入n,按回车确认。
输入p,按回车确认。
输入1,按回车确认。
采用默认配置,按回车确认。
采用默认配置,按回车确认。
输入w,按回车保存。
创建文件系统(以xfs为例,根据实际需求创建文件系统)
shellmkfs.xfs /dev/sdb1
挂载分区到“/data”以供操作系统使用。
shellmkdir /data
shellmount /dev/sdb1 /data
执行命令“vi /etc/fstab", 编辑“/etc/fstab”使重启后自动挂载数据盘。如下图中,添加最后一行内容。
其中,/dev/nvme0n1p1为示例,具体名称以实际情况为准。
方法二:在root权限下使用LVM进行磁盘管理
说明
此步骤需要安装镜像中的lvm2相关包,步骤如下:
(1)配置本地yum源,详细信息请参考搭建repo服务器。如果已经执行,则可跳过此步。
(2)执行yum install lvm2
安装lvm2。
创建物理卷(sdb为硬盘名称,具体名字以实际为准)。
shellpvcreate /dev/sdb
创建物理卷组(其中datavg为创建的卷组名称,具体名字以实际规划为准)。
shellvgcreate datavg /dev/sdb
创建逻辑卷(其中600G为规划的逻辑卷大小,具体大小以实际情况为准;datalv为创建的逻辑卷的名字,具体名称以实际规划为准。)。
shelllvcreate -L 600G -n datalv datavg
创建文件系统。
shellmkfs.xfs /dev/datavg/datalv
创建数据目录并挂载。
shellmkdir /data
shellmount /dev/datavg/datalv /data
执行命令vi /etc/fstab,编辑“/etc/fstab”使重启后自动挂载数据盘。如下图中,添加最后一行内容。
其中,/dev/datavg/datalv为示例,具体名称以实际情况为准。
创建数据库目录并且授权
在已创建的数据目录 /data 基础上,使用root权限继续创建进程所需的相关目录并授权MySQL用户(组)。
shellmkdir -p /data/mysql cd /data/mysql mkdir data tmp run log chown -R mysql:mysql /data
安装、运行和卸载
安装
配置本地yum源,详细信息请参考搭建repo服务器章节。
清除缓存。
shelldnf clean all
创建缓存。
shelldnf makecache
在root权限下安装MySQL服务器。
shelldnf install mysql-server
查看安装后的rpm包。
shellrpm -qa | grep mysql-server
运行
修改配置文件。
在root权限下创建my.cnf文件,其中文件路径(包括软件安装路径basedir、数据路径datadir等)根据实际情况修改。
shellvi /etc/my.cnf
编辑my.cnf内容如下:
text[mysqld_safe] log-error=/data/mysql/log/mysql.log pid-file=/data/mysql/run/mysqld.pid [mysqldump] quick [mysql] no-auto-rehash [client] default-character-set=utf8 [mysqld] basedir=/usr/local/mysql socket=/data/mysql/run/mysql.sock tmpdir=/data/mysql/tmp datadir=/data/mysql/data default_authentication_plugin=mysql_native_password port=3306 user=mysql
确保my.cnf配置文件修改正确。
shellcat /etc/my.cnf
注意
其中basedir为软件安装路径,请根据实际情况修改。
在root权限下修改/etc/my.cnf文件的组和用户为mysql:mysql
shellchown mysql:mysql /etc/my.cnf
配置环境变量。
安装完成后,在root权限下将MySQL二进制文件路径到PATH。
shellecho export PATH=$PATH:/usr/local/mysql/bin >> /etc/profile
注意
其中PATH中的“/usr/local/mysql/bin“路径,为MySQL软件安装目录下的bin文件的绝对路径。请根据实际情况修改。
在root权限下使环境变量配置生效。
shellsource /etc/profile
说明
本步骤倒数第2行中有初始密码,请注意保存,登录数据库时需要使用。
shell# mysqld --defaults-file=/etc/my.cnf --initialize 2020-03-18T03:27:13.702385Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.17) initializing of server in progress as process 34014 2020-03-18T03:27:24.112453Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: iNat=)#V2tZu 2020-03-18T03:27:28.576003Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.17) initializing of server has completed
查看打印信息,打印信息中包括“initializing of server has completed”表示初始化数据库完成,且打印信息中“A temporary password is generated for root@localhost: iNat=)V2tZu”的“iNat=)V2tZu”为初始密码。
启动数据库。
注意
如果第一次启动数据库服务,以root用户启动数据库,则启动时会提示缺少mysql.log文件而导致失败。使用mysql用户启动之后,会在/data/mysql/log目录下生成mysql.log文件,再次使用root用户启动则不会报错。
在root权限下修改文件权限。
shellchmod 777 /usr/local/mysql/support-files/mysql.server chown mysql:mysql /var/log/mysql/*
在root权限下启动MySQL。
shellcp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql chkconfig mysql on
以mysql用户启动数据库。
shellsu - mysql service mysql start
登录数据库。
说明
提示输入密码时,请输入3产生的初始密码。
如果采用官网RPM安装方式,则mysql文件在/usr/bin目录下。登录数据库的命令根据实际情况修改。shell# /usr/local/mysql/bin/mysql -uroot -p -S /data/mysql/run/mysql.sock
配置数据库帐号密码。
登录数据库以后,修改通过root用户登录数据库的密码。
shellmysql> alter user 'root'@'localhost' identified by "123456";
创建全域root用户(允许root从其他服务器访问)。
shellmysql> create user 'root'@'%' identified by '123456';
进行授权。
shellmysql> grant all privileges on *.* to 'root'@'%'; mysql> flush privileges;
退出数据库。
执行 \q 或者 exit 退出数据库。
shellmysql> exit
卸载
在root权限下关闭数据库进程。
shellps -ef | grep mysql kill -9 进程ID
在root权限下执行dnf remove mysql命令卸载MySQL。
shelldnf remove mysql
管理数据库用户
创建用户
可以使用CREATE USER语句来创建一个或多个用户,并设置相应的口令。
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
其中:
- username:用户名。
- host:主机名,即用户连接数据库时所在的主机的名字。若是本地用户可用localhost,若在创建的过程中,未指定主机名,则主机名默认为“%”,表示一组主机。
- password:用户的登录密码,密码可以为空,如果为空则该用户可以不需要密码登录服务器,但从安全的角度而言,不推荐这种做法。
使用CREATE USER语句必须拥有数据库的INSERT权限或全局CREATE USER权限。
使用CREATE USER语句创建一个用户帐号后,会在系统自身的数据库的user表中添加一条新记录。若创建的帐户已经存在,则语句执行时会出现错误。
新创建的用户拥有的权限很少,只允许进行不需要权限的操作,如使用SHOW语句查询所有存储引擎和字符集的列表等。
示例
创建密码为123456,用户名为userexample1的本地用户。
> CREATE USER 'userexample1'@'localhost' IDENTIFIED BY '123456';
创建密码为123456,用户名为userexample2,主机名为192.168.1.100的用户。
> CREATE USER 'userexample2'@'192.168.1.100' IDENTIFIED BY '123456';
查看用户
可以使用SHOW GRANTS语句或SELECT语句查看一个或多个用户。
查看特定用户:
SHOW GRANTS [FOR 'username'@'hostname'];
SELECT USER,HOST,PASSWORD FROM mysql.user WHERE USER='username';
查看所有用户:
SELECT USER,HOST FROM mysql.user;
其中:
- username:用户名。
- hostname:主机名。
示例
查看userexample1用户。
> SHOW GRANTS FOR 'userexample1'@'localhost';
查看mysql数据库中所有用户。
> SELECT USER,HOST FROM mysql.user;
修改用户
修改用户名
可以使用RENAME USER语句修改一个或多个已经存在的用户名。
RENAME USER 'oldusername'@'hostname' TO 'newusername'@'hostname';
其中:
- oldusername:旧的用户名。
- newusername:新的用户名。
- hostname:主机名。
RENAME USER语句用于对原有的帐号进行重命名。若系统中旧帐号不存在或者新帐号已存在,则该语句执行时会出现错误。
使用RENAME USER语句,必须拥有数据库的UPDATE权限或全局CREATE USER权限。
修改用户示例
将用户名userexample1修改为userexapme2,主机名为locahost。
> RENAME USER 'userexample1'@'localhost' TO 'userexample2'@'localhost';
修改用户密码
可以使用SET PASSWORD语句修改一个用户的登录密码。
SET PASSWORD FOR 'username'@'hostname' = 'newpassword';
其中:
- FOR 'username'@'hostname':FOR字句,可选项,指定欲修改密码的用户名及主机名。
- 'newpassword':新密码。
在SET PASSWORD语句中,若不加上FOR子句,表示修改当前用户的密码。
FOR字句中必须以'username'@'hostname'的格式给定,username为帐户的用户名,hostname为帐户的主机名。
欲修改密码的帐号必须在系统中存在,否则语句执行时会出现错误。
修改用户密码示例
将用户名为userexample的密码修改为0123456,主机名为locahost。
> SET PASSWORD FOR 'userexample'@'localhost' = '0123456';
删除用户
可以使用DROP USER语句来删除一个或多个用户帐号以及相关的权限。
DROP USER 'username1'@'hostname1' [,'username2'@'hostname2']…;
注意
用户的删除不会影响他们之前所创建的表、索引或其他数据库对象,因为数据库并不会记录创建了这些对象的帐号。
DROP USER语句可用于删除一个或多个数据库帐号,并删除其原有权限。
使用DROP USER语句必须拥有数据库的DELETE权限或全局CREATE USER权限。
在DROP USER语句的使用中,若没有明确地给出帐号的主机名,则该主机名默认为“%”。
示例
删除用户名为userexample的本地用户。
> DROP USER 'userexample'@'localhost';
用户授权
可以使用GRANT语句来对新建用户的授权。
GRANT privileges ON databasename.tablename TO 'username'@'hostname';
其中:
- ON字句:用于指定权限授予的对象和级别。
- privileges:用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所有的权限则使用ALL。
- databasename:数据库名。
- tablename:表名。
- TO字句:用来设定用户密码,以及指定被赋予权限的用户。
- username:用户名。
- hostname:主机名。
如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,如*.*。
如果在TO子句中给系统中存在的用户指定密码,则新密码会将原密码覆盖。
如果权限被授予给一个不存在的用户,则会自动执行一条CREATE USER语句来创建这个用户,但同时必须为该用户指定密码。
示例
对本地用户userexample授予SELECT和INSERT权限。
> GRANT SELECT,INSERT ON *.* TO 'userexample'@'localhost';
删除用户权限
可以使用REVOKE语句来删除一个用户的权限,但此用户不会被删除。
REVOKE privilege ON databasename.tablename FROM 'username'@'hostname';
其中REVOKE语句的参数与GRANT语句的参数含义相同。
要使用 REVOKE 语句,必须拥有数据库的全局CREATE USER权限或UPDATE权限。
示例
删除本地用户userexample的INSERT权限。
> REVOKE INSERT ON *.* FROM 'userexample'@'localhost';
管理数据库
创建数据库
可以使用CREATE DATABASE语句来创建数据库。
CREATE DATABASE databasename;
其中:databasename为数据库名称,且数据库名称不区分大小写。
示例
创建数据库名为databaseexample的数据库。
> CREATE DATABASE databaseexample;
查看数据库
可以使用SHOW DATABASES语句来查看数据库。
SHOW DATABASES;
示例
查看所有数据库。
> SHOW DATABASES;
选择数据库
一般创建表,查询表等操作首先需要选择一个目标数据库。可以使用USE语句来选择数据库。
USE databasename;
其中:databasename为数据库名称。
示例
选择databaseexample数据库。
> USE databaseexample;
删除数据库
可以使用DROP DATABASE语句来删除数据库。
注意
删除数据库要谨慎操作,一旦删除,数据库中的所有表和数据都会删除。
DROP DATABASE databasename;
其中:databasename为数据库名称。
DROP DATABASE命令用于删除创建过(已存在)的数据库,且会删除数据库中的所有表,但数据库的用户权限不会自动删除。
要使用DROP DATABASE,您需要数据库的DROP权限。
DROP SCHEMA是DROP DATABASE的同义词。
示例
删除databaseexample数据库。
> DROP DATABASE databaseexample;
备份数据库
可以在root权限下使用mysqldump命令备份数据库。
备份一个或多个表:
mysqldump [options] databasename [tablename ...] > outfile
备份一个或多个库:
mysqldump [options] -databases databasename ... > outfile
备份所有库:
mysqldump [options] -all-databases > outputfile
其中:
- databasename:数据库名称。
- tablename:数据表名称。
- outfile:数据库备份的文件。
- options:mysqldump命令参数选项,多个参数之间可以使用空格分隔。常用的mysqldump命令参数选项如下:
- -u, --user= username :指定用户名。
- -p, --password[= password]:指定密码。
- -P, --port= portnumber :指定端口。
- -h, --host= hostname :指定主机名。
- -r, --result-file= filename :将导出结果保存到指定的文件中,等同于“>”。
- -t:只备份数据。
- -d:只备份表结构。
示例
备份主机为192.168.202.144,端口为3306,root用户下的所有数据库到alldb.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 --all-databases > alldb.sql
备份主机为192.168.202.144,端口为3306,root用户下的db1数据库到db1.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 --databases db1 > db1.sql
备份主机为192.168.202.144,端口为3306,root用户下的db1数据库的tb1表到db1tb1.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 db1 tb1 > db1tb1.sql
只备份主机为192.168.202.144,端口为3306,root用户下的db1数据库的表结构到db1.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 -d db1 > db1.sql
只备份主机为192.168.202.144,端口为3306,root用户下的db1数据库的数据到db1.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 -t db1 > db1.sql
恢复数据库
可以在root权限下使用mysql命令恢复数据库。
恢复一个或多个表:
mysql -h hostname -P portnumber -u username -ppassword databasename < infile
其中:
- hostname:主机名。
- portnumber:端口号。
- username:用户名。
- password:密码。
- databasename:数据库名。
- infile:mysqldump命令中的outfile参数。
示例
恢复数据库。
# mysql -h 192.168.202.144 -P 3306 -uroot -p123456 -t db1 < db1.sql
openGauss服务器
软件介绍
openGauss是一款开源关系型数据库管理系统,采用木兰宽松许可证v2发行。openGauss内核深度融合华为在数据库领域多年的经验,结合企业级场景需求,持续构建竞争力特性。
安装
安装指南请参见《openGauss rpm安装》。
管理数据库角色
创建角色
可以使用CREATE ROLE语句来创建角色,在数据库界面执行。
CREATE ROLE role_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [EXPIRED] | DISABLE };
其中:
- role_name:角色名。
- password:登录密码。
- option为参数选项,常用的有:
- SYSADMIN | NOSYSADMIN:决定一个新角色是否为"系统管理员",具有SYSADMIN属性的角色拥有系统最高权限。缺省为NOSYSADMIN。
- CREATEDB | NOCREATEDB:定义一个角色是否能创建数据库,若未指定,则默认为NOCREATEDB,即不能创建数据库。
- CREATEROLE | NOCREATEROLE:决定一个角色是否可以创建新角色,若未指定,则默认为NOCREATEROLE,即不能创建新角色。
- INHERIT | NOINHERIT:这些子句决定一个角色是否“继承”它所在组的角色的权限。不推荐使用。
- LOGIN | NOLOGIN:具有LOGIN属性的角色才可以登录数据库。一个拥有LOGIN属性的角色可以认为是一个用户。
- ENCRYPTED | UNENCRYPTED:控制密码存储在系统表里的口令是否加密。按照产品安全要求,密码必须加密存储,所以,UNENCRYPTED在openGauss中禁止使用。因为系统无法对指定的加密口令字符串进行解密,所以如果目前的口令字符串已经是用SHA256加密的格式,则会继续照此存放,而不管是否声明了ENCRYPTED或UNENCRYPTED。这样就允许在dump/restore的时候重新加载加密的口令。
- VALID UNTIL:设置角色失效的时间戳。如果省略了该子句,角色无有效结束时间限制。
- IN ROLE:新角色立即拥有IN ROLE子句中列出的一个或多个现有角色拥有的权限。不推荐使用。
- ROLE:ROLE子句列出一个或多个现有的角色,它们将自动添加为这个新角色的成员,拥有新角色所有的权限。
要使用这条命令,必须拥有 CREATE ROLE 权限或者是系统管理员。
示例
创建一个角色,名为manager,密码为xxxxxxxxx。
openGauss=# CREATE ROLE manager IDENTIFIED BY 'xxxxxxxxx';
创建一个角色,从2015年1月1日开始生效,到2026年1月1日失效。
openGauss=# CREATE ROLE miriam WITH LOGIN PASSWORD 'xxxxxxxxx' VALID BEGIN '2015-01-01' VALID UNTIL '2026-01-01';
查看角色
可以使用SELECT语句或gsql的元命令du或者du+查看角色。
SELECT * FROM pg_roles;
\du
\du+
示例
查看所有角色名包括内置角色。
openGauss=# SELECT rolname from pg_roles;
查看现有角色不包含内置角色。
openGauss=# \du
修改角色
修改用户名
可以使用ALTER ROLE语句修改一个已经存在的角色名。
ALTER ROLE oldrolename RENAME TO newrolename;
其中:
- oldrolename:旧的角色名。
- newrolename:新的角色名。
修改用户示例
将角色名manager修改为newmanager。
openGauss=# ALTER ROLE manager RENAME TO newmanager;
修改用户密码
可以使用ALTER ROLE语句修改一个角色的登录密码。
ALTER ROLE rolename with PASSWORD 'password'
其中:
- rolename:角色名。
- password:密码。
修改角色密码示例
将manager的密码修改为xxxxxxxxx。
openGauss=# ALTER ROLE manager with PASSWORD 'xxxxxxxxx';
删除角色
可以使用DROP ROLE语句来删除角色。在数据库界面执行。
DROP ROLE rolename;
其中:rolename为角色名。
示例
删除manager角色。
openGauss=# DROP ROLE manager;
角色授权
可以使用GRANT语句来对角色授权。
对角色授予表或视图的操作权限:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ALTER | DROP | COMMENT | INDEX | VACUUM } [, ...]
| ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
对角色授予序列的操作权限:
GRANT { { SELECT | UPDATE | USAGE | ALTER | DROP | COMMENT } [, ...]
| ALL [ PRIVILEGES ] }
ON { [ [ LARGE ] SEQUENCE ] sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
对角色授予数据库的操作权限:
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP | ALTER | DROP | COMMENT } [, ...]
| ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
对角色授予函数的操作权限:
GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
对角色授予过程语言的操作权限:
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
将模式的访问权限赋予指定的角色:
GRANT { { CREATE | USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
对角色授予表空间的操作权限:
GRANT { { CREATE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
将角色rolename1的成员关系赋予角色rolename2:
GRANT rolename1 [, ...] TO rolename2 [, ...] [ WITH ADMIN OPTION ]
其中:
- SELECT、INSERT、UPDATE、DELETE、REFERENCES、TRIGGER、USAGE、CREATE、CONNECT、TEMPORARY、TEMP、EXECUTE、ALL PRIVILEGES:用户的操作权限,ALL PRIVILEGES表示所有的权限。
- ON字句:用于指定权限授予的对象。
- tablename:表名。
- TO字句:用来指定被赋予权限的角色。
- rolename1、rolename2:角色名。
- PUBLIC:表示该权限要赋予所有角色,包括那些以后可能创建的用户。
- WITH GRANT OPTION:被授权的用户也可以将此权限赋予他人,否则就不能授权给他人。这个选项不能赋予PUBLIC。
- sequence_name:序列名。
- database_name:数据库名。
- function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]:函数名及其参数。
- lang_name:过程语言名。
- schema_name:模式名。
- tablespace_name:表空间名。
- WITH ADMIN OPTION:表示成员随后就可以将角色的成员关系赋予其他角色,以及撤销其他角色的成员关系。
示例
对manager授予数据库database1的CREATE权限。·
openGauss=# GRANT CREATE ON DATABASE database1 TO manager;
对所有用户授予表table1的所有权限。
openGauss=# GRANT ALL PRIVILEGES ON TABLE table1 TO PUBLIC;
删除用户权限
可以使用REVOKE语句来撤销一个或多个角色的权限。
撤销角色对表的操作权限:
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | ALTER | DROP | COMMENT | INDEX | VACUUM }[, ...]
| ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ];
撤销角色对序列的操作权限:
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | UPDATE | ALTER | DROP | COMMENT }[, ...]
| ALL [ PRIVILEGES ] }
ON { [ [ LARGE ] SEQUENCE ] sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ];
撤销角色对数据库的操作权限:
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | CONNECT | TEMPORARY | TEMP | ALTER | DROP | COMMENT } [, ...]
| ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ];
撤销角色对函数的操作权限:
REVOKE [ GRANT OPTION FOR ]
{ { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ];
撤销角色对过程语言的操作权限:
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ];
撤销角色对模式的操作权限:
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ];
撤销角色对表空间的操作权限:
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ];
删除rolename2的rolename1之间的成员关系:
REVOKE [ ADMIN OPTION FOR ] rolename1 [, ...] FROM rolename2 [, ...] [ CASCADE | RESTRICT ]
其中:
- GRANT OPTION FOR:表示只是撤销对该权限的授权的权力,而不是撤销该权限本身。
- SELECT、INSERT、UPDATE、DELETE、REFERENCES、TRIGGER、USAGE、CREATE、CONNECT、TEMPORARY、TEMP、EXECUTE、ALL PRIVILEGES:用户的操作权限,ALL PRIVILEGES表示所有的权限。
- ON字句:用于指定撤销权限的对象。
- table_name:表名。
- FROM字句:用来指定被撤销权限的角色。
- rolename1、rolename2:角色名。
- PUBLIC:表示撤销隐含定义的、拥有所有角色的组,但并不意味着所有角色都失去了权限,那些直接得到的权限以及通过一个组得到的权限仍然有效。
- sequence_name:序列名。
- CASCADE:撤销所有依赖性权限。
- RESTRICT:不撤销所有依赖性权限。
- database_name:数据库名。
- function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]:函数名及其参数。
- lang_name:过程语言名。
- schema_name:模式名。
- tablespace_name:表空间名。
- ADMIN OPTION FOR:表示传递的授权不会自动收回。
示例
对manager授予数据库database1的CREATE权限。
openGauss=# GRANT CREATE ON DATABASE database1 TO manager;
对所有用户撤销表table1的所有权限。
openGauss=# REVOKE ALL PRIVILEGES ON TABLE table1 FROM PUBLIC;
管理数据库
创建数据库
可以使用CREATE DATABASE语句来创建数据库。在数据库界面使用。
CREATE DATABASE databasename;
其中:databasename为数据库名。
要使用这条命令,必须拥有CREATEDB权限。
示例
创建一个数据库database1。
openGauss=# CREATE DATABASE database1;
选择数据库
可以使用\c语句来选择数据库。
\c databasename;
其中:databasename为数据库名称。
示例
选择database_example数据库。
openGauss=# \c database_example;
查看数据库
可以使用\l语句来查看数据库。
\l;
示例
查看所有数据库。
openGauss=# \l;
删除数据库
可以使用DROP DATABASE语句来删除数据库。
注意
删除数据库要谨慎操作,一旦删除,数据库中的所有表和数据都会删除。
DROP DATABASE databasename;
其中:databasename为数据库名称。
DROP DATABASE会删除数据库的系统目录项并且删除包含数据的文件目录。
DROP DATABASE只能由超级管理员或数据库管理者执行。
示例
删除databaseexample数据库。
openGauss=# DROP DATABASE databaseexample;
备份数据库
gs_dump支持将数据库信息导出至纯文本格式的SQL脚本文件或其他归档文件中。
gs_dump [OPTION]... [DBNAME]
gs_dump -p port_number postgres -f dump1.sql
其中:
- DBNAME前面不需要加短或长选项。DBNAME指定要连接的数据库。 例如: 不需要-d,直接指定DBNAME。
- dump1.sql:数据库备份的文件。
- option:gs_dump命令参数选项,多个参数之间可以使用空格分隔。常用的gs_dump命令参数选项如下:
- -F, --format=c|d|t|p:选择输出格式。格式如下:
- p|plain:输出一个文本SQL脚本文件(默认)。
- c|custom:输出一个自定义格式的归档,并且以目录形式输出,作为gs_restore输入信息。该格式是最灵活的输出格式,因为能手动选择,而且能在恢复过程中将归档项重新排序。该格式默认状态下会被压缩。
- d|directory:该格式会创建一个目录,该目录包含两类文件,一类是目录文件,另一类是每个表和blob对象对应的数据文件。
- t|tar:输出一个tar格式的归档形式,作为gs_restore输入信息。tar格式与目录格式兼容;tar格式归档形式在提取过程中会生成一个有效的目录格式归档形式。但是,tar格式不支持压缩且对于单独表有8GB的大小限制。此外,表数据项的相应排序在恢复过程中不能更改.
- -h, --host=HOSTNAME:指定主机名。
- -p, --port=PORT:指定端口。
- -U, --username=NAME:指定连接的用户名。
- -W, --password=PASSWORD:指定用户连接的密码。如果主机的认证策略是trust,则不会对系统管理员进行密码验证,即无需输入-W选项;如果没有-W选项,并且不是系统管理员,“Dump Restore工具”会提示用户输入密码。
- -w, --no-password:不出现输入密码提示。如果主机要求密码认证并且密码没有通过其它形式给出,则连接尝试将会失败。 该选项在批量工作和不存在用户输入密码的脚本中很有帮助。
- -F, --format=c|d|t|p:选择输出格式。格式如下:
示例
执行gs_dump,导出postgres数据库全量信息,导出的MPPDB_backup.sql文件格式为纯文本格式。
[openGauss@localhost ~]# gs_dump -U omm -W password -f backup/MPPDB_backup.sql -p port postgres -F p
执行gs_dump,导出postgres数据库全量信息,导出的MPPDB_backup.dmp文件格式为自定义归档格式。
gs_dump -U omm -W password -f backup/MPPDB_backup.dmp -p port postgres -F c
恢复数据库
gs_restore是openGauss提供的针对gs_dump导出数据的导入工具。通过此工具可将由gs_dump生成的导出文件进行导入。
gs_restore [OPTION]... FILE
其中:
- FILE没有短选项或长选项。用来指定归档文件所处的位置。
- 作为前提条件,需输入dbname或-l选项。不允许用户同时输入dbname和-l选项。
- gs_restore默认是以追加的方式进行数据导入。为避免多次导入造成数据异常,在进行导入时,建议使用“-c” 参数,在重新创建数据库对象前,清理(删除)已存在于将要还原的数据库中的数据库对象。
- option:通用参数如下:
- -f,--file=FILENAME:指定生成脚本的输出文件,或使用-l时列表的输出文件。
- -d, --dbname=NAME:连接数据库dbname并直接导入到该数据库中。
- -h, --host=HOSTNAME:指定主机名。
- -p, --port=PORT:指定端口。
- -U, --username=NAME:指定连接的用户名。
- -W, --password=PASSWORD:指定用户连接的密码。如果主机的认证策略是trust,则不会对系统管理员进行密码验证,即无需输入-W参数;如果没有-W参数,并且不是系统管理员,“gs_restore”会提示用户输入密码。
示例
执行gs_restore,将导出的MPPDB_backup.dmp文件(自定义归档格式)导入到postgres数据库。
[openGauss@localhost ~]# gs_restore -W password backup/MPPDB_backup.dmp -p port -d postgres
GreatSQL服务器
软件介绍
GreatSQL 数据库是开放原子开源基金会旗下捐赠项目,拥有中国信通院可信开源社区+可信开源项目双认证。
GreatSQL 数据库是一款 开源免费 数据库,可在普通硬件上满足金融级应用场景,具有 高可用、高性能、高兼容、高安全 等特性,可作为 MySQL 或 Percona Server for MySQL 的理想可选替换。
GreatSQL 致力于保持开源的开放性。GreatSQL 采用 GPLv2 协议。
GreatSQL 数据库由 GreatSQL 社区 主导负责运营维护,已有众多社区维护者、爱好者及用户参与,包括提交代码、功能测试验证、发现 Bug 以及文档改进等多方面贡献。
配置环境
说明
以下环境配置仅为参考示例,具体配置视实际需求做配置
关闭防火墙并取消开机自启动
说明
测试环境下通常会关闭防火墙以避免部分网络因素影响,视实际需求做配置。
在root权限下停止防火墙。
shellsystemctl stop firewalld
在root权限下关闭防火墙。
shellsystemctl disable firewalld
说明
执行disable命令关闭防火墙的同时,也取消了开机自启动。
修改SELINUX为disabled
在root权限下修改配置文件。
shellsed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux
创建组和用户
说明
服务器环境下,为了系统安全,通常会为进程分配单独的用户,以实现权限隔离。本章节创建的组和用户都是操作系统层面的,不是数据库层面的。
在root权限下创建MySQL用户(组)。
groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql
搭建数据盘
说明
测试极限性能时,建议单独挂载IO性能更优的NVME SSD存储介质创建GreatSQL测试实例,避免磁盘IO对性能测试结果的影响,本文以单独挂载NVME SSD为例,参考步骤1~步骤4。
非性能测试时,在root权限下执行以下命令,创建数据目录即可。然后跳过本小节:
mkdir /data
在root权限下创建文件系统(以xfs为例,根据实际需求创建文件系统),若磁盘之前已做过文件系统,执行此命令会出现报错,可使用-f参数强制创建文件系统。
shellmkfs.xfs /dev/nvme0n1
在root权限下创建数据目录。
shellmkdir /data
在root权限下挂载磁盘。
shellmount -o noatime,nobarrier /dev/nvme0n1 /data
创建数据库目录
在已创建的数据目录 /data 基础上,使用root权限继续创建进程所需的相关目录并授权MySQL用户(组)。
mkdir -p /data/GreatSQL
cd /data/GreatSQL
chown -R mysql:mysql /data/GreatSQL
安装、运行和卸载
安装
注意
安装GreatSQL前请先检查是否已先安装了MySQL/Percona Server For MySQL/MariaDB等这些MySQL同态数据库,如果已经安装需要先卸载,否则无法再安装GreatSQL,会产生冲突。
配置本地yum源,详细信息请参考搭建repo服务器章节。
清除缓存。
shelldnf clean all
创建缓存。
shelldnf makecache
在root权限下安装MySQL服务器。
shelldnf install greatsql-server
如果安装时遇到冲突报错,需要先卸载MySQL/Percona Server For MySQL/MariaDB等MySQL同态数据库。
查看安装后的rpm包。
shellrpm -qa | grep greatsql-server
运行
在root权限下修改/etc/my.cnf文件,包括软件安装路径basedir、数据路径datadir、监听端口号port、内存缓冲区innodb_buffer_pool_size等根据实际情况修改。
shellvi /etc/my.cnf
编辑/etc/my.cnf内容如下(下面是一个最基本参考):
shell[client] datadir = /data/GreatSQL/mysql.sock [mysqld] !includedir /etc/my.cnf.d user = mysql datadir = /data/GreatSQL socket = /data/GreatSQL/mysql.sock log-error = /data/GreatSQL/mysqld.log pid-file = /data/GreatSQL/mysqld.pid slow_query_log = ON long_query_time = 0.01 log_slow_verbosity = FULL log_error_verbosity = 3 innodb_buffer_pool_size = 1G innodb_redo_log_capacity = 256M innodb_io_capacity = 10000 innodb_io_capacity_max = 20000 innodb_flush_sync = OFF
如果想要让GreatSQL获得更好的运行状态和性能,可以参考GreatSQL社区提供的my.cnf配置文件参考模板。
确保my.cnf配置文件修改正确。
shellcat /etc/my.cnf
- shell
systemctl start mysqld
首次启动会比较慢,可能需要数分钟,因为包含初始化GreatSQL数据库的工作。
待初始化完成后,查看
datadir
配置参数对应的目录下是否已有数据文件,以及相应的日志文件/data/GreatSQL/mysqld.log
。shellls -la /data/GreatSQL
查看
/data/GreatSQL/mysqld.log
日志文件,若其中包括“Bootstrapping complete”表示初始化数据库完成,且包含“A temporary password is generated for root@localhost: =dz;kqt8svrF”这样的信息,则其中的“=dz;kqt8svrF”为GreatSQL数据库root账户的初始密码,第一次登入后要立即修改才能继续使用GreatSQL。 登录数据库。
说明
- 提示输入密码时,请输入步骤4产生的初始密码。
shellmysql -uroot -p
修改初始化密码
如上面截图所示,首次登录GreatSQL数据库后,需要先修改临时初始密码才能继续后面的操作。
shellgreatsql [(none)]> \s ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. greatsql [(none)]> ALTER USER USER() IDENTIFIED BY 'GreatSQL@202X'; Query OK, 0 rows affected (0.00 sec)
创建一个允许从任何服务器访问的数据库账户GreatSQL,并授权允许该账户对名为test的数据库下所有数据对象的所有操作权限
shell-- 在这里指定新账户GreatSQL的密码为"GreatSQL-202X",建议修改成您自己设定成其他密码 greatsql [(none)]> CREATE USER GreatSQL@'%' IDENTIFIED BY 'GreatSQL-202X'; Query OK, 0 rows affected (0.00 sec) greatsql [(none)]> GRANT ALL ON test.* TO GreatSQL@'%'; Query OK, 0 rows affected (0.00 sec)
创建名为test的新数据库
shellgreatsql [(none)]> CREATE DATABASE test; Query OK, 1 row affected (0.00 sec)
退出数据库。
执行
\q
或者exit
退出数据库。shellgreatsql> exit
切换新创建的GreatSQL账户登录,并操作数据库
shell-- 这里使用上面创建GreatSQL新账户时指定的密码 "GreatSQL-202X",或者修改成您自己设定的密码 -- 后面指定test是指登录后立即使用test数据库,要带空格,否则会被认为是密码 mysql -uGreatSQL -p test Enter password: ... -- 创建新表 greatsql [test]> CREATE TABLE t1(id INT PRIMARY KEY); Query OK, 0 rows affected (1.48 sec) -- 查看都有哪些数据表 greatsql [test]> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) -- 写入测试数据 greatsql [test]> INSERT INTO t1 SELECT RAND()*1024; Query OK, 1 row affected (0.34 sec) Records: 1 Duplicates: 0 Warnings: 0 -- 查询数据 greatsql [test]> SELECT * FROM t1; +-----+ | id | +-----+ | 565 | +-----+ 1 row in set (0.00 sec)
这就完成了对GreatSQL数据库的基本操作。
更多相关SQL命令/语法详见手册:SQL Statements。
更多基于 GreatSQL 的应用开发内容请参考:应用开发。
卸载GreatSQL
在root权限下关闭数据库进程。
shellsystemctl stop mysqld
在root权限下执行dnf remove -y greatsql-server命令卸载GreatSQL。
shelldnf remove -y greasql-server
管理数据库用户
创建用户
可以使用CREATE USER语句来创建一个或多个用户,并设置相应的口令。
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
其中:
- username:用户名。
- host:主机名,即用户连接数据库时所在的主机的名字。若是本地用户可用localhost,若在创建的过程中,未指定主机名,则主机名默认为“%”,表示一组主机。
- password:用户的登录密码,密码可以为空,如果为空则该用户可以不需要密码登录服务器,但从安全的角度而言,不推荐这种做法。
使用CREATE USER语句必须拥有数据库的INSERT权限或全局CREATE USER权限。
使用CREATE USER语句创建一个用户帐号后,会在系统自身的数据库的user表中添加一条新记录。若创建的帐户已经存在,则语句执行时会出现错误。
新创建的用户拥有的权限很少,只允许进行不需要权限的操作,如使用SHOW语句查询所有存储引擎和字符集的列表等。
示例
创建密码为"GreatSQL@202X-local",用户名为GreatSQL_local的本地用户。
CREATE USER GreatSQL_local@localhost IDENTIFIED BY 'GreatSQL@202X';
创建密码为"GreatSQL@202X-remote",用户名为GreatSQL_remote,主机名为192.168.1.100的用户。
CREATE USER 'GreatSQL_remote'@'192.168.1.100' IDENTIFIED BY 'GreatSQL@202X-remote';
查看用户
可以使用SHOW GRANTS语句或SELECT语句查看一个或多个用户。
查看特定用户:
SHOW GRANTS [FOR 'username'@'hostname'];
SELECT USER,HOST,PASSWORD FROM mysql.user WHERE USER='username';
查看所有用户:
SELECT USER,HOST FROM mysql.user;
其中:
- username:用户名。
- hostname:主机名。
示例
查看userexample1用户。
SHOW GRANTS FOR GreatSQL;
+----------------------------------------------------+
| Grants for GreatSQL@% |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `GreatSQL`@`%` |
| GRANT ALL PRIVILEGES ON `test`.* TO `GreatSQL`@`%` |
+----------------------------------------------------+
2 rows in set (0.00 sec)
修改用户
修改用户名
可以使用RENAME USER语句修改一个或多个已经存在的用户名。
RENAME USER 'oldusername'@'hostname' TO 'newusername'@'hostname';
其中:
- oldusername:旧的用户名。
- newusername:新的用户名。
- hostname:主机名。
RENAME USER语句用于对原有的帐号进行重命名。若系统中旧帐号不存在或者新帐号已存在,则该语句执行时会出现错误。
使用RENAME USER语句,必须拥有数据库的UPDATE权限或全局CREATE USER权限。
修改用户示例
将用户名userexample1修改为userexapme2,主机名为locahost。
> RENAME USER 'userexample1'@'localhost' TO 'userexample2'@'localhost';
修改用户密码
可以使用SET PASSWORD语句修改一个用户的登录密码。
SET PASSWORD FOR 'username'@'hostname' = 'newpassword';
其中:
- FOR 'username'@'hostname':FOR字句,可选项,指定欲修改密码的用户名及主机名。
- 'newpassword':新密码。
在SET PASSWORD语句中,若不加上FOR子句,表示修改当前用户的密码。
FOR字句中必须以'username'@'hostname'的格式给定,username为帐户的用户名,hostname为帐户的主机名。
欲修改密码的帐号必须在系统中存在,否则语句执行时会出现错误。
建议:更推荐使用ALTER USER
命令来修改数据库中的账号密码。
修改用户密码示例
将用户名为GreatSQL_remote的密码修改为"GreatSQL@@202X-remote"。
SET PASSWORD FOR 'GreatSQL_remote'@'192.168.1.100' = 'GreatSQL@@202X-remote';
删除用户
可以使用DROP USER语句来删除一个或多个用户帐号以及相关的权限。
DROP USER 'username1'@'hostname1' [,'username2'@'hostname2']…;
注意
用户的删除不会影响他们之前所创建的表、索引或其他数据库对象,因为数据库并不会记录创建了这些对象的帐号。
DROP USER语句可用于删除一个或多个数据库帐号,并删除其原有权限。
使用DROP USER语句必须拥有数据库的DELETE权限或全局CREATE USER权限。
在DROP USER语句的使用中,若没有明确地给出帐号的主机名,则该主机名默认为“%”。
示例
删除用户名为userexample的本地用户。
DROP USER 'userexample'@'localhost';
用户授权
可以使用GRANT语句来对新建用户的授权。
GRANT privileges ON databasename.tablename TO 'username'@'hostname';
其中:
- ON字句:用于指定权限授予的对象和级别。
- privileges:用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所有的权限则使用ALL。
- databasename:数据库名。
- tablename:表名。
- TO字句:用来设定用户密码,以及指定被赋予权限的用户。
- username:用户名。
- hostname:主机名。
如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,如*.*。
如果在TO子句中给系统中存在的用户指定密码,则新密码会将原密码覆盖。
如果权限被授予给一个不存在的用户,则会自动执行一条CREATE USER语句来创建这个用户,但同时必须为该用户指定密码。
示例
对本地用户userexample授予SELECT和INSERT权限。
GRANT SELECT,INSERT ON *.* TO 'userexample'@'localhost';
回收用户权限
可以使用REVOKE语句来回收一个用户的权限,但此用户不会被删除。
REVOKE privilege ON databasename.tablename FROM 'username'@'hostname';
其中REVOKE语句的参数与GRANT语句的参数含义相同。
要使用 REVOKE 语句,必须拥有数据库的全局CREATE USER权限或UPDATE权限。
示例
回收本地用户userexample的INSERT权限。
REVOKE INSERT ON *.* FROM 'userexample'@'localhost';
管理数据库
创建数据库
可以使用CREATE DATABASE语句来创建数据库。
CREATE DATABASE databasename;
其中:databasename为数据库名称,且数据库名称不区分大小写。
示例
创建数据库名为databaseexample的数据库。
CREATE DATABASE databaseexample;
查看数据库
可以使用SHOW DATABASES语句来查看数据库。
SHOW DATABASES;
示例
查看所有数据库。
SHOW DATABASES;
选择数据库
一般创建表,查询表等操作首先需要选择一个目标数据库。可以使用USE语句来选择数据库。
USE databasename;
其中:databasename为数据库名称。
示例
选择databaseexample数据库。
USE databaseexample;
删除数据库
可以使用DROP DATABASE语句来删除数据库。
注意
删除数据库要谨慎操作,一旦删除,数据库中的所有表和数据都会删除。
DROP DATABASE databasename;
其中:databasename为数据库名称。
DROP DATABASE命令用于删除创建过(已存在)的数据库,且会删除数据库中的所有表,但数据库的用户权限不会自动删除。
要使用DROP DATABASE,您需要数据库的DROP权限。
DROP SCHEMA是DROP DATABASE的同义词。
示例
删除databaseexample数据库。
DROP DATABASE databaseexample;
备份数据库
可以在root权限下使用mysqldump命令备份数据库。
备份一个或多个表:
mysqldump [options] databasename [tablename ...] > outfile
备份一个或多个库:
mysqldump [options] -databases databasename ... > outfile
备份所有库:
mysqldump [options] -all-databases > outputfile
其中:
- databasename:数据库名称。
- tablename:数据表名称。
- outfile:数据库备份的文件。
- options:mysqldump命令参数选项,多个参数之间可以使用空格分隔。常用的mysqldump命令参数选项如下:
- -u, --user= username :指定用户名。
- -p, --password[= password]:指定密码,在下面的例子中密码都指定为123456,您需要根据实际情况调整。
- -P, --port= portnumber :指定端口。
- -h, --host= hostname :指定主机名。
- -r, --result-file= filename :将导出结果保存到指定的文件中,等同于“>”。
- -t:只备份数据。
- -d:只备份表结构。
示例
备份主机为192.168.202.144,端口为3306,root用户下的所有有权限读取的数据库到alldb.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 --all-databases > alldb.sql
备份主机为192.168.202.144,端口为3306,root用户下的db1数据库到db1.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 --databases db1 > db1.sql
备份主机为192.168.202.144,端口为3306,root用户下的db1数据库的tb1表到db1tb1.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 db1 tb1 > db1tb1.sql
只备份主机为192.168.202.144,端口为3306,root用户下的db1数据库的表结构到db1.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 -d db1 > db1.sql
只备份主机为192.168.202.144,端口为3306,root用户下的db1数据库的数据到db1.sql中。
mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 -t db1 > db1.sql
恢复数据库
可以在root权限下使用mysql命令恢复数据库。
恢复一个或多个表:
mysql -h hostname -P portnumber -u username -ppassword databasename < infile
其中:
- hostname:主机名。
- portnumber:端口号。
- username:用户名。
- password:密码。
- databasename:数据库名。
- infile:mysqldump命令中的outfile参数。
示例
恢复数据库。
# mysql -h 192.168.202.144 -P 3306 -uroot -p123456 -t db1 < db1.sql