MySQL的多實體
目錄- MySQL的多實體
- 實驗準備：
- 準備階段：
- 實驗階段
實驗準備：
1. 一個干凈的centos7系統
2. 關閉防火墻和selinux
3. 之前已經二進制安裝過的MySQL資料庫
4. 準備檔案夾
準備階段：
- 準備檔案目錄
[root@centos7 data]#mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}
mkdir: created directory ‘/mysql’
mkdir: created directory ‘/mysql/3306’
mkdir: created directory ‘/mysql/3306/data’
mkdir: created directory ‘/mysql/3306/etc’
mkdir: created directory ‘/mysql/3306/socket’
mkdir: created directory ‘/mysql/3306/log’
mkdir: created directory ‘/mysql/3306/bin’
mkdir: created directory ‘/mysql/3306/pid’
mkdir: created directory ‘/mysql/3307’
mkdir: created directory ‘/mysql/3307/data’
mkdir: created directory ‘/mysql/3307/etc’
mkdir: created directory ‘/mysql/3307/socket’
mkdir: created directory ‘/mysql/3307/log’
mkdir: created directory ‘/mysql/3307/bin’
mkdir: created directory ‘/mysql/3307/pid’
mkdir: created directory ‘/mysql/3308’
mkdir: created directory ‘/mysql/3308/data’
mkdir: created directory ‘/mysql/3308/etc’
mkdir: created directory ‘/mysql/3308/socket’
mkdir: created directory ‘/mysql/3308/log’
mkdir: created directory ‘/mysql/3308/bin’
mkdir: created directory ‘/mysql/3308/pid’
(生成之后的效果)
[root@centos7 data]#tree /data
/data
├── mariadb-10.2.29-linux-x86_64.tar.gz
├── mariadb-install.sh
├── my.cnf
└── mysql
├── aria_log.00000001
├── aria_log_control
├── centos7.localdomain.pid
├── ib_buffer_pool
├── ibdata1
├── ib_logfile0
├── ib_logfile1
├── ibtmp1
├── multi-master.info
├── mysql
│ ├── columns_priv.frm
│ ├── columns_priv.MYD
│ ├── columns_priv.MYI
│ ├── column_stats.frm
│ ├── column_stats.MYD
│ ├── column_stats.MYI
│ ├── db.frm
│ ├── db.MYD
│ ├── db.MYI
│ ├── db.opt
│ ├── event.frm
│ ├── event.MYD
│ ├── event.MYI
│ ├── func.frm
│ ├── func.MYD
│ ├── func.MYI
│ ├── general_log.CSM
│ ├── general_log.CSV
│ ├── general_log.frm
│ ├── gtid_slave_pos.frm
│ ├── gtid_slave_pos.ibd
│ ├── help_category.frm
│ ├── help_category.MYD
│ ├── help_category.MYI
│ ├── help_keyword.frm
│ ├── help_keyword.MYD
│ ├── help_keyword.MYI
│ ├── help_relation.frm
│ ├── help_relation.MYD
│ ├── help_relation.MYI
│ ├── help_topic.frm
│ ├── help_topic.MYD
│ ├── help_topic.MYI
│ ├── host.frm
│ ├── host.MYD
│ ├── host.MYI
│ ├── index_stats.frm
│ ├── index_stats.MYD
│ ├── index_stats.MYI
│ ├── innodb_index_stats.frm
│ ├── innodb_index_stats.ibd
│ ├── innodb_table_stats.frm
│ ├── innodb_table_stats.ibd
│ ├── plugin.frm
│ ├── plugin.MYD
│ ├── plugin.MYI
│ ├── proc.frm
│ ├── proc.MYD
│ ├── proc.MYI
│ ├── procs_priv.frm
│ ├── procs_priv.MYD
│ ├── procs_priv.MYI
│ ├── proxies_priv.frm
│ ├── proxies_priv.MYD
│ ├── proxies_priv.MYI
│ ├── roles_mapping.frm
│ ├── roles_mapping.MYD
│ ├── roles_mapping.MYI
│ ├── servers.frm
│ ├── servers.MYD
│ ├── servers.MYI
│ ├── slow_log.CSM
│ ├── slow_log.CSV
│ ├── slow_log.frm
│ ├── tables_priv.frm
│ ├── tables_priv.MYD
│ ├── tables_priv.MYI
│ ├── table_stats.frm
│ ├── table_stats.MYD
│ ├── table_stats.MYI
│ ├── time_zone.frm
│ ├── time_zone_leap_second.frm
│ ├── time_zone_leap_second.MYD
│ ├── time_zone_leap_second.MYI
│ ├── time_zone.MYD
│ ├── time_zone.MYI
│ ├── time_zone_name.frm
│ ├── time_zone_name.MYD
│ ├── time_zone_name.MYI
│ ├── time_zone_transition.frm
│ ├── time_zone_transition.MYD
│ ├── time_zone_transition.MYI
│ ├── time_zone_transition_type.frm
│ ├── time_zone_transition_type.MYD
│ ├── time_zone_transition_type.MYI
│ ├── user.frm
│ ├── user.MYD
│ └── user.MYI
├── performance_schema
│ └── db.opt
├── tc.log
└── test
└── db.opt
4 directories, 103 files
- 更改所有者所屬組
[root@centos7 data]#id mysql (因為之前已經安裝過MySQL資料庫所以有這個用戶)
uid=987(mysql) gid=981(mysql) groups=981(mysql)
[root@centos7 data]#chown -R mysql.mysql /mysql/
[root@centos7 data]#ll mysql/
total 122936
-rw-rw---- 1 mysql mysql 16384 Nov 19 18:06 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Nov 19 18:06 aria_log_control
-rw-rw---- 1 mysql mysql 5 Nov 19 18:06 centos7.localdomain.pid
-rw-rw---- 1 mysql mysql 938 Nov 19 18:06 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 Nov 19 18:06 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Nov 19 18:06 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Nov 19 18:06 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 Nov 19 18:06 ibtmp1
-rw-rw---- 1 mysql mysql 0 Nov 19 18:06 multi-master.info
drwx------ 2 mysql mysql 4096 Nov 19 18:06 mysql
drwx------ 2 mysql mysql 20 Nov 19 18:06 performance_schema
-rw-rw---- 1 mysql mysql 24576 Nov 19 18:06 tc.log
drwx------ 2 mysql mysql 20 Nov 19 18:06 test
實驗階段
- 分別創建資料庫并確認資料庫檔案都生成了，再查看所有者所屬組是否正確，
143 /usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3306/data --user=mysql
144 /usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3307/data --user=mysql
145 /usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3308/data --user=mysql
[root@centos7 data]#ll /mysql/3307
total 0
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 bin
drwxr-xr-x 5 mysql mysql 181 Nov 19 18:39 data
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 etc
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 log
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 pid
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 socket
[root@centos7 data]#ll /mysql/3308
total 0
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 bin
drwxr-xr-x 5 mysql mysql 181 Nov 19 18:39 data
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 etc
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 log
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 pid
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 socket
[root@centos7 data]#ll /mysql/3306
total 0
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 bin
drwxr-xr-x 5 mysql mysql 181 Nov 19 18:39 data
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 etc
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 log
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 pid
drwxr-xr-x 2 mysql mysql 6 Nov 19 18:25 socket
[root@centos7 data]#ll /mysql/3306/data/
total 110620
-rw-rw---- 1 mysql mysql 16384 Nov 19 18:39 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Nov 19 18:39 aria_log_control
-rw-rw---- 1 mysql mysql 938 Nov 19 18:39 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 Nov 19 18:39 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Nov 19 18:39 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Nov 19 18:39 ib_logfile1
drwx------ 2 mysql mysql 4096 Nov 19 18:39 mysql
drwx------ 2 mysql mysql 20 Nov 19 18:39 performance_schema
drwx------ 2 mysql mysql 20 Nov 19 18:39 test
- 準備組態檔
[root@centos7 data]#cp /etc/my.cnf /mysql/3306/etc/
[root@centos7 data]#vim /mysql/3306/etc/my.cnf (根據自己規劃的路徑更改)
[mysqld]
port=3306
socket=/mysql/3306/socket/mysqld.sock
datadir=/mysql/3306/data
innodb_file_per_table=1
[mysqld_safe]
log-error=/mysql/3306/log/mysqld.log
pid-file=/mysql/3306/pid/mysqld.pid
~
（準備另外兩個資料庫的組態檔）
[root@centos7 data]#cp /mysql/3306/etc/my.cnf /mysql/3307/etc/
[root@centos7 data]#cp /mysql/3306/etc/my.cnf /mysql/3308/etc/
（使用sed替換）
[root@centos7 data]#sed -i 's/3306/3307/' /mysql/3307/etc/my.cnf
[root@centos7 data]#sed -i 's/3306/3308/' /mysql/3308/etc/my.cnf
（確認已經替換掉）
[root@centos7 data]#cat /mysql/3308/etc/my.cnf
[mysqld]
port=3308
socket=/mysql/3308/socket/mysqld.sock
datadir=/mysql/3308/data
innodb_file_per_table=1
[mysqld_safe]
log-error=/mysql/3308/log/mysqld.log
pid-file=/mysql/3308/pid/mysqld.pid
[root@centos7 data]#cat /mysql/3307/etc/my.cnf
[mysqld]
port=3307
socket=/mysql/3307/socket/mysqld.sock
datadir=/mysql/3307/data
innodb_file_per_table=1
[mysqld_safe]
log-error=/mysql/3307/log/mysqld.log
pid-file=/mysql/3307/pid/mysqld.pid
- 設定服務啟動腳本（只有多實體安裝需要自己手寫腳本其他安裝都是自動生成的）
[root@centos7 data]#cd /mysql/3306/bin/
[root@centos7 bin]#ls
[root@centos7 bin]#vim mysqld
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd="" （這個就為空就行）
cmd_path="/usr/local/mysql/bin" （這個路徑按你自己的配置更改）
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysqld.sock" （這個之前改過名就在mysql后面加了個d）
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting 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 $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac
[root@centos7 bin]#chmod +x mysqld （加上執行權限）
[root@centos7 bin]#pwd
/mysql/3306/bin
（在這一步的時候可以先試啟動一下如果沒問題就把剩下兩個啟動腳本一起設定好）
- 試啟動3306埠的資料庫
[root@centos7 bin]#ss -ntl (確定3306埠沒有占用，這里是因為我之前二進制安裝的資料庫占用了關掉就好了)
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:6000 *:*
LISTEN 0 5 192.168.122.1:53 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 127.0.0.1:6010 *:*
LISTEN 0 128 127.0.0.1:6011 *:*
LISTEN 0 80 :::3306 （這里埠還開著） :::*
LISTEN 0 128 :::111 :::*
LISTEN 0 128 :::6000 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 128 ::1:631 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 128 ::1:6010 :::*
LISTEN 0 128 ::1:6011 :::*
[root@centos7 bin]#service mysqld stop （關閉資料庫）
Stopping mysqld (via systemctl): [ OK ]
[root@centos7 bin]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:6000 *:*
LISTEN 0 5 192.168.122.1:53 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 127.0.0.1:6010 *:*
LISTEN 0 128 127.0.0.1:6011 *:*
LISTEN 0 128 :::111 :::*
LISTEN 0 128 :::6000 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 128 ::1:631 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 128 ::1:6010 :::*
LISTEN 0 128 ::1:6011 :::*
[root@centos7 bin]#./mysqld （以為是自己寫的啟動腳本支持基本的三個引數）
Usage: /mysql/3306/bin/mysqld {start|stop|restart}
[root@centos7 bin]#./mysqld start
Starting MySQL...
[root@centos7 bin]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:6000 *:*
LISTEN 0 5 192.168.122.1:53 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 127.0.0.1:6010 *:*
LISTEN 0 128 127.0.0.1:6011 *:*
LISTEN 0 80 :::3306 （啟動成功） :::*
LISTEN 0 128 :::111 :::*
LISTEN 0 128 :::6000 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 128 ::1:631 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 128 ::1:6010 :::*
LISTEN 0 128 ::1:6011 :::*
[root@centos7 bin]#mysql -S /mysql/3306/socket/mysqld.sock （這里要登陸的話可以指定socket檔案路徑進行登錄）
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.29-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases; （查看資料庫）
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> create database db3306; （建立一個資料庫）
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@centos7 bin]#cd ..
[root@centos7 3306]#ls
bin data etc log pid socket
[root@centos7 3306]#ls data/
aria_log.00000001 ib_buffer_pool ib_logfile1 mysql test
aria_log_control ibdata1 ibtmp1 performance_schema
db3306 ib_logfile0 multi-master.info tc.log
（建立的資料庫就是生成一個新的檔案夾）
（到這里說明已經成功了，可以把之前沒做完的3307，3308做完）
- 繼續設定另外兩個啟動腳本
[root@centos7 3306]#cp bin/mysqld /mysql/3307/bin/
[root@centos7 3306]#cp bin/mysqld /mysql/3308/bin/ (把腳本拷貝過去)
[root@centos7 3306]#sed -i 's/3306/3307/' /mysql/3307/bin/mysqld (sed替換)
[root@centos7 3306]#sed -i 's/3306/3308/' /mysql/3308/bin/mysqld
[root@centos7 3306]#cat /mysql/3307/bin/mysqld （確認替換掉了）
#!/bin/bash
port=3307
.
.
.
.
[root@centos7 3306]#cat /mysql/3308/bin/mysqld
#!/bin/bash
port=3308
.
.
.
.
- 啟動所有腳本
[root@centos7 3306]#/mysql/3307/bin/mysqld start （3306也可以這樣啟動）
Starting MySQL...
[root@centos7 3306]#/mysql/3308/bin/mysqld start
Starting MySQL...
[root@centos7 3306]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:6000 *:*
LISTEN 0 5 192.168.122.1:53 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 127.0.0.1:6010 *:*
LISTEN 0 128 127.0.0.1:6011 *:*
LISTEN 0 80 :::3306 :::*
LISTEN 0 80 :::3307 (成功) :::*
LISTEN 0 80 :::3308 :::*
LISTEN 0 128 :::111 :::*
LISTEN 0 128 :::6000 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 128 ::1:631 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 128 ::1:6010 :::*
LISTEN 0 128 ::1:6011 :::*
（記得添加一下PATH變數要不使用命令工具很麻煩）
[root@centos7 mysql]#echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@centos7 mysql]#. /etc/profile.d/mysql.sh
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/154775.html
標籤：Linux
上一篇：二進制安裝MySQL資料庫