mysql多主主从数据库_MySQL多实例，主从同步

末蓝、 2023-10-05 18:23 28阅读 0赞

MySQL 主从复制是一个异步复制过程(但看起来也是实时的)，数据库数据从一个MySQL数据库(我们称为Master)复制到另一个MySQL数据库(我们称之为Slave)。在Master和Slave之间实现整个主从复制的过程有三个线程参与完成。其中两个线程(SQL线程和IO线程)在Slave端，另一个线程(IO线程)在Master端。

要实现MySQL的主从复制，首先必须打开Master端的Binlog(MySQL-bin.XXXXX)功能，否则无法实现主从复制。因为整个复制过程实际上就是Slave从Master端获取Binlog日志，然后再在Slave自身上以相同顺序执行binlog日志中所记录的各种操作。打开MySQL的binlog可以通过MySQL的配置文件my.cnf中的mysqld模块(\[mysqld\]标识后的参数部分)添加“log-bin”参数项。

MySQL主从复制过程描述

下面简单描述下MySQL Replication的复制过程：

1.Slave服务器上执行start slave，开启主从复制开关。

2.此时，Slave服务器的IO线程会通过在Master上授权的复制用户请求连接Master服务器，并请求从指定Binlog日志文件的指定位置(日志文件和位置是在配置主从服务时change master 时指定的)之后的Binlog日志内容。

3.Master服务器接收到来自Slave服务器的IO线程的请求后，Master服务器上负责复制IO线程根据Slave服务器的IO线程请求的信息读取指定Binlog日志文件指定位置之后的Binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了日志内容外，还有本次返回日志内容在Master服务器端的新Binlog文件名称以及在Binlog中的指定位置。

4. 当Slave服务器的IO线程获取到来自Master服务器上IO线程发送日志内容及日志文件及位置点后，将Binlog日志内容依次写入到Slave端自身的Relay Log(即中继日志)文件(MySQL-relay-bin.xxxxx)的最末端，并将新的Binlog文件名和位置记录到master-info文件中，以便下一次读取Master端新binlog日志时能够告诉Master服务器需要从新binlog日志的那个文件那个位置开始请求新的Binlog日志内容。

5. Slave服务器的SQL线程会实时的检测本地Relay Log中新增加了日志内容，然后及时的把Log文件中的内容解析成在Master端曾经执行的SQL语句的内容，并在自身Slave服务器上按语句的顺序执行应用这些ＳＱＬ语句。

6. 经过了上面的过程，就可以确保在Master端和Slave端执行了同样的SQL语句。当复制状态正常的情况下，Master端和Slave端的数据是完全一样的。

主从复制的原理图

![ba32a51723968763bee20469236f6745.png][]

具体实施主从复制

下面介绍的测试环境的安装，关于线上的环境不好多讲，具体步骤如下：

单实例安装步骤

1.建立MySQL 账户

\#groupadd mysql \#useradd -s /sbin/nologin -g mysql -M mysql

\#tail -l /etc/passwd

建立 MySQL 软件目录

\#mkdir -p /home/tools

\#cd /home/tools/

\#tar zxf mysql-5.1.62.tar.gz \#cd mysql-5.1.62

配置

./configure \\

\--prefix=/usr/local/mysql \\

\--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock \\

\--localstatedir=/usr/local/mysql/data \\

\--enable-assembler \\

\--enable-thread-safe-client \\

\--with-mysqld-user=mysql \\

\--with-big-tables \\

\--without-debug \\

\--with-pthread \\

\--enable-assembler \\

\--with-extra-charsets=complex \\

\--with-ssl \\

\--with-embedded-server \\

\--enable-local-infile \\

\--with-plugins=partition,innobase \\

\--with-plugin-PLUGIN \\

\--with-mysqld-ldflags=-all-static \\

\--with-client-ldflags=-all-static

3.静态编译生成mysqld的执行文件

\#make

4.安装MySQL

\#make install

5.获取MySQL 配置文件

\#ls -l support-files/\*.cnf \#cp support-files/my-small.cnf /etc/my.cnf

6.创建数据库文件

\#mkdir -p /usr/local/mysql/data \#chown -R mysql.mysql /usr/local/mysql

\#/usr/local/mysql/bin/mysql\_install\_db --user=mysql

7.启动MySQL 数据库

\#cp support-files/mysql.server /usr/local/mysql/bin \#netstat -lnt|grep 3306

\#/user/local/bin/mysql\_safe --user=mysql &

8.配置MySQL 命令的全局使用路径

\#echo 'export PATH=$PATH:/usr/local/mysql/bin' >>/etc/profile \#source /etc/profile

9.配置/etc/init.d/mysqld start 方式启动数据库

\#cp support-files/mysql.server /etc/init.d/mysqld \#chmod 700 /etc/init.d/mysqld

\#/etc/init.d/mysqld restart

多实例安装

1.采用不同的端口来作为二级目录

mkdir -p /data/\{3306,3307\}/data

2 创建MySQL多实例配置文件

ls -l support-files/\*.cnf

/bin/cp support-files/my-small.cnf /etc/my.cnf

3 通过vi命令添加如下：

vi /data/3306/my.cnf

vi /data/3307/my.cnf

my.cnf配置

\[client\]

port = 3306

socket = /data/3306/mysql.sock

\[mysql\]

no-auto-rehash

\[mysqld\]

user = mysql

port = 3306

socket = /data/3306/mysql.sock

basedir = /usr/local/mysql

datadir = /data/3306/data

open\_files\_limit = 1024

back\_log = 600

max\_connections = 800

max\_connect\_errors = 3000

table\_cache = 614

external-locking = FALSE

max\_allowed\_packet =8M

sort\_buffer\_size = 1M

join\_buffer\_size = 1M

thread\_cache\_size = 100

thread\_concurrency = 2

query\_cache\_size = 2M

query\_cache\_limit = 1M

query\_cache\_min\_res\_unit = 2k

default\_table\_type = InnoDB

thread\_stack = 192K

transaction\_isolation = READ-COMMITTED

tmp\_table\_size = 2M

max\_heap\_table\_size = 2M

long\_query\_time = 1

log\_long\_format

log-error = /data/3306/error.log

log-slow-queries = /data/3306/slow.log

pid-file = /data/3306/mysql.pid

log-bin = /data/3306/mysql-bin

relay-log = /data/3306/relay-bin

relay-log-info-file = /data/3306/relay-log.info

binlog\_cache\_size = 1M

max\_binlog\_cache\_size = 1M

max\_binlog\_size = 2M

expire\_logs\_days = 7

key\_buffer\_size = 16M

read\_buffer\_size = 1M

read\_rnd\_buffer\_size = 1M

bulk\_insert\_buffer\_size = 1M

myisam\_sort\_buffer\_size = 1M

myisam\_max\_sort\_file\_size = 10G

myisam\_max\_extra\_sort\_file\_size = 10G

myisam\_repair\_threads = 1

myisam\_recover

lower\_case\_table\_names = 1

skip-name-resolve

slave-skip-errors = 1032,1062

replicate-ignore-db=mysql

server-id = 1

innodb\_additional\_mem\_pool\_size = 4M

innodb\_buffer\_pool\_size = 32M

innodb\_data\_file\_path = ibdata1:128M:autoextend

innodb\_file\_io\_threads = 4

innodb\_thread\_concurrency = 8

innodb\_flush\_log\_at\_trx\_commit = 2

innodb\_log\_buffer\_size = 2M

innodb\_log\_file\_size = 4M

innodb\_log\_files\_in\_group = 3

innodb\_max\_dirty\_pages\_pct = 90

innodb\_lock\_wait\_timeout = 120

innodb\_file\_per\_table = 0

\[mysqldump\]

quick

max\_allowed\_packet = 2M

\[mysqld\_safe\]

log-error=/data/3306/mysql\_barry3306.err

pid-file=/data/3306/mysqld.pid

4 创建MySQL多实例的启动文件

启动文件mysql 3306

\#!/bin/sh

\#/data/3306/mysql 脚本

\#init

port=3306

mysql\_user="root"

mysql\_pwd=""

CmdPath="/usr/local/mysql/bin"

\#startup function

function\_start\_mysql()

printf "Starting MySQL...\\n"

/bin/sh $\{CmdPath\}/mysqld\_safe --defaults-file=/data/$\{port\}/my.cnf 2>&1 > /dev/null &

\#stop function

function\_stop\_mysql()

printf "Stoping MySQL...\\n"

$\{CmdPath\}/mysqladmin -u $\{mysql\_user\} -p$\{mysql\_pwd\} -S /data/$\{port\}/mysql.sock shutdown

\#restart function

function\_restart\_mysql()

printf "Restarting MySQL...\\n"

function\_stop\_mysql

sleep 2

function\_start\_mysql

case $1 in

start)

function\_start\_mysql

;;

stop)

function\_stop\_mysql

;;

restart)

function\_restart\_mysql

;;

\*)

printf "Usage: /data/$\{port\}/mysql \{start|stop|restart\}\\n"

esac

5 目录结构

tree /data

/data

\--3306

|--my.cnf

|--mysql

|--data

\--3307

|--my.cnf

|--mysql

|--data

\#授权

chown -R mysql.mysql /data

find /data -name mysql -exec chmod 700 \{\} \\

6 配置MySQL命令全局使用路径

配置全局路径意义

如果不配置MySQL命令的全局路径，就无法直接敲MySQL等命令，也就是只能/usr/local/msyql/bin/mysql这样敲命令。

echo 'export PATH=$PATH:/usr/local/mysql/bin' >>/etc/profile

source /etc/profile

7 创建MySQL多实例数据文件

mysql\_install\_db --datadir=/data/3306/data --user=mysql

mysql\_install\_db --datadir=/data/3307/data --user=mysql

8启动MySQL多实例数据库

/data/3306/mysql start

/data/3307/mysql start

\#检查MySQL数据是否启动

netstat -lnt|grep 330\[6,7\]

9 配置MySQL多实例数据库开机自动启动

echo "/data/3306/mysql start" >>/etc/rc.local

echo "/data/3307/mysql start" >>/etc/rc.local

10 登录MySQL测试

mysql -S /data/3306/mysql.sock

mysql -S /data/3307/mysql.sock

11 MySQL 安全配置

为root增加密码

mysqladmin -u root -S /data/3306/mysql.sock password 'barry123' \#

mysql -S /data/3306/mysql.sock \#

mysql -uroot -p -S /data/3306/mysql.sock \#

查看并清理多余用户

select user,host form mysql.user

主从复制配置

主库，称为Master 从库称为Slave。

1. 主库上执行操作

(1) 设置server-id 值并开启binlog设置

根据前文MySQL的同步原理，我们知道复制的关键因素就是binlog日志。

执行 vi /data/3306/my.cnf 编辑my.cnf配置文件，按如下两个参数内容修改：

\[mysqld\]

server-id =1

log-bin=/data/3306/mysql-bin

检查配置后的结果

grep -E "server-id|log-bin" /data/3306/my.cnf

log-bin=/data/3306/mysql-bin

server-id=1

(2) 建立用于同步的账号rep

mysql -uroot -p'' -S /data/3306/mysql.sock

grant replication slave on \*.\* to 'rep'@'10.0.0.%' identified by 'password';

(3) 锁表只读(当前窗口不要关闭)

生产环境时，操作主从复制，需要申请停机事件，锁表会影响业务。

flush tables with read lock;

interactive\_timeout=60

wait\_timeout=60

(4) 查看主库状态

查看主库状态，即当前日志文件名和二进制偏移量

show master status 命令显示的信息要记录在案，后面的从库复制时是从这个位置开始同步。

(5)导出数据库数据

单开新窗口，导出数据库数据，如果数据量比较大，可以停库直接打包数据文件迁移。

mkdir /server/backup/ -p

mysqldump -uroot -p'password' -S /data/3306/mysql.sock -A -B |gzip >/server/backup/mysql\_bak.$\{date +%F\}.sql.gz

ls -l /server/backup/mysql\_bak.$\{date +%F\}.sql.gz

导为了确保导库期间，数据库没有数据插入，可以再检查下主库状态信息

mysql -u root -p'password' -S /data/3306/mysql.sock -e "show master status"

导库后，解锁主库，恢复可写：

unlock tables;

(6) 把主库备份的MySQL数据迁移到从库

这不常用命令有scp,rsync等，相关命令前面的课程已详细讲解过了，这里就不多描述了。

本文讲解的是单数据库多实例主从配置，因此，数据在一台机器上，查看下数据

2 从库执行操作

(1) 设置server-id 值并关闭binlog设置

数据库的server-id一般在LAN内是唯一的，这里的server-id要和主库及其他从库不同，并解析掉从库的binlog参数配置执行vi /data/3307/my.cnf配置文件，按如下两个参数内容修改：

\[mysqld\]

server-id=2

\#log-bin=/data/3307/mysql-bin

(2) 还原主库导出的数据备份

gzip -d mysql\_bak.2014-04-17.sql.gz

mysql -uroot -p'password' -S /data/3307/mysql.sock < mysql\_bak.2014-04-17.sql

(3) 登录从库配置同步参数

mysql -uroot -p'password' -S /data/3307/mysql.sock

CHANGE MASTER TO

MASTER\_HOST='10.0.0.x', <==这里是主库的IP

MASTER\_PORT=3306, <==这里是主库的端口，从库端口可以和主库不同。

MASTER\_USER='rep',<==这里是主库上建立的用于复制的用户rep

MASTER\_PASSWORD='password',

MASTER\_LOG\_FILE='mysql-bin.0000008',<==这里是show master status时看到的查到二进制文件名称

MASTER\_POS=342;<==这里是show master status时看到的查看二进制日志偏移量，注意不能多空格。

不登陆数据库，在命令行快速执行CHANGE MASTER的语句(适合在脚本中批量建Slave库用)

本文即用此法来操作

cat |mysql -uroot -p'password' -S /data/3307/mysql.sock<< EOF

CHANGE MASTER TO

MASTER\_HOST='10.0.0.x',

MASTER\_PORT=3306,

MASTER\_USER='rep',

MASTER\_PASSWORD='password',

MASTER\_LOG\_FILE='mysql-bin.0000008',

MASTER\_LOG\_POS=342;

EOF

也可登录数据库里面执行如下语句：

CHANGE MASTER TO

MASTER\_HOST='192.168.1.234',

MASTER\_PORT=3306,

MASTER\_USER='rep',

MASTER\_PASSWORD='password',

MASTER\_LOG\_FILE='mysql-bin.000010',

MASTER\_LOG\_POS=261;

(4) 启动从库同步开关

启动从库同步开关，并查看同步状态

mysql -uroot -p'password' -S /data/3307/mysql.sock -e "start slave;"

mysql -uroot -p'password' -S /data/3307/mysql.sock -e "show slave status\\G;"

判断复制是否搭建成功就看如下IO和SQL两个线程是否显示为Yes状态

Slave\_IO\_Running:Yes 负责从库去主库读取Binlog日志，并写入从库的中继日志中

Slave\_SQL\_Runnint:Yes 负责读取并中继日志中Binlog，转换SQL语句后应用到数据库汇总

Ok 到此整个过程基本上完成了。

[ba32a51723968763bee20469236f6745.png]: https://img-blog.csdnimg.cn/img_convert/ba32a51723968763bee20469236f6745.png

mysql多主主从数据库_MySQL多实例，主从同步

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL8 数据库主从同步

相关 Mysql的多机配置（主从、主主等）

相关 mysql多主主从数据库_MySQL多实例，主从同步

相关 mysql数据库主从同步配置

相关 MySQL数据库主从同步

相关 Mysql主从复制、多主多从配置

相关 Mysql主从同步

相关 MySQL主从同步

相关 mysql数据库主从同步（交互式）

相关 mysql数据库主从同步（交互式）