mysql主从复制

发表于 | 分类于 | | 阅读次数 |
| 字数统计 1,232 | 阅读时长 5

概念

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

MySQL中主从复制的优点

  1. 横向扩展解决方案

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

  1. 数据安全性

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

  1. 分析

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

  1. 长距离数据分发

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

环境准备

MySQL版本 MySQL 5.7.21
Master-Server 192.168.229.116
Slave-Server 192.168.229.119

安装MySQL

参考上篇博文
首先在两台机器上装上，保证正常启动，可以使用

Master-Server 配置

修改 my.cnf

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

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

1
2
3
4
$ cat /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

重启MySQL服务

1
$ service mysql restart

登录MySQL

1
$ mysql -u root -p

创建备份用户

1
2
mysql> CREATE USER 'backup'@'192.168.229.119' IDENTIFIED BY 'backup';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.252.119';

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

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

Slave-Server 配置

修改 my.cnf

1
2
3
$ cat /etc/my.cnf
[mysqld]
server-id=2

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

重启MySQL服务

1
$ service mysql restart

登录MySQL

1
$ mysql -u root -p

配置主库通信

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

1
2
3
4
5
6
7
8
9
10
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1697
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql>

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

配置从库通信

1
CHANGE MASTER TO MASTER_HOST='192.168.229.116', MASTER_USER='backup', MASTER_PASSWORD='backup', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1697;

启动从服务器复制线程

1
2
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看复制状态

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

检查主从复制通信状态

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

必须都是 Yes

测试主从复制

启动mysql服务并在主库中创建表

1
2
3
4
5
6
7
mysql> create database demo;
Query OK, 1 row affected (0.00 sec)
mysql> use demo;
Database changed
mysql> create table users(id int not null auto_increment,name varchar(32) not null, primary key(id)) engine=InnoDB auto_increment=0 default charset=utf8;
Query OK, 0 rows affected (0.03 sec)

从库中查询库表

1
2
3
4
5
6
7
8
9
10
11
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

主库中刚才创建的数据库demo已经同步到存库中,即标识一个主从复制的数据库配置已经大功告成

注意事项

如果在用启动mysql> start slave;启动从服务器复制线程是出错，可使用reset slave进行重置

参考资料：