生产环境部署过的二种mysql同步: binlog和gtid

生产环境部署过的二种mysql同步: binlog和gtid

服务器A: 192.168.21.33 | master | winserver2019

服务器B: 192.168.21.40 | slave  | winserver2019

Mysql版本: 8.0.34.0

数据库连接软件: mysql-workbench-community-8.0.30-winx64

(注: linux版本一样的配置方法, 注意my.ini和my.cnf中的配置参数)

===biglog方式同步===========================================

1. 安装mysql8

2. mysql的Master端my.ini配置如下

[mysqld]
##设置server_id,同一局域网中需要唯一
server_id=33

##开启二进制日志功能,文件名指定为baiinfo-mysql-bin
log-bin=baiinfo-mysql-bin

##指定不需要备份的数据库名,如备份多个数据库，重复这个选项
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys

##指定需要复制的数据库名为test,如果备份多个数据库，重复设置这个选项即可
##官方文档推荐在master端不指定binlog-do-db，在slave端用replication-do-db来过滤
#binlog-do-db=test

##设置使用的二进制日志格式(mixed,statement,row),不要使用mix
binlog_format=row

##最小化日志记录
binlog_row_image=minimal

##从库开启binlog日志记录,从服务器上的更改也会被写入主服务器的二进制日志中
log-slave-updates=1

##配置二进制日志自动删除/过期时间，单位秒，默认值为2592000，即30天
binlog_expire_logs_seconds=2592000

##跳过主从复制中遇到的所有错误或指定类型的错误，避免slaves端复制中断
##如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=all

3. 配置master服务器的备份专用帐号和密码

--创建用户名/密码: slave/H8UAaO#$AfQeTiqo
create user 'slave'@'%' identified by 'H8UAaO#$AfQeTiqo';
--赋予备份权限,需要指定登陆来源ip的, 改'slave'@'%'为'slave'@'192.168.21.40'
grant all on *.* to 'slave'@'%';
--刷新使生效
flush privileges;
--查看一下用户状态确认正确
use mysql;
select Host,User from user;
--为Master服务器设置只读锁, 完成同步配置后记得解锁"unlock tables;"
Flush tables with read lock;

4. 将Master数据导出, 导入到Slave服务器, 数据必须保持一模一样!

  (略)

5. mysql的slave服务器my.ini配置如下:

[mysqld]
##从服务器 ID
server_id=40

##开启二进制日志功能,文件名指定为baiinfo-mysql-bin
log-bin=baiinfo-mysql-bin

##设置使用的二进制日志格式(mixed,statement,row),不要使用mix
binlog_format=row

##最小化日志记录
binlog_row_image=minimal

##配置二进制日志自动删除/过期时间，单位秒，默认值为2592000，即30天
binlog_expire_logs_seconds=2592000

##跳过主从复制中遇到的所有错误或指定类型的错误，避免slaves端复制中断
##如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=all

# 设置不需要同步的数据库
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=information_schema.%

# 指定需要做同步的数据库
replicate-do-db=oversea_bigdata
replicate-do-db=nacos_config
replicate-do-db=meeting_exhibition
replicate-do-db=iron_steel_data
replicate-do-db=economic_strategy
replicate-do-db=coop
replicate-do-db=bi_web_site
replicate-do-db=bi_app_visiting_assistants
replicate-do-db=xxl_job

##relay_log配置中继日志 
relay_log=baiinfo-relay-bin  

##mysql-slave将复制事件写进自己的二进制日志
log_replica_updates=ON

##防止改变数据(只读操作，除了特殊的线程)
read_only=ON

6. 设置主服务器链接ip用户名密码和binlog日志的文件名和当前日志点位

--先通过命令查询Master服务器, 获取File和Position参数
show master status;
--进入slave服务器中, 配置与主库的关联(logfilet和pos值,在master服务器上使用命令"show master status;"查询)
CHANGE MASTER TO MASTER_HOST='192.168.21.33',MASTER_PORT=3306, MASTER_USER='slave', MASTER_PASSWORD='baiinfo', MASTER_LOG_FILE='BAIINFO-APPDB06-bin.000004',MASTER_LOG_POS=776058804;
--回到Master服务器解除只读锁
unlock tables;
--在slave服务器上启动同步, 停止和查看同步状态
start slave;
stop slave;
show slave status \G;
--查看slave服务器状态
show slave status;
--->结果: Slave_IO_Running:'Yes' 和 Slave_IO_Running:'Yes' 则同步正常, 否则异常查看日志排查错误.

7. 配置过程中的一些问题
  1).mysql安装目录非默认时, 安装完成后启动mysql时会报"Mysql:ibdata1 Can‘t determine file permissions........"的错误
    解决方法:
      需要给\bin目录权限, 把NETWORK SERVICE添加到Administrators组, 才能正常启动mysql.
  2).修改my.ini文件后mysql无法启动
    解决方法:
      打开 my.ini文件另存为ANSI格式-覆盖-确定, 启动mysql, 解决!
  3).同步过程中的报错提示:
      Slave_IO_Running: No # 表示主服务器未成功读取
      Slave_SQL_Running: No # 表示slave不同步
      Slave_IO_Running: Connecting IP #与master连接有问题, 比如用户名或密码错误, 比如之前配置指定过master信息需要清除等等

 

===gtid方式同步===========================================================

1. 在以上biglog同步方式配置成功的基础上, 按如下配置稍做修改就可以转换成gtid方式同步

2. 修改Master端my.ini打开GTID模式, 配置如下:

# 增加打开gtid模式的配置
[mysqld]
gtid_mode=ON
enforce_gtid_consistency=1

-- 重启mysql服务后,可以用如下命令查询gtid是否打开成功"on"
SHOW GLOBAL VARIABLES LIKE 'gtid_mode'

3. 在slave上设置主服务器链接, (执行change master命令时注意mysql8.x版本的语法和配置命令):

-- 需要先停掉同步
stop slave;
stop replica;
-- 清空之前指定的master配置信息
change master to master_auto_position=0;
CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.21.33', SOURCE_USER='slave',SOURCE_PASSWORD='baiinfo', SOURCE_LOG_FILE='BAIINFO-APPDB06-bin.000012', SOURCE_LOG_POS=157;
-- 重新启动配置
start replica;
start slave;

#查看slave服务器状态

show slave status;
--->结果: Slave_IO_Running:'Yes' 和 Slave_IO_Running:'Yes' 则同步正常, 否则异常查看日志排查错误.

5. 同步检查常用命令
  SHOW GLOBAL VARIABLES LIKE 'gtid_mode'
  show slave status;
  show master status;
  show replica status;
  start slave;
  stop slave;
  start replica;
  stop replica;
  show global variables like 'slave_parallel_%';

6.根据二进制日志来恢复数据

-- 登录服务器，并查看日志
mysql -uroot -p
show binlog events in 'BAIINFO-DB06-bin.000022';
​
-- 查看到如下事务日志列表, 根据Pos到End_log_pos的值可以确定DB中执行的作业语句:
+---------------+---+----------+---------+------------+---------------------------------+
| Log_name      |Pos|Event_type|Server_id|End_log_pos| Info                             |
+---------------+---+----------+---------+------------+---------------------------------+
| BAI-bin.000022|157|Gtid      |     33 |     243 | SET @@SESSION.GTID_NEXT= ''836137c1-|
| BAI-bin.000022|243|Query     |     33 |     487 | 'use `bi-manage`; CREATE TABLE `bi-m|
| BAI-bin.000022|487|Gtid      |     33 |     571 | 'SET @@SESSION.GTID_NEXT= ''836137c1|
| BAI-bin.000022|571|Query     |     33 |     710 | 'use `bi-manage`; DROP TABLE `test` |
+---------------+---+----------+--------+---------+-------------------------------------+

-- 恢复命令语法格式：
mysqlbinlog [参数] [日志文件] | mysql -u[用户名] -p[密码] [数据库名]
​
-- mysqlbinlog的参数说明：
  -h, --host=<hostname>：连接MySQL服务器的主机名。
  -P, --port=<port_num>：连接MySQL服务器的端口号。
  -u, --user=<username>：连接MySQL服务器的用户名。
  -p, --password=<password>：连接MySQL服务器的密码。
  --ssl-ca=<ca_file>：使用指定的CA证书文件进行SSL连接。
  --ssl-cert=<cert_file>：使用指定的SSL证书文件进行SSL连接。
  --ssl-key=<key_file>：使用指定的SSL密钥文件进行SSL连接。
  -R, --read-from-remote-server：从远程MySQL服务器读取二进制日志。
  -r, --result-file=<file_name>：将解析后的日志输出到指定的文件。
  --base64-output[=value]：将blob字段以Base64编码的形式输出。
  -v, --verbose：详细输出解析后的日志内容。
  -d, --database=<database_name>：仅输出指定数据库的日志内容。
  -t, --to-last-log：从当前日志文件开始读取，直到最新的日志文件结束。
  -s, --start-datetime=<datetime>：从指定的日期和时间开始读取日志。
  -e, --stop-datetime=<datetime>：在指定的日期和时间停止读取日志。
  --start-position=<position>：从指定的位置开始读取日志。
  --stop-position=<position>：在指定的位置停止读取日志。
  --skip-gtids[=value]：跳过指定的GTID（全局事务标识符）。
  --include-gtids[=value]：仅包括指定的GTID。
  --hexdump：以十六进制格式输出日志内容。
  --no-defaults：不读取默认的配置文件。

MySQL常用的一些操作命令:
    mysql -u root -p   #进入mysql数据库
    ALTER user 'root'@'localhost' IDENTIFIED BY '1+1=2?Yes'; #修改初始密码
    CREATE DATABASE 数据库名称;   #创建新的数据库
    DROP DATABASE 数据库名称;   #删除数据库
    USE 数据库名称;  #指定使用的数据库
    DESCRIBE 表单名称;  #显示表结构
    SHOW databases;  #显示当前已有的数据库
    SHOW tables;   #显示当前数据库中的表单
    UPDATE 表单名称 SET attribute=新值 WHERE attribute>原始值;   #更新表单中的数据
    SELECT * FROM 表单名称;   #从表单中选中某个记录值
    DELETE FROM 表单名 WHERE attribute=值;   #从表单中删除某个记录值
    INSERT INTO mybook(name,price,pages) VALUES('linuxprobe','60', '518') ;   #插入值
    mysqldump -u root -p myDBName > /root/myDB.dump  #备份数据库
    mysqldump --single-transaction -u root -h 192.168.10.35 --password='password' -p myDBName > /root/myDB.sql  #指定密码备份数据库
    mysql -u root -p myDBName < /root/myDB.sql  #手动输密码,还原数据库,安全
    mysql -u root --password=password myDBName < /root/myDB.sql  #指定密码,自动还原数据库,不安全
    开通mysql外部访问权限:
     use mysql;
     update user set host="%" where user="root"; # 所有机器都能访问root用户
     flush privileges; # 重新加载权限
     show grants for '用户名'@'%';  #查看某用户名权限
    主从同步:
     start replica;
     stop replica;
     show replica status;
     show slave status \G;