mysql主库从库在同一台服务器_一台服务器多实例 MySQL 做主从复制

1)安装mysql(安装过程这里就不做过多介绍)

参考：http://www.cnblogs.com/kevingrace/p/6109679.html

本文在一台服务器上做主从实验

主库：172.29.16.24：3306

从库：172.29.16.24：3307

主从库的安装目录分别为/usr/local/mysql3306、/usr/local/mysql3307

主从库的数据目录分别为/data/mysql3306、/data/mysql3307

主从库的登录密码都为123456

两个实例的my.cnf里不一样的配置部分

端口不一样！另外：server-id一定不能一样，最好用端口号来标明server-id，一个是3306，一个是3307

其他内容配置一样，主库一定要开启binlog日志功能，从库可以开启，也可以不开启)

[root@radius01 ~]# cat /usr/local/mysql3306/my.cnf |grep mysql3306

socket = /usr/local/mysql3306/var/mysql.sock

socket = /usr/local/mysql3306/var/mysql.sock

basedir = /usr/local/mysql3306/

datadir = /data/mysql3306/data

pid-file = /data/mysql3306/data/mysql.pid

log_error = /data/mysql3306/data/mysql-error.log

slow_query_log_file = /data/mysql3306/data/mysql-slow.log

[root@radius01 ~]# cat /usr/local/mysql3307/my.cnf |grep mysql3307

socket = /usr/local/mysql3307/var/mysql.sock

socket = /usr/local/mysql3307/var/mysql.sock

basedir = /usr/local/mysql3307/

datadir = /data/mysql3307/data

pid-file = /data/mysql3307/data/mysql.pid

log_error = /data/mysql3307/data/mysql-error.log

slow_query_log_file = /data/mysql3307/data/mysql-slow.log

注意分别授权

[root@radius01 ~]# chown -R mysql.mysql /usr/local/mysql3306

[root@radius01 ~]# chown -R mysql.mysql /usr/local/mysql3307

[root@radius01 ~]# chown -R mysql.mysql /data/mysql3306/

[root@radius01 ~]# chown -R mysql.mysql /data/mysql3307

启动主从库

[root@radius01 ~]# nohup /usr/local/mysql3306/bin/mysqld_safe --datadir=/data/mysql3306/data --pid-file=/data/mysql3306/data/mysql.pid &

[root@radius01 ~]# nohup /usr/local/mysql3307/bin/mysqld_safe --datadir=/data/mysql3307/data --pid-file=/data/mysql3307/data/mysql.pid &

[root@radius01 ~]# lsof -i:3306

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

mysqld 28617 mysql 19u IPv4 838860 0t0 TCP *:mysql (LISTEN)

[root@radius01 ~]# lsof -i:3307

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

mysqld 29538 mysql 19u IPv4 839401 0t0 TCP *:opsession-prxy (LISTEN)

注意，当一台服务器上安装了多个实例的mysql的时候，直接登录mysql的时候，记住要在登录命令中跟上对应的sock路径，如下：

[root@radius01 ~]# /usr/local/mysql3306/bin/mysql -p123456 -S /usr/local/mysql3306/var/mysql.sock

......

mysql> select @@port;

+--------+

| @@port |

+--------+

| 3306 |

+--------+

1 row in set (0.00 sec)

[root@radius01 ~]# /usr/local/mysql3307/bin/mysql -p123456 -S /usr/local/mysql3307/var/mysql.sock

......

mysql> select @@port;

+--------+

| @@port |

+--------+

| 3307 |

+--------+

1 row in set (0.00 sec)

------------------------------------------------------------------------------------------------------------------------

如果不跟上对应的sock路径，直接登录的话会报错：

[root@radius01 ~]# /usr/local/mysql3306/bin/mysql -p123456

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

这样，如果你如下做软链接：

[root@radius01 ~]# ln -s /usr/local/mysql3306/var/mysql.sock /var/lib/mysql/mysql.sock

或者

[root@radius01 ~]# ln -s /usr/local/mysql3307/var/mysql.sock /var/lib/mysql/mysql.sock

那么这样操作之后，无论是登录3306端口的mysql，还是登录3307端口的mysql，里面的操作都是一样的，即这样就分不清两个端口的mysql实例了！

所以还是在登录各个端口的mysql实例时要跟上对应的sock路径！

2)部署主从复制环境

先在主库上操作：

[root@radius01 ~]# /usr/local/mysql3306/bin/mysql -p123456 -S /usr/local/mysql3306/var/mysql.sock

......

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.29.16.%' IDENTIFIED BY 'mycatms';

mysql> flush privileges;

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000015 | 199 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

接着在从库(即33077端口)上设置主从复制

先在从库上验证下是否能使用授予的权限连接主库

[root@radius01 ~]# /usr/local/mysql3307/bin/mysql -u repl -h 172.29.16.24 -pmycatms -S /usr/local/mysql3306/var/mysql.sock

.....

mysql> select @@port;

+--------+

| @@port |

+--------+

| 3306 |

+--------+

1 row in set (0.00 sec)

然后进行主从复制设置

[root@radius01 ~]# /usr/local/mysql3307/bin/mysql -p123456 -S /usr/local/mysql3307/var/mysql.sock

.......

mysql> select @@port;

+--------+

| @@port |

+--------+

| 3307 |

+--------+

1 row in set (0.00 sec)

mysql> stop slave;

mysql> reset slave;

mysql> change master to master_user='repl', master_password='mycatms', master_host='172.29.16.24',master_port=3306, master_log_file='mysql-bin.000015',master_log_pos=199;

mysql> start slave;

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.29.16.24

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000015

Read_Master_Log_Pos: 199

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000015

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

.......

.......

-------------------------------------------------------------------------------------------------------------------------------------

如果出现下面报错：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

原因分析：

mysql 5.6的复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，但是查看到直接copy data文件夹后server_uuid是相同的，show variables like '%server_uuid%';

也就是说：

我的实验环境中的3306端口的mysql实例和3307端口的mysql实例的uuid是一样的导致的，因为我是直接复制的data数据目录。如下查看：

[root@radius01 ~]# /usr/local/mysql3306/bin/mysql -p123456 -S /usr/local/mysql3306/var/mysql.sock

.........

mysql> show variables like '%server_uuid%';

+---------------+--------------------------------------+

| Variable_name | Value |

+---------------+--------------------------------------+

| server_uuid | 5509fe69-96b6-11e7-aef5-aaafa07a2e23 |

+---------------+--------------------------------------+

1 row in set (0.00 sec)

[root@radius01 ~]# /usr/local/mysql3307/bin/mysql -p123456 -S /usr/local/mysql3307/var/mysql.sock

......

mysql> show variables like '%server_uuid%';

+---------------+--------------------------------------+

| Variable_name | Value |

+---------------+--------------------------------------+

| server_uuid | 5509fe69-96b6-11e7-aef5-aaafa07a2e23 |

+---------------+--------------------------------------+

1 row in set (0.00 sec)

解决办法：

找到3306端口和3307端口的data文件夹下的auto.cnf文件，直接删除掉，然后重启各自的mysql即可！重启后，可以再次生成auto.conf文件(重新生成后的uuid就不一样了)

[root@radius01 ~]# rm -rf /data/mysql3306/data/auto.cnf

[root@radius01 ~]# rm -rf /data/mysql3307/data/auto.cnf

[root@radius01 ~]# nohup /usr/local/mysql3306/bin/mysqld_safe --datadir=/data/mysql3306/data --pid-file=/data/mysql3306/data/mysql.pid &

[root@radius01 ~]# nohup /usr/local/mysql3307/bin/mysqld_safe --datadir=/data/mysql3307/data --pid-file=/data/mysql3307/data/mysql.pid &

[root@radius01 ~]# cat /data/mysql3306/data/auto.cnf

[auto]

server-uuid=f6a726d2-96fd-11e7-b0c8-aaafa07a2e23

[root@radius01 ~]# cat /data/mysql3307/data/auto.cnf

[auto]

server-uuid=fc6ee68c-96fd-11e7-b0c8-aaafa07a2e23

-------------------------------------------------------------------------------------------------------------------------------------

数据同步测试：

在主库里写入新数据

[root@radius01 ~]# /usr/local/mysql3306/bin/mysql -p123456 -S /usr/local/mysql3306/var/mysql.sock

mysql> create database wangshibo;

mysql> use wangshibo;

mysql> create table tehui(

-> id int not null primary key,

-> name varchar(10));

mysql> insert into tehui values(1,"huanhuan");

mysql> insert into tehui values(11,"meimei");

mysql> select * from tehui;

+----+----------+

| id | name |

+----+----------+

| 1 | huanhuan |

| 11 | meimei |

+----+----------+

2 rows in set (0.00 sec)

在从库查看是否已同步

[root@radius01 ~]# /usr/local/mysql3307/bin/mysql -p123456 -S /usr/local/mysql3307/var/mysql.sock

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

| wangshibo |

+--------------------+

5 rows in set (0.00 sec)

mysql> use wangshibo;

mysql> show tables;

+---------------------+

| Tables_in_wangshibo |

+---------------------+

| tehui |

+---------------------+

1 row in set (0.00 sec)

mysql> select * from tehui;

+----+----------+

| id | name |

+----+----------+

| 1 | huanhuan |

| 11 | meimei |

+----+----------+

2 rows in set (0.00 sec)