爱站程序员基地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/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.piduser = mysqlskip-name-resolvelog-bin = mysql_binserver-id = 10
重启服务
[root@localhost ~]# service mysqld restartShutting 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/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.piduser = mysqlskip-name-resolveserver-id = 20relay-log = myrelay
配置从数据库
[root@localhost ~]# vi /etc/my.cnf[root@localhost ~]# cat /etc/my.cnf[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.piduser = mysqlskip-name-resolveserver-id = 30relay-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: YesSlave_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: 0mysql> 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)# 从1mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.01 sec)# 从2mysql> 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/3306port = 3306socket = /tmp/mysql3306.sockpid-file = /opt/data/3306/mysql_3306.pidlog-error=/var/log/3306.loglog-bin=mysql_binserver_id=10gtid_mode=onenforce_gtid_consistency=onlog-slave-updates=1binlog_format=rowskip_slave_start=1
重启MySQL服务
[root@localhost ~]# service mysqld restartShutting 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/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.piduser = mysqlskip-name-resolveog-bin=mysql_binserver_id=10gtid_mode=onenforce_gtid_consistency=onlog-slave-updates=1binlog_format=rowskip_slave_start=1[root@localhost ~]# service mysqld restartShutting 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 //此次必须为yesSlave_SQL_Running: Yes //此次必须为yes......1 row in set (0.00 sec)
从数据库2
[root@localhost ~]# vim /etc/my.cnf//添加以下内容[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.piduser = mysqlskip-name-resolveog-bin=mysql_binserver_id=10gtid_mode=onenforce_gtid_consistency=onlog-slave-updates=1binlog_format=rowskip_slave_start=1[root@localhost ~]# service mysqld restartShutting 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 //此次必须为yesSlave_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 changedmysql> insert student(name,age) values(\'tom\',20),(\'jerry\',25),(\'zhangshan\',,26);Query OK, 3 rows affected (0.07 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from student;+----+-----------+------+| id | name | age |+----+-----------+------+| 1 | tom | 20 || 2 | jerry | 25 || 3 | zhangshan | 26 |+----+-----------+------+3 rows in set (0.00 sec)
查看数据是否同步
//从1mysql> select * from HZY.hzy;+----+-----------+------+| id | name | age |+----+-----------+------+| 1 | tom | 20 || 2 | jerry | 25 || 3 | zhangshan | 26 |+----+-----------+------+3 rows in set (0.01 sec)//从2mysql> select * from HZY.hzy;+----+-----------+------+| id | name | age |+----+-----------+------+| 1 | tom | 20 || 2 | jerry | 25 || 3 | zhangshan | 26 |+----+-----------+------+3 rows in set (0.00 sec)
