MySQL服务器企业实战一
1.MySQL数据库使用场景介绍
目前Web主流架构是LAMP(Linux+Apache+Mysql+PHP)和LNMP(Linux+Nginx+Mysql+PHP), Mysql更是得到各位IT运维、DBA的青睐。
MySQL常用的两大引擎有MyISAM和InnoDB,那么它们之间的区别是什么，根据不同场合该如何进行选择。
MyISAM类型的数据库表强调的是性能，其执行速度比InnoDB类型更快，但不提供事务支持，不支持外键，如果执行大量的查询操作，MyISAM引擎是更好的选择。
InnoDB提供事务支持事务、外部键、行级锁等高级数据库功能，执行大量的insert或update操作，出于性能方面的考虑，可以使用InnoDB引擎。
2.MySQL数据库安装方式
1)CentOS7.X基于YUM方式安装MySQL的方法，执行命令如下：
#yum install mariadb-server mariadb mariadb-libs -y
2)源码安装MySQL 5.5.6方法
[root@localhost tools]# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.62.tar.gz
[root@localhost tools]# tar -zxvf mysql-5.5.62.tar.gz
[root@localhost tools]# yum install cmake ncurses - devel ncurses -y
[root@localhost tools]# cd mysql-5.5.62
[root@localhost mysql-5.5.62]#
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DMYSQL_DATADIR=/data/mysql \ #数据库存放路径
-DSYSCONFDIR=/etc \ #配置文件路径
-DMYSQL_USER=mysql \ #运行用户
-DMYSQL_TCP_PORT=3306 \
-DWITH_XTRADB_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \ #开启MyISAM引擎支持
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #开启InnoDB引擎支持
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_EXTRA_CHARSETS=1 \
-DEXTRA_CHARSETS=all \ #安装扩展所有字符集
-DDEFAULT_CHARSET=utf8 \ #默认字符集
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_BIG_TABLES=1 \
-DWITH_DEBUG=0
#cmake报以下错误
-- Could NOT find Curses (missing: CURSES_LIBRARY CURSES_INCLUDE_PATH)
CMake Error at cmake/readline.cmake:83 (MESSAGE):
Curses library not found. Please install appropriate package,
remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
cmake/readline.cmake:118 (FIND_CURSES)
cmake/readline.cmake:214 (MYSQL_USE_BUNDLED_READLINE)
CMakeLists.txt:394 (MYSQL_CHECK_READLINE)
[root@localhost mysql-5.5.62]# rm CMakeCache.txt #此步骤非常重要
[root@localhost mysql-5.5.62]# yum install bison ncurses-devel git
#cmake #重新配置环境
[root@localhost mysql-5.5.62]# make && make install
[root@localhost mysql]# pwd
/usr/local/mysql
[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 --level 35 mysqld on
[root@localhost mysql]# mkdir -p /data/mysql
[root@localhost mysql]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql/ --basedir=/usr/local/mysql/ #初始化数据库
Installing MySQL system tables...
[root@localhost mysql]# ln -s /usr/local/mysql/bin/ * /usr/bin/
[root@localhost mysql]# service mysqld restart
ERROR! MySQL server PID file could not be found!
Starting MySQL.Logging to '/data/mysql/localhost.localdomain.err'.
. SUCCESS!
[root@localhost mysql]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.62-log Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
MySQL [(none)]> use mysql;
Database changed
MySQL [mysql]> update user set password=password('****') where user='root'; #设置密码
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MySQL [mysql]> flush privileges;
3.MySQL数据库配置文件详解
[mysqld]
port = 3306
socket = /tmp/mysql.sock #通信设置
skip-external-locking
key_buffer_size = 256M #索引缓冲区的大小
max_allowed_packet = 1M
table_open_cache = 256 #打开表的缓存数量
sort_buffer_size = 1M
read_buffer_size = 1M #读查询操作所能使用的缓冲区大小
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8 #可重用的线程数
query_cache_size= 16M #查询结果缓冲区大小
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8 #最大线程数，服务器逻辑CPU*2
