共計 15362 個字符，預計需要花費 39 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 Linux 下 Mysql 如何安裝多實例和主從配置，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！
mysql 創(chuàng)建多個實例，其實就是把 DATA 文件，SOCK,PORT 指向不同的文件和端口
Linux 下搭建 MySQL 多實例環(huán)境
1. 安裝 cmake
[root@mysql local]# yum -y install ncurses-devel gcc-c++
[root@mysql local]# cd /usr/local
[root@mysql local]# tar zxvf cmake-2.8.4.tar.gz
[root@mysql local]# cd cmake-2.8.4
[root@mysql cmake-2.8.4]# ./bootstrap – 出現如下報錯，缺少 c ++ 編譯器
[root@mysql local]# yum install gcc-c++ – 可處理下面問題
[root@mysql cmake-2.8.4]# gmake
[root@mysql cmake-2.8.4]# make install
[root@mysql cmake-2.8.4]# /usr/local
2. 安裝 bison
[root@mysql local]# tar -zxvf bison-2.5.tar.gz
[root@mysql local]# cd bison-2.5
[root@mysql bison-2.5]# ./configure
[root@mysql bison-2.5]# make
[root@mysql bison-2.5]# make install
[root@mysql bison-2.5]# /usr/local
3. 編譯安裝 mysql 包
[root@localhost workspace]# tar xvf mysql-5.5.32.tar.gz
[root@localhost workspace]# cd mysql-5.5.32
安裝第一個 MySQL 數據庫
（1）創(chuàng)建所需要的文件目錄
[root@localhost mysql]# useradd mysql
[root@localhost local]# cd /usr/local/
[root@localhost local]# mkdir mysql
[root@localhost local]# cd mysql/
[root@localhost mysql]# mkdir data
[root@localhost mysql]# mkdir etc
[root@localhost mysql]# mkdir log
[root@localhost mysql]# mkdir /var/log/mysql
[root@localhost mysql]# mkdir /var/run/mysqld
[root@localhost mysql]# chown /var/run/mysqld -R
[root@localhost mysql]# chown mysql.mysql /var/log/mysql -R
[root@mysql mysql]# chown mysql.mysql /usr/local/mysql -R
[root@mysql mysql]# chmod +x /usr/local/mysql -R
（2）配置 MySQL 源碼編譯選項
[root@localhost mysql-5.5.32]#cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DSYSCONFDIR=/usr/local/mysql/etc \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
（3）編譯安裝
[root@localhost mysql-5.5.32]# make make install
（4）配置第一個 MySQL 實例
[root@localhost mysql-5.5.32]# cd /usr/local/mysql
[root@localhost mysql]# cp support-files/my-medium.cnf /usr/local/mysql/etc/my.cnf
[root@localhost mysql]# vi /usr/local/mysql/etc/my.cnf
[client]
#password = your_password
port = 3306
socket = /usr/local/mysql/mysqld.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /usr/local/mysql/mysqld.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
datadir=/usr/local/mysql/data
default-storage-engine=Innodb
slow-query-log-file=/usr/local/mysql/log/slow.log
log-error=/usr/local/mysql/log/err.log
pid-file=/usr/local/mysql/mysql3306.pid
server_id=1
character_set_server = utf8
wait-timeout=30
max_connections = 512
log-bin =/usr/local/mysql/log/binlog
sync_binlog=1
slow-query-log=1
long-query-time=1
general-log=1
#general-log-file=/data/mysql/log/dml.log
lower_case_table_names=1
log_bin_trust_function_creators=1
skip-slave-start
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
expire-logs-days=10
[root@localhost mysql]# cd /usr/local/mysql/scripts/
[root@localhost scripts]# ./mysql_install_db –user=mysql –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data
[root@localhost bin]# cd /usr/local/mysql/bin
[root@localhost bin]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/usr/local/mysql/etc/my.cnf – 安全模式啟動
[1] 28869
[root@localhost bin]# 131016 20:07:13 mysqld_safe Logging to /usr/local/mysql/data/localhost.localdomain.err .
131016 20:07:14 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
查看服務是否啟動成功
[root@localhost bin]# netstat -tlnap | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 29147/mysqld
[root@localhost bin]#ps -ef | grep mysql
如若啟動之后的環(huán)境變量與配置文件 my.cnf 的指定量不同，則查看 /etc/ 下是否有 my.cnf 文件存在干擾設置的生效，將 /etc/my.cnf 刪除，重啟數據庫再次查看指定量是否生效。
登錄 MySQL 并修改 root 用戶密碼
[root@localhost bin]# ./mysqladmin -uroot password newpasswd
[root@localhost bin]# ./mysql -uroot -pnewpasswd
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.32-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type help; or \h for help. Type \c to clear the current input statement.
mysql exit
安裝第二個 MySQL 數據庫
（1）清理配置信息
[root@localhost bin]# cd /usr/local/mysql-5.5.32
[root@localhost mysql-5.5.32]# make clean
[root@localhost mysql-5.5.32]# rm -rf CMakeCache.txt
（2）創(chuàng)建所需要的文件目錄
[root@localhost mysql-5.5.32]# cd /usr/local/
[root@localhost local]# mkdir mysql3307
[root@localhost local]# cd mysql3307/
[root@localhost mysql3307]# mkdir data
[root@localhost mysql3307]# mkdir etc
[root@localhost mysql3307]# mkdir log
[root@localhost mysql3307]# chown mysql.mysql /usr/local/mysql3307 -R
（3）配置第二個實例的編譯信息
[root@localhost bin]# cd /usr/local/mysql-5.5.32
[root@mysql mysql-5.5.17]# rm -rf CMakeCache.txt
[root@localhost mysql-5.5.32]# cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql3307 \
-DMYSQL_DATADIR=/usr/local/mysql3307/data \
-DSYSCONFDIR=/usr/local/mysql3307/etc \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DMYSQL_UNIX_ADDR=/tmp/mysqld3307.sock \
-DMYSQL_TCP_PORT=3307 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
（4）編譯安裝
[root@localhost mysql-5.5.32]# make make install
（5）配置第二個 MySQL 實例
[root@localhost mysql-5.5.32]# cd /usr/local/mysql3307
[root@localhost mysql3307]# cp support-files/my-medium.cnf /usr/local/mysql3307/etc/my.cnf
[root@localhost mysql3307]# vi /usr/local/mysql3307/etc/my.cnf
[client]
port = 3307
socket = /usr/local/mysql3307/mysqld3307.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3307
socket = /usr/local/mysql3307/mysqld3307.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
datadir=/usr/local/mysql3307/data
default-storage-engine=Innodb
slow-query-log-file=/usr/local/mysql3307/log/slow.log
log-error=/usr/local/mysql3307/log/err.log
[root@localhost mysql3307]# cd /usr/local/mysql3307/scripts/
[root@localhost scripts]# ./mysql_install_db –user=mysql –basedir=/usr/local/mysql3307 –datadir=/usr/local/mysql3307/data
[root@localhost scripts]# cd /usr/local/mysql3307/bin
[root@localhost bin]# /usr/local/mysql3307/bin/mysqld_safe –defaults-file=/usr/local/mysql3307/etc/my.cnf
131016 20:40:27 mysqld_safe Logging to /usr/local/mysql3307/data/localhost.localdomain.err .
131016 20:40:27 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql3307/data
查看服務是否啟動成功
[root@localhost bin]# netstat -tlnap | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 29147/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 7447/mysqld
登錄 MySQL 并修改 root 用戶密碼
[root@localhost bin]# ./mysqladmin -uroot password eisoo.com123
[root@localhost bin]# ./mysql -uroot -peisoo.com123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.32-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type help; or \h for help. Type \c to clear the current input statement.
mysql exit
增加系統(tǒng)環(huán)境變量
[root@localhost /]# vi /etc/profile
增加一行：export PATH=/usr/local/mysql/bin:/usr/local/mysql3307/bin:$PATH
[root@localhost /]# source /etc/profile
[root@localhost /]# export $PATH
分別啟動不同實例：
[root@localhost /]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/usr/local/mysql/etc/my.cnf
[root@localhost /]# /usr/local/mysql3307/bin/mysqld_safe –defaults-file=/usr/local/mysql3307/etc/my.cnf
分別登陸不同實例：
[root@localhost /]# mysql -uroot -pnewpasswd -S /tmp/mysqld.sock
[root@localhost /]# mysql -uroot -pnewpasswd -S /tmp/mysqld3307.sock
啟動還是太麻煩，可以這樣做：
[root@localhost /]# ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql3306
[root@localhost /]# ln -s /usr/local/mysql3307/support-files/mysql.server /etc/init.d/mysql3307
給 mysql 用戶添加權限：
[root@localhost /]# chmod -R 755 /usr/local/mysql/data
[root@localhost /]# chmod -R 755 /usr/local/mysql3307/data
分別啟動實例對應的服務：
[root@localhost tmp]# service mysql3306 start
Starting MySQL. [確定]
[root@localhost tmp]# service mysql3307 start
Starting MySQL. [確定]
查看服務：
[root@localhost tmp]# netstat -tlnap | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 10759/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 11097/mysqld
將服務添加到系統(tǒng)服務中，使其開機自動啟動。
[root@localhost /]# chkconfig –add mysql3306
[root@localhost /]# chkconfig –add mysql3307
多實例的平滑關閉：
[root@localhost /]# mysqladmin -u root -p -S –socket=/usr/local/mysql/mysqld3306.sock shutdown
[root@localhost /]# mysqladmin -u root -p -S –socket=/usr/local/mysql3307/mysqld3307.sock shutdown
二．mysql 主從配置
1．修改 master 數據庫的 my.cnf 文件
# vi /etc/my.cnf
[mysqld]
basedir =/data/mysql
datadir =/data/mysql/data
port =3306
server_id =1
socket = /data/mysql/mysql.sock
pid-file=/data/mysql/mysql.pid
character_set_server = utf8
wait-timeout=30
max_connections = 512
default-storage-engine = Innodb
log-bin =/data/mysql/log/binlog
sync_binlog=1
slow-query-log=1
long-query-time=1
slow-query-log-file=/data/mysql/log/slow.log
log-error=/data/mysql/log/err.log
general-log=1
general-log-file=/data/mysql/log/dml.log
lower_case_table_names=1
log_bin_trust_function_creators=1
skip-slave-start
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
expire-logs-days=10
[mysql.server]
user=mysql
basedir=/data/mysql
[client]
socket=/data/mysql/mysql.sock
(注意：1. 修改后可以因為文件夾不存在，或者文件夾權限問題無法寫入導致啟動數據庫失敗)
# mkdir /data/mysql/log chown mysql.mysql /data/mysql –R
2. 如果沒有 [client] 條件的話，啟動時會報錯 ERROR 2002 (HY000): Can t connect to local MySQL server through socket /var/lib/mysql/mysql.sock (2))
啟動 master 主庫
# service mysql start
Starting MySQL…… SUCCESS!
2．修改 slave 從庫的配置文件（/etc/my.cnf）
# vi /etc/my.cnf
[mysqld]
basedir =/data/mysql
datadir =/data/mysql/data
port =3306
server_id =3 – 與主庫不同即可
socket = /data/mysql/mysql.sock
pid-file=/data/mysql/mysql.pid
character_set_server = utf8
wait-timeout=30
max_connections = 512
default-storage-engine = Innodb
log-bin =/data/mysql/log/binlog
sync_binlog=1
log-error=/data/mysql/log/err.log
relay-log-index =/data/mysql/relaylog/relaylogindex
relay-log-info-file =/data/mysql/relaylog/relayloginfo
relay-log = /data/mysql/relaylog/relaylog
slow-query-log=1
long-query-time=1
slow-query-log-file=/data/mysql/log/slow.log
log-error=/data/mysql/log/err.log
general-log=1
general-log-file=/data/mysql/log/dml.log
lower_case_table_names=1
log_bin_trust_function_creators=1
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
expire-logs-days=10
read-only
[mysql.server]
user=mysql
basedir=/data/mysql
[client]
socket=/data/mysql/mysql.sock
(注意：1. 修改后可以因為文件夾不存在，或者文件夾權限問題無法寫入導致啟動數據庫失敗
# mkdir /data/mysql/relaylog/ # mkdir /data/mysql/log/ # chown mysql.mysql /data/mysql/ -R
2. 如果沒有 [client] 條件的話，啟動時會報錯 ERROR 2002 (HY000): Can t connect to local MySQL server through socket /var/lib/mysql/mysql.sock (2))
3.master 數據庫鎖表(以便導出庫時無數據操作干擾 獲取二進制坐標)
授權主從復制的用戶‘longshine’登錄密碼為‘longshine’
mysql grant replication slave,replication client on *.* to longshine@ 192.168.81.121 identified by longshine
Query OK, 0 rows affected (0.22 sec)
鎖表便于 down 數據
mysql flush tables with read lock; – 將主庫鎖表，保持主從的一致性
Query OK, 0 rows affected (0.05 sec)
導出數據
# mysqldump -uroot -p –all-databases /home/mysql/all.sql
Enter password: ##—–（輸入密碼）
從庫導入數據
# /usr/local/mysql3307/bin/mysql -uroot -p –socket=/usr/local/mysql3307/mysqld3307.sock /home/mysql/all.sql
Enter password: ##—–（輸入密碼）
查看主庫的二進制日志狀態(tài)
mysql show master status;
mysql show master status\G
*************************** 1. row ***************************
File: binlog.000004
Position: 335
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)
從庫依據主庫的二進制日志狀態(tài)設置與主庫同步
mysql change master to master_host = 192.168.81.14 ,master_port=3306,master_user= longshine ,master_password= longshine ,master_log_file= binlog.000004 ,master_log_pos=335;
4. 解鎖主庫
主庫登錄執(zhí)行
mysql unlock tables;
啟動從庫復制線程
mysql start slave;
Query OK, 0 rows affected (0.01 sec)
查看從庫復制狀態(tài)
mysql show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.81.13
Master_User: longshine
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 120
Relay_Log_File: relaylog.000007
Relay_Log_Pos: 280
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 606
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: d67ab0e7-6044-11e5-8147-000c299db641
Master_Info_File: /data/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
（注：如果顯示 Slave_IO_Running: connecting，首先在主庫查看主從傳輸用戶“l(fā)ongshine”的權限是否出現問題，然后關閉從庫線程，修改主庫用戶權限，重啟數據庫）
重建備庫報錯處理：
160402 9:44:24 [ERROR] Failed to open the relay log ./mysql-relay-bin.000005 (relay_log_pos 1001920)
160402 9:44:24 [ERROR] Could not find target log during relay log initialization
160402 9:45:16 [ERROR] Failed to open the relay log ./mysql-relay-bin.000005 (relay_log_pos 1001920)
160402 9:45:16 [ERROR] Could not find target log during relay log initialization
160402 9:45:58 [ERROR] Failed to open the relay log ./mysql-relay-bin.000005 (relay_log_pos 1001920)
160402 9:45:58 [ERROR] Could not find target log during relay log initialization
分析應該是由于 mysql-relay-bin.index 中仍然保存著舊 relay 日志文件的路徑，而這些路徑下又找不到合適的文件，因此報錯。
對于這類問題解決起來是比較簡單的，重置 slave 的參照即可，執(zhí)行命令如下：
mysql reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql change master to master_host = 192.168.81.121 ,master_port=3306,master_user= longshine ,master_password= longshine ,master_log_file= mysql-bin.000002 ,master_log_pos=9187015;
Query OK, 0 rows affected (0.21 sec)
mysql start slave;
Query OK, 0 rows affected (0.02 sec)
看完了這篇文章，相信你對“Linux 下 Mysql 如何安裝多實例和主從配置”有了一定的了解，如果想了解更多相關知識，歡迎關注丸趣 TV 行業(yè)資訊頻道，感謝各位的閱讀！
