<服务>数据库和MySQL服务多实例配置

时间：Dec. 25, 2016 分类：Linux服务

目录：

什么是数据库

数据库是按照一定数据结构进行组织和存储，并提供多种方法进行管理。

数据库种类

早期的数据库理论来讲，比较流行的数据库模型有三种，层次式数据库，网状型数据库和关系型数据库。

常用的数据库为关系型数据库和非关系型数据库。

虽然网状型数据库和层次式数据库已经很好的解决了数据集中存储和共享的问题，但是数据独立性和抽象级别上仍有很大的欠缺，在使用的过程中仍要明确数据的存储结构指出存储路径，关系型数据库则可以很好的解决这些问题。

关系型数据库是把复杂的数据结构归结为简单的二元关系（即二维表格），对数据库的操作都建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取等运算来实现数据库的管理。

关系型数据库的特点

  1. 关系型以二维表进行表示
  2. 以结构化查询语句，SQL语句

代表产品为Oracle和MySQL

什么是非关系型数据库

非关系型数据库，是在web2.0时代诞生的产物，为提高更好的查询能力

NoSQL的分类

  1. key，value的形式通过Hash表存储，代表为memcached（支持内存）和Redis（持久化，即支持内存又支持磁盘）
  2. 基于列式存储，代表为HBase和Cassandra
  3. 面向文档的数据库，数据以文档（XML，JSON等）的形式存储，代表为MongoDB和CouchDB
  4. 图形数据库，Neo4J和InfoCrid

MySQL服务

MySQL数据通过不同库，库下不同的表进行数据的存储，查询语句为结构化查询语句，SQL语句。

特点

  1. 没有版权制约，源码开放，成本低
  2. 性能和服务稳定，很少异常宕机
  3. 历史悠久，社区和用户活跃
  4. 软件体积小，安装简单，易于维护
  5. 符合大多数架构
  6. 支持多种操作系统，提供多种API接口，支持多种开发语言

MySQL官网链接

版本说明

Alpha进行开发内部测试，一般不对外公开的版本 Beta经过完整的功能开发和内部测试的版本 RC生产环境发布前根据Beta版测试结果进行完善的一个小版本， GA软件产品的正式发布版本，即生产环境版本

MySQL的产品线

  1. MySQL原产品的延续，产品持续完善与改进其用户体验和性能，并增加新的功能，版本从5.0开始。
  2. 整合MySQL AB公司社区和第三方公司开发的新的存储引擎，以及吸收新的实现算法等，进行了大量的代码重构提高性能。版本为5.4版本开始到5.6版本
  3. 推广MySQL Cluster版本，可以提高MySQL Cluster的性能和稳定性，版本是从6开始。

MySQL版本选择

  1. 稳定版
  2. 5.1或者5.5的主流版本

MySQL的安装方式

yum/rpm安装

一般是由四个包组成，MySQL-client，MySQL-devel，MySQL-share和MySQL-server组成。

rpm安装可以通过放到同一目录下，rpm -ivh MySQL-*即可，yum安装直接执行yum install mysql-server即可。

这样做一般适合对数据库要求不太高的场合，例如并发不太大，公司内部或企业内部的一些场合，像本博客就是通过yum安装mysql作为数据的存储。不过一般大公司也会根据业务需要把源码包制作成rpm包然后进行rpm安装或yum安装。

编译安装

安装cmake

[root@why ~]# tar xf cmake-2.8.8.tar.gz 
[root@why ~]# cd cmake-2.8.8
[root@why cmake-2.8.8]# ./configure 
[root@why cmake-2.8.8]# gmake
[root@why cmake-2.8.8]# gmake install
[root@why cmake-2.8.8]# which cmake
/usr/local/bin/cmake

安装依赖包

yum install -y ncurses-devel

编译安装MySQL

编译参数详解

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \指定编译目录
-DMYSQL_DATADIR=/application/mysql-5.5.32/data \指定数据目录
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \指定socket文件目录
-DDEFAULT_CHARSET=utf8 \默认字符集，
-DDEFAULT_COLLATION=utf8_general_ci \默认的校对规则
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \支持的额外字符集
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \支持的各种引擎
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0

编译命令

[root@why ~]# rm -rf mysql-5.5.32
[root@why ~]# tar xf mysql-5.5.32.tar.gz 
[root@why ~]# cd mysql-5.5.32
[root@why mysql-5.5.32]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.32 -DMYSQL_DATADIR=/usr/local/mysql-5.5.32/data -DMYSQL_UNIX_ADDR=/usr/local/mysql-5.5.32/tmp/mysql.sock -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii -DENABLED_LOCAL_INFILE=ON -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITHOUT_PARTITION_STORAGE_ENGINE=1 -DWITH_FAST_MUTEXES=1 -DWITH_ZLIB=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1 -DWITH_EMBEDDED_SERVER=1
[root@why mysql-5.5.32]# make
[root@why mysql-5.5.32]# make install
[root@why mysql-5.5.32]# ln -s /usr/local/mysql-5.5.32/   /usr/local/mysql
[root@why mysql-5.5.32]# cp support-files/my-small.cnf /etc/my.cnf 
cp：是否覆盖"/etc/my.cnf"？ y
[root@why mysql-5.5.32]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
[root@why mysql-5.5.32]# . !$
. /etc/profile
[root@why mysql-5.5.32]# echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile
[root@why mysql-5.5.32]# . !$
. /etc/profile
[root@why mysql-5.5.32]# tail -l !$
tail -l /etc/profile
            . "$i"
        else
            . "$i" >/dev/null 2>&1
        fi
    fi
done

unset i
unset -f pathmunge
export PATH=/usr/local/mysql/bin:$PATH
一般我们会把这个路径放在PATH前，是因为如果PATH在前，主机中yum安装了MySQL的话，就会导致了启动MySQL系统会选取排在前面的MySQL，即在$PATH里的/usr/bin中mysql命令启动yum安装的MySQL。
[root@why mysql-5.5.32]# useradd mysql -s /sbin/nologin -M
[root@why mysql-5.5.32]# chown -R mysql.mysql /usr/local/mysql/data/
[root@why scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/ --user=mysql
WARNING: The host 'why' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
OK
Filling help tables...
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:

/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h why password 'new-password'

Alternatively you can run:
/usr/local/mysql/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 /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/local/mysql/scripts/mysqlbug script!
[root@why scripts]# cp /root/mysql-5.5.32/support-files/mysql.server /etc/init.d/mysqld
[root@why scripts]# chmod +x !$
chmod +x /etc/init.d/mysqld
[root@why scripts]# /etc/init.d/mysqld start
Starting MySQL...                                          [确定]
[root@why scripts]# netstat -nlput| grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      31184/mysqld        
[root@why scripts]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32 Source distribution

Copyright (c) 2000, 2013, 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服务编译就完成了。

MySQL多实例

MySQL多实例概念

MySQL多实例是指在一台机器上开启多个不同的服务端口运行多个MySQL服务，这些进程通过不用的socket监听不同的服务端口来提供服务。这些多实例共用一套MySQL安装程序，使用不同（也可以相同）的my.cnf配置文件、启动程序和数据文件，多实例逻辑上相互独立，自身通过配置文件对应的设定值来取得服务器的相关硬件资源。

多实例作用和问题

  1. 多实例可以有效的利用服务器资源
  2. 当公司资金紧张但是数据库又需要各自尽量独立提供服务，如果需要主从同步的时候多实例是非常好用的

  3. 多实例会出现资源互相抢占的情况

    多实例应用场景

  4. 公司资金紧张，业务访问量不是过大

  5. 并发访问不是特别大的业务
  6. 门户网站通过高硬件配置实现，做主从配置

多配置文件多实例部署方案

通过多个配置文件，多个启动程序和多个数据文件来实现多实例。本博客中也主要介绍这种方式。

依赖包

yum install -y ncurses-devel yum install -y libaio-devel

部署多实例

流程和前边编译安装一样，只要做到make install即可。 现在博主通过之前的继续实现多实例。

[root@why scripts]# /etc/init.d/mysqld stop
Shutting down MySQL.                                       [确定]
[root@why scripts]# rm -f /etc/init.d/mysqld
[root@why scripts]# mkdir -p /data/330{6,7}/data
[root@why scripts]# tree /data/
/data/
├── 3306
│   └── data
└── 3307
    └── data

4 directories, 0 files
[root@why scripts]# chown -R mysql.mysql /data

配置文件

修改配置文件/etc/my.cnf放入到/data/3306和/data/3307下

[client]
port            = 3306
socket          = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user    = mysql
port    = 3306
socket  = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
open_files_limit    = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192K
#transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
#log_long_format
#log-error = /data/3306/error.log
#log-slow-queries = /data/3306/slow.log
pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover

lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql

server-id = 1

innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M

[mysqld_safe]
log-error=/data/3306/mysql_3306.err
pid-file=/data/3306/mysqld.pid


[root@why scripts]# tree /data/
/data/
├── 3306
│   ├── data
│   └── my.cnf
└── 3307
    ├── data
    └── my.cnf

4 directories, 2 files

初始化数据库

[root@why scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3306/data --user=mysql
[root@why scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3307/data --user=mysql

多实例启动mysql

mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null &
mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 > /dev/null &

多实例停止mysql

mysqladmin -u root -p 123456 -S /data/3306/mysql.sock shutdown
mysqladmin -u root -p 123456 -S /data/3307/mysql.sock shutdown

多实例登录mysql

mysql -S /data/3306/mysql.sock
mysql -S /data/3307/mysql.sock

安全配置

mysqladmin -u root -S /data/3306/mysql.sock password '123456'
mysqladmin -u root -S /data/3307/mysql.sock password '123456'

多实例数据库排查故障

通过配置文件指定的error.log

查看多个

[root@why 3307]# ss -nlput | grep 330
tcp    LISTEN     0      128                    *:3307                  *:*      users:(("mysqld",5503,11))
tcp    LISTEN     0      128                    *:3306                  *:*      users:(("mysqld",3296,12))

单配置文件多实例部署方案

通过一个配置文件，一个启动程序和多个数据文件来实现多实例。这样虽然是官方推荐，但是耦合性太高，本博客就不过多介绍了。

配置文件 my.cnf

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqldadmin = /usr/bin/mysqladmin
user = mysql
[mysqld1]
socket = /var/lib/mysql/mysql.sock
port =3306
pid-file = /var/lib/mysql/mysql.pid
datadir = /var/lib/mysql/
user = mysql
[mysqld2]
socket = /var/lib/db2/mysql.sock
port =3307
pid-file = /var/lib/db2/mysql.pid
datadir = /var/lib/db2/
user = mysql
skip-name-resolve
server-id = 10
default-storage-engine = innodb
innodb_buffer_pool_size = 512M
innodb_addition_mem_pool = 10M
default_character_set = utf8
#read-only
relay-log-space-limit = 3G
expire_logs_day = 20
...
...
以此类推

启动方式

mysqld_multi --config-file=/usr/local/mysql/my_multi.cnf start 1,2

关闭方式

mysqld_multi --config-file=/usr/local/mysql/my_multi.cnf stop 2