mysql主从原理及配置

1.简介

解决宕机带来的数据不一致，因为MySQL复制可以实时备份数据。
减轻数据库服务器的压力，多台服务器的性能一般比单台要好。

1.1主从作用

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

1.2主从形式

1.一主一从
2.主主复制
3.一主多从
4.多主一从
5.联级复制

2.主从复制原理

1. 主库db的更新事件(update、insert、delete)被写到binlog
2. 主库创建一个binlog dump thread，把binlog的内容发送到从库
3. 从库启动并发起连接，连接到主库
4. 从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
5. 从库启动之后，创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

3. 主从复制配置

主从复制配置步骤：

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

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

环境说明：

数据库角色	IP	主机名	应用与系统版本	有无数据
主数据库	172.16.12.128	wyt1	centos7/redhat7mysql-5.7	有数据
从数据库	172.16.12.130	wyt2	centos7/redhat7mysql-5.7	无数据

步骤：

1.安装mysql5.7版本，此处可参考mysql进阶
2.确保从数据库与主数据库里的数据一样

//查看主数据库
[root@wyt1 ~]# mysql -uroot -p'123456'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

//查看从数据库
[root@wyt2 ~]# mysql -uroot -p'wyt123'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

//全备主库
//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.02 sec)
//此锁表的终端必须在备份完成以后才能退出

//备份主库并将备份文件传送到从库
[root@wyt1 ~]# mysqldump -uroot -p123456 --all-databases > all-2010062301.sql 
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@wyt1 ~]# scp all-2010062301.sql root@192.168.232.130:/root/
root@192.168.232.130's password: 
all-2010062301.sql                         100%  831KB  26.5MB/s   00:00 

//解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye

//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@wyt2 ~]# mysql -uroot -pwyt123 < all-2010062301.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@wyt2 ~]# mysql -uroot -pwyt123 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

3.在主数据库里创建一个同步账号授权给从数据库使用

mysql> grant replication slave on *.* to 'repl'@'192.168.232.130' identified by 'repl123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4.配置主数据库

//编辑配置文件
[root@wyt1 ~]# vim /etc/my.cnf
server-id = 10  //数据库服务器唯一标识符，主库的server-id值必须比从库的大
log-bin = mysql_bin  //启用binlog日志

//重启mysql服务
[root@wyt1 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//查看主库的状态
[root@wyt1 ~]# mysql -uroot -p123456 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

5.配置从数据库

//编辑配置文件
[root@wyt2 ~]# vim /etc/my.cnf
server-id = 20  //设置从库的唯一标识符，从库的server-id值必须小于主库的该值
relay-log = myrelay_bin //启用中继日志relay-log

//重启mysql服务
[root@wyt2 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//配置并启动主从复制
mysql> change master to \
    -> master_host='192.168.232.128',
    -> master_user='repl',
    -> master_password='repl123',
    -> master_log_file='mysql_bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

查看从服务器状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.232.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: myrelay_bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes  //此处必须为Yes
            Slave_SQL_Running: Yes //此处必须为Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
.....

6.验证是否成功

//在主数据库中添加数据和删除数据
mysql> create database abc; //增加数据库
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> select * from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   22 |
|  3 | sean      |   28 |
|  4 | zhangshan |   26 |
+----+-----------+------+
4 rows in set (0.00 sec)

mysql> delete from student where id = 4; //删除数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  2 | jerry |   22 |
|  3 | sean  |   28 |
+----+-------+------+
3 rows in set (0.00 sec)


//在从数据库中查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  2 | jerry |   22 |
|  3 | sean  |   28 |
+----+-------+------+
3 rows in set (0.01 sec)