网站域名空间管理,学校网站建设评估,wordpress格式化sql串,培训机构网站php源码1.关系型数据库相关概念关系Relational #xff1a;关系就是二维表#xff0c;其中#xff1a;表中的行、列次序并不重要行row#xff1a;表中的每一行#xff0c;又称为一条记录record列column#xff1a;表中的每一列#xff0c;称为属性#xff0c;字段#xff0c;…1.关系型数据库相关概念关系Relational 关系就是二维表其中表中的行、列次序并不重要行row表中的每一行又称为一条记录record列column表中的每一列称为属性字段域field主键Primary keyPK ,一个或多个字段的组合, 用于惟一确定一个记录的字段一张表只有一个主 键, 主键字段不能为空NULL唯一键Unique key: 一个或多个字段的组合,用于惟一确定一个记录的字段,一张表可以有多个UK,而 且UK字段可以为NULL域domain属性的取值范围如性别只能是男和女两个值人类的年龄只能0-1502.常用关系数据库MySQL: MySQL, MariaDB, Percona ServerPostgreSQL: 简称为pgsqlEnterpriseDBOracleMSSQL ServerDB23联系类型一对一联系(1:1)一对多联系(1:n)外键多对多联系(m:n)增加第三张表3联系类型4.数据库规划流程1. 收集数据得到字段 收集必要且完整的数据项 转换成数据表的字段2. 把字段分类归入表建立表的关联 关联表和表间的关系 分割数据表并建立关联的优点 节省空间 减少输入错误 方便数据修改3. 规范化数据库5.数据库的正规化在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF)其余范式以次类 推。一般数据库只需满足第三范式(3NF)即可 规则是死的,人是活的,所以范式是否必须遵守,要看业务需要而定 掌握范式的目的是为了在合适的场景下违反范式第一范式1NF无重复的列每一列都是不可分割的基本数据项同一列中不能有多个值即实体中的某个属性不能有 多个值或者不能有重复的属性确保每一列的原子性。除去同类型的字段就是无重复的列 说明第一范式(1NF)是对关系模式的基本要求不满足第一范式(1NF)的数据库就不是关系数据 库第二范例2NF第二范式必须先满足第一范式属性完全依赖于主键要求表中的每个行必须可以被唯一地区分通常 为表加上每行的唯一标识PK非PK的字段需要与整个PK有直接相关性,即非PK的字段不能依赖于部分主键第三范式3NF满足第三范式必须先满足第二范式属性非主键属性不依赖于其它非主键属性。第三范式要求一个数据 表中不包含已在其它表中已包含的非主关键字信息非PK的字段间不能有从属关系6.MySQL安装方式介绍和快速安装安装方式程序包管理器管理的程序包源代码编译安装二进制格式的程序包展开至特定路径并经过简单配置后即可使用centos8安装光盘直接提供mysql-server8.0mariadb-server10.3.17centos7安装光盘直接提供mariadb-server5.5 服务包mariadb 客户端工具包centos6mysql-serve5.1 服务器包mysql 客户端工具包范例[14:07:38 rootcentos7 yum.repos.d]$vim mysql.repo 创建仓库[mysql]namemysql5.7baseurlhttps://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/gpgcheck0配置清华大学仓库路径[14:10:44 rootcentos7 yum.repos.d]$yum list |grep mysql*可以看到包[14:10:44 rootcentos7 yum.repos.d]$yum -y install mysql-community-server[14:19:40 rootcentos7 yum.repos.d]$systemctl enable --now mysqldss-ntl 发现端口3306开启[14:22:34 rootcentos7 yum.repos.d]$mysql 5.7版本会默认创建数据库用户rootlocalhostERROR1045 (28000): Access denied for user rootlocalhost(using password: NO)[14:27:43 rootcentos7 yum.repos.d]$grep password /var/log/mysqld.log 临时密码位置2021-01-28T06:20:26.300014Z 1 [Note] A temporary password is generated for rootlocalhost: AK:1dY92qyb2021-01-28T06:21:55.139910Z 2 [Note] Access denied for user rootlocalhost(using password: NO)2021-01-28T06:22:45.441398Z 3 [Note] Access denied for user rootlocalhost(using password: NO)[14:28:11 rootcentos7 yum.repos.d]$mysql -uroot -pAK:1dY92qyb初始密码登录后会要求你修改密码mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connectionid is 4Server version:5.7.33Copyright (c)2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Typehelp; or \h for help. Type \c to clearthe current input statement.mysql ALTER USER rootlocalhost identified by Yangbo!0; 注意密码要设置的复杂一些Query OK,0 rows affected (0.00sec)mysqlstatus--------------mysql Ver14.14 Distrib 5.7.33, forLinux (x86_64) using EditLine wrapperConnectionid: 5Current database:Current user: rootlocalhostSSL: NotinuseCurrent pager: stdoutUsing outfile:Using delimiter: ;Server version:5.7.33Protocol version:10Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. characterset: utf8UNIX socket:/var/lib/mysql/mysql.sockUptime:24 min 13secThreads:1 Questions: 22 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 100 Queries per second avg: 0.015--------------mysqlexit修改初始密码吗方法二[14:45:28 rootcentos7 yum.repos.d]$mysqladmin -uroot -pAK:1dY92qyb password Yangbo!0Ubuntu默认启用空密码登录7初始化脚本提高安全性运行脚本mysql_secure_installation设置数据库管理员root口令禁止root远程登录删除anonymous用户帐号删除test数据库范例针对5.6版本安全加固[15:06:20 rootcentos7 yum.repos.d]$mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connectionid is 3Server version:5.6.51MySQL Community Server (GPL)Copyright (c)2000, 2021, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Typehelp; or \h for help. Type \c to clearthe current input statement.默认空密码登录mysql selectuser,host from mysql.user;-----------------| user | host |-----------------| root | 127.0.0.1 || root | ::1 || | centos7 || root | centos7 || | localhost |匿名账号| root | localhost |-----------------6 rows in set (0.00sec)代表6个用户[15:12:11 rootcentos7 yum.repos.d]$mysql_secure_installationNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQLSERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!In order to log into MySQL to secure it, well need the currentpassword for the root user. If youve just installed MySQL, andyou havent set the root password yet, the password will be blank,so you should just press enter here.Enter current passwordfor root (enter fornone): 没有密码回车OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MySQLroot user without the proper authorisation.Set root password? [Y/n] y 是否设置root口令New password:Re-enter new password:Password updated successfully!Reloading privilege tables..... Success!By default, a MySQL installation has an anonymous user, allowing anyoneto log into MySQL without having to have a user account createdforthem. This is intended onlyfor testing, and to makethe installationgo a bit smoother. You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n] y 是否移除匿名用户... Success!Normally, root should only be allowed to connect fromlocalhost. Thisensures that someone cannot guess at the root password from the network.Disallow rootlogin remotely? [Y/n] y 禁用远程登录... Success!By default, MySQL comes with a database namedtestthat anyone canaccess. This is also intended onlyfortesting, and should be removedbefore moving into a production environment.Remove test database and access to it? [Y/n] y-Dropping test database...ERROR1008 (HY000) at line 1: Cant drop databasetest; database doesnt exist... Failed!Not critical, keep moving...-Removing privileges on test database...... Success!Reloading the privilege tables will ensure that all changes made so farwill take effect immediately.Reload privilege tables now? [Y/n] y 是否生效... Success!Alldone! If youve completed all of the above steps, your MySQLinstallation should now be secure.Thanksfor using MySQL!Cleaning up...[15:18:19 rootcentos7 yum.repos.d]$mysqlERROR1045 (28000): Access denied for user rootlocalhost(using password: NO)再次空口令登录失败[15:17:56 rootcentos7 yum.repos.d]$mysql -uroot -p123456登录成功mysql selectuser,host from mysql.user;-----------------| user | host |-----------------| root | 127.0.0.1 || root | ::1 || root | localhost |-----------------3 rows in set (0.00sec)删除了之前的不安全登录8.MYSQL组成客户端程序mysql: 交互式或非交互式的CLI工具mysqldump备份工具基于mysql协议向mysqld发起查询请求并将查得的所有数据转换成insert等写操作语句保存文本文件中mysqladmin基于mysql协议管理mysqld[15:35:39 rootcentos7 yum.repos.d]$mysqladmin -uroot -pYangbo!0用法帮助ping[15:29:05 rootcentos7 yum.repos.d]$mysqladmin -uroot -pYangbo!0 pingmysqladmin: [Warning] Using a password on the command line interface can be insecure.mysqld is alive验证数据库是否正常运行shutdown[15:29:05 rootcentos7 yum.repos.d]$mysqladmin -uroot -pYangbo!0shutdown安全的关闭数据库要耐心等待mysqlimport数据导入工具服务器端程序mysqld_safemysqldmysqld_multi 多实例 示例mysqld_multi --example用户账户mysql用户账号由两部分组成USERNAMEHOST说明 HOST限制此用户可通过哪些远程主机连接mysql服务器 支持使用通配符% 匹配任意长度的任意字符,相当于shell中*, 示例: 172.16.0.0/255.255.0.0 或 172.16.%.%_ 匹配任意单个字符9.mysql 客户端命令客户端命令本地执行每个命令都完整形式和简写格式mysql\h, helpmysql\uusemysql\sstatusmysql \!system服务端命令通过mysql协议发往服务器执行并取回结果命令末尾都必须使用命令结束符号默 认为分号-A, --no-auto-rehash 禁止补全-u, --user用户名,默认为root-h, --host服务器主机,默认为localhost-p, --passowrd 用户密码,建议使用-p,默认为空密码-P, --port服务器端口-S, --socket指定连接socket文件路径-D, --database指定默认数据库-C, --compress 启用压缩-e “SQL“ 执行SQL命令-V, --version 显示版本-v --verbose 显示详细信息--print-defaults 获取程序默认使用的配置范例mysql的配置文件修改提示符[16:15:51 rootcentos7 yum.repos.d]$mysql -uroot -pYangbo!0 --prompt(\\u\\h) [\\d]\\_临时修改mysql提示符[16:17:49 rootcentos7 ~]$vim /etc/my.cnf[mysql]prompt\\r:\\m:\\s(\\u\\h) [\\d]\\_写入配置文件永久保存范例配置所有MySQL客户端的自动登录[16:17:49 rootcentos7 ~]$vim /etc/my.cnf[mysql]userrootpasswordYangbo!0服务器端配置服务器端配置文件/etc/my.cnf #Global选项/etc/mysql/my.cnf #Global选项~/.my.cnf #User-specific 选项配置文件格式[mysqld][mysqld_safe][mysqld_multi][mysql][mysqldump][server][client]格式parameter valuesocket地址ip socket: 监听在tcp的3306端口支持远程通信 侦听3306/tcp端口可以在绑定有一个或全部接 口IP上 unix sock: 监听在sock文件上仅支持本机通信, 如/var/lib/mysql/mysql.sock范例关闭远程连接通过socket文件连接vim /etc/my.cnf[mysqld]skip-networking110.通用二进制格式安装MySQL 5.7安装相关包[16:45:03 rootcentos7 ~]$yum -y install libaio numactl-libs用户和组[16:45:03 rootcentos7 ~]$groupadd mysql[16:45:43 rootcentos7 ~]$useradd -r -g mysql -s /bin/false mysql准备程序文件[16:54:22 rootcentos7 ~]$wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.31-linux-glibc2.12[16:58:44 rootcentos7 ~]$tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/[16:59:46 rootcentos7 ~]$cd /usr/local/[16:59:56 rootcentos7 local]$ln -s mysql-5.7.31-linux-glibc2.12-x86_64/mysql[17:03:30 rootcentos7 local]$chown -R root:root /usr/local/mysql/准备环境变量[17:05:43 rootcentos7 local]$echo PATH/usr/local/mysql/bin:$PATH /etc/profile.d/mysql.sh[17:09:09 rootcentos7 local]$. /etc/profile.d/mysql.sh准备配置文件[17:13:41 rootcentos7 local]$cp /etc/my.cnf{,.bak} 备份原文件[17:14:31 rootcentos7 local]$vim /etc/my.cnf[mysqld]datadir/data/mysqlskip_name_resolve1socket/data/mysql/mysql.socklog-error/data/mysql/mysql.logpid-file/data/mysql/mysql.pid[client]socket/data/mysql/mysql.sock生成数据库文件并提取root密码[17:46:49 rootcentos7 /]$mkdirdata[17:47:00 rootcentos7 /]$mysqld --initialize --usermysql --datadir/data/mysql5.6版本生成数据库/usr/local/mysql/scripts/mysql_install_db --usermysql -- datadir/data/mysql --basedir/usr/local/mysql/[17:48:51 rootcentos7 /]$grep password /data/mysql/mysql.log2021-01-28T09:47:16.362119Z 1 [Note] A temporary password is generated for rootlocalhost: Rd0iVsQ,564初始密码[17:53:37 rootcentos7 /]$awk /temporary password/{print $11} /data/mysql/mysql.logRd0iVsQ,564准备服务脚本和启动[17:53:54 rootcentos7 /]$cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld[18:01:31 rootcentos7 /]$chkconfig --add mysqld[18:01:40 rootcentos7 /]$service mysqld start修改口令[18:01:51 rootcentos7 /]$mysqladmin -uroot -pRd0iVsQ,564password yangbo[18:05:06 rootcentos7 /]$mysql -uroot -pyangbo登录成功11.源码编译安装mysql-5.6.51安装相关依赖包yum -y install gcc gcc-c cmake bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel perl-Data-Dumper准备用户和数据目录useradd -r -s /sbin/nologin -d /data/mysql mysql准备用户和数据目录[18:25:56 rootcentos7 ~]$mkdir -p /data/mysql[18:26:24 rootcentos7 data]$chown mysql:mysql /data/mysql下载源码包并解压缩源码包[18:21:06 rootcentos7 ~]$tar -xf mysql-5.6.51.tar.gz -C /usr/local/src[18:27:03 rootcentos7 data]$cd /usr/local/src/mysql-5.6.51/[18:52:44 rootcentos7 mysql-5.6.51]$cmake . \-DCMAKE_INSTALL_PREFIX/apps/mysql \-DMYSQL_DATADIR/data/mysql/\-DSYSCONFDIR/etc/\-DMYSQL_USERmysql \-DWITH_INNOBASE_STORAGE_ENGINE1\-DWITH_ARCHIVE_STORAGE_ENGINE1\-DWITH_BLACKHOLE_STORAGE_ENGINE1\-DWITH_PARTITION_STORAGE_ENGINE1\-DWITHOUT_MROONGA_STORAGE_ENGINE1\-DWITH_DEBUG0\-DWITH_READLINE1\-DWITH_SSLsystem \-DWITH_ZLIBsystem \-DWITH_LIBWRAP0\-DENABLED_LOCAL_INFILE1\-DMYSQL_UNIX_ADDR/data/mysql/mysql.sock \-DDEFAULT_CHARSETutf8 \-DDEFAULT_COLLATIONutf8_general_ci[18:52:44 rootcentos7 mysql-5.6.51]$make make install如果出错rm-rf CMakeCache.txt准备环境变量[18:52:44 rootcentos7 mysql-5.6.51]$echo PATH/apps/mysql/bin:$PATH /etc/profile.d/mysql.sh[18:59:11 rootcentos7 mysql-5.6.51]$. /etc/profile.d/mysql.sh生成数据库文件[18:59:33 rootcentos7 mysql-5.6.51]$cd /apps/mysql/[19:01:20 rootcentos7 mysql]$scripts/mysql_install_db --datadir/data/mysql/ --usermysql准备配置文件[19:01:20 rootcentos7 mysql]$cp -b /apps/mysql/support-files/my-default.cnf /etc/my.cnfcp: overwrite ‘/etc/my.cnf’? y准备启动脚本并启动服务[19:03:28 rootcentos7 mysql]$cp /apps/mysql/support-files/mysql.server /etc/init.d/mysqld[19:09:23 rootcentos7 mysql]$chkconfig --add mysqld[19:09:32 rootcentos7 mysql]$service mysqld start安全初始化mysql_secure_installation
