目录
使用master1 MYSQL 插入数据,在master2查看你msyql数据会实时更新
4-5 返回主服务器(master1)更新数据,在从服务器(master2 )观察同步。
概念
准备5台虚拟机,全新安装msyql,否则会报错
一、准备环境master1(M-S手动)
1-1 写入hosts
准备环境5台 机器,一台不安装mysql,并对4台机器同步写入hosts IP数据
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.85.128 master1
192.168.85.129 master2
192.168.85.130 slave1
192.168.85.131 slave2
1-2 开启二进制日志 mastrt1
1-3 创建复制授权用户
mysql> grant replication slave,
replication client on *.* to 'rep'@'192.168.85.%'
identified by 'QianFeng@123';
1-4 备份master1数据库的数据
mysqldump -p'YiFan@123'
--all-databases --single-transaction
--master-data=2 --flush-logs > 'date +%F'-mysql-all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
scp -r scp -r 2020-1-1-mysql-all.sql master2:/tmp /**复制并发给master2 */
1-5 添加数据
insert into master1db.master1tab values (33333333);
insert into master1db.master1tab values (55555555);
二、master2 测试
2-1 在master2 上测试,
使用创建复制用户“rep”
mysql -h master1 -urep -p'QianFeng@123'
2-2 启动服务器序号
vim /etc/my.cnf
server-id=2
systemctl restart mysqld
mysql -uroot -p'QianFeng@123' /**测试服务器是否修改正确,能否登录*/
2-3 手动同步数据
set sql_log_bin=0;
mysql> source /tmp/2020-1-1-mysql-full.sql /*这里使用的master2的mysql root用户*/
2-4 设置主服务器
mysql> change master to
master_host='master1',
master_user='rep',
master_password='QianFeng@123',
master_log_file='localhost-bin.000002',
/*这里二进制文件查看(cat /tmp/*.sql的备份文件)*/
master_log_pos=154;
2-5 启动从设备
mysql>start slave;
2-6 查看状态
show slave status\:G;
2-7 使用master1插入数据测试
使用master1 MYSQL 插入数据,在master2查看你msyql数据会实时更新
三、 MYSQL集群(M-S)自动化一主一从
概念
3-1 清理环境master2
与实验1功能相同,不同之处,使用了
“gtid_mode=ON
enforce_gtid_consistency=1”
该属性自动记录position位置。不需要手动指定了。
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -rf /var/lib/mysql/*
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# grep password /var/log/mysqld.log
2022-05-16T10:51:35.286309Z 1 [Note] A temporary password is generated for root@localhost: <+-opOsIy9lo
mysqladmin -p'da=PEh7hC*>/' password'YiFan@123'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Your password has expired. To log in you must change it using a client that supports expired passwords.'
[root@localhost ~]# mysql -uroot -p'da=PEh7hC*>/'
mysql> set password=password('YiFan@123');
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
3-2 主master1
vim /etc/my.cnf
log_bin
server-id=1
gtid_mode=ON /********开启自动模式******/
enforce_gtid_consistency=1 /****强制开启***/
systemctl restart mysqld