共计 15362 个字符，预计需要花费 39 分钟才能阅读完成。
丸趣 TV 小编给大家分享一下 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 如何安装多实例和主从配置”有了一定的了解，如果想了解更多相关知识，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
