mysql主从

主从简介

在现代企业中,数据显得尤为重要,而存储数据的数据库选择又五花八门,但无论是何种数据库,均存在着一种隐患。

主从作用

  • 实时灾备,用于故障切换
  • 读写分离,提供查询服务
  • 备份,避免影响业务

主从形式

mysql主从

  • 一主一从
  • 主主复制
  • 一主多从---扩展系统读取的性能,因为读是在从库读取的
  • 多主一从---5.7开始支持
  • 联级复制

主从复制原理

mysql主从
主从复制步骤:

    • 主库将所有的写操作记录到binlog日志中并生成一个log dump线程,将binlog日志传给从库的I/O线程
    • 从库生成两个线程,一个I/O线程,一个SQL线程
      • I/O线程去请求主库的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中
      • SQL线程,会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,达到最终数据一致的目的

主从复制配置

主从复制配置步骤:

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

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

环境说明:

 

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

查看主从有什么库,准备两台虚拟机

[root@master ~]# mysql -uroot -p123
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
[root@slave ~]# mysql -uroot -p123
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
[root@master ~]# systemctl stop firewalld
[root@master ~]# setenforce 0
[root@master ~]# mysql -uroot -p123
MariaDB [(none)]> grant replication cb2 on *.* to cb@192.168.122.134 identified by cb123;
MariaDB [(none)]> flush privileges;

[root@slave ~]# systemctl stop firewalld
[root@slave ~]# setenforce 0
[root@slave ~]# mysql -ucb -pcb123 -h192.168.122.134

配置主数据库

[root@master ~]# vim /etc/my.cnf
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

[mysqld]           
log-bin=mysql_bin
server-id=10
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[root@master ~]# systemctl restart mariadb
[root@master ~]# mysql -uroot -p123
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000001 |      432 |              |                  |
+------------------+----------+--------------+------------------+

配置从库数据

[root@slave ~]# vi /etc/my.cnf

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
[mysqld]          
server-id=20       
relay-log=myrelay
#
# include all files from the config directory
!includedir /etc/my.cnf.d
[root@slave ~]# systemctl restart mariadb
[root@slave ~]# mysql -uroot -p123
MariaDB [(none)]> change master to master_host=192.168.122.134,master_user=cb,master_password=cb123,master_log_file=mysql_bin.000001,master_log_pos=432;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
Slave_IO_Running: Yes         
Slave_SQL_Running: Yes

测试验证

mysql> create database cb;
Query OK, 1 row affected (0.02 sec)
 
mysql> use cb;
Database changed
mysql> create table test(id int not null auto_increment,name varchar(20),age tinyint,primary key(id));
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert test(name,age) values(aa,21),(bb,22),(cc,23);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from test;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | aa   |   21 |
|  2 | bb   |   22 |
|  3 | cc   |   23 |
+----+------+------+
3 rows in set (0.01 sec)

在从库中查看数据是否同步

[root@slave ~]# mysql -ucb -pcb123 -e select * from cb.test;
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | aa   |   21 |
|  2 | bb   |   22 |
|  3 | cc   |   23 |
+----+------+------+
数据库角色 IP 有无数据
主数据库 192.168.122.134 有数据
从数据库 192.168.122.130 无数据

主库

[root@localhost ~]# mysql -uroot -p -e show databases;
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cb               |
| mysql              |
| performance_schema |
+--------------------+
 
[root@master ~]# mysql -uroot -p  -e select * from  cb.student;
Enter password:
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | tom   |   11 |
|  2 | zhangshan |   12 |
|  3 | lisi   |   13 |
+----+---------+------+

从库

[root@slave ~]# mysql -uroot -p -e show databases;
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

全备主库

MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.02 sec
[root@master ~]# mysqldump -uroot -p123 --all-databases >all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]# scp all.sql 192.168.122.134:/root
[root@slave ~]# mysql -uroot -p123 < all.sql
[root@slave ~]# mysql -uroot -p123 -e show databases;
+--------------------+
| Database           |
+--------------------+
| cb               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

 

mysql主从

上一篇:初探 MongoDB 分片集群


下一篇:MySql 主从