«

MySQL-主从-主主

指尖二进制 • 1 年前 • 910 次点击 • MYSQL

[TOC]

主从：

天生的缺陷：复制延迟，slave上同步要慢于master,如果大并发的情况那延迟更严重

主机名IP系统/MySQL版本角色
jz1192.168.111.130CentOS7.4/5.7.20Master
jz2192.168.111.131CentOS7.4/5.7.20slave

模式：C/S
端口：3306
配置主数据库服务器jz1
创建同步的数据库并插入数据

ONE SLAVE MUTI MASTER 一从对多主
好处：节省成本，将多个master数据自动化整合
缺陷：对库和表数据的修改较多

配置从数据库服务器jz_130：

安装完数据库之后需要创建库同步到库，并主从数据库同时插入一些数据。

mysql> create database test;
mysql> use test;
mysql> create table emp (empno numeric(4) not null,ename varchar(10),job varchar(9),mgr numeric(4),hiredate datetime,sal numeric(7, 2),comm numeric(7, 2),deptno numeric(2));
mysql> insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
mysql> select * from emp;
+-------+-------+-------+------+---------------------+--------+------+--------+
| empno | ename | job   | mgr  | hiredate            | sal    | comm | deptno |
+-------+-------+-------+------+---------------------+--------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL |     20 |
+-------+-------+-------+------+---------------------+--------+------+--------+
1 row in set (0.00 sec)

修改主库130配置文件。配置my.cnf

[root@jz_130 ~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin-master    #启用二进制日志
server-id=1         #本机数据库ID 标示
binlog-do-db=test       #可以被从服务器复制的库, 二进制需要同步的数据库名
binlog-ignore-db=mysql      #不可以被从服务器复制的库
validate-password=off       #关闭密码强度审计

重启mysql(如果重启卡死现象，kill掉再启动)

[root@jz_130 ~]# systemctl restart mysqld

登录授权

[root@jz_130 ~]# mysql -uroot -p123456

注意：如果只想设置简单密码需要修改两个全局参数：先执行授权语句。下面两条没有必要执行，因为配置文件添加了validate-passwtd=off。

mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;

授权：创建一个账号使从数据库能够连接到主数据库，同步使用

mysql> grant replication slave on *.* to slave@192.168.111.131 identified by "123456";

刷新权限：

mysql> flush privileges;

查看状态信息

mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000001 |      604 | test         | mysql            |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

查看二进制日志：

mysql> show binlog events\G
[root@jz_130 ~]# ll /var/lib/mysql|grep mysql-bin
-rw-r-----. 1 mysql mysql      604 Dec  3 09:43 mysql-bin-master.000001
-rw-r-----. 1 mysql mysql       26 Dec  3 09:37 mysql-bin-master.index

配置从数据库服务器jz_131

两台数据库服务器mysql版本要一致

mysql> show variables like '%version%';

jz_131(192.168.111.131)测试连接到主服务器是否成功。jz1需要开启3306端口

[root@jz_130 ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@jz_130 ~]# firewall-cmd --reload
success
[root@jz_131 ~]# mysql -uslave -p123456 -h192.168.111.130
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

只有复制的权限, 是看不到其他库的。正常现象。

修改从服务器配置文件：
从服务器没必要开启bin-log日志

[root@jz_131 ~]# vim /etc/my.cnf
server-id=2  #从服务器ID号，不要和主ID相同，如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。可以认为server-id值类似于IP地址：这些ID值能唯一识别复制服务器群集中的每个服务器实例。
validate-password=off #关闭密码强度审计
[root@jz2 ~]# systemctl restart mysqld
[root@jz_131 ~]# mysql -uroot -p123456
mysql> stop slave; #停止slave
mysql> change master to master_host='192.168.111.130',master_user='slave',master_password='123456'; #把复制账号保存到数据库里面。同步时使用。
mysql> start slave; #启动slave
mysql> show slave status\G #查看状态
Slave_IO_Running ：一个负责与主机的io通信
Slave_SQL_Running：负责自己的slave mysql进程
两个为YES 就成功了！

再到主服务器上查看状态：

[root@jz_130 ~]# mysql -uroot -p123456
mysql> show processlist \G
*************************** 1. row ***************************
     Id: 5
   User: slave
   Host: 192.168.111.131:37758
     db: NULL
Command: Binlog Dump
   Time: 126
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 2. row ***************************
     Id: 6
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
2 rows in set (0.00 sec)

主服务器插入数据测试同步：

mysql> use test
mysql> insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

从服务器查看test库emp表：如下显示即为成功

[root@jz_131 ~]# mysql -uroot -p123456
mysql> use test
mysql> select * from emp;
+-------+-------+----------+------+---------------------+---------+--------+--------+
| empno | ename | job      | mgr  | hiredate            | sal     | comm   | deptno |
+-------+-------+----------+------+---------------------+---------+--------+--------+
|  7369 | SMITH | CLERK    | 7902 | 1980-12-17 00:00:00 |  800.00 |   NULL |     20 |
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |
+-------+-------+----------+------+---------------------+---------+--------+--------+
2 rows in set (0.00 sec)

主主：

部署MYSQL主主双向主从复制 M-M
通过mysql主主：进行mysql 双向同步数据库test的配置

主机名IP系统/MySQL版本角色
jz_131192.168.111.130CentOS7.4/5.7.20Master
jz_132192.168.111.131CentOS7.4/5.7.20Master

如果是主从的基础上需要先清空一下二进制日志，mysql> reset master
在192.168.111.131创建一个用户给192.168.111.130服务器，同步使用。互为主从。

mysql> grant replication slave on *.* to slave@'192.168.111.130' identified by '123456';
mysql> flush privileges;

开启二进制日志

[root@jz_131 ~]# vim /etc/my.cnf
server-id=2
log-bin=mysql-bin-slave #开启二进制日志
binlog-do-db=test #要复制的库
binlog-ignore-db=mysql #禁止同步的库
validate-password=off #关闭密码强度审计

jz_130(192.168.111.130)测试连接到jz_131(192.168.111.131)服务器是否成功。jz_131需要开启3306端口

[root@jz_131 ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@jz_131 ~]# firewall-cmd --reload
[root@jz_130 ~]# mysql -uslave -p123456 -h192.168.111.131
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

重启jz_131(192.168.111.131)

[root@jz_131 ~]# systemctl restart mysqld
[root@jz_131 ~]# mysql -uroot -p123456 
mysql> show master status;
+------------------------+----------+--------------+------------------+-------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-slave.000001 |      154 |              | mysql,mysql      |                   |
+------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

把复制账号保存到数据库里面。同步时使用。

[root@jz_130 ~]# mysql -uroot -p123456
mysql> stop slave;
mysql> change master to master_host='192.168.111.131',master_user='slave',master_password='123456';
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.111.131
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-slave.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: jz_130-relay-bin.000002
                Relay_Log_Pos: 379
        Relay_Master_Log_File: mysql-bin-slave.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

jz_130和jz_131插入数据进行测试。每个主机插入一条。在查看test库emp表。

mysql> insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
mysql> insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
还没收到回复