linux系统mysql主从配置

一、原理



mysql主从配置的流程大体如图：
1）master会将变动记录到二进制日志里面；
2）master有一个I/O线程将二进制日志发送到slave;
3）slave有一个I/O线程把master发送的二进制写入到relay日志里面；
4）slave有一个SQL线程，按照relay日志处理slave的数据；
二、操作步骤
按照原理，我们开启mysql主从复制，我们大体需要做以下操作：
1）开启master的二进制日志
2）开启slave的二进制日志
3）将slave指向master
4）开始复制
三、开启master二进制日志
1）编辑mysql的配置文件，使用命令：
vim /etc/my.cnf

2）添加二进制日志配置，开启二进制（master-bin只是日志文件名称，可以自己指定）
log-bin=master-bin
server-id=1

注意：server-id是要指定的，不然会报错，每一台指定一个唯一标识符
四、授权
我们需要给slave配置一个用户/密码的权限
mysql>GRANT REPLICATION SLAVE ON *.* TO '用户名'@'slave数据库的IP地址' IDENTIFIED BY '密码';

这行命令的意思是：允许在某个IP地址的某个用户以某个密码对当前数据库的所有库和所有表进行复制操作
注意：以上配置了权限，我们需要刷新以下权限使用命令：
mysql>flush privileges;

或者直接重启mysql服务：
service mysql restart;

五、查看以下master的状态
其实主要是查看以下master的日志文件名字，以及文件所在的位置，使用命令：
show master status;

可以看到类似如下信息：
±------------------±---------±-------------±-----------------±------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±------------------±---------±-------------±-----------------±------------------+
| master-bin.000001 |     1516 |              |                  |                   |
±------------------±---------±-------------±-----------------±------------------+
File: 日志文件名称
Position: 日志所在位置
六、开启slave的二进制日志
进入slave的服务器
1）编辑mysql的配置文件，使用命令：
vim /etc/my.cnf

2）添加二进制日志配置，开启二进制（relay-bin只是日志文件名称，可以自己指定）
log-bin=relay-bin
server-id=2

注意：server-id是要指定的，不然会报错，每一台指定一个唯一标识符
七、将slave指向master
mysql>CHANGE MASTER TO
MASTER_HOST='master所在服务器的IP',
MASTER_USER='master授权的账号',
MASTER_PASSWORD='master授权的密码',
MASTER_LOG_FILE='master的日志文件名',
MASTER_LOG_POS=master的日志所在位置;

八、开始主从复制
在slave上执行
mysql>start slave;

我们可以查看slave的运行状态：
show slave status\G;

可以看到类似如下内容：
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master的IP地址
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 1516
               Relay_Log_File: slave-bin.000004
                Relay_Log_Pos: 1117
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......    

注意：
Slave_IO_Running: YES 表示slave的日志读取线程开启
Slave_SQL_Running: YES 表示SQL执行线程开启
如果有主从复制有错误信息其实也可以看到
九、测试
以上我们就将主从复制功能设置完毕了，我们可以通过在master里面创建数据库，或者表，插入数据等来测试一下
十、注意点
1）开启了主从复制，slave库如果写入数据的话，可能导致数据回滚从而主从复制线程中断，可以通过以下方式解决：
mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;

2）如果要停止slave的复制可以使用命令：
mysql>stop slave;

3）由于主从复制是基于I/O的日志，所以会存在一定延时，如果对数据一致性要求非常高的话，简单的主从复制在实际环境中会存在问题
参考文章：
1）https://www.cnblogs.com/phpstudy2015-6/p/6485819.html#_label0
2）https://www.linuxidc.com/Linux/2014-02/96945.htm

Mysql主从配置

主从介绍


  
主从：很多人也叫做AB复制
  
  
官方称为：replication 从一个主把日志复制到从上，从上根据这个日志执行相应的操作，从而达到，两个数据库的数据保持一致。
  
  
A – change data – > bin_log -transfer-> B– repl_log –> change 

  database MySQL主从是基于binlog的，主上须开启binlog才能进行主从。


主从过程


  
1,主将更改操作记录到binlog中 ; 

  2,从将主的binlog事件（SQL语句）同步到本机并记录在relaylog中; 

  3,从根据relaylog里面的SQL语句按顺序执行;


说明： 该过程有三个线程，主上有一个log dump线程，用来和从的i/o线程传递binlog；从上有两个线程，其中i/o线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的SQL语句落地。

Mysql主从工作原理



应用环境


  
备份重要数据 ； 

  分担主库数据读取压力；


工作准备

主：192.168.96.128 

从：192.168.96.127 

两台服务器需安装Mysql并启动服务。

配置主服务器



编辑配置文件添加如下参数

[root@dl-001 ~]# vim /etc/my.cnf    //添加

server-id=128   # 设置128为主，127为从，注意主从的id不要相同
log_bin=dl1     ##指定log前缀

重启mysql服务



[root@dl-001 ~]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS!



查看mysql库文件：



[root@dl-001 ~]# cd /data/mysql/
[root@dl-001 mysql]# ls -lt
总用量 176228
-rw-rw----. 1 mysql mysql 50331648 1月  18 15:45 ib_logfile0
-rw-rw----. 1 mysql mysql 79691776 1月  18 15:45 ibdata1
-rw-rw----. 1 mysql mysql    58478 1月  18 15:45 dl-001.err
-rw-rw----. 1 mysql mysql        5 1月  18 15:45 dl-001.pid
-rw-rw----. 1 mysql mysql       13 1月  18 15:45 dl1.index
-rw-rw----. 1 mysql mysql      120 1月  18 15:45 dl1.000001
drwx------. 2 mysql mysql    12288 1月  11 15:54 zabbix
-rw-rw----. 1 mysql mysql       56 12月 14 22:41 auto.cnf
drwx------. 2 mysql mysql     4096 12月 14 22:35 mysql
drwx------. 2 mysql mysql     4096 12月 14 22:35 performance_schema
-rw-rw----. 1 mysql mysql 50331648 12月 14 22:35 ib_logfile1
drwx------. 2 mysql mysql        6 12月 14 22:35 test

说明： 重启后生成两个前缀为dl1的二进制文件。

试验准备:新建一个数据库



#备份一个数据库
[root@dl-001 mysql]# mysqldump -uroot -p'mysqldl991124' test > /tmp/test.sql
Warning: Using a password on the command line interface can be insecure.
[root@dl-001 mysql]# du -sh /tmp/test.sql 
4.0K test.sql

#新建一个数据库
[root@dl-001 mysql]# mysql -uroot -p'mysqldl991124' -e "create database dltest"
Warning: Using a password on the command line interface can be insecure.

#将备份的数据恢复到新建的数据库中
[root@dl-001 mysql]# mysql -uroot -p'mysqldl991124' dltest < /tmp/test.sql 
Warning: Using a password on the command line interface can be insecure.

创建一个用于同步数据的用户



[root@dl-001 mysql]# mysql -uroot -p'mysqldl991124'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to 'repl'@'192.168.96.127' identified by 'testdl991124';  //创建用户。（IP为“从”的IP）
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;   //锁定数据表（目的是暂时使其不能继续写，保持现有状态用于同步）     
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;      //记住file和position(设置主从同步时会使用)
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| dl1.000001 |      431 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> quit
Bye

备份主库中所有数据库



[root@dl-001 mysql]# ls
auto.cnf    dl-001.pid  dl1.index  ibdata1      ib_logfile1  performance_schema  zabbix
dl-001.err  dl1.000001  dltest     ib_logfile0  mysql        test
[root@dl-001 mysql]# mysqldump -uroot -p'mysqldl991124' zabbix > /tmp/zabbix.sql
Warning: Using a password on the command line interface can be insecure.

配置从服务器



添加配置文件

[root@dl-002 ~]# vim /etc/my.cnf
server-id=127

说明： 从的server-id要和主的server-id不同。

重启



[root@dl-002 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 

配置完成后将主中备份的数据发送到从中



[root@dl-002 ~]# scp 192.168.96.128:/tmp/*.sql /tmp/
root@192.168.96.128's password: 
test.sql                                                 100% 1258     1.2KB/s   00:00    
zabbix.sql                                               100% 1414KB   1.4MB/s   00:00

[root@dl-002 ~]# ls /tmp       //查看
hsperfdata_root                                                           test.sql
mysql.sock                                                                zabbix.sql
systemd-private-929ff93f14ea4a4a9bbb19a64dfdd55f-vmtoolsd.service-ftrUel

创建库

mysql> create database dltest;
Query OK, 1 row affected (0.00 sec)

mysql> create database zabbix;
Query OK, 1 row affected (0.00 sec)



mysql> quit
Bye

恢复数据库



[root@dl-002 ~]# mysql -uroot -p'mysqldl991124' dltest< /tmp/test.sql 
Warning: Using a password on the command line interface can be insecure.
[root@dl-002 ~]# mysql -uroot -p'mysqldl991124' zabbix < /tmp/zabbix.sql 
Warning: Using a password on the command line interface can be insecure.

注意： 该过程要保证主从数据库内容的一致。

实现主从同步



[root@dl-002 ~]# mysql -uroot -p'mysqldl991124'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)




mysql> change master to master_host='192.168.96.128',master_user='repl',master_password='mysqldl991124',master_log_file='dl1.000001',master_log_pos=431;    //IP为主的IP；file、pos分别为主的filename和position。
Query OK, 0 rows affected, 2 warnings (0.07 sec)


mysql> start slave;         //检测主从是否建立成功
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G        //查看是否主从配置成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

以上操作完成后，解锁主库的表（在主上的操作）



[root@dl-001 mysql]# mysql -uroot -p'mysqldl991124'        //在主机器上恢复写操作
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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


注意：一定不要在从mysql库上进行操作，不然容易造成主mysql库的紊乱 
说明：此时已经配置成功，在主上操作，从也会随之更改！！！

参数介绍



主服务器（在/etc/my.cnf中配置）:



binlog-do-db=               //类似白名单，将需要同步的库添进去
binlog-ignore-db=           //类似黑名单，将不需要同步的库列出来

从服务器（在/etc/my.cnf中配置）：



replicate_do_db=                //同步指定的库
replicate_ignore_db=        //忽略指定的库
replicate_do_table=         //同步指定的表
replicate_ignore_table=         //忽略指定的表

replicate_wild_do_table=    //如aming.%，支持通配符    
replicate_wild_ignore_table=    

总结：主从配置经常容易中断，建议及时监控，在从mysql上关注命令show slave status\G; 

另外，千万别在从mysql上去写入数据，很容易会导致主mysql的紊乱

建议: MySQL主从机制比较脆弱，谨慎操作。如果重启master主mysql库，务必要先把slave停掉，也就是说需要在slave上去执行 slave stop 命令，然后再去重启 

master的mysql服务，否则很有可能就会中断了。当然重启完后，还需要把slave给开启 slave start.

------------恢复内容开始------------
MySQL主从配置
MySQL主从又叫做Replication、AB复制，两台机器做主从配置之后，数据实时同步
主从配置大致步骤
①主将更改操作记录到binlog里
②从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里，中继日志
③从根据relaylog里面的sql语句按顺序执行
主配置(安装完mysql的虚拟机)
基础配置
# vi /etc/my.cnf
编辑配置文件
# /etc/init.d/mysqld restart
重启mysqld服务
# mysqldump -uroot mysql > /tmp/mysql.sql
备份mysql库(加入环境变量)
# mysql -uroot -e "create database kei"
创建一个库保存数据
# mysql -uroot kei < /tmp/mysql.sql
将mysql库恢复成新建的库，作为测试数据
数据库配置
# mysql -uroot
进入数据库(没有密码)
> grant replication slave on *.* to 'repl' @192.168.37.13 identified by '123456';
创建用作同步数据的用户并赋予权限
> flush tables with read lock;
将表锁住，保持表内数据不变
> show master status;
显示主机状态
从配置(安装完mysql的虚拟机)
基础配置
# vi /etc/my.cnf
编辑配置文件
# /etc/init.d/mysqld restart
重启mysqld服务
# scp /tmp/mysql.sql root@10.1.0.12:/tmp/
在主上将文件拷贝到从上，并在从上查看文件大小是否一致
# mysql -uroot -e "create database kei"
创建一个和主一样的库
# mysql -uroot kei < /tmp/mysql.sql
将文件内容导入库
数据库配置
# mysql -uroot
进入数据库(没有密码)
> change master to master_host='192.168.37.12',master_user='repl',master_password='123456',master_log_file='linux1.000001',master_log_pos=698861;
> unlock tables;
在主上执行解锁表
检验主从配置
从
> show slave status\G;
在从上执行命令，查看(将防火墙关闭)
主
# mysql -uroot kei
在主上进入数据库
> select count(*) from db;
> truncate table db;
从
# mysql -uroot kei
在从上进入数据库
> select count(*) from db;
主
> drop table db;

MySQL主从配置
主从配置篇
Mster配置
Slave配置:

主从配置篇
Mysql版本:5.5.53

Master:master 172.16.112.7

Slave:slave 172.16.112.21
Mster配置
一、配置my.conf
`[mysqld]
server-id = 1  #服务器的唯一ID号，主从之间不能冲突，默认是1

log-bin   = mysql-bin   #启动binlog 日志功能。也称事务日志

binlog-format = mixed   #日志的记录格式，默认是mixed，推荐也是用这个`
二、配置给从库Slave服务器使用的账号:

#创建账号，我测试环境的从服务器的IP地址就是192.168.15.21，可根据自己IP进行定义

#账号名也自己按自己的想法来取名
CREATE USER 'repl'@'192.168.15.66' IDENTIFIED BY '密码'; 
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.15.66'; #添加权限
FLUSH PRIVILEGES;  #刷新权限，立马生效

三、查看当前日志状态
SHOW MASTER STATUS;


Slave配置:
一、配置my.cnf
[mysqld]
server-id = 101  #服务器唯一ID号，不要冲突
log-bin   = mysql-bin  
binlog-format = mixed  
#如果不会在从库上做事务日志备份（增量备份）功能，建议不开启，减少磁盘IO
log-slave-updates = 1  

二、添加主库信息

这里的 MASTER_LOG_FILE  和  MASTER_LOG_POS 就是刚主数据库中查看出来的结果

我测试环境主库的IP是192.168.112.7
CHANGE MASTER TO 
MASTER_HOST='192.168.112.7',MASTER_PORT=63307,MASTER_USER='zly',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=959;

三、启动从库复制功能
START SLAVE;  #启动从复制功能
STOP SLAVE;  #停止从复制功能的命令
RESET SLAVE;  #重置从复制功能的配置，会清除 master.info 和 relay-log.info 两个文件

四、查看从库复制功能状态


常见故障
error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
解决方法：

找到data文件夹下的auto.cnf文件，删除，重启db自动重新生成auto.cnf文件
借鉴：https://blog.csdn.net/zhengchaooo/article/details/79767991