第1章 Mysql 主从复制
MySQL replication
主从复制 (也称之为AB复制) 允许将一个MySQL数据库服务器 (主服务器) 的数据复制到一 或多个MySQL数据库服务器 (从服务器).
复制是异步的****从站不需要永久连接来接收来自主站的更新数据
根据配置 可用复制数据库中所有数据库 或 选择数据库中指定的表
MySQL主从复制优点
(1) 横向扩展解决方案 - 在多个从站之间分配负载以提高性能。
在此环境中，所有写入和更新都必须在主服务器上进行。 但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度
(2) 数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。
(3) 分析 - 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。
(4) 远程数据分发 - 您可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器。
Replication的原理
(1) 主服务器上的任何修改都会通过自己的I/O tread(I/O 线程)保存在二进制日志 Binary log 里面
(2) 从服务器上面也启动一个I/O tread 通过配找到用户名和密码,连接到主服务器上面 请求读取二进制日志 然后把读取到的二进制日志写到本地的一个Realy log (中继日志)内
(3) 从服务器上面同时开启一个SQL thread 定时检查Realy log (这个文件也是二进制的) 如果发现有更新 立即把更新内容在本地数据库上执行一遍
每个从服务器都会收到主服务器的二进制日志其内容的副本
从服务器设备负责决定应该执行二进制日志中的那些语句,除非另行指定 否则主从二进制日志中的所有事件都会在从站上执行一次
如果需要, 你可以将从服务器配置为仅处理一些特定数据库或表的事件
你无法将主服务器配置为仅处理一些特定数据库或表的事件
每个从站都会记录二进制日志坐标: 文件名 文件中它已经从主站读取和处理的位置。
由于每个从服务器都分别记录了自己当前处理的二进制日志中的位置,因此可以断开从服务器的连接, 重新连接然后恢复继续处理任务.
一主多从
如果一主多从的话，这时主库既要负责写又要负责为几个从库提供二进制日志。
此时可以稍做调整，将二进制日志只给某一从，这一从再开启二进制日志并将自己的二进制日志再发给其它从。或者是干脆这个从不记录只负责将二进制日志转发给其它从，这样架构起来性能可能要好得多，而且数据之间的延时应该也稍微要好一些。
工作原理图如下：
关于二进制日志
mysqld将数字扩展名附加到二进制日志基本名称以生成二进制日志文件名。每次服务器创建新日志文件时，该数字都会增加，从而创建一系列有序的文件。
每次启动或刷新日志时，服务器都会在系列中创建一个新文件。
服务器还会在当前日志大小达到max_binlog_size参数设置的大小后自动创建新的二进制日志文件 。
二进制日志文件可能会比max_binlog_size使用大型事务时更大， 因为事务是以一个部分写入文件，而不是在文件之间分割。
为了跟踪已使用的二进制日志文件， mysqld还创建了一个二进制日志索引文件，其中包含所有使用的二进制日志文件的名称。默认情况下，它具有与二进制日志文件相同的基本名称，并带有扩展名'.index'。在mysqld运行时，您不应手动编辑此文件。
术语二进制日志文件通常表示包含数据库事件的单个编号文件。
术语 二进制日志 表示含编号的二进制日志文件集加上索引文件。
SUPER 权限的用户可以使用SET sql_log_bin=0语句禁用其当前环境下自己的语句的二进制日志记录
第2章 配置 Replication
配置步骤:
主服务器配置
第一步
在主服务器上,你必须启用二进制日志记录并配置唯一的服务器ID信息 最后重启服务器
- 编辑主服务器上的配置文件 /etc/my.cof 添加内容
vim /etc/my.cof
最后一行添加
[mysqld]
server-id=1
log-bin=mysql-bin (mysql-bin)默认指定文件 不用设置默认存放在/var/lib/mysql/
log_bin_index=mysql-bin.index (master-bin.index)默认指认文件 不用设置
binlog_do_db=test
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
备注：
server-id 服务器唯一标识。
log_bin 启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句。
log_bin_index 指定的是二进制文件的索引文件 这个文件管理了所有的binlog文件的目录
binlog_do_db 指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可。
binlog_ignore_db 指定不记录二进制日志的数据库，即不需要复制的数据库名，如果有多个数据库，重复设置这个选项即可。
其中需要注意的是，binlog_do_db和binlog_ignore_db为互斥选项，一般只需要一个即可。
- 重启服务
systemctl restart mysqld
systemctl restart mariadb (小型数据库)
注意：
如果省略 server-id（或将其显式设置为默认值0），则主服务器拒绝来自从服务器的任何连接。
为了在使用带事务的InnoDB进行复制设置时尽可能提高持久性和一致性， 您应该在master (主服务器) my.cnf文件中使用以下配置项：
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
确保在主服务器上 skip_networking 选项处于 OFF 关闭状态, 这是默认值。 如果是启用的，则从站无法与主站通信，并且复制失败。
mysql> show variables like '%skip_networking%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | OFF |
+-----------------+-------+
查看主数据库信息，记住下面的“File”与“Position”的信息，它们是用来配置从数据库的关键信息。
mysql> show master status;
+------------------+----------+--------------+------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------------+
| mysql-bin.000004 | 381 | test | |
+------------------+----------+--------------+------------------------+
1 row in set (0.00 sec)
第二步
创建一个专用于负责数据的用户
每个从站需要使用MySQL主站上的用户名和密码连接到主站上
例如: 计划使用用户old可以从任何主机上连接到master (主服务器)上进行辅助操作并且用户old仅仅可以使用复制权限
实现以上执行操作
mysql> GRANT REPLICATION SLAVE ON *.* TO 'old'@'%' identified by '123456';
192.168.17.%通配符，表示0-255的IP都可访问主服务器，正式环境请配置指定从服务器IP
若将 192.168.17.% 改为 %，则任何ip均可作为其从数据库来访问主服务器
在从服务器上使用用户进行测试连接
shell> mysql -uold -p'123456' -h
如果主服务器中有数据
如果在启动复制之前有现有数据需要与从属设备同步，请保持客户端正常运行，以便锁定保持不变。这可以防止进行任何进一步的更改，以便复制到从站的数据与主站同步。
在主服务器中导出现有的数据
如果主数据库包含现有数据，则必须将此数据复制到每个从站。有多种方法可以实现
使用mysqldump工具创建要复制的所有数据库的转储。这是推荐的方法
mysqldump -u用户名 -p密码 --all-databases --master-data=1 > /tmp/back.sql
这里的用户是主服务器的用户
如果不使用--master-data参数，则需要手动锁定单独会话中的所有表。从主服务器中使用 scp 或 rsync 等工具，把备份出来的数据传输到从服务器中。
在主服务中执行如下命令
rsync -avz /tmp/back.sql 目标ip:存放目录
这里的 目标ip需要能被主服务器解析出 IP 地址，或者说可以在主服务器中 ping 通的。导入数据到从服务器，并配置连接到主服务器的相关信息
登录到从服务器上，执行如下操作
导入数据mysql> source /tmp/back.sql
从服务器配置
- 配置从服务器，并重启 在从服务器 上编辑其配置文件 /etc/my.cnf 并添加如下内容：
vim my.cnf 文件 最后编写
[mysqld]
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
replicate-do-db=test
server-id 服务器唯一标识，如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识， 如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。
relay-log 启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，或主服务器挂掉了将此从服务器作为其他从服务器的主服务器。
replicate-do-db 指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。 若在master端不指定binlog-do-db，则在slave端可用replication-do-db来过滤。
replicate-ignore-db 不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。
其中需要注意的是，replicate-do-db和replicate-ignore-db为互斥选项，一般只需要一个即可。
- 在从服务器配置连接到主服务器的相关信息
mysql> CHANGE MASTER TO
MASTER_HOST='远程IP地址', -- 主服务器的主机名(也可以是 IP)
MASTER_USER='old', -- 连接到主服务器的用户
MASTER_PASSWORD='123456', -- 到主服务器的密码
MASTER_LOG_FILE=' mysql-bin.000004', -- 日志文件的名称,需要与主服务器对应
MASTER_LOG_POS='381'; -- 日志位置,需要与主服务器对应
- 启动从服务器的复制线程
mysql> start slave; 开启线程
Query OK, 0 rows affected (0.09 sec)
stop slave 停止线程
重启服务
systemctl restart mysql
systemctl restart mariadb.service
出现问题解决思路
如果执行出现问题可使用下面命令 查看server_id状态 主站应为数值1 从站应为数值2以上
show variables like 'server_id';
可在数据库中手动进行设置
SET GLOBAL server_id=2;
启动服务出现:
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log
解决办法: 由于新的slave改变了服务端口和文件路径，分析应该是由于mysql-relay-bin.index中仍然保存着旧relay日志文件的路径，而这些路径下又找不到合适的文件，因此报错。
对于这类问题解决起来是比较简单的，重置slave的参照即可，执行命令如下：
mysql>reset slave; 重置slave的参照
Query OK, 0 rows affected (0.01 sec)
重新设置salve参照
mysql> CHANGE MASTER TO
MASTER_HOST='172.16.1.51',
MASTER_USER='old',
MASTER_PASSWORD='123456';
检查是否成功
在从服务上执行如下操作，加长从服务器端 IO线程和 SQL 线程是否是 OK
mysql>show slave status\G;
输出结果中应该看到 I/O 线程和 SQL 线程都是 YES, 就表示成功。
执行此过程后，在主服务上操作的修改数据的操作都会在从服务器中执行一遍，这样就保证了数据的一致性。
第3章 小结
读写分离，我们可以通过程序来实现，这里简单讲解一下实现思想。
我们可以在主服务器创建一个数据库用户（出于安全，根据需求给予相应的权限）主要用于写操作，在程序中通过这一用户连接主数据库的只用于写操作而不用读操作。
在从服务器上创建一个数据库用户（出于安全，只给予读select的权限）主要用于读操作，在程序中通过这一用户连接从数据库即可。
当然，也可以找一个组件来完成MYSQL的代理，实现SQL语句的路由，这样就不需要我们在程序上关注哪个数据库是写，哪个数据库是读的了。
