mysql5.7.26 基于GTID的主从复制环境搭建

主库：  binlog_dump_thread 二进制日志投递线程  mysql -S /data/3307/mysql.sock -e "show processlist" 从库:  IO_Thread :  从库IO线程 ：    请求和接收binlog SQL_Thread:  从库的SQL线程 ： 回放日志

 

主从环境的基本准备 

1. mysql主从服务器说明

192.168.1.219 mysql主服务器192.168.1.17 mysql从服务器192.168.1.151  mysql从服务器 #中间配置IP和网络及防火墙设置省略2.   安装mysql2.1    yum安装所需相关依赖包。 yum -y install gcc-c++  yum -y install zlib zlib-devel pcre pcre-devel yum -y install openssl-develyum -y install  libaio-devel.x86_642.2搜索关键字：MySQL5.7 linux二进制安装参考网址：https://www.cnblogs.com/chenmh/p/5413881.htmlMySQL5.7 linux二进制安装下载路径：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz2.2.1#创建mysql的数据目录，该目录在初始化数据库的候会用到mkdir -p /data/mysql/datamkdir -p /data/mysql/log 2.2.2#移动mysql文件到/data/mysql/mysqltar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/cd /usr/local/ ln -s mysql-5.7.26-linux-glibc2.12-x86_64 mysql ## 软连接2.2.3 #先创建mysql组groupadd mysql2.2.4#将MySQL用户加入mysql组useradd -r -g mysql -s /bin/false mysql     2.2.5#修改目录权限chown -R mysql:mysql /data/mysql/chmod 750 /data/mysql2.2.6#配置my.cnfls -l  /etc/my.cnfrpm -e mariadb-libs  --nodepsvim /etc/my.cnf  #创建my.cnf文件要命名为my.cnf，另外删除/etc/下的所有my.*文件=====================================================/etc/my.cnf配置修改如下配置主库配置[root@mysqltest01 ~]# cat /etc/my.cnf[client]port = 3306socket = /data/mysql/mysql.sock[mysqld]port = 3306user = mysqlcharacter-set-server = utf8mb4default_storage_engine = innodblog_timestamps = SYSTEMsocket = /data/mysql/mysql.sockbasedir =/usr/local/mysqldatadir = /data/mysql/datapid-file = /data/mysql/mysql.pidmax_connections = 1000max_connect_errors = 1000table_open_cache = 1024max_allowed_packet = 128Mopen_files_limit = 65535server-id=1gtid_mode=onenforce_gtid_consistency=onlog-slave-updates=1log-bin=master-binlog-bin-index = master-bin.indexrelay-log = relay-logrelay-log-index = relay-log.indexbinlog_format=rowlog_error = /data/mysql/log/mysql-error.log skip-name-resolvelog-slave-updates=1relay_log_purge = 0 slow_query_log = 1long_query_time = 1 slow_query_log_file = /data/mysql/log/mysql-slow.logsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES==========================================从库1配置：[client]port = 3306socket = /data/mysql/mysql.sock[mysqld]port = 3306user = mysqlcharacter-set-server = utf8mb4default_storage_engine = innodblog_timestamps = SYSTEMsocket = /data/mysql/mysql.sockbasedir =/usr/local/mysqldatadir = /data/mysql/datapid-file = /data/mysql/mysql.pidmax_connections = 1000max_connect_errors = 1000table_open_cache = 1024max_allowed_packet = 128Mopen_files_limit = 65535server-id=2gtid_mode=onenforce_gtid_consistency=onlog-slave-updates=1log-bin=master-binlog-bin-index = master-bin.indexrelay-log = relay-logrelay-log-index = relay-log.indexbinlog_format=rowlog_error = /data/mysql/log/mysql-error.log skip-name-resolvelog-slave-updates=1relay_log_purge = 0 slow_query_log = 1long_query_time = 1 slow_query_log_file = /data/mysql/log/mysql-slow.logsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES=========================================从库2配置：[client]port = 3306socket = /data/mysql/mysql.sock[mysqld]port = 3306user = mysqlcharacter-set-server = utf8mb4default_storage_engine = innodblog_timestamps = SYSTEMsocket = /data/mysql/mysql.sockbasedir =/usr/local/mysqldatadir = /data/mysql/datapid-file = /data/mysql/mysql.pidmax_connections = 1000max_connect_errors = 1000table_open_cache = 1024max_allowed_packet = 128Mopen_files_limit = 65535server-id=3         gtid_mode=onenforce_gtid_consistency=onlog-slave-updates=1log-bin=master-binlog-bin-index = master-bin.indexrelay-log = relay-logrelay-log-index = relay-log.indexbinlog_format=rowlog_error = /data/mysql/log/mysql-error.log skip-name-resolvelog-slave-updates=1relay_log_purge = 0 slow_query_log = 1long_query_time = 1 slow_query_log_file = /data/mysql/log/mysql-slow.log============================================2.2.7#初始化数据库 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --basedir=/data/mysql/mysql --datadir=/data/mysql/data --innodb_undo_tablespaces=3 --explicit_defaults_for_timestamp    #初始化mysql2.2.7#根据配置了my.cnf的error.log，查看初始密码grep ‘password‘ /data/mysql/log/mysql-error.log   2.2.8#创建ssl加密/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/data/mysql/data2.2.9#将里面的basedir和datadir改为和my.cnf一致vi /usr/local/mysql/support-files/mysql.server            basedir=/usr/local/mysqldatadir=/data/mysql/data2.2.10#拷贝和配置启动文件cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld     2.2.11设置开机启动chkconfig --add mysqldchkconfig mysqld on 2.2.12配置环境变量echo ‘PATH=/usr/local/mysql/bin:$PATH‘>>/etc/profiletail -1 /etc/profilesource /etc/profile   #让环境变量生效 echo $PATH    #检查变量是否添加成功##/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin2.2.13#启动mysqlservice mysqld start 2.2.14#进入mysqlmysql -uroot -p         #密码通过前面的2.2.7找到2.2.15#修改初始密码ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘123456‘;    2.2.16#刷新权限flush privileges; 2.2.17#查看路径信息SHOW  GLOBAL VARIABLES LIKE ‘%log%‘;  2.2.18日志查询1.查找错误日志文件路径show variables like ‘log_error‘;2.查找日志文件路径show variables like ‘general_log_file‘; 3.慢查询日志文件路径show variables like ‘slow_query_log_file‘; 2.2.19加入开机启动项 vim /usr/lib/systemd/system/mysqld.service [Unit]Description=MySQL ServerDocumentation=man:mysqld(8)Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnfLimitNOFILE = 5000 chmod +x /usr/lib/systemd/system/mysqld.service ##给予执行权限systemctl daemon-reload ###重新加载systemctl enable mysqld.servicesystemctl start mysqld.service

 

============================================

 GTID主从复制： 

 

 

配置步骤：1.检查gtid是否运行正常启动后可以使用show global variables like ‘%gtid%‘;查看GTID状态

gtid_mode | ON  

enforce_gtid_consistency | ON  

注：主库从库都需要开启GTID否则在做主从复制的时候就会报错：查看数据库的uuid在数据目录的auto.cnf在mysql里面使用命令查看show global variables like ‘server_uuid‘2.登录主库给从库复制权限grant replication slave on *.* to ‘replication‘@‘192.168.1.%‘ identified by ‘1234‘;flush privileges;select host,user from mysql.user;3.登录从库使用命令进行复制 change master to master_host=‘192.168.1.219‘, master_port=3306, master_user=‘replication‘, ###用户名和密码要与在主库上建立的一致 master_password=‘1234‘, master_auto_position = 1; ##此参数可以一直不变化start slave; ##启动同步show slave status\G; ##查看同步状态

Slave_IO_Running: Yes  

Slave_SQL_Running: Yes

这两个参数必须为yes否则同步没有成功 

4.主从的binlog日志观察show binlog events in ‘master-bin.000001‘;5.测试同步增加数据来验证是否同步成功create database shijiange;use shijiange;create table test (id int);insert into test values (1);update test set id = 3 where id = 1;delete from test;drop database shijiange;

 

 

 

主库的信息（master.info）：    Master_Host: 10.0.0.51                主库的IPMaster_User: repl   复制用户名Master_Port: 3307   主库的端口Connect_Retry: 10   断连之后重试次数Master_Log_File: mysql-bin.000001     已经获取得到binlog的文件名Read_Master_Log_Pos: 444              已经获取得到binlog的位置号从库的relaylog的信息(relay-log.info)：Relay_Log_File: db01-relay-bin.000002 从库已经运行过的relaylog的文件名Relay_Log_Pos: 320   从库已经运行过的relaylog的位置点从库复制线程工作状态：Slave_IO_Running: YesSlave_SQL_Running: Yes           过滤复制相关的状态：Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: 从库延时主库的时间:Seconds_Behind_Master: 0        从库延时主库的时间（秒为单位）从库线程报错详细信息：Last_IO_Errno: 0        IO报错的号码Last_IO_Error:          IO报错的具体信息Last_SQL_Errno: 0       SQL报错的号码Last_SQL_Error:         SQL线程报错的具体原因延时从库：SQL_Delay: 0              延时从库设定的时间SQL_Remaining_Delay: NULL 延时操作剩余时间  GTID复制信息：Retrieved_Gtid_Set:     接收到的GTID的个数Executed_Gtid_Set: 执行了的GTID的个数

 

 

SQL线程故障原因一：读relay-log.info 读relay-log ，并执行日志更新relay-log.info 以上文件损坏，最好是重新构建主从原因二：为什么一条SQL语句执行不成功？1. 主从数据库版本差异较大2. 主从数据库配置参数不一致（例如：sql_mode等）3. 想要创建的对象已经存在4. 想要删除或修改的对象不存在5. 主键冲突6. DML语句不符合表定义及约束时归根结底是从库写入了。

 

IO线程No的状态分析：

 

原因一： 日志名不对从库信息：Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 444对比备份的位置号。原因二：日志损坏，日志不连续

 

 

 

mysql5.7.26 基于GTID的主从复制环境搭建

标签：connect   user   dump   eve   linu   安装mysql   基于   logs   io线程