在一臺伺服器上開兩個埠的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)