MySQL测试环境搭建主主集群
主机参数调调整
vi /etc/sysconfig/selinux
#查看是否SELINUX=disabled
#禁用firewalld
service firewalld stop
架构 | IP | hostname |
---|---|---|
主1数据库 | 192.168.206.3 | zhou |
主2数据库 | 192.168.206.4 | bin |
主1数据库安装
# 进入目录
cd /opt
# 下载安装包
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
# 解压
tar -xvf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
# 拷贝到/usr/local
mv /opt/mysql-8.0.20-linux-glibc2.12-x86_64 /usr/local
# 进入/usr/local
cd /usr/local
# 修改名称为mysql-8.0.20
mv mysql-8.0.20-linux-glibc2.12-x86_64 mysql-8.0.20
# 创建存放数据文件夹
mkdir /usr/local/mysql-8.0.20/data
# 创建用户及用户组
groupadd mysql
useradd -g mysql mysql
# 授权
chown -R mysql.mysql /usr/local/mysql-8.0.20
# 初始化数据库(记录临时密码)
cd /usr/local/mysql-8.0.20/
./bin/mysqld --user=mysql --lower-case-table-names=1 --basedir=/usr/local/mysql-8.0.20/ --datadir=/usr/local/mysql-8.0.20/data/ --initialize ;
# 配置my.cnf
vi /etc/my.cnf
# 清空,使用下面内容
// 文件内容开始
[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
character-set-server=utf8
lower-case-table-names=1
default_authentication_plugin=mysql_native_password
server-id = 1
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=1
replicate-do-db=test_db
// 文件内容结束
# 建立Mysql服务
cp -a ./support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql
# 检查服务是否生效
chkconfig --list mysql
# 启动、停止、重启
service mysql start
service mysql stop
service mysql restart
# 创建软连接
ln -s /usr/local/mysql-8.0.20/bin/mysql /usr/bin
# 登录(使用临时密码)
mysql -uroot -p
# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
# 退出,使用新密码登录
quit
mysql -uroot -p
# 修改root权限,增加远程连接
use mysql
update user set host ='%' where user='root';
alter user 'root'@'%' identified with mysql_native_password by '123456';
flush privileges;
# 退出
quit
主2数据库安装
和主1数据库安装一致,但配置文件内容不同
# 配置my.cnf
vi /etc/my.cnf
# 清空,使用下面内容
// 文件内容开始
[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
character-set-server=utf8
lower-case-table-names=1
default_authentication_plugin=mysql_native_password
server-id = 2
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=2
replicate-do-db=test_db
// 文件内容结束
# 主数据库服务器测试从数据库
mysql -uroot -p -h192.168.206.4 -P3306
# 从数据库服务器测试主数据库
mysql -uroot -p -h192.168.206.3 -P3306
设置主从架构
把主1这台设置为主库
#登录数据库
mysql -uroot -p
#创建复制用户
#这里设置的用户名是:zhou,密码 123456
create user 'zhou'@'%' identified with mysql_native_password by '123456';
#创建用户 mysql8.0中密码需要填写mysql_native_password
grant replication slave on *.* to 'zhou'@'%';
#分配权限
flush privileges;
#刷新权限
#查看主1的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 2037 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
设置主2为从库
mysql -uroot -p
#在主2上执行同步语句
change master to master_host='192.168.206.3',master_user='zhou',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=2037;
# 开始同步
start slave;
# 若出现错误,则停止同步,重置后再次启动
stop slave;
reset slave;
start slave;
# 查询Slave状态
show slave status\G
#出现yes则成功
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.206.4
Master_User: bin
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 4968
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test_db
测试
# 在主数据库创建数据库test
create database test_db;
# 从数据库查看
show databases;
# 在主数据库创建表
use test_db;
create table t_user(id int, name varchar(20));
# 插入数据
insert into t_user values(1, 'C3Stones');
# 在从数据库查看
use test_db;
select * from t_user;
# 其他删改查操作请自行测试
把主2这台设置为主库
mysql -uroot -p
#先对主2上进行授权
create user 'bin'@'%' identified with mysql_native_password by '123456';
#创建用户 mysql.80中密码需要填写mysql_native_password
grant replication slave on *.* to 'bin'@'%';
#分配权限
flush privileges;
#查看主2的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 4968 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
把主1这台设置为从库
mysql> change master to master_host='192.168.206.4', master_user='bin', master_password='123456', master_log_file='mysql-bin.000003', master_log_pos=4968;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
#启动同步
start slave;
# 若出现错误,则停止同步,重置后再次启动
stop slave;
reset slave;
start slave;
# 查询Slave状态
show slave status\G
测试
# 插入数据
use test_db;
insert into t_user values(2, 'tones');
# 在从数据库查看
use test_db;
select * from t_user;