mysql是RDBMS的一种,最早是MYSQL AB公司,后来被SUN收购,再后来被Oracle收购。96年mysql面世,前身是Unireg。
mysql的几个特性:
高性能:mysql支持完全多线程,而且支持查询缓存
稳定性:facebook、google、alibaba等都在使用
伸缩性:mysql具有很强的扩展性和使用弹性
易使用:轻量级的服务器
完全的多用户支持:支持多用户并发访问
国际性:支持多种语言,支持多种编码
支持多种开发语言:C、C++、Python、Java等
开源:mysql到现在为止都是开源
mysql版本:95年就出现了内部版本,直到96年才出现公开版本,在1998年出先了Windows版本。2001年,mysql3.23后开始被广泛接收,在2002年,mysql4.0支持Unions
。
mysql的一些引申服务套件:
mysql server(mysqld,mysql):mysql的服务器端,nysqld是服务端,mysql是客户端
mysql cluster:mysql集群,将多个物理服务器集合起来实现高可用(可以提供四个9的高可用能力)和负载均衡能力,至少需要四个节点,但是使用的很少
mysql proxy:mysql代理服务器,读写分离,本身也提供分析,重写sql语句等(协议分析并重写能力),还可以路由语句。但是也不推荐在生产环境中使用
mysql adminitrator:mysql的管理工具
等等。。。
mysql前途未卜,mariadb可以替代之,percona官网可以提供大量mysql学习工具。
mysql安装
mysql的安装有三种方法:
专用软件包管理器包:即专用的软件包格式包(deb和rpm)
通用二进制格式包:gcc、icc
源代码包:需要自己编译,5.5版本之前使用
make
编译,5.5之后使用cmake
工具编译,源码编译可以DIY定制自己需要的功能
mysql安装之前需要注意的问题:
要选择选择
GA
版本(general availability)一般情况,需要安装
mysql client
、mysql devel
、mysql server
、mysql shared
等
mysql安装完成后目录详解:
bin:二进制执行程序(包括服务端和客户端)
data:默认数据链目录
include:头文件目录
lib:库文件目录
man:帮助手册
mysql-test:测试组件
scripts:mysql初始化脚本
share:对不同语言版本的信息
sql-bench:基准性能测试工具包
support-files:主配置文件样例
mysql如何完成初始化?mysql服务器端在安装完成后必须完成初始化。
源码编译安装mysql
[root@localhost data]# pwd /mysql/data [root@localhost data]# groupadd -r mysql [root@localhost data]# useradd -g mysql -r -d /data/mydata mysql //建立mysql和mysql用户用户组 [root@localhost data]# ls mysql-5.5.60.tar.gz [root@mysql mnt]# tar zxvf mysql-5.5.60.tar.gz //解压mysql源码包 [root@mysql mnt]# ls mysql-5.5.60 mysql-5.5.60.tar.gz [root@localhost data]# cd mysql-5.5.60/ [root@localhost mysql-5.5.60]#yum install -y gcc gcc-c++ make ncurses-devel bison openssl-devel zlib-devel //可能会用到的依赖 [root@localhost mysql-5.5.60]# cmake . -DMYSQL_DATADIR=/data/mydata -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci [root@mysql mysql-5.7.11]#make && make install //等待吧,编译时间比较长,吃内存吃cpu
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql 安装目录
-DMYSQL_DATADIR=/data/mydata 数据库存放数据目录
-DSYSCONFDIR=/etc 配置文件存放目录
-DWITH_INNOBASE_STORAGE_ENGINE=1 安装 innodb 存储引擎
DWITH_ARCHIVE_STORAGE_ENGINE=1 安装 archive 存储引擎
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 安装blackhole 存储引擎(进入这个存储都被扔掉)
-DWITH_READLINE=1 快捷键功能
-DWITH_SSL=system 支持SSL模块
-DWITH_ZLIB=system 支持zlib压缩功能
-DWITH_LIBWRAP=0 不支持LIBWRAP功能
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock mysql sock文件路径
DDEFAULT_CHARSET=utf8 使用utf-8字符集
-DDEFAULT_COLLATION=utf8_general_ci 检验字符
编译时碰到的问题
问题:
CMake Error: your CXX compiler: "CMAKE_CXX_COMPILER-NOTFOUND" was not found. Please set CMAKE_CXX_COMPILER to a valid compiler path or name. 这是编译工具需要C语言支持,解决方法:下载安装gcc-c++ [root@mysql mysql-5.7.11]# yum install gcc-c++
注意
重新编译时,需要清除旧的对象文件和缓存信息 make clean rm -f CmakeCache.txt
mysql初始化
安装完成后进入/usr/local/mysql
目录下查看:
[root@localhost mysql]# pwd /usr/local/mysql [root@localhost mysql]# ls bin data include lib mysql-test scripts sql-bench COPYING docs INSTALL-BINARY man README share support-files //和使用rpm包安装一样
初始化步骤:
将属组改称
mysql
[root@localhost mysql]# chown -R :mysql /usr/local/mysql/ [root@localhost mysql]# ll total 56 drwxr-xr-x 2 root mysql 4096 May 7 22:23 bin -rw-r--r-- 1 root mysql 17987 Feb 26 21:02 COPYING drwxr-xr-x 3 root mysql 17 May 7 22:23 data drwxr-xr-x 2 root mysql 52 May 7 22:23 docs drwxr-xr-x 3 root mysql 4096 May 7 22:23 include -rw-r--r-- 1 root mysql 301 Feb 26 21:02 INSTALL-BINARY drwxr-xr-x 3 root mysql 4096 May 7 22:23 lib drwxr-xr-x 4 root mysql 28 May 7 22:23 man drwxr-xr-x 10 root mysql 4096 May 7 22:23 mysql-test -rw-r--r-- 1 root mysql 2496 Feb 26 21:02 README drwxr-xr-x 2 root mysql 29 May 7 22:23 scripts drwxr-xr-x 27 root mysql 4096 May 7 22:23 share drwxr-xr-x 4 root mysql 4096 May 7 22:23 sql-bench drwxr-xr-x 2 root mysql 4096 May 7 22:23 support-files
初始化所有用户和数据存放目录
te] Ignoring --secure-file-priv value as server is running with --bootstrap. 180507 22:31:00 [Note] ./bin/mysqld (mysqld 5.5.60) starting as process 8445 ... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h localhost.localdomain password 'new-password' Alternatively you can run: ./bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd ./mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/
复制样例配置文件
[root@localhost mysql]# cp support-files/my-large.cnf /etc/my.cnf [root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld [root@localhost mysql]# chkconfig --add mysqld [root@localhost mysql]# chkconfig --list mysqld Note: This output shows SysV services only and does not include native systemd services. SysV configuration data might be overridden by native systemd configuration. If you want to list systemd services use 'systemctl list-unit-files'. To see services enabled on particular target use 'systemctl list-dependencies [target]'. mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
为了直接使用客户端命令,将
/usr/local/mysql/bin
添加到环境变量中
[root@localhost mysql]# echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile.d/mysql.sh [root@localhost mysql]# source /etc/profile.d/mysql.sh
进入
mysql
[root@localhost mysql]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.60-log Source distribution //注意这里提示是源码安装版本 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
到这里就编译安装大功告成,为了保证数据库存储数据在/mysql
我们还要修改主配置文件
将“datadir = /mysql”,添加到/etc/my.cnf中
一些别的概念
什么是字符集?计算机存储的都是01
代码,要标识一个别的字符使用01
的不同排序表示,ASCI码就是字符集,UTF-8也是字符集。*
什么是排序规则?同一种字符集,里面的排列顺序也不同,按照不同规则进行排序就是排序规则。(比如汉字按笔画或者按拼音)
mysql通信方式
注意:mysql是一个标准的C/S架构服务,是客户端mysql
去连接服务端mysqld
,通信方式:
如果在同一台主机上:对于linux主机,通过mysql.sock进行进程间通信;对于windows主机,使用memory(pipe)完成进程间通信。
如果不在同一台主机上,则基于TCP/IP协议完成通信
mysql的客户端工具:mysql、mysqldump、mysqladmin、mysqlcheck、mysqlimport
mysql工具支持的选项:
-u USERNAME 用户
-h host 主机(ip地址)
-p passwd 密码
--protocol {tcp/socket/pipe/memory}指定协议,后两种在Windows上使用
--port PORT 指定端口
mysql的非客户端工具:myisamchk(修复iasm表)、myisampack(压缩iasm表)
mysql用户管理
安装成功,感受以下mysql
最简单的一些命令:
mysql> show databases; //查看数据库 +--------------------+ | Database | +--------------------+ | information_schema | | data | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> show engines; //查看存储引擎,默认引擎是InnoDB +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 8 rows in set (0.00 sec)
mysql源码编译完成会默认有四个用户:两个root用户,两个匿名用户,匿名用户会是安全因患,最好删除之!
mysql> DROP USER ''@localhost; Query OK, 0 rows affected (0.00 sec) mysql> DROP USER ''@'127.0.0.1'; //提示没有这个用户,就不用管了 ERROR 1396 (HY000): Operation DROP USER failed for ''@'127.0.0.1'
为root
修改密码
mysql> use mysql; Database changed mysql> SELECT User,Host,Password FROM user; +------+-----------------------+----------+ | User | Host | Password | +------+-----------------------+----------+ | root | localhost | | | root | localhost.localdomain | | | root | 127.0.0.1 | | | root | ::1 | | //ipv6的root用户 | | localhost.localdomain | | +------+-----------------------+----------+ 5 rows in set (0.00 sec) mysql> DROP USER ''@'localhost.localdomain'; //发现由各匿名用户,删除之 Query OK, 0 rows affected (0.00 sec) mysql> DROP USER 'root'@'::1'; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE user SET Password=PASSWORD("fsx123") WHERE user='root'; //剩余的三个root用户修改密码 Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT User,Host,Password FROM user; +------+-----------------------+-------------------------------------------+ | User | Host | Password | +------+-----------------------+-------------------------------------------+ | root | localhost | *110E370AA9F82D045833B6138B49FF1C37ACDCE6 | | root | localhost.localdomain | *110E370AA9F82D045833B6138B49FF1C37ACDCE6 | | root | 127.0.0.1 | *110E370AA9F82D045833B6138B49FF1C37ACDCE6 | +------+-----------------------+-------------------------------------------+ 3 rows in set (0.00 sec)
基于TCP/IP协议连接方式:
[root@localhost mysql]# mysql -uroot -h172.25.254.111 -p Enter password: ERROR 1130 (HY000): Host 'ooo.ooo' is not allowed to connect to this MySQL server //发现不能连接,这是因为三个root用户只允许本机连接(这里说的本机是localhost、localhost.localdomain、127.0.0.1)
设置密码后发现不能登陆Mysql了,似乎是密码复杂度不够?然后进入安全模式去调式修改密码:
[root@localhost mysql]# service mysqld stop Shutting down MySQL. SUCCESS! [root@localhost mysql]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking 180507 23:12:46 mysqld_safe Logging to '/mysql/localhost.localdomain.err'. 180507 23:12:46 mysqld_safe Starting mysqld daemon with databases from /mysql //重新打开一个终端,进入mysql更改密码 mysql> update mysql.user set password=PASSWORD('fsx123.456') where user='root'; OK
mysql用户密码修改的三种方式:
mysqladmin -u root password 'new_passwd' -p
->SET PASSWORD FOR username@hostname=PASSWORD('NEW_PASSWORD')
->UPDATE mysql.user SET PASSWORD=PASSWORD('NEW_PASS') WHERE CONDITION;
->FULSH
mysql删除用户方式:
mysql>DROP USER ‘ ’@localhost; //删除匿名用户