mysql-主从同步与读写分离
一、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主库
- 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 –p
mysql8改为这样了
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.62
Master_User: rep_user
Master_Port: 3306
这3条信息，显示了slave连接master时，使用的master的主机---master_host、连接master用的用户---master_user、连接master的端口---master_port。
3. Connect_Retry: 10
连接中断后，重新尝试连接的时间间隔。默认值是60秒。
4. Master_Log_File: binlog.000026
Read_Master_Log_Pos: 446
这两条信息，显示了与master相关的日志的信息。master_log_file：当前I/O线程正在读取的master 二进制日志的文件名；read_master_log_pos：当前I/O线程正在读取的二进制日志的位置（主库Position值）
5. Relay_Log_File: relay.000008
Relay_Log_Pos: 589
Relay_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: Yes
Slave_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: 0
Last_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: 446
sql线程当前执行的事件，在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: 2211
master的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 statement
mysql> 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)