主从复制可以使MySQL数据库主服务器的主数据库,复制到一个或多个MySQL从服务器从数据库,默认情况下,复制异步; 根据配置,可以复制数据库中的所有数据库,选定的数据库或甚至选定的表。

MySQL中主从复制的优点

横向扩展解决方案

在多个从库之间扩展负载以提高性能。在这种环境中,所有写入和更新在主库上进行。但是,读取可能发生在一个或多个从库上。该模型可以提高写入的性能(由于主库专用于更新),同时在多个从库上读取,可以大大提高读取速度。

数据安全性

由于主库数据被复制到从库,从库可以暂停复制过程,可以在从库上运行备份服务,而不会破坏对应的主库数据。

分析

可以在主库上创建实时数据,而信息分析可以在从库上进行,而不会影响主服务器的性能。

长距离数据分发

可以使用复制创建远程站点使用的数据的本地副本,而无需永久访问主库。

1.准备工作

  1. Mysql版本:MySQL 5.7.24
  2. Master-Server : 192.168.1.100
  3. Slave-Server : 192.168.1.200

关闭防火墙

  1. systemctl stop firewalld.service

安装 MySQL
首先在两台机器上装上,保证正常启动,可以使用

2. Master-Server 配置

修改 my.cnf

配置 Master 以使用基于二进制日志文件位置的复制,必须启用二进制日志记录并建立唯一的服务器ID,否则则无法进行主从复制。

停止MySQL服务。

  1. systemctl stop mysql.server

开启binlog ,每台设置不同的 server-id

  1. [mysqld]
  2. log-bin=mysql-bin
  3. server-id=100

启动MySQL服务

  1. systemctl start mysql.server

登录MySQL

  1. mysql -uroot -p

创建用户

每个从库使用MySQL用户名和密码连接到主库,因此主库上必须有用户帐户,从库可以连接。任何帐户都可以用于此操作,只要它已被授予 REPLICATION SLAVE权限。可以选择为每个从库创建不同的帐户,或者每个从库使用相同帐户连接到主库

虽然不必专门为复制创建帐户,但应注意,复制用到的用户名和密码会以纯文本格式存储在主信息存储库文件或表中 。因此,需要创建一个单独的帐户,该帐户只具有复制过程的权限,以尽可能减少对其他帐户的危害。

  1. mysql> CREATE USER 'replication'@'192.168.1.200' IDENTIFIED BY 'Admin$1314';
  2. mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.200';

3.Slave-Server 配置

修改 my.cnf

停止MySQL服务。

  1. systemctl stop mysql.server

设置不同的 server-id

  1. [mysqld]
  2. server-id=200

如果要设置多个从库,则每个从库的server-id与主库和其他从库设置不同的唯一值。

启动MySQL服务

  1. systemctl start mysql.server

登录MySQL

  1. mysql -uroot -p

配置主库通信

查看 Master-Server , binlog File 文件名称和 Position值位置 并且记下来

  1. mysql> show master status;
  2. +------------------+----------+--------------+------------------+-------------------+
  3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  4. +------------------+----------+--------------+------------------+-------------------+
  5. | mysql-bin.000001 | 625 | | | |
  6. +------------------+----------+--------------+------------------+-------------------+
  7. 1 row in set (0.00 sec)

要设置从库与主库进行通信,进行复制,使用必要的连接信息配置从库在从库上执行以下语句
将选项值替换为与系统相关的实际值

  1. CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='replication',MASTER_PASSWORD='Admin$1314', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=625;

启动从服务器复制线程

  1. mysql> START SLAVE;
  2. Query OK, 0 rows affected (0.00 sec)

查看复制状态

  1. mysql> show slave status\G
  2. *************************** 1. row ***************************
  3. Slave_IO_State: Waiting for master to send event
  4. Master_Host: 192.168.1.100
  5. Master_User: replication
  6. Master_Port: 3306
  7. Connect_Retry: 60
  8. Master_Log_File: mysql-bin.000001
  9. Read_Master_Log_Pos: 625
  10. Relay_Log_File: master2-relay-bin.000003
  11. Relay_Log_Pos: 320
  12. Relay_Master_Log_File: mysql-bin.000001
  13. Slave_IO_Running: Yes
  14. Slave_SQL_Running: Yes
  15. ......

检查主从复制通信状态

Slave_IO_State #从站的当前状态
Slave_IO_Running: Yes #读取主程序二进制日志的I/O线程是否正在运行
Slave_SQL_Running: Yes #执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样
Seconds_Behind_Master #是否为0,0就是已经同步了

如果不是原因主要有以下 4 个方面:

1、网络不通
2、密码不对
3、MASTER_LOG_POS 不对 ps
4、mysql 的 auto.cnf server-uuid 一样(可能你是复制的mysql)

  1. $ find / -name 'auto.cnf'
  2. $ cat /var/lib/mysql/auto.cnf
  3. [auto]
  4. server-uuid=6b831bf3-8ae7-11e7-a178-000c29cb5cbc # 按照这个16进制格式,修改server-uuid,重启mysql即可

4.测试主从复制

启动MySQL服务

  1. systemctl start mysql.server

登录MySQL

  1. mysql -uroot -p

在 Master-Server 创建测试库

  1. mysql> CREATE DATABASE `simondong`;
  2. mysql> use `simondong`;
  3. mysql> CREATE TABLE `test` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

在 Slave-Server 查看是否同步过来

  1. mysql> show databases;
  2. +-------------------------+
  3. | Database |
  4. +-------------------------+
  5. | information_schema |
  6. | mysql |
  7. | performance_schema |
  8. | simondong |
  9. | sys |
  10. +-------------------------+
  11. mysql> use simondong
  12. mysql> show tables;
  13. +-----------------------------------+
  14. | Tables_in_simondong |
  15. +-----------------------------------+
  16. | test |
  17. +-----------------------------------+
  18. 1 row in set (0.00 sec)

一些命令

查看主服务器的运行状态

  1. mysql> show master status;
  2. +------------------+----------+--------------+------------------+-------------------+
  3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  4. +------------------+----------+--------------+------------------+-------------------+
  5. | mysql-bin.000001 | 1190 | | | |
  6. +------------------+----------+--------------+------------------+-------------------+

查看从服务器主机列表

  1. mysql> show slave hosts;
  2. +-----------+------+------+-----------+--------------------------------------+
  3. | Server_id | Host | Port | Master_id | Slave_UUID |
  4. +-----------+------+------+-----------+--------------------------------------+
  5. | 200 | | 3306 | 1 | 6b831bf2-8ae7-11e7-a178-000c29cb5cbc |
  6. +-----------+------+------+-----------+--------------------------------------+

获取binlog文件列表

  1. mysql> show binary logs;
  2. +------------------+-----------+
  3. | Log_name | File_size |
  4. +------------------+-----------+
  5. | mysql-bin.000001 | 1190 |
  6. +------------------+-----------+

只查看第一个binlog文件的内容
mysql> show binlog events;

查看指定binlog文件的内容
mysql> show binlog events in ‘mysql-bin.000001’;

启动从库复制线程

  1. mysql> START SLAVE;
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)

停止从库复制线程

  1. mysql> STOP SLAVE;
  2. Query OK, 0 rows affected (0.00 sec)

MySQL同步故障:” Slave_SQL_Running:No” 两种解决办法

  1. 进入slave服务器,运行:
  2. mysql> show slave status\G
  3. .......
  4. Relay_Log_File: localhost-relay-bin.000535
  5. Relay_Log_Pos: 21795072
  6. Relay_Master_Log_File: localhost-bin.000094
  7. Slave_IO_Running: Yes
  8. Slave_SQL_Running: No
  9. Replicate_Do_DB:
  10. Replicate_Ignore_DB:
  11. ......

解决办法一:
Slave_SQL_Running: No
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后,事务回滚造成的.

一般是事务回滚造成的:
解决办法:

  1. mysql> stop slave ;
  2. mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
  3. mysql> start slave ;

分类: MySQL

标签:   mysql