MySQL 复制详解及简单实例

2024-02-25 02:19:03 80

MySQL复制详解及简单实例

主从复制技术在MySQL中被广泛使用，主要用于同步一台服务器上的数据至多台从服务器，可以用于实现负载均衡，高可用和故障切换，以及提供备份等等。MySQL支持多种不同的复制技术，诸如单向，半同步异步复制等以及不同级别的复制，诸如数据库级别，表级，跨库同步等等。本文简要描述了一个基本的主从复制并给出示例。

1、复制的基本原理(步骤)

a、在主库上把数据更改记录的二进制日志(binarylog)
b、从库上的I/O线程连接到主库并请求发送其二进制日志文件(主库上的binlogdump线程将二进制日志内容发送到从库)
c、从库上的I/O线程读取主服务发送的二进制内容并将其拷贝到中继日志
d、从库上的SQL线程读取中继日志并执行日志中包含的更新

2、为配置文件添加复制项

#本文的演示基于同一服务器上的多实例环境，其中3406端口用作主库，而3506用作从库。
#关于多实例的部署可参考：
#MySQL多实例配置(一)http://blog.csdn.net/leshami/article/details/40339167
#MySQL多实例配置(二)http://blog.csdn.net/leshami/article/details/40339295
#3406与3506为都为新装且含缺省库等，所以本文演示中未涉及先迁移主库数据到备库步骤
a、主库上的配置文件
#moremy3406.cnf
[mysqld]
socket=/tmp/mysql3406.sock
port=3406
pid-file=/data/inst3406/data3406/my3406.pid
user=mysql
log-error=/data/inst3406/data3406/inst3406.err
datadir=/data/inst3406/data3406
basedir=/app/soft/mysql5

####formasteritems####
server-id=3406
log_bin=/data/inst3406/log/bin/inst3406bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1

b、从库上的配置文件
#moremy3506.cnf
[mysqld]
socket=/tmp/mysql3506.sock#Author:Leshami
port=3506#Blog:http://blog.csdn.net/leshami
pid-file=/data/inst3506/data3506/my3506.pid
user=mysql
log-error=/data/inst3506/data3506/inst3506.err
datadir=/data/inst3506/data3506
basedir=/app/soft/mysql5

####forslaveitems####
server-id=3506
relay_log=/data/inst3506/log/relay/relay-bin
read_only=1

3、创建复制账号

#启动端口为3406的实例并添加账户
[mysql@app~]$mysqld_safe--defaults-file=/data/inst3406/data3406/my3406.cnf&
[mysql@app~]$mysql-P3406#登陆到3406

master@localhost[(none)]>showvariableslike'server_id';
+---------------+-------+
|Variable_name|Value|
+---------------+-------+
|server_id|3406|
+---------------+-------+

#创建用于复制的账户
master@localhost[(none)]>grantreplicationslave,replicationclienton*.*
->torepl@'192.168.1.177'identifiedby'repl';

#初始化主库日志文件，生成环境慎用reset
master@localhost[(none)]>resetmaster;
QueryOK,0rowsaffected(0.01sec)

#查看主库的状态，日志初始化至000001，
master@localhost[(none)]>showmasterstatus,Position为120
+--------------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+--------------------+----------+--------------+------------------+-------------------+
|inst3406bin.000001|120||||
+--------------------+----------+--------------+------------------+-------------------+

4、配置主从同步

#启动端口为3506的实例
[mysql@app~]$mysqld_safe--defaults-file=/data/inst3506/data3506/my3506.cnf&

[mysql@app~]$msyql-P3506

slave@localhost[(none)]>showvariableslike'server_id';
+---------------+-------+
|Variable_name|Value|
+---------------+-------+
|server_id|3506|
+---------------+-------+
1rowinset(0.00sec)

#为从库添加指向主库的相关配置信息，该命令会生成及修改备库上的master.info及relay-log.info文件
slave@localhost[(none)]>CHANGEMASTERTOMASTER_HOST='192.168.1.177',
->MASTER_USER='repl',
->MASTER_PASSWORD='repl',
->MASTER_PORT=3406,
->MASTER_LOG_FILE='inst3406bin.000001',
->MASTER_LOG_POS=0;
QueryOK,0rowsaffected,2warnings(0.04sec)

#出现了2个warnings，查看一下
slave@localhost[(none)]>showwarnings\G
***************************1.row***************************
Level:Note
Code:1759
Message:SendingpasswordsinplaintextwithoutSSL/TLSisextremelyinsecure.
***************************2.row***************************
Level:Note
Code:1760
Message:StoringMySQLusernameorpasswordinformationinthemaster.inforepositoryisnotsecureandisthereforenotrecommended.
PleaseseetheMySQLManualformoreaboutthisissueandpossiblealternatives.
2rowsinset(0.00sec)

#此时查看从库的状态信息
slave@localhost[(none)]>showslavestatus\G
***************************1.row***************************
Slave_IO_State:
Master_Host:192.168.1.177
Master_User:repl
Master_Port:3406
Connect_Retry:60
Master_Log_File:inst3406bin.000001
Read_Master_Log_Pos:4
Relay_Log_File:relay-bin.000001
Relay_Log_Pos:4
Relay_Master_Log_File:inst3406bin.000001
Slave_IO_Running:No#IO线程没有运行
Slave_SQL_Running:No#SQL线程没有运行
......................
Master_Info_File:/data/inst3506/data3506/master.info

slave@localhost[(none)]>startslave;#启动slave
QueryOK,0rowsaffected(0.01sec)

#含义如下
STARTSLAVEwithnothread_typeoptionsstartsbothoftheslavethreads.TheI/Othreadreads
eventsfromthemasterserverandstoresthemintherelaylog.TheSQLthreadreadseventsfromthe
relaylogandexecutesthem.

#再次查看slave的状态
robin@localhost[(none)]>showslavestatus\G
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:192.168.1.177
Master_User:repl
Master_Port:3406
Connect_Retry:60
Master_Log_File:inst3406bin.000001
Read_Master_Log_Pos:120
Relay_Log_File:relay-bin.000002
Relay_Log_Pos:285
Relay_Master_Log_File:inst3406bin.000001
Slave_IO_Running:Yes#IO线程处于运行状态
Slave_SQL_Running:Yes#SQL线程处于运行状态
..............
Exec_Master_Log_Pos:120
Relay_Log_Space:452
............
Master_Server_Id:3406
Master_UUID:32f53a0a-63ef-11e4-93d9-8c89a5d108ae
Master_Info_File:/data/inst3506/data3506/master.info
SQL_Delay:0
SQL_Remaining_Delay:NULL
Slave_SQL_Running_State:Slavehasreadallrelaylog;waitingfortheslaveI/Othreadtoupdateit#重要的提示信息

#可以看到从库上的2个线程，一个是用于I/O线程，用于连接到主库请求主库发送binlog，一个是用于执行SQL的SQL线程。
slave@localhost[(none)]>showprocesslist\G
***************************1.row***************************
Id:4
User:systemuser
Host:
db:NULL
Command:Connect
Time:510993
State:Waitingformastertosendevent
Info:NULL
***************************2.row***************************
Id:5
User:systemuser
Host:
db:NULL
Command:Connect
Time:333943
State:Slavehasreadallrelaylog;waitingfortheslaveI/Othreadtoupdateit
Info:NULL

5、验证同步情况

#下面在主库上执行一些操作以检查从库的同步情况
master@localhost[(none)]>showvariableslike'server_id';
+---------------+-------+
|Variable_name|Value|
+---------------+-------+
|server_id|3406|
+---------------+-------+
1rowinset(0.00sec)

#主库上BinlogDump线程用于发送binlog日志文件到从库，如下查询
master@localhost[(none)]>showprocesslist\G
***************************1.row***************************
Id:12
User:repl
Host:192.168.1.177:57440
db:NULL
Command:BinlogDump
Time:511342
State:Masterhassentallbinlogtoslave;waitingforbinlogtobeupdated
Info:NULL

#主库创建数据库及表
master@localhost[(none)]>createdatabasetempdb;
QueryOK,1rowaffected(0.01sec)

master@localhost[(none)]>usetempdb
Databasechanged
master@localhost[tempdb]>createtabletb_enginesasselect*frominformation_schema.engines;
QueryOK,9rowsaffected(0.02sec)
Records:9Duplicates:0Warnings:0

#下面是在从库上检查的结果
slave@localhost[(none)]>selectcount(*)fromtempdb.tb_engines;
+----------+
|count(*)|
+----------+
|9|
+----------+

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

MySQL 复制详解及简单实例

热门推荐

随机推荐