mysql主从
数据库主从：
1.传统主从
2.gtid主从
主从复制配置步骤：
- 确保从数据库与主数据库里的数据一样
- 在主数据库里创建一个同步账号授权给从数据库使用
- 配置主数据库（修改配置文件）
- 配置从数据库（修改配置文件）
主从形式：
一主多从
主主复制
一主一从
多主一从
联级复制
环境说明：
| 数据库角色 | IP | 应用与系统版本 | 有无数据 |
|---|---|---|---|
| 主数据库 | 192.168.207.131 | centos stream8 | 无数据 |
| 从数据库 | 192.168.207.140 | centos stream8 | 无数据 |
| 从数据库 | 192.168.207.141 | centos stream8 | 无数据 |
安装三台主机
mysql> grant replication slave on *.* to 'hzy'@'192.168.207.140' identified by 'hzy123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant replication slave on *.* to 'hzy'@'192.168.207.141' identtified by 'hzy123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
配置主数据库
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin = mysql_bin
server-id = 10
重启服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!查看主库的状态
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@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id = 20
relay-log = myrelay
配置从数据库
[root@localhost ~]# vi /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id = 30
relay-log = myrelay
配置并启动主从复制
mysql> change master to
-> master_host='192.168.207.131',
-> master_user='hzy',
-> master_password='hzy123',
-> master_log_file='mysql_bin.000001',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)开启同步
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes 在主服务器的HZY库的hzy表中插入数据：
mysql> insert into hzy values ('sean',21),('tom',22),('jerry',23);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from hzy;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | sean | 21 |
| 2 | tom | 22 |
| 3 | jerry | 23 |
+----+-------+------+
3 rows in set (0.00 sec)在从数据库中查看数据是否同步：
mysql> select * from hzy;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | sean | 21 |
| 2 | tom | 22 |
| 3 | jerry | 23 |
+----+-------+------+
3 rows in set (0.00 sec)GTID主从配置
查看各个主机服务器中的mysq中数据是否一致
# 主
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
# 从1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
# 从2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)锁表
mysql> FLUSH TABLES WITH READ LOCK;在主数据库里创建一个同步账号授权给从数据库使用
mysql> CREATE USER 'hzy'@'192.168.207.131' IDENTIFIED BY 'hzy123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'hzy123'@'192.168.207.131';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER 'hzy'@'192.168.207.131' IDENTIFIED BY 'hzy123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'hzy123'@'192.168.207.131';
Query OK, 0 rows affected (0.00 sec)配置主数据库
[root@localhost ~]# vim /etc/my.cnf
#添加以下数据
[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log
log-bin=mysql_bin
server_id=10
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=1
binlog_format=row
skip_slave_start=1
重启MySQL服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!查看主库的状态
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000001 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)配置从数据库
从数据库1
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
og-bin=mysql_bin
server_id=10
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=1
binlog_format=row
skip_slave_start=1
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
ysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.207.131',
-> MASTER_USER='hzy',
-> MASTER_PASSWORD='hzy123',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysqld-bin.000001',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql>reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
//查看从服务器状态
mysql> show slave status \G
*************************** 1. row ***************************
......
Slave_IO_Running: Yes //此次必须为yes
Slave_SQL_Running: Yes //此次必须为yes
......
1 row in set (0.00 sec)从数据库2
[root@localhost ~]# vim /etc/my.cnf
//添加以下内容
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
og-bin=mysql_bin
server_id=10
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=1
binlog_format=row
skip_slave_start=1
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
ysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.207.131',
-> MASTER_USER='hzy',
-> MASTER_PASSWORD='hzy123',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysqld-bin.000001',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql>reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
//查看从服务器状态
mysql> show slave status \G
*************************** 1. row ***************************
......
Slave_IO_Running: Yes //此次必须为yes
Slave_SQL_Running: Yes //此次必须为yes
......
1 row in set (0.00 sec)进行验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database hyh;
Query OK, 1 row affected (0.00 sec)
mysql> use hyh;
Database changed
mysql> insert student(name,age) values('tom',20),('jerry',25),('zhangshan',
,26);
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 25 |
| 3 | zhangshan | 26 |
+----+-----------+------+
3 rows in set (0.00 sec)查看数据是否同步
//从1
mysql> select * from HZY.hzy;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 25 |
| 3 | zhangshan | 26 |
+----+-----------+------+
3 rows in set (0.01 sec)
//从2
mysql> select * from HZY.hzy;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 25 |
| 3 | zhangshan | 26 |
+----+-----------+------+
3 rows in set (0.00 sec)