MySQL主从复制

部署环境：MySQL主从复制

系统环境CentOS 7

主mysql服务器ip：192.168.0.20

从mysql服务器ip：192.168.0.21

开始部署安装：

master服务器上：

安装MySQL服务：

[root@node-20 ~]# yum -y install mariadb mariadb-server

关闭防火墙和selinux：

[root@node-20 ~]# systemctl stop firewalld
[root@node-20 ~]# setenforce 0

启动MySQL服务并开启二进制日志：

[root@node-20 ~]# systemctl start mariadb
[root@node-20 ~]# vim /etc/my.cnf

server-id=1
log-bin=master
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
[root@node-20 ~]# systemctl restart mariadb

[root@node-20 ~]# netstat -nlptu|grep 3306

  tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2582/mysqld

授权用户：

[root@node-20 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
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)]> grant all on *.* to 'slave'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

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

查看二进制日志：

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

slave服务器上：

安装MySQL服务：

[root@node-21 ~]# yum -y install mariadb mariadb-server

关闭防火墙和selinux：

[root@node-21 ~]# systemctl stop firewalld
[root@node-21 ~]# setenforce 0

启动MySQL服务并开启中继日志：

[root@node-21 ~]# systemctl start mariadb
[root@node-21 ~]# vim /etc/my.cnf

server-id=2
log-bin=slave

[root@node-21 ~]# systemctl restart mariadb 
[root@node-21 ~]# netstat -anlptu|grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      2571/mysqld

连接主服务器：

[root@node-21 ~]# 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)]> stop slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> change master to 
    -> master_host='192.168.0.20',
    -> master_user='slave',
    -> master_password='123',
    -> master_log_file='master.000001',
    -> master_log_pos=446;
Query OK, 0 rows affected (0.00 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.0.20
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000001
          Read_Master_Log_Pos: 446
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 526
        Relay_Master_Log_File: master.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试主从复制：

master服务器：

创建一个数据库：

MariaDB [(none)]> create database abc charset utf8;
Query OK, 1 row affected (0.00 sec)

slave服务器：

查看数据库：

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

到此MySQL主从复制配置成功