mysql创建多个实例，其实就是把DATA文件，SOCK,PORT指向不同的文件和端口

Linux下搭建MySQL多实例环境

1.安装cmake

[root@mysql local]# yum -y install ncurses-develgcc-c++

[root@mysql local]# cd/usr/local

[root@mysql local]# tarzxvf cmake-2.8.4.tar.gz

[root@mysql local]# cdcmake-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]# makeinstall

[root@mysql cmake-2.8.4]# /usr/local

2.安装bison

[root@mysql local]# tar -zxvf bison-2.5.tar.gz

[root@mysql local]# cdbison-2.5

[root@mysql bison-2.5 ]# ./configure

[root@mysql bison-2.5 ]# make

[root@mysql bison-2.5 ]# makeinstall

[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

tcp0 0 0.0.0.0:33060.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,Oracleand/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

tcp0 0 0.0.0.0:33060.0.0.0:*LISTEN 29147/mysqld

tcp0 0 0.0.0.0:33070.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

tcp0 0 0.0.0.0:33060.0.0.0:*LISTEN 10759/mysqld

tcp0 0 0.0.0.0:33070.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)