本文为小菜上网
整理多个文档外加自己试验
而成，每个地方的
出处已经找不到了，因为找了好多博文，原作者勿怪。小菜先告罪了。。。
mysql创建多个实例，其实就是把DATA文件，SOCK,PORT指向不同的文件和端口
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)
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29029866/viewspace-2114897/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29029866/viewspace-2114897/
318
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
