MySQL 5.7 主从复制架构搭建
参考
https://www.jianshu.com/p/208667156faf
准备环境
使用的系统环境是 Red Hat Enterprise Linux Server release 7.4 (Maipo)最小化安装版本
[[email protected] ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.4 (Maipo)
关闭 Selinux
并且进行更新系统 yum update -y ; yum upgrade -y
主数据库： 192.168.23.52
从数据库： 192.168.23.53
作者：AllenWGZ
链接：https://www.jianshu.com/p/208667156faf
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
master配置
cp /etc/my.cnf /etc/my.cnf.bak
[[email protected] ~]# vi /etc/my.cnf
server_id=31
#binlog-ignore-db=mydql
#binlog-ignore-db=information_schema
#binlog-ignore-db=performance_schema
#log-bin=dev-slave-bin
log-bin=dev-bin
binlog_cache_size=1m
expire_logs_days=7
slave_skip_errors=1062
保存并且重启数据库
systemctl restart mysqld
锁表
[[email protected] ~]# mysql -uroot -p
Enter password:
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
备份：
[[email protected] ~]# mysqldump -u root -p --databases eagleyesrisk_db > eagleyesrisk_db.sql
登陆数据库查看Pos值并且解锁数据表
mysql> show master status ;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| dev-bin.000001 | 154 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| dev-bin.000001 | 1970 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
退出数据库并且进行备份
[[email protected] ~]# mysqldump -u root -p --databases eagleyesrisk_db > eagleyesrisk_db.sql
Enter password:
[[email protected] ~]#
登陆数据库查看Pos值并且解锁数据表
show master status ;
unlock tables;
将备份数据库scp到从数据库的主机上面
[[email protected] ~]# scp eagleyesrisk_db.sql [email protected]:/root/
[email protected]'s password:
eagleyesrisk_db.sql 100% 4062KB 106.5MB/s 00:00
[[email protected] ~]#
登陆master数据库，创建备份使用的用户，并且赋予相应的权限
grant replication slave, replication client on *.* to 'repl'@'192.170.106.%' identified by 'slavePassword!123';
flush privileges ;
slave配置
cp /etc/my.cnf /etc/my.cnf.bak
[[email protected] ~]# vi /etc/my.cnf
server_id=33
#binlog-ignore-db=mydql
#binlog-ignore-db=information_schema
#binlog-ignore-db=performance_schema
#log-bin=dev-slave-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=dev-relay-bin
#log_slave_updates=1
read_only=1
保存并且重启数据库
systemctl restart mysqld
登陆数据库，并且还原master的数据到slave
[[email protected] ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> source /root/eagleyesrisk_db.sql
Query OK, 0 rows affected (0.00 sec)
从数据库连接到主数据库
mysql> change master to master_host='192.170.106.31',master_user='repl',master_password='slavePassword!123',master_port=3306,master_log_file='dev-bin.000001',master_log_pos=1970,master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
查看slave状态
show slave status \G
启动slave后并且再查看状态
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
主从复制重置
在做mysql主从复制的时候，在从服务器上进行show slave status\G;查看时 Slave_IO_Running: No与Slave_SQL_Running: No，然后stop slave;后stat slave出现如下错误：
mysql> start slave;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
解决方案：
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> reset slave;（reset重置）
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host='192.168.10.155',master_user='slave1',master_password='123456',master_log_file='mysql-bin.000023',master_log_pos=107;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
然后在执行show slave status\G;查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这样就ok了，在网上查看出现这样的原始可能是以前的mysql做过主从！