mysql主从

数据库主从：

​ 1.传统主从

​ 2.gtid主从

主从复制配置步骤：

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

主从形式：

一主多从

主主复制

一主一从

多主一从

联级复制

环境说明：

安装三台主机

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)