MYSQL 搭建主从（主备）节点

一、 MYSQL安装

1. 检查依赖

rpm -qa | grep libaio

2. mysql包准备

# 创建mysql安装目录
mkdir -p /usr/local/mysql
cd /usr/local/mysql

# 下载mysql安装包
curl -L -O  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz

tar -zxvf mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.43-linux-glibc2.12-x86_64/* .

# 创建mysql依赖目录及文件
mkdir -p {data,logs}
touch ./logs/mysqld.log

3. 配置文件

vi /etc/my.cnf

!includedir /etc/my.cnf.d
[mysqld]

datadir=/usr/local/mysql/data
basedir=/usr/local/mysql

user=root
port=3306
character-set-server=utf8

server-id=1
max_connections=1000

log-bin=mysql-bin
binlog_format=MIXED
max_binlog_size = 512M
expire_logs_days = 3
binlog_ignore_db = mysql,performance_schema,information_schema

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 取消密码验证
#skip-grant-tables

symbolic-links=0

[mysqld_safe]

log-error=/usr/local/mysql/logs/mysqld.log
pid-file=/usr/local/mysql/data/ahtech.pid

4. 初始化及配置

./bin/mysqld  --defaults-file=/etc/my.cnf --initialize

ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
mv /usr/bin/mysql /usr/bin/mysql.old
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

5. 启动mysql及修改默认密码

service mysqld start

# 密码在初始化那一步有输出打印
mysql -u root -p

ALTER USER USER() IDENTIFIED BY '12345678';

6. 配置开机自启动

systemctl restart mysqld 
/sbin/chkconfig mysqld on
systemctl daemon-reload

二、 配置主从

1. 主节点配置

# 修改主节点配置文件
vi /etc/my.cnf

# 确认一下内容
[mysqld]
server-id = 1
log-bin = mysql-bin

# 重启生效
systemctl restart mysql

# 配置主
mysql -u root -p
CREATE USER 'repl'@'备节点ip' IDENTIFIED BY 'repl-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'备节点ip';
FLUSH PRIVILEGES;

# 查看主 二进制日志文件File和位置Position
SHOW MASTER STATUS;

2. 备节点配置

# 修改主节点配置文件
vi /etc/my.cnf

# 确认以下内容
[mysqld]
server-id = 2

# 重启生效
systemctl restart mysql

# 配置备
mysql -u root -p
MASTER_PASSWORD='repl-password', MASTER_LOG_FILE='File', MASTER_LOG_POS=Position;
START SLAVE;

# 查看备状态
SHOW SLAVE STATUS\G;

# Slave_IO_Running和Slave_SQL_Running的值为Yes
# 测试数据后，Last_Error Last_Errno为空即可