主从简介
在现代企业中，数据显得尤为重要，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。
想几个问题：
- 用一台数据库存放数据，若此数据库服务器宕机了导致数据丢失怎么办？
- 业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量了怎么办？
1.1 主从作用
- 实时灾备，用于故障切换
- 读写分离，提供查询服务
- 备份，避免影响业务
1.2 主从形式
- 一主一从
- 主主复制
- 一主多从---扩展系统读取的性能，因为读是在从库读取的
- 多主一从---5.7开始支持
- 联级复制
2. 主从复制原理
主从复制步骤：
1.主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程。
2.从库生成两个线程，一个I/O线程，一个SQL线程。
2.1 I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中
2.2SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的
主从复制配置步骤：
- 确保从数据库与主数据库里的数据一样
- 在主数据库里创建一个同步账号授权给从数据库使用
- 配置主数据库（修改配置文件）
- 配置从数据库（修改配置文件）
需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
| IP | 有无数据 | |
|---|---|---|
| 主数据库 | 192.168.44.128 | 无数据 |
| 从数据库 | 192.168.44.131 |
1.准备两台虚拟机，一台主机名为master，一台主机名为slave，都使用yum装上mariadb服务，安全初始化数据库。在master上创建一个同步账号。
//mysql_secure_installation 安全初始化数据库 [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 //在主数据库里创建一个同步账号csl授权给从数据库使用 MariaDB [(none)]> grant replication slave on *.* to 'csl'@'192.168.44.131' identified by 'csl123'; MariaDB [(none)]> flush privileges; [root@slave ~]# systemctl stop firewalld [root@slave ~]# setenforce 0 //在从数据库里使用csl账号登陆 [root@slave ~]# mysql -ucsl -pcsl123 -h192.168.44.128
2.配置主数据库
[root@master ~]# 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] #加入三行数据 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 | 328 | | | +------------------+----------+--------------+------------------+
3.配置从数据库
[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 #从库的server-id必须要比主库的大 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.44.128',master_user='csl',master_password='csl123',master_log_file='mysql_bin.000001',master_log_pos=328; MariaDB [(none)]> start slave; MariaDB [(none)]> show slave statusG Slave_IO_Running: Yes #这两行是yes就代表成功 Slave_SQL_Running: Yes
4.验证测试，在主数据库上写入数据看从数据库上数据是否同步。
MariaDB [(none)]> create database school; MariaDB [(none)]> use school; MariaDB [school]> create table student(id int not null primary key auto_increment,name varchar(20) not null,age tinyint); MariaDB [school]> insert student(name,age) values('tom',15),('tim',12),('jack',18); [root@slave ~]# mysql -uroot -p123 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | +--------------------+ MariaDB [(none)]> use school; MariaDB [school]> select * from student; +----+------+------+ | id | name | age | +----+------+------+ | 1 | tom | 15 | | 2 | tim | 12 | | 3 | jack | 18 | +----+------+------+
| IP | 有无数据 | |
|---|---|---|
| 主数据库 | 192.168.44.128 | 有数据 |
| 从数据库 | 192.168.44.131 |
5.准备两台虚拟机，一台主机名为master，一台主机名为slave，都使用yum装上mariadb服务，安全初始化数据库。在master上创建一个同步账号，在master写入数据。
[root@master ~]# systemctl stop firewalld [root@master ~]# setenforce 0 [root@slave ~]# systemctl stop firewalld [root@slave ~]# setenforce 0 [root@master ~]# mysql -uroot -p123 MariaDB [(none)]> create database info; MariaDB [(none)]> use info;d MariaDB [info]> create table basic(id int not null primary key auto_increment,name varchar(50),job varchar(50)); MariaDB [info]> insert basic(name,job) values('tom','engineer'),('jerry','office'); MariaDB [info]> create table salary(name varchar(50),salary float); MariaDB [info]> insert salary(name,salary) values('tom',8000),('jerry',8500),('zhangshan',9000),('lisi',7500);
6.全备主数据库，确保从数据库与主数据库里的数据一样
//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致 MariaDB [(none)]> flush tables with read lock; //此锁表的终端必须在备份完成以后才能退出 //备份主库并将备份文件传送到从库 [root@master ~]# mysqldump -uroot -p123 --all-databases >all.sql [root@master ~]# scp all.sql 192.168.44.131:/root //解除主库的锁表状态，直接退出交互式界面即可 mysql> quit Bye //在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致 [root@slave ~]# mysql -uroot -p123 < all.sql [root@slave ~]# mysql -uroot -p123 -e 'show databases;' +--------------------+ | Database | +--------------------+ | info | | information_schema | | mysql | | performance_schema | +--------------------+
7.在主数据库里创建一个同步账号csl授权给从数据库使用，再配置主数据库
[root@master ~]# mysql -uroot -p123 MariaDB [(none)]> grant replication slave on *.* to 'csl'@'192.168.44.131' identified by 'csl123'; MariaDB [(none)]> quit [root@master ~]# 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=10 log-bin=mysql_bin # # 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 | 328 | | | +------------------+----------+--------------+------------------+
8.配置从数据库
[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 #从库的server-id必须要比主库的大 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.44.128',master_user='csl',master_password='csl123',master_log_file='mysql_bin.000001',master_log_pos=328; MariaDB [(none)]> start slave; MariaDB [(none)]> show slave statusG Slave_IO_Running: Yes Slave_SQL_Running: Yes
9.查看从数据库数据是否和主数据库一致，再在数据库上插入数据，看从数据库上是否同步数据。
MariaDB [(none)]> use info; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [info]> show tables; +----------------+ | Tables_in_info | +----------------+ | basic | | salary | +----------------+ 2 rows in set (0.000 sec) MariaDB [info]> select * from basic; +----+-------+----------+ | id | name | job | +----+-------+----------+ | 1 | tom | engineer | | 2 | jerry | office | +----+-------+----------+ //在主数据库插入数据 [root@master ~]# mysql -uroot -p123 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | info | | information_schema | | mysql | | performance_schema | +--------------------+ MariaDB [(none)]> use info; MariaDB [info]> insert basic(name,job) values('zhangshan','cleanner'); //在从数据库中查看数据是否同步 MariaDB [info]> select * from basic; +----+-----------+----------+ | id | name | job | +----+-----------+----------+ | 1 | tom | engineer | | 2 | jerry | office | | 3 | zhangshan | cleanner | +----+-----------+----------+