mysql主从

 主从复制配置

主从复制配置步骤：

1. 确保从数据库与主数据库里的数据一样
2. 在主数据库里创建一个同步账号授权给从数据库使用
3. 配置主数据库（修改配置文件）
4. 配置从数据库（修改配置文件）

需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明：

数据库角色	IP	应用与系统版本	有无数据
主数据库	172.16.12.128	centos8/redhat8 mysql-5.7	有数据
从数据库	172.16.12.129	centos8/redhat8 mysql-5.7	无数据

3.1 mysql安装

分别在主从两台服务器上安装mysql-5.7版本，此处略过安装步骤，若有疑问请参考《mysql基础》与《mysql进阶》两篇文章。

mysql主从配置

确保从数据库与主数据库里的数据一样

为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中

（3307是主 3308主机是从）

[root@node1 ~]# mysql -uroot -pmingzi.. -h127.0.0.1 -P3307


mysql> grant replication slave on *.* to 'repl'@'192.168.80.22' identified by 'repl123!';
Query OK, 0 rows affected, 1 warning (0.01 sec)


[root@node1 ~]# mysql -urepl -prepl123! -h192.168.80.22 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

//给从数据库设置账号

配置主库

[root@node1 ~]# vim /etc/my.cnf
[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log

server-id=10
log-bin=mysql_bin

重启主库

[root@node1 ~]# systemctl restart my3307
[root@node1 ~]# systemctl restart my3307
[root@node1 ~]# mysql -uroot -pmingzi540.. -h127.0.0.1 -P3307


mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

接下来配置从库

[root@node1 ~]# vim /etc/my.cnf


[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log

server-id = 20
relay-log = xiaoxin

重启从库进去

[root@node1 ~]# systemctl restart my3308
[root@node1 ~]# mysql -uroot -pmingzi540.. -h127.0.0.1 -P3308

接下来配置主从

mysql> change master to
    -> master_host='192.168.80.22',
    -> master_user='repl',
    -> master_password='repl123!',
    -> master_port=3307,
    -> master_log_file='mysql_bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> 


mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


mysql> show slave status\G
Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

//确保这2行是yes


[root@node1 ~]# systemctl stop firewalld
[root@node1 ~]# getenforce 
Disabled

在主库3307创建数据库验证从库

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> 
//此时从库也有数据school