第十六节、MySQL数据库服务
2017-12-09 本文已影响0人
妄语莫言
一、Mysql数据库入门及简介
- MySQL是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 旗下公司。MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
- MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL 所使用的 SQL 语言是用于访问数据库的最常用标准化语言。
- MySQL 软件采用了双授权政策，它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。由于其社区版的性能卓越，搭配 PHP 和 Apache 可组成良好的开发环境。
- （RDBMS即关系数据库管理系统(Relational Database Management System)，是将数据组织为相关的行和列的系统，而管理关系数据库的计算机软件就是关系数据库管理系统，常用的数据库软件有Oracle、SQL Server等。）
- RDBMS的特点：
1)数据以表格的形式出现；
2)每行为各种记录名称；
3)每列为记录名称所对应的数据域；
4)许多的行和列组成一张表单；
5)若干的表单组成database；
对应目前主流的LAMP(Linux+Apache+Mysql+PHP)架构来说,Mysql更是得到各位IT运维、DBA的青睐，目前mysql已被orcacle收购，不过好消息是原来mysql创始人已独立出来自己重新开发了一个MariaDB，而且使用的人数越来越多。而且MariaDB兼容mysql所有的功能和相关参数。
二、Mysql数据库引擎详解
MySQL是我们比较常用的一种数据库软件。它有着诸多的优点，如开源的，免费的等等。其实它还有一个很好的特点，那就是有多种引擎可以供你选择。如果赛车手能根据不同的路况，地形随手更换与之最适宜的引擎，那么他们将创造奇迹。
- MyISAMMySQL 5.0 之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务；
-
InnoDB事务型数据库的首选引擎，支持ACID事务，支持行级锁定, MySQL 5.5 起成为默认数据库引擎；
-BDB源自 Berkeley DB，事务型数据库的另一种选择，支持Commit 和Rollback 等其他事务特性； - Memory所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 MySQL 重新启动时丢失；
- Mysql常用的两大引擎有MyISAM和innoDB，那他们有什么明显的区别呢，什么场合使用什么引擎呢？
- MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但不提供事务支持，如果执行大量的SELECT(查询)操作，MyISAM是更好的选择，支持表锁。
- InnoDB提供事务支持事务，外部键等高级 数据库功能，执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表，支持行锁。
三、服务安装
- YUM安装
yum install mysql-server mysql-devel mysql
配置文件/etc/my.cnf
服务文件/etc/init.d/mysqld
命令文件/usr/bin/mysql
-源码安装
[root@mini src]# tar zxvf mysql-5.1.63.tar.gz
[root@mini src]# cd mysql-5.1.63
[root@mini mysql-5.1.63]# ls
aclocal.m4 config.guess Docs libmysqld mysql-test server-tools tests
BUILD config.sub extra libmysql_r mysys sql unittest
ChangeLog configure include ltmain.sh netware sql-bench vio
client configure.in install-sh Makefile.am plugin sql-common win
CMakeLists.txt COPYING INSTALL-SOURCE Makefile.in README storage ylwrap
cmd-line-utils dbug INSTALL-WIN-SOURCE man regex strings zlib
config depcomp libmysql missing scripts support-files
[root@mini mysql-5.1.63]# ./configure --prefix=/usr/local/mysql --enable-assembler
#预编译指定安装目录,使用汇编模式提高性能
[root@mini mysql-5.1.63]# make #编译
[root@mini mysql-5.1.63]# make install #安装
四、mysql 配置文件说明
[root@mini ~]# cat /etc/my.cnf
[mysqld] #mysql服务相关的配置
datadir=/var/lib/mysql #数据目录,企业一般用单独目录存放如/data/mysql
socket=/var/lib/mysql/mysql.sock #socket通信设置
user=mysql #使用mysql用户启动数据库
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0 #是否支持快捷方式,0禁止,1支持
log-bin=mysql-bin #开启bin-log日志,二进制文件记录数据库的操作,用于数据库的还原,起备份作用
server-id=1 #mysql服务ID
character-set-server=utf8 #设置服务端字符集utf8
[mysqld_safe] #安全启动设置
log-error=/var/log/mysqld.log #错误日志保存目录
pid-file=/var/run/mysqld/mysqld.pid #设置pid启动文件
[client] #对其他客户端设置
default-character-set=utf8 #设置客户端字符集
[mysql] #对本机设置
default-character-set=utf8 #设置本地客户端字符集
#######五、mysql数据库忘记密码重置办法
- 首先通过命令停止mysqld服务
- 然后以跳过权限方式后台启动(这里以yum安装为例)：
/usr/bin/mysqld_safe --user=mysql --skip-grant-tables & - 直接通过命令
mysql登录数据库后通过update修改user表对应用户的密码,update user set password=password('123456') where user='root and host='127.0.0.1'; - 修改完密码后以正常方式重启
mysqld服务
五、修改mysql的工作引擎
由于版本原因5.1使用的是默认myISAM，而且编译安装里面也没有innodb引擎
mysql> show engines;
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
4 rows in set (0.00 sec)
#安装innodb
mysql> install plugin innodb soname 'ha_innodb.so';
Query OK, 0 rows affected (1.65 sec)
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)
修改my.cnf配置文件
[mysqld]
skip-external-locking #原句是skip-locking,有报错需要修改
default-storage-engine = INNODB #新增定义默认引擎
重启mysqld服务/etc/init.d/mysqld restart
备注:源码安装错误日志
以实验机器为例错误日志路径/usr/local/mysal/var/mini.err
发生错误时查看该日志可以帮助排错
如果是yum安装机器日志一般在/var/log/mysqld.log
六、慢查询
慢查询对于跟踪有问题的查询很有用，可以分析出当前程序里那些Sql语句比较耗费资源。
- 查看当前mysql慢查询
mysql> show variables like "%slow%";
+---------------------+------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------+
| log_slow_queries | OFF |
| slow_launch_time | 2 | #超过2秒定义为慢查询
| slow_query_log | OFF | #慢查询状态,关闭
| slow_query_log_file | /usr/local/mysql/var/mini-slow.log | # 慢查询日志文件
+---------------------+------------------------------------+
4 rows in set (0.01 sec)
- 在mysql数据库里临时开启慢查询
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.34 sec)
mysql> show variables like "%slow%";
+---------------------+------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/var/mini-slow.log |
+---------------------+------------------------------------+
4 rows in set (0.00 sec)
- 修改配置文件永久开启慢查询
log-slow-queries = /data/mysql/var/db-Test2-slow.log #日志目录。
long_query_time = 0.1 #记录下查询时间查过1秒。
log-queries-not-using-indexes #表示记录下没有使用索引的查询。
附件：实际生产mysql数据库配置文件my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
user = mysql
server_id = 10
port = 3306
socket = /tmp/mysql.sock
datadir = /data/mysql/data1
old_passwords = 1
lower_case_table_names = 1
character-set-server = utf8
default-storage-engine = MYISAM
log-bin = bin.log
log-error = error.log
pid-file = mysql.pid
long_query_time = 2
slow_query_log
slow_query_log_file = slow.log
binlog_cache_size = 4M
binlog_format = mixed
max_binlog_cache_size = 16M
max_binlog_size = 1G
expire_logs_days = 30
ft_min_word_len = 4
back_log = 512
max_allowed_packet = 64M
max_connections = 4096
max_connect_errors = 100
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
sort_buffer_size = 2M
query_cache_size = 64M
table_open_cache = 10000
thread_cache_size = 256
max_heap_table_size = 64M
tmp_table_size = 64M
thread_stack = 192K
thread_concurrency = 24
local-infile = 0
skip-show-database
skip-name-resolve
skip-external-locking
connect_timeout = 600
interactive_timeout = 600
wait_timeout = 600
#*** MyISAM
key_buffer_size = 512M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 1
concurrent_insert = 2
myisam_recover
#*** INNODB
innodb_buffer_pool_size = 16G
innodb_additional_mem_pool_size = 32M
innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency = 16
innodb_open_files = 10000
#innodb_force_recovery = 4
#*** Replication Slave
read-only
#skip-slave-start
relay-log = relay.log
log-slave-updates
