一.无数据情况下:
准备工作,准备两台虚拟机
1.关闭防火墙,selinux
2.时间同步,修改时区:timedatectl set-timezone Asia/Shanghai
3.同步网络时间:ntpdate time.windows.com
4.修改主机名并做本地解析:hostnamectl set-hostname master(主服务器)
hostnamectl set-hostname slave(从服务器)
本地解析:vi /etc/hosts 192.168.203.163 master
192.168.203.164 slave
5.nat模式下 固定IP /etc/sysconfig/network-scripts/ifcfg-ens33
清理环境【两台机器】
[root@slave ~]# yum -y erase `rpm -qa | grep -E "mysql|mariadb"`
[root@slave ~]# rm -rf /etc/my* /var/lib/mysql* /var/log/mysql*
#检查一下
[root@slave ~]# [[ ! -f /etc/my.cnf ]] && [[ ! -d /var/lib/mysql ]] && [[ ! -f /usr/bin/mysql ]] && echo "数据库已清除" || echo "数据库未清理"
安装MySQL
初始化数据库:systemctl start mysql
修改初始密码:mysqladmin -p"`awk '/temporary password/{p=$NF}END{print p}' /var/log/mysqld.log`" password '123'
(查找初始密码:`awk '/temporary password/{p=$NF}END{print p}' /var/log/mysqld.log)
配置MySQL主从
主库
1.创建binlog日志存放路径
[root@master ~]# mkdir -p /data/binlog
2.修改权限
[root@master ~]# chown -R mysql.mysql /data/binlog
3.修改MySQL配置文件
[root@master ~]# vim /etc/my.cnf
[mysqld]
server-id = 163
log-bin=/data/binlog/mysql-bin
4.重启mysql
[root@master ~]# systemctl restart mysqld
查询master状态
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 596
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.01 sec)
授权账号:mysql> grant replication slave on *.* to 'replication'@'%' identified by"123";
刷新:mysql> flush privileges;
从库
1.修改MySQL配置文件
[mysqld]
server-id = 1642.重启mysql:[root@slave ~]# systemctl restart mysqld
3.配置从库:
mysql> change master to
-> master_host='master',
-> master_port=3306,
-> master_user='replication',
-> master_password='Qianfeng@123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=596;4.启动slave: mysql> start slave;
? change master to 查看配置主库连接信息
5.查看slave状态:
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 596 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes
在此刻 Slave_IO_Running: Yes ,Slave_SQL_Running: Yes 都为yes则证明连接正常。
重新配置主从:mysql> stop slave;
mysql> reset slave;
二.有数据情况下:清理环境,重新初始化
主库
1.创建binlog日志存放路径:[root@master ~]# mkdir -p /data/binlog
2.修改权限:[root@master ~]# chown -R mysql.mysql /data/binlog
3.修改mysql配置文件: [root@master ~]# vim /etc/my.cnf
[mysqld]
server-id = 163
log-bin=/data/binlog/mysql-bin4.重启mysql:[root@master ~]# systemctl restart mysqld
从库
1.修改mysql配置文件:[root@slave ~]# vim /etc/my.cnf
[mysqld]
server-id = 1642.重启mysql:[root@slave ~]# systemctl restart mysqld
主库:锁表备份,新开一个窗口去备份数据,备份完成以后解锁,添加复制账户
mysql> flush tables with read lock; 锁表
新开窗口备份操作
mysqldump -uroot -pQianfeng@123 -A --master-data > all.sql
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by '123';
备份完成以后解锁
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 589
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
mysql> unlock tables;
从库配置主从:导入主备份sql保证数据一致性
mysql> CHANGE MASTER TO
-> MASTER_HOST='master',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123',
-> MASTER_log_file=mysql-bin.000004,
-> MASTER_log_pos=589;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 589
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 802
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes