准备工作
服务器1：IP：192.168.4.58 操作系统CentOS6.8 数据库：MySQL5.6 服务器1：IP：192.168.4.88 操作系统CentOS6.8 数据库：MySQL5.6
主从配置的前提条件
1、MySQL版本一致 2、MySQL中的数据一致 3、操作前停止一切更新操作（写入、更新、删除等）
配置步骤
配置master 1、修改配置文件 vim /etc/my.cnf log-bin=mysql-bin 启用二进制日志，mysql-bin为自己取名字 server-id=58 服务器ID，唯一性，一般是IP地址最后一段 binlog_format=mixed 日志存储格式 2、创建备份专用账户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;
3、查看master主机状态 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 注：File 和Position 值待会儿要用到 4、关闭防火墙 [root@master ~]# service iptables stop 配置 slave 1、修改配置文件 vim /etc/my.cnf log-bin=mysql-bin [可选] 启用二进制日志 relay-log=relay-log 启用中继日志 server-id=88 配置服务器ID 2、配置主从复制 mysql> change master to master_host='192.168.4.58',master_user='backup',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=120;
注： master_host=主服务器IP master_user=在主服务器上创建的备份用户名 master_password=备份用户密码 master_log_file=查询master(主服务器)的状态得到的File列的值 master_log_pos=Position列的值 start slave：启动从服务器复制功能 3、启动从服务器 mysql> start slave; 4、检查从服务器复制功能状态 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.4.58 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 120 Relay_Log_File: relay-log.000004 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000002 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: 120 Relay_Log_Space: 613 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: 58 Master_UUID: ebb71aca-381c-11e8-8b6d-000c29138f00 Master_Info_File: /data/mysql/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 5、设置从服务器为只读 mysql> SHOW GLOBAL VARIABLES LIKE 'READ%'; +----------------------+--------+ | Variable_name | Value | +----------------------+--------+ | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 262144 | +----------------------+--------+ 在slave的mysql配置文件中修改： read-only=on（在从服务器上设定，但对具有SUPER权限的用户不生效）
主要查看以下两项： Slave_IO_Running: Yes Slave_SQL_Running: Yes Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。 以上操作过程，主从服务器配置完成。
主从服务器测试
在主服务器上执行以下操作：
mysql -uroot -ppassword
create database db_test_slave;
use db_test_slave;
create table tb_test(id int(3), name varchar(50));
insert into tb_test values(1,'hello slave');
show databases;
mysql> select * from db_test_slave.tb_test;
在从服务器上执行以下操作： [root@node2 ~]# mysql -uroot -p123456 -e "show databases;use db_test_slave; select * from tb_test;" | Database | +--------------------+ | information_schema | | db_test_slave | | mysql | | performance_schema | | test | +--------------------+ +------+-------------+ | id | name | +------+-------------+ | 1 | hello_slave | +------+-------------+ 看到在主服务器上创建的数据库、数据表、插入的数据记录。
MySQL的半同步复制
1、分别在主从节点上安装相关的插件
master：
安装插件：mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
启动模块：mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
（ rpl_semi_sync_master模块名称，semisync_master.so模块文件）
（取消加载插件mysql> UNINSTALL PLUGIN rpl_semi_sync_master;）
2、查看安装的模块
mysql> SHOW GLOBAL VARIABLES LIKE '%rpl%';
+------------------------------------+----------+
| Variable_name | Value |
+------------------------------------+----------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_stop_slave_timeout | 31536000 |
+------------------------------------+----------+
slave：
安装插件：msyql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
启动模块：mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
重启进程使其模块生效：mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD; （将从线程关掉在启动才可以） mysql> show global variables like "rpl%"; +---------------------------------+----------+ | Variable_name | Value | +---------------------------------+----------+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | | rpl_stop_slave_timeout | 31536000 | +---------------------------------+----------+
