在一台服务器上开两个端口的mysql（3306、3307），做成主从复制环境
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)