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