出产环境MySQL 5.5.x单机多实例装备实践51CTO博客 - 千亿集团

出产环境MySQL 5.5.x单机多实例装备实践51CTO博客

2019-01-03 17:52:21 | 作者: 醉柳 | 标签: 实例,装备,装置 | 浏览: 2033

布景需求:
1)在一台新收购的服务器上经过源码编译装置一个版别为5.5.x以上的MySQL数据库,并将一切装备文件与数据等均寄存在/opt/mysql,以便于往后完成快速搬迁、仿制和全体备份;
2)在同一个MySQL中运转两个实例,一个绑定在端口3306,一个绑定在端口3307;绑定在3306端口的实例,不敞开binlog,数据寄存在/opt/mysql/data;绑定在3307端口的实例,敞开binlog,数据寄存在/opt/mysql/data2;
3)两个实例均选用InnoDB作为默许的存储引擎,字符编码选用UTF-8;两个实例均选用相同的功能优化装备参数,完成单机多实例的装备运用。

施行计划:
在编译装置时,将数据库的装备文件my.cnf以及data目录等均指向到/opt/mysql目录;经过mysqld_multi的方法来办理两个不同的实例,选用相同的装备文件同享功能优化装备参数;在同一个装备文件中,运用[mysqld1]与[mysqld2]标签完成不同实例的差异化装备。

装备进程:
1、源码编译装置MySQL
1)装置所需体系软件包
# yum -y install gcc gcc-c++ autoconf bison libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel openldap openldap-devel nss_ldap openldap-clients openldap-servers

2)装置前的体系设置
# mkdir /opt/mysql  //mysql装置目录
# mkdir /opt/mysql/data  //mysql数据寄存目录
# groupadd mysql  //创立用户
# useradd -g mysql mysql //创立用户组
# chown mysql:mysql -R /opt/mysql/data //赋予数据寄存目录权限

3)装置cmake
MySQL从5.5版别开端,经过./configure进行编译装备方法现已被撤销,取而代之的是cmake东西。因而,需求先在体系中源码编译装置cmake。
# mkdir /home/tools
# cd /home/tools
# wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz
# tar xf cmake-2.8.4.tar.gz
# cd cmake-2.8.4
# ./configure
# make;make install

4)开端编译装置MySQL
# wget http://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.19.tar.gz
# tar xf mysql-5.5.19.tar.gz
# cd mysql-5.5.19
# cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql \
-DSYSCONFDIR=/opt/mysql/etc \
-DMYSQL_DATADIR=/opt/mysql/data \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock \
-DMYSQL_USER=mysql \
-DEXTRA_CHARSETS=all \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1
# make;make install

2、创立单机支撑多实例
1)进入MySQL主目录
# cd /opt/mysql/

2)删去默许的data目录
# rm -rf data

3)创立需求的目录
# mkdir etc tmp run log binlogs data data2
# chown -R mysql:mysql tmp run log binlogs data data2

4)创立my.cnf装备文件
# vi etc/my.cnf

## This server may run 2+ separate instances 
## So we use mysqld_multi to manage their services 
[mysqld_multi] 
mysqld = /opt/mysql/bin/mysqld_safe 
mysqladmin = /opt/mysql/bin/mysqladmin 
log = /opt/mysql/log/mysqld_multi.log 
user = root ## Used for stopping the server via mysqladmin 
#password =  

## This is the general purpose database 
## The locations are default 
# They are left in [mysqld] in case the server is started normally instead of by mysqld_multi 
[mysqld1] 
socket = /opt/mysql/run/mysqld.sock 
port = 3306 
pid-file = /opt/mysql/run/mysqld.pid 
datadir = /opt/mysql/data 
lc-messages-dir = /opt/mysql/share/english 
  
## These support master - master replication 
#auto-increment-increment = 4 
#auto-increment-offset = 1  ## Since it is master 1 
#log-bin = /opt/mysql/binlogs/bin-log-mysqld1 
#log-bin-index = /opt/mysql/binlogs/bin-log-mysqld1.index 
#binlog-do-db = ## Leave this blank if you want to control it on slave 
#max_binlog_size = 1024M 
  
## This is exlusively for mysqld2 
## It is on 3307 with data directory /opt/mysql/data2 
[mysqld2] 
socket = /opt/mysql/run/mysqld.sock2 
port = 3307 
pid-file = /opt/mysql/run/mysqld.pid2 
datadir = /opt/mysql/data2 
lc-messages-dir = /opt/mysql/share/english 
  
## Disable DNS lookups 
#skip-name-resolve 
  
## These support master - slave replication 
log-bin = /opt/mysql/binlogs/bin-log-mysqld2 
log-bin-index = /opt/mysql/binlogs/bin-log-mysqld2.index 
#binlog-do-db =  ## Leave this blank if you want to control it on slave 
max_binlog_size = 1024M 
  
## Relay log settings 
#relay-log = /opt/mysql/log/relay-log-mysqld2 
#relay-log-index = /opt/mysql/log/relay-log-mysqld2.index 
#relay-log-space-limit = 4G 
  
## Slow query log settings 
#log-slow-queries = /opt/mysql/log/slow-log-mysqld2 
#long_query_time = 2 
#log-queries-not-using-indexes 
   
## The rest of the my.cnf is shared 
## Here follows entries for some specific programs 
## The MySQL server 
[mysqld] 
basedir = /opt/mysql 
tmpdir = /opt/mysql/tmp 
socket = /opt/mysql/run/mysqld.sock 
port = 3306 
pid-file = /opt/mysql/run/mysqld.pid 
datadir = /opt/mysql/data 
lc-messages-dir = /opt/mysql/share/english 
 
skip-external-locking 
key_buffer_size = 16K 
max_allowed_packet = 1M 
table_open_cache = 4 
sort_buffer_size = 64K 
read_buffer_size = 256K 
read_rnd_buffer_size = 256K 
net_buffer_length = 2K 
thread_stack = 128K 
  
## Increase the max connections 
max_connections = 200 
  
## The expiration time for logs, including binlogs 
expire_logs_days = 14 
  
## Set the character as utf8 
character-set-server = utf8 
collation-server = utf8_unicode_ci 
  
## This is usually only needed when setting up chained replication 
#log-slave-updates 
  
## Enable this to make replication more resilient against server crashes and restarts 
## but can cause higher I/O on the server 
#sync_binlog = 1 
  
## The server id, should be unique in same network 
server-id = 1 
 
## Set this to force MySQL to use a particular engine/table-type for new tables
## This setting can still be overridden by specifying the engine explicitly 
## in the CREATE TABLE statement 
default-storage-engine = INNODB 
  
## Uncomment the following if you are using InnoDB tables 
#innodb_data_home_dir = /opt/mysql/data 
#innodb_data_file_path = ibdata1:10M:autoextend 
#innodb_log_group_home_dir = /opt/mysql/data 
## You can set .._buffer_pool_size up to 50 - 80 % of RAM 
## but beware of setting memory usage too high 
innodb_buffer_pool_size = 16M 
innodb_additional_mem_pool_size = 2M 
## Set .._log_file_size to 25 % of buffer pool size 
innodb_log_file_size = 5M 
innodb_log_buffer_size = 8M 
innodb_flush_log_at_trx_commit = 1 
innodb_lock_wait_timeout = 50 
  
[mysqldump] 
quick 
max_allowed_packet = 16M 
  
[mysql] 
no-auto-rehash 
   
[myisamchk] 
key_buffer_size = 8M 
sort_buffer_size = 8M 
   
[mysqlhotcopy] 
interactive-timeout 
  
[mysql.server] 
user = mysql 
  
[mysqld_safe] 
log-error = /opt/mysql/log/mysqld.log 
pid-file = /opt/mysql/run/mysqld.pid 
open-files-limit = 8192 
  
[client] 
default-character-set = utf8

5)修正my.cnf读写权限,防止普通用户获取到MySQL暗码
# chown -R root:root /opt/mysql/etc
# chmod 600 /opt/mysql/etc/my.cnf

3、初始化数据库
1)切换到mysql用户,进入MySQL主目录
# su - mysql 
# cd /opt/mysql/ 

2)初始化实例[mysqld1]和[mysqld2]
# scripts/mysql_install_db --basedir=/opt/mysql --user=mysql --datadir=/opt/mysql/data/
# scripts/mysql_install_db --basedir=/opt/mysql --user=mysql --datadir=/opt/mysql/data2/

3)回到root,创立mysqld_multi.server脚本
# exit
# mkdir -p /opt/mysql/init.d
# cp support-files/mysqld_multi.server /opt/mysql/init.d/
# vi /opt/mysql/init.d/mysqld_multi.server

#!/bin/sh
#
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
# This script assumes that my.cnf file exists either in /etc/my.cnf or
# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
# mysqld_multi documentation for detailed instructions.
#
# This script can be used as /etc/init.d/mysql.server
#
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
#
# Version 1.0
#

basedir=/opt/mysql
bindir=/opt/mysql/bin

conf=/opt/mysql/etc/my.cnf
export PATH=$PATH:$bindir

if test -x $bindir/mysqld_multi
then
  mysqld_multi="$bindir/mysqld_multi";
else
  echo "Cant execute $bindir/mysqld_multi from dir $basedir";
  exit;
fi

case "$1" in
    start )
        "$mysqld_multi" --defaults-extra-file=$conf start $2
        ;;
    stop )
        "$mysqld_multi" --defaults-extra-file=$conf stop $2
        ;;
    report )
        "$mysqld_multi" --defaults-extra-file=$conf report $2
        ;;
    restart )
        "$mysqld_multi" --defaults-extra-file=$conf stop $2
        "$mysqld_multi" --defaults-extra-file=$conf start $2
        ;;
    *)
        echo "Usage: $0 {start|stop|report|restart}" >&2
        ;;
esac

4、全体备份MySQL
# cd /opt/
# tar czvf mysql_5.5.19_full.tar.gz mysql5519/

备份完成后,直接将mysql_5.5.19_full.tar.gz拿到其他服务器上,解压后便能够直接启用。

5、办理MySQL实例
1)一起发动实例 [mysqld1] 与 [mysqld2]:
# /opt/mysql/init.d/mysqld_multi.server start 1,2

2)检查两个MySQL实例是否都现已成功发动
# netstat -lntp | grep mysqld
tcp        0      0 0.0.0.0:3306        0.0.0.0:*         LISTEN      28752/mysqld       
tcp        0      0 0.0.0.0:3307        0.0.0.0:*         LISTEN      28756/mysqld
一起封闭实例 [mysqld1] 与 [mysqld2]:
# /opt/mysql/init.d/mysqld_multi.server stop 1,2

仅发动/封闭实例[mysqld1]:
# /opt/mysql/init.d/mysqld_multi.server start 1
# /opt/mysql/init.d/mysqld_multi.server stop 1

6、登陆MySQL实例
在发动了实例[mysqld1]与[mysqld2]后,经过以下方法登陆不同的实例:

登陆[mysqld1]:
# /opt/mysql/bin/mysql -uroot -h127.0.0.1 -P3306 -p

登陆[mysqld2]:
# /opt/mysql/bin/mysql -uroot -h127.0.0.1 -P3307 -p

7、其他初始化设置
1)为MySQL的root帐户设置初始暗码
# /opt/mysql/bin/mysqladmin -u root -h127.0.0.1 -P3306 password 123456
# /opt/mysql/bin/mysqladmin -u root -h127.0.0.1 -P3307 password 123456

2)修正my.cnf装备文件中MySQL的root账户暗码
# vim /opt/mysql/etc/my.cnf
user = root ## Used for stopping the server via mysqladmin 
password = 123456
 
3)删去匿名衔接的空暗码帐号
别离登陆实例[mysqld1]与[mysqld2],履行以下指令:
mysql> use mysql; //挑选体系数据库mysql
Database changed
mysql> select Host,User,Password from user; //检查一切用户
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql-01  | root |                                           |
| 127.0.0.1 | root |                                           |
| ::1       | root |                                           |
| localhost |      |                                           |
| mysql-01  |      |                                           |
+-----------+------+-------------------------------------------+
6 rows in set (0.00 sec)

mysql> delete from user where password=""; //删去匿名衔接的空暗码帐号
Query OK, 5 rows affected (0.00 sec)

mysql> flush privileges;  //改写权限
Query OK, 0 rows affected (0.00 sec)

mysql> select Host,User,Password from user; //承认暗码为空的用户是否已悉数删去
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> exit;

经验总结:
1、选用源码编译装置MySQL,能够便利咱们自己安排一切MySQL相关文件的方位;一起经过源码编译装置后的MySQL,能够直接仿制到其它服务器上运转,大大便利了咱们往后的搬迁、备份和新服务器的装备;
2、本次计划只是施行了两个实例[mysqld1]与[mysqld2],实际上咱们能够经过这样的方法,拓宽完成[mysqld3],[mysqld4],[mysqld5]...等更多的实例;
3、MySQL自带了几个不同的装备文件,放置在/opt/mysql/support-files目录下,别离是my-huge.cnf,my-innodb-heavy-4G.cnf,my-large.cnf,my-medium.cnf,my-small.cnf,经过称号咱们能够很直观的了解到他们是针对不同的服务器装备的;
4、在单机运转多实例的情况下,不要运用mysql -hlocalhost或直接疏忽-h参数登陆服务器,由于假如运用localhost或疏忽-h参数,而不是指定127.0.0.1的话,即便挑选的端口是3307,仍是会登陆到3306实例中去,所以尽量防止这种紊乱的发生,统一用127.0.0.1绑定端口或选用socket来登陆。

BTW:假如我们以为我写的还能够,希望能给我的博客投个票,谢谢!O(∩_∩)O

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表千亿集团立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章