mariadb--multiple-mode deployment and multi-instance deployment

Background

??

MARIADB Introduction

??
?? MARIADB database management System is a branch of MySQL, mainly by the open source community in the maintenance, the use of GPL licensing MARIADB is fully compatible with MySQL, including the API and command line, so that it can easily become a substitute for MySQL. For the storage engine, use XtraDB (English: XtraDB) instead of the MySQL InnoDB. MARIADB, led by MySQL's founder Michael Widenius (English: Michael Widenius), sold his company, MySQL AB, to Sun for $1 billion earlier, and since Sun was acquired by Oracle, MySQL's ownership also falls into Oracle's hands. Mariadb name comes from Michael Widenius's daughter Maria's name.
?? MARIADB, the transaction-based Maria Storage engine, replaces the MySQL MyISAM storage engine, which uses a variant of the Percona Xtradb,innodb, which the branch developers want to provide access to the upcoming MySQL 5.4 InnoDB performance. This version also includes the PrimeBase XT (PBXT) and Federatedx storage engines.
??

3 Ways of deploying

??

Install by Yum mode

??
CentOS (or other distributions) contains the corresponding version in its own official source that can be installed directly from its own package manager

    #centos 7    yum install mariadb-server -y    #centos 6    yum install mysql-server -y

?? Typically, the MARAIDB versions of the built-in libraries are older. To take care of the user's desire to use the new version and to manage it conveniently, MARIADB officially provides a new version of the update source. Users can follow the prompts to configure their own update source to use the new version of the MARIADB, the official link is as follows
https://downloads.mariadb.org/mariadb/repositories/#mirror =tuna&distro=centos&distro_release= centos7-amd64--centos7&version=10.2

Example: Here, take Centos7 as an example.
Create a Maraidb.repo under/etc/yum.repos.d/

    vim  /etc/yum.repos.d/mariadb.repo

Write the following fields within the file

# MariaDB 10.2 CentOS repository list - created 2018-06-05 07:06 UTC# http://downloads.mariadb.org/mariadb/repositories/[mariadb]name = MariaDBbaseurl = http://yum.mariadb.org/10.2/centos7-amd64gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck=1

Purge Yum cache after save and rebuild cache

yum clean allyum makecache

Wait for the cache to be built and manage the new version of MARIADB with Yum directly.

    #查看mariadb的版本信息    yum info mariadb-server    #安装新版mariadb，默认优先安装新版    yum install mariadb-server -y

??
??

CENTOS7 Installing binary mariadb-10.2.15

??

Check the configuration environment of this machine

??
Close SELinux, clear iptables rule

    setenforce 0    iptables -F    iptables -X

??

Get a binary package

??
On the official download page https://downloads.mariadb.org/mariadb/10.2.15/#
Below to find the following package (here is a 64-bit package) download to local

??

Create user groups and corresponding directories

??
Create users and Groups

useradd -r -d /data/mysqldb -s /sbin/nologin mysql

Create a Directory

mkdir -pv /data/mysqldbchown mysql:mysql /data/mysqldbchmod 770 /data/mysqldb

??

Unzip the Tarball and configure the environment variables

??
Unzip the tarball and specify the directory

tar xvf  mariadb-10.2.15-linux-x86_64.tar.gz  -C /usr/local/

Create a soft connection to upgrade later, modify file permissions

cd /usr/localln -s mariadb-10.2.15-linux-x86_64/ mysqlchown -R root:root mysql/

Configuring Environment variables

echo PATH=/usr/local/mysql/bin:$PATH > /etc/profile.d/mysql.sh#重读环境信息，使变量生效. /etc/profile.d/mysql.sh

??

Preparing the configuration file startup script and initializing the database

??
Prepare the configuration file and modify it to the appropriate parameters

cd /usr/local/mysqlcp support-files/my-huge.cnf  /etc/my.cnf#修改配置文件vim /etc/my.cnf[mysqld]datadir=/data/mysqldb 加此行

Initializing the database

cd /usr/local/mysqlscripts/mysql_install_db  --datadir=/data/mysqldb --user=mysql

Prepare to start the script and start the service

cd /usr/local/mysqlcp support-files/mysql.server  /etc/init.d/mysqldchkconfig --add mysqldservice mysqld start

Do security initialization

Login database Verification After configuration is complete

mysql -u用户 -p密码

??
??

centos7.4 source code Compilation installation mariadb-10.2.15

??

Configure the development environment to pre-install the required packages

??
Install the Development Environment Toolkit

    yum groupinstall "Development Tools" -y

Additional packages required for installation

yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boostdevel  gcc  gcc-c++  cmake libevent-devel gnutls-devel libaio-devel openssl-devel  

??

Create user, directory and get source package

??
Creating Directories and Users

mkdir /data useradd -r –s /bin/false -m –d  /data/mysqldb/  mysql#ps：#   /bin/false是最严格的禁止login选项，一切服务都不能用，而/sbin/nologin只是不允许系统login，可以使用其他ftp等服务

Source package, Official link https://downloads.mariadb.org/mariadb/10.2.15/#

??

Compiling the installation

??
#参考文档mysql, mariadb. When do you get a formal document?
https://dev.mysql.com/doc/
Unzip the source package

tar xvf  mariadb-10.2.12.tar.gz

Enter the directory and specify the compilation parameters

cd mariadb-10.2.15 #mariadb使用的cmake进行编辑安装 cmake . -DCMAKE_INSTALL_PREFIX=/app/mysql -DMYSQL_DATADIR=/data/mysqldb/ -DSYSCONFDIR=/etc -DMYSQL_USER=mysql -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1  -DWITHOUT_MROONGA_STORAGE_ENGINE=1 -DWITH_DEBUG=0 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DENABLED_LOCAL_INFILE=1  -DMYSQL_UNIX_ADDR=/app/mysql/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci  #安装 make &&make install

Detailed parameters

CMake. #安装目录, each person set-dcmake_install_prefix=/app/mysql #数据库目录 according to their preferences,-dmysql_datadir=/data/mysqldb/# Database configuration file directory-dsysconfdir=/etc #指定运行用户-dmysql_user=mysql #WITH_xxx_STORAGE_ENGINE将xxx存储引擎静态编译到服务器中-dwith_innobase_ Storage_engine=1-dwith_archive_storage_engine=1-dwith_blackhole_storage_engine=1-dwith_partition_storage_ engine=1-dwithout_mroonga_storage_engine=1 #是否包含调试支持-dwith_debug=0 \ #是否使用readline捆绑在发行版中的库. This option was removed in MySQL 5.6.5 because readline it is no longer bundled. #这里可以不再指定-dwith_readline=1 #SSL的支持类型 #ssl_type can be one of the following values: #yes: Use the System SSL library (if present), or use a library bundled with the release version. #bundled: Use the SSL library bundled in the release version. This is the default setting. #system: Use the system SSL library. -dwith_ssl=system #某些功能要求服务器与压缩库支持来构建, such as COMPRESS () and uncompress () functions, and compression on the client/server protocol. The with_zlib indicates the source zlib support: #bundled: A library that is bundled with the release version using Zlib. This is the default setting. #system: Use the system zlib library. The-dwith_zlib=system #是否包含libwrap (TCP encapsulation) supports-dwith_libwrap=0 #该选项控制LOCALMySQL客户端库的编译默认 functionality. Therefore, a client that is not explicitly scheduled will disable or enable the feature based on the settings specified at Enabled_local_infilemysql build time. -denabled_local_infile=1 #服务器侦听套接字连接的Unix套接字文件路径. This must be an absolute path name. The default is/tmp/mysql.sock. #该值可以在服务器启动时使用该--socket options are setReset -dmysql_unix_addr=/app/mysql/mysql.sock #服务器字符集. By default, MySQL uses the latin1 (cp1252 Western European) character set. #charset_name可以是一种 binary,armscii8, Ascii,big5, cp1250,cp1251, cp1256,cp1257, cp850,cp852, cp866,cp932, Dec8,eucjpms, euckr,gb2312, Gbk,geostd8, Greek,hebrew, HP8,KEYBCS2, koi8r,koi8u, Latin1,latin2, latin5,latin7, Macce,macroman, Sjis, Swe7, TIS620,UCS2, Ujis,utf8, Utf8mb4,utf16, Utf16le,utf32. The allowed character set is listed as the value charsets_available in the Cmake/character_sets.cmake file. Set #该值可以在服务器启动时使用该 the--character_set_server option. -ddefault_charset=utf8 #服务器默认排序字符集. By default, MySQL uses Latin1_swedish_ci. Use the SHOW collation statement to determine the collation that is available for each character set. Set #该值可以在服务器启动时使用该 the--collation_server option. -ddefault_collation=utf8_general_ci

??

environment variable configuration and startup database

??
Preparing environment variables

 echo ‘PATH=/app/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh     

Generate Database Information

cd /app/mysql/ scripts/mysql_install_db --datadir=/data/mysqldb/ --user=mysql

Build and modify the configuration file

cp  /app/mysql/support-files/my-huge.cnf  /etc/my.cnf vim /etc/my.cnf#指定数据目录[mysqld]datadir=/data/mysqldb

modifying startup scripts

cp /app/mysql/support-files/mysql.server  /etc/init.d/mysqld #修改vim /etc/init.d/mysqld basedir=/app/mysqldatadir=/data/mysqldb

Start the service
Modify/app/mysql Permissions

chown mysql:root /app/mysql

Add a service and start

chkconfig --add mysqld service mysqld start

??
??

MARIADB Implementing multiple Instances

??

Installing MARIADB

??
In order to save the convenience of the direct Yum installed, in fact, no matter how the installation basic steps are similar

    yum install mariadb-server -y

??

Create catalogs to clear environmental impact

??
Turn off SELinux to empty firewall data

    getenforce 0    iptables -F    iptables -X

Create a directory and modify permissions

mkdir /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data} -pvchown -R mysql.mysql /mysqldb/

??

Create DATABASE Information

??
Create your own database information

mysql_install_db --datadir=/mysqldb/3306/data --user=mysqlmysql_install_db --datadir=/mysqldb/3307/data --user=mysqlmysql_install_db --datadir=/mysqldb/3308/data --user=mysq

??

modifying configuration files and startup scripts

??
Since it is a Yum-installed database, the configuration file has been generated, and only a few modifications are required.

Modify the configuration file for 3306

cp /etc/my.cnf /mysqldb/3306/etc/#修改以下信息vim /mysqldb/3306/etc/my.cnf [mysqld]port=3306datadir=/mysqldb/3306/datasocket=/mysqldb/3306/socket/mysql.sock[mysqld_safe]log-error=/mysqldb/3306/log/mariadb.logpid-file=/mysqldb/3306/pid/mariadb.pid#!includedir /etc/my.cnf.d

Copy the configuration files for 3306 instances to 3307 and 3308, and modify the

cp /mysqldb/3306/etc/my.cnf  /mysqldb/3307/etc/my.cnfcp /mysqldb/3306/etc/my.cnf  /mysqldb/3308/etc/my.cnfvim /mysqldb/3308/etc/my.cnfvim /mysqldb/3307/etc/my.cnf

Write the startup script and copy to the corresponding path
Sample Script

  #!/bin/bashport=3306mysql_user= "root" mysql_pwd= "" cmd_path= "/usr/bin" mysql_basedir= "/mysqldb" Mysql_ sock= "${mysql_basedir}/${port}/socket/mysql.sock" Function_start_mysql () {if [!-e "$mysql _sock"];then printf "St  Arting mysql...\n "${cmd_path}/mysqld_safe--defaults-file=${mysql_basedir}/${port}/etc/my.cnf &>/dev/null        & Else printf "MySQL is running...\n" Exit Fi}function_stop_mysql () {if [!-e "$mysql _sock"];then  printf "MySQL is stopped...\n" exit else printf "stoping mysql...\n" ${cmd_path}/mysqladmin-u    ${mysql_user}-p${mysql_pwd}-S ${mysql_sock} shutdown fi}function_restart_mysql () {printf "Restarting mysql...\n" Function_stop_mysql Sleep 2 function_start_mysql}case $ instart) function_start_mysql;; stop) Function_stop_mysql;; restart) Function_restart_mysql;; *) printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n" Esac  

Copy to the corresponding directory

cp mysqld /mysqldb/3306/cp mysqld /mysqldb/3307/cp mysqld /mysqldb/3308/

Modify Permissions

chmod 700 /mysqldb/3308/mysqldchmod 700 /mysqldb/3307/mysqldchmod 700 /mysqldb/3306/mysqld

Modify the corresponding information

vim  /mysqldb/3306/mysqldvim  /mysqldb/3307/mysqldvim  /mysqldb/3308/mysqld

??

Start the service and connect to the database

??
Start the service

/mysqldb/3308/mysqld start  /mysqldb/3307/mysqld start  

Connecting to a database

mysql  -S /mysqldb/3308/socket/mysql.sock mysql  -S /mysqldb/3307/socket/mysql.sock mysql  -S /mysqldb/3306/socket/mysql.sock

Verify that the login is on a different port

#在mariadb内输入show variables like ‘%port%‘;

mariadb--multiple-mode deployment and multi-instance deployment