Linux运维学习笔记之十九：数据库服务之MySQL基础

第四部分数据库服务

第三十章   MySQL基础

一、MySQL单实例源码cmake方式编译安装

1、安装cmake软件

（1）解压cmake

tar zxf cmake-2.8.8.tar.gz

cd cmake-2.8.8

（2）configure

./configure

（3）gmake

gmake

（4）gmake install

gmake install

cd ..

2、确认依赖包是否安装

rpm -qa | grep ncurses

ncurses-5.7-4.20090207.el6.x86_64

ncurses-base-5.7-4.20090207.el6.x86_64

ncurses-term-5.7-4.20090207.el6.x86_64

ncurses-libs-5.7-4.20090207.el6.x86_64

ncurses-devel-5.7-4.20090207.el6.x86_64

如没有安装，则安装之

yum install ncurses-devel -y

3、安装MySQL

（1）解压MySQL

tar xf mysql-5.5.32.tar.gz

cd mysql-5.5.32

（2）创建mysql用户和组

useradd mysql -s /sbin/nologin -M

id mysql

（3）configure

a、不指定字符集，使用默认拉丁字符集

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 \

-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

b、指定字符集为utf8

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

（4）make && make install

make && make install

（5）创建MySQL软连接

ln -s /application/mysql-5.5.32/ /application/mysql

（6）配置环境变量

echo 'export PATH=/application/mysql/bin/:$PATH' >>/etc/profile

（7）检查环境变量

a、查看profile

tail -1 /etc/profile                                           

export PATH=/application/mysql/bin/:$PATH

b、让 配置生效

source /etc/profile

c、查看PATH

echo $PATH

/application/mysql/bin/:/application/nginx/sbin:/application/nginx/sbin:/usr/lib64/qt-3.3/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

（8）检查数据目录是否创建

ll -d /application/mysql/data/

drwxr-xr-x 3 root root 4096 Mar 26 22:10 /application/mysql/data/

（9）授权mysql用户访问数据目录

chown -R mysql.mysql /application/mysql/data/

（10）调整/tmp目录权限（否则初始化会报错）

chmod 1777 /tmp/

（11）创建MySQL配置文件:my.conf

cp mysql-5.5.32/support-files/my-small.cnf /etc/my.cnf

（12）初始化MySQL

cd /application/mysql/scripts/

./mysql_install_db --user=mysql --basedir=/application/mysql/--datadir=/application/mysql/data/

（13）创建MySQL启动脚本

cp/application/mysql/support-files/mysql.server /etc/init.d/mysqld

（14）增加MySQL启动脚本的可执行权限

chmod +x /etc/init.d/mysqld

（15）启动MySQL

/etc/init.d/mysqld start

（16）为root用户指定密码

/application/mysql/bin/mysqladmin -u rootpassword '123456'

（17）登陆MySQL

mysql -uroot -p

（18）删除无用的数据库和用户

delete from mysql.user where host='::1'or user='';

drop database test;

（19）额外添加管理员

grant all privileges on *.* tosystem@'localhost' identified by '123456' with grant option

（20）配置MySQL启动级别

chkconfig mysqld on

chkconfig --list mysqld

mysqld          0:off   1:off  2:on    3:on    4:on   5:on    6:off

二、MySQL多实例概念

1、什么是多实例

就是一台服务器上开启多个不同的服务端口，来运行多个MySQL服务里程，通过不用同socket 监听不同的服务端口提供各自的服务。

这些MySQL多实例，共用一套MySQL安装程序，使用不同（也可相同）的my.conf配置文件、启动程序、数据文件。在提供服务时，多实例的MySQL在逻辑上看起来是各自独立的，多个实例根据配置文件对应的设定值，获取相应的服务器资源。

2、多实例的作用、问题和应用场景

（1）作用

a、有效利用服务器资源

b、节约服务器资源

c、节省IDC机柜

（2）问题

a、资源互相抢占

b、当某个服务实例并发很高或有慢查询时导致其它实例性能下降

（3）应用场景

a、资金紧张的公司

b、并发访问不是特别大的业务

c、门户网站应用较多

百度搜索引擎的数据库是多实例，一般是从库（48核，96G，3-4个实例）

sina网也是多实例（内存48G）

3、MySQL多实常见配置方案

（1）多配置文件及多个启动程序（推荐方法）

tree data 

 /data

   ├── 3306

   │   ├── data     #3306实例的数据文件

   │   ├── my.cnf   #3306实例的配置文件

   │   └── mysql    #3306实例的启动文件

   └── 3307

       ├── data

       ├── my.cnf

       └── mysql

（2）单一配置文件部署方案

a、my.cnf配置文件样例（mysql手册中提到的方法）

[mysqld_multi]

mysqld      = /usr/bin/mysqld_safe

mysqladmin = /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     =/mnt/data/db2/mysql.sock

port       =3303

pid-file   = /mnt/data/db2/mysql.pid

datadir    =/mnt/data/db2/

user       =mysql

skip-name-resolve

server-id  = 11

master-connect-retry = 60

default-storage-engine = innodb

innodb_buffer_pool_size = 512M

innodb_additional_mem_pool = 10M

default_character_set = utf8

character_set_server = utf8

#read-only

relay-log-space-limit =3G

expire_logs_day =20

[mysqld3]

socket     =/mnt/data/db1/mysql.sock

port       =3302

pid-file   = /mnt/data/db1/mysql.pid

datadir    =/mnt/data/db1/

user       =mysql

skip-name-resolve

server-id  = 10

default-storage-engine = innodb

innodb_buffer_pool_size = 512M

innodb_additional_mem_pool = 10M

default_character_set = utf8

character_set_server = utf8

#read-only

relay-log-space-limit =3G

expire_logs_day =20

b、启动方法

mysqld_multi--config-file=/data/mysql/my_multi.cnf start 1,2,3

c、停止方法

mysqld_multi stop 1,3

d、存在的问题

耦合性太强

三、MySQL多实例安装：多配置文件、启动程序方法

1、安装依赖包

yum install ncurses-devel -y

yum install libaio-devel -y

2、cmake方式编译安装MySQL单实例源码

整个安装过程只到make install和建立软链接就停止，后面的步骤不用操作，多实例方式需重新配置。

3、创建MySQL多实例的数据文件目录

（1）关闭单实例MySQL的所有进程

pkill mysqld

ps -ef | grep mysql

（2）删除MySQL启动命令

rm -f /etc/init.d/mysqld

（3）创建多实例目录

mkdir -p /data/{3306,3307}/data

4、创建多实例配置文件

（1）配置3306的配置文件

vi /data/3306/my.cnf

[client]

port            = 3306

socket          =/data/3306/mysql.sock

 

[mysql]

no-auto-rehash

 

[mysqld]

user    = mysql

port    = 3306

socket  =/data/3306/mysql.sock

basedir = /application/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_oldboy3306.err

pid-file=/data/3306/mysqld.pid

（2）配置3307的配置文件

vi /data/3307/my.cnf

[client]

port            = 3307

socket          =/data/3307/mysql.sock

 

[mysql]

no-auto-rehash

 

[mysqld]

user    = mysql

port    = 3307

socket  =/data/3307/mysql.sock

basedir = /application/mysql

datadir = /data/3307/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/3307/error.log

#log-slow-queries = /data/3307/slow.log

pid-file = /data/3307/mysql.pid

#log-bin = /data/3307/mysql-bin

relay-log = /data/3307/relay-bin

relay-log-info-file = /data/3307/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 = 3

 

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/3307/mysql_oldboy3307.err

pid-file=/data/3307/mysqld.pid

5、创建多实例启动文件

（1）多实例启动/停止MySQL服务的实质

当启动MySQL时，如果配置文件不在/etc下的话，在启动时，必须通过--defaults-file参数来指定MySQL的配置文件my.cnf的路径

启动：

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

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

停止（平滑停止MySQL）：

mysqladmin -uroot -p 123456 -S /data/3306/mysql.sock shutdown

mysqladmin -uroot -p 123456 -S /data/3307/mysql.sock shutdown

（2）配置3306的启动文件

vi /data/3306/mysql

  #!/bin/sh

#init

port=3306

mysql_user="root"

mysql_pwd="oldboy"

CmdPath="/application/mysql/bin"

mysql_sock="/data/${port}/mysql.sock"

 

#startup function

function_start_mysql()

{

    if [ ! -e"$mysql_sock" ];then

      printf "StartingMySQL...\n"

      /bin/sh${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &

    else

      printf "MySQL isrunning...\n"

      exit

    fi

}

 

#stop function

function_stop_mysql()

{

    if [ ! -e"$mysql_sock" ];then

       printf "MySQL isstopped...\n"

       exit

    else

       printf "StopingMySQL...\n"

       ${CmdPath}/mysqladmin-u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

   fi

}

 

#restart function

function_restart_mysql()

{

    printf "RestartingMySQL...\n"

    function_stop_mysql

    sleep 2

    function_start_mysql

}

 

case $1 in

start)

    function_start_mysql

;;

stop)

    function_stop_mysql

;;

restart)

    function_restart_mysql

;;

*)

    printf "Usage:/data/${port}/mysql {start|stop|restart}\n"

esac

（3）配置3307的启动文件

vi /data/3307/mysql

#!/bin/sh

 

#init

port=3307

mysql_user="root"

mysql_pwd="oldboy"

CmdPath="/application/mysql/bin"

mysql_sock="/data/${port}/mysql.sock"

 

#startup function

function_start_mysql()

{

    if [ ! -e"$mysql_sock" ];then

      printf "StartingMySQL...\n"

      /bin/sh${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &

    else

      printf "MySQL isrunning...\n"

      exit

    fi

}

 

#stop function

function_stop_mysql()

{

    if [ ! -e "$mysql_sock"];then

       printf "MySQL isstopped...\n"

       exit

    else

       printf "StopingMySQL...\n"

       ${CmdPath}/mysqladmin-u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

   fi

}

 

#restart function

function_restart_mysql()

{

    printf "RestartingMySQL...\n"

    function_stop_mysql

    sleep 2

    function_start_mysql

}

 

case $1 in

start)

    function_start_mysql

;;

stop)

    function_stop_mysql

;;

restart)

    function_restart_mysql

;;

*)

    printf "Usage:/data/${port}/mysql {start|stop|restart}\n"

esac

6、授权mysql用户管理/data目录

chown -R mysql.mysql /data

7、给予mysql脚本执行权限

find /data -type f -name mysql | xargs chmod +x

8、配置环境变量

echo 'export PATH=/application/mysql/bin/:$PATH' >>/etc/profile

9、初始化多实例MySQL

（1）MySQL5.1.X

#命令在mysql/bin目录下

mysql_install_db --basedir=/application/mysql--datadir=/data/3306/data --user=mysql

mysql_install_db --basedir=/application/mysql--datadir=/data/3307/data --user=mysql

（2）MySQL5.5.X

#命令在mysql/scripts目录下

cd /application/mysql/scripts

./mysql_install_db--basedir=/application/mysql --datadir=/data/3306/data --user=mysql

./mysql_install_db--basedir=/application/mysql --datadir=/data/3307/data --user=mysql

（3）比较一下：单实例MySQL5.5.X

    mysql_install_db