MYSQL主从复制
概述
主从复制是指将主数据库的DDL和DML操作通过二进制日志（binlog）传到从库服务器中，然后在从库上对这些日志重新执行 （也叫重做），从而使得从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。
Mysql复制的优点主要包合以下三个方面：
- 主库出现问题，可以快速切换到从库提供服务。
- 实现读写分离，降低主库的访问压力。
- 可以在从库中执行备份，以避免备份期间影响主库服务。
基于日志点的主从复制
原理
搭建主从复制
0. 准备
两台机器安装好mysql，并且启动mysql
1. 主库配置
1.1 修改配置文件/etc/my.cnf
#开启二进制日志（myslq8.0+是默认开启binlog的）,bin_log=后面接二进制日志的路径和文件名，默认路径为/var/lib/mysql/
log_bin=mysql-bin
#mysql服务id，保证整个集群环境中唯一，取值范围：1-232-1，默认为1
server_id=1
#普通用户是否只读,1代表只读,0代表读写，默认为读写
read_only=0
#超级用户是否只读,1代表只读,0代表读写，默认为读写
super_read_only=0
#忽略的数据库，即不需要同步的数据库
#binlog_ignore_db=mysql
#binlog_ignore_db=sys
#binlog_ignore_db=information_schema
#指定同步的数据库
#binlog_do_db=db1
#binlog_do_db=db2
1.2 重启mysql服务
systemctl restart mysqld
1.3 登录mysql，创建远程连接的账号，并授予主从复制权限
#创建mysync用户，井设置密码，该用户可在任意主机连接该MySQL服务
CREATE USER 'mysync'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
#为mysync用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'%';
1.4 查看主库二进制日志坐标
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000042 | 100725 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#File表示二进制日志的名称为binlog.000042
#Position表示从库的IOthread线程从主库的二进制日志：binlog.000042的100725位置处开始读
2.从库配置
2.1 修改配置文件 /etc/my.cnf
#mysql服务id，保证整个集群环境中唯一，取值范園：1-232-1，和主库不一样即可
server_id=2
#普通是否只读,1代表只读,2代表读写,从库一般设置为只读
read_only=1
#超级用户是否只读,1代表只读,0代表读写，默认为读写，从库一般设置为只读
super_read_only=0
2.2 重启mysql服务
systemctl restart mysqld
2.3 登录从库mysql，查看配置
mysql> SHOW VARIABLES like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | ON |
| transaction_read_only | OFF |
+-----------------------+-------+
4 rows in set (0.02 sec)
#read_only和super_read_only均为ON表示任何用户都不能对从库进行写操作
注意：
- 可以在登录mysql后使用命令
set global read_only=1或set global super_read_only=1来设置数据库的普通用户或super用户为只读状态。 - 使用此命令设置的只读状态是临时的，当重启mysql服务(
systemctl restart mysqld)后，设置将失效。所以如果需要设置为永久生效，则需要在配置文件/etc/my.cnf中配置。
2.4 登录从库mysql，设置主库配置
mysql 8.0.23 之后的版本，执行如下SOL：
CHANGE REPLICATION SOURCE TO SOURCE_HOST='主库ip', SOURCE_USER='用于主从复制的账号', SOURCE_ PASSWORD='用于主从复制的账号密码', SOURCE_LOG_FILE='主库二进制日志名称', SOURCE_LOG_POS='主库二进制日志位置';
mysql 8.0.23 之前的版本，执行如下SOL：
CHANGE MASTER TO MASTER_HOST='主库ip', MASTER_USER ='用于主从复制的账号', MASTER_PASSWORD= '用于主从复制的账号密码', MASTER_LOG_FILE='主库二进制日志名称', MASTER_LOG_POS='主库二进制日志位置';
注意：
-
使用上述执行的
change语句会将主库的信息写到matser.info文件中，包括用户名和明文密码。如果不想将明文密码显示在master.info中，可以执行:#mysql 8.0.22之前的版本 CHANGE MASTER TO MASTER_HOST='主库ip', MASTER_LOG_FILE='主库二进制日志名称', MASTER_LOG_POS='主库二进制日志位置';然后在启动主从复制时使用命令:
#mysql 8.0.22之前的版本 start slave user='用于主从复制的账号' password='用于主从复制的账号密码';在启动主从复制时指定用户和密码后，用户和密码将不会被保存到master.info中
2.5 登录从库，开启主从复制
start replica; #8.0.22之后
start slave; #8.0.22之前
2.6 查看从库的状态
show replica status; #8.0.22之后
show slave status; #8.0.22之前
Replica IO Running: Yes
Replica SQL Running: Yes
表示启动主从复制成功
其他命令：
-
reset slave/master all;清除主库/从库信息 -
stop slave/master;停止主库/从库
基于日志点的优缺点
优点：
-
是mysql最早支持的复制技术，BUG相对较少
-
对SQL查询没有任何限制
-
故障处理比较容易
缺点：
- 从库中日志点(pos)配置错误时会导致主从数据不一致
- 故障转移时从新获取新的日志点(即binlog的pos)信息比较困难
基于GUID的主从复制
GTID即全局事务ID，其保证为每一个在master上提交的事务在及群众可以生成唯一的ID。
基于GUID的主从复制是Mysql 5.6版本之后新增的特性
原理
1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。
搭建
修改主服务器配置文件并重启mysql
log_bin=mysql-bin
server_id=1
gtid_mode=on #开启GTID
enforce_gtid_consistency=on #强制gtid一致性
log-slave_updates=on # 开启slave中记录master传过来的修改日志
修改从服务器配置文件并重启mysql
log_bin=mysql-bin
server_id=2
relay_log=relay-log #中继日志
gtid_mode=on
enforce_gtid_consistency=on
log-slave_updates=on
read_only=on
master_info_repository=table #将master的信息存到表中。存到表中的好处：操作失误时可以通过事务回滚回到操作之前
relay_log_info_repository=table #将中继日志存到表中
登录从服务器配置主库信息
change master to master_host='master_host_ip',master_password='password',master_user ='用于主从复制的账号',master_auto_position=1; #master_auto_position=1表示GTID的自动增长值为1
开启slave主从复制
start slave;
查看两个线程是否为YES
show slave status;
基于GTID的优缺点
优点：
- 可以很方便的进行故障转移
- 从库不会丢失主库上的任何修改数据
缺点：
- 开启GTID后，一些sql语句将不能使用，如：
create table...select、Create temporary table
主从复制的弊端
因为主从复制的原理是：从库从主库的二进制日志中的某个位置开始读取，所以开启主从复制之前的主库数据将不会复制到从库中。所以如果做主从复制之前主库中就已经存在数据，那么应该先将主库中的数据导入到从库后再搭建主从复制。
MYSQL导出 SQL 格式的数据
mysqldump 命令是 mysql 用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等，SQL脚本中不包含创建数据库的语句。
1. 导出指定数据库中的所有表：
mysqldump -u username -p [--databases] database_name > path\dbname.sql
-u：mysql用户名参数。
username：我们可以登录到数据库的用户名。
-p：mysql密码参数。
database_name：我们要导出的数据库的名称。
>：指定输出路径位置。
path/dbname.sql：path是我们要导出的文件夹路径，dbname.sql 是保存输出的文件。
注意：
-
使用
--databases参数时，导出的sql文件包含创建数据库的语句。 -
加上
--master-data参数时，导出的sql文件中会包含change master to master_log_file='xxx',master_log_pos='xxx';语句。 -
--master-data=2会在sql文件中加上注释前缀。
#导出db1这个数据库中的所有表
mysqldump -uroot -p [--databases] db1 > /home/db1.sql
#导出多个数据库（db1、db2）的表
mysqldump -uroot -p --databases db1 db2 > /home/db1_db2.sql
2. 导出指定数据库中的指定表
#导出db1这个数据库中tb1这个表
mysqldump -uroot -p db1 tb1 > /home/db1.tb1.sql
3. 导出所有数据库
常用于数据库的备份
mysqldump -uroot -p --all-databases > /home/database_dump.sql
4. 不导出数据，只导出表结构
使用-d参数表示只导出表结构，不导出数据
#导出数据库db1内的所有表结构
mysqldump -u root -p -d db1 > /home/db1.sql
#导出数据库db1、db2内的所有表结构
mysqldump -u root -p -d --databases db1 db2 > /home/db1_db2.sql
#导出数据库db1内的tb1表结构
mysqldump -u root -p -d db1 tb1 > /home/db1.tb1.sql
#导出所有数据库的表结构
mysqldump -u root -p -d --all-databases > /home/all_db.sql
MYSQL导入SQL格式的数据
因为使用mysqldump命令导出sql文件时，sql文件中不会有创建数据库的语句，所以在导入sql文件之前要提前创建好数据库。
1. mysql 命令导入
mysql -u用户名 -p密码 指定数据库 < 要导入的数据库数据(db1.sql)
#指定数据库要提前创建好，否则导入sql时会报错。
2. source 命令导入
source 命令导入数据库需要先登录到数据库，并且创建好响应的数据库
mysql> create database db1 default charset utf8mb4; # 创建数据库
mysql> use db1; # 使用已创建的数据库
mysql> source /home/db1.sql # 导入备份数据库
3966
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
