一、mysql主从同步原理

mysql主从是用于数据灾备。也可以缓解服务器压力(读写分离)，即为主数据库服务器增加一个备服务器，
两个服务器之间通过mysql主从复制进行同步，这样一台服务器有问题的情况下可以切换到另一台服务器继续使用。
如何想实现mysql主从自动切换，需要增加高可用，比如MHA

注意：
1、主库挂了，从库可以继续对外提供服务(需要研发把连接主库的ip改为从库)
2、主库起来后主从环境会自动恢复，如果从库写入了数据不会同步到主库，也不会影响主从同步但是会导致主从数据不一致，所以从库最好只读。
3、主从同步是主库往从库单项同步的。
4、如果想把从库升级为主库，需要再配置一遍主从同步的过程并把旧的从库的同步功能关闭stop slave(实测)
5、主从和主备其实是一个东西，唯一区别就是主备环境中备机不对外提供服务只做备份， 主从环境中主挂了备机可以对外提供读或读写服务。
6、当后期出现不同步的时候只需要锁主库并记住master当前pos值，然后从库再执行一遍同步命令再解锁从库即可，实在不行再备份/还原数据。

从库生成两个线程，一个I/O线程，一个SQL线程；
i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；
主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；
SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；

主从同步原理：

主库写入数据到binlog日志，从库通过IO线程将主库的binlog日志读取到从库的中级日志(relaylog)中，从库再通过自己的SQL线程将中级日志中的数据写入到数据库，来完成主从同步。

在从库使用show slave status;查看Read_Master_Log_Pos: 和 Exec_Master_Log_Pos: 的值如果不一样说明数据同步不一致
Seconds_Behind_Master: 0 #和主库比同步延迟的秒数

二、配置mysql主库

1. MySQL主服务器配置

1.编辑配置文件/etc/my.cnf                       #其余参数保持默认
[mysqld]                                        #必须在【mysqld】这个模块下
server-id=1                                     #主从服务器的server-id 不能相同
log-bin=mysql-bin                               #开启二进制同步日志
expire_logs_days = 7                      # binlog保持7天
#binlog-do-db=test2                             #设置需要同步的数据库
binlog-ignore-db=performacen_schema,mysql       #设置不需要同步的数据库
------------------------
mysql5.7.8以后expire_logs_days已经被弃用，改为binlog_expire_logs_seconds = 30240，单位秒

2.建立用户

mysql>  grant replication slave on *.* to rep@'192.168.1.121' identified by 'Clouddeep@8890';
允许rep用户通过192.168.1.121服务器登录并读取本地mysql的权限
mysql>flush privileges;           #刷新权限// 可在Slave上做连接测试: mysql -h 192.168.1.200  -u  wenqiang  –pmysql8改为这样了
mysql> create user 'test'@'172.17.0.3' identified by '123456';  # 创建用户
ERROR 1396 (HY000): Operation CREATE USER failed for    # 这个报错是之前这个用户创建过可能之前的用户没删干净导致的
mysql> grant replication slave on *.* to 'test'@'172.17.0.3';  # 授权用户主从复制权限
mysql> alter user 'slave'@'172.17.0.3' identified with mysql_native_password by '123456';  # 把加密方式也改了防止后期出现Last_IO_Errno: 2061问题。
mysql> grant all privileges on *.* to 'root'@'192.168.1.%'; # 授权所有与权限

3.锁主库表（锁定数据库中所有表）

mysql> FLUSH TABLES WITH READ LOCK;mysql> flush logs;

4.显示主库信息（记下来）

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB|
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      371 | test2        | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5.另开一个终端，备份（导出）需要复制的数据库，这一步是为了防止两边数据不一致导致同步不成功。同步时必须保证两边数据一致。

# mysqldump -u root --opt --default-character-set=utf8 -p --events --all-databases >/server/backup/master.sql
#用mysqldump命令导出字符集为（utf8）的所有数据库，导出到目录/tmp下的master.sql主从同步时主库为什么要锁库？
同步数据时mysql-bin.xxxx和Position的值必须和当前数据量能对的上，mysql-bin.xxx和Postion相当于当前数据量内容的标签，所以同步之前先锁主库并记住mysql-bin和postition值以及备份出与之对应的数据量内容，然后再把备份的数据还原到从库中，从库再根据当前的数据量内容和对应的2个标签(mysql-bin.xxx和Position)进行同步。
比如：
mysql-bin.00007 132   10M数据量内容
mysql-bin.00028 155   30M数据量内容

6、解锁主库表,此时主库可以运行了

mysql> UNLOCK TABLES;

三、MySQL从服务器配置

1.、将主数据库的master.sql传输到从服务器的 /tmp目录下

# yum -y install openssh-clients                      #安装scp#scp -pr /tmp/master.sql 192.168.1.121:/tmp

2、导入数据库

# mysql  -u root  -p --default-character-set=utf8   < /server/backup/master.sql

3、编辑从库配置

#vi /etc/my.cnf（其余参数保持默认）[mysqld]                                                 #必须在【mysqld】这个模块下
server-id=2                                              #不能与master的id相同
log-bin=mysql-bin                                        #开启二进制日志/etc/init.d/mysql restat                            重启从库

4、在SLAVE上设置同步

mysql> stop slave;                                       #关闭slave 同步服务
mysql> reset slave all;                                  #清除之前同步的残留数据
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.200',MASTER_USER='rep',MASTER_PASSWORD='Clouddeep@8890',MASTER_LOG_FILE='mysql-bin.000007',MASTER_LOG_POS=371;
-----------------------
从库通过用户rep去同步192.168.1.200上的数据，并根据mysql-bin.000007和position值完成同步。

5、启动SLAVE服务

mysql>start slave;

6、查看SLAVE状态

mysql> SHOW SLAVE STATUS\G;
其中 Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 "Yes"，表明 Slave 的 I/O 和 SQL 线程都在正常运行

7、如果需要的话设置从库对普通用户只读（不影响主从同步）

# 设置只读模式
mysql> set global read_only=1;    # 1只读，0读写，对super用户无效
mysql> flush privileges;# 也可以通过配置文件添加
[mysqld]
read_only=ON# 查看是否开启只读
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+# 查看哪些用户具有super权限
mysql> select user,host,Super_priv from mysql.user;# mysql5创建普通用户
mysql> grant select,insert,update,delete on s18.* to 'test'@'127.0.0.1' identifi
ed by '123456';
# mysql8创建普通用户
create user "wen"@"%" identified by '123456';  # 创建用户
grant insert,update on *.* to "wen"@"%";       # 授权
alter user "wenqiang2"@"%"  identified with mysql_native_password by '123456';  # 修改加密方式# 查看用户权限
mysql> show grants for "wen"@"%";
+----------------------------------------------------------------+
| Grants for wen@%                                         |
+----------------------------------------------------------------+
| GRANT INSERT, UPDATE ON *.* TO `wen`@`%` |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

一主多从，多个从的server-id 不能设置一样

状态参数解释：2. Master_Host: 10.1.8.62Master_User: rep_userMaster_Port: 3306这3条信息，显示了slave连接master时，使用的master的主机---master_host、连接master用的用户---master_user、连接master的端口---master_port。3. Connect_Retry: 10连接中断后，重新尝试连接的时间间隔。默认值是60秒。4. Master_Log_File: binlog.000026Read_Master_Log_Pos: 446这两条信息，显示了与master相关的日志的信息。master_log_file：当前I/O线程正在读取的master 二进制日志的文件名；read_master_log_pos：当前I/O线程正在读取的二进制日志的位置（主库Position值）5.  Relay_Log_File: relay.000008Relay_Log_Pos: 589Relay_Master_Log_File: binlog.000026这3条信息，显示了与relay log相关的信息。relay_log_file：当前SQL线程正在读取并执行的relay log的文件名；relay_log_pos：当前SQL线程正在读取并执行的relay log文件的位置；relay_master_log_file：master 二进制日志的文件名。该文件包含当前SQL执行的事物6.  Slave_IO_Running: YesSlave_SQL_Running: Yes显示了当前I/O线程和SQL线程的状态7.  Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:这部分显示的是关于复制DB和table的信息。8.  Last_Errno: 0Last_Error: laster_errno和laster_error是 Last_SQL_Errno和Last_SQL_Error的同义词。9.  Skip_Counter: 0系统参数sql_slave_skip_counter的值。sql_slave_skip_counter:slave应该跳过的事件数10. Exec_Master_Log_Pos: 446sql线程当前执行的事件，在master 二进制日志中的position（从库的Position值）11. Relay_Log_Space: 878所有存在relay log的大小12. Seconds_Behind_Master: 0这个值是时间戳的差值。是slave当前的时间戳和master记录该事件时的时间戳的差值13. Replicate_Ignore_Server_Ids: slave当前会跳过的事件号14. Master_Server_Id: 2211master的server-id；如果master和slave的server-id相同，在启动slave时，会报错在从库使用show slave status\;查看Read_Master_Log_Pos:   和  Exec_Master_Log_Pos:  的值如果不一样说明数据同步不一致

相关命令

(1)查看主从同步状态
show slave status\G;
(2)启动从库开始同步数据
start slave;

四、mysql读写分离

(1)mysql读写分离必须依赖mysql主从同步，开发将数据写入mysql主库，然后mysql主库的数据会自动同步到mysql从库，当需要读取数据的时候从mysql从库读取数据，一般主库负责写，从库负责读，(2)mysql读写分离多由开发控制，当然也可以交由运维实现：常见现象运维工作中会经常维护MySQL主从服务器，当然Slave我们只是用于读操作。
一般权限开通也只授权只读账号，但是有时候维护工作可能不是一个人在做，你不能保证其他同事都按照这个标准操作。
有同事可能会授权Slave库MySQL账号为all或者select,update,insert,delete。还有一种情况是主从做了对所有数据的同步（包括用户信息），在Master库上面授权的账号也同步到了Slave库上面，当然Master账号中肯定会有select,update,insert,delete权限。存在的问题那么问题来了，当运维人员或者开发人员程序错误的连接了Mysql把Slave当成了Master等情况，那么就悲催了所有的数据修改就到Slave了，也会直接影响到主从的同步。为了避免上述问题，我们需要给MySQL的Slave设置为只读模式,当然不会影响到主从同步，从库只读对super账户无效。解决方法
演示如下：
mysql> set global read_only=1; 
Query OK, 0 rows affected (0.00 sec)
#set global read_only=0读写模式，1只读模式# 查看哪些用户具有super权限
mysql> select user,host,Super_priv from mysql.user;
+------------------+------------+------------+
| user             | host       | Super_priv |
+------------------+------------+------------+
| root             | %          | Y          |
| wenqiang         | %          | Y          |
| root             | 127.0.0.1  | Y          |
| slave2           | 172.17.0.2 | N          |
| slave            | 172.17.0.3 | N          |
| mysql.infoschema | localhost  | N          |
| mysql.session    | localhost  | Y          |
| mysql.sys        | localhost  | N          |
+------------------+------------+------------+
8 rows in set (0.00 sec)授权普通MySQL测试账号,创建普通用户不能用 grant all privileges 
mysql> grant select,insert,update,delete on s18.* to 'test'@'127.0.0.1' identifi
ed by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
用测试账号登陆进行删除等操作，会提示--read-only错误
复制代码
复制代码
mysql> delete from student where sid=14;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so i
t cannot execute this statementmysql> insert class values(5,三年级十班);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so i
t cannot execute this statement
复制代码
复制代码
注意:set global read_only=1 对拥有super权限的账号是不生效的，所以在授权账号的时候尽量避免添加super权限那么我们在做数据迁移的时候不想发生任何数据的修改，包括super权限修改也要限制。
可以用锁表：mysql> flush tables with read lock;
Query OK, 0 rows affected (0.18 sec)
使用root账号测试：
mysql>  delete from student where sid=13;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read
lock
解锁测试：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql>  delete from student where sid=13;
Query OK, 0 rows affected (0.00 sec)