mysql主从复制超详细14步曲

主从复制概述

随着技术的发展，在实际的生产环境中，由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题了。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器。在此之前我们必须要保证每台MySQL服务器里的数据同步。

主从作用

• 实时灾备，故障切换
• 读写分离，负载均衡
• 备份数据，避免丢失

主从形式

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

主从复制原理步骤

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

简单概述下就是 主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

主从复制具体配置步骤：

1. 同步数据
   为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中，如果是新部署的主从则不需要此项。
   主库数据：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
| test3              |
+--------------------+
7 rows in set (0.00 sec)

从库数据：

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

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

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

mysql> 

mysql> create database test4; ##不提示也不报错

# 退出此终端即解锁

查看状态

mysql> show processlist;
+----+------+-----------+------+---------+------+------------------------------+-----------------------+
| Id | User | Host      | db   | Command | Time | State                        | Info                  |
+----+------+-----------+------+---------+------+------------------------------+-----------------------+
|  5 | root | localhost | NULL | Query   |    8 | Waiting for global read lock | create database test4 |
|  6 | root | localhost | NULL | Query   |    0 | starting                     | show processlist      |
+----+------+-----------+------+---------+------+------------------------------+-----------------------+
2 rows in set (0.00 sec)

可以直接终止命令

mysql> kill 5
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  6 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

3. 在主库备份生成备份文件

[root@master ~]# mysqldump -uroot -p123 --all-databases > all_databases.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]# ls
all_databases.sql  Desktop    Downloads  original-ks.cfg  Public                     sqlkey.txt         Templates         Videos
anaconda-ks.cfg    Documents  Music      Pictures         some-databases_backup.sql  tables_backup.sql  test1_backup

4. 传到从库

[root@master ~]# scp all_databases.sql 192.168.216.181:/root/
all_databases.sql                

5. 从库恢复数据

[root@localhost ~]# mysql -uroot -p1 < all_databases.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

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

mysql> grant replication slave on *.* to 'lhj'@'192.168.216.181' identified by '123'; #记得解除读锁
Query OK, 0 rows affected, 1 warning (5.60 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

7. 修改主库配置文件

[root@master ~]# cat /etc/my.cnf
[mysqld]
datadir=/opt/data
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
port=3306
character-set-server=utf8
log-error=/var/log/mysqld.log
pid-file=/opt/data/mysql.pid
#添加下面2行
log-bin=mysql-bin ##启用二进制日志
server-id=1        ## 数据库服务器唯一标识符，主库的server-id值必须比从库的小

8. 保存重启

[root@master ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

9. 查看主库状态

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)

10. 配置从库

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/opt/data
socket= /tmp/mysql.sock
port=3306
basedir=/usr/local/mysql
pid-file=/opt/data/mysqld.pid
skip-name-resolve

# slave config ##从库添加配置
server-id = 20  ## 设置从库的唯一标识符，从库的server-id值必须大于主库的该值
relay-log=mysql-relay-bin ## 启用中继日志relay-log

11. 重启从库

[root@localhost ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

12. 进入从库

mysql> change master to
    -> master_host='192.168.216.179',## 主库地址
    -> master_user='lhj', ## 用户
    -> master_password='123',## 密码
    -> master_log_file='mysql-bin.000001', ##查询主库的状态引用
    -> master_log_pos=154; ## 地址不带引号
Query OK, 0 rows affected, 2 warnings (0.02 sec)

13. 开启主从并查看状态

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

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.216.179
                  Master_User: lhj
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes  # IO与SQL必须都为yes，反之不成功
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 043362d2-08cc-11ec-a0fd-000c2933c088
             Master_Info_File: /opt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

14. 检验

mysql> create database aaa;
Query OK, 1 row affected (0.00 sec)

主库建库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
| test3              |
+--------------------+
8 rows in set (0.00 sec)
从库也有