MariaDB多实例

MariaDB多实例

什么是mariadb

 

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Widenius（英语：Michael Widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。(来自百度百科)

多实例介绍

 

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

 

搭建环境

操作系统：CentOS 6.4

Mariadb: 5.5.25

PORT：3307，3308

 

 

安装单实例mariadb

[root@test60 ~]#wget  

[root@test60 ~]#wget  

安装cmake

[root@test60 ~]# tar xzvf cmake-2.8.5

[root@test60 ~]# cd cmake-2.8.5

[root@test60 ~]#./bootstrap

[root@test60 ~]#make

[root@test60 ~]#make install

[root@test60 ~]#cd ..

 

安装mariadb

[root@test60 ~]# useradd mysql –s /sbin/nologin –M

[root@test60 ~]# yum install

[root@test60 ~]# yum install ncurses-devel –y

[root@test60 ~]# yum install libaio-devel –y

[root@test60 ~]# tar xzvf mariadb-5.5.25.tar.gz

[root@test60 ~]# cd mariadb-5.5.25

[root@test60 mariadb-5.5.25]# cmake .\

-DCMAKE_INSTALL_PREFIX=/usr/local/mariamysql \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=1 \

-DENABLED_LOCAL_INFILE=1 \

-DEXTRA_CHARSETS=all \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_DEBUG=0 \

-DBUILD_CONFIG=mysql_release \

-DFEATURE_SET=community \

-DWITH_EMBEDDED_SERVER=OFF

[root@test60 mariadb-5.5.25]#make -j 4

[root@test60 mariadb-5.5.25]#make install

 

创建目录

[root@test60 ~]#mkdir -p /data/{3306,3307}/data

[root@test60 ~]# cd /data

[root@test60 ~]#chown –R mysql.mysql 3007

[root@test60 ~]#chown –R mysql.mysql 3008

 

创建配置文件my.cnf

[root@test60 ~]#cd /data/3307

[root@test60 ~]#vim 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 = /usr/local/mariamysql

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

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

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

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

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

#relay-log-info-file = /data/3307relay-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/3307mysql_oldboy3306.err

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

 

创建启动脚本

[root@test60 ~]#cd /data/3307

[root@test60 ~]#vim mariadb

#!/bin/sh

port=3307

mysql_user="root"

mysql_pwd=""

CmdPath="/usr/local/mariamysql/bin"

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

 

#startup function

function_start_mysql()

{

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

printf "Starting MariaMySQL...\n"

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

else

printf "MariaMySQL is running...\n"

exit

fi

}

#stop function

function_stop_mysql()

{

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

printf "MariaMySQL is stopped...\n"

exit

else

printf "Stoping MariaMySQL...\n"

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

fi

}

#restart function

function_restart_mysql()

{

printf "Restarting MariaMySQL...\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

 

[root@test60 ~]#chmod 700 mariadb

 

数据库初始化

/usr/local/mariamysql/scripts/mysql_install_db --basedir=/usr/local/mariamysql/ --datadir=/data/3307/data --user=mysql

 

启动端口为3307的数据库实例

[root@test60 ~]#/data/3307/mariadb start

检查是否正常监听

[root@test60 ~]# lsof -i :3307

COMMAND  PID  USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME

mysqld  7261 mysql   14u  IPv4 7939256      0t0  TCP *:opsession-prxy (LISTEN)

测试是否能登陆

[root@test60 ~]# /usr/local/mariamysql/bin/mysql -u root -p -S /data/3307/mysql.sock

 

增加mariadb实例（端口:3308）

[root@test60 ~]# cd /data/3308

[root@test60 3308]# cp ../3307/my.cnf .

[root@test60 3308]# cp ../3307/mariadb .

[root@test60 3308]# sed -i 's#3307#3308#g' my.cnf

[root@test60 3308]# sed -i 's#3307#3308#g' mariadb

[root@test60 3308]#chown –R mysql.mysql /data/3308

[root@test60 3308]#chmod 700 mariadb

[root@test60 3308]# /usr/local/mariamysql/scripts/mysql_install_db --basedir=/usr/local/mariamysql/ --datadir=/data/3307/data --user=mysql

[root@test60 3308]#/data/3308/mariadb start

[root@test60 3308]# /usr/local/mariamysql/bin/mysql -u root -p -S /data/3307/mysql.sock

 

[root@test60 3308]# netstat -lntp | grep 330

tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      7261/mysqld        

tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      13824/mysqld