mysql主从模式搭建

#########--------mysql安装--------#########

# 下载安装包 # 解压上传并修改存放文件名

wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.27-el7-x86_64.tar.gz

tar -zxvf mysql-8.0.27-el7-x86_64.tar.gz

# 添加用户组和用户

groupadd mysql && useradd -r -g mysql mysql

# 创建数据目录并赋予权限

mkdir -p /data/mysql

chown mysql:mysql -R /data/mysql

# 修改配置文件  vim /etc/my.cnf

[mysqld]

bind-address=0.0.0.0

port=3306

user=mysql

basedir=/usr/local/mysql

datadir=/data/mysql

socket=/tmp/mysql.sock

log-error=/data/mysql/mysql.err

pid-file=/data/mysql/mysql.pid

#character config

character_set_server=utf8mb4

symbolic-links=0

explicit_defaults_for_timestamp=true

# 初始化

cd /usr/local/mysql/bin/

./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --user=mysql --initialize

# 查看初始密码

cat /data/mysql/mysql.err

# 启动mysql

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

service mysql start

# 登录mysql-master   登录提示缺少libncurses.so.5 直接yum install libncurses*

/usr/local/mysql/bin/mysql -u root -p

# 修改密码

#(使用随机密码：set generated_random_password_length=12;                                                  ALTER USER 'root'@'%' IDENTIFIED BY RANDOM PASSWORD;)

mysql> ALTER USER USER() IDENTIFIED BY '#Ab123456';

# 创建账户并授权--------grant all on 数据库名.数据库表 to 用户名@'%'  identified by "密码";  all 可以替换为 select,delete,update,create,drop

CREATE USER 'slave1'@'%' IDENTIFIED BY '#Ab123456';

GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';

ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '#Ab123456';

flush privileges;

# 刷新规则允许外部访问

use mysql  #选择访问mysql库

update user set host = '%' where user = 'slave1'; 　　　　 #使slave1能再任何host访问

FLUSH PRIVILEGES; 　

# 退出重启数据库服务

service mysql restart

# 同样的步骤安装从数据库

# 主数据库配置文件修改

vim /etc/my.cnf

server-id = 1 # 节点ID，确保唯一,用来区分服务器的编号

log-bin = master-bin #开启mysql的binlog日志功能(就会创建二进制文件)

# 从数据库配置文件修改

vim /etc/my.cnf

server-id=2

relay-log=mysql-relay-bin

# 登录主数据库,查看log-file和position

show master status;

# 登录从数据库，并按照上述修改密码------建立软连接ln -s /usr/local/mysql/bin/mysql /usr/bin/可以不用据对路径登录

/usr/local/mysql/bin/mysql -u root -p

# 从服务器连接主服务器

stop slave;

change master to master_host='34.201.113.11', master_user='slave1', master_password='#Ab123456', master_log_file='master-bin.000003', master_log_pos=156;

start slave;

# 查看同步状态

show slave status\G;

# 测试

主数据库创建一张表，查看从数据库是否同步

create database test;

### ---云服务器还需要开始3306端口---- ####