改:UUID:vi /var/lib/mysql/auto.cnf
GTID双主双从
MA和MB互为主从
清理环境:
没有数据库的服务器下载:
yum -y install lftp
systemctl start mysqld
修改密码(略)
第一台
本地解析
拷贝
ping 各个域名
修改配置文件
vi /etc/my.cnf
log-bin = my1log
server-id = 1
gtid_mode=ON
enforce_gtid_consistency=1
[root@master-A ~]# mysql -u root -pQianFeng@123
mysql> grant all on *.* to 'slave'@'%' identified by 'QianFeng@123';
mysql>exit
重启服务
[root@master-1 ~]# systemctl restart mysqld
[root@master-1 ~]# mysql -u root -pQianFeng@123
mysql> change master to
-> master_host='master-2',
-> master_user='slave',
-> master_password='QianFeng@123',
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
第二台
修改配置文件
[root@master-2 ~]# vim /etc/my.cnf
log-bin = my2log
server-id = 2
gtid_mode=ON
enforce_gtid_consistency=1
[root@master-2 ~]# mysql -u root -pQianFeng@123
mysql> grant all on *.* to 'slave'@'%' identified by 'QianFeng@123';
mysql> flush privileges;
[root@master-2 ~]# systemctl restart mysqld
[root@master-B ~]# mysql -u root -pQianFeng@123
mysql> change master to
-> master_host='master-1',
-> master_user='slave',
-> master_password='QianFeng@123',
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
第三台
edit
mysql> start slave;
mysql> show slave status\G
第四台
[root@slave-2 ~]# vim /etc/my.cnf
log-bin = my4log
server-id = 4
gtid_mode=ON
enforce_gtid_consistency=1
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_recovery = on[root@slave-2 ~]# systemctl restart mysqld
[root@slave-2 ~]# mysql -u root -pQianFeng@123
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G
读写分离
2.读写分离集群部署
A:数据库集群部署(略)
单主单从;多主多从等均可
B:Mycat部署
新机器,不需要安装mysql 多加一台内存2G mycat服务器
改名:mycat
上传jdk8u
上传mycat
改名
设置环境变量:
[root@mycat ~]# vi /etc/profile
JAVA_HOME=/usr/local/java
PATH=$JAVA_HOME/bin:$PATH
export JAVA_HOME PATH
[root@mycat ~]# source /etc/profile
[root@mycat ~]# java -version //验证
java version "1.8.0_211"
安装mycat:
[root@mycat ~]# tar xf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -C /usr/local/
server.xml//配置mycat
schema.xml //配置连接数据库相关内容
设置环境变量(可不做):
[root@mycat ~]# vi ~/.bash_profile //~当前用户家目录 //
PATH=$PATH:$HOME/bin:/usr/local/mycat/bin
[root@mycat ~]# source ~/.bash_profile
master-A数据库cloud
[root@master-A ~]# mysql -u root -pQianFeng@123
mycat
<!-- -->//注释
schema 去掉2个 名字为db
一个mycat用户对应一个逻辑库
权限控制
3. Firewall标签
防火墙的设置,也就是在网络层对请求的地址进行限制,主要是从安全角度来保证Mycat不被匿名IP进行访问
<firewall>
<whitehost>
<host host="127.0.0.1" user="mycat"/>
<host host="127.0.0.2" user="mycat"/>
</whitehost>
<blacklist check="false">
</blacklist>
</firewall>
schema.xml
E:启动服务
[root@mycat conf]# mycat start
Starting Mycat-server...
[root@mycat conf]# jps
1494 WrapperSimpleApp
1528 Jps[root@mycat conf]# ss -antpl //看8066
验证:
主:
[root@master-1 ~]# mysql -u shop -p123456 -P 8066 -h 10.0.0.47
mysql> show databases;
mysql> use shop
mysql> show tables;
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.03 sec)mysql> insert into t1 values(2);
Query OK, 1 row affected (0.01 sec)
从服务数据验证:
[root@slave-2 ~]# mysql -u root -pQianFeng@123
mysql> show databases;
mysql> show databases;
mysql> use shop
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> show tables;mysql> select * from t1;
schema.xml: