• MySQL多实例部署与优化

    MySQL安装

     1 ##上传MySQL安装包##
 2 mkdir /home/oldboy/tools -p
 3 cd /home/oldboy/tools/
 4 ###wget -q http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.34.tar.gz 
 5 ####安装依赖
 6 yum install ncurses-devel libaio-devel -y
 7 ###yum安装cmake
 8 yum install cmake -y
 9 ###添加MySQL用户
10 useradd -s /sbin/nologin -M mysql
11 id mysql
12 ####解压MySQL安装包
13 tar xf mysql-5.6.34.tar.gz
14 cd mysql-5.6.34
15 ####编译
16 cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.34 
17 -DMYSQL_DATADIR=/application/mysql-5.6.34/data 
18 -DMYSQL_UNIX_ADDR=/application/mysql-5.6.34/tmp/mysql.sock 
19 -DDEFAULT_CHARSET=utf8 
20 -DDEFAULT_COLLATION=utf8_general_ci 
21 -DWITH_EXTRA_CHARSETS=all 
22 -DWITH_INNOBASE_STORAGE_ENGINE=1 
23 -DWITH_FEDERATED_STORAGE_ENGINE=1 
24 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 
25 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 
26 -DWITH_ZLIB=bundled 
27 -DWITH_SSL=bundled 
28 -DENABLED_LOCAL_INFILE=1 
29 -DWITH_EMBEDDED_SERVER=1 
30 -DENABLE_DOWNLOADS=1 
31 -DWITH_DEBUG=0
32 echo $?
33 make && make install
34 echo $?
35 ln -s /application/mysql-5.6.34/ /application/mysql
36 ####初始化数据库
37 cp support-files/my*.cnf /etc/my.cnf
38 /application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data --user=mysql
39 
40 cp support-files/mysql.server /etc/init.d/mysqld
41 chmod 700 /etc/init.d/mysqld
42 chkconfig mysqld on
43 chkconfig --list mysqld
44 
45 ##启动数据库
46 /etc/init.d/mysqld start
47 echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
48 tail -1 /etc/profile
49 source /etc/profile
50 echo $PATH
51 mysql
52 
53 ###出现问题查看日志
54 tail -100 /application/mysql/data/db01.err
55 
56 ###优化数据库
57 ###设置密码
58 mysqladmin -uroot -p password oldboy123
59 ###清理无用的库，无用的用户
60 show databases;
61 drop database test;
62 select user,host from mysql.user;
63 drop user 'root'@'::1';
64 select user,host from mysql.user;
65 drop user ''@'db01';
66 drop user 'root'@'db01';
67 drop user ''@'localhost';
68 select user,host from mysql.user;
69 #################以上单实例#####################
    mysql的安装

    第1章 MySQL多实例配置

    1.1 什么是MySQL多实例？

    简单地说，MySQL多实例就是在一台服务器上同时开启多个不同的服务端口（如：3306,3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务。

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

    打个比方吧，MySQL多实例就相当于房子的多个卧室，每个实例可以看作一间卧室，整个服务器就是一套房子，服务器的硬件资源（cpu,men,disk）、软件资源（CentOS操作系统）可以看作房子的卫生间、厨房、客厅，是房子的共用资源。若你是北漂的小伙伴，与朋友一起租房子，相信更好理解，大家蜗居在一起，休息在自己的卧室，出来活动肯定是要共用上述公共资源。这样就可以很好的理解MySQL多实例了。

     

    其实很多网络服务都是可以配置多实例的，例如nginx、Apache、haproxy、redis等都可以配置多实例。这在门户网站使用都很广泛。

    1.2 MySQL多实例的作用于问题

    • 有效利用服务器资源

    当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务，且可以实现资源的逻辑隔离。

    • 节约服务器资源

    当公司资金紧张，但是数据库又需要各自尽量独立地提供服务，而且，需要主从复制等技术，多实例就再好不过了。

    MySQL多实例有它的好处，但也有其弊端，比如，会存在资源互相抢占的问题。

    当某个数据库实例并发很高或者有SQL慢查询时，整个实例会消耗大量的系统CPU、磁盘I/O等资源，导致服务器上的其他数据库实例提供服务的质量一起下降。这就相当于大家住在一个房子的不同卧室一样，早晨起来上班，都要刷牙、洗脸等，这样卫生间就会长期占有，其他人就要等待一样。不同实例获取的资源是相对独立的，无法像虚拟化一样完全隔离。

    1.3 MySQL多实例的生产应用场景

    1、资金紧张型公司的选择

    若公司资金紧张，公司业务访问量又不是太大，但又希望不同业务的数据库服务各自尽量独立地提供服务而互相不受影响，同时，还需要主从复制等技术提供备份或读写分离服务，那么，多实例就再好不过了。比如：可以通过3台服务器部署9~15个实例，交叉做主从复制、数据备份及读写分离，这样就可以达到9~15台服务器每个只装一个数据库才有的效果。这里要强调的是，所谓的尽量独立是相对的。

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

    当公司业务访问量不太大的时候，服务器的资源基本都是浪费的，这时就很适合多实例的应用，如果对SQL语句的优化做得比较好，MySQL多实例会是一个很值得使用的技术，即使并发很大，合理分配好系统资源以及搭配好服务，也不会有太大问题。

    3、门户网站应用MySQL多实例场景

        门户网站通常都会使用多实例，因为配置硬件好的服务器，可节省IDC机柜空间，同时，跑多实例也会减少硬件资源跑不满的浪费。比如：百度公司的很多数据库都是多实例，不过，一般是从库多实例，例如某部门使用的IBM服务器为48核CPU，内存96GB，一台服务器跑3~4个实例；此外，sina网也是用的多实例，内存48GB左右。

    说明：据调查，sina网的数据库单机1~4个数据库实例的居多，其中又数1~2个的最多，因为大业务占用的机器比较多。服务器是DELL R510的居多，CPU是E5210，48GB内存，磁盘12*300GSAS，做RAID10，此为门户网站的服务器配置参考。

        另外，sina网站安装数据库时，一般采用编译安装的方式，并且会在进行优化之后做rpm包，以便统一使用。

    1.4 配置MySQL多实例

    上传文件地址：

    链接：https://pan.baidu.com/s/14nfi-bHweexKO5dJAoZyMA 密码：nmko

     1 ##关闭单实例，跟多实例端口有冲突
 2 /etc/init.d/mysqld stop
 3 chkconfig mysqld off
 4 chkconfig --list|grep mys
 5 ####创建目录##
 6 mkdir -p /data/{3306,3307}/data
 7 ###上传配置文件到根下，并解压###
 8  
 9 chown -R mysql.mysql /data/
10 find /data -name mysql
11 find /data -name mysql|xargs chmod 700
12 find /data -name mysql|xargs ls -l
13 ####初始化数据库
14 cd /application/mysql/scripts
15 ./mysql_install_db  --defaults-file=/data/3306/my.cnf --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
16 ./mysql_install_db  --defaults-file=/data/3307/my.cnf --basedir=/application/mysql --datadir=/data/3307/data --user=mysql
17 ####配置环境变量###
18 #echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
19 #source  /etc/profile
20 ####启动数据库###
21 /data/3306/mysql start
22 /data/3307/mysql start
23 netstat -lntup|grep 330
24 ###登录数据库####
25 mysql -S /data/3306/mysql.sock 
26 mysql -S /data/3307/mysql.sock
    多实例

    1.5 增加一个实例3308

     1 ####增加一个实例3308####
 2 mkdir -p /data/3308/data
 3 
 4 cd /data/3308
 5 vim my.cnf
 6 [client]
 7 port            = 3308
 8 socket          = /data/3308/mysql.sock
 9 
10 [mysql]
11 no-auto-rehash
12 
13 [mysqld]
14 user    = mysql
15 port    = 3308
16 socket  = /data/3308/mysql.sock
17 basedir = /application/mysql
18 datadir = /data/3308/data
19 open_files_limit    = 1024
20 back_log = 600
21 max_connections = 800
22 max_connect_errors = 3000
23 table_open_cache = 614
24 external-locking = FALSE
25 max_allowed_packet =8M
26 sort_buffer_size = 1M
27 join_buffer_size = 1M
28 thread_cache_size = 100
29 thread_concurrency = 2
30 query_cache_size = 2M
31 query_cache_limit = 1M
32 query_cache_min_res_unit = 2k
33 #default_table_type = InnoDB
34 thread_stack = 192K
35 #transaction_isolation = READ-COMMITTED
36 tmp_table_size = 2M
37 max_heap_table_size = 2M
38 #long_query_time = 1
39 #log_long_format
40 #log-error = /data/3308/error.log
41 #log-slow-queries = /data/3308/slow.log
42 pid-file = /data/3308/mysql.pid
43 #log-bin = /data/3308/mysql-bin
44 relay-log = /data/3308/relay-bin
45 relay-log-info-file = /data/3308/relay-log.info
46 binlog_cache_size = 1M
47 max_binlog_cache_size = 1M
48 max_binlog_size = 2M
49 expire_logs_days = 7
50 key_buffer_size = 16M
51 read_buffer_size = 1M
52 read_rnd_buffer_size = 1M
53 bulk_insert_buffer_size = 1M
54 lower_case_table_names = 1
55 skip-name-resolve
56 slave-skip-errors = 1032,1062
57 replicate-ignore-db=mysql
58 server-id = 8
59 innodb_additional_mem_pool_size = 4M
60 innodb_buffer_pool_size = 32M
61 innodb_data_file_path = ibdata1:128M:autoextend
62 innodb_file_io_threads = 4
63 innodb_thread_concurrency = 8
64 innodb_flush_log_at_trx_commit = 2
65 innodb_log_buffer_size = 2M
66 innodb_log_file_size = 4M
67 innodb_log_files_in_group = 3
68 innodb_max_dirty_pages_pct = 90
69 innodb_lock_wait_timeout = 120
70 innodb_file_per_table = 0
71 [mysqldump]
72 quick
73 max_allowed_packet = 2M
74 
75 [mysqld_safe]
76 log-error=/data/3308/oldboy_3308.err
77 pid-file=/data/3308/mysqld.pid
    增加一个实例3308
     1 vim mysql
 2 #!/bin/sh
 3 ################################################
 4 #this scripts is created by oldboy at 2007-06-09
 5 #oldboy QQ:31333741
 6 #site:http://www.etiantian.org
 7 #blog:http://oldboy.blog.51cto.com
 8 #oldboy trainning QQ group: 208160987 226199307  44246017
 9 ################################################
10 #init
11 port=3308
12 mysql_user="root"
13 mysql_pwd="oldboy123"
14 CmdPath="/application/mysql/bin"
15 mysql_sock="/data/${port}/mysql.sock"
16 start(){
17     if [ ! -e "$mysql_sock" ];then
18          printf "Starting MySQL...
"
19         /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
20     else
21         printf "MySQL is running...
"
22         exit 1
23     fi
24 }
25 stop(){
26     if [ ! -e "$mysql_sock" ];then
27         printf "MySQL is stopped...
"
28         exit 1
29     else
30         printf "Stoping MySQL...
"
31         ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
32     fi
33 }
34 
35 restart(){
36     printf "Restarting MySQL...
"
37     stop
38     sleep 2
39     start
40 }
    对应脚本

    1.6 快速增加一个实例3308

     1 ####快速增加实例####
 2 mkdir -p /data/3308/data
 3 cp /data/3306/my.cnf  /data/3308/
 4 cp /data/3306/mysql  /data/3308/
 5 sed -i 's/3306/3308/g' /data/3308/my.cnf 
 6 sed -i 's/server-id = 6/server-id = 8/g' /data/3308/my.cnf 
 7 sed -i 's/3306/3308/g' /data/3308/mysql
 8 chown -R mysql:mysql /data/3308
 9 chmod 700 /data/3308/mysql
10 cd /application/mysql/scripts
11 ./mysql_install_db --defaults-file=/data/3308/my.cnf --datadir=/data/3308/data --basedir=/application/mysql --user=mysql
12 chown -R mysql:mysql /data/3308
13 egrep "server-id|log-bin" /data/3308/my.cnf 
14 /data/3308/mysql start
15 sleep 5
16 netstat -lnt|grep 3308
    View Code

    1.7 为数据库设置密码

    1 ####
2 mysqladmin -uroot -p password oldboy123 -S /data/3308/mysql.sock
3 mysqladmin -uroot -p password oldboy123 -S /data/3307/mysql.sock
4 mysqladmin -uroot -p password oldboy123 -S /data/3306/mysql.sock
    View Code

    1.8 MySQL关闭优化

     1 ####优化关闭MySQL####
 2 [root@db01 3306]# cat /data/3306/mysql_oldboy
 3 #!/bin/sh
 4 #init
 5 port=3306
 6 mysql_user="root"
 7 mysql_pwd="oldboy123"
 8 CmdPath="/application/mysql/bin"
 9 mysql_sock="/data/${port}/mysql.sock"
10 mysqld_pid_file_path=/application/mysql/3306.pid
11 start(){
12     if [ ! -e "$mysql_sock" ];then
13          printf "Starting MySQL...
"
14         /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &
15         sleep 3
16     else
17         printf "MySQL is running...
"
18         exit 1
19     fi
20 }
21 stop(){
22     if [ ! -e "$mysql_sock" ];then
23         printf "MySQL is stopped...
"
24         exit 1
25     else
26         printf "Stoping MySQL...
"
27         mysqld_pid=`cat "$mysqld_pid_file_path"`
28          if (kill -0 $mysqld_pid 2>/dev/null)
29            then
30              kill $mysqld_pid
31              sleep 2
32          fi
33     fi
34 }
35 
36 restart(){
37     printf "Restarting MySQL...
"
38     stop
39     sleep 2
40     start
41 }
42 
43 case "$1" in
44     start)
45         start
46     ;;
47     stop)
48         stop
49     ;;
50     restart)
51         restart
52     ;;
53     *)
54         printf "Usage: /data/${port}/mysql {start|stop|restart}
"
55 esac
56 
57 sh -x /data/3306/mysql_oldboy stop
    优化

    1.9 不用密码登录配置

     1 ###以前面配置的单实例MySQL为例
 2 ###不用密码登录
 3 [root@db01 3306]# head /etc/my.cnf
 4 # For advice on how to change settings please see
 5 # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
 6 # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
 7 # *** default location during install, and will be replaced if you
 8 # *** upgrade to a newer version of MySQL.
 9 [client]          
10 user=root         
11 password=oldboy123
12 [root@db01 3306]# chmod 700 /etc/my.cnf
    /etc/my.cnf文件

    1.10 数据库管理

    #####数据库管理###

    help contents; #查看帮助，help可以用?替代

    Account Management

    账户管理

    Administration

    管理

    Compound Statements

    复合语句

    Data Definition

    数据定义

    Data Manipulation

    数据操作

    Data Types

    数据类型

    Functions

    函数

    Functions and Modifiers for Use with GROUP BY

    与GROUP BY相关的函数和修饰符

    Geographic Features

    地理特征

    Help Metadata

    帮助元数据

    Language Structure

    语言结构

    Plugins

    插件

    Procedures

    存储过程

    Storage Engines

    存储引擎

    Table Maintenance

    表维护

    Transactions

    事务处理

    User-Defined Functions

    用户自定义函数

    Utility

    实用程序

    1.11 MySQL密码忘记找回

    1 ###MySQL密码忘记####
2 mysqld_safe --skip-grant-tables --user=mysql &
3 #mysqld_safe --defaults-file=/data/3307/my.cnf --skip-grant-tables >/dev/null 2>&1 &
4 mysql
5 #用户@主机    唯一标识
6 mysql> update mysql.user set password=PASSWORD('old') where user='root' and host='localhost';
7 mysql> flush privileges;
8 /etc/init.d/mysqld stop
9 /etc/init.d/mysqld start
    MySQL密码忘记
  • 相关阅读:
    while (cin>>str)退出死循环
    内存溢出（heap corruption detected:）
    二叉树的遍历--递归+非递归(两种)
    直接插入排序（带哨兵和不带哨兵）
    二项队列
    左式堆
    优先队列之二叉堆与d-堆
    centos6.4 安装code::blocks
    结构之美——优先队列基本结构（四）——二叉堆、d堆、左式堆、斜堆
    数据结构与算法分析-开放定址散列表的实现
  • 原文地址：https://www.cnblogs.com/zhaojingyu/p/8874125.html
Copyright © 2020-2023  润新知