版本信息：
Ubuntu16.04
mysql-5.7.21
测试环境如下：
master: 192.168.3.12
slave: 192.168.3.11
(一)master配置
1.修改配置文件：
vim /etc/my.cnf
[client]
port=3306
socket=/usr/local/mysql/mysqld.sock
[mysqld]
port=3306
socket=/usr/local/mysql/mysqld.sock
pid-file=/usr/local/mysql/Data/mysql.pid
basedir=/usr/local/mysql
datadir=/usr/local/mysql/Data
log-bin=mysql-bin
server-id=27
2.启动mysql：
root@dss:~# mysqld --user=mysql
root@dss:~# mysql -uroot -p
3.添加同步帐号
1) 配置数据库：
mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'IDENTIFIED BY '123456'; #这里要小心各种符号别写错了！！
2) 使配置生效：
mysql> flush privileges;
mysql> exit
3) 安装插件，并启动半同步复制:
重新进入mysql：
root@dss:~# mysql -uroot -p
4) 安装同步插件:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=ON;
mysql> flush privileges;
5) 查看从库是否开启同步复制:
mysql> show status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
注：在这里 Rpl_semi_sync_master_status 的值是off，是由于还未设置“从”！！
6）查看master状态，slave要用到
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 741 | | | |
+------------------+----------+--------------+------------------+-------------------+
(二)slave配置
注：我是通过ssh来进入了小伙伴的电脑;并获得了小伙伴电脑的root权限！！
不知道为什么，就这样进入别人或者别人进入自己的电脑，就感觉怪怪的！
所以立下flag，要把linux的权限问题搞清楚啊！
1.修改配置文件
1 [client]
2 port=3306
3 socket=/usr/local/mysql/mysqld.sock
4 [mysqld]
5 port=3306
6 socket=/usr/local/mysql/mysqld.sock
7 pid-file=/usr/local/mysql/data/mysql.pid
8 basedir=/usr/local/mysql
9 datadir=/usr/local/mysql/data
10
11 log-bin=mysql-bin
12 server-id=26
2.重启mysql服务
sudo service mysql restart
3.数据库的配置
1）进入数据库
mysql -uroot -p
2）安装同步插件
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=ON;
mysql> flush privileges;
3）连接master
mysql> stop slave;
注：这个命令是需要执行的;不然哼哼，
跳过这个，当然会报错啦！！
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
执行过stop slave，这样子就可以喇嘛！
mysql> change master to master_host='192.168.3.12', #根据master的信息配置！
-> master_user='repl',
-> master_password='123456',
-> master_log_file='mysql-bin.000008',
-> master_log_pos=741;
mysql> flush privileges;
4）启动并查看slave状态
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.12
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 741
Relay_Log_File: dai-ThinkPad-S5-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 741
Relay_Log_Space: 537
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: 27
Master_UUID: 00e2160a-7ba2-11e8-924e-843a4b7d4144
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
BINGO！！
完成了！！
我在master上创建一个数据库：bling
mysql> create database bling;
Query OK, 1 row affected (0.04 sec)
我们查看slave的数据库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bling |
| mysql |
| performance_schema |
| sys |
+--------------------+
完成！
人人为我，我为人人！！
网友评论