freeai logo
<<返回mysql首页 mysql

《MySQL 应用案例》

MySQL 在线搭建一主一从复制框架

注意:在实践本课程前，需要您具备MySQL知识，Linux相关知识。

主从复制（也称 AB 复制）允许将来自一个 MySQL 数据库服务器（主服务器）的数据复制到一个或多个 MySQL数据库服务器（从服务器）。

复制是异步的 从站不需要永久连接以接收来自主站的更新。

根据配置，您可以复制数据库中的所有数据库，所选数据库甚至选定的表。

本节课程将在线搭建 MySQL 主从复制，框架为一主一从结构。

MySQL中复制的优点简介：

  • 横向扩展解决方案 - 在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。
  • 数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。
  • 分析 - 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。
  • 远程数据分发 - 您可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器。

前提是作为主服务器角色的数据库服务器必须开启二进制日志

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

每个从服务器都会收到主服务器二进制日志的全部内容的副本。

从服务器设备负责决定应该执行二进制日志中的哪些语句。

除非另行指定，否则主从二进制日志中的所有事件都在从站上执行。

如果需要，您可以将从服务器配置为仅处理一些特定数据库或表的事件。

重要: 您无法将主服务器配置为仅记录特定事件。

每个从站(从服务器)都会记录二进制日志坐标：

  • 文件名
  • 文件中它已经从主站读取和处理的位置。

由于每个从服务器都分别记录了自己当前处理二进制日志中的位置，因此可以断开从服务器的连接，重新连接然后恢复继续处理。

MySQL 主从复制 Master主机配置

在主机{host0.hostname}上进行如下操作

在右侧实验区root@{host0.hostname}:~#提示符后进行如下操作

复制MySQL安装文件并解压到合适的目录

cd /usr/local/
cp /share/tar/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz .
tar xvf mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
rm mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.19-linux-glibc2.12-x86_64 /usr/local/mysql

配置MySQL用户

groupadd mysql
useradd -r -g mysql mysql

配置相关目录及权限

chown -R mysql  /usr/local/mysql
chgrp -R mysql /usr/local/mysql

#准备数据目录
mkdir -p /data/mysql
chown -R mysql:mysql /data/mysql
chmod -R 755 /data/mysql

mkdir -p /usr/local/mysql/mysqld
chown -R mysql:mysql /usr/local/mysql/mysqld
chmod -R 755 /usr/local/mysql/mysqld

配置相关环境变量

ln -fs /usr/local/mysql/bin/mysql /usr/local/bin/mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
MYSQL_HOME=/usr/local/mysql/bin
PATH=$MYSQL_HOME/bin:$PATH
export MYSQL_HOME

安装必要的支持

apt update && apt install libaio1 numactl -y

生成{host0.hostname}上的MySQL配置文件

cat >/etc/my.cnf << EOF
[mysqld]
server_id=11
log-bin=mysql-bin
#binlog-do-db=test
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp=true
basedir = /usr/local/mysql/
datadir = /data/mysql
port = 3306
socket = /usr/local/mysql/mysqld/mysql.sock
pid-file = /usr/local/mysql/mysql.pid
character-set-server=utf8
back_log = 300
max_connections = 3000
max_connect_errors = 50
table_open_cache = 4096
max_allowed_packet = 32M
max_heap_table_size = 128M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 16
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
thread_stack = 512K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 128M
long_query_time = 6
innodb_buffer_pool_size = 1G
innodb_thread_concurrency = 16
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = on
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
default-character-set=utf8
safe-updates
[myisamchk]
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
[client]
port = 3306
socket = /usr/local/mysql/mysqld/mysql.sock
default-character-set = utf8
EOF

开始初始化MYSQL配置文件

/usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql --initialize

注意：

一定要记住临时密码，出现在初始化过程中的[Note] A temporary password is generated for root@localhost后边12位长度的随机数字字母串。

启动MySQL服务

/etc/init.d/mysqld start

登陆MySQL，使用上边初始化时产生的临时密码

mysql -uroot -p

重新设置密码

set password=password('abcabc');

创建复制用户replicationtester并授权

GRANT REPLICATION SLAVE,FILE ON *.* TO 'replicationtester'@'{host1.ip}' IDENTIFIED BY '123456';

查看binlog信息，记住当前binlog日志文件名及位置信息，用于在从机上进行配置。

show master status;

MySQL binlog扩展

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

查看指定binlog文件的内容 show binlog events in 'mysql-bin.000002';

获取binlog文件列表 show binary logs;

show variables like 'log%';

清空所有binlog日志 reset master;

MySQL 主从复制 从机配置

在从机{host1.hostname}上进行如下操作

从右侧实验区的终端界面使用ssh命令进行从机{host1.hostname}

ssh root@{host1.ip}

密码为:{host1.token}

进入从机后{host1.hostname}后，命令行提示符应变为root@{host1.hostname}:~#

在从机上进行如下操作：

cd /usr/local/
cp /share/tar/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz .
tar xvf mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
rm mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.19-linux-glibc2.12-x86_64 /usr/local/mysql

配置MySQL用户

groupadd mysql
useradd -r -g mysql mysql

配置相关目录及权限

chown -R mysql  /usr/local/mysql
chgrp -R mysql /usr/local/mysql

#准备数据目录
mkdir -p /data/mysql
chown -R mysql:mysql /data/mysql
chmod -R 755 /data/mysql

mkdir -p /usr/local/mysql/mysqld
chown -R mysql:mysql /usr/local/mysql/mysqld
chmod -R 755 /usr/local/mysql/mysqld

配置相关环境变量

ln -fs /usr/local/mysql/bin/mysql /usr/local/bin/mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
MYSQL_HOME=/usr/local/mysql/bin
PATH=$MYSQL_HOME/bin:$PATH
export MYSQL_HOME

安装必要的支持

apt update && apt install libaio1 numactl -y

生成从机上的MySQL配置文件

cat >/etc/my.cnf << EOF
[mysqld]
server_id=12
log-bin=mysql-bin
#binlog-do-db=test
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp=true
basedir = /usr/local/mysql/
datadir = /data/mysql
port = 3306
socket = /usr/local/mysql/mysqld/mysql.sock
pid-file = /usr/local/mysql/mysql.pid
character-set-server=utf8
back_log = 300
max_connections = 3000
max_connect_errors = 50
table_open_cache = 4096
max_allowed_packet = 32M
max_heap_table_size = 128M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 16
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
thread_stack = 512K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 128M
long_query_time = 6
innodb_buffer_pool_size = 1G
innodb_thread_concurrency = 16
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = on
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
default-character-set=utf8
safe-updates
[myisamchk]
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
[client]
port = 3306
socket = /usr/local/mysql/mysqld/mysql.sock
default-character-set = utf8
EOF

开始初始化MYSQL配置文件

/usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql --initialize

注意：

一定要记住临时密码，出现在初始化过程中的[Note] A temporary password is generated for root@localhost后边。

启动MySQL服务

/etc/init.d/mysqld start

登陆MySQL，使用上边初始化时产生的临时密码

mysql -uroot -p

重新设置密码

set password=password('abcabc');

设置主机信息

CHANGE MASTER TO MASTER_HOST='{host0.ip}',MASTER_PORT=3306,MASTER_USER='replicationtester',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=154;

启动复制

start slave;

查看从机复制状态

show slave status\G

会出现以下类似信息：

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.165.46.11
                  Master_User: mstest
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 701
               Relay_Log_File: node1-relay-bin.000002
                Relay_Log_Pos: 867
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 701
              Relay_Log_Space: 1074
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 11
                  Master_UUID: a0542e1c-9b6a-11ea-a548-00000045a233
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

注意其中这两部分：

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Slave_IO_State: Waiting for master to send event

当出现该提示，即证明主从已经搭建完成。

MySQL 主从复制 验证

在主机{host0.hostname}上进行如下操作

在右侧实验区root@{host0.hostname}:~#提示符后进行如下操作

#密码为上一节设置的abcabc
mysql -u root -p

执行以下语句用于在主机{host0.hostname}上的建立一个新的数据库test1,并在此数据库下建立表格t11后，并退出。

create database test1;
use test1;
create table t11(id int);

使用show processlist命令，查看复制进程的提示中State是否为Master has sent all binlog to slave; waiting for more updates

show processlist\G
exit;

在从机{host1.hostname}上进行如下操作

连接到从机{host1.hostname}，验证是否同步了主机的操作。从右侧实验区的终端界面使用ssh命令进入从机{host1.hostname}

ssh root@{host1.ip}

密码为:{host1.token}

进入从机后{host1.hostname}后，命令行提示符应变为root@{host1.hostname}:~#

进入从机的mysql,密码为abcabc

mysql -uroot -p

使用show processlist命令，查看复制进程的提示中State是否为Waiting for master to send event和Slave has read all relay log; waiting for more updates,该种状态标明复制进程工作正常。

show processlist\G

查看是否同步了主机的test1数据库

show databases;

查看是否同步了主机的test1数据库中的数据表t11

use test1;
show tables;

如果显示与{host0.hostname}相同，恭喜您，则证明MySQL主从复制架构搭建完完成并工作正常。

关于二进制日志

mysqld将数字扩展名附加到二进制日志基本名称以生成二进制日志文件名。每次服务器创建新日志文件时，该数字都会增加，从而创建一系列有序的文件。每次启动或刷新日志时，服务器都会在系列中创建一个新文件。服务器还会在当前日志大小达到[max_binlog_size]参数设置的大小后自动创建新的二进制日志文件 。二进制日志文件可能会比[max_binlog_size](使用大型事务时更大， 因为事务是以一个部分写入文件，而不是在文件之间分割。

为了跟踪已使用的二进制日志文件， [mysqld]还创建了一个二进制日志索引文件，其中包含所有使用的二进制日志文件的名称。默认情况下，它具有与二进制日志文件相同的基本名称，并带有扩展名'.index'。在[mysqld]运行时，您不应手动编辑此文件。

术语二进制日志文件通常表示包含数据库事件的单个编号文件。

术语 二进制日志 表示含编号的二进制日志文件集加上索引文件。

SUPER 权限的用户可以使用SET sql_log_bin=0语句禁用其当前环境下自己的语句的二进制日志记录

移动端设备除iPad Pro外，其它移动设备仅能阅读基础的文本文字。
建议使用PC或笔记本电脑，浏览器使用Chrome或FireFox进行浏览，以开启左侧互动实验区来提升学习效率，推荐使用的分辨率为1920x1080或更高。
我们坚信最好的学习是参与其中这一理念，并致力成为中文互联网上体验更好的学练一体的IT技术学习交流平台。
您可加QQ群：575806994，一起学习交流技术,反馈网站使用中遇到问题。
内容、课程、广告等相关合作请扫描右侧二维码添加好友。

狐狸教程 Copyright 2021

进入全屏