mysql主从搭建

最新推荐文章于 2021-02-17 21:32:13 发布

牛奶二

最新推荐文章于 2021-02-17 21:32:13 发布

阅读量611

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/a13567054138/article/details/75513143

版权

mysql 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

（以下搭建是主库是新建的无数据，所以从库无需同步主库数据）

1.添加MySQL 用户,创建相应的目录

useradd -m mysql
mkdir -p /data/mysqltest1
chown -R mysql:mysql /data/mysqltest 主节点数据文件目录
mkdir -p /data/mysqltest1
chown -R mysql:mysql /data/mysqltest1 从节点数据文件目录

2.解压二进制软件包
tar -xzvf mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz -C /app/

3.配置mysql.cnf 配置参数文件
1）mysql.cnf 主节点
basedir='/app/mysql'
datadir='/data/mysqltest'
secure_file_priv=/tmp
log-bin=mysql-bin
server-id=221
port=3306
socket=/tmp/mysql.sock

2）mysql.cnf 从节点
basedir='/app/mysql'
datadir='/data/mysqltest1'
secure_file_priv=/tmp
log-bin=mysql-bin
server-id=222
port=3307
socket=/tmp/mysql1.sock

4.配置mysql用户环境变量
[mysql@milk ~]$ cat .bash_profile
# .bash_profile
export PATH=/app/mysql/bin:$PATH
export MYSQL_HOME=/app/mysql
export MYSQL_PS1="\\u@\\h [\\d] \\r:\\m:\\s>>>"

5.初始化并启动主从节点
[mysql@milk ~]$ mysqld --defaults-file=/app/mysql/mysql1.cnf --initialize-insecure
[mysql@milk ~]$ mysqld --defaults-file=/app/mysql/mysql.cnf --initialize-insecure
[mysql@milk ~]$ mysqld --defaults-file=/app/mysql/mysql.cnf &
[mysql@milk ~]$ mysqld --defaults-file=/app/mysql/mysql1.cnf &

6.主节点建立帐户并授权slave（为了主从切换，从节点也建立相关账号）
[mysql@milk ~]$mysql -S /tmp/mysql.sock

root@localhost [(none)] 12:00:26>>> GRANT REPLICATION SLAVE ON *.* to 'mysqlzc'@'%' identified by 'zc123456';

[mysql@milk ~]$mysql -S /tmp/mysql1.sock
root@localhost [(none)] 12:01:26>>> GRANT REPLICATION SLAVE ON *.* to 'mysqlzc'@'%' identified by 'zc123456';

7.主节点查询master的状态

[mysql@milk ~]$mysql -S /tmp/mysql.sock
root@localhost [(none)] 12:00:26>>>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1033 | | | |
+------------------+----------+--------------+------------------+-------------------+

8.配置从节点slave
[mysql@milk ~]$mysql -S /tmp/mysql1.sock
change master to master_host='192.168.88.8',master_user='mysqlzc',master_port=3306，master_password='zc123456', master_log_file='mysql-bin.000002',master_log_pos=1033;

9.测试主从同步
[mysql@milk ~]$mysql -S /tmp/mysql.sock
root@localhost [(none)] 12:03:26>>> create database milk;
Query OK, 1 row affected (0.01 sec)
root@localhost [(none)] 12:03:28>>> create table milk.a1 (id int,name char(33));
Query OK, 0 rows affected (0.07 sec)
root@localhost [(none)] 12:03:36>>> insert into milk.a1 values (1,'milk');
Query OK, 1 row affected (0.02 sec)
登录从节点查询
[mysql@milk ~]$mysql -S /tmp/mysql1.sock
root@localhost [(none)] 12:03:38>>>select * from milk.a1;
+------+------+
| id | name |
+------+------+
| 1 | milk |
+------+------+

1 row in set (0.00 sec)

10.主从切换
从库执行 stop slave io_thread;
激活从库
执行stop slave; 和 reset master;
将原来主库变为从库
reset slave all;
change master to master_host='192.168.88.8',master_user='mysqlzc',master_port=3307,master_password='zc123456';