Welcome everyone

mysql主从复制

mysql 汪明鑫 11浏览 0评论

mysql安装

wget http://dev.mysql.com/get/mysql57-community-release-el6-8.noarch.rpm

rpm -ivh mysql57-community-release-el6-8.noarch.rpm

yum install mysql-server

systemctl start mysqld
mysql -uroot -p

 

初始的随机密码在这个文件

/var/log/mysqld.log
找到粘贴过来登陆
再修改密码
mysql> set password=password("123");

 

设置mysql远程访问权限

grant all PRIVILEGES on *.* to 'root'@'%' identified by '123' WITH GRANT OPTION ;

 

 

mysql文件

  • 数据文件和二进制文件: /var/lib/mysql/
  • 配置文件: /etc/my.cnf
  • 日志文件: /var/log/mysql.log

 

[root@xinyeshuaiqi ~]# cd /var/lib/mysql/
[root@xinyeshuaiqi mysql]# ls
auto.cnf    ca.pem           client-key.pem  ibdata1      ib_logfile1  learningcenter  mysql       mysql.sock.lock     private_key.pem  server-cert.pem  sys
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  ibtmp1       mydb            mysql.sock  performance_schema  public_key.pem   server-key.pem

 

[root@xinyeshuaiqi mysql]# cat  /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

 

mysql主从

master负责:读/写

slave负责:读

 

slave替master承担读压力(大部分压力都在读),当然可以一主多从

其实还有双主模型等

 

主从复制原理

数据的同步是异步执行的,并不是实时的

因此有时会由于主从同步延迟导致数据读不到,可以强行绑定读主库

下面我们探一探主从复制的奥妙=-=

主从数据复制核心在于binlog

 

Mysql的binlog日志作用是用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录(对数据库的改动),对数据库的查询select或show等不会被binlog日志记录;主要用于数据库的主从复制以及增量恢复。

 

主数据库必须开启二进制日志

 

  • 主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。
  • 从服务器上面也启动一个 I/O thread,通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志,然后把读取到的二进制日志写到本地的一个Realy log(中继日志)里面。
  • 从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的),如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。

 

如果是一主多从的话:
master只把binlog发给一个slave,从在把binlog向其他slave转发
不用master负责给所有slave都发一遍

 

主从同步延时问题

原因:

1,master吞吐量过高

2,网络问题 (binlog文件传输耗时)

3,io读写问题

主从搭建实战

环境:2台阿里云服务器  都装了mysql

 

我们分别称为node1(master),node2(slave)

 

master操作

步骤一:

先在node1,修改 /etc/my.cnf

步骤二:

重启mysql systemctl restart mysqld

步骤三:

mysql -u root -p  登陆mysql查看状态

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)

步骤四:

创建用户并授权

mysql> Create user datasync identified by 'fpx2019WIN!';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'datasync'@'%' IDENTIFIED BY 'fpx2019WIN!';    
Query OK, 0 rows affected, 1 warning (0.02 sec)

FLUSH PRIVILEGES;    刷新权限

 

允许slave通过创建的用户远程访问master,去读取二进制数据,实现数据同步

 

mysql> select host,user,password_last_changed from user;
+-----------+---------------+-----------------------+
| host      | user          | password_last_changed |
+-----------+---------------+-----------------------+
| localhost | root          | 2019-03-29 18:20:29   |
| localhost | mysql.session | 2019-03-29 18:07:17   |
| localhost | mysql.sys     | 2019-03-29 18:07:17   |
| %         | root          | 2019-03-29 18:20:46   |
| %         | datasync      | 2019-11-06 16:28:53   |
+-----------+---------------+-----------------------+
5 rows in set (0.00 sec)

 

其他设置(可选)

#不同步哪些数据库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema

#只同步哪些数据库,除此之外,其他不同步
binlog-do-db = game

 

 

slave操作

步骤一:

在node2,修改 /etc/my.cnf,加上下面这些

server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
read_only=1

 

步骤二:

重启mysql

systemctl restart mysqld

 

步骤三:

登陆mysql  mysql -u root -p

【然后连接master】

change master to master_host='master的ip', master_port=3306,master_user='datasync',master_password='fpx2019WIN!',master_log_file='mysql-bin.000001',master_log_pos=154;

master_log_file master_log_pos 都是在上文master status出现的

 

步骤四:

start slave;
步骤五:
show slave status\G;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ip
                  Master_User: datasync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 842
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 286
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No

Slave_IO_Running: Yes

Slave_SQL_Running: No

但是两个必须都是 YES才表示成功。。。

 

 

在node2远程登陆master就可以

 

卧槽无情

为啥???

 

stop slave后冷静冷静,分析下为啥出现这种问题

我突然发现,我是先开启二进制文件,再新创建user

就会导致create user 语句记录在binlog

slave执行j就会出现问题

 

解决方法,得到master  最新的pos  1291

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

 

change master to master_host='master的ip', master_port=3306,master_user='datasync',master_password='fpx2019WIN!',master_log_file='mysql-bin.000001',master_log_pos=1291;

master_log_pos 改成1291

 

再start slave;

show slave status\G;

 

好了,现在舒服了

 

简单测试一下

在master:

mysql> create database 11_6;
Query OK, 1 row affected (0.00 sec)
mysql> create table A(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into A(id) value(33);
Query OK, 1 row affected (0.03 sec)

 

在slave:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| 11_6 |
| mysql |
| performance_schema |
| zjoin |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_11_6 |
+----------------+
| A              |
+----------------+
1 row in set (0.00 sec)

mysql> select * from A
    -> ;
+------+
| id   |
+------+
|   33 |
+------+
1 row in set (0.00 sec)

 

 

binlog的细节

[root@xinyeshuaiqi ~]# cd /var/lib/mysql
[root@xinyeshuaiqi mysql]# ls
11_6      ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  ibtmp1          mydb   mysql-bin.000001  mysql.sock       performance_schema  public_key.pem   server-key.pem
auto.cnf  ca.pem      client-key.pem   ibdata1         ib_logfile1  learningcenter  mysql  mysql-bin.index   mysql.sock.lock  private_key.pem     server-cert.pem  sys

mysql-bin.000001 就是binlog了

 

查看binlog的内容:

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001

 

可以看到sql语句(数据更新)

 

 

binlog的三种工作模式:

  (1)Row level
  日志中会记录每一行数据被修改的情况,然后在slave端对相同的数据进行修改。
  优点:能清楚的记录每一行数据修改的细节
  缺点:数据量太大
  (2)Statement level
  每一条被修改数据的sql都会记录到master的binlog中,
      slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行(sql重放)
  优点:解决了 Row level下的缺点,不需要记录每一行的数据变化,减少binlog日志量,节约磁盘IO,提高新能
  缺点:容易出现主从复制不一致
  (3)Mixed(混合模式)
  结合了Row level和Statement level的优点
     实际上就是前两种模式的结合,在mixed模式下,mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也是在statement和row之间选择一种。

 
 

-查看binlog的工作模式
show variables like ‘%log%’;
 
 

-设置 binlog_format

1,set global binlog_format=’MIXED’;   (注意:一定要大写)

2,在  vim /etc/my.cnf  下增加   binlog_format=’MIXED’

 
 

退出客户端重连
mysql> show variables like "%binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)

 
生效了
 

转载请注明:汪明鑫的个人博客 » mysql主从复制

喜欢 (0)

说点什么

您将是第一位评论人!

提醒
avatar
wpDiscuz