mysql主从复制 – 汪明鑫的个人博客

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主从复制