主从简介
在现代企业中,数据显得尤为重要,而存储数据的数据库选择又五花八门,但无论是何种数据库,均存在着一种隐患。
主从作用
实时灾备,用于故障切换
读写分离,提供查询服务
备份,避免影响业务
主从形式
一主一从
主主复制
一主多从---扩展系统读取的性能,因为读是在从库读取的
多主一从---5.7开始支持
联级复制
主从复制原理
主从复制步骤:
主库将所有的写操作记录到binlog日志中并生成一个log dump线程,将binlog日志传给从库的I/O线程
从库生成两个线程,一个I/O线程,一个SQL线程
I/O线程去请求主库的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中
SQL线程,会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,达到最终数据一致的目的
主从复制配置
主从复制配置步骤:
确保从数据库与主数据库里的数据一样
在主数据库里创建一个同步账号授权给从数据库使用
配置主数据库(修改配置文件)
配置从数据库(修改配置文件)
需求:
搭建两台MySQL服务器,一台作为主服务器,一台作为从服务器,主服务器进行写操作,从服务器进行读操作
环境说明:
数据库角色IP应用与系统版本有无数据
主数据库
172.168.122.134
centos7/redhat7
mysql-5.7
有数据
从数据库
172.168.122.130
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 itforoptions 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 itforoptions that affect everything
#
[client-server]
[mysqld]
server-id=20relay-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.02sec)
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.01sec)
mysql> insert test(name,age) values('aa',21),('bb',22),('cc',23);
Query OK,3 rows affected (0.01sec)
Records:3 Duplicates: 0 Warnings: 0mysql> 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.02sec
[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
[root@slave~]# mysql -uroot -p123 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| cb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+