MySQL服務(wù)器企業(yè)實(shí)戰(zhàn)一

1.MySQL數(shù)據(jù)庫使用場景介紹

目前Web主流架構(gòu)是LAMP(Linux+Apache+Mysql+PHP)和LNMP(Linux+Nginx+Mysql+PHP), Mysql更是得到各位IT運(yùn)維、DBA的青睞。

MySQL常用的兩大引擎有MyISAM和InnoDB,那么它們之間的區(qū)別是什么，根據(jù)不同場合該如何進(jìn)行選擇。

MyISAM類型的數(shù)據(jù)庫表強(qiáng)調(diào)的是性能，其執(zhí)行速度比InnoDB類型更快，但不提供事務(wù)支持，不支持外鍵，如果執(zhí)行大量的查詢操作，MyISAM引擎是更好的選擇。

InnoDB提供事務(wù)支持事務(wù)、外部鍵、行級鎖等高級數(shù)據(jù)庫功能，執(zhí)行大量的insert或update操作，出于性能方面的考慮，可以使用InnoDB引擎。

2.MySQL數(shù)據(jù)庫安裝方式

1)CentOS7.X基于YUM方式安裝MySQL的方法，執(zhí)行命令如下：

#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 \? ? #數(shù)據(jù)庫存放路徑

-DSYSCONFDIR=/etc \? ? ? ? #配置文件路徑

-DMYSQL_USER=mysql \? ? ? ??#運(yùn)行用戶

-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 \? ? ? ? ? ?#安裝擴(kuò)展所有字符集

-DDEFAULT_CHARSET=utf8 \? ? ? ?#默認(rèn)字符集

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_BIG_TABLES=1 \

-DWITH_DEBUG=0

#cmake報(bào)以下錯(cuò)誤

-- 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? ? ? ? ? ? ?? #重新配置環(huán)境

[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/? ? ?#初始化數(shù)據(jù)庫

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';? ? #設(shè)置密碼

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4? Changed: 4? Warnings: 0

MySQL [mysql]> flush privileges;

3.MySQL數(shù)據(jù)庫配置文件詳解

[mysqld]

port? ? ? ? ? ? = 3306

socket? ? ? ? ? = /tmp/mysql.sock? ?#通信設(shè)置

skip-external-locking

key_buffer_size = 256M? ? ? ?#索引緩沖區(qū)的大小

max_allowed_packet = 1M

table_open_cache = 256?? ? #打開表的緩存數(shù)量

sort_buffer_size = 1M? ?

read_buffer_size = 1M? ? ? ??#讀查詢操作所能使用的緩沖區(qū)大小

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8? ? ?#可重用的線程數(shù)

query_cache_size= 16M? ??#查詢結(jié)果緩沖區(qū)大小

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8? ? ?#最大線程數(shù)，服務(wù)器邏輯CPU*2