[转] 数据库介绍 (MySQL 安装 体系结构, 基本管理)
第 1 章 数据库介绍及 MySQL 安装
1.1 数据库简介
数据库, 简而言之可视为电子化的文件柜 -- 存储电子文件的处所, 用户可以对文件中的数据运行新增, 截取, 更新, 删除等操作.
所谓 "数据库" 系以一定方式储存在一起, 能予多个用户共享, 具有尽可能小的冗余度, 与应用程序彼此独立的数据集合.
1.1.1 什么是数据
数据是指对客观事件进行记录并可以鉴别的符号, 是对客观 事物的性质, 状态以及相互关系等进行记载的物理符号或这些物 理符号的组合. 它是可识别的, 抽象的符号.
1.1.2 数据库管理系统
非关系型数据库
NoSQL: 非关系型数据库 (Not only SQL)
不是否定关系型数据库, 做关系型数据库的的补充.
想做老大, 先学会做老二.
关系型数据库
关系型数据库的特点
二维表
典型产品 Oracle 传统企业, MySQL 是互联网企业
数据存取是通过 SQL(结构化查询语句)
最大特点, 数据安全性方面强 (ACID)
1.1.3 NoSQL 特性总览
l 不是否定关系型数据库, 而是作为补充, 现在也有部分替代的趋势.
l 关注高性能, 高并发, 灵活性, 忽略和上述无关的功能.
l 现在也在提升安全性和使用功能.
l 典型产品: Redis(持久化缓存, 两个半天),MongoDB(最接近关系型数据 的 NoSQL),Memcached.
l 管理不适用 SQL 管理, 而是用一些特殊的 API 或 数据接口.
1.1.4 NoSQL 的分类, 特点, 典型产品
键值 (KV) 存储: Memcached,Redis
列存储 (column-oriented):HBASE(新浪, 360),Cassandra(200 台服务器集群 )
文档数据库 (document-oriented):MongoDB(最接近关系型数据库的 NoSQL)
图形存储 (Graph):Neo4j
- PostgreSQL
- MongoDB
- Redis
- [root@db02 ~]# cat /etc/RedHat-release
- CentOS release 6.9 (Final)
- [root@db02 ~]# uname -r
- 2.6.32-696.el6.x86_64
- [root@db02 ~]# /etc/init.d/iptables status
- iptables: Firewall is not running.
- [root@db02 ~]# getenforce
- Disabled
- [root@db02 ~]# hostname -I
- 10.0.0.52 172.16.1.52
- useradd -s /sbin/nologin -M MySQL -u1000
- id MySQL
- mkdir -p /server/tools
- cd /server/tools
- wget -q http://mirrors.163.com/mysql/Downloads/MySQL-5.6/mysql-5.6.40.tar.gz
- tar xf MySQL-5.6.40.tar.gz
- cd MySQL-5.6.40
- cmake . -DCMAKE_INSTALL_PREFIX=/application/MySQL-5.6.40 -DMYSQL_DATADIR=/application/MySQL-5.6.40/data -DMYSQL_UNIX_ADDR=/application/MySQL-5.6.40/tmp/MySQL.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ZLIB=bundled -DWITH_SSL=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_EMBEDDED_SERVER=1 -DENABLE_DOWNLOADS=1 -DWITH_DEBUG=0
- make && make install
- ln -s /application/MySQL-5.6.40/ /application/MySQL
- cp support-files/MySQL.server /etc/init.d/mysqld
- chmod 700 /etc/init.d/mysqld
- echo 'PATH=/application/mysql/bin/:$PATH'>>/etc/profile
- tail -1 /etc/profile
- source /etc/profile
- echo $PATH
- MySQL
- mysqladmin -u root password '123456'
- MySQL -uroot -p123456
- select user,host from MySQL.user;
- drop user ''@'db02';
- drop user ''@'localhost';
- drop user 'root'@'db02';
- drop user 'root'@'::1';
- select user,host from MySQL.user;
- drop database test;
- show databases;
- [root@db13 ~]# cat /etc/RedHat-release
- CentOS release 6.9 (Final)
- [root@db13 ~]# uname -r
- 2.6.32-696.el6.x86_64
- [root@db13 ~]# getenforce
- Disabled
- [root@db13 ~]# /etc/init.d/iptables status
- iptables: Firewall is not running.
- [root@db13 ~]# hostname -I
- 10.0.0.153 172.16.1.153
- mkdir -p /server/tools
- cd /server/tools
- wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
- mkdir -p /application/
- mv MySQL-5.7.17-Linux-glibc2.5-x86_64 /application/MySQL-5.7.17
- ln -s /application/MySQL-5.7.17 /application/MySQL
- useradd -M -s /sbin/nologin -u 1000 MySQL
- chown -R MySQL.MySQL /application/MySQL-5.7.17
- \cp /application/MySQL-5.7.17/support-files/my-default.cnf /etc/my.cn
- \cp \application/MySQL-5.7.17/support-files/MySQL.server /etc/init.d/mysqld
- # 修改启动文件内容 (配置目录)
- [root@db13 tools]# /etc/init.d/mysqld start
- Starting MySQL.Logging to '/application/mysql/data/db13.err'.
- SUCCESS!
- [root@db13 tools]# /etc/init.d/mysqld status
- SUCCESS! MySQL running (1509)
- echo 'PATH=/application/mysql/bin/:$PATH'>>/etc/profile
- tail -1 /etc/profile
- source /etc/profile
- echo $PATH
- mysqladmin: [Warning] Using a password on the command line interface can be insecure.
- Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
- [root@db02 ~]# cd /application/MySQL/bin/
- [root@db02 bin]# file mysqld
- mysqld: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped
- [root@db02 bin]# file ../support-files/MySQL.server
- ../support-files/MySQL.server: POSIX shell script text executable
- [root@db02 data]# file /application/MySQL/bin/mysqld_safe
- /application/MySQL/bin/mysqld_safe: POSIX shell script text executable
- ,mysqladmin -uroot -p123 shutdown
- ,servive mysqld stop
- [root@db02 bin]# mysqladmin shutdown -uroot -p123
- Warning: Using a password on the command line interface can be insecure.
- [root@db02 bin]# perror 13
- OS error code 13: Permission denied
- [MySQL]
- [mysqladmin]
- [mysqldump]
- [root@db02 data]# cat /etc/my.cnf
- [mysqld]
- basedir=/application/MySQL
- datadir=/application/MySQL/data
- socket=/tmp/MySQL.sock
- log-error=/var/log/MySQL.log
- port=3307
- [MySQL]
- socket=/tmp/MySQL.sock
- user=root
- password=123
- [root@db02 data]# ps -ef |grep [my]sql
- root 3411 1918 0 15:52 pts/1 00:00:00 /bin/sh /application/MySQL/bin/mysqld_safe
- MySQL 3548 3411 0 15:52 pts/1 00:00:00 /application/MySQL/bin/mysqld --basedir=/application/MySQL --datadir=/application/MySQL/data --plugin-dir=/application/MySQL/lib/plugin --user=MySQL --log-error=/var/log/MySQL.log --pid-file=/application/MySQL/data/db02.pid --socket=/tmp/MySQL.sock --port=3307
- [root@db02 /]# tree /data/
- /data/
- [root@db02 /]# cat data/3306/my.cnf
- [client]
- port = 3306
- socket = /data/3306/MySQL.sock
- [mysqld]
- user = MySQL
- port = 3306
- socket = /data/3306/MySQL.sock
- basedir = /application/MySQL
- datadir = /data/3306/data
- log-bin = /data/3306/MySQL-bin
- server-id = 6
- [mysqld_safe]
- log-error=/data/3306/mysql_3306.err
- pid-file=/data/3306/mysqld.pid
- [root@db02 /]# cat /data/3307/my.cnf
- [client]
- port = 3307
- socket = /data/3307/MySQL.sock
- [mysqld]
- user = MySQL
- port = 3307
- socket = /data/3307/MySQL.sock
- basedir = /application/MySQL
- datadir = /data/3307/data
- log-bin = /data/3307/MySQL-bin
- server-id = 7
- [mysqld_safe]
- log-error=/data/3307/mysql_3307.err
- pid-file=/data/3307/mysqld.pid
- [root@db02 /]# cat data/3306/MySQL
- #!/bin/sh
- #3306 start scripts
- #init
- port=3306
- mysql_user="root"
- CmdPath="/application/mysql/bin"
- mysql_sock="/data/${port}/mysql.sock"
- mysqld_pid_file_path=/data/3306/3306.pid
- start(){
- if [ ! -e "$mysql_sock" ];then
- printf "Starting MySQL...\n"
- /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1> /dev/null &
- sleep 3
- else
- printf "MySQL is running...\n"
- exit 1
- fi
- }
- stop(){
- if [ ! -e "$mysql_sock" ];then
- printf "MySQL is stopped...\n"
- exit 1
- else
- printf "Stoping MySQL...\n"
- mysqld_pid=`cat "$mysqld_pid_file_path"`
- if (kill -0 $mysqld_pid 2>/dev/null)
- then
- kill $mysqld_pid
- sleep 2
- fi
- fi
- }
- restart(){
- printf "Restarting MySQL...\n"
- stop
- sleep 2
- start
- }
- case "$1" in
- start)
- start
- ;;
- stop)
- stop
- ;;
- restart)
- restart
- ;;
- *)
- printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
- esac
- [root@db02 /]# cat data/3307/MySQL
- #!/bin/sh
- #3307 start scripts
- #init
- port=3307
- mysql_user="root"
- CmdPath="/application/mysql/bin"
- mysql_sock="/data/${port}/mysql.sock"
- mysqld_pid_file_path=/data/3307/3307.pid
- start(){
- if [ ! -e "$mysql_sock" ];then
- printf "Starting MySQL...\n"
- /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1> /dev/null &
- sleep 3
- else
- printf "MySQL is running...\n"
- exit 1
- fi
- }
- stop(){
- if [ ! -e "$mysql_sock" ];then
- printf "MySQL is stopped...\n"
- exit 1
- else
- printf "Stoping MySQL...\n"
- mysqld_pid=`cat "$mysqld_pid_file_path"`
- if (kill -0 $mysqld_pid 2>/dev/null)
- then
- kill $mysqld_pid
- sleep 2
- fi
- fi
- }
- restart(){
- printf "Restarting MySQL...\n"
- stop
- sleep 2
- start
- }
- case "$1" in
- start)
- start
- ;;
- stop)
- stop
- ;;
- restart)
- restart
- ;;
- *)
- printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
- esac
- [root@db02 /]# mkdir /data/{
- 3306,3307
- }/data -p
- [root@db02 /]# chown -R MySQL.MySQL /data/
- cd /application/MySQL/scripts && ./mysql_install_db --defaults-file=/data/3306/my.cnf --basedir=/application/MySQL --datadir=/data/3306/data --user=MySQL
- ./mysql_install_db --defaults-file=/data/3307/my.cnf --basedir=/application/MySQL --datadir=/data/3307/data --user=MySQL
- #5.6.40 特殊性: 需要创建错误日志文件
- touch /data/3306/mysql_3306.err
- touch /data/3307/mysql_3307.err
- [root@db02 scripts]# /data/3306/MySQL start
- Starting MySQL...
- [root@db02 scripts]# /data/3307/MySQL start
- Starting MySQL...
- [root@db02 scripts]# ps -ef |grep [my]sql
- root 4341 1 0 16:46 pts/1 00:00:00 /bin/sh /application/MySQL/bin/mysqld_safe --defaults-file=/data/3306/my.cnf --pid-file=/data/3306/3306.pid
- MySQL 4526 4341 13 16:46 pts/1 00:00:03 /application/MySQL/bin/mysqld --defaults-file=/data/3306/my.cnf --basedir=/application/MySQL --datadir=/data/3306/data --plugin-dir=/application/MySQL/lib/plugin --user=MySQL --log-error=/data/3306/mysql_3306.err --pid-file=/data/3306/3306.pid --socket=/data/3306/MySQL.sock --port=3306
- root 4549 1 0 16:46 pts/1 00:00:00 /bin/sh /application/MySQL/bin/mysqld_safe --defaults-file=/data/3307/my.cnf --pid-file=/data/3307/3307.pid
- MySQL 4734 4549 51 16:46 pts/1 00:00:08 /application/MySQL/bin/mysqld --defaults-file=/data/3307/my.cnf --basedir=/application/MySQL --datadir=/data/3307/data --plugin-dir=/application/MySQL/lib/plugin --user=MySQL --log-error=/data/3307/mysql_3307.err --pid-file=/data/3307/3307.pid --socket=/data/3307/MySQL.sock --port=3307
- [root@db02 scripts]# netstat -lntup |grep MySQL
- tcp 0 0 :::3306 :::* LISTEN 4526/mysqld
- tcp 0 0 :::3307 :::* LISTEN 4734/mysqld
- MySQL -S /data/3306/MySQL.sock
- MySQL -S /data/3307/MySQL.sock
- cd /application/MySQL/bin/
- mysqld_safe --skip-grant-table --user=MySQL --skip-networking &
- [root@db02 3306]# MySQL
- MySQL> update MySQL.user set password=password('123') where user='root' and host='localhost';
- MySQL> flush privileges;
来源: http://www.bubuko.com/infodetail-2839911.html