CENTOS7 YUM 安装 MYSQL 主从

安装 环境：
192.168.141.147 master
192.168.141.154 slave

1 关闭防火墙selinux
[root@bogon ~]# systemctl stop firewalld
[root@bogon ~]# setenforce 0
2.安装数据库
yum -y install mariadb mariadb-server

3 编辑 master my.conf

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

server-id=1
log-bin=mysql-bin
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

启动数据库查看二进制日志授权用户

[root@localhost ~]# systemctl start mariadb
[root@bogon ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show master status;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000003 |     523 |              | information_schema,performance_schema,mysql |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> grant all on *.* to 'slave'@'192.168.141.154' identified by '123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>

4 编辑 slave my.cnf

vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

log-bin=myql-bin
server-id=2

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> change master to master_host='192.168.141.147',master_user='slave',master_password='123',master_log_file='mysql-bin.000003',master_log_pos=523;
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.141.147
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 523
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

出现双Yes,主从搭建完成


查看binlog方法一：
MariaDB [(none)]> show binlog events;
#查看指定binlog文件的内容
MariaDB [(none)]> show binlog events in ‘mysql-bin.000002’;

查看binlog方法二：mysqlbinlog
[root@server01 mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000001 |more

mysql主从复制的三种类型：
binlog有三种格式：Statement、Row以及Mixed。
–基于SQL语句的复制(statement-based replication,SBR)，
–基于行的复制(row-based replication,RBR)，
–混合模式复制(mixed-based replication,MBR)。

查看当前日志格式：
MariaDB [(none)]> show variables like ‘binlog_format’;
±--------------±----------+
| Variable_name | Value |
±--------------±----------+
| binlog_format | STATEMENT |
±--------------±----------+

1、STATMENT模式：基于SQL语句的复制(statement-based replication, SBR)，

每一条会修改数据的sql语句会记录到binlog中。

优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。

缺点：在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

2、基于行的复制(row-based replication, RBR)：

不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。（不记录sql执行过程，只记录结果！）

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。

缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。

3、混合模式复制(mixed-based replication, MBR)：以上两种模式的混合使用，

一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

mysql 主从复制的作用：

1、主数据库出现问题，可以切换到从数据库。

2、可以进行数据库层面的读写分离。

3、可以在从数据库上进行日常备份。