福州网站建站建设,摄影师网站推荐,手机网站百度关键词排名,文化传播网站模板MySQL测试环境搭建主主集群
主机参数调调整
vi /etc/sysconfig/selinux
#查看是否SELINUXdisabled
#禁用firewalld
service firewalld stop架构IPhostname主1数据库192.168.206.3zhou主2数据库192.168.206.4bin
主1数据库安装
# 进入目录
cd /opt# 下载安装包
wget https:/…MySQL测试环境搭建主主集群
主机参数调调整
vi /etc/sysconfig/selinux
#查看是否SELINUXdisabled
#禁用firewalld
service firewalld stop架构IPhostname主1数据库192.168.206.3zhou主2数据库192.168.206.4bin
主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 --usermysql --lower-case-table-names1 --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-serverutf8
lower-case-table-names1
default_authentication_pluginmysql_native_passwordserver-id 1
log-binmysql-bin
auto_increment_increment2
auto_increment_offset1
replicate-do-dbtest_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 rootlocalhost IDENTIFIED WITH mysql_native_password BY 123456;# 退出使用新密码登录
quit
mysql -uroot -p# 修改root权限增加远程连接
use mysql
update user set host % where userroot;
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-serverutf8
lower-case-table-names1
default_authentication_pluginmysql_native_passwordserver-id 2
log-binmysql-bin
auto_increment_increment2
auto_increment_offset2
replicate-do-dbtest_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_host192.168.206.3,master_userzhou,master_password123456,master_log_filemysql-bin.000002,master_log_pos2037;# 开始同步
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 eventMaster_Host: 192.168.206.4Master_User: binMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 4968Relay_Log_File: localhost-relay-bin.000002Relay_Log_Pos: 324Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_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_host192.168.206.4, master_userbin, master_password123456, master_log_filemysql-bin.000003, master_log_pos4968;
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;
