Linux下Mysql如何安裝多實例和主從配置

小編給大家分享一下Linux下Mysql如何安裝多實例和主從配置，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

mysql創建多個實例，其實就是把DATA文件，SOCK,PORT指向不同的文件和端口

Linux下搭建MySQL多實例環境

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）創建所需要的文件目錄

[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

如若啟動之后的環境變量與配置文件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）創建所需要的文件目錄

[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

增加系統環境變量

[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

將服務添加到系統服務中，使其開機自動啟動。

[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:                                                  ##-----（輸入密碼）

查看主庫的二進制日志狀態

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)

從庫依據主庫的二進制日志狀態設置與主庫同步

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.解鎖主庫

主庫登錄執行

mysql> unlock tables;

啟動從庫復制線程

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

查看從庫復制狀態

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，首先在主庫查看主從傳輸用戶“longshine”的權限是否出現問題，然后關閉從庫線程，修改主庫用戶權限，重啟數據庫）

重建備庫報錯處理：

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的參照即可，執行命令如下：

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如何安裝多實例和主從配置”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！