MYSQL主从复制配置（整理）
mysql主从原理及过程
原理
mysql的 replication 是一个异步的复制过程（mysql5.1.7以上版本分为异步复制和半同步两种模式），从一个 mysql instace(我们称之为 master)复制到另一个 mysql instance(我们称之 slave)。在 master 与 slave 之间的实现整个复制过程主要由三个线程来完成，其中两个线程(sql线程和io线程)在 slave 端，另外一个线程(io线程)在 master 端。
要实现 mysql 的 replication ，首先必须打开 master 端的binary log(mysql-bin.xxxxxx)功能，否则无法实现。因为整个复制过程实际上就是slave从master端获取该日志然后再在自己身上完全 顺序的执行日志中所记录的各种操作。打开 mysql 的 binary log 可以通过在启动 mysql server 的过程中使用 “—log-bin” 参数选项，或者在配置文件中的 mysqld 参数组([mysqld]标识后的参数部分)增加 “log-bin” 参数项。
基本过程
slave 上面的io线程连接上 master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容；
master 接收到来自 slave 的 io 线程的请求后，通过负责复制的 io 线程根据请求信息读取指定日志指定位置之后的日志信息，返回给 slave 端的 io 线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在 master 端的 binary log 文件的名称以及在 binary log 中的位置；
slave 的 io 线程接收到信息后，将接收到的日志内容依次写入到 slave 端的relay log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的master端的bin-log的文件名和位置记录到master- info文件中，以便在下一次读取的时候能够清楚的告诉master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”
slave 的 sql 线程检测到 relay log 中新增加了内容后，会马上解析该 log 文件中的内容成为在 master 端真实执行时候的那些可执行的 query 语句，并在自身执行这些 query。这样，实际上就是在 master 端和 slave 端执行了同样的 query，所以两端的数据是完全一样的。
mysql复制的几种模式
.从 mysql 5.1.12 开始，可以用以下三种模式来实现：
– 基于sql语句的复制(statement-based replication, sbr)，
– 基于行的复制(row-based replication, rbr)，
– 混合模式复制(mixed-based replication, mbr)
相应地，binlog的格式也有三种：statement，row，mixed。 mbr 模式中，sbr 模式是默认的。
设定主从复制模式：
log-bin=mysql-bin #binlog_format="statement" #binlog_format="row" binlog_format="mixed"
也可以在运行时动态修改binlog的格式。例如
mysql> set session binlog_format = 'statement'; mysql> set session binlog_format = 'row'; mysql> set session binlog_format = 'mixed'; mysql> set global binlog_format = 'statement';
mysql主从复制配置
版本：mysql5.7 centos7.2
场景描述：
主数据库服务器：192.168.206.100，mysql已经安装，并且无应用数据。
从数据库服务器：192.168.206.200，mysql已经安装，并且无应用数据。
1 主服务器上进行的操作
启动mysql服务
service mysqld start
通过命令行登录管理mysql服务器
mysql -uroot -p'new-password'
授权复制权限给从数据库服务器192.168.206.200
mysql> grant replication slave on *.* to 'rep1'@'192.168.206.200' identified by ‘password’;
查询主数据库状态
配置从服务器时会用到
mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| file| position | binlog_do_db | binlog_ignore_db | executed_gtid_set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-master-bin.000001 | 154 | | | |
+-------------------------+----------+--------------+------------------+-------------------+
这里需要注意一点，若查询时返回的是
mysql> show slave status; empty set (0.01 sec)
这是因为没有开启bin-log造成的，需要去修改/etc/my.cnf文件
server-id =1 log-bin=mysql-master-bin
修改文件时还需要注意一点，mysql5.7之后，开启binlog时还需要同时指定server-id，否则会报错
2 配置从服务器
修改从服务器的配置文件/opt/mysql/etc/my.cnf
将 server-id = 1修改为 server-id = 2，并确保这个id没有被别的mysql服务所使用。
启动mysql服务
service mysqld start
登录管理mysql服务器
mysql -uroot -p'new-password'
执行同步sql语句
change master to master_host='192.168.206.100', master_user='root', master_password='xu261220..', master_log_file='mysql-master-bin.000001', master_log_pos=154;
正确执行后启动slave同步进程
mysql> start slave;
注意，这里又有一个坑了
即使启动start slave成功了，主从复制任然是失败的
1、错误消息 mysql> show slave staus; last_io_error: 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. 2、查看主从的server_id变量 master_mysql> show variables like 'server_id'; +---------------+-------+ | variable_name | value | +---------------+-------+ | server_id | 33| +---------------+-------+ slave_mysql> show variables like 'server_id'; +---------------+-------+ | variable_name | value | +---------------+-------+ | server_id | 11| +---------------+-------+ -- 从上面的情形可知，主从mysql已经使用了不同的server_id 3、解决故障 ###查看auto.cnf文件 [root@dbsrv1 ~] cat /data/mysqldata/auto.cnf ### 主上的uuid [auto] server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026 [root@dbsrv2 ~]# more /data/mysqldata/auto.cnf ###从上的uuid，果然出现了重复，原因是克隆了虚拟机，只改server_id不行 [auto] server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026 [root@dbsrv2 ~]# mv /data/mysqldata/auto.cnf /data/mysqldata/auto.cnf.bk ###重命名该文件 [root@dbsrv2 ~]# service mysql restart ###重启mysql shutting down mysql.[ ok ] starting mysql.[ ok ] [root@dbsrv2 ~]# more /data/mysqldata/auto.cnf ###重启后自动生成新的auto.cnf文件，即新的uuid [auto] server-uuid=6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9 ###再次查看slave的状态已经正常 [root@dbsrv1 ~]# mysql -uroot -pxxx -e "show slave status\g"|grep running warning: using a password on the command line interface can be insecure. slave_io_running: yes slave_sql_running: yes slave_sql_running_state: slave has read all relay log; waiting for the slave i/o thread to update it ###主库端查看自身的uuid master_mysql> show variables like 'server_uuid'; +---------------+--------------------------------------+ | variable_name | value| +---------------+--------------------------------------+ | server_uuid | 62ee10aa-b1f7-11e4-90ae-080027615026 | +---------------+--------------------------------------+ 1 row in set (0.00 sec) ###主库端查看从库的uuid master_mysql> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | server_id | host | port | master_id | slave_uuid | +-----------+------+------+-----------+--------------------------------------+ |33 | | 3306 |11 | 62ee10aa-b1f7-11e4-90ae-080027615030 | |22 | | 3306 |11 | 6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9 | +-----------+------+------+-----------+--------------------------------------+
参考：
其中slave_io_running 与 slave_sql_running 的值都必须为yes，才表明状态正常。
如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：
(1)主数据库进行锁表操作，不让数据再进行写入动作
mysql> flush tables with read lock;
(2)查看主数据库状态
mysql> show master status;
(3)记录下 file 及 position 的值。
将主服务器的数据文件（整个/opt/mysql/data目录）复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。
(4)取消主数据库锁定
mysql> unlock tables;
3 验证主从复制效果
在主服务器上创建数据库first_db
mysql> create database first_db; query ok, 1 row affected (0.01 sec)
在主服务器上创建表first_tb
mysql> create table first_tb(id int(3),name char(10)); query ok, 1 row affected (0.00 sec)
在主服务器上的表first_tb中插入记录
mysql> insert into first_tb values (001,“myself”); query ok, 1 row affected (0.00 sec)
在从服务器上查看
mysql> show databases;