2019-03-27笔记—MariaDB主从

MySQL/MariaDB主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的，MySQL主从基于binlog，主上须开启binlog才能进行主从。

主从执行过程三步骤：

• 主将更改操作记录到binlog里
• 从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
• 从根据relaylog里面的sql语句按顺序执行
• 主上有一个log dump线程，用来和从的I/O线程传递binlog
• 从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地

使用场景：

• 数据备份，主机器宕机，从机器还能随时对web提供服务
• 读写分离，作为一个从库，读的库，减轻主库的压力，数据备份且可以分担主机器被调用数据时的压力，写的操作只能在主库，不能在从库，如果从库写数据就会导致主从不同步。

补充：

https://blog.csdn.net/xiaoyi23000/article/details/80521423
https://www.cnblogs.com/abobo/p/4242417.html
http://blog.itpub.net/15498/viewspace-2125085/

实验步骤：
linux2019_01（192.168.85.129）—主
linux2019_02（192.168.85.128）—备

1. 两台机器上安装好mysql
2. 主机器上配置

[root@linux2019_01 ~]# vi /etc/my.cnf   #添加配置，开启二进制日志
log-bin=linux2019
server-id=129
[root@linux2019_01 ~]# systemctl restart msyqld
[root@linux2019_01 ~]# mysql -uroot -proot -e “grant replication slave on *.* to 'repl'@'192.168.85.129' identified by 'repl_passwd';” #给从机器授用户授权

3. 从机器上配置

[root@linux2019_02 ~]# vi /etc/my.cnf
server-id=128
replicate_wild_ignore_table=mysql.user
[root@linux2019_02 ~]# systemctl restart mysqld

4. 备份主机器上的数据库，恢复到备机器上

[root@linux2019_01 ~]# mariabackup --backup --user=root --password=root --target-dir /data/backup/20190328
[root@linux2019_01 ~]# rsync -a /data/backup/20190328 192.168.85.128:/data/backup/20190328

[root@linux2019_02 ~]# systemctl stop mysqld    #停止数据库
[root@linux2019_02 ~]# mv /data/mysql /data/mysql_bak0328   #清空datadir
[root@linux2019_02 ~]# mariabackup --prepare --target-dir /data/backup/20190328
[root@linux2019_02 ~]# mariabackup --copy-back --target-dir /data/backup/20190328
[root@linux2019_02 ~]# chown -R mysql:mysql /data/mysql #改属主、属组
[root@linux2019_02 ~]# systemctl start mysqld

5. 开启同步

MariaDB [(none)]> stop slave;
MariaDB [(none)]> change master to master_host='192.168.85.129', master_port=3306, master_user='repl', master_password='repl_passwd', master_log_file='linux2019.000001', master_log_pos=98559;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;  #可查看主从的相关配置信息
*************************** 1. row ***************************
                Slave_IO_State: Connecting to master
                   Master_Host: 192.168.85.129
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: linux2019.000001
           Read_Master_Log_Pos: 98559
                Relay_Log_File: linux2019_02-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: linux2019.000001
              Slave_IO_Running: Connecting
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: mysql.%
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 98559
               Relay_Log_Space: 256
               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: NULL
 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: 0
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

6. 测试主从：在主机器上做相关插入删除的操作，在从机器上能实时同步