MySQL 主从复制
2022-07-03
主从复制原理
上图
mysql 5.6 主从配置、手动切换主从
主从配置
旧数据库配置（需要迁移的数据库服务器）
# 查看主状态
mysql> show master status;
# 查看从状态
mysql> show slave status\G;
# 主数据库 dump 数据 sql
# dump master 数据
/data/mysql3306/bin/mysqldump -P3306 -uroot -p -S /data/mysql3306/var/mysql.sock --routines --single-transaction --master-data=2 --database vt_db > /backup/3306vt_db.sql
# ⚠️：参数说明
# --routines 导出存储过程以及自定义函数。
# --single-transaction 该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。
# --master-data=2 该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables选项。
新数据库配置（需要迁移到该服务器）
#######################
##### 安装数据库前准备
# 创建 用户组、用户
groupadd -g 501 mysql
useradd -u 501 mysql -g 501
# 下载 mysql 5.6.17 二进制安装包；这里使用自己的源下载，mysql 国内网速不大快（貌似）
# mysql 官网地址：wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.17-linux-glibc2.5-x86_64.tar.gz
wget https://download.wfc.center/Mysql/5.6/mysql-5.6.17-linux-glibc2.5-x86_64.tar.gz
tar xf mysql-5.6.17-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.6.17-linux-glibc2.5-x86_64 /data/mysql3306
# 安装依赖包
yum -y install *Dumper*
######################
#### mysql 二进制安装、初始化
# 解压之后，修改目录
# mysql 初始化
# 初始化不会产生password，所以可以直接登录
# 这里的 my.cnf 直接从线上 数据库 拷贝的；就不贴出来了；
sudo /data/mysql3306/scripts/mysql_install_db --defaults-file=/data/mysql3306/my.cnf --user=mysql --basedir=/data/mysql3306 --datadir=/data/mysql3306/data
# 配置管理脚本
参考本书 Mysql启动脚本
# 启动 数据库
systemctl start mysqld3306.service
#################
# 优化mysql安全，
# 因为 是 多实例 mysql 所以 执行 /data/mysql3306/bin/mysql_secure_installation 时会报错，所以这里手动优化 msyql test 库、用户秘密
# 直接回车登录；因为初始化不会产生密码
/data/mysql3306/bin/mysql -uroot -p -P 3306 -S /data/mysql3306/var/mysql.sock
# 删除test库
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
# 查看登录用户，
mysql> select user,host,password from mysql.user;
+------+---------------------------------------------+----------+
| user | host | password |
+------+---------------------------------------------+----------+
| root | localhost | |
| root | ip-10-21-155-195.us-west-1.compute.internal | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | ip-10-21-155-195.us-west-1.compute.internal | |
+------+---------------------------------------------+----------+
6 rows in set (0.00 sec)
# 优化登录用户
delete from mysql.user where user='root' and host='localhost';
delete from mysql.user where user='root' and host='::1';
delete from mysql.user where user='root' and host='127.0.0.1';
delete from mysql.user where user='root' and host='ip-10-21-155-195.us-west-1.compute.internal';
delete from mysql.user where user=' ' and host='ip-10-21-155-195.us-west-1.compute.internal';
delete from mysql.user where user=' ' and host='localhost';
# 添加用户,并授予用户 执行 GRANT 的权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
#############
# 创建 需要 导入的数据库
# 首先查看 源 数据库 创建 database 语句
mysql> SHOW CREATE DATABASE vt_db;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| vt_db | CREATE DATABASE `vt_db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
# 根据 建库语句，然后 在新数据库中 建库
mysql> CREATE DATABASE vt_db DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
# 导入数据
# 中途可能会遇到导入速度慢的问题；可以参考下网络上的资料自行调整参数
/data/mysql3306/bin/mysql -uroot -p -P 3306 -S /data/mysql3306/var/mysql.sock vt_db < /data/3306vt_db.sql
# 登录 旧数据库 配置 数据同步用户，用于新数据库追平数据
# 登陆数据库配置主从授权
#mysql -uroot -p
# 创建用户，别用root账户，用root账户亲测不可以，创建slave用户赋予该用户slave权限
# %代表全部ip，也可以用固定ip代替。password 为slave的密码；
mysql> grant replication slave on *.* to 'slave1'@'%' identified by 'password';
# 查看 sql 文件 pos 值，
more /backup/3306vt_db.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='master-mysql-bin.000038', MASTER_LOG_POS=517257832;
# 停止slave状态，清除从库主从信息
stop slave;
reset slave all;
# 指定数据库
# 编辑从服务器上的 my.cnf 配置参数,vt_db，zhengjun数据库被复制，test2数据库不会被复制。
[mysqld]
log_bin = mysql-bin #[必须]启动二进制
server_id = 73 #[必须]服务器唯一id，默认是1，一般取ip最后一段
replicate-do-db=vt_db # 指定复制的数据库
replicate-do-db=zhengchengjun # 指定要复制的数据库
replicate-ignore-db=test2 # 不复制该数据库
# 指定表复制
# 编辑从服务器上的配置参数,指定test数据库中的rep_t1表被复制，rep_t3表不会被复制。
replicate-do-table=test.rep_t1 # 指定复制的表
replicate-ignore-table=test.rep_t3 # 跳过该表
# mysql slave 配置主
change master to master_host='10.1.0.180',master_user='slave1',master_password='password',master_log_file='master-mysql-bin.000038',master_log_pos=517257832;
# 启动从
start slave;
# 查看slave 状态
show slave status\G;
.......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.......
# 看到上边两个 yes 表示 msyql 主从建立成功；
手动切换主从
因为旧的数据库要下线，所以这里要启用从库，因为没有配置 高可用，只是配置简单的主从，这里就手动切换主从配置了；
1.停止业务 或者 直接锁表来停止 业务对数据库的写操作；
2.等从数据库同步完毕；在该过程中 修改业务服务器链接的 mysql 数据库地址
select count(1) from tbl_vt_userservice; # 该命令是统计 该表的第一行数据；一定不要 select count(*) from table;
或者
select count(id) from database.tables; # 该命令是统计 表中的 id 列
3.slave 追平数据后，找一个大一点儿的表同意一下行数，两边保持数据一直之后；停止从服务器的 slave
stop slave;
reset slave all; 或者 reset slave;
4. 重启业务程序；这样就切换到从服务器了；