转 mysql 主从复制以及binlog 测试 (5.7)

https://www.cnblogs.com/feiyun8616/p/9009497.html
(这篇文档描述了5.6 版本搭建从库)，以下文档描述了5.7 版本mysql 从库的搭建。
5.7 版本使用600M tar 解压包，使用非编译 安装法

5.6 版本使用300M tar 解压包，使用非编译安装法

这里主要参考的模板为以下文档，感谢payon
https://www.jb51.net/article/108726.htm

###for mysql 5.7

##感谢关键步就几步
https://www.cnblogs.com/javabg/p/9951852.html

strings /lib64/libc.so.6 | grep GLIBC

cp /dbsoft/lxy/mysql/nbu/mysqlbackup /usr/bin/mysqlbackup

scp mysql-advanced-5.7.24-linux-glibc2.12-x86_64.tar.gz cradmin@56.18.99.206:/tmp/dba
tar -xzvf mysql-advanced-5.7.24-linux-glibc2.12-x86_64.tar.gz

groupadd mysql
useradd -r -g mysql mysql
mkdir /crabank/mysql

 

规划目录如下： my.conf 在data 目录下。

 

 

--defaults-file=/db/mysql/data/3306/my.cnf   (mysql 数据文件目录)
--basedir=/db/mysql/base/                            （mysql 软件安装目录）        
--datadir=/db/mysql/data/3306/mydata
--plugin-dir=/db/mysql/base/lib/plugin/
--log-error=/db/mysql/data/3306/mydata/mysql-error.log
--pid-file=/db/mysql/data/3306/mydata/mysql.pid
--socket=/db/mysql/data/3306/mysqltmp/mysql.sock

mysql主目录处理
在software目录下移动文件到/usr/local/mysql:

mv mysql-advanced-5.7.24-linux-glibc2.12-x86_64 /db/mysql
cd /db/mysql
mv mysql-advanced-5.7.24-linux-glibc2.12-x86_64 base

chown -R mysql:mysql /db/mysql
##cp /db/db1/mysql/app/bin/mysql /usr/bin/mysql
cd /db/mysql
mkdir data

 

show variables like '%gtid%';

edit /db/mysql/data/3306/my.cnf
gtid_mode=on
log-bin=mysql-bin
log-slave-updates=1
enforce-gtid-consistency=1

 

1、在MySQL的生产从库进行mysqlbackup的全备  (这种方式本地备份，好像不会产生锁，但是NBU 备份确会产生锁，数据库HANG,原因未知)

##mysqlbackup --login-path=root --socket=/user/my$port/var/mysql.sock --backup-dir=$mybackdir_full ##--backup-image=$mybackdir_full/mybackup.mbi --compress backup-to-image

mysqlbackup -uroot -p --socket=/db/mysql/data/mysqltmp/mysql.sock --backup-dir=/db/mysql/bak \
--backup-image=/db/mysql/bak/mybackup.mbi --compress backup-to-image

2、将备份文件拷贝到目标主机
cd /db/mysql/bak
scp -rp * root@10.10.227.196:/db/db1/mysql/bak

 

#### 感谢 火星人华仔 user.frm,user.MYD，user.MYI https://www.cnblogs.com/shihua513/p/6166200.html

mkdir /home/mysql
chown mysql:mysql /home/mysql

2、将备份文件拷贝到目标主机
cd /db/mysql/bak
scp -rp * root@10.10.227.196:/db/db1/mysql/bak

3、在目标库进行恢复，可以用Mysql，也可以用root

 

##mysqlbackup --defaults-file=$mybackdir_full/backup-my.cnf --datadir=/user/my$port/data ##--backup_image=$mybackdir_full/mybackup.mbi --backup_dir=$mybackdir_full copy-back-and-apply-log --uncompress

 

export mybackdir_full=/db/mysql/bak
##注意：恢复数据文件目录 --datadir 要写正确，否则会导致 恢复错目录导致 MySQL无法启动：Table mysql.host doesn t exist！ 发生错误1067 ，解决。

##这个命令貌似换行有问题，只能用notepad 放在一行执行，恢复目录：
##一定要cd 到数据文件目录，不然恢复也会报错，
cd /db/mysql/data/3306/mydata
mysqlbackup --defaults-file=$mybackdir_full/server-my.cnf --datadir=/db/mysql/data/3306/mydata --backup_image=$mybackdir_full/mybackup.mbi --backup_dir=$mybackdir_full copy-back-and-apply-log --uncompress

chown -R mysql:mysql mydata

4、mysql 用户 启动数据库

--cd /user/mysql/base
--nohup ./bin/mysqld_safe --defaults-file=/user/my${port}/my.cnf &

cd /db/mysql/base/
nohup ./bin/mysqld_safe --defaults-file=/db/mysql/data/3306/my.cnf &

cp /db/db1/mysql/bak/server-my.cnf /db/db1/mysql/app/my.cnf

--检查my.cnf
[mysqld]
basedir = /db/db1/mysql/app
datadir = /db/db1/mysql/data/mydata
log-error = /db/db1/mysql/app/mysql_error.log
pid-file = /db/db1/mysql/app/mysql.pid
#user = mysql
#tmpdir = /tmp
tmpdir=/db/db1/mysql/data/mydata/tmp
socket=/db/db1/mysql/data/mysqltmp/mysql.sock
slow_query_log_file=/db/db1/mysql/data/mydata/mysql-slow.log
slave_load_tmpdir=/db/db1/mysql/data/mydata/tmp
plugin_dir=/db/db1/mysql/app/lib/plugin/
slave_load_tmpdir=/db/db1/mysql/data/mydata/tmp
bind_address=10.10.227.198
character_sets_dir=/db/db1/mysql/app/share/charsets/
general_log_file=/db/db1/mysql/data/mydata/pdb1db02.log
lc_messages_dir=/db/db1/mysql/app/share/
log_bin=/db/db1/mysql/data/mydata/mysql-bin
log_bin_index=/db/db1/mysql/data/mydata/mysql-bin.index
log_error=/db/db1/mysql/data/mydata/mysql-error.log

cd /db/db1/mysql/app/
nohup ./bin/mysqld_safe --defaults-file=/db/db1/mysql/app/my.cnf &

chown -R mysql:mysql /db/mysql/bak/

##感谢 散尽浮华
##测试GTID 是否要开了
"show variables like '%gtid%';"查看
/db/mysql/app/mysql/my.cnf
gtid_mode=on
log-bin=mysql-bin
log-slave-updates=1
enforce-gtid-consistency=1

 

select INET_ATON('56.18.99.206')
server_id=180806598

 

三、重启
su - mysql
mysqladmin -uusername -p shutdown

/db/mysql/base/bin/mysqld_safe --defaults-file=/db/mysql/data/3306/my.cnf &
##mysqld_safe --defaults-file=/**/**/my.cnf &

 

show master status;

##调整my.cnf文件中的server_id参数,这条命令可以在主库执行，但是IP 用的是从库VIP
###通过 server-id 来区分 是否主库 还是 备库
server-id只需采用ip地址的整数形式如:
select INET_ATON('10.10.227.198')，server_id=180806598

show variables like '%server_id%';

5、为了接续复制需要重新设置gtid_purged，

运行$mybackdir_full/meta/backup_gtid_executed.sql重置gtid_purged

--@/db/db1/mysql/bak/meta/backup_gtid_executed.sql

SET @@GLOBAL.GTID_PURGED='52714855-1870-11e8-bce2-005056bd438d:1-3';

####感谢 Payon https://www.jb51.net/article/108726.htm
#####执行时候报错 ： ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

 fix:

reset master;

--@/db/db1/mysql/bak/meta/backup_gtid_executed.sql

SET @@GLOBAL.GTID_PURGED='52714855-1870-11e8-bce2-005056bd438d:1-3';

 

6、slave 操作 ，修改MASTER，MASTER_HOST需要设置为生产从库IP

1).在主库上建立复制账户并授予权限

###基于GTID的复制会自动地将没有在从库执行的事务重放, 所以不要在其他从库上建立相同的账号. 如果建立了相同的账户, 有可能造成复制链路的错误.
###注意在生产上的密码必须依照相关规范以达到一定的密码强度, 并且规定在从库上的特定网段上才能访问主库.

因为可能在ZH和SS 同时搭建从库，所以不需要有IP 限制

create user 'resync'@'%' identified by 'Resync$123';
grant replication slave on *.* to 'resync'@'%';

or
GRANT REPLICATION SLAVE ON *.* TO 'resync'@'%' IDENTIFIED BY 'Resync$123';

 

flush privileges;

select user, host from mysql.user;
show grants for 'resync'@'56.%';

 

##已有的从库查看 同步的密码
感谢li 
1.SHOW SLAVE STATUS\G
master.info
查看 master.info 位置
2.Master_Info_File: /db/mysql/data/mydata/master.info
cat /db/mysql/data/mydata/master.info 查看密码

 

 

2).从库上配置：
mysql> show databases;

CHANGE MASTER TO MASTER_HOST='10.200.210.187', MASTER_USER='resync',Master_Port=3306, MASTER_PASSWORD='Resync$123', MASTER_AUTO_POSITION=1;

7、RESET SLAVE

必须reset，否则会报错：ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

8、START SLAVE

9、SHOW SLAVE STATUS\G
如下三项值说明同步正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

当Slave_IO_Running, Slave_SQL_Running为YES,
且Slave_SQL_Running_State 为Slave has read all relay log; waiting for more updates时表示成功构建复制链路

从库设置为read-only
show variables like 'read_only';
调整前状态
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+

调整为只读
set global read_only=on;

10.mysql> show master status;检查主库状态

use tmp
create table tb1 ( id int);
create table tb2 ( id int, age int, name char(20), primary key(id) );

12. 数据库名字验证
show databases;