mysql主从

mysql主从复制简介

MySQL Replication概述：
mysql replication俗称MySQL AB 复制或者主从复制，是mysql官方推荐的数据同步技术。
优点：
1.通过增加从服务器来提高数据库平台的可靠性，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而缓解主服务器平台的高性能
2.提高数据安全性，因为数据已复制到从服务器，主服务器数据异常时，可以将从服务器复制进程终止来达到保护数据完整性的特点。
3.在主服务器上生成实时数据，而在从服务器上分析这些数据，从而缓解主服务器的性能。

主从复制的作用

1、做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
2、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3、读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

主从复制原理

1.数据库有个bin-log二进制文件，记录了所有sql语句。
2.我们的目标就是把主数据库的bin-log文件的sql语句复制过来。
3.让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。
4.下面的主从配置就是围绕这个原理配置
5.具体需要三个线程来操作：
1.binlog输出线程:每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。在从库里，当复制开始的时候，从库就会创建两个线程进行处理：
2.从库I/O线程:当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。
3.从库的SQL线程:从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。
可以知道，对于每一个主从复制的连接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每一个从库都有它自己的I/O线程和SQL线程。

主从复制如图：

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

主从复制配置

主从复制配置步骤：
1.确保从数据库与主数据库里的数据一样
2.在主数据库里创建一个同步账号授权给从数据库使用
3.配置主数据库（修改配置文件）
4.配置从数据库（修改配置文件）
需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
分别在主从两台服务器上安装mysql-5.7版本，此处略过安装步骤，若有疑问请参考《mysql基础》与《mysql进阶》两篇文章。

mysql主从配置

     本人配置环境声明：
              主库IP：192.168.201.130
              从库IP：192.168.201.131


#先查看主库有哪些库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
| teacher            |
+--------------------+
6 rows in set (0.00 sec)

#再查看从库有哪些库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)


#全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.31 sec)
注意：此锁表的终端必须在备份完成以后才能退出，期间不要有任何操作

#备份主库并将备份文件传送到从库
[root@wnz ~]# mysqldump -uroot -pwang123! --all-databases > /opt/all-202006241553.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@wnz ~]# ls /opt/
all-202006241553.sql  mysqldata
[root@wnz ~]# scp /opt/all-202006241553.sql root@192.168.201.131:/opt/
The authenticity of host '192.168.201.131 (192.168.201.131)' can't be established.
ECDSA key fingerprint is SHA256:d+tl6HNy0Y8Wc2x2ZzZuP5WEnD7z5bUBW3/ylWOSHPo.
ECDSA key fingerprint is MD5:75:e8:a2:9a:54:3a:0d:22:a6:27:cd:96:c6:00:a9:19.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.201.131' (ECDSA) to the list of known hosts.
root@192.168.201.131's password: 
all-202006241553.sql                               100%  831KB   5.0MB/s   00:00    

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

#在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@wnz ~]# mysql -uroot -pwang123! < /opt/all-202006241553.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@wnz ~]# mysql -uroot -pwang123! -e 'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
| teacher            |
+--------------------+



#在主数据库里创建一个同步账号授权给从数据库使用
mysql> CREATE USER 'repl'@'192.168.201.131' IDENTIFIED BY 'repl123';
Query OK, 0 rows affected (0.12 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.201.131';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.36 sec)


#配置主数据库
在配置文件里面添加如下内容
[root@wnz ~]# vim /etc/my.cnf
server-id = 10               //数据库服务器唯一标识符，主库的server-id值必须比从库的小
log-bin = mysql-bin          //启用binlog日志
重启服务
[root@wnz ~]# service mysqld restart
Shutting 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@wnz ~]# vim /etc/my.cnf
server-id = 20               //数据库服务器唯一标识符，从库的server-id值必须比主库的大
relay-log=mysql-relay-bin       //启用中继日志relay-log
重启服务
[root@wnz ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL.......... SUCCESS! 

#配置并启动主从复制
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.201.130',  //主服务器IP地址
    -> MASTER_USER='repl',             //授权用户
    -> MASTER_PASSWORD='repl123',  //授权用户密码
    -> 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.10 sec)


mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.201.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: wnz-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes  //负责从库去主库读取二进制日志，并写入到从库的中继日志
            Slave_SQL_Running: Yes  // 负责将中继日志转换成SQL语句后执行
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 

       这样主从复制就配置完成了！！！