mysql复制技术、mysql集群
mysql复制技术/mysql集群https://img2023.cnblogs.com/blog/3165277/202308/3165277-20230815183932805-1063237772.png
准备
1.四台虚拟机都关闭防火墙
systemctl stop firewalld
systemctl disable firewalld2.四台虚拟机都设置好host域名解析,在/etc/hosts文件中添加如下
# vim /etc/hosts
192.168.70.33 master1
192.168.70.34 master2
192.168.70.35 slave1
192.168.70.36 slave2
五台机器都ping一下网络
# ping master1
PING master1 (192.168.70.33) 56(84) bytes of data.
64 bytes from master1 (192.168.70.33): icmp_seq=1 ttl=64 time=0.031 ms
64 bytes from master1 (192.168.70.33): icmp_seq=2 ttl=64 time=0.050 ms
64 bytes from master1 (192.168.70.33): icmp_seq=3 ttl=64 time=0.056 ms3.在干净的环境下安装好mysql（yum、二进制、源码都行）
四台mysql统一密码方便后续使用
# grep password /var/log/mysqld.log
2023-08-13T13:44:46.837257Z 6 A temporary password is generated for root@localhost: 7R_e*/+_)(l8
# mysqladmin -uroot -p password
Enter password:
New password:
Confirm new password: 一、mysql M-S 传统
master设置
1.首先在master1中创建数据库、表 模拟服务器已经运行一段时间
mysql> create database xux;
Query OK, 1 row affected (0.00 sec)
mysql> use xux;
Database changed
mysql> create table xux.t1(id int(10));
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> insert into xux.t1 values(1),(2);
Query OK, 2 rows affected (0.09 sec)
Records: 2Duplicates: 0Warnings: 0
mysql> select * from xux.t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)2.设置master的mysql配置
1.因为是备库中的i/o线程将主库中的二进制日志复制到中继日志中，所以需要打开二进制日志，同时设置好server-id。
在/etc/my.cnf文件下下面添加
vim /etc/my.cnf
log-bin
server-id=1
# tail -2 /etc/my.cnf
log-bin
server-id=1
2.设置好后重启mysql
systemctl restart mysqld3.创建复制用户
mysql> CREATE USER 'rep'@'192.168.70.%' IDENTIFIED BY 'Xuxin_0709';
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'rep'@'192.168.70.%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;4.备份数据
# mysqldump -u root -p 'Xuxin_0709' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql扩展（可忽略）
如果设置--master-data=1 后续slave设置主服务器可以不用填master_log_file和master_log_pos，但是数据库导入必须使用source导入
5.复制给slave
# scp ./2023-08-14-mysql-all.sql master2:/root6.再插入一些数据
mysql> insert into xux.t1 values(3);
Query OK, 1 row affected (0.01 sec)
mysql> select * from xux.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)slave设置
1.测试授权的用户
# mysql -hmaster1 -urep -p'Xuxin_0709'2.开启server-id
# vim /etc/my.cnf
server-id=2
# systemctl restart mysqld3.导入数据库
先进入mysql暂停log-bin日志记录，不记录导入数据的记录
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
导入sql
mysql> source 2023-08-14-mysql-all.sql
或者在linux界面导入
# mysql -uroot -p < /2023-08-14-mysql-all.sql
mysql> select * from xux.t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)4.查看sql中的日志master_log_file和master_log_pos
# vim 2023-08-14-mysql-all.sql
https://img2023.cnblogs.com/blog/3165277/202308/3165277-20230815184004691-645774899.png
5.设置主服务器
mysql> change master to master_host='master1',master_user='rep',master_password='Xuxin_0709',master_log_file='mysql01-bin.000002',master_log_pos=157;6.启动从设备并查看slave状态
mysql> start slave;
show slave status\Ghttps://img2023.cnblogs.com/blog/3165277/202308/3165277-20230815184034767-1891759040.png
7.查看数据可以发现数据已同步
mysql> select * from xux.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.01 sec)测试
再来测试一下
在master1 xux.t1表中插入数据 看看slave机器会不会同步数据
1.在master1机器登录mysql插入数据
mysql> insert into xux.t1 values(4);
Query OK, 1 row affected (0.01 sec)2.在slave机器查看数据
mysql> select * from xux.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)二、mysql M-S GTID
准备
为接下来新的同步实验需要将之前的同步设置清空，也就是重置master2的mysql
systemctl stop mysqld
rm -rf /var/lib/mysql/*
systemctl start mysqld
grep password /var/log/mysqld.log
mysql -uroot -p'w#lHVC_Rc30r'
mysqladmin -uroot -p passwordmaster设置
1.启动二进制日志，服务器ID，GTID
由于我们之前配置过了id这里只需配置GTID。配置完后重启mysqld
# vim /etc/my.cnf
gtid_mode=ON
enforce_gtid_consistency=1
# systemctl restart mysqld2.授权复制用户rep(上面M-S传统已配置 这里忽略)
3.备份数据
为了实验方便这里把之前的sql备份文件删掉
# rm -rf 2023-08-14-mysql-all.sql
备份一份新的
# mysqldump -u root -p --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql
传给master2
# scp ./2023-08-14-mysql-all.sql master2:/root/4.模拟数据变化
mysql> insert into xux.t1 values(5);
Query OK, 1 row affected (0.01 sec)
mysql> select * from xux.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)slave设置
1.测试rep用户是否可用
# mysql -hmaster1 -urep -p'Xuxin_0709'2.启动二进制日志，服务器ID,GTID,配置完后重启mysqld
# vim /etc/my.cnf
server-id=2
log_bin
gtid_mode=ON
enforce_gtid_consistency=1
# systemctl restart mysqld3.恢复手动同步数据
先关闭log-bin日志记录mysql> set sql_log_bin=0;导入数据mysql> source /root/2023-08-14-mysql-all.sql;查询一下数据mysql> select * from xux.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)4.设置主服务器
mysql> change master to master_host='master1',master_user='rep',master_password='Xuxin_0709',master_auto_position=1;5.启动slave并查看数据
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> select * from xux.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.01 sec)三、mysql M-M-S-S
考虑到主服务器如果宕机，那个整个集群的写入能力将会受到影响，那么可以设置双主多从的方式，为数据做好保障。
M-M
因为前面大多数设置已经设置好了，下面是在上面已完成步骤的基础下操作
1.在master2上进行授权 （可忽略）
查看user表 发现之前已经创建用户并授权了 如果没有就创建用户并授权与上述操作一致不再赘述
mysql> select user,host from mysql.user;
+------------------+--------------+
| user | host |
+------------------+--------------+
| rep | 192.168.70.% |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+--------------+
5 rows in set (0.01 sec)
在master1上测试
# mysql -urep -hmaster2 -p'Xuxin_0709';2.配置master1
mysql> change master to master_host='master2',master_user='rep',master_password='Xuxin_0709',master_auto_position=1; //基于GTID
让master1也有从的功能，所以现在master1 master2都即是主，也是从。
mysql> start slave;3.测试
在master2中插入数据
mysql> insert into xux.t1 values(6);
在master1看数据是否会同步
mysql> select * from xux.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.00 sec)S-S
1.先把master1数据备份 传给slave1 slave2
# mysqldump -u root -p'Xuxin_0709' --all-databases --single-transaction --master-data=2 --flush-logs > mmss-mysql-all.sql
# scp mmss-mysql-all.sql slave1:/root
# scp mmss-mysql-all.sql slave2:/root2.在slave1 slave2 导入数据（两台机器都导入数据）
# mmysql -uroot -p'Xuxin_0709' < mmss-mysql-all.sql
# mmysql -uroot -p'Xuxin_0709' < mmss-mysql-all.sql3.配置slave1 slave2 （从服务器没必要开启log-bin）并重启
# vim /etc/my.cnf
server-id=3
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
# vim /etc/my.cnf
server-id=4
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
# systemctl restart mysqld
# systemctl restart mysqld4.设置主服务器
slave1
mysql> change master to master_host='master1',master_user='rep',master_password='Xuxin_0709',master_auto_position=1 for channel 'master1';
mysql> change master to master_host='master2',master_user='rep',master_password='Xuxin_0709',master_auto_position=1 for channel 'master2';
mysql> start slave;
mysql> show slave status\Gslave2
mysql> change master to master_host='master1',master_user='rep',master_password='Xuxin_0709',master_auto_position=1 for channel 'master1';
mysql> change master to master_host='master2',master_user='rep',master_password='Xuxin_0709',master_auto_position=1 for channel 'master2';
mysql> start slave;
mysql> show slave status\G5.测试
在master1 master2 变更数据
master1
mysql> insert into xux.t1 values(7);
查看master2 slave1 slave2数据是否变化
mysql> select * from xux.t1; master2
mysql> insert into xux.t1 values(8);
查看master1 slave1 slave2数据是否变化
mysql> select * from xux.t1;
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]