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;